Initial Load - Instantiation - Oracle Golden Gate using Datapump

Full database export using expdp

Enable Minimal Supplemental Logging in Oracle on source

alter database add supplemental log data ;

Enable Supplemental Logging at Table Level on source

GGSCI> dblogin userid xxxxx password xxxxxx

GGSCI> add trandata <schema>.<tablename>

Add Extract, Add Exttrail, Add Pump, Add Rmttrail on source

Start Extract, Start Pump on source

Create a database directory:

SQLPLUS> create directory dumpdir as '<some directory>' ;

Get the current SCN on the source database: 

SQLPLUS> select current_scn from v$database ; 

28318029

Run the Data Pump Export using the flashback SCN you obtained in the previous step. The following example shows running the expdp utility at a Degree Of Parallelism (DOP) of 4. If you have sufficient system resources (CPU, memory and IO) then running at a higher DOP will decrease the amount of time it takes to take the export (up to 4x for a DOP of 4). Note that expdp uses Oracle Database parallel execution settings (e.g. parallel_max_servers) which have to be set appropriately in order to take advantage of parallelism. Other processes running in parallel may be competing for those resources. See the Oracle Documentation for more details.

>expdp directory=dumpdir full=y parallel=4 dumpfile=ora102_%u.dmp flashback_scn=28318029

>Username: system NOTE: any DB user with DBA privileges will do >Password:

Note: The export log needs to be checked for errors.

Start an import using impdp to the target database when the export step is complete.

Add and Start Replicat after import is complete:

GGSCI> add replicat <repname>, exttrail ./dirdat/<xx>

GGSCI> start replicat <repname>, aftercsn <value returned from step 6>

Example 2: schema export using exp

Enable Minimal Supplemental Logging in Oracle on source

SQLPLUS > alter database add supplemental log data;

Enable Supplemental Logging at Table Level on source

GGSCI> dblogin userid xxxxx password xxxxxx

GGSCI> add trandata <schema>.<tablename>

Add Extract, Add ExtTrail FileTrail File, Add Pump, Add RmtTrail FileTrail File on source

Start Extract, Start Pump on source

Get the current SCN on the source database:

SQLPLUS> select current_scn from v$database ;

CURRENT_SCN

-----------------------

28318029

Get a flashback snapshot from the SCN that you obtained in the previous step. You can do this with exp or expdp utility. The following example shows the use of export utility (exp):

>exp <username>/<password> owner=<schema_name> grants=n statistics=none triggers=n compress=n FLASHBACK_SCN=28318029

Note: Undo_retention has to be set high enough, and the export log needs to be checked for errors. You can speed up exp by running multiple session in parallel but you have to manually configure subsets of the data you want to export (e.g. different tables for different export files). Make sure to use the same FLASHBACK_SCN for all export sessions to ensure taking a read consistent backup.

Start an import to the target database when export is complete.

Add and Start Replicat:

GGSCI> add replicat <rename>, exttrail ./dirdat/<xx>

GGSCI> start replicat <rename>, aftercsn <SCN value returned from query on source>