Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will be talking about Data Guard Broker – Disabling and Enabling Apply Services.
As we know, we perform Redo-Apply operations in Physical and Logical Standby Databases with the following commands.
1 2 3 | [Standby-1] SQL> alter database recover managed standby database cancel; Database altered. |
1 2 3 | [Logical-1] SQL> alter database stop logical standby apply; Database altered. |
We do these transactions with the broker as follows.
1. We stop Redo-Apply in Physical Standby Database.
1 2 | DGMGRL> edit database standby set state='APPLY-OFF'; Succeeded. |
[Standby-2]—> ALERT LOG
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 | Fri Jan 13 14:07:47 2017 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL Fri Jan 13 14:07:47 2017 MRP0: Background Media Recovery cancelled with status 16037 Errors in file /u01/app/oracle/diag/rdbms/standby/primary2/trace/primary2_pr00_13441.trc: ORA-16037: user requested cancel of managed recovery operation Fri Jan 13 14:07:47 2017 Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 4464112 Fri Jan 13 14:07:48 2017 Reconfiguration started (old inc 13, new inc 15) List of instances: 1 2 (myinst: 2) Global Resource Directory frozen Communication channels reestablished Fri Jan 13 14:07:48 2017 * domain 0 valid = 1 according to instance 1 Master broadcasted resource hash value bitmaps Non-local Process blocks cleaned out Fri Jan 13 14:07:48 2017 LMS 0: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Fri Jan 13 14:07:48 2017 LMS 1: 0 GCS shadows cancelled, 0 closed, 0 Xw survived Set master node info Submitted all remote-enqueue requests Dwn-cvts replayed, VALBLKs dubious All grantable enqueues granted Submitted all GCS remote-cache requests Fix write in gcs resources Reconfiguration complete Fri Jan 13 14:07:49 2017 Block change tracking service stopping. Stopping background process CTWR Fri Jan 13 14:07:50 2017 MRP0: Background Media Recovery process shutdown (primary2) Managed Standby Recovery Canceled (primary2) Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL |
[Standby-1]—->ALERT LOG
1 2 | Fri Jan 13 14:07:53 2017 Managed Standby Recovery not using Real Time Apply |
2. We check whether it is stopped from the broker.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | DGMGRL> show database standby; Database - standby Role: PHYSICAL STANDBY Intended State: APPLY-OFF Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 8 minutes 38 seconds (computed 1 second ago) Apply Rate: (unknown) Real Time Query: OFF Instance(s): primary1 primary2 (apply instance) Database Status: SUCCESS |
3. Query all DBs before a table is DROPed to check if Redo-Apply has stopped.
1 2 3 4 5 6 | [Primary-2] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOB_HISTORY_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 6 | [Physical-2] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOB_HISTORY_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 6 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK JOB_HISTORY_YEDEK |
4. We DROP the table and query it from Standby DB’s.
1 2 3 4 5 6 7 8 9 | [Primary-2] SQL> drop table test.JOB_HISTORY_YEDEK; Table dropped. [Primary-2] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK |
1 2 3 4 5 6 | [Physical-2] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ JOB_HISTORY_YEDEK EMPLOYEES_YEDEK |
1 2 3 4 5 | [Logical-1] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK |
5. When we see that the table is not deleted, we also check the APPLY LAG.
1 2 3 4 5 6 7 8 | [Physical-2] SQL> set linesize 500 [Physical-2] SQL> column name format a10 [Physical-2] SQL> column value format a30 [Physical-2] SQL> select name, value from v$dataguard_stats where name ='apply lag'; NAME VALUE ---------- ------------------------------ apply lag +00 00:07:45 |
6. We start Redo-Apply again.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | DGMGRL> edit database standby set state='APPLY-ON'; Succeeded. Fri Jan 13 14:17:11 2017 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE THROUGH ALL SWITCHOVER DISCONNECT USING CURRENT LOGFILE Attempt to start background Managed Standby Recovery process (primary2) DGMGRL> show database standby; Database - standby Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 0 seconds ago) Apply Lag: 0 seconds (computed 0 seconds ago) Apply Rate: 0 Byte/s Real Time Query: ON Instance(s): primary1 primary2 (apply instance) Database Status: SUCCESS |
7. We check if the table has been deleted.
1 2 3 4 5 | [Physical-2] SQL> select table_name from dba_tables where owner='TEST'; TABLE_NAME ------------------------------ EMPLOYEES_YEDEK |
