oracle dataguard 常用脚本

--查看恢复和错误信息

SELECT * FROM V$DATAGUARD_STATUS ORDER BY TIMESTAMP desc

--备库查看RFS接收日志和MRP应用日志同步主库情况
select process, sequence#, status, delay_mins from v$managed_standby;

--查询剩余多少日志没有应用

WITH a AS (
 SELECT PROCESS, max(SEQUENCE#) s
 FROM  v$managed_standby WHERE PROCESS IN ('ARCH','MRP0') GROUP BY PROCESS
)
,b AS (

SELECT 
    CASE 
              WHEN PROCESS='ARCH' THEN  
                s 
              WHEN PROCESS='MRP0' THEN 
                -s
    END s

FROM a
)

SELECT sum(s) 剩余数量 ,SYSDATE now FROM b

--查看日志接收状态
select REGISTRAR,CREATOR,THREAD#,APPLIED,sequence#,first_change#,next_change#, COMPLETION_TIME from v$archived_log ORDER BY COMPLETION_TIME desc;

--备库查看已经应用的redo
select thread#,sequence# ,first_change#,next_change# from v$log_history ORDER BY sequence# desc;

-- 主、备库查看是否有gap
select status,gap_status from v$archive_dest_status where dest_id=2;
--备库查看
select * from v$archive_gap;

-- 切换日志
alter system switch logfile;

-- 查看备库 接收的最大日志

select sequence# from v$archived_log where recid = (select max(recid) from v$archived_log);
    
-- 查看主库的日志

select sequence#,status from v$log;

-----------------------------
alter database recover managed standby database cancel;
-- 每次断电重启后需要执行
alter database recover managed standby database using current logfile disconnect from session;

--检查是否正在应用日志
select thread#,sequence# ,first_change#,next_change# from v$log_history ORDER BY sequence# desc;