Logical Standby

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

By October 26, 2014 September 12th, 2016 No Comments

ENVIRONMENT

DATABASE – ORACLE 11gR2 11.2.0.3.0

PRIMARY DATABASE – PRODDB

STANDBY DATABASE – LOGSTDB (LOGICAL STANDBY)

SCHEMA NAME – TEST

NOTE: Already two tables are created in the TEST schema namely T1 and T2.

On Primary Database

  • Connect to the schema and check the table rows.

PRODDB>conn test/test@PRODDB  

Connected.

PRODDB>select * from tab;

TNAME  TABTYPE CLUSTERID

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

T1  TABLE

T2  TABLE

PRODDB>select * from t1;

NO

———-

1

2

3

PRODDB>select * from t2;

NO

———-

1

2

3

4

4

5

6

7 rows selected.

On Logical Standby Database

  • Stop the Logical Standby apply.

alter database stop logical standby apply;

LOGSTDB-LOGICAL>alter database stop logical standby apply;

Database altered.

  • Execute the below statement to skip a table replication.,

exec dbms_logstdby.skip(‘DML’,’SCHEMA_NAME’,’TABLE_NAME’,null);

LOGSTDB-LOGICAL>exec dbms_logstdby.skip(‘DML’,’TEST’,’T1′,null);

PL/SQL procedure successfully completed.

  • Start the Logical Standby apply.

alter database start logical standby apply immediate;

LOGSTDB-LOGICAL>alter database start logical standby apply immediate;

Database altered.

On Primary Database

  • Insert rows in to the tables T1 and T2.

PRODDB>insert into t1 values(4);

1 row created.

PRODDB>commit;

Commit complete.

PRODDB>select * from t1;

NO

———-

 1

 2

 3

 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.

On Logical Standby Database

  • 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

RE-INSTANTIATE THE SKIPPED TABLES

On Logical Standby Database

  • 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.

REPLICATION TESTING AFTER RE-INSTANTIATION

On Primary Database

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

On Logical Standby Database

LOGSTDB-LOGICAL>select * from test.t1;

NO

———-

1

2

3

4

5

Leave a Reply

© 2020 ORACLE-SCN. All Rights Reserved.

error: Content is protected !!