共計 5341 個字符，預計需要花費 14 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 oracle 12c data guard 如何使用 sqlplus 主備切換，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
第一，切換之前的檢查。確認備庫已經準備好切換
確認備庫與主庫是同步的并且日志切換正常。
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;
以上是“oracle 12c data guard 如何使用 sqlplus 主備切換”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！
