A-A+
ADG不影响主库下的备库FAILOVER测试
众所周知,当数据库dg搭建完成后,都需要进行switchover和failover测试,其中failover在生产中很难进行测试,下边便是一个在不影响主库的情况下,进行的一个模拟的failover的测试,以便对当前dg的可用性的一个测试。
之前,我在主备库同步的情况下,将主备库中的文件tnsnames.ora中的相关配置注销后,以为主备库会不同步以便进行failover的测试,但是在实际测试中,主备库还是保持同步的,所以,可以推测,数据库已经将这些配置记录进内存或者数据库中了。所以该条路无法进行测试。
下边便是一个在11g版本中,使用备库闪回及ADG特性的一个rac+dg的可行测试方案。
====================================================================================================
设置备库闪回
SQL> select log_mode from v$database; LOG_MODE ------------ ARCHIVELOG SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recovery _area/rac_adg db_recovery_file_dest_size big integer 3852M SQL> show parameter db_flashback_retention_target NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL> select FLASHBACK_ON from v$database; FLASHBACK_ON ------------------ NO SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 630501376 bytes Fixed Size 2230992 bytes Variable Size 264242480 bytes Database Buffers 360710144 bytes Redo Buffers 3317760 bytes Database mounted. SQL> alter database flashback on; Database altered. SQL> alter database open; Database altered. SQL> alter database recover managed standby database disconnect from session; Database altered.
主库2个节点的状态
[grid@11grac1 ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....SM1.asm application ONLINE ONLINE 11grac1 ora....C1.lsnr application ONLINE ONLINE 11grac1 ora....ac1.gsd application OFFLINE OFFLINE ora....ac1.ons application ONLINE ONLINE 11grac1 ora....ac1.vip ora....t1.type ONLINE ONLINE 11grac1 ora....SM2.asm application ONLINE ONLINE 11grac2 ora....C2.lsnr application ONLINE ONLINE 11grac2 ora....ac2.gsd application OFFLINE OFFLINE ora....ac2.ons application ONLINE ONLINE 11grac2 ora....ac2.vip ora....t1.type ONLINE ONLINE 11grac2 ora.DATA.dg ora....up.type ONLINE ONLINE 11grac1 ora....ER.lsnr ora....er.type ONLINE ONLINE 11grac1 ora....N1.lsnr ora....er.type ONLINE ONLINE 11grac2 ora.asm ora.asm.type ONLINE ONLINE 11grac1 ora.cvu ora.cvu.type ONLINE ONLINE 11grac2 ora.gsd ora.gsd.type OFFLINE OFFLINE ora....network ora....rk.type ONLINE ONLINE 11grac1 ora.oc4j ora.oc4j.type ONLINE ONLINE 11grac2 ora.ons ora.ons.type ONLINE ONLINE 11grac1 ora.racdb.db ora....se.type ONLINE ONLINE 11grac1 ora.scan1.vip ora....ip.type ONLINE ONLINE 11grac2
备库状态
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY
确认同步
主库任意节点操作
SQL> sho user USER is "TEST" SQL> select * from yallonking; ID NAME MYDATE ---------- -------------------- --------- 1 yallonking 14-MAY-13 2 oraking 14-MAY-13 3 oraking 14-MAY-13 SQL> insert into yallonking values(4,'cfca',sysdate); 1 row created. SQL> commit; Commit complete. SQL> select * from yallonking; ID NAME MYDATE ---------- -------------------- --------- 4 cfca 20-AUG-13 1 yallonking 14-MAY-13 2 oraking 14-MAY-13 3 oraking 14-MAY-13 SQL> conn /as sysdba Connected. SQL> alter system archive log current; System altered.
备库验证
SQL> conn test/test Connected. SQL> select * from yallonking; ID NAME MYDATE ---------- -------------------- --------- 4 cfca 20-AUG-13 1 yallonking 14-MAY-13 2 oraking 14-MAY-13 3 oraking 14-MAY-13
开始进行failover测试(备库读写打开后并恢复原状继续同步于主库的测试)
在备库操作
SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database recover managed standby database finish; Database altered. SQL> alter database commit to switchover to primary with session shutdown; Database altered. SQL> alter database open; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE
下边在主库任意节点插入测试数据
SQL> select * from test.yallonking; ID NAME MYDATE ---------- -------------------- --------- 4 cfca 20-AUG-13 1 yallonking 14-MAY-13 2 oraking 14-MAY-13 3 oraking 14-MAY-13 SQL> insert into test.yallonking values(5,'yunxingbu',sysdate); 1 row created. SQL> commit; Commit complete. SQL> alter system archive log current; System altered.
在备库查确认已经看不到同步后的数据
SQL> select * from test.yallonking; ID NAME MYDATE ---------- -------------------- --------- 4 cfca 20-AUG-13 1 yallonking 14-MAY-13 2 oraking 14-MAY-13 3 oraking 14-MAY-13
在备库插入测试数据
SQL> insert into test.yallonking values(6,'ceshi',sysdate); 1 row created. SQL> commit; Commit complete. SQL> alter system archive log current; System altered.
在主库查看是否有影响
SQL> select * from test.yallonking; ID NAME MYDATE ---------- -------------------- --------- 4 cfca 20-AUG-13 1 yallonking 14-MAY-13 2 oraking 14-MAY-13 3 oraking 14-MAY-13 5 yunxingbu 20-AUG-13
下边开始恢复备库为之前的状态
SQL> select to_char(standby_became_primary_scn) fallover_scn from v$database; FALLOVER_SCN ---------------------------------------- 1354815 SQL> shutdown abort ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 630501376 bytes Fixed Size 2230992 bytes Variable Size 264242480 bytes Database Buffers 360710144 bytes Redo Buffers 3317760 bytes Database mounted. SQL> flashback database to scn 1354815; Flashback complete. SQL> alter database convert to physical standby; Database altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 630501376 bytes Fixed Size 2230992 bytes Variable Size 264242480 bytes Database Buffers 360710144 bytes Redo Buffers 3317760 bytes Database mounted. Database opened. SQL> alter database recover managed standby database disconnect from session; Database altered.
检查备库数据同步情况
SQL> select * from test.yallonking; ID NAME MYDATE ---------- -------------------- --------- 4 cfca 20-AUG-13 1 yallonking 14-MAY-13 2 oraking 14-MAY-13 3 oraking 14-MAY-13 5 yunxingbu 20-AUG-13
至此全部OK!