Standby Redo Log是Oracle Dataguard的重要元件內容。在筆者看來，Standby Redo Log就是Physical Standby進行資料同步的online redo log。Standby端要想進行同步資料，就必須存在一組或者多組的Standby Redo Log。

根據不同的保護模式（Protection Mode），主庫Primary和備庫Standby維持一種同步關係。這主要體現在一旦網路連線中斷或者應用動作Apply中斷，主庫的事務形式上。那麼，在預設保護模式情況下，如果主庫不斷的將新的redo log傳送給Standby端，standby redo log寫滿或者切換滿之後，Oracle的行為是什麼樣？下面透過實驗來進行驗證。

1、環境說明

筆者使用Oracle 11gR2進行測試，具體版本編號是11.2.0.4。當前Primary和Standby端已經搭建完成，Redo Apply動作正常。

主庫Primary情況如下：

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

-------------------- ----------------

READ WRITE PRIMARY

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

---------- ---------- -------- ----------------

1 37 NO CURRENT

2 35 YES INACTIVE

3 36 YES INACTIVE

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where name='vlifesb';

RECID SEQUENCE# ARCHIVED APPLIED DELETED

---------- ---------- -------- --------- -------

（篇幅原因，有省略……）

20 31 YES YES NO

22 32 YES YES NO

24 33 YES YES NO

26 34 YES YES NO

28 35 YES YES NO

30 36 YES NO NO

15 rows selected

Standby端情況如下：

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

-------------------- ----------------

READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select group#, dbid, sequence#, used, archived, status from v$standby_log;

GROUP# DBID SEQUENCE# USED ARCHIVED STATUS

---------- -------------------- ---------- ---------- -------- ----------

4 4207470439 37 6491648 YES ACTIVE

5 UNASSIGNED 0 0 NO UNASSIGNED

6 UNASSIGNED 0 0 YES UNASSIGNED

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where name is not null;

RECID SEQUENCE# ARCHIVED APPLIED DELETED

---------- ---------- -------- --------- -------

11 32 YES YES NO

12 33 YES YES NO

13 34 YES YES NO

14 35 YES YES NO

15 36 YES IN-MEMORY NO

當前兩者同步開啟狀態，Standby Redo Log當前對應編號是37，與Primary端的Current Redo Log相匹配。

2、中斷監聽傳輸測試

“資料庫當機”是我們經常說到的資料庫故障名詞。但是當機會有不同的故障點和故障方式。如果在Redo Apply的過程中，監聽器發生故障終止服務，系統是什麼方式和現象。

檢視Standby端監聽器情況，關閉監聽器。

[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2015 11:07:58

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

STATUS of the LISTENER

------------------------

（篇幅原因，有省略……）

Service "vlifesb" has 2 instance(s).

Instance "vlifesb", status UNKNOWN, has 1 handler(s) for this service...

Instance "vlifesb", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl stop

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2015 11:08:04

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

The command completed successfully

當終止Standby端監聽程式的時候，主庫立即在alert log中有對應反映。

******************************************

Fatal NI connect error 12541, connecting to:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.19.90)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=vlifesb)(CID=(PROGRAM=oracle)(HOST=vLIFE-URE-OT-DB-PRIMARY)(USER=oracle))))

VERSION INFORMATION:

TNS for Linux: Version 11.2.0.4.0 - Production

TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production

Time: 19-OCT-2015 11:09:05

Tracing not turned on.

Tns error struct:

ns main err code: 12541

TNS-12541: TNS:no listener

ns secondary err code: 12560

nt main err code: 511

TNS-00511: No listener

nt secondary err code: 111

nt OS err code: 0

Error 12541 received logging on to the standby

Check whether the listener is up and running.

PING[ARC2]: Heartbeat failed to connect to standby 'vlifesb'. Error is 12541.

主庫端檢視傳輸通道情況。

SQL> select * from v$archive_dest_status;

DEST_ID DEST_NAME STATUS TYPE DATABASE_MODE RECOVERY_MODE PROTECTION_MODE DESTINATION STANDBY_LOGFILE_COUNT STANDBY_LOGFILE_ACTIVE ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ# ERROR SRL DB_UNIQUE_NAME SYNCHRONIZATION_STATUS SYNCHRONIZED GAP_STATUS

---------- -------------------- --------- -------------- --------------- ----------------------- -------------------- -------------------------------------------------------------------------------- --------------------- ---------------------- ---------------- ------------- --------------- ------------ -------------------------------------------------------------------------------- --- ------------------------------ ---------------------- ------------ ------------------------

1 LOG_ARCHIVE_DEST_1 VALID LOCAL OPEN IDLE MAXIMUM PERFORMANCE /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch 0 0 1 36 0 0 NO NONE CHECK CONFIGURATION NO

