In today’s article, I will be talking about Data Guard Broker – Disabling and Enabling Apply Services.<\/p>\n
As we know, we perform Redo-Apply operations in Physical and Logical Standby Databases with the following commands.<\/p>\n
[Standby-1] SQL> alter database recover managed standby database cancel;\r\n\r\nDatabase altered.\r\n<\/pre>\n[Logical-1] SQL> alter database stop logical standby apply;\r\n\r\nDatabase altered.\r\n<\/pre>\nWe do these transactions with the broker as follows.<\/p>\n
1. We stop Redo-Apply in Physical Standby Database.<\/p>\n
DGMGRL> edit database standby set state='APPLY-OFF';\r\nSucceeded.\r\n<\/pre>\n[Standby-2]—> ALERT LOG<\/p>\nFri Jan 13 14:07:47 2017\r\nALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL\r\nFri Jan 13 14:07:47 2017\r\nMRP0: Background Media Recovery cancelled with status 16037\r\nErrors in file \/u01\/app\/oracle\/diag\/rdbms\/standby\/primary2\/trace\/primary2_pr00_13441.trc:\r\nORA-16037: user requested cancel of managed recovery operation\r\nFri Jan 13 14:07:47 2017\r\nManaged Standby Recovery not using Real Time Apply\r\nRecovery interrupted!\r\nRecovered data files to a consistent state at change 4464112\r\nFri Jan 13 14:07:48 2017\r\nReconfiguration started (old inc 13, new inc 15)\r\nList of instances:\r\n 1 2 (myinst: 2) \r\n Global Resource Directory frozen\r\n Communication channels reestablished\r\nFri Jan 13 14:07:48 2017\r\n * domain 0 valid = 1 according to instance 1 \r\n Master broadcasted resource hash value bitmaps\r\n Non-local Process blocks cleaned out\r\nFri Jan 13 14:07:48 2017\r\n LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\nFri Jan 13 14:07:48 2017\r\n LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived\r\n Set master node info \r\n Submitted all remote-enqueue requests\r\n Dwn-cvts replayed, VALBLKs dubious\r\n All grantable enqueues granted\r\n Submitted all GCS remote-cache requests\r\n Fix write in gcs resources\r\nReconfiguration complete\r\nFri Jan 13 14:07:49 2017\r\nBlock change tracking service stopping.\r\nStopping background process CTWR\r\nFri Jan 13 14:07:50 2017\r\nMRP0: Background Media Recovery process shutdown (primary2)\r\nManaged Standby Recovery Canceled (primary2)\r\nCompleted: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL\r\n<\/pre>\n[Standby-1]—->ALERT LOG<\/p>\nFri Jan 13 14:07:53 2017\r\nManaged Standby Recovery not using Real Time Apply\r\n<\/pre>\n2. We check whether it is stopped from the broker.<\/p>\n
DGMGRL> show database standby; \r\n\r\nDatabase - standby\r\n\r\n Role: PHYSICAL STANDBY\r\n Intended State: APPLY-OFF\r\n Transport Lag: 0 seconds (computed 1 second ago)\r\n Apply Lag: 8 minutes 38 seconds (computed 1 second ago)\r\n Apply Rate: (unknown)\r\n Real Time Query: OFF\r\n Instance(s):\r\n primary1\r\n primary2 (apply instance)\r\n\r\nDatabase Status:\r\nSUCCESS\r\n<\/pre>\n3. Query all DBs before a table is DROPed to check if Redo-Apply has stopped.<\/p>\n
[Primary-2] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOB_HISTORY_YEDEK\r\nEMPLOYEES_YEDEK\r\n<\/pre>\n[Physical-2] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOB_HISTORY_YEDEK\r\nEMPLOYEES_YEDEK\r\n<\/pre>\n[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\nJOB_HISTORY_YEDEK\r\n<\/pre>\n4. We DROP the table and query it from Standby DB’s.<\/p>\n
[Primary-2] SQL> drop table test.JOB_HISTORY_YEDEK;\r\n\r\nTable dropped.\r\n\r\n[Primary-2] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK<\/pre>\n[Physical-2] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nJOB_HISTORY_YEDEK\r\nEMPLOYEES_YEDEK\r\n<\/pre>\n[Logical-1] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\n<\/pre>\n5. When we see that the table is not deleted, we also check the APPLY LAG.<\/p>\n
[Physical-2] SQL> set linesize 500\r\n[Physical-2] SQL> column name format a10\r\n[Physical-2] SQL> column value format a30\r\n[Physical-2] SQL> select name, value from v$dataguard_stats where name ='apply lag';\r\n\r\nNAME VALUE\r\n---------- ------------------------------\r\napply lag +00 00:07:45\r\n<\/pre>\n6. We start Redo-Apply again.<\/p>\n
DGMGRL> edit database standby set state='APPLY-ON';\r\nSucceeded.\r\n\r\nFri Jan 13 14:17:11 2017\r\nALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE\r\nAttempt to start background Managed Standby Recovery process (primary2)\r\n\r\nDGMGRL> show database standby;\r\n\r\nDatabase - standby\r\n\r\n Role: PHYSICAL STANDBY\r\n Intended State: APPLY-ON\r\n Transport Lag: 0 seconds (computed 0 seconds ago)\r\n Apply Lag: 0 seconds (computed 0 seconds ago)\r\n Apply Rate: 0 Byte\/s\r\n Real Time Query: ON\r\n Instance(s):\r\n primary1\r\n primary2 (apply instance)\r\n\r\nDatabase Status:\r\nSUCCESS\r\n<\/pre>\n7. We check if the table has been deleted.<\/p>\n
[Physical-2] SQL> select table_name from dba_tables where owner='TEST';\r\n\r\nTABLE_NAME\r\n------------------------------\r\nEMPLOYEES_YEDEK\r\n<\/pre>\n<\/p>\n
<\/div>\n