Versions Compared

Old Version 10

changes.mady.by.user Arjen Visser

Saved on

compared with

New Version Current

changes.mady.by.user Martin Knazovicky (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

...

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. This is done with the Oracle SCN. By using the Oracle SCN it is guaranteed that no transactions are lost during the replication setup.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.

This is called Data Instantiation and the Oracle SCN plays a key part in this. 

The Oracle SCN is captured at the point at which the replication starts to enable to pick a snapshot of the data at a point in time. This Oracle SCN is then used in two ways:

  1. Everything before the SCN must be copied manually. This is called the Data Instantiation and is done in this example through Oracle DataPump, but other methods are also available such as using a standby database.
  2. Everything after the SCN is replicated by the logical replication tool (in this case Dbvisit Replicate, but can also be Golden Gate)

...

1. On the target server (dbv02), ensure Ensure that the OE REPOE schema is clean. Run the following drop script to ensure that there are no OE objects. If . Do this by executing the record_count.bash script in the $HOME/replicate directory of the source machine. This will show you the record counts for the source and target tables but it will also show you if the target tables have not been created. (which is what you want). If there are then the DataPump script will fail.

No Format
sqlplus oe/oe@ttorcl_trg
No Format
SQL> @/u01/oracle/swingbench/sql/soedgdrop2.sql
No Format
SQL> exit

...

cd $HOME/replicate
./record_count.bash
TABLE_NAME                     TTORCL_SRC  TTORCL_TRG
------------------------------ ----------- -----------
ADDRESSES                      750003      *No Table*
CARD_DETAILS                   750003      *No Table*
CUSTOMERS                      500003      *No Table*
INVENTORIES                    900131      *No Table*
LOGON                          1191500     *No Table*
ORDERENTRY_METADATA            4           *No Table*
ORDERS                         714895      *No Table*
ORDER_ITEMS                    2143687     *No Table*
PRODUCT_DESCRIPTIONS           1000        *No Table*
PRODUCT_INFORMATION            1000        *No Table*
WAREHOUSES                     1000        *No Table*
11 rows selected.

Sum of orders         TTORCL_SRC         TTORCL_TRG
------------- ------------------ ------------------
ORDERS         $3,572,944,731.00               $.00

The output should indicate there are no tables in the REPOE schema in the target database. 

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, setup the database link as user systemthe user SYSTEM pointing to the source database.

SQL> exit
No Format
sqlplus system/oracle@ttorclmanager@ttorcl_trg
No Format

SQL> CREATE public DATABASE LINK ttorcl_src CONNECT TO system IDENTIFIED BY oraclemanager USING 'ttorcl_src';
No Format
Note

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

...

noformat

the following. DO NOT COPY THE CONTENT. This is just an example. 

Code Block
impdp SYSTEM/
oracle@ttorcl
manager@ttorcl_trg table_exists_action=TRUNCATE network_link=ttorcl_src directory=
ttorcl_trg_
DATA_PUMP_DIR flashback_scn=
3144204
564273 tables=
OE
REPOE.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
REPOE.
PROMOTIONS,OE.
WAREHOUSES   logfile=
OE
REPOE_WAREHOUSES.log JOB_NAME=DP_dbvrep_
orcl
XE_0001

 

The flashback_scn number (in this case 3144204example 564273) 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 , as oracle, in the $HOME/dbvrep_XE directory, execute the APPLY.sh script. This script will take approximately 25 3 - 9 minutes to complete depending on your host machine.

No Format
$cd $HOME/dbvrep_XE
./APPLY.sh

The output will be similar to:

/usr/local/bin/del_arch.sh orcl 0
STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."DP_DBVREP_XE_0001" successfully completed at 08:36:19
No Format
Import: Release 1211.12.0.12.0 - Production on FriTue Aug 126 0508:31:4333 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 table_exists_action=TRUNCATE network_link=ttorcl_src directory=ttorcl_trg_DATA_PUMP_DIR flashback_scn=3144204564273 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,OEREPOE.PROMOTIONS,OE.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"                         4291621900131 rows
. . imported "OEREPOE"."INVENTORIESORDER_ITEMS"                          8998462143687 rows
. . imported "OEREPOE"."ORDERS"                              1430027714895 rows
. . imported "OEREPOE"."ADDRESSES"                         750003  1500112 rows
. . imported "OEREPOE"."CUSTOMERS"                           1000103500003 rows
. . imported "OEREPOE"."CARD_DETAILS"                      750003  1500103 rows
. . imported "OEREPOE"."LOGON"                               23836131191500 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/INDEXCONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICSFUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEXAND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
ORA-39166: Object OE.CMP35780 was not found.
ORA-39166: Object OE.CMP45780 was not found.
Job "SYSTEM"."DP_DBVREP_ORCL_0001" completed with 2 error(s) at Fri Aug 1 05:38:02 2014 elapsed 0 00:06:14
[oracle@source dbvrep_orcl]$
Note

During the import of the data, the archiver will become stuck because there will 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

On the source machine, as oracle, in the $HOME/replicate directory, execute the record_count.bash script to check the record counts between the two databases.

No Format
cd $HOME/replicate
./record_count.bash
TABLE_NAME                     TTORCL_SRC  TTORCL_TRG
------------------------------ ----------- -----------
ADDRESSES                      750003      750003
CARD_DETAILS                   750003      750003
CUSTOMERS                      500003      500003
INVENTORIES                    900131      900131
LOGON                          1191500     1191500
ORDERENTRY_METADATA            4           4
ORDERS                         714895      714895
ORDER_ITEMS                    2143687     2143687
PRODUCT_DESCRIPTIONS           1000        1000
PRODUCT_INFORMATION            1000        1000
WAREHOUSES                     1000        1000

11 rows selected.

Sum of orders         TTORCL_SRC         TTORCL_TRG
------------- ------------------ ------------------
ORDERS         $3,572,944,731.00  $3,572,944,731.00

Because there was no transaction activity in the source database, the source and target database record counts should be the same.

The source and target databases are now in sync and we can start the replication processes.