Oracle Data Pump can be used to load the historical data from the source database to the target database to ensure the source and target databases are in synch before the data can be replicated. Data Pump is useful when a subset of the database is being replicated. If the full database is being replicated, consider using RMAN or a using standby database, rman duplicate or rman restore and recover to create the target database, although Data Pump can be still be used for copying the full database.
...
In the setup wizard the above options are displayed as follows:
| Section | ||
|---|---|---|
| Column | ||
| ||
| No Format | ||
| ||
What data copy script to create? (dp_networklink/dp_exp/exp/ddl_file/ddl_run/none) |
The options in the setup wizard are:
...
Manually generating the Data Pump script with a network link:
| Section | ||
|---|---|---|
| Column | ||
| ||
| No Format | ||
| ||
ENGINE PREPARE_DP WRITE DP_NETWORKLINK DIRECTORY DATA_PUMP_DIR FILE /home/oracle/d112f/APPLY.sh DBLINK d112f_dbvisit210 USERID SYSTEM/oracle@d112f_dbvisit230 dbvisit230 |
Manually generating the exp/imp script with a network link:
| Section | ||
|---|---|---|
| Column | ||
| ||
| Panel | ||
| No Format | ||
| ||
ENGINE PREPARE_DP WRITE EXP FILE /home/oracle/d112f/APPLY.sh MINE_USERID SYSTEM/oracle@d112f_dbvisit210 APPLY_USERID SYSTEM/oracle@d112f_dbvisit230 |
Example of Data Pump scripts generated by Dbvisit Replicate
...
An example of the Data Pump with network link script which is generated by Dbvisit Replicate is:
| Section | ||
|---|---|---|
| Column | ||
| ||
| Panel | ||
| No Format | ||
| ||
impdp SYSTEM/xxx@d112f_dbvisit230 table_exists_action=TRUNCATE network_link=d112f_dbvisit210 directory=DATA_PUMP_DIR flashback_scn=297940636 tables=OE.CUSTOMERS,OE.INVENTORIES,OE.LOGON,OE.ORDERENTRY_METADATA,OE.ORDERS,OE.ORDER_ITEMS,OE.PRODUCT_DESCRIPTIONS,OE.PRODUCT_INFORMATION,OE.STRESSTESTTABLE,OE.WAREHOUSES logfile=OE_CUSTOMERS.log JOB_NAME=DP_d112f_0001 |
| Note |
|---|
If the import is going in a different tablespace, make sure you specify that with a parameter REMAP_TABLESPACE=source_tablespace:target_tablespace |
In this example the
- Source TNS name is: d112f_dbvisit210
- Target TNS name is: d112f_dbvisit230
- The flashback_scn is 297940636. This is the starting point for replication and all data before this point will be exported from the source database and into the target database.
The network_link is: d112f_dbvisit210. This has to be created manually in the target database with the following command:
| Section | ||
|---|---|---|
| Column | ||
| ||
| Panel | ||
| No Format | ||
| ||
CREATE public DATABASE LINK d112f_dbvisit210 CONNECT TO system IDENTIFIED BY xxx USING 'd112f_dbvisit210' |
Data Pump with export datafile
An example of a Data Pump with export datafile script is:
| Section | ||
|---|---|---|
| Column | ||
| ||
| Panel | ||
| No Format | ||
| ||
echo ======================================== echo EXPORT echo ======================================== expdp SYSTEM/xxxxxx@d112f_dbvisit210 dumpfile=OE_SHEMA.dmp directory=DATA_PUMP_DIR \ flashback_scn=xxxxxxxxxx tables=OE.CUSTOMERS,OE.INVENTORIES,OE.LOGON,\ OE.ORDERENTRY_METADATA,OE.ORDERS,OE.ORDER_ITEMS,\ OE.PRODUCT_DESCRIPTIONS,OE.PRODUCT_INFORMATION,\ OE.STRESSTESTTABLE,OE.WAREHOUSES \ logfile=exp_OE_schema.log JOB_NAME=DP_d112f_0001 echo ======================================== echo IMPORT echo ======================================== impdp SYSTEM/xxxx@d112f_dbvisit230 dumpfile=OE_SHEMA.dmp directory=DATA_PUMP_DIR logfile=imp_OE_SCHEMA.log |
...
| Note |
|---|
| The EXPORT part has to be run on the source server and the IMPORT part has to be run on the target server. |
Timing of running Data Pump
...
- Run the setup wizard
- Run the resulting *-all.sh or *-all.bat script
- Start Data Pump
- Start the Mine MINE process. You do not have to wait until Data Pump has completed before started the Mine MINE process
- Start the Apply APPLY process once Data Pump has completed and the data instantiation on the target database has completed
...