如何理解primary数据库standby

E). 查询备库上gap问题  --v$archived_gap，显示有关备用数据库上存档空缺的信息。 这个视图可以用来找出当前存档的差距，阻碍目前恢复化身的恢复

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 数据库中的进程如 ARCH\LGWR等

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_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