...
...
What is Data instantiation
Before data replication can start, the target database must have a copy of the database objects that are going to be replicated. It is also necessary that the data for the objects to be replicated are completely in sync at the point at which the replication starts. Replication can only keep data in sync from a certain point in time, all the data before that point in time must be replicated manually as a one off task.
...
- Everything before the SCN must be copied manually. This is the Data Instantiation and is done in this example through Oracle DataPump, but other methods are also available such as using a standby database.
- Everything after the SCN is replicated by the logical replication tool (in this case Dbvisit Replicate, but can also be Golden Gate)
Running DataPump
1. On the target server (dbv02), ensure that the OE REPOE schema is clean. Run the following drop script to ensure that there are no OE objects. If there are then the DataPump script will fail.
...
| No Format |
|---|
SQL> @/u01/oracle/swingbench/sql/soedgdrop2.sql |
| No Format |
Create the Database Link
The APPLY.sh script created by the Setup Wizard for instantiating the target schema using DataPump export/import over a database link. This database link must be created for the script to work.
2. On the target server (dbv02), setup the database link as user systemSYSTEM pointing to the source database.
| No Format |
|---|
sqlplus system/oracle@ttorcl_trg |
| No Format |
oracle@source SQL> CREATE public DATABASE LINK ttorcl_srcsource CONNECT TO system IDENTIFIED BY oraclemanager USING 'ttorcl_srcsource'; |
SQL> exit |
This database link is needed for the Datapump script that is created as APPLY.sh. The content of datapump script APPLY.sh is similar to the following. DO NOT COPY THE CONTENT. This is just an example.
| No Format |
|---|
impdp SYSTEM/oracle@ttorcl_trgmanager@target table_exists_action=TRUNCATE network_link=ttorcl_srcsource directory=ttorcl_trg_DATA_PUMP_DIR flashback_scn=3046186 tables=OEREPOE.ADDRESSES,OE REPOE.CARD_DETAILS,OE.CMP3$95780,OE.CMP4$95780,OE REPOE.CUSTOMERS,OE REPOE.INVENTORIES,OE REPOE.LOGON,OE REPOE.ORDERENTRY_METADATA,OE REPOE.ORDERS,OE REPOE.ORDER_ITEMS,OE REPOE.PRODUCT_DESCRIPTIONS,OE REPOE.PRODUCT_INFORMATION,OE.PROMOTIONS,OE REPOE.WAREHOUSES logfile=OEREPOE_WAREHOUSES.log JOB_NAME=DP_dbvrep_orclXE_0001 |
The flashback_scn number (in this case3046186example 3046186) determines determines the consistency point as to where the data will be loaded to. All data prior to this SCN will be loaded using the above DataPump script. All data past this SCN will be replicated using Dbvisit Replicate.
3. On the source server (dbv01) , start the APPLY.sh script. This script will take approximately 5 3 - 25 9 minutes to complete depending on your host machine.
| No Format |
|---|
cd ~oracle/dbvrep_orclXE |
a. Run the script
| No Format |
|---|
./APPLY.sh |
The output will be similar to:
| No Format | |
|---|---|
Import: Release 1211.12.0.12.0 - Production on FriTue Aug 112 0515:31:4334 2014 Copyright (c) 1982, 20132009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c11g EnterpriseExpress Edition Release 1211.12.0.12.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYSTEM"."DP_DBVREP_ORCLXE_0001": SYSTEM/********@ttorcl_trg@target table_exists_action=TRUNCATE network_link=ttorcl_srcsource directory=ttorcl_trg_DATA_PUMP_DIR flashback_scn=3046186515210 tables=OEREPOE.ADDRESSES,OEREPOE.CARD_DETAILS,OE.CMP35780,OE.CMP45780,OE.REPOE.CUSTOMERS,OEREPOE.INVENTORIES,OEREPOE.LOGON,OEREPOE.ORDERENTRY_METADATA,OEREPOE.ORDERS,OEREPOE.ORDER_ITEMS,OEREPOE.PRODUCT_DESCRIPTIONS,OEREPOE.PRODUCT_INFORMATION,OE.PROMOTIONS,OEREPOE.WAREHOUSES logfile=OEREPOE_WAREHOUSES.log JOB_NAME=DP_dbvrep_orclXE_0001 Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 1616.0166 GBMB Processing object type TABLE_EXPORT/TABLE/TABLE Table "OE"."PROMOTIONS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate . . imported "OEREPOE"."ORDER_ITEMSINVENTORIES" 900131 4291621 rows . . imported "OEREPOE"."INVENTORIESORDER_ITEMS" 2143687 899846 rows . . imported "OEREPOE"."ORDERS" 1430027714895 rows . . imported "OEREPOE"."ADDRESSES" 750000 1500112 rows . . imported "OEREPOE"."CUSTOMERS" 500000 1000103 rows . . imported "OEREPOE"."CARD_DETAILS" 750000 1500103 rows . . imported "OEREPOE"."LOGON" 1191492 2383613 rows . . imported "OEREPOE"."PRODUCT_DESCRIPTIONS" 1000 rows . . imported "OEREPOE"."PRODUCT_INFORMATION" 1000 rows . . imported "OEREPOE"."ORDERENTRY_METADATA" 4 rows . . imported "OEREPOE"."WAREHOUSES" 1000 rows . . imported "OE"."PROMOTIONS" 0 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/INDEXCONSTRAINT/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICSREF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/CONSTRAINTINDEX/REF_CONSTRAINTFUNCTIONAL_AND_BITMAP/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/TABLEFUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKERTABLE_STATISTICS Job "SYSTEM"."DP_DBVREP_ORCLXE_0001" successfully completed | |
| Note | |
During the import of the data, the archiver may become stuck because there may not be enough space. Run the following script a few times on the target server (dbv02) to ensure that the flash recovery area does not fill up and pause the import: No Format | at 15:34:44 |