Data Guard Broker 之管理日志应用

在今天的文章中，我们一起学习 Data Guard Broker – 启用和禁用 Redo Apply 服务。

首先，我们需要操作以下命令在物理及逻辑备库中执行 Redo-Apply操作。

[Standby-1] SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
 
Database altered.

[Logical-1] SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
 
Database altered.

[Standby-2]—> ALERT LOG

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

Fri Jan 13 14:07:53 2017 
Managed Standby Recovery not using Real Time Apply

1、在物理备库Broker中停用Redo-Apply功能。

DGMGRL> edit database standby set state='APPLY-OFF'; 
Succeeded.

2、检查broker中是否为关闭状态。

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、查验所有数据库中实验表（JOB_HISTORY_YEDEK）状态，再做删除操作。

[Primary-2] SQL> select table_name from dba_tables where owner='TEST';
 
TABLE_NAME
------------------------------
JOB_HISTORY_YEDEK
EMPLOYEES_YEDEK

[Physical-2] SQL> select table_name from dba_tables where owner='TEST';
 
TABLE_NAME
------------------------------
JOB_HISTORY_YEDEK
EMPLOYEES_YEDEK

[Logical-1] SQL> select table_name from dba_tables where owner='TEST';
 
TABLE_NAME
------------------------------
EMPLOYEES_YEDEK
JOB_HISTORY_YEDEK

4、删除该表并从备库查验。

[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

[Physical-2] SQL> select table_name from dba_tables where owner='TEST';
 
TABLE_NAME
------------------------------
JOB_HISTORY_YEDEK
EMPLOYEES_YEDEK

[Logical-1] SQL> select table_name from dba_tables where owner='TEST';
 
TABLE_NAME
------------------------------
EMPLOYEES_YEDEK

5、我们看到物理备库中的实验表未被删除，并检查“APPLY LAG”。

[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、再次启用 Redo-Apply。

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、可以看到该表已被删除。

[Physical-2] SQL> select table_name from dba_tables where owner='TEST';
 
TABLE_NAME
------------------------------
EMPLOYEES_YEDEK

原文标题：Data Guard Broker – Disabling and Enabling Apply Services
原文作者：Onur ARDAHANLI 
原文地址：https://dbtut.com/index.php/2022/05/04/data-guard-broker-disabling-and-enabling-apply-services/