DataGuard 之物理STANDBY 的监控与管理

1、监控恢复进度
1.1、查看进程的活动状态

SELECT PROCESS,CLIENT_PROCESS,SEQUENCE#,STATUS FROM V$MANAGED_STANDBY;

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED

Elapsed: 00:00:00.02

CLIENT_PROCESS 对应 Primary 数据库中的进程如 ARCH\LGWR等
SEQUENCE#：归档序号
STATUS 当前进程状态：
   CONNECTED :已连接至 PRIMARY 数据库
   ALLOCATED: 正在准备连接PRIMARY数据库
   ATTACHED:正在连接PRIMARY数据库
   IDLE：空闲中
   RECEIVING:正在接收归档文件
   OPENNING ：正在处理归档文件
   CLOSING: 归档文件已处理完，收尾中
   WRITING: 正在向归档文件中写入redo数据
   WAIT_FOR_LOG ：正在等待新的REDO数据
   WAIT_FOR_GAP：归档发生中断，正在等待新的REDO 数据
   APPLYING_LOG：正在应用REDO数据

1.2 查看REDO应用进度
SELECT DEST_NAME,ARCHIVED_THREAD#,ARCHIVED_SEQ#,APPLIED_THREAD#,APPLIED_SEQ#,DB_UNIQUE_NAME,STATUS FROM V$ARCHIVE_DEST_STATUS 
 --WHERE STATUS='VALID'

DEST_NAME                 ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# DB_UNIQUE_NAME                 STATUS
------------------------- ---------------- ------------- --------------- ------------ ------------------------------ ---------
LOG_ARCHIVE_DEST_1                       0             0               0            0 cuuo                           VALID
LOG_ARCHIVE_DEST_2                       0             0               0            0 cuug                           VALID
LOG_ARCHIVE_DEST_3                       0             0               0            0 NONE                           INACTIVE
LOG_ARCHIVE_DEST_4                       0             0               0            0 NONE                           INACTIVE
LOG_ARCHIVE_DEST_5                       0             0               0            0 NONE                           INACTIVE
LOG_ARCHIVE_DEST_6                       0             0               0            0 NONE                           INACTIVE
LOG_ARCHIVE_DEST_7                       0             0               0            0 NONE                           INACTIVE
LOG_ARCHIVE_DEST_8                       0             0               0            0 NONE                           INACTIVE
LOG_ARCHIVE_DEST_9                       0             0               0            0 NONE                           INACTIVE
LOG_ARCHIVE_DEST_10                      0             0               0            0 NONE                           INACTIVE
STANDBY_ARCHIVE_DEST                     0             0               0            0 NONE                           VALID

11 rows selected.

1.3 查看归档文件的路径及创建信息
15:24:30 > SELECT NAME,CREATOR,THREAD#,SEQUENCE#,APPLIED,ARCHIVED,COMPLETION_TIME FROM V$ARCHIVED_LOG;

NAME                                     CREATOR    THREAD#  SEQUENCE# APP ARC COMPLETIO
---------------------------------------- ------- ---------- ---------- --- --- ---------
/u01/app/oracle/oradata/cuuo/arch1_91_78 ARCH             1         91 YES YES 04-JUL-12
7689201.dbf

/u01/app/oracle/oradata/cuuo/arch1_92_78 LGWR             1         92 YES YES 04-JUL-12
7689201.dbf

/u01/app/oracle/oradata/cuuo/arch1_93_78 LGWR             1         93 YES YES 04-JUL-12
7689201.dbf

/u01/app/oracle/oradata/cuuo/arch1_94_78 LGWR             1         94 YES YES 04-JUL-12
7689201.dbf

1.4 查看归档历史
SELECT FIRST_TIME,FIRST_CHANGE#,NEXT_CHANGE#,SEQUENCE# FROM V$LOG_HISTORY;

1.5 查看物理STANDBY数据库未接收的日志文件
SELECT LOCAL.THREAD#,LOCAL.SEQUENCE# FROM
   (SELECT THREAD#,SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=1) LOCAL
    WHERE LOCAL.SEQUENCE# NOT IN
 (SELECT SEQUENCE# FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND THREAD# = LOCAL.THREAD#);
----从primary 数据库获取

2 监控日志应用服务
2.1 查询当前数据的基本信息（V$DATABASE) 数据库角色、保护模式、保护级别
SELECT DATABASE_ROLE,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS FROM V$DATABASE;

查询failover后快速启动的信息：
SELECT FS_FAILOVER_STATUS,FS_FAILOVER_CURRENT_TARGET,FS_FAILOVER_THRESHOLD,FS_FAILOVER_OBSERVER_PRESENT FROM V$DATABASE;

2.2 查询REDO应用和REDO传输服务的活动状态
SELECT PROCESS,STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS FROM V$MANAGED_STANDBY;

2.3 查看REDO应用模式（物理STANDBY数据库）
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
RECOVERY_MODE
-----------------------
MANAGED

-----如果开启了实时应用，此处显示的状态应该为 MANAGED REAL TIME APPLY

2.4 DATAGUARD 事件监控
2.4.1 ALERT LOG
2.4.2 查询 V$DATAGUARD_STATUS 视图
16:03:17 > SELECT SEVERITY,DEST_ID,MESSAGE_NUM,ERROR_CODE,CALLOUT,MESSAGE FROM V$DATAGUARD_STATUS;

SEVERITY         DEST_ID MESSAGE_NUM ERROR_CODE CAL MESSAGE
------------- ---------- ----------- ---------- --- ----------------------------------------
Informational          0           1          0 NO  ARC0: Archival started
Informational          0           2          0 NO  ARC1: Archival started
Informational          0           3          0 NO  ARC0: Becoming the 'no FAL' ARCH
Informational          0           4          0 NO  ARC0: Becoming the 'no SRL' ARCH
Informational          0           5          0 NO  ARC1: Becoming the heartbeat ARCH
Control                0           6          0 YES Media Recovery Start: Managed Standby Re
                                                    covery

Informational          0           7          0 NO  Managed Standby Recovery not using Real
                                                    Time Apply

3.调整物理STANDBY端REDO数据应用频率
3.1设置RECOVER并行度
在介质恢复或REDO应用期间都需要读取redo log ，默认都是串行恢复，
可以在RECOVER的时候加上PARALLEL子句来指定并行度。
RECOVER STANDBY DATABASE PARALLEL 2;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 2 DISCONNECT FROM SESSION;

3.2 加快REDO 应用频率
修改 DB_BLOCK_CHECKING=FALSE 能够提高2倍的应用效率，设置为FALSE只适合物理STANDBY数据库，不适合primary数据库。

3.3 设置 parallel_execution_message_size 
如果打开了并行恢复，适当加大parallel_execution_message_size大小也可以提升性能，不过需要注意的事
增加该参数会占用更多的内存。

3.3 优化磁盘I/O
恢复期间最大的性能瓶颈是I/O读写，某些情况下将 DISK_ASYNCH_IO设置为TRUE 即使用本地异步I/O能够降低并行读取的次数，加快整个恢复时间。

             

转载请注明出处及原文链接：

http://blog.csdn.net/xiangsir/article/details/8570386