DG1是主库，DG2是备库；运行在最大可用模式。
实验一：未使用using current logfile参数时，备库在最大可用模式下，不能实时同步备库的更新
-此时需要等待主库进行归档---侧面证明备库应用的归档来恢复数据。
BYS@dg1>show parameter log_archive_dest_2
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_2 string SERVICE=dg2 LGWR SYNC AFFIRM V ALID_FOR=(ONLINE_LOGFILES,PRIM ARY_ROLE) DB_UNIQUE_NAME=dg2主库上的log_archive_dest_2 参数中LGWR SYNC AFFIRM意思是用LGWR传输日志，SYNC是同步传输，ARRIFRM是需要确认。 日志的传输方法有以下 ：使用ARCH–把完整的REDO文件COPY到standby数据库服务器上。使用LGWR，有两种模式：ASYNC - 异步：redo written by LGWR to local disk---LGWR将redo写到本地LOG文件。LNSn进程读取redo，传到standby服务器上。SYNC - 同步：Redo written to standby by LGWR - LGWR讲Redo信息直接写到standby数据库服务器上[oracle@dg2 ~]$ sqlplus / as sysdba BYS@dg2>select protection_mode,database_role,protection_level from v$database; PROTECTION_MODE DATABASE_ROLE PROTECTION_LEVEL -------------------- ---------------- -------------------- MAXIMUM AVAILABILITY PHYSICAL STANDBY MAXIMUM AVAILABILITY SYS@dg2>alter database recover managed standby database disconnect from session; Database altered.1.此时在dg1-主库上进行插入数据操作
BYS@dg1>select * from test; A ---------- 999 123 333 111 BYS@dg1>insert into test values(555); 1 row created. BYS@dg1>commit; Commit complete.
2.返回dg2备库上进行查询操作--验证数据同步情况
我这里未使用ACTIVE STANDBY，如果使用更方便，只需要停止应用并查询就可以了。
SYS@dg2>alter database recover managed standby database cancel; Database altered. SYS@dg2>alter database open; Database altered. SYS@dg2>select open_mode from v$database; OPEN_MODE --------------------READ ONLY SYS@dg2>conn bys/bys Connected.不能查询到DG1上的最新数据。 BYS@dg2>select * from test; A ---------- 999 123 333 111 BYS@dg2>conn / as sysdba SYS@dg2>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@dg2>startup mount;
3.此时切换主库REDO日志，产生的日志大致如下：--日志是后来补上的，日期不 一样，环境 相同
[oracle@dg1 ~]$ cat alert_dg.log Thu Aug 08 10:07:43 2013 LGWR: Standby redo logfile selected to archive thread 1 sequence 44 LGWR: Standby redo logfile selected for thread 1 sequence 44 for destination LOG_ARCHIVE_DEST_2 Thread 1 advanced to log sequence 44 (LGWR switch) Current log# 2 seq# 44 mem# 0: /u01/oradata/dg/redo02.log Thu Aug 08 10:07:43 2013Archived Log entry 65 added for thread 1 sequence 43 ID 0x676c9833 dest 1: -------43号归档已经完成 [oracle@dg2 ~]$ cat alert_dg.log Thu Aug 08 10:07:42 2013 Standby controlfile consistent with primary RFS[1]: Selected log 4 for thread 1 sequence 44 dbid 1735160627 branch 821829622 Thu Aug 08 10:07:42 2013Archived Log entry 29 added for thread 1 sequence 43 ID 0x676c9833 dest 1: -------43号归档已经完成 Thu Aug 08 10:07:46 2013Media Recovery Log /u01/archivelog/arc_1_43_821829622.arc Media Recovery Waiting for thread 1 sequence 44 (in transit)
实验二：使用using current logfile参数，可以实时同步主库的更新。
SYS@dg2>alter database recover managed standby database using current logfile disconnect from session; Database altered.
1.此时在主库上进行DML操作：
BYS@dg1> BYS@dg1>set time on 17:31:36 BYS@dg1>insert into test values(666); 1 row created. 17:31:49 BYS@dg1> 17:31:54 BYS@dg1>commit; Commit complete. 17:31:58 BYS@dg1>
2.在备库上以read-only模式打开数据库并进行查看--要在主库的操作提交后再到备库上操作：
SYS@dg2>set time on 17:31:39 SYS@dg2> 17:32:11 SYS@dg2>alter database recover managed standby database cancel; Database altered. 17:32:40 SYS@dg2>alter database open; Database altered. 17:32:54 SYS@dg2>conn bys/bys Connected. 查询结果显示，备库上已经同步到了主库的最新操作。 17:32:59 BYS@dg2>select * from test; A ---------- 999 123 333 555 666 111 17:33:03 BYS@dg2>
3.此时的日志大致如下，也有日志切换（alter system switch lofile)
11:15:39 SYS@dg1>alter system switch logfile; System altered. 11:15:48 SYS@dg1>exit [oracle@dg1 ~]$ cat alert_dg.log Thu Aug 08 11:15:48 2013 LGWR: Standby redo logfile selected to archive thread 1 sequence 55 LGWR: Standby redo logfile selected for thread 1 sequence 55 for destination LOG_ARCHIVE_DEST_2 Thread 1 advanced to log sequence 55 (LGWR switch) Current log# 1 seq# 55 mem# 0: /u01/oradata/dg/redo01.log Thu Aug 08 11:15:48 2013 Archived Log entry 87 added for thread 1 sequence 54 ID 0x676c9833 dest 1: 54号归档已经完成[oracle@dg2 ~]$ cat alert_dg.log Thu Aug 08 11:15:48 2013 Archived Log entry 40 added for thread 1 sequence 54 ID 0x676c9833 dest 1: 54号归档已经完成 Thu Aug 08 11:15:48 2013 Standby controlfile consistent with primary Thu Aug 08 11:15:48 2013 Media Recovery Waiting for thread 1 sequence 55 (in transit) RFS[12]: Selected log 4 for thread 1 sequence 55 dbid 1735160627 branch 821829622 Recovery of Online Redo Log: Thread 1 Group 4 Seq 55 Reading mem 0 Mem# 0: /u01/oradata/dg/standbyredo04.log Thu Aug 08 11:15:49 2013 RFS[13]: Assigned to RFS process 3881 RFS[13]: Identified database type as 'physical standby': Client is ARCH pid 2929