Create Oracle Data Pump script Manually

Dbvisit Replicate assists in creating the starting point by creating Oracle Data Pump scripts which can be run manually to export the data out of the source database and into the target database.

Normally, the Data Pump scripts are created as part of the setup wizard and running the resulting *all.sh or *all.bat script.

However, The Oracle Data Pump scripts can also be manually created by running the following command:

ENGINE PREPARE_DP WRITE DP_NETWORKLINK DIRECTORY DATA_PUMP_DIR FILE...

This command is part of the *.dbvrep script that is created by the setup wizard and is run as part of the *-all.sh or *-all.bat script to configure the replication. This command can be used to generate datapump script after adding table(s)/schema(s) into an already running replication by using prepare command.

The dbvrep ENGINE PREPARE_DP command creates the Oracle Data Pump script using the following information:

1. The SCN starting point for each table to be replicated. The SCN is used to set the flashback_scn in Data Pump so that the data will be exported consistent with the starting point of the replication.

2. The SQL*Net TNS connections to connect to the source and target database using network import. The network_link has to be created manually in the target database. If the network link is not in place the following error message is displayed "ORA-39200: Link name "xxx" is invalid". The network link can be created with command:

CREATE public DATABASE LINK link_name CONNECT TO system IDENTIFIED BY password USING connect_string

1. The system username and password to connect to the source and target database.

2. Oracle Directory on the target database called DATA_PUMP_DIR. This directory is created by install or upgrade of Oracle.

No outage of the source database is required because the SCN is used to extract the data out of the source data. This ensures that the data will be consistent at that particular point in time. Dbvisit Replicate will start replicating at the same point in time and therefore consistency is maintained between the source and target database.

Example

Following commands will add test1.emp table into the current replication and generate datapump script to load data into target database. Following environment is used in this example.

dbvrep>ENGINE PREPARE_DP SETUP CLEAR 
dbvrep>PREPARE TABLE TEST1.EMP
Connecting to running apply [TEST1.EMP]: [Apply table removed (0 metadata record(s)). 
Apply table added (1 metadata record(s)).]
Connecting to running mine [TEST1.EMP]: [Table prepared (321356 internal records).]
Table test1.temp instantiated at SCN 6704096
dbvrep>ENGINE PREPARE_DP WRITE DP_NETWORKLINK DIRECTORY DATA_PUMP_DIR FILE /home/oracle/reptest1/APPLY2.sh DBLINK reptest1_link USERID SYSTEM/password@reptest2
Created Data Pump script /home/oracle/reptest1/APPLY1.sh, using network import.

Above command will generate following datapump script.

impdp SYSTEM/password@reptest2 table_exists_action=TRUNCATE network_link=reptest1_link directory=
DATA_PUMP_DIR flashback_scn=6704096 tables=TEST1.emp logfile=TEST1_temp.log JOB_NAME=DP_reptest1_0001