ENVIRONMENT
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
