failover切换后恢复原来主库为新备库

我的虚拟机一直开着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/