第一，切換之前的檢查。

確認備庫已經準備好切換

確認備庫與主庫是同步的並且日誌切換正常。

SQL>alter database switchover to <target standby db_unique_name> verify;

以上命令會驗證如下資訊：

a. 驗證資料庫的版本至少為 12.1。

b. 主庫 REDO 傳輸正常。

c. 備庫 MRP 程式正常執行並且與主庫同步，否則會在 SQLPROMPT 和 alert 中都報如下錯誤：

列如：

SQL>alter database switchover to chicago verify;

ORA-16470: Redo Apply is not running on switchover target

------Primary alert log------

SQL>alter database switchover to chicago verify;

ORA-16470 signaled during: alter database switchover to chicago verify...

在目標備庫上檢查 ORL 的狀態

如果是髒的那麼會報出如下資訊：

SQL> alter database switchover to chicago verify;

ERROR at line 1:

ORA-16475: succeeded with warnings, check alert log for more details

-----Primary Alert log-----------

SWITCHOVER VERIFY WARNING: switchover target has dirty online redo logfiles that require clearing.

It takes time to clear online redo logfiles. This may slow down switchover process.

需要做什麼呢？

確認 log_file_name_convert 引數在目標備庫被正確設定：

SQL>show parameter log_file_name_convert;

注意：如果沒有設定，請設定。例如，如果路徑結構只有 db_uniquq_name 發生了改變：

主庫的日誌在： /oradata/boston/<>

備庫的日誌在： /oradata/chicago/logfiles>

這樣設定引數：

alter system set LOG_FILE_NAME_CONVERT=’boston’,’chicago’ scope=spfile;

注意：重啟備庫並且啟動 MRP。當重啟備庫並且啟動 MRP，將清除所有備庫的 log_file_name_convert 引數設定的日誌。

如果備庫是與主庫同步的，將得到如下的資訊：

SQL> alter database switchover to chicago verify;

Database altered.

--------- Primary Alert log -------

SWITCHOVER VERIFY: Send VERIFY request to switchover target CHICAGO

SWITCHOVER VERIFY COMPLETE

Completed: alter database switchover to chicago verify

確認主庫和備庫的臨時資料檔案匹配

臨時資料檔案在建立備庫之後，不會同時建立臨時資料檔案，用如下的命令查詢臨時資料檔案，並且在備庫進行建立。

SQL> col name for a45

SQL> select ts#,name,ts#,status from v$tempfile;

注意：對於多個備庫的環境，確保每個備庫與主庫同步。

Check if log_archive_Dest(remote redo transport) set on Standby side

SWITCHOVER VERIFY command returns ORA-16475 when log_archive_dest_n is not set on Standby as follows.

SQL> ALTER DATABASE SWITCHOVER TO S1202 VERIFY;
ALTER DATABASE SWITCHOVER TO S1202 VERIFY
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

In alert.log, following messages appear.

SWITCHOVER VERIFY: Send VERIFY request to switchover target S1202
SWITCHOVER VERIFY COMPLETE
SWITCHOVER VERIFY WARNING: switchover target has no standby database definedin LOG_ARCHIVE_DEST_n parameter. If the switchover target is converted to
a primary database, the new primary database will not be protected.ORA-16475 signalled during: ALTER DATABASE SWITCHOVER TO S1202 VERIFY...

What to do? set log_archive_dest_state_n on Standby.

如果 v$database 的 switchover_status 的值為 UNRESOLVABLE GAP（RAC 或者非 RAC）

1. 檢查是否有些關閉的執行緒存在並且 disable

SQL>SELECT thread#, instance, status FROM v$thread;

Disable 執行緒使用：

SQL> ALTER DATABASE DISABLE THREAD <n>;

2. 檢查是否有 log_archive_destination 指向不合法的目錄

SQL> select status,DEST_ID,TYPE,ERROR,GAP_STATUS,SYNCHRONIZED,SYNCHRONIZATION_STATUS,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where STatus <> 'INACTIVE';
SQL>show parameter log_Archive_dest

第二，切換角色

在主庫和備庫同時開啟 trace，用於發生問題時候的診斷。

SQL>alter system set log_archive_trace=8191 sid=’*’;

監視各個例項的 alert log（可選）

@primary and standby,

SQL>show parameter background_dump_dest

$tail -600f background_dump_dest/alert*

* 注意: 在 RAC 環境不需要關閉其他的例項，執行 Switchover 的命令會關閉所有的例項。

切換備庫角色為主庫

主庫 - Boston,

SQL>alter database switchover to chicago;

Database altered.

以下是主庫（BOSTON）和備庫（CHICAGO）的 alert 輸出：

--------------Primary Alert log "alert_boston.log"--------------

Fri Aug 23 11:05:23 2013

ALTER SYSTEM SET log_archive_trace=8191 SCOPE=BOTH;

alter database switchover to chicago

Fri Aug 23 11:05:43 2013

Starting switchover [Process ID: 3340]

Fri Aug 23 11:05:43 2013

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 3340] (boston)

.

Fri Aug 23 11:05:44 2013

Active, synchronized Physical Standby switchover target has been identified

Preventing updates and queries at the Primary

Generating and shipping final logs to target standby

Switchover End-Of-Redo Log thread 1 sequence 11 has been fixed <--------- 序列號 11 是日誌的終止序號

Switchover: Primary highest seen SCN set to 0x0.0x229306

ARCH: Noswitch archival of thread 1, sequence 11

.

Switchover: Primary controlfile converted to standby controlfile succesfully.

Switchover: Complete - Database shutdown required

Sending request (convert to primary database) to switchover target CHICAGO

OCISessionBegin with PasswordVerifier succeeded

Switchover complete. Database shutdown required

USER (ospid: 3340): terminating the instance

Fri Aug 23 11:05:51 2013

Instance terminated by USER, pid = 3340

Completed: alter database switchover to chicago

-------------------alert log "alert_chicago.log"------------------

Fri Aug 23 11:05:47 2013

SWITCHOVER: received request 'ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY' from primary database.

Fri Aug 23 11:05:47 2013

ALTER DATABASE SWITCHOVER TO PRIMARY (chicago)

Maximum wait for role transition is 15 minutes.

.

Standby became primary SCN: 2265860

Switchover: Complete - Database mounted as primary

SWITCHOVER: completed request from primary database.

注意：備庫一端只等待15分鐘，實現備庫與主庫的同步。

開啟新的主資料庫

SQL>alter database open;

重啟新的備庫

SQL>shut abort

SQL>startup

SQL>alter database recover managed standby database disconnect;

第三，切換後的後續步驟

關閉 trace

SQL> alter system set log_archive_trace=0;

System altered.

確認新的主庫已經正常傳輸日誌到備庫，並且備庫能正常應用。

在主庫側執行

SQL>alter system switch logfile;

SQL>select dest_id,error,status from v$archive_dest where dest_id=<your remote log_archive_dest_<n>>;

SQL>select max(sequence#),thread# from v$log_history group by thread#;

如果備庫端對應 log_archive_dest_2

SQL>select max(sequence#) from v$archived_log where applied='YES' and
dest_id=2;

在備庫側：

SQL>select thread#,sequence#,process,status from gv$managed_standby;

SQL>select max(sequence#),thread# from v$archived_log group by thread#;

注意：在 12.2 中使用 v$dataguard_process 替代 v$managed_standby

SQL> select name,role,instance,thread#,sequence#,action from gv$dataguard_process;

12c data guard 使用 sqlplus 主備切換最佳實踐 (文件 ID 2403970.1)

12c data guard 使用 sqlplus 主備切換最佳實踐