逻辑备库常用命令

Inkcup

--查看日志应用情况
SELECT SEQUENCE#,to_char(first_time,'yyyy-mm-dd hh24:mm'),to_char(NEXT_TIME,'yyyy-mm-dd hh24:mm'),
DICT_BEGIN,DICT_END
FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

--查看恢复进程是否运行
SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#
FROM V$MANAGED_STANDBY;

--检查重做数据是否被应用
SELECT NAME, VALUE FROM V$LOGSTDBY_STATS
WHERE NAME = 'coordinator state';


-- 停止应用
ALTER DATABASE STOP LOGICAL STANDBY APPLY;

-- 等待10秒
-- 重新启动
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

-- 检查 SQL Apply 状态
SQL> select state from v$logstdby_state;

-- 尝试查看错误事件（19c）
SELECT * FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIME DESC
FETCH FIRST 10 ROWS ONLY;

-- 尝试查看错误事件（11g）
SELECT EVENT_TIME, STATUS, EVENT
FROM DBA_LOGSTDBY_EVENTS
WHERE STATUS_CODE != 0  -- 过滤出错误信息
ORDER BY EVENT_TIME DESC;

--查看 SQL Apply 当前状态
SELECT STATE, REALTIME_APPLY FROM V$LOGSTDBY_STATE;

--查看sql apply的进度
SELECT APPLIED_SCN, LATEST_SCN
FROM V$LOGSTDBY_PROGRESS;

--审计表的跳过
见 碎片笔记 同名条目；

--跳过SYS.JOB$表的同步
见 碎片笔记 同名条目；


--在主库执行（将主库转换为逻辑备库）【WITH SESSION SHUTDOWN 子句不支持逻辑备库的切换操作】
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;


--查看是否接收到任何归档-19c
SELECT FILE_NAME, SEQUENCE#, DICT_BEGIN, DICT_END, STATUS
FROM DBA_LOGSTDBY_LOG
ORDER BY SEQUENCE# DESC
FETCH FIRST 10 ROWS ONLY;

--11g
SELECT SEQUENCE#, DICT_BEGIN, DICT_END, APPLIED
FROM (
    SELECT SEQUENCE#, DICT_BEGIN, DICT_END, APPLIED
    FROM DBA_LOGSTDBY_LOG
    ORDER BY SEQUENCE# DESC
)
WHERE ROWNUM <= 10;

--检查日志传输延迟
-- 在备库执行
SELECT NAME, VALUE, UNIT, TIME_COMPUTED
FROM V$DATAGUARD_STATS
WHERE NAME IN ('apply lag','transport lag');