Data Guard实时应用(Real-Time Apply)总结
2026-01-12 21:06 潇湘隐者 阅读(39) 评论(0) 收藏 举报实时应用(Real-Time Apply)概述
早期的数据库版本,DG的备库默认只应用归档日志做MRP，备库的数据同步只能在主库发生日志切换时。从Oracle 10g开始,提供了实时应用(Real-Time Apply)特性，有些文章/资料翻译为[实时日志应用]/[实时应用日志].只要主库产生的重作日志直接经备库的RFS进程写入到备库的standby redo log file,备库的MRP进程（即介质恢复进程）会立即直接应用到备库。
官方文档描述:
Real-Time Apply is a new feature in 10g that enables the log apply services to apply redo data (physical standby database) or
SQL (logical standby database) as it is received from Primary database without waiting for the current standby redo log file to
be archived. This results in faster switchover and failover times because the standby redo log files are applied to the standby
database before failover or switchover begins.
实时应用日志的好处
- DG更快速的切换与故障转移操作。
- 物理备库以只读模式打开后，可立即获取实时数据。
- 借助 Active Data Guard(Oracle 11g 新特性)，在逻辑备库和物理备库上实现实时报表生成。
- 支持使用更大的日志文件。结合实时应用功能（Real Time Apply），使用更大的日志文件是更优选择，因为应用服务在单个日志文件上的运行时间会更长，日志切换的开销对实时应用进程的影响也会更小。
检查DG是否实时应用
方法1:
set linesize 255 pagesize 60
col dest_name for a20;
col recovery_mode for a36
select dest_name , status , recovery_mode from v$archive_dest_status;
set linesize 255 pagesize 60
col dest_name for a20;
col recovery_mode for a36
select dest_name , status , recovery_mode from v$archive_dest_status
where dest_id<=2;
如果recovery_mode列显示MANAGED REAL TIME APPLY WITH QUERY(主库)/MANAGED REAL TIME APPLY(备库)则表示实时应用. 如果显示的是MANAGED WITH QUERY/MANAGED则是非实时应用.
注意: 主库和备库都可以执行这个SQL.
主库查询:
SQL> set linesize 255
SQL> col dest_name for a20;
SQL> col recovery_mode for a36
SQL> select dest_name , status , recovery_mode from v$archive_dest_status;
DEST_NAME STATUS RECOVERY_MODE
-------------------- --------- ------------------------------------
LOG_ARCHIVE_DEST_1 VALID IDLE
LOG_ARCHIVE_DEST_2 VALID MANAGED REAL TIME APPLY WITH QUERY
LOG_ARCHIVE_DEST_3 INACTIVE IDLE
..................................................................
SQL>
备库查询:
SQL> set linesize 255
SQL> col dest_name for a20;
SQL> col recovery_mode for a36
SQL> select dest_name , status , recovery_mode from v$archive_dest_status;
DEST_NAME STATUS RECOVERY_MODE
-------------------- --------- ------------------------------------
LOG_ARCHIVE_DEST_1 VALID MANAGED REAL TIME APPLY
LOG_ARCHIVE_DEST_2 INACTIVE IDLE
LOG_ARCHIVE_DEST_3 INACTIVE IDLE
..................................................................
方法2: 查看物理备库日志进程的状态：
set linesize 255
col process for a16
col status for a20
select process, status, thread#, sequence#, block#, blocks from v$managed_standby order by 1;
物理主库执行结果:
SQL> set linesize 255
SQL> col process for a16
SQL> col status for a20
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby order by 1;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------------- -------------------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 409 700416 1117
ARCH CLOSING 1 408 704512 966
ARCH CLOSING 1 406 704512 1429
ARCH CLOSING 1 407 702464 87
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
LNS WRITING 1 410 85917 1
9 rows selected.
SQL>
物理备库执行结果:
SQL> set linesize 255
SQL> col process for a16
SQL> col status for a20
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby order by 1;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------------- -------------------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 409 700416 1117
ARCH CLOSING 1 408 704512 966
ARCH CLOSING 1 404 186368 703
ARCH CLOSING 1 405 704512 2027
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
MRP0 APPLYING_LOG 1 410 86031 819200
RFS IDLE 1 410 86031 1
RFS IDLE 1 0 0 0
9 rows selected.
SQL>
注意: 备库执行此SQL语句.从v$managed_standby这个视图看,备库MRP进程(MRP0)的STATUS是APPLYING_LOG则表示实时应用，并且主库LNS 进程是WRITING则是实时应用;
备库MRP进程STATUS是WAIT_FOR_LOG是非实时应用.(实时应用是用LNS进程发送日志，非实时应用是用LGWR或者ARCH进程发送日志）
WAIT_FOR_LOG:等待日志传输，说明当前MRP进程应用归档文件进行介质恢复.
一般来说,备库如果没有设置SRL或RECOVERY_MODE为则意味着非实时模式.
SRL=NO
RECOVERY_MODE = Managed : It means Redo-Apply only, Not Real Time Aapply.
[备库]启用实时应用
开启实时应用模式的条件
- 启用ADG实时应用的前提时配置了SRL(standby redo log).
- 数据库处于归档模式
官方文档描述:
Real-time apply requires a standby database that is configured with a standby redo log and that is in ARCHIVELOG mode.
LOG_ARCHIVE_DEST_n initialization parameter to delay applying archived redo log files to the standby database.
By default, there is no time delay. If you specify the DELAY attribute without specifying a value, then the default delay interval is 30 minutes.
物理备库(standby)实时应用日志命令：
Oracle 12.1之前的版本:
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
或
SQL> RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Oracle 12.1或之后的版本
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
启用MR前台恢复进程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
逻辑备库(standby)实时应用日志命令：
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
如果standby redo log已经存在(例如,实验过程中实时应用与非实时应用来回切换),从非实时应用切换为实时应用.如下步骤所示(备库):
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active
SQL> set linesize 255
SQL> col process for a16
SQL> col status for a20
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------------- -------------------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 403 524288 1842
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 405 704512 2027
ARCH CLOSING 1 402 700416 1578
ARCH CLOSING 1 404 186368 703
RFS IDLE 1 0 0 0
RFS IDLE 1 406 172049 1881
MRP0 WAIT_FOR_LOG 1 406 0 0
9 rows selected.
--如上所示,MRP进程正在运行,必须先停掉MRP进程,然后执行命令
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
SQL> set linesize 255
SQL> col process for a16
SQL> col status for a20
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------------- -------------------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 403 524288 1842
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
ARCH CLOSING 1 405 704512 2027
ARCH CLOSING 1 402 700416 1578
ARCH CLOSING 1 404 186368 703
RFS IDLE 1 0 0 0
RFS IDLE 1 406 187241 1
MRP0 APPLYING_LOG 1 406 187241 819200
9 rows selected.
如果没有standby log的话,启用实时模式的步骤如下:
- 检查主库是否存在standby log
set linesize 255
col member for a40
select a.group#, a.member, b.bytes FROM v$logfile a, v$standby_log b WHERE a.group# = b.group#;
- 检查归档日志信息
set linesize 255;
set pagesize 200;
col "group#" for 999999
col "thread#" for 9999999
col status for a12
col member for a48
col status for a8
select a.group#
, a.thread#
, a.sequence#
, b.member
, a.blocksize
, a.status
, a.bytes/1024/1024 as size_mb
, a.archived
from v$log a, v$logfile b
where a.group#=b.group#
order by a.group#;
- 新增备用重做日志(standby log)
根据实际情况新增standby log的组数,一般比归档日志多一组.
alter database add standby logfile group 7 'xxx/data/stand_redo01.log' size 200M;
alter database add standby logfile group 8 'xxx/data/stand_redo02.log' size 200M;
alter database add standby logfile group 9 'xxx/data/stand_redo03.log' size 200M;
................................................................................
- 备库启用实时应用
alter database recover managed standby database disconnect from session using current logfile;
- 检查验证实时应用是否生效.
停止实时日志应用
需要停止实时日志应用，按照非实时模式启动MRP，如下操作所示:
备库上执行:
alter database recover managed standby database cancel;
alter database recover managed standby database using archived logfile disconnect from session;
备库上检查结果
SQL> set linesize 255
SQL> col process for a16
SQL> col status for a20
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby order by 1;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------------- -------------------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 409 700416 1117
ARCH CLOSING 1 408 704512 966
ARCH CLOSING 1 404 186368 703
ARCH CLOSING 1 405 704512 2027
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 410 0 0
RFS IDLE 1 410 95010 1
RFS IDLE 1 0 0 0
9 rows selected.
SQL>
SQL>
SQL> set linesize 255 pagesize 60
SQL> col dest_name for a20;
SQL> col recovery_mode for a36
SQL> select dest_name , status , recovery_mode from v$archive_dest_status
2 where dest_id<=2;
DEST_NAME STATUS RECOVERY_MODE
-------------------- -------------------- ------------------------------------
LOG_ARCHIVE_DEST_1 VALID MANAGED
LOG_ARCHIVE_DEST_2 DEFERRED IDLE
SQL>
主库上检查结果
SQL> set linesize 255
SQL> col process for a16
SQL> col status for a20
SQL> select process, status, thread#, sequence#, block#, blocks from v$managed_standby order by 1;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------------- -------------------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 409 700416 1117
ARCH CLOSING 1 408 704512 966
ARCH CLOSING 1 406 704512 1429
ARCH CLOSING 1 407 702464 87
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
DGRD ALLOCATED 0 0 0 0
LNS WRITING 1 410 95261 1
9 rows selected.
SQL>
SQL> set linesize 255 pagesize 60
SQL> col dest_name for a20;
SQL> col recovery_mode for a36
SQL> select dest_name , status , recovery_mode from v$archive_dest_status
2 where dest_id<=2;
DEST_NAME STATUS RECOVERY_MODE
-------------------- -------------------- ------------------------------------
LOG_ARCHIVE_DEST_1 VALID IDLE
LOG_ARCHIVE_DEST_2 VALID MANAGED WITH QUERY
SQL>
物理standby停止实时应用日志：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;
DG 实时应用日志没有生效的原因
- standby redo 的大小必须和redo log的大小一致.否则即使DG启动了实时应用，但是并未真正的实时应用.
- 相关参数设置不正确.
参考资料
- Data Guard Real-Time Apply FAQ (Doc ID 828274.1)
- Data Guard Do Not Real-time Apply To Standby even though SRL are configured (Doc ID 2864452.1)
扫描上面二维码关注我
如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
浙公网安备 33010602011771号