Sometimes, we may not want to reflect the operations performed on the Primary database to the Logical Standby database. It can be ensured that DML operations performed in Primary are not applied in Logical Standby Database as follows;
Step1:
Stop SQL Apply in Logical Standby
First we should stop applying sql in logical standby.
|
1 2 3 4 |
[Logical-1] SQL> alter database stop logical standby apply; Database altered. |
Step2:
Oracle Logical Standby Skip Schema
To skip applying DMLs performed in a specific schema to the Logical Standby
|
1 2 3 4 |
[Logical-1] SQL> EXECUTE DBMS_LOGSTDBY.SKIP(STMT => 'DML', schema_name => 'TEST', object_name => '%'); PL/SQL procedure successfully completed. |
Step3:
Start SQL Apply in Logical Standby
|
1 2 3 4 |
[Logical-1] SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE; Database altered. |
Step4:
Make sure archives are transported to Logical Standby and Applied
|
1 2 3 4 5 6 7 |
[Primary-1] SQL> select max(sequence#),thread# from v$archived_log group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 359 1 222 2 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 |
[Logical-1] SQL> SELECT sequence#, first_time, next_time, dict_begin, dict_end, applied FROM dba_logstdby_log ORDER BY sequence#; SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC APPLIED ---------- --------- --------- --- --- -------- 186 10-JAN-17 10-JAN-17 NO NO YES 187 10-JAN-17 10-JAN-17 NO NO YES 188 10-JAN-17 10-JAN-17 NO NO YES 189 10-JAN-17 10-JAN-17 NO NO YES 190 10-JAN-17 10-JAN-17 NO NO YES 191 10-JAN-17 10-JAN-17 NO NO YES 192 10-JAN-17 10-JAN-17 YES NO YES 193 10-JAN-17 10-JAN-17 NO YES YES 194 10-JAN-17 10-JAN-17 NO NO YES 195 10-JAN-17 10-JAN-17 NO NO YES 196 10-JAN-17 10-JAN-17 NO NO YES SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC APPLIED ---------- --------- --------- --- --- -------- 197 10-JAN-17 10-JAN-17 NO NO YES 198 10-JAN-17 10-JAN-17 NO NO YES 199 10-JAN-17 10-JAN-17 NO NO YES 200 10-JAN-17 10-JAN-17 NO NO YES 201 10-JAN-17 10-JAN-17 NO NO YES 202 10-JAN-17 10-JAN-17 NO NO YES 203 10-JAN-17 10-JAN-17 NO NO YES 204 10-JAN-17 10-JAN-17 NO NO YES 205 10-JAN-17 10-JAN-17 NO NO YES 206 10-JAN-17 10-JAN-17 NO NO YES 207 10-JAN-17 10-JAN-17 NO NO YES SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC APPLIED ---------- --------- --------- --- --- -------- 208 10-JAN-17 10-JAN-17 NO NO YES 209 10-JAN-17 10-JAN-17 NO NO YES 210 10-JAN-17 10-JAN-17 NO NO YES 211 10-JAN-17 10-JAN-17 NO NO YES 212 10-JAN-17 10-JAN-17 NO NO YES 213 10-JAN-17 10-JAN-17 NO NO YES 214 10-JAN-17 10-JAN-17 NO NO YES 215 10-JAN-17 10-JAN-17 NO NO YES 216 10-JAN-17 10-JAN-17 NO NO YES 217 10-JAN-17 10-JAN-17 NO NO YES 218 10-JAN-17 10-JAN-17 NO NO YES SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC APPLIED ---------- --------- --------- --- --- -------- 219 10-JAN-17 10-JAN-17 NO NO YES 220 10-JAN-17 10-JAN-17 NO NO YES 221 10-JAN-17 11-JAN-17 NO NO YES 222 11-JAN-17 11-JAN-17 NO NO YES 324 10-JAN-17 10-JAN-17 YES NO YES 325 10-JAN-17 10-JAN-17 NO YES YES 326 10-JAN-17 10-JAN-17 NO NO YES 327 10-JAN-17 10-JAN-17 NO NO YES 328 10-JAN-17 10-JAN-17 NO NO YES 329 10-JAN-17 10-JAN-17 NO NO YES 330 10-JAN-17 10-JAN-17 NO NO YES SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC APPLIED ---------- --------- --------- --- --- -------- 331 10-JAN-17 10-JAN-17 NO NO YES 332 10-JAN-17 10-JAN-17 NO NO YES 333 10-JAN-17 10-JAN-17 NO NO YES 334 10-JAN-17 10-JAN-17 NO NO YES 335 10-JAN-17 10-JAN-17 NO NO YES 336 10-JAN-17 10-JAN-17 NO NO YES 337 10-JAN-17 10-JAN-17 NO NO YES 338 10-JAN-17 10-JAN-17 NO NO YES 339 10-JAN-17 10-JAN-17 NO NO YES 340 10-JAN-17 10-JAN-17 NO NO YES 341 10-JAN-17 10-JAN-17 NO NO YES SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC APPLIED ---------- --------- --------- --- --- -------- 342 10-JAN-17 10-JAN-17 NO NO YES 343 10-JAN-17 10-JAN-17 NO NO YES 344 10-JAN-17 10-JAN-17 NO NO YES 345 10-JAN-17 10-JAN-17 NO NO YES 346 10-JAN-17 10-JAN-17 NO NO YES 347 10-JAN-17 10-JAN-17 NO NO YES 348 10-JAN-17 10-JAN-17 NO NO YES 349 10-JAN-17 10-JAN-17 NO NO YES 350 10-JAN-17 10-JAN-17 NO NO YES 351 10-JAN-17 10-JAN-17 NO NO YES 352 10-JAN-17 10-JAN-17 NO NO YES SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC APPLIED ---------- --------- --------- --- --- -------- 353 10-JAN-17 10-JAN-17 NO NO YES 354 10-JAN-17 10-JAN-17 NO NO YES 355 10-JAN-17 10-JAN-17 NO NO YES 356 10-JAN-17 10-JAN-17 NO NO YES 357 10-JAN-17 10-JAN-17 NO NO YES 358 10-JAN-17 11-JAN-17 NO NO YES 359 11-JAN-17 11-JAN-17 NO NO YES 73 rows selected. |
Step5:
Check if DML changes in Schema are Applied to Logical Standby.
Perform a deletion on a table of the related schema in Primary and check if it has been deleted from Logical Standby.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[Primary-1] SQL> select * from test.regions_yedek; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas 4 Middle East and Africa SQL> delete test.regions_yedek where region_id=4; 1 row deleted. SQL> commit; Commit complete. SQL> select * from test.regions_yedek; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas |
Step6:
Check a table without DML Filtering
Query the data of a table without DML Filtering from Primary and Logical Standby.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
[Primary-1] SQL> select * from hr.countries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
[Logical-1] SQL> select * from hr.countries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 ZW Zimbabwe 4 25 rows selected. |
Step7:
Check again by deleting from Primary
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
[Primary-1] SQL> delete hr.countries where country_name='Zimbabwe'; 1 row deleted. SQL> commit; Commit complete. SQL> select * from hr.countries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 24 rows selected. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
[Logical-1] SQL> select * from hr.countries; CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- AR Argentina 2 AU Australia 3 BE Belgium 1 BR Brazil 2 CA Canada 2 CH Switzerland 1 CN China 3 DE Germany 1 DK Denmark 1 EG Egypt 4 FR France 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- IL Israel 4 IN India 3 IT Italy 1 JP Japan 3 KW Kuwait 4 ML Malaysia 3 MX Mexico 2 NG Nigeria 4 NL Netherlands 1 SG Singapore 3 UK United Kingdom 1 CO COUNTRY_NAME REGION_ID -- ---------------------------------------- ---------- US United States of America 2 ZM Zambia 4 24 rows selected. |
Step8:
Error and Transaction Filtering in Logical Standby
Apart from DML, Filtering can also be applied for errors and transaction specifics.
|
1 2 3 |
SQL> EXECUTE DBMS_LOGSTDBY.SKIP_ERROR('GRANT'); SQL> EXECUTE DBMS LOGSTDBY SKIP TRANSACTION (XIDUSN => 1, XIDSLT => 13, XIDSQN => 1726); |
XIDUSN: Specifies the Undo Segment Number of Transaction.
XIDSLT: Specifies the Slot Number of Transaction.
XIDSQN: Specifies the Sequence Number of Transaction.
Step9:
Check the existing SQL Apply Filtering
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
SQL> DESC dba_logstdby_skip; Name Null? Type ----------------------------------------- -------- ---------------------------- ERROR VARCHAR2(1) STATEMENT_OPT VARCHAR2(30) OWNER VARCHAR2(30) NAME VARCHAR2(65) USE_LIKE VARCHAR2(1) ESC VARCHAR2(1) PROC VARCHAR2(98) SQL> select max(length(STATEMENT_OPT)) from dba_logstdby_skip; MAX(LENGTH(STATEMENT_OPT)) -------------------------- 15 SQL> column STATEMENT_OPT format a15 SQL> select max(length(OWNER)) from dba_logstdby_skip; MAX(LENGTH(OWNER)) ------------------ 18 SQL> column OWNER format a18 SQL> select max(length(NAME)) from dba_logstdby_skip; MAX(LENGTH(NAME)) ----------------- 1 SQL> column name format a10 SQL> select max(length(PROC)) from dba_logstdby_skip; MAX(LENGTH(PROC)) ----------------- SQL> set linesize 500 SQL> column error format a5 SQL> column proc format a10 SQL> column use_like format a10 SQL> column esc format a5 SQL> select * from dba_logstdby_skip; ERROR STATEMENT_OPT OWNER NAME USE_LIKE ESC PROC ----- --------------- ------------------ ---------- ---------- ----- ---------- N DML TEST % Y N INTERNAL SCHEMA SYSTEM % N N INTERNAL SCHEMA SYS % N N INTERNAL SCHEMA OLAPSYS % N N INTERNAL SCHEMA BI % N N INTERNAL SCHEMA SI_INFORMTN_SCHEMA % N N INTERNAL SCHEMA MGMT_VIEW % N N INTERNAL SCHEMA OWBSYS % N N INTERNAL SCHEMA APEX_030200 % N N INTERNAL SCHEMA XS$NULL % N N INTERNAL SCHEMA ORDPLUGINS % N ERROR STATEMENT_OPT OWNER NAME USE_LIKE ESC PROC ----- --------------- ------------------ ---------- ---------- ----- ---------- N INTERNAL SCHEMA APPQOSSYS % N N INTERNAL SCHEMA ORACLE_OCM % N N INTERNAL SCHEMA XDB % N N INTERNAL SCHEMA SYSMAN % N N INTERNAL SCHEMA WMSYS % N N INTERNAL SCHEMA DBSNMP % N N INTERNAL SCHEMA DIP % N N INTERNAL SCHEMA OUTLN % N N INTERNAL SCHEMA EXFSYS % N N INTERNAL SCHEMA ANONYMOUS % N N INTERNAL SCHEMA CTXSYS % N ERROR STATEMENT_OPT OWNER NAME USE_LIKE ESC PROC ----- --------------- ------------------ ---------- ---------- ----- ---------- N INTERNAL SCHEMA ORDSYS % N N INTERNAL SCHEMA ORDDATA % N N INTERNAL SCHEMA MDSYS % N 25 rows selected. |
Step10:
Delete SQL Apply Filtering
Stop SQL Apply.
|
1 2 3 4 |
[Logical-1] SQL> alter database stop logical standby apply; Database altered. |
Delete SQL Apply Filter.
|
1 2 3 4 |
[Logical-1] SQL> EXECUTE DBMS_LOGSTDBY.UNSKIP(STMT => 'DML', schema_name => 'TEST', object_name => '%'); PL/SQL procedure successfully completed. |
Start SQL Apply.
|
1 2 3 4 |
[Logical-1] SQL> alter database start logical standby apply immediate; Database altered. |
Query the data of the table of the deleted filter from both sides.
|
1 2 3 4 5 6 7 |
[Primary-1] SQL> select * from test.regions_yedek; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe 2 Americas |
|
1 2 3 4 5 6 7 8 |
[Logical-1] SQL> select * from test.regions_yedek; REGION_ID REGION_NAME ---------- ------------------------- 2 Americas 3 Asia 4 Middle East and Africa |
Delete data from the table and check it from the Logical Standby side.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[Primary-1] SQL> delete test.regions_yedek where region_id=2; 1 row deleted. SQL> commit; Commit complete. SQL> select * from test.regions_yedek; REGION_ID REGION_NAME ---------- ------------------------- 1 Europe |
|
1 2 3 4 5 6 7 |
[Logical-1] SQL> select * from test.regions_yedek; REGION_ID REGION_NAME ---------- ------------------------- 3 Asia 4 Middle East and Africa |
