Oracle GoldenGate Initial Loading: Oracle Database to Oracle Database

For Oracle DB to Oracle DB initial loading, you can use the following approaches:

• Import/Export with the flashback_scn option and start replication with aftercsn
• Backup with RMAN and start replciat with atcsn

Different Between SCN and CSN
SCN is used in Oracle Database means SCN (System Change/Commit Number). CSN is used by Oracle GoldenGate means CSN (Checkpoint Sequence Number). They both refer to the same number for Oracle database transactions.

​1. Import/Export with flashback and start replication with AFTERCSN. 
The steps are as follows:​

Step 1. Start the Extract

GGSCI>start extract exext, begin now

Step 2. Check the Current SCN

SQL> select dbms_flashback.get_system_change_number() from dual; (for schema export) 
SQL> select current_scn from v$database ; (for database export)

Step 3. Export (expdp) the Source Database with the SCN

>expdp directory=DATA_PUMP_DIR flashback_scn=xxx dumpfile=sourcedb.dmp parallel=4
logfile=sourcedb.log (schema=xxx)

Step 4. Import (impdp) to the Target Database

>impdp directory=DATA_PUMP_DIR dumpfile=sourcedb.dmp logfile=sourcedb_imp.log (schemas=xxx)

Step 5. Start the Replicat with AFTERCSN

GGSCI> start replicat exrep, AFTERCSN 

When to use ATCSN and AFTERCSN?ATCSN means start 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 datapump is used to instantiate target. The export operation performed is consistent as of FLASHBACK_SCN. AFTERCSN means starting from “that SCN+1”. Transaction with that SCN isn’t included. ​

2 .Backup with RMAN and start replciat with ATCSN

The steps are as follows:​​

Step 1. Start the Extract

GGSCI>start extract exext, begin now

Step 2. Check the Current SCN

SQL> select current_scn from v$database ; 

Step 3. Backup the Database with RMAN

> 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;

Step 4. Start the Replicat with ATCSN

GGSCI> start replicat exrep, ATCSN 

Reference

1. Oracle GoldenGate Best Practices: Instantiation from an Oracle Source Database –  Doc ID 1276058.1

source knowledge : http://jinyuwang.weebly.com/goldengate-for-oracle-blog/oracle-goldengate-initial-loading-oracle-database-to-oracle-database