Skip DML Replication of a Table to Logical Standby site and Re-Instantiate it

PRODDB>select * from t2;

NO

———-

1

2

3

4

4

5

6

7 rows selected.

On Logical Standby Database

• Insert rows in to the tables T1 and T2.

PRODDB>insert into t1 values(4);

PRODDB>insert into t2 values(7);

1 row created.

PRODDB>commit;

Commit complete.

PRODDB>select * from t2;

NO

———-

1

2

3

4

4

5

6

7

8 rows selected.

• Now Check whether the inserted rows in to tables T1 and T2 are replicated to the Logical Standby Database.

LOGSTDB-LOGICAL>select * from test.t1;

NO

———-

1

2

3

LOGSTDB-LOGICAL>select * from test.t2;

NO

———-

1

2

3

4

6

7

4

5

8 rows selected.

NOTE: 

• From the above output we can clearly see that the inserted rows are not replicated to the Logical Standby Database. Replication to table T1 is skipped.

• Rows in the table T2 is replicated since it is not skipped

• Stop SQL apply process.

LOGSTDB-LOGICAL>alter database stop logical standby apply;

Database altered.

• Drop and recreate the table test.t1.

LOGSTDB-LOGICAL>drop table test.t1;

Table dropped.

LOGSTDB-LOGICAL>create table test.t1 (no number(4));

Table created.

• On the logical standby database execute the following the query.

LOGSTDB-LOGICAL>SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;

APPLIED_SCN    LATEST_SCN       MINING_SCN    RESTART_SCN

—————-  —————-    —————   —————-

2087190          2090492                             2087191

• Take a backup of the table test.T1 from the production database.

 host expdp test/test@PRODDB dumpfile=test.dmp directory=data_pump_dir tables=test.T1 flashback_scn=2087191

PRODDB>host expdp test/test@PRODDB dumpfile=test.dmp directory=data_pump_dir tables=test.T1 flashback_scn=2087191

Export: Release 11.2.0.3.0 – Production on Wed Feb 22 20:15:01 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting “TEST”.”SYS_EXPORT_TABLE_01″:  test/********@PRODDB dumpfile=test.dmp directory=data_pump_dir tables=test.T1 flashback_scn=2087191

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

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported “TEST”.”T1″                                 5.078 KB       4 rows

Master table “TEST”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded

******************************************************************************

Dump file set for TEST.SYS_EXPORT_TABLE_01 is:

  /cbsprddbs/oracle/admin/PRODDB/dpdump/test.dmp

Job “TEST”.”SYS_EXPORT_TABLE_01″ successfully completed at 20:15:53

Note: Flashback scn will be restart_scn which you get from v$logstdby_progress.

• Then import the datapump data to the Logical.

host impdp dumpfile=test.dmp directory=data_pump_dir table_exists_action=append

LOGSTDB-LOGICAL>host impdp dumpfile=test.dmp directory=data_pump_dir table_exists_action=append

Import: Release 11.2.0.3.0 – Production on Wed Feb 22 20:18:44 2012

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

Username: sys@LOGSTDB as sysdba

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded

Starting “SYS”.”SYS_IMPORT_FULL_01″:  sys/********@LOGSTDB AS SYSDBA dumpfile=test.dmp directory=data_pump_dir table_exists_action=append

Processing object type TABLE_EXPORT/TABLE/TABLE

Table “TEST”.”T1″ exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported “TEST”.”T1″                                 5.078 KB       4 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job “SYS”.”SYS_IMPORT_FULL_01″ successfully completed at 20:19:20

• Then unskip the skip rule for the table.

EXECUTE DBMS_LOGSTDBY.UNSKIP(‘DML’,’SCHEMA_NAME’,’TABLE_NAME’);

LOGSTDB-LOGICAL>EXECUTE DBMS_LOGSTDBY.UNSKIP(‘DML’,’TEST’,’T1′);

PL/SQL procedure successfully completed.

• Then start sql apply.

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

LOGSTDB-LOGICAL>ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

Note: If required analyze table test.T1 compute statistics or use dbms_stats to gather statistics.

PRODDB>insert into test.t1 values(5);

1 row created.

PRODDB>commit;

Commit complete.

PRODDB>select * from test.t1;

NO

———-

1

2

3

4

5

LOGSTDB-LOGICAL>select * from test.t1;

NO

———-

1

2

3

4

5