DG常用sql

检查oracle dg（物理）时你需要用到的sql

1.日志应用

1.1 启用日志应用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

1.2 启用实时的日志应用

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

DISCONNECT FROM SESSION子句并非必需，该子句的作用是指定启动完应用后自动退出到命令操作符前。如果不指定该子句的话，那么当前SESSION就会一直停留处理Redo应用，如果想做其它操作，那么就只能新建一个连接。

1.3 停止日志应用服务

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2 日志应用检查

查看DG备机是否启用了日志应用，有两种办法可以判断：①可以查看是否有mrp进程，如果看不到mrp进程，那么说明没有启用日志应用。②查看V$ARCHIVE_DEST_STATUS的RECOVERY_MODE列。

ps -ef|grep ora_mrp

3 DG总体情况查询

以下的SQL是一个非常有用的SQL语句，可以查询出当前DG的运行和配置的总体情况

SELECT AL.THREAD#,
       ADS.DEST_ID,
       ADS.DEST_NAME,
       (SELECT ADS.TYPE || ' ' || AD.TARGET
          FROM V$ARCHIVE_DEST AD
         WHERE AD.DEST_ID = ADS.DEST_ID) TARGET,
       ADS.DATABASE_MODE,
       ADS.STATUS,
       ADS.ERROR,
       ADS.RECOVERY_MODE,
       ADS.DB_UNIQUE_NAME,
       ADS.DESTINATION,
       ADS.GAP_STATUS,
       (SELECT MAX(SEQUENCE#) FROM V$LOG NA WHERE NA.THREAD# = AL.THREAD#) CURRENT_SEQ#,
       MAX(SEQUENCE#) LAST_ARCHIVED,
       MAX(CASE
             WHEN AL.APPLIED = 'YES' AND ADS.TYPE <> 'LOCAL' THEN
              AL.SEQUENCE#
           END) APPLIED_SEQ#,
       (SELECT AD.APPLIED_SCN
          FROM V$ARCHIVE_DEST AD
         WHERE AD.DEST_ID = ADS.DEST_ID) APPLIED_SCN
  FROM (SELECT *
          FROM V$ARCHIVED_LOG V
         WHERE V.RESETLOGS_CHANGE# =
               (SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)) AL,
       V$ARCHIVE_DEST_STATUS ADS
 WHERE AL.DEST_ID(+) = ADS.DEST_ID
   AND ADS.STATUS != 'INACTIVE'
 GROUP BY AL.THREAD#,
          ADS.DEST_ID,
          ADS.DEST_NAME,
          ADS.STATUS,
          ADS.ERROR,
          ADS.TYPE,
          ADS.DATABASE_MODE,
          ADS.RECOVERY_MODE,
          ADS.DB_UNIQUE_NAME,
          ADS.DESTINATION,
          ADS.GAP_STATUS
 ORDER BY ADS.DEST_ID,AL.THREAD#;

3 查看日志应用情况

SELECT THREAD#, NAME, SEQUENCE#, ARCHIVED, APPLIED, A.NEXT_CHANGE#
  FROM V$ARCHIVED_LOG A
 WHERE A.SEQUENCE# >= (SELECT MAX(B.SEQUENCE#) - 3
                         FROM V$ARCHIVED_LOG B
                        WHERE B.THREAD# = A.THREAD#
                          AND B.RESETLOGS_CHANGE# = A.RESETLOGS_CHANGE#
                          AND B.RESETLOGS_CHANGE# =
                              (SELECT D.RESETLOGS_CHANGE# FROM V$DATABASE D)
                          AND B.APPLIED = 'YES' 
              GROUP BY B.THREAD#)
 ORDER BY A.THREAD#, A.SEQUENCE#;

4 查看备库进程信息

 SELECT A.INST_ID,
        A.PROCESS,
        A.CLIENT_PROCESS,
        A.CLIENT_PID,
        A.STATUS,
        A.GROUP#         GROUP_#,
       A.THREAD#,
       A.SEQUENCE#,
       A.DELAY_MINS,
       A.RESETLOG_ID,
       C.SID,
       C.SERIAL#,
       A.PID            SPID,
       B.PNAME
  FROM GV$MANAGED_STANDBY A, GV$PROCESS B, GV$SESSION C
 WHERE A.PID = B.SPID
   AND B.ADDR = C.PADDR
   AND A.INST_ID = B.INST_ID
   AND B.INST_ID = C.INST_ID
ORDER BY A.INST_ID,B.PNAME;

①　PROCESS：进程名称，如ARCH、RFS、MRP0等

②　CLIENT_PID：在备库查询时对应的Primary数据库中的进程，如ARCH、LGWR等，在主库查询时就是后台进程

③　SEQUENCE#：归档序号

④　STATUS：进程的当前状态，值较多，常见的有：

	 ALLOCATED：正准备连接Primary数据库

	 ATTACHED：正在连接Primary数据库

	 CONNECTED：已连接至Primary数据库

	 IDLE：空闲中

	 RECEIVING：归档文件接收中

	 OPENING：归档文件处理中

	 CLOSING：归档文件处理完，收尾中

	 WRITING：Redo数据库写向归档文件中

	 WAIT_FOR_LOG：等待新的Redo数据中

	 WAIT_FOR_GAP：归档有中断，正等待中断的那部分Redo数据

	APPLYING_LOG：应用Redo数据中

5 手动注册日志

如果有日志文件未被传输或未被注册到备库，那么可以使用如下命令手工注册到备库。下面的SQL语句可以生成物理和逻辑DG注册的SQL语句，日志号从2000到2023：

SELECT 'alter database register or replace logical logfile ''/arch/log_1_' || A ||
       ' _666200636.arc'';' LOGICAL_DG,
       'alter database register or replace  logfile ''/arch/log_1_' || A ||
       ' _666200636.arc'';' PHYSICAL_DG
  FROM (SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <= 2023)
 WHERE A >= 2000;