2 LOG_ARCHIVE_DEST_2 ERROR PHYSICAL OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE vlifesb 3 0 1 36 1 35 ORA-12541: TNS: ???à?????ò YES vlifesb CHECK CONFIGURATION NO RESOLVABLE GAP

切換一下日誌。

SQL> alter system switch logfile;

System altered

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where name='vlifesb';

RECID SEQUENCE# ARCHIVED APPLIED DELETED

---------- ---------- -------- --------- -------

（篇幅原因，有省略……）

24 33 YES YES NO

26 34 YES YES NO

28 35 YES YES NO

30 36 YES NO NO

32 37 YES NO NO

16 rows selected

新日誌沒有能夠apply，v$log中資訊。

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

---------- ---------- -------- ----------------

1 37 YES ACTIVE

2 38 NO CURRENT

3 36 YES INACTIVE

強行手工checkpoint操作。

SQL> alter system checkpoint;

System altered

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

---------- ---------- -------- ----------------

1 37 YES INACTIVE

2 38 NO CURRENT

3 36 YES INACTIVE

恢復連線之後，可以發現傳輸和應用持續過程。

[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-OCT-2015 11:14:41

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.4.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/vLIFE-URE-OT-DB-STANDBY/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production

Start Date 19-OCT-2015 11:14:41

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/vLIFE-URE-OT-DB-STANDBY/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "vlifesb" has 1 instance(s).

Instance "vlifesb", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

在standby端，可以檢視到持續後追的applied動作。

--Standby端

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where name is not null;

RECID SEQUENCE# ARCHIVED APPLIED DELETED

---------- ---------- -------- --------- -------

11 32 YES YES NO

12 33 YES YES NO

13 34 YES YES NO

14 35 YES YES NO

15 36 YES YES NO

16 37 YES YES NO

17 38 YES IN-MEMORY NO

7 rows selected

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where name='vlifesb';

RECID SEQUENCE# ARCHIVED APPLIED DELETED

---------- ---------- -------- --------- -------

（篇幅原因，有省略……）

28 35 YES YES NO

30 36 YES YES NO

32 37 YES YES NO

34 38 YES NO NO

17 rows selected

3、終止apply過程實驗

如果standby端終止apply過程，後續的redo log不斷傳入到standby redo log中，看現象如何。

Standby端處理，終止應用日誌過程。

--Standby端

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

-------------------- ----------------

READ ONLY WITH APPLY PHYSICAL STANDBY

--終止應用日誌

SQL> alter database recover managed standby database cancel;

Database altered

SQL> select open_mode, database_role from v$database;

OPEN_MODE DATABASE_ROLE

-------------------- ----------------

READ ONLY PHYSICAL STANDBY

此時，standby端日誌上顯示資訊。

Mon Oct 19 11:18:53 2015

alter database recover managed standby database cancel

Mon Oct 19 11:18:53 2015

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/vlifesb/vlifesb/trace/vlifesb_pr00_9008.trc:

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Recovered data files to a consistent state at change 1398760

Mon Oct 19 11:18:53 2015

MRP0: Background Media Recovery process shutdown (vlifesb)

Managed Standby Recovery Canceled (vlifesb)

Completed: alter database recover managed standby database cancel

此時，主庫情況也是進行到39號redo log。

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where name='vlifesb';

RECID SEQUENCE# ARCHIVED APPLIED DELETED

---------- ---------- -------- --------- -------

（篇幅原因，有省略……）

22 32 YES YES NO

24 33 YES YES NO

26 34 YES YES NO

28 35 YES YES NO

30 36 YES YES NO

32 37 YES YES NO

34 38 YES YES NO

17 rows selected

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

---------- ---------- -------- ----------------

1 37 YES INACTIVE

2 38 YES INACTIVE

3 39 NO CURRENT

連續切換主庫日誌。

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

System altered

主庫情況：

SQL> select group#, sequence#, archived, status from v$log;

GROUP# SEQUENCE# ARCHIVED STATUS

---------- ---------- -------- ----------------

1 40 YES INACTIVE

2 41 YES INACTIVE

3 42 NO CURRENT

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log where name='vlifesb';

RECID SEQUENCE# ARCHIVED APPLIED DELETED

---------- ---------- -------- --------- -------

（篇幅原因，有省略…..）

30 36 YES YES NO

32 37 YES YES NO

34 38 YES YES NO

36 39 YES NO NO

38 40 YES NO NO

40 41 YES NO NO

20 rows selected

當前日誌切換到42號，由於網路傳輸是通暢的，所以三個日誌是被成功的傳輸到Standby端，但是沒有被應用。

這個時候，我們需要觀察standby端的standby redo log情況。

（standby情況）

SQL> select group#, dbid, sequence#, used, archived, status from v$standby_log;

GROUP# DBID SEQUENCE# USED ARCHIVED STATUS

---------- -------------------- ---------- ---------- -------- ----------

4 4207470439 42 17920 YES ACTIVE

5 UNASSIGNED 0 0 NO UNASSIGNED

6 UNASSIGNED 0 0 YES UNASSIGNED

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log;

RECID SEQUENCE# ARCHIVED APPLIED DELETED

---------- ---------- -------- --------- -------

（篇幅原因，有省略……）

16 37 YES YES NO

17 38 YES YES NO

18 39 YES NO NO

19 40 YES NO NO

20 41 YES NO NO

20 rows selected

注意：當apply動作沒有進行，但是日誌不斷傳輸的時候，standby redo log中只是儲存最新的當前log，與Primary相匹配。過期的日誌是會作為歸檔儲存在歸檔日誌列表中。

此時alert log中的資訊如下：

Mon Oct 19 11:21:57 2015

Archived Log entry 18 added for thread 1 sequence 39 ID 0xfac9d167 dest 1:

Mon Oct 19 11:21:57 2015

Primary database is in MAXIMUM PERFORMANCE mode

RFS[13]: Assigned to RFS process 15589

RFS[13]: Selected log 4 for thread 1 sequence 40 dbid -87496857 branch 892734889

Mon Oct 19 11:21:58 2015

Archived Log entry 19 added for thread 1 sequence 40 ID 0xfac9d167 dest 1:

Mon Oct 19 11:21:58 2015

Primary database is in MAXIMUM PERFORMANCE mode

RFS[14]: Assigned to RFS process 15591

RFS[14]: Selected log 4 for thread 1 sequence 41 dbid -87496857 branch 892734889

Mon Oct 19 11:22:02 2015

Archived Log entry 20 added for thread 1 sequence 41 ID 0xfac9d167 dest 1:

Mon Oct 19 11:22:02 2015

Primary database is in MAXIMUM PERFORMANCE mode

RFS[15]: Assigned to RFS process 15593

RFS[15]: Selected log 4 for thread 1 sequence 42 dbid -87496857 branch 892734889

注意：這個日誌告訴我們，在standby端，是依次的找可用的standby redo log來使用。如果找到可用的standby redo log，就直接使用好了。

順便討論一下，那麼什麼時候會找不到合適的standby redo log用呢？筆者遇到過檔案不存在，另外如果出現standby端arch程式來不及將日誌寫入歸檔，應該也會寫入到另一組的standby redo log中。

下面如果啟動程式，進行更新。

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered

在日誌中看到應用日誌過程：

Mon Oct 19 11:23:53 2015

alter database recover managed standby database using current logfile disconnect from session

Attempt to start background Managed Standby Recovery process (vlifesb)

Mon Oct 19 11:23:53 2015

MRP0 started with pid=28, OS id=15602

MRP0: Background Managed Standby Recovery process started (vlifesb)

started logmerger process

Mon Oct 19 11:23:58 2015

Managed Standby Recovery starting Real Time Apply

Parallel Media Recovery started with 4 slaves

Waiting for all non-current ORLs to be archived...

All non-current ORLs have been archived.

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_19/o1_mf_1_39_c28rgoqb_.arc

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_19/o1_mf_1_40_c28rgpq1_.arc

Media Recovery Log /u01/app/oracle/fast_recovery_area/VLIFESB/archivelog/2015_10_19/o1_mf_1_41_c28rgtl2_.arc

Media Recovery Waiting for thread 1 sequence 42 (in transit)

Recovery of Online Redo Log: Thread 1 Group 4 Seq 42 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_4_c265gc9q_.log

Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_4_c265gcfk_.log

Completed: alter database recover managed standby database using current logfile disconnect from session

這個過程中，進行standby recovery操作，先從歸檔日誌中找到沒有apply的進行應用。之後應用standby redo log。

此時，apply成功，能夠追上Primary。

SQL> select group#, dbid, sequence#, used, archived, status from v$standby_log;

GROUP# DBID SEQUENCE# USED ARCHIVED STATUS

---------- -------------------- ---------- ---------- -------- ----------

4 4207470439 42 107520 YES ACTIVE

5 UNASSIGNED 0 0 NO UNASSIGNED

6 UNASSIGNED 0 0 YES UNASSIGNED

SQL> select recid, sequence#, ARCHIVED, APPLIED, DELETED from v$archived_log;

RECID SEQUENCE# ARCHIVED APPLIED DELETED

---------- ---------- -------- --------- -------

（篇幅原因，有省略……）

15 36 YES YES NO

16 37 YES YES NO

17 38 YES YES NO

18 39 YES YES NO

19 40 YES YES NO

20 41 YES IN-MEMORY NO

20 rows selected

4、結論

Standby Redo Log是Standby資料庫的online redo log。對於Oracle而言，online redo log和standby redo log都反映了當前最近的一個日誌物件。針對資料庫角色的不同，應用的操作各走兩支。

Oracle Standby Redo Log實驗兩則

相關文章