Step by Step Example : Data Pump
- Copy Space for Confluence
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.