Step by Step Example : Data Pump

Owned by Copy Space for Confluence
Last updated: Nov 21, 2015 by Chris Lawless (Deactivated)
5 min read

The following example shows the complete starting point creation and replication start when there are no target objects in the target database. 
The approach is:

1. Complete the setup wizard and select the tables or schemas to be replicated. In this example a table (scott.avi_objects) and a schema (avi) will be replicated. The schema should exist on target database. Datapump script creates objects to be replicated in respective schemas. 

 

2. Run the *-all.sh script. This script will create the replication environment and also create the Oracle Data Pump script for the objects to be replicated.

 

3. Start the MINE process to start the replication on the source database.

 

4. Start the Oracle Data Pump script to create the objects in the target database and load them with initial data. In this example, script will create and load avi_objects table in scott schema and all the objects of avi schema on source database will be created and loaded in target database.

 

5. Once the Oracle Data Pump load has been completed, the APPLY process can be started on the target database.

 

1. The order of the step 3 (starting MINE) or 4 (Data Pump) above is not important. Step 4 can be done before step 3. It is however important that step 5 (starting APPLY) be done at the completion of step 4.

 

2. By default Dbvisit Replicate expects the replicated objects to exists on the target database when the prepare command is run. This may result in the following error:

dbvrep> PREPARE OFFLINE SCHEMA AVI
ERR-9083: Table AVI.AVI_TEST2 not found at apply.
FATAL-9043: Error detected and ON_ERROR set to EXIT.
Error encountered, not starting Dbvisit Replicate.

 

In this case edit the *.dbvrep script and set:

set ON_ERROR ERROR

 

to:

set ON_ERROR SKIP

 

After setting the ON_ERROR to skip, run the *-all script again.

 

3. The object grants on the target database may also error during Dbvisit Replicate setup:

grant select, update, insert, delete on SCOTT.AVI_OBJECTS to dbvrep
*
ERROR at line 1:
ORA-00942: table or view does not exist

 

This can be ignored as the Oracle Data Pump will create the necessary object grants as long as the same dbvrep user is used in both source and target databases.

 


Example

1. Start the setup wizard and setup Dbvisit Replicate. In this example the replication is called d112f, the source and target databases are both called d112f, the source server is dbvisit210 and the target server is dbvisit230.

 

2. Run the d112f-all.sh script created by the setup wizard on the source server. Note the error and warning messages because the objects do not appear in the target database.

