1 LOG_ARCHIVE_DEST_1 SYNCHRONOUS 0 NO YES ALL_LOGFILES ALL_ROLES NONE
2 LOG_ARCHIVE_DEST_2 ASYNCHRONOUS 61440 NO WRONG VALID_TYPE ONLINE_LOGFILE PRIMARY_ROLE vlife
32 STANDBY_ARCHIVE_DEST SYNCHRONOUS 0 NO YES ALL_LOGFILES ALL_ROLES NONE
SQL> show parameter LOG_ARCHIVE_DEST_2;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=vlife valid_for=(online_logfiles,primary_role) db_unique_name=vlife
log_archive_dest_20 string
log_archive_dest_21 string
3、最大可用模式Maximum Availability Mode测试
当前从默认的最大性能切换到最大可用模式,首先需要满足将日志传输模式进行修改。
SQL> alter system set log_archive_dest_2='SERVICE=vlifesb sync affirm net_timeout=30 valid_for=(online_logfiles,primary_role) db_unique_name=vlifesb';
System altered
SQL> select dest_id, dest_name, TRANSMIT_MODE, ASYNC_BLOCKS, AFFIRM TYPE, VALID_NOW, VALID_TYPE, VALID_ROLE, DB_UNIQUE_NAME, NET_TIMEOUT from v$archive_dest where status<>'INACTIVE';
DEST_ID DEST_NAME TRANSMIT_MODE ASYNC_BLOCKS TYPE VALID_NOW VALID_TYPE VALID_ROLE DB_UNIQUE_NAME NET_TIMEOUT
---------- -------------------- ------------- ------------ ---- ---------------- --------------- ------------ ------------------------------ -----------
1 LOG_ARCHIVE_DEST_1 SYNCHRONOUS 0 NO YES ALL_LOGFILES ALL_ROLES NONE 0
2 LOG_ARCHIVE_DEST_2 PARALLELSYNC 0 YES YES ONLINE_LOGFILE PRIMARY_ROLE vlifesb 30
此时,将保护模式使用alter database进行设置。
SQL> alter database set standby database to maximize availability;
Database altered
SQL> select name, open_mode, database_role, protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
VLIFE READ WRITE PRIMARY MAXIMUM AVAILABILITY
在切换动作的时候,主库日志情况如下:
Wed Oct 21 15:13:48 2015
alter database set standby database to maximize availability
Completed: alter database set standby database to maximize availability
Wed Oct 21 15:13:49 2015
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED –发现没有同步,需要补充。
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Oct 21 15:13:49 2015
NSS2 started with pid=34, OS id=9186
LGWR: Standby redo logfile selected to archive thread 1 sequence 82
LGWR: Standby redo logfile selected for thread 1 sequence 82 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 82 (LGWR switch)
Current log# 3 seq# 82 mem# 0: /u01/app/oracle/oradata/VLIFE/onlinelog/o1_mf_3_c1kb1c24_.log
Current log# 3 seq# 82 mem# 1: /u01/app/oracle/fast_recovery_area/VLIFE/onlinelog/o1_mf_3_c1kb1c43_.log
Wed Oct 21 15:13:53 2015
Archived Log entry 104 added for thread 1 sequence 81 ID 0xfad4f44b dest 1:
Wed Oct 21 15:13:54 2015
ARC3: Archive log rejected (thread 1 sequence 81) at host 'vlifesb'
FAL[server, ARC3]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance vlife - Archival Error. Archiver continuing.
Wed Oct 21 15:14:42 2015
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected to archive thread 1 sequence 83
LGWR: Standby redo logfile selected for thread 1 sequence 83 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 83 (LGWR switch)
Current log# 1 seq# 83 mem# 0: /u01/app/oracle/oradata/VLIFE/onlinelog/o1_mf_1_c1kb19q4_.log
Current log# 1 seq# 83 mem# 1: /u01/app/oracle/fast_recovery_area/VLIFE/onlinelog/o1_mf_1_c1kb19sb_.log
Wed Oct 21 15:14:42 2015
Archived Log entry 107 added for thread 1 sequence 82 ID 0xfad4f44b dest 1:
在Primary端,在进行切换之后,Oracle发现传输状态不是同步情况。于是自动加快进行日志传输和同步动作。在Standby端,也可以看到后续追赶动作。
SQL> select name, open_mode, database_role, protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
VLIFE READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM AVAILABILITY
Standby端上的日志追赶动作。
Wed Oct 21 08:27:05 2015
Primary database is in MAXIMUM PERFORMANCE mode
Re-archiving standby log 4 thread 1 sequence 80
Wed Oct 21 08:27:05 2015
Media Recovery Waiting for thread 1 sequence 81
RFS[14]: Assigned to RFS process 31500
RFS[14]: Selected log 5 for thread 1 sequence 81 dbid -87496857 branch 892734889
Wed Oct 21 08:27:05 2015
Archived Log entry 76 added for thread 1 sequence 80 ID 0xfad4f44b dest 1:
Recovery of Online Redo Log: Thread 1 Group 5 Seq 81 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/VLIFESB/onlinelog/o1_mf_5_c265gqd8_.log
Mem# 1: /u01/app/oracle/fast_recovery_area/VLIFESB/onlinelog/o1_mf_5_c265gqj0_.log
Wed Oct 21 15:13:52 2015
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY mode
Changing standby controlfile to RESYNCHRONIZATION level
Standby controlfile consistent with primary
RFS[15]: Assigned to RFS process 969
RFS[15]: Selected log 4 for thread 1 sequence 82 dbid -87496857 branch 892734889
Wed Oct 21 15:13:53 2015
Archived Log entry 77 added for thread 1 sequence 81 ID 0xfad4f44b dest 1:
Wed Oct 21 15:13:53 2015
Media Recovery Waiting for thread 1 sequence 82 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 82 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
Wed Oct 21 15:14:41 2015
Archived Log entry 78 added for thread 1 sequence 82 ID 0xfad4f44b dest 1:
Wed Oct 21 15:14:41 2015
Media Recovery Waiting for thread 1 sequence 83
Wed Oct 21 15:14:42 2015
Primary database is in MAXIMUM AVAILABILITY mode
Changing standby controlfile to MAXIMUM AVAILABILITY level
Standby controlfile consistent with primary
RFS[16]: Assigned to RFS process 976
RFS[16]: Selected log 4 for thread 1 sequence 83 dbid -87496857 branch 892734889
Recovery of Online Redo Log: Thread 1 Group 4 Seq 83 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
此时,两个库由于网络通畅,同步状态正常,同步测试正常。
(Maximium Availiablity模式下使用)
--主库下
SQL> create table t_m as select * from dba_objects where rownum<10;
Table created
--Standby下
SQL> select count(*) from t_m;
COUNT(*)
----------
9
如果此时中断应用日志,Standby情况如下:
SQL> alter database recover managed standby database cancel;
Database altered
SQL> select name, open_mode, database_role, protection_mode from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE
--------- -------------------- ---------------- --------------------
VLIFE READ ONLY PHYSICAL STANDBY MAXIMUM AVAILABILITY
日志情况如下:
Wed Oct 21 15:20:49 2015
alter database recover managed standby database cancel
Wed Oct 21 15:20:49 2015
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/vlifesb/vlifesb/trace/vlifesb_pr00_17539.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 1692263
Wed Oct 21 15:20:49 2015
MRP0: Background Media Recovery process shutdown (vlifesb)
Managed Standby Recovery Canceled (vlifesb)
Completed: alter database recover managed standby database cancel
如果此时再中断监听器,中断连接。此时数据库不能做到实时同步。
[oracle@vLIFE-URE-OT-DB-STANDBY ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 21-OCT-2015 15:24:17
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
--主库
***********************************************************************
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: 21-OCT-2015 15:24:38
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> alter system switch logfile;
System altered
SQL> alter system switch logfile;
System altered
SQL> alter system switch logfile;
System altered
SQL> select * from v$archive_dest_status;