如何理解primary数据库standby

1.1、查看进程的活动状态

SQL> select process,status,thread#,sequence# from v$managed_standby order by 3,1;

PROCESS STATUS THREAD# SEQUENCE#
——— ———— ———- ———-
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
RFS IDLE 0 0
ARCH CLOSING 1 13411
ARCH CLOSING 1 13412
RFS IDLE 1 13413
ARCH CLOSING 2 8849
ARCH CLOSING 2 4101
MRP0 APPLYING_LOG 2 8850
RFS IDLE 2 8850

这里，process就是进程名，包括ARCH, RFS, MRP0等，对应英文解释如下：
Type of the process whose information is being reported:
RFS – Remote file server—-接收进程
MRP0 – Detached recovery server process—-恢复进程
MR(fg) – Foreground recovery session
ARCH – Archiver process
FGRD
LGWR
RFS(FAL)
RFS(NEXP)
LNS – Network server process

CLIENT_PROCESS 对应 Primary 数据库中的进程如 ARCHLGWR等
SEQUENCE#：归档序号
STATUS 当前进程状态

重要的是status字段，表示当前的进程状态，中文解释如下：
ALLOCATED: 正在准备但还未连接主库
ATTACHED: 正在连接到主库
CONNECTED:已经连接到主库
IDLE:空闲
ERROR：失败的进程，需要关注
RECEIVING:归档日志接收中
OPENING:归档日志处理中
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/arch2_91_787689201.dbf ARCH 1 91 YES YES 04-JUL-12
/u01/app/oracle/oradata/cuuo/arch2_92_787689201.dbf LGWR 1 92 YES YES 04-JUL-12
/u01/app/oracle/oradata/cuuo/arch2_93_787689201.dbf LGWR 1 93 YESYES 04-JUL-12

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

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

1.5 查看物理STANDBY数据库未接收的日志文件
–从primary 数据库获取

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#);

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 Recovery
Informational 0 7 0 NO Managed Standby Recovery not using Real Time Apply