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;
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
Copyright (c) 1991, 2013, Oracle. All rights reserved.
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
Copyright (c) 1991, 2013, Oracle. All rights reserved.
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;