failover测试文档.docx

Oracle10g rac dataguard failover故障切换测试把dataguard设置为最大可用，dataguard可以同步日志，无问题，开始测试,如果主库有日志未发送到备库，请参考附录,.第一步，主库服务器不用操作，然后在备库执行： alter database recover managed standby database cancel;create restore point dgfail guarantee flashback database;alter database recover managed standby database finish force;alter database commit to switchover to primary;shutdown immediate;startup;结果如下：SQL alter database recover managed standby database cancel;Database altered.SQL create restore point dgfail guarantee flashback database;Restore point created.SQL alter database recover managed standby database finish force;Database altered.SQL alter database commit to switchover to primary;Database altered.SQL shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL startup;ORACLE instance started.Total System Global Area 989855744 bytesFixed Size 2088760 bytesVariable Size 444596424 bytesDatabase Buffers 536870912 bytesRedo Buffers 6299648 bytesDatabase mounted.Database opened.第二步，开始在备库创建表空间和用户：SQL create tablespace testls datafile /opt/app/oracle/oradata/standby/testls.dbf size 50M;Tablespace created.SQL create user ls identified by ls default tablespace testls;User created.SQL grant dba to ls;Grant succeeded.SQL connect ls/lsConnected.SQL create table ls (test int);Table created.SQL insert into ls values(1);1 row created.SQL insert into ls values(2);1 row created.SQL select * from ls; TEST- 1 2备库可以进行ddl和dml操作。第三步，开启主库服务器，并且闪回到点” dgfail”，检测dataguard是否正常工作：（1） 主库能够正常启动，数据库服务正常，如下：oraclerac1 $ crs_stat -tName Type Target State Host-ora.oradb.db application ONLINE ONLINE rac2ora.b1.inst application ONLINE ONLINE rac1ora.b2.inst application ONLINE ONLINE rac2ora.adbs.cs application ONLINE ONLINE rac2ora.db1.srv application ONLINE ONLINE rac1ora.db2.srv application ONLINE ONLINE rac2ora.SM1.asm application ONLINE ONLINE rac1ora.C1.lsnr application ONLINE ONLINE rac1ora.rac1.gsd application ONLINE ONLINE rac1ora.rac1.ons application ONLINE ONLINE rac1ora.rac1.vip application ONLINE ONLINE rac1ora.SM2.asm application ONLINE ONLINE rac2ora.C2.lsnr application ONLINE ONLINE rac2ora.rac2.gsd application ONLINE ONLINE rac2ora.rac2.ons application ONLINE ONLINE rac2ora.rac2.vip application ONLINE ONLINE rac2（2） 在备库执行如下语句：shutdown immediate;startup mount;flashback database to restore point dgfail;alter database convert to physical standby;shutdown immediate;startup open read only;recover managed standby database disconnect using current logfile;结果如下：SQLshutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL startup mount;ORACLE instance started.Total System Global Area 989855744 bytesFixed Size 2088760 bytesVariable Size 444596424 bytesDatabase Buffers 536870912 bytesRedo Buffers 6299648 bytesDatabase mounted.SQLflashback database to restore point dgfail;SQLFlashback complete.SQLSQL alter database convert to physical standby;Database altered.SQL shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL startup open read only;recover managed standby database disconnect using current logfile;ORACLE instance started.Total System Global Area 989855744 bytesFixed Size 2088760 bytesVariable Size 444596424 bytesDatabase Buffers 536870912 bytesRedo Buffers 6299648 bytesDatabase mounted.Database opened.（3） 在主库切换日志，查看备库是否应用SQL alter system switch logfile;System altered.备库查询：SQL select SEQUENCE#,thread#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG; SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 25 1 06-JUN-14 06-JUN-14 YES YES 24 1 05-JUN-14 06-JUN-14 YES YES 8 2 05-JUN-14 06-JUN-14 YES YES 26 1 06-JUN-14 06-JUN-14 YES YES 10 2 06-JUN-14 06-JUN-14 YES YES 27 1 06-JUN-14 06-JUN-14 YES YES 9 2 06-JUN-14 06-JUN-14 YES YES 28 1 06-JUN-14 06-JUN-14 YES YES 29 1 06-JUN-14 06-JUN-14 YES YES 12 2 06-JUN-14 06-JUN-14 YES YES 11 2 06-JUN-14 06-JUN-14 YES YES SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 13 2 06-JUN-14 06-JUN-14 YES YES 30 1 06-JUN-14 06-JUN-14 YES YES 1 1 06-JUN-14 06-JUN-14 NO YES 30 1 06-JUN-14 06-JUN-14 NO YES 13 2 06-JUN-14 06-JUN-14 NO YES 31 1 06-JUN-14 06-JUN-14 NO YES17 rows selected.31号为最新切换日志，等待10分钟后依旧没有被应用。第五步，停止主库数据库，停止备库数据库，重新启动备库数据库，然后重新启动主库数据库，查看日志应用情况。备库执行：SQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL startup mount;ORACLE instance started.Total System Global Area 989855744 bytesFixed Size 2088760 bytesVariable Size 444596424 bytesDatabase Buffers 536870912 bytesRedo Buffers 6299648 bytesDatabase mounted.SQL ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;Database altered.SQL select SEQUENCE#,thread#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG; SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 25 1 06-JUN-14 06-JUN-14 YES YES 24 1 05-JUN-14 06-JUN-14 YES YES 8 2 05-JUN-14 06-JUN-14 YES YES 26 1 06-JUN-14 06-JUN-14 YES YES 10 2 06-JUN-14 06-JUN-14 YES YES 27 1 06-JUN-14 06-JUN-14 YES YES 9 2 06-JUN-14 06-JUN-14 YES YES 28 1 06-JUN-14 06-JUN-14 YES YES 29 1 06-JUN-14 06-JUN-14 YES YES 12 2 06-JUN-14 06-JUN-14 YES YES 11 2 06-JUN-14 06-JUN-14 YES YES SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 13 2 06-JUN-14 06-JUN-14 YES YES 30 1 06-JUN-14 06-JUN-14 YES YES 1 1 06-JUN-14 06-JUN-14 NO YES 30 1 06-JUN-14 06-JUN-14 YES YES 13 2 06-JUN-14 06-JUN-14 YES YES 31 1 06-JUN-14 06-JUN-14 YES YES 14 2 06-JUN-14 06-JUN-14 YES YES 32 1 06-JUN-14 06-JUN-14 YES YES19 rows selected.主库查询：SQL select SEQUENCE#,thread#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = standby order by FIRST_TIME; SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 8 2 05-JUN-14 06-JUN-14 YES YES 24 1 05-JUN-14 06-JUN-14 YES YES 25 1 06-JUN-14 06-JUN-14 YES YES 26 1 06-JUN-14 06-JUN-14 YES YES 9 2 06-JUN-14 06-JUN-14 YES YES 27 1 06-JUN-14 06-JUN-14 YES YES 10 2 06-JUN-14 06-JUN-14 YES YES 11 2 06-JUN-14 06-JUN-14 YES YES 28 1 06-JUN-14 06-JUN-14 YES YES 29 1 06-JUN-14 06-JUN-14 YES YES 12 2 06-JUN-14 06-JUN-14 YES YES SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 30 1 06-JUN-14 06-JUN-14 YES YES 13 2 06-JUN-14 06-JUN-14 YES YES 31 1 06-JUN-14 06-JUN-14 YES YES 14 2 06-JUN-14 06-JUN-14 YES YES 32 1 06-JUN-14 06-JUN-14 YES YES16 rows selected.由上图可知备库的日志开始应用，主库无未应用日志。再次在备库进行操作切换日志，查询日志应用情况：SQL create table test (i int);Table created.SQL alter system switch logfile;System altered.SQL select SEQUENCE#,thread#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = standby order by FIRST_TIME; SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 8 2 05-JUN-14 06-JUN-14 YES YES 24 1 05-JUN-14 06-JUN-14 YES YES 25 1 06-JUN-14 06-JUN-14 YES YES 26 1 06-JUN-14 06-JUN-14 YES YES 9 2 06-JUN-14 06-JUN-14 YES YES 27 1 06-JUN-14 06-JUN-14 YES YES 10 2 06-JUN-14 06-JUN-14 YES YES 11 2 06-JUN-14 06-JUN-14 YES YES 28 1 06-JUN-14 06-JUN-14 YES YES 29 1 06-JUN-14 06-JUN-14 YES YES 12 2 06-JUN-14 06-JUN-14 YES YES SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 30 1 06-JUN-14 06-JUN-14 YES YES 13 2 06-JUN-14 06-JUN-14 YES YES 31 1 06-JUN-14 06-JUN-14 YES YES 14 2 06-JUN-14 06-JUN-14 YES YES 32 1 06-JUN-14 06-JUN-14 YES YES 33 1 06-JUN-14 06-JUN-14 YES YES17 rows selected.Oracle10g rac dataguard switchover主备切换测试第一步，首先确认主库的pfile和备库的pfile是否满足switchover的要就：具体如下：（1），主库要有standby logfile（2），备库参数文件的LOG_ARCHIVE_DEST_1 参数中的 db_unique_name 修改为备库的相应唯一名LOG_ARCHIVE_DEST_2 参数，修改为主库对应的服务名和数据库唯一名。（3），FAL_SERVER 参数修改指向主库的服务名，FAL_CLIENT参数修改指向备库的服务名。（4）如果主备库目录结构相同，则不需要db_file_name_convert和log_file_name_convert，否则需要设置这两个参数来转换主备库目录。确认以上四条正确，然后开始进行switchover第二步，确认主库是否可以切换为备库：select switchover_status from v$database;SQL select switchover_status from v$database;SWITCHOVER_STATUS-SESSIONS ACTIVE如果为SESSIONS ACTIVE则需要关闭所有活动的session，然后重新查询：SQL select switchover_status from v$database;SWITCHOVER_STATUS-TO STANDBY然后把rac数据库关掉，只启动一个节点的数据库，然后执行“alter database commit to switchover to physical standby”把主库切换为物理备库，否则会报错“ORA-01105: mount is incompatible with mounts by other instances”。如果SWITCHOVER_STATUS依旧为session active 则切换语句使用“alter database commit to switchover to physical standby with session shutdown”；SQL alter database commit to switchover to physical standby with session shut down;Database altered.查看备库switchover_status状态：SQL select switchover_status from v$database;SWITCHOVER_STATUS-TO PRIMARY然后执行“alter database commit to switchover to primary”切换备库为主库SQL alter database commit to switchover to primary;Database altered.然后在以前的主库（新备库）启用日志应用：SQLALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;SQLDatabase altered.第三步，主备切换结束后进行验证，在新主库进行插入操作，切换日志，检查备库日志应用情况：SQL alter database set standby database to maximize availability;Database altered.SQLselect SEQUENCE#,thread#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = oradb order by FIRST_TIME;SQL SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 39 1 06-JUN-14 06-JUN-14 YES YES 40 1 06-JUN-14 06-JUN-14 YES YES日志已经被应用，继续进行操作SQL create table mm(i int);Table created.SQL insert into mm values(1);1 row created.SQL commit;Commit complete.SQL create tablespace test datafile /opt/app/oracle/oradata/standby/test.dbf size 100M;Tablespace created.注意：该处建立表空间的文件，如果在旧主库（新备库）的参数文件里没有设置db_file_name_convert和log_file_name_convert参数，则旧主库（新备库）rac创建的表空间文件会与新主库的文件路径相同，切换日志，检查日志应用情况，然后关闭主库，检查备库数据是否同步SQL alter system switch logfile;System altered.SQL select SEQUENCE#,thread#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = oradb order by FIRST_TIME; SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 39 1 06-JUN-14 06-JUN-14 YES YES 40 1 06-JUN-14 06-JUN-14 YES YES 41 1 06-JUN-14 06-JUN-14 YES YES日志已经被应用，关闭主库：SQL shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL备库查询：SQL select * from mm; I- 1SQLSwitchover主备切换成功，关闭备库，在主库执行数据插入和日志切换，然后再打开备库，查看日志应用情况：SQL select SEQUENCE#,thread#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$A RCHIVED_LOG where name = oradb order by FIRST_TIME; SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 39 1 06-JUN-14 06-JUN-14 YES YES 40 1 06-JUN-14 06-JUN-14 YES YES 41 1 06-JUN-14 06-JUN-14 YES YES 42 1 06-JUN-14 06-JUN-14 YES YES 43 1 06-JUN-14 06-JUN-14 YES YES备库打开后，日志应用成功。第四步：再次进行主备切换，步骤如下确认主库是否可以切换为备库：select switchover_status from v$database;SQL select switchover_status from v$database;SWITCHOVER_STATUS-SESSIONS ACTIVE如果为SESSIONS ACTIVE则需要关闭所有活动的session，然后重新查询：SQL select switchover_status from v$database;SWITCHOVER_STATUS-TO STANDBY然后执行“alter database commit to switchover to physical standby”把主库切换为物理备库， 如果SWITCHOVER_STATUS依旧为session active 则切换语句使用“alter database commit to switchover to physical standby with session shutdown”；SQL alter database commit to switchover to physical standby with session shutdo wn;Database altered.查看备库switchover_status状态：SQL select switchover_status from v$database;SWITCHOVER_STATUS-TO PRIMARY然后执行“alter database commit to switchover to primary”切换备库为主库，并且启动rac的所有节点SQL alter database commit to switchover to primary;Database altered.然后在以前的主库（新备库）启用日志应用：SQLALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;SQLDatabase altered.在新的rac主库进行操作首先查询standby日志：SQL select SEQUENCE#,thread#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$A RCHIVED_LOG where name = standby order by FIRST_TIME; SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 8 2 05-JUN-14 06-JUN-14 YES YES 24 1 05-JUN-14 06-JUN-14 YES YES 25 1 06-JUN-14 06-JUN-14 YES YES 26 1 06-JUN-14 06-JUN-14 YES YES 9 2 06-JUN-14 06-JUN-14 YES YES 27 1 06-JUN-14 06-JUN-14 YES YES 10 2 06-JUN-14 06-JUN-14 YES YES 11 2 06-JUN-14 06-JUN-14 YES YES 28 1 06-JUN-14 06-JUN-14 YES YES 29 1 06-JUN-14 06-JUN-14 YES YES 12 2 06-JUN-14 06-JUN-14 YES YES SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 30 1 06-JUN-14 06-JUN-14 YES YES 13 2 06-JUN-14 06-JUN-14 YES YES 31 1 06-JUN-14 06-JUN-14 YES YES 14 2 06-JUN-14 06-JUN-14 YES YES 32 1 06-JUN-14 06-JUN-14 YES YES 15 2 06-JUN-14 06-JUN-14 YES YES 33 1 06-JUN-14 06-JUN-14 YES YES 34 1 06-JUN-14 06-JUN-14 YES YES 35 1 06-JUN-14 06-JUN-14 YES YES 16 2 06-JUN-14 06-JUN-14 YES YES 36 1 06-JUN-14 06-JUN-14 YES YES SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 37 1 06-JUN-14 06-JUN-14 YES YES 38 1 06-JUN-14 06-JUN-14 YES YES 47 1 06-JUN-14 06-JUN-14 YES YES 17 2 06-JUN-14 06-JUN-14 YES YES26 rows selected.然后进行插入和日志切换SQL create table listen (ss number);Table created.SQL insert into listen values(44);1 row created.SQL commit;Commit complete.SQL alter system switch logfile;System altered.然后再次查询standby日志：SQL select SEQUENCE#,thread#, FIRST_TIME, NEXT_TIME, APPLIED, ARCHIVED from V$ARCHIVED_LOG where name = standby order by FIRST_TIME; SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 8 2 05-JUN-14 06-JUN-14 YES YES 24 1 05-JUN-14 06-JUN-14 YES YES 25 1 06-JUN-14 06-JUN-14 YES YES 26 1 06-JUN-14 06-JUN-14 YES YES 9 2 06-JUN-14 06-JUN-14 YES YES 27 1 06-JUN-14 06-JUN-14 YES YES 10 2 06-JUN-14 06-JUN-14 YES YES 11 2 06-JUN-14 06-JUN-14 YES YES 28 1 06-JUN-14 06-JUN-14 YES YES 29 1 06-JUN-14 06-JUN-14 YES YES 12 2 06-JUN-14 06-JUN-14 YES YES SEQUENCE# THREAD# FIRST_TIM NEXT_TIME APP ARC- - - - - - 30 1 06-JUN-14 06-JUN-14 YES YES 13 2 06-JUN-14 06-JUN-14 YES YES 31 1 06-JUN-14 06-JUN-14 YES YES 14 2 06-JUN-14 06-JUN-14 YES YES 32 1 06-JUN-14 06-JUN-14 YES YES 15 2 06-JUN-14 06-JUN-14 YES YES 33 1 06-JUN-14 06-JUN-14 YES YES 34 1 06-JUN-14 06-JUN-14 YES YES 35 1 06-JUN-14 06-JUN-14 YES YES 16 2 06-JUN-14 06-JUN-14 YES YES 36 1 06-JUN-14 06-JUN-14 YES YES SEQUENCE# THREAD# FIRST_TIM NEXT_TI