實時應用(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)

瀟湘隱者博客

瀟湘隱者博客

博客 / 詳情

Data Guard實時應用(Real-Time Apply)總結

實時應用(Real-Time Apply)概述

檢查DG是否實時應用

方法1:

方法2: 查看物理備庫日誌進程的狀態：

[備庫]啓用實時應用

開啓實時應用模式的條件

物理備庫(standby)實時應用日誌命令：

邏輯備庫(standby)實時應用日誌命令：

停止實時日誌應用

DG 實時應用日誌沒有生效的原因

參考資料

發佈評論

Product

Company

Support

Company

博客 / 詳情

Data Guard實時應用(Real-Time Apply)總結

實時應用(Real-Time Apply)概述

檢查DG是否實時應用

方法1:

方法2: 查看物理備庫日誌進程的狀態：

[備庫]啓用實時應用

開啓實時應用模式的條件

物理備庫(standby)實時應用日誌命令：

邏輯備庫(standby)實時應用日誌命令：

停止實時日誌應用

DG 實時應用日誌沒有生效的原因

參考資料

發佈 評論

發佈評論