We're here to help!

Load a few tables in Target when replication is running and then re-sync them with Source

Follow

Read our docs This article explains the procedure on how to sync the tables in Source, after a few tables have had data loaded into the Target. This is similar to a DR DRILL in which the application is re-directed to the Target during which time a few tables are populated and subsequently these changes have to be 're-sync'd' with the Source

Problem Details

Problem Statement

Sync tables in source when the tables are loaded in target

Applicable Error Code

N/A

Affected Versions

Tested in 2.8 and 2.9

Affected Platforms

Platform Independent

 
Description

 The example used here is a sample replication environment, Below are the high level steps that are performed for this activity. 

1. Mine and Apply Process is running.
2. Unprepare the tables which would be affected in target.
3. Load data in target /Test application in target.
4. Truncate table in source.
5. Load the affected table from target(using expdp/impdp)
6. prepare the table again.
7. Test a data load in source to see if target is getting replicated.
Steps Performed

1. MINE and APPLY Process is running.


\ Dbvisit Replicate 2.9.00(MAX edition) - Evaluation License expires in 30 days
MINE is running. Currently at plog 1090 and SCN 15067569 (06/29/2017 14:09:30).
APPLY is running. Currently at plog 1090 and SCN 15067539 (06/29/2017 14:09:16).
Progress of replication TEST:MINE->APPLY: total/this execution
--------------------------------------------------------------------------------------------------------------------------------------------
VIJAY.EMP:                    100%  Mine:100/100         Unrecov:0/0         Applied:100/100     Conflicts:0/0       Last:29/06/2017 14:07:04/OK
VIJAY.DEPT:                   100%  Mine:10/10           Unrecov:0/0         Applied:10/10       Conflicts:0/0       Last:29/06/2017 14:07:10/OK
--------------------------------------------------------------------------------------------------------------------------------------------
2 tables listed.

2. Unprepare the tables which would be affected in the target(In this example we are just using one table)


dbvrep> unprepare table vijay.emp
Connecting to running mine [VIJAY.EMP]: [Table unprepared (1 internal records).]
Connecting to running apply:[Apply table removed (1 metadata record(s)). Apply table added (0 metadata record(s)).]
Table VIJAY.EMP processed.
dbvrep>

3. Load data in Target/Test Application in target(In this example we are just loading 1000rows for table EMP in target



Target:
SQL> exec gen_data('EMP',1000);

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

  COUNT(*)
----------
      1114

4. Truncate the table in Source


Source:

SQL> truncate table emp;

Table truncated.

SQL>

5. Export the table in target


[oracle@target TEST]$ expdp dumpfile=exp_emp.dmp logfile=exp_emp.log directory=my_dir tables=vijay.emp

Export: Release 11.2.0.2.0 - Production on Thu Jun 29 14:12:56 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA dumpfile=exp_emp.dmp logfile=exp_emp.log directory=my_dir tables=vijay.emp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "VIJAY"."EMP"                               68.44 KB    1114 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/exp_emp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 14:13:04

6. Import in Source


[oracle@source ~]$ impdp dumpfile=exp_emp.dmp logfile=imp_emp.log directory=my_dir tables=vijay.emp table_exists_action=truncate

Import: Release 11.2.0.2.0 - Production on Thu Jun 29 14:14:21 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  /******** AS SYSDBA dumpfile=exp_emp.dmp logfile=imp_emp.log directory=my_dir tables=vijay.emp table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "VIJAY"."EMP" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "VIJAY"."EMP"                               68.44 KB    1114 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 14:14:26

7. Prepare the table again in replication


dbvrep> prepare table vijay.emp
Prepare enabled supplemental logging, waiting for SCN advance enough to prevent ORA-01466 during consistent export....Waited 2 seconds until scn_to_timestamp changed.
Connecting to running apply [VIJAY.EMP]: [Apply table removed (0 metadata record(s)). Apply table added (1 metadata record(s)).]
Connecting to running mine [VIJAY.EMP]: [Table prepared (2 internal records).]
Table VIJAY.EMP instantiated at SCN 15070663
dbvrep>

8. Load data in source to check if replication is catching up


Source:
SQL> exec gen_data('EMP',1000);

PL/SQL procedure successfully completed.

SQL> select count(*) from emp;

  COUNT(*)
----------
      2114

Target:
SQL> !hostname
target

SQL> select count(*) from emp;

  COUNT(*)
----------
      2114
Conclusion

We highly recommend to perform these steps in a test environment before trying in the live production environment, If any of the steps are missed , you might have to instantiate the entire replication again after reloading the source environment with the latest data.

Have more questions? Submit a request

Comments