1、VR虚拟现实物理DATAGUARD主备切换对OGGDML同步影响测试物理DATAGUARD主备切换对OGG（DML）同步影响测试一、环境介绍数据库角色DG主库DG备库OGG同步库（查询库）IP10.15.59.10210.14.156.13710.15.34.36操作系统版本AIX 5.3AIX 5.3AIX 5.2Oracle rdbms版本10.2.0.4.010.2.0.4.010.2.0.4.0OGG版本10.2.0.19 build 002None10.2.0.19 build 002xlC.rte版本9.0.0.110.1.0.09.0.0.1Oracle sidoratestor
2、atestoratestDb_unique_nameoratestphyoratestoratest注：如何搭建环境在此文档中不做描述。测试表：test.testtbSQL desc test.testtb Name Null? Type - - - A NUMBER(38)切换前纪录：主库：SQL select * from test.testtb order by a; A- 4 6 7 8 9 10 20 100 200 500 1000 A- 1001 1002 5000 3000015 rows selected.OGG查询库：SQL select * from test.testt
3、b order by a; A- 4 6 7 8 9 10 20 100 200 500 1000 A- 1001 1002 5000 3000015 rows selected.通过表中的数据比较可以看到主库与OGG查询库为同步状态。主库后台日志：Thread 1 advanced to log sequence 48 (LGWR switch) Current log# 2 seq# 48 mem# 0: /oratest/oratest/redo02.logWed Dec 18 13:28:54 2013ARC1: Standby redo logfile selected for th
4、read 1 sequence 47 for destination LOG_ARCHIVE_DEST_2Wed Dec 18 13:40:32 2013Thread 1 advanced to log sequence 49 (LGWR switch) Current log# 3 seq# 49 mem# 0: /oratest/oratest/redo03.logWed Dec 18 13:40:32 2013ARC1: Standby redo logfile selected for thread 1 sequence 48 for destination LOG_ARCHIVE_D
5、EST_2可以看到当前主库归档日志为48号，在线日志为49号（未归档）DG灾备库后台日志：RFS1: Identified database type as physical standbyTue Dec 17 23:27:20 2013RFS LogMiner: Client disabled from further notificationTue Dec 17 23:29:51 2013RFS1: Successfully opened standby log 4: /oratest/oratest/stand01.dbfTue Dec 17 23:29:52 2013Media Rec
6、overy Log /oratest/archive/1_47_834059738.dbfMedia Recovery Waiting for thread 1 sequence 48Tue Dec 17 23:41:30 2013RFS1: Successfully opened standby log 4: /oratest/oratest/stand01.dbfTue Dec 17 23:41:33 2013Media Recovery Log /oratest/archive/1_48_834059738.dbfMedia Recovery Waiting for thread 1 s
7、equence 49可以看到当前灾备库已经Media Recovery的归档日志为48号，等待的下个日志为49号综上，可以判断出主库与灾备库当前为同步状态。二、DG主备切换：主库操作：1. Alter system switch logfile;主库后台日志：Wed Dec 18 13:45:03 2013Thread 1 advanced to log sequence 50 (LGWR switch) Current log# 1 seq# 50 mem# 0: /oratest/oratest/redo01.logWed Dec 18 13:45:03 2013ARC1: Standby
8、 redo logfile selected for thread 1 sequence 49 for destination LOG_ARCHIVE_DEST_2备库后台日志：Tue Dec 17 23:46:01 2013RFS1: Successfully opened standby log 4: /oratest/oratest/stand01.dbfTue Dec 17 23:46:03 2013Media Recovery Log /oratest/archive/1_49_834059738.dbfMedia Recovery Waiting for thread 1 sequ
9、ence 502. 在主库关闭OGG查询库同步进程GGSCI (loopback) 34 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNING EXTRACT RUNNING DP01 00:00:00 00:00:02 EXTRACT RUNNING EXT01 00:00:00 00:00:06 GGSCI (loopback) 35 stop *Sending STOP request to EXTRACT DP01 .Request processed.Sending STOP request to EXTRA
10、CT EXT01 .Request processed.GGSCI (loopback) 36 info allProgram Status Group Lag Time Since ChkptMANAGER RUNNING EXTRACT STOPPED DP01 00:00:00 00:00:03 EXTRACT STOPPED EXT01 00:00:00 00:00:013.查看主库状态并切换主-备主库操作：Sqlselect NAME,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER#,DATAB
11、ASE_ROLE,SWITCHOVER_STATUS from v$database;NAME DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER#- - - - - -DATABASE_ROLE SWITCHOVER_STATUS- -ORATEST oratest READ WRITE MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 3369866885PRIMARY TO STANDBYSqlalter database commit to switchover to p
12、hysical standby;Database altered.主库后台日志：Wed Dec 18 13:48:01 2013alter database commit to switchover to physical standbyWed Dec 18 13:48:01 2013ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY (oratest)Wed Dec 18 13:48:02 2013Thread 1 advanced to log sequence 51 (LGWR switch) Current log# 2 se
13、q# 51 mem# 0: /oratest/oratest/redo02.logWed Dec 18 13:48:02 2013ARC0: Standby redo logfile selected for thread 1 sequence 50 for destination LOG_ARCHIVE_DEST_2Wed Dec 18 13:48:02 2013SMON: disabling tx recoveryWed Dec 18 13:48:02 2013Stopping background process CJQ0Wed Dec 18 13:48:02 2013Stopping
14、background process QMNCWed Dec 18 13:48:04 2013Stopping Job queue slave processes, flags = 27Wed Dec 18 13:48:04 2013Job queue slave processes stoppedWaiting for dispatcher D000 to shutdownAll dispatchers and shared servers shutdownWed Dec 18 13:48:09 2013SMON: disabling cache recoveryWed Dec 18 13:
15、48:09 2013Shutting down archive processesArchiving is disabledWed Dec 18 13:48:14 2013ARCH shutting downARC1: Archival stoppedWed Dec 18 13:48:19 2013ARCH shutting downARC0: Archival stoppedWed Dec 18 13:48:20 2013Thread 1 closed at log sequence 51Successful close of redo thread 1Wed Dec 18 13:48:20
16、 2013ARCH: Noswitch archival of thread 1, sequence 51ARCH: End-Of-Redo Branch archival of thread 1 sequence 51ARCH: Archiving is disabled due to current logfile archivalClearing standby activation ID 3369866885 (0xc8dc1685)The primary database controlfile was created using theMAXLOGFILES 16 clause.T
17、here is space for up to 13 standby redo logfilesUse the following SQL commands on the standby database to createstandby redo logfiles that match the primary database:ALTER DATABASE ADD STANDBY LOGFILE srl1.f SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE srl2.f SIZE 52428800;ALTER DATABASE ADD STA
18、NDBY LOGFILE srl3.f SIZE 52428800;ALTER DATABASE ADD STANDBY LOGFILE srl4.f SIZE 52428800;Archivelog for thread 1 sequence 51 required for standby recoverySwitchover: Primary controlfile converted to standby controlfile succesfully.MRP0 started with pid=14, OS id=1802254Wed Dec 18 13:48:21 2013MRP0:
19、 Background Managed Standby Recovery process started (oratest)Managed Standby Recovery not using Real Time Apply parallel recovery started with 15 processesOnline logfile pre-clearing operation disabled by switchoverMedia Recovery Log /oratest/archive/1_51_834059738.dbfIdentified End-Of-Redo for thr
20、ead 1 sequence 51Wed Dec 18 13:48:26 2013Media Recovery End-Of-Redo indicator encounteredWed Dec 18 13:48:27 2013Media Recovery Applied until change 633877Wed Dec 18 13:48:27 2013MRP0: Media Recovery Complete: End-Of-REDO (oratest)Resetting standby activation ID 3369866885 (0xc8dc1685)Wed Dec 18 13:
21、48:28 2013MRP0: Background Media Recovery process shutdown (oratest)Wed Dec 18 13:48:29 2013Switchover: Complete - Database shutdown required (oratest)Wed Dec 18 13:48:29 2013Completed: alter database commit to switchover to physical standbyWed Dec 18 13:48:29 2013idle dispatcher D000 terminated, pi
22、d = (14, 1)备库后台日志：Tue Dec 17 23:48:59 2013Redo Shipping Client Connected as PUBLIC- Connected User is ValidRFS2: Assigned to RFS process 655484RFS2: Identified database type as physical standbyRFS2: Successfully opened standby log 4: /oratest/oratest/stand01.dbfTue Dec 17 23:49:03 2013Media Recovery
23、 Log /oratest/archive/1_50_834059738.dbfMedia Recovery Waiting for thread 1 sequence 51Tue Dec 17 23:49:18 2013Redo Shipping Client Connected as PUBLIC- Connected User is ValidRFS3: Assigned to RFS process 647280RFS3: Identified database type as physical standbyRFS3: Archived Log: /oratest/archive/1
24、_51_834059738.dbfTue Dec 17 23:49:19 2013Media Recovery Log /oratest/archive/1_51_834059738.dbfIdentified End-Of-Redo for thread 1 sequence 51Tue Dec 17 23:49:22 2013Media Recovery End-Of-Redo indicator encounteredTue Dec 17 23:49:22 2013Media Recovery Applied until change 633877Tue Dec 17 23:49:22
25、2013MRP0: Media Recovery Complete: End-Of-REDO (oratest)Resetting standby activation ID 3369866885 (0xc8dc1685)Tue Dec 17 23:49:25 2013MRP0: Background Media Recovery process shutdown (oratest)4. 启动主库到mount状态，并查看主库状态主库操作：SQL shutdown immediateORA-01507: database not mountedORACLE instance shut down.
26、SQL startup mountORACLE instance started.Total System Global Area 1610612736 bytesFixed Size 2084400 bytesVariable Size 385876432 bytesDatabase Buffers 1207959552 bytesRedo Buffers 14692352 bytesDatabase mounted.SQL select NAME,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER#,DA
27、TABASE_ROLE,SWITCHOVER_STATUS from v$database;NAME DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER#- - - - - -DATABASE_ROLE SWITCHOVER_STATUS- -ORATEST oratest MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 0PHYSICAL STANDBY TO PRIMARY可以看到已经转变为备库5.查看备库状态并切换备-主备库操作：SQL select NAME,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER#,DATABASE_ROLE,SWITCHOVER_STATUS from v$database;NAME DB_UNIQUE_NAME- -OPEN_MODE PROTECTION_MODE- -PROTECTION_LEVEL SWITCHOVER#- -DATABASE_ROLE SWITCHOVER_STATUS- -ORATEST phyoratestMOUNTED MAXIMUM PERFORMANCEMAXIMUM PER
