物理DATAGUARD主备切换对OGG(DML)同步影响测试.doc

物理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 sidoratestoratestoratestDb_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.testtb 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 thread 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_DEST_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 Recovery 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 sequence 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 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 sequence 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 EXTRACT 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#,DATABASE_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 physical 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 seq# 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 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: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 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.There 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 STANDBY 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: 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 thread 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: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, pid = (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 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_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 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.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#,DATABASE_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 PERFORMANCE 0PHYSICAL STANDBY TO PRIMARYSQL alter database commit to switchover to primary;Database altered.SQL alter database open;Database altered.原主库后台日志：Wed Dec 18 13:51:55 2013Using STANDBY_ARCHIVE_DEST parameter default value as /oratest/archiveRedo Shipping Client Connected as PUBLIC- Connected User is ValidRFS1: Assigned to RFS process 1888674RFS1: Identified database type as physical standbyWed Dec 18 13:51:55 2013RFS LogMiner: Client disabled from further notification原备库后台日志：Tue Dec 17 23:52:27 2013alter database commit to switchover to primaryTue Dec 17 23:52:27 2013ALTER DATABASE SWITCHOVER TO PRIMARY (oratest)Tue Dec 17 23:52:27 2013If media recovery active, switchover will wait 900 secondsSwitchOver after complete recovery through change 633877Online log /oratest/oratest/redo01.log: Thread 1 Group 1 was previously clearedOnline log /oratest/oratest/redo02.log: Thread 1 Group 2 was previously clearedOnline log /oratest/oratest/redo03.log: Thread 1 Group 3 was previously clearedStandby became primary SCN: 633875Converting standby mount to primary mount.Tue Dec 17 23:52:27 2013Switchover: Complete - Database mounted as primary (oratest)Completed: alter database commit to switchover to primaryTue Dec 17 23:52:27 2013ARC1: STARTING ARCH PROCESSESARC2: Archival startedARC1: STARTING ARCH PROCESSES COMPLETEARC2 started with pid=19, OS id=553370Tue Dec 17 23:52:32 2013alter database openTue Dec 17 23:52:32 2013Assigning activation ID 3369786035 (0xc8dadab3)Thread 1 opened at log sequence 52 Current log# 1 seq# 52 mem# 0: /oratest/oratest/redo01.logSuccessful open of redo thread 1Tue Dec 17 23:52:32 2013MTTR advisory is disabled because FAST_START_MTTR_TARGET is not setTue Dec 17 23:52:32 2013ARC0: Becoming the no SRL ARCHTue Dec 17 23:52:32 2013SMON: enabling cache recoveryTue Dec 17 23:52:32 2013Successfully onlined Undo Tablespace 1.Dictionary check beginningDictionary check completeTue Dec 17 23:52:32 2013SMON: enabling tx recoveryTue Dec 17 23:52:32 2013Database Characterset is WE8ISO8859P1Opening with internal Resource Manager planwhere NUMA PG = 1, CPUs = 16replication_dependency_tracking turned off (no async multimaster replication found)Starting background process QMNCQMNC started with pid=35, OS id=647290Tue Dec 17 23:52:33 2013LOGSTDBY: Validating controlfile with logical metadataTue Dec 17 23:52:33 2013LOGSTDBY: Validation completeCompleted: alter database openTue Dec 17 23:52:47 2013Shutting down archive processesTue Dec 17 23:52:52 2013ARCH shutting downARC2: Archival stopped6. 查看当前原备库状态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 phyoratestREAD WRITE MAXIMUM PERFORMANCEMAXIMUM PERFORMANCE 3369786035PRIMARY TO STANDBY可以看到已经转变为主库7. 在原主库开启DG同步SQL alter database recover managed standby database using current logfile disconnect from session;Database altered.原主库后台日志:- Connected User is ValidRFS1: Assigned to RFS process 1888674RFS1: Identified database type as physical standbyWed Dec 18 13:51:55 2013RFS LogMiner: Client disabled from further notificationWed Dec 18 13:55:42 2013alter database recover managed standby database using current logfile disconnect from sessionWed Dec 18 13:55:42 2013Attempt to start background Managed Standby Recovery process (oratest)MRP0 started with pid=20, OS id=1986742Wed Dec 18 13:55:42 2013MRP0: Background Managed Standby Recovery process started (oratest)Managed Standby Recovery starting Real Time Apply parallel recovery started with 15 processesWed Dec 18 13:55:48 2013Waiting for all non-current ORLs to be archived.Clearing online redo logfile 1 /oratest/oratest/redo01.logClearing online log 1 of thread 1 sequence number 52Wed Dec 18 13:55:48 2013Completed: alter database recover managed standby database using current logfile disconnect from sessionWed Dec 18 13:55:50 2013Clearing online redo logfile 1 completeClearing online redo logfile 2 /oratest/oratest/redo02.logClearing online log 2 of thread 1 sequence number 51Clearing online redo logfile 2 completeClearing online redo logfile 3 /oratest/oratest/redo03.logClearing online log 3 of thread 1 sequence number 49Clearing online redo logfile 3 completeMedia Recovery Waiting for thread 1 sequence 528. 在原备库对表test.testtb进行一系列dml操作，并切换日志备库操作：SQL insert into test.testtb values(500000);1 row created.SQL insert into test.testtb values(600000);1 row created.SQL insert into test.testtb values(700000); 1 row created.SQL update test.testtb set a=1000000 where a=600000;1 row updated.SQL delete from test.testtb where a=500000;1 row deleted.SQL commit;Commit complete.SQL select * from test.testtb order by a; A- 4 6 7 8 9 10 20 100 200 500 1000 A- 1001 1002 5000 30000 700000 1000000SQL alter system switch logfile;System altered.原备库后台日志：Tue Dec 17 23:59:36 2013Thread 1 advanced to log sequence 53 (LGWR switch) Current log# 2 seq# 53 mem# 0: /oratest/oratest/redo02.logTue Dec 17 23:59:36 2013ARC1: Standby redo logfile selected for thread 1 sequence 52 for destination LOG_ARCHIVE_DEST_2可以看到当前已经归档的日志文件为52号。原主库后台日志：Wed Dec 18 13:58:39 2013RFS1: Successfully opened standby log 4: /oratest/oratest/stand01.dbfWed Dec 18 13:58:43 2013Media Recovery Log /oratest/archive/1_52_834059738.dbfMedia Recovery Waiting for thread 1 sequence 53可以看到当前原主库已经Media Recovery到第52号归档文件，正在等待53号归档文件通过后台日志，可以确认当前主备切换后的DG体系，同步正常。三、DG主备再切换（即还原为最初的主备状态）1.查看原备库（即当前的主库）状态并切换备-主原备库操作：SQL select NAME,DB_UNIQUE_NAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER#,DATABASE_ROLE,SWITCHOVE