$ ./d112f-all.sh 
Setting up Dbvisit Replicate configuration
Configure database d112f_dbvisit210...
Configure database d112f_dbvisit230...
Object grants for database d112f_dbvisit210...
Object grants for database d112f_dbvisit230...
grant select, update, insert, delete on SCOTT.AVI_OBJECTS to dbvrep
*
ERROR at line 1:
ORA-00942: table or view does not exist
Setting up the configuration
Initializing.....done
WARN-1850: No DDC DB available, dictionary table does not exist.
DDC loaded from database (0 variables).
Dbvisit Replicate version 2.8.00.0000
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /home/oracle/d112f/d112f-onetime.ddc loaded.
MINE: Cannot determine Dbvisit Replicate dictionary version. (no dictionary exists)
APPLY: Cannot determine Dbvisit Replicate dictionary version. (no dictionary exists)
dbvrep> set ON_WARNING SKIP
Variable ON_WARNING set to SKIP for process *.
dbvrep> set ON_ERROR EXIT
Variable ON_ERROR set to EXIT for process *.
dbvrep> ENGINE SETUP MINE DROP DICTIONARY
0 dictionary objects dropped.
dbvrep> ENGINE SETUP MINE CREATE DICTIONARY
dbvrep> ENGINE SETUP MINE LOAD DICTIONARY
Supplemental logging on database set.
Loading dictionary table DBRSCOL$
Loading dictionary table DBRSOBJ$
Loading dictionary table DBRSTAB$
Loading dictionary table DBRSUSER$
Loading dictionary table DBRSV_$DATABASE
dbvrep> ENGINE SETUP APPLY DROP DICTIONARY
0 dictionary objects dropped.
dbvrep> ENGINE SETUP APPLY CREATE DICTIONARY
dbvrep> ENGINE SETUP APPLY LOAD DICTIONARY
dbvrep> ENGINE PREPARE_DP SETUP CLEAR
dbvrep> ENGINE SETUP PAIR MINE AND APPLY
ID of mine proces is 98796642-23B7-11E1-BC5C-1BD78DA50EF3.
If not using DDC in database, set MINE_UNIQUE_ID to this value.
1 applier SCN set.
dbvrep> PREPARE OFFLINE SCHEMA AVI
WARN-9083: Table AVI.AVI_TEST2 not found at apply.
WARN-1645: No columns found for AVI.AVI_TEST2,
check privileges for Dbvisit Replicate dictionary user at apply or create the
target table if it does not exist - before you start the apply.
WARN-9246: Column AVI.AVI_TEST2.DATESTAMP is not in table AVI.AVI_TEST2 on apply.
WARN-9246: Column AVI.AVI_TEST2.ID is not in table AVI.AVI_TEST2 on apply.
WARN-9246: Column AVI.AVI_TEST2.TEST2 is not in table AVI.AVI_TEST2 on apply.
Table AVI.AVI_TEST2 instantiated at SCN 15652921
dbvrep> PREPARE OFFLINE TABLE SCOTT.AVI_OBJECTS
WARN-9083: Table SCOTT.AVI_OBJECTS not found at apply.
WARN-1645: No columns found for SCOTT.AVI_OBJECTS,
check privileges for Dbvisit Replicate dictionary user at apply or create
the target table if it does not exist - before you start the apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.CREATED is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.DATA_OBJECT_ID is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.EDITION_NAME is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.GENERATED is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.LAST_DDL_TIME is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.NAMESPACE is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.OBJECT_ID is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.OBJECT_NAME is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.OBJECT_TYPE is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.OWNER is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.SECONDARY is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.STATUS is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.SUBOBJECT_NAME is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.TEMPORARY is not in table SCOTT.AVI_OBJECTS on apply.
WARN-9246: Column SCOTT.AVI_OBJECTS.TIMESTAMP is not in table SCOTT.AVI_OBJECTS on apply.
Table SCOTT.AVI_OBJECTS instantiated at SCN 15652952dbvrep>
ENGINE PREPARE_DP WRITE DP_NETWORKLINK DIRECTORY DATA_PUMP_DIR FILE /home/oracle/d112f/APPLY.sh DBLINK d112f_dbvisit210
USERID SYSTEM/oracle@d112f_dbvisit230
Created Data Pump script /home/oracle/d112f/APPLY.sh, using network import.
dbvrep> create ddcdb from ddcfile
DDC loaded into database (156 variables).
dbvrep> set ON_WARNING SKIP
Variable ON_WARNING set to SKIP for process *.
dbvrep> set ON_ERROR SKIP
Variable ON_ERROR set to SKIP for process *.
OK-0: Completed successfully.
1) Create the necessary directory(ies) on the servers:
dbvisit210: /home/oracle/d112f
dbvisit230: /home/oracle/d112f 
2) Copy the DDC files to the server(s) where the processes will run:
/home/oracle/d112f/d112f-APPLY.ddc
/home/oracle/d112f/d112f-MINE.ddc 
3) Review that path to dbvrep executable is correct in the run scripts:
/home/oracle/d112f/d112f-run-dbvisit210.sh
/home/oracle/d112f/d112f-run-dbvisit230.sh 
4) Copy the run script to the server(s) where the processes will run:
/home/oracle/d112f/d112f-run-dbvisit210.sh
/home/oracle/d112f/d112f-run-dbvisit230.sh 
5) Ensure firewall is open for listen interfaces dbvisit230:7902, dbvisit210:7901 used by the processes.
6) Make sure the data on apply are in sync as of time when setup was run.
Scripts for Data Pump/export/DDL were created as requested:
/home/oracle/orcl/APPLY.sh
7) Start the replication processes on all servers:
/home/oracle/d112f/d112f-run-dbvisit210.sh
/home/oracle/d112f/d112f-run-dbvisit230.sh 
8) Start the console to monitor the progress:
/usr/bin/dbvrep --ddcfile /home/oracle/d112f/d112f-MINE.ddc

 

3. Start the MINE process by running the d112f-run-dbvisit210.sh on the source server.

$ ./d112f-run-dbvisit210.sh
Initializing.....done
DDC loaded from database (159 variables).
Dbvisit Replicate version 2.8.00.0000
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /home/oracle/d112f/d112f-MINE.ddc loaded.
Starting process MINE...started

 

4. Start the Oracle Data Pump script (ensure it has execution privileges).

$ chmod 755 APPLY.sh 
$ ./APPLY.sh 
Import: Release 11.2.0.1.0 - Production on Sun Dec 11 18:30:07 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": SYSTEM/********@d112f_dbvisit230
table_exists_action=TRUNCATE network_link=d112f_dbvisit210
directory=DATA_PUMP_DIR flashback_scn=15652921 tables=AVI.AVI_TEST2 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "AVI"."AVI_TEST2" 5 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 18:26:06
Import: Release 11.2.0.1.0 - Production on Sun Dec 11 18:30:17 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 
Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": SYSTEM/********@d112f_dbvisit230
table_exists_action=TRUNCATE network_link=d112f_dbvisit210
directory=DATA_PUMP_DIR flashback_scn=15652952 tables=SCOTT.AVI_OBJECTS 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 3 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."AVI_OBJECTS" 20000 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at 18:26:13

 

The target objects have now been created and the initial data has been loaded. The data is in sync for the APPLY process to start applying the replicated data.

 

5. Start the APPLY process on the target server by running the d112f-run-dbvisit230.sh script.

$ d112f-run-dbvisit230.sh
Initializing.....done
DDC loaded from database (159 variables).
Dbvisit Replicate version 2.8.00.0000
Copyright (C) Dbvisit Software Limited. All rights reserved.
DDC file /home/oracle/d112f/d112f-APPLY.ddc loaded.
Starting process APPLY...started

 

The replication on both servers has now been started and the data will be kept in sync.

Â