重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛

标题: 逻辑备库常用命令 [打印本页]

作者: Inkcup 时间: 2026-6-7 17:25
标题: 逻辑备库常用命令
本帖最后由 Inkcup 于 2026-6-14 19:14 编辑

--查看日志应用情况
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');

欢迎光临重庆思庄Oracle、KingBase、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)