- Oracle Data Pump (EXPDP/IMPDP)
- Oracle RMAN
- Oracle Data Guard/Active Data Guard
- Oracle GoldenGate Initial Load
Oracle Data Pump (EXPDP and IMPDP) comes with the Oracle Database. You can use the tool to export and import Oracle database data at the database, schema or table level. You can also use them to perform the target database instantiation. The steps are described as follows:â
While your source database is running, you first need to start the extract so that the transactions are captured by Oracle GoldenGate.
GGSCI>start extract exext, begin now
Check the database SCN after the extract started, so that you make sure all the new transactions after this SCN are captured by Oracle GoldenGate.
SQL> select dbms_flashback.get_system_change_number() from dual; (for schema export) SQL> select current_scn from v$database ; (for database export)
Export the database with with the flashback_scn option.
>expdp directory=DATA_PUMP_DIR flashback_scn=xxx dumpfile=sourcedb.dmp parallel=4 logfile=sourcedb.log (schema=xxx)
>impdp directory=DATA_PUMP_DIR dumpfile=sourcedb.dmp logfile=sourcedb_imp.log (schemas=xxx)
Starting the replicat with aftercsn.
GGSCI> start replicat exrep, AFTERCSN XXX
Answer: SCN is used in Oracle Database which stands for the System Change/Commit Number. CSN is used by Oracle GoldenGate which stands for Checkpoint Sequence Number. They both refer to the same number of Oracle database transactions.
RMAN is an backup utility provided by the Oracle Database. To use RMAN for instantiation, the steps are as follows:ââ
GGSCI>start extract exext, begin now
SQL> select current_scn from v$database ;
> rman target sys/ @source auxiliary / connected to target database: SOURCE (DBID=XXX) connected to auxiliary database: TARGET (not mounted) RMAN> duplicate target database to "target" until scn XXX;
GGSCI> start replicat exrep, ATCSN XXX
You can also instantiate the target database by creating a standby database with Data Guard and then converting it into a snapshot database for Oracle GoldenGate to delivery data to. The steps are described as follows:
3.1 Start the Extract
ATCSN means applying from that SCN. So transaction with that particular SCN is included. ATCSN is used to start replicat if RMAN is used to instantiate target. From Database Backup and Recovery Reference, UNTIL SCN specifies an SCN as an upper limit. RMAN restore or recover up to but not including the specified SCN.
AFTERCSN is used to start replicat if data pump is used to instantiate the target database. The export operation performed is consistent as of FLASHBACK_SCN. AFTERCSN means starting from "that SCN+1". The transaction with that SCN isn't included. â
GoldenGate doesn't support ATCSN and AFTERCSN for all databases, for example, ATCSN and AFTERCSN is not supported for Informix IDS database.
GGSCI>start extract exext, begin now
We will discuss the steps later.
SQL> select standby_became_primary_scn from v$database;
GGSCI> start replicat exrep, AFTERCSN XXX
Oracle GoldenGate provides the build-in intial load support which includes initila load with GoldenGate trail fileswhen the source and target database are different, for example, schema mappings and data transformations are included in the data replication. Please refer the details at How to Configure a Recoverable Initial Load.
- 1276058.1 Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database, Oracle Support Notes
- Oracle GoldenGate on Linux: Initial Load from Oracle 11gR2 to Oracle 11gR2, Oracle Tutorial
- Oracle Goldengate 11g Initial Load Using Direct Load Method, Yet another technology blog.
- Loren Penton, Oracle GoldenGate: Heterogeneous Database Initial Load Using Oracle GoldenGate, Oracle A-Team Blog
- Amazon Web Services â Strategies for Migrating Oracle Database to AWS, Amazon AWS Whitepaper
- Burleson Consulting, GoldenGate configure Initial Load Extract Tips