我的虚拟机一直开着DG库，一直想做完这个failover切换并把原来的主库恢复为新备库的测试，但由于事情太多，
搁了10天才把这个DG的failover切换做了一遍，往后还会有一两篇文章是讲DG切换的，但是后面的使用DGbroker做的切换。
failover切换：
---查看是否有断档：
--主备库都没有：
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
no rows selected
#查看主备库是否有断档的情况，主要看备库。
----主库在mount状态下清空未传输的日志：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 696257656 bytes
Database Buffers 130023424 bytes
Redo Buffers 2392064 bytes
Database mounted.
#也当做是主库挂库的状态。
SQL> ALTER SYSTEM FLUSH REDO TO 'ORA11GR2';----ora11gr2指的是备库
System altered.
#没有任何报错，则说明没有断档的状况。
----备库停止应用主库传输的日志：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
----此时查看主备库的状态：
--主库的状态：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
MOUNTED MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY
----备库的状态：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY MAXIMUM PERFORMANCE TO PRIMARY PHYSICAL STANDBY
----备库侧作切换成主库的操作：
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
----备库（新主库）实例此时的状态：
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
ORA11GR2 MOUNTED
#此时是mount状态。
----打开新的主库：
SQL> alter database open;
Database altered.
#新的主库没有发生报错，正常打开。
----此时查看新主库的角色与状态：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE FAILED DESTINATION PRIMARY
#此时只是作为原来DG的主库作用，不允许做切换。
----查看原来主库此时的状态：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
MOUNTED MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY
#原来的主库依然显示为主库。
----新主库查看切换时的scn号：
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
3214388
----在原来的主库重启并打开闪回：
SQL> SHUTDOWN IMMEDIATE;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 696257656 bytes
Database Buffers 130023424 bytes
Redo Buffers 2392064 bytes
Database mounted.
----原来的主库尝试闪回到SCN 3214388：
SQL> FLASHBACK DATABASE TO SCN 3214388;
FLASHBACK DATABASE TO SCN 3214388
*
ERROR at line 1:
ORA-38726: Flashback database logging is not on.
----查看原来主库的FLASHBACK_ON：
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
NO
---设置为打开模式：
SQL> alter database FLASHBACK on;
Database altered.
---确认闪回是够开启成功：
SQL> select FLASHBACK_ON from v$database;
FLASHBACK_ON
------------------
YES
----闪回原来主库切换前那一刻的状态：
SQL> FLASHBACK DATABASE TO SCN 3214388;
Flashback complete.
#闪回成功。SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
MOUNTED MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY
----把原来主库切换成物理备库角色：
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
#切换成功。
----此时查看切换后的状态与角色：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
select open_mode,protection_mode,switchover_status,database_role from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
SBDB STARTED
SQL> shutdown immediate;
ORA-01507: database not mounted
----重启到mount状态：
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 696257656 bytes
Database Buffers 130023424 bytes
Redo Buffers 2392064 bytes
Database mounted.
SQL>
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
MOUNTED MAXIMUM PERFORMANCE SWITCHOVER LATENT PHYSICAL STANDBY
----查看新主库的（这部分可以先前设置好）：
SQL> col DESTINATION for a35
SQL> SELECT DEST_ID, DEST_NAME, STATUS, PROTECTION_MODE, DESTINATION, ERROR,SRL FROM V$ARCHIVE_DEST_STATUS;
DEST_ID DEST_NAME STATUS PROTECTION_MODE DESTINATION ERROR SRL
---------- ------------------------------ --------- -------------------- ----------------------------------- ---------- ---
1 LOG_ARCHIVE_DEST_1 VALID MAXIMUM PERFORMANCE /u01/app/arch/ORA11GR2/ NO
2 LOG_ARCHIVE_DEST_2 VALID MAXIMUM PERFORMANCE SBDB YES
3 LOG_ARCHIVE_DEST_3 INACTIVE MAXIMUM PERFORMANCE NO
4 LOG_ARCHIVE_DEST_4 INACTIVE MAXIMUM PERFORMANCE NO
---新备主库设置归档传输目录（这部分可以先前设置好）：
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_5=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_6=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_7=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_8=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_9=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_10=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_11=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_12=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_13=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_14=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_14=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_16=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_17=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_18=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_19=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_20=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_21=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_22=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_23=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_24=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_26=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_27=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_28=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_29=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_30=ENABLE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_31=ENABLE;
SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.
----打开新备库：
SQL> alter database open;
Database altered.
----查看新主备库的角色与状态：
--新主库：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE TO STANDBY PRIMARY
--新备库：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY MAXIMUM PERFORMANCE SWITCHOVER LATENT PHYSICAL STANDBY
----新备库引用主库传输的日志：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
----查看新主备库的最大日志号：
SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1
2 and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);
MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T
-------------- -------------------
182 2017-03-18 23:53:23
#主备库的最大日志号一样。
---切换完成。
----再次查看主备库的状态与角色：
---主库：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE SESSIONS ACTIVE PRIMARY
---备库：
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE SWITCHOVER PENDING PHYSICAL STANDBY
--这种切换一般是应急时采用的，比如主库主机宕机了或者数据库挂了，因为这种failover切换备库为新主库，
快速地把被库投入生产当中，但多多少少会丢失部分数据（未传输被备库应用的的数据）。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2135698/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2135698/
2665
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
