使用Oracle DG Broker做Switchover是非常簡單的一件事情,但是流程必須規範,事前做足檢查工作.避免沒有檢查到位,做Switchover時出現異常.
切換前檢查
DGMGRL>show configuration;
DGMGRL>show database <primary_db_name>;
DGMGRL>show database <standby_db_name>;
DGMGRL>show database verbose <primary_db_name>;
DGMGRL>show database verbose <standby_db_name>;
DGMGRL>show database <db_name> logxptstatus;
DGMGRL>show database <db_name> 'inconsistentlogxptprops';
注意: 用具體的主庫名和備庫名替換上面的<primary_db_name>和<standby_db_name>或<db_name>
主要檢查下面這些內容:
- DG中所有數據庫都處於正常狀態.無任何錯誤或警告信息。
- 主數據庫上已配置備用重做日誌文件。
- 主數據庫與備用數據庫的狀態分別為日誌傳輸開啓（TRANSPORT-ON）和日誌應用開啓（APPLY-ON）。
DGMGRL> validate database gsp;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
gsp: Off
Managed by Clusterware:
gsp: NO
Validating static connect identifier for the primary database gsp...
The static connect identifier allows for a connection to database "gsp".
DGMGRL> validate database gspro
Database Role: Physical standby database
Primary Database: gsp
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
gsp : Off
gspro: Off
Managed by Clusterware:
gsp : NO
gspro: NO
Validating static connect identifier for the primary database gsp...
The static connect identifier allows for a connection to database "gsp".
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(gsp) (gspro)
1 7 3 Insufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(gspro) (gsp)
1 7 3 Insufficient SRLs
DGMGRL>
主庫信息:
Ready for Switchover: Yes 表示可以準備切換(Switchover)
備庫信息:
Ready for Switchover: Yes 表示切換操作已準備就緒
如果需要進一步診斷或troubleshoot,可以使用下面命令查看更多詳細信息
validate database verbose gsp;
validate database verbose gspro;
例子:
DGMGRL> validate database verbose gsp;
Database Role: Primary database
Ready for Switchover: Yes
Flashback Database Status:
gsp: On
Capacity Information:
Database Instances Threads
gsp 1 1
Managed by Clusterware:
gsp: NO
Validating static connect identifier for the primary database gsp...
The static connect identifier allows for a connection to database "gsp".
Temporary Tablespace File Information:
gsp TEMP Files: 3
Data file Online Move in Progress:
gsp: No
Transport-Related Information:
Transport On: Yes
Log Files Cleared:
gsp Standby Redo Log Files: Cleared
DGMGRL> validate database verbose gspro;
Database Role: Physical standby database
Primary Database: gsp
Ready for Switchover: Yes
Ready for Failover: Yes (Primary Running)
Flashback Database Status:
gsp : On
gspro: Off
Capacity Information:
Database Instances Threads
gsp 1 1
gspro 1 1
Managed by Clusterware:
gsp : NO
gspro: NO
Validating static connect identifier for the primary database gsp...
The static connect identifier allows for a connection to database "gsp".
Temporary Tablespace File Information:
gsp TEMP Files: 3
gspro TEMP Files: 3
Data file Online Move in Progress:
gsp: No
gspro: No
Standby Apply-Related Information:
Apply State: Running
Apply Lag: 0 seconds (computed 1 second ago)
Apply Delay: 0 minutes
Transport-Related Information:
Transport On: Yes
Gap Status: No Gap
Transport Lag: 0 seconds (computed 1 second ago)
Transport Status: Success
Log Files Cleared:
gsp Standby Redo Log Files: Cleared
gspro Online Redo Log Files: Cleared
gspro Standby Redo Log Files: Available
Current Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(gsp) (gspro)
1 3 4 Sufficient SRLs
Future Log File Groups Configuration:
Thread # Online Redo Log Groups Standby Redo Log Groups Status
(gspro) (gsp)
1 3 4 Sufficient SRLs
Current Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(gsp) (gspro)
1 200 MBytes 200 MBytes
Future Configuration Log File Sizes:
Thread # Smallest Online Redo Smallest Standby Redo
Log File Size Log File Size
(gspro) (gsp)
1 200 MBytes 200 MBytes
Apply-Related Property Settings:
Property gsp Value gspro Value
DelayMins 0 0
ApplyParallel AUTO AUTO
ApplyInstances 0 0
Transport-Related Property Settings:
Property gsp Value gspro Value
LogShipping ON ON
LogXptMode ASYNC ASYNC
Dependency <empty> <empty>
DelayMins 0 0
Binding optional OPTIONAL
MaxFailure 0 0
ReopenSecs 300 300
NetTimeout 30 30
RedoCompression DISABLE DISABLE
DGMGRL>
切換步驟
主庫切換(Switchover)
switchover to <standby_db>
DGMGRL> switchover to gspro
Performing switchover NOW, please wait...
Operation requires a connection to database "gspro"
Connecting ...
Connected to "gspro"
Connected as SYSDBA.
New primary database "gspro" is opening...
Operation requires start up of instance "gsp" on database "gsp"
Starting instance "gsp"...
Connected to an idle instance.
ORACLE instance started.
Connected to "gsp"
Database mounted.
Database opened.
Switchover succeeded, new primary is "gspro"
DGMGRL>
注意: 在切換過程,最好用下面命令實時觀察主庫&備庫的錯誤日誌與dg broker的日誌輸出信息.
主庫:
$ tail -60f drcgsp.log
$ tail -60f alert_gsp.log
備庫:
$ tail -60f alert_gsp.log
$ tail -60f drcgsp.log
注意事項:
如果切換的目標是物理standby，那麼Brocker 在切換過程中會關閉主庫。
如果切換環境是MAX_PROTECTION模式並且主庫只有一個備庫，Brocker在切換過程中將同時關閉主備庫；如果主庫有多個備庫，那麼Brocker僅關閉新主庫。
檢查驗證
DGMGRL> show configuration;
Configuration - dg_gsp
Protection Mode: MaxPerformance
Members:
gspro - Primary database
gsp - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 67 seconds ago)
DGMGRL>
疑問問題
- DG Broker做switchover會改變DG的保護模式嗎?
DG Broker will not downgrade the PROTECTION mode during the process of Switchover.
Configure suitable REDO transport mode on current standby that becomes Primary after switchover.
Configure SRL (Standby Redo Logs) and local archiving destination on current Primary.
Broker switchover command verifies the availability of SRL on current Primary Database and suitable Redo transport method on current Standby.
DG Broker在主備切換(Switchover)過程中不會降級數據庫的保護模式。
在當前備庫(切換後的主庫)上配置合適的重做日誌傳輸模式(redo transport mode)
在當前主庫上配置備庫重做日誌(SRL, Standby Redo Logs)與本地歸檔目標位置
Broker 主備切換命令會校驗當前主庫上備用重做日誌（SRL）的可用性，以及當前備庫上適配的重做日誌傳輸方式。
- 配置了DG Broker後,還能使用SQL手工切換嗎? 手工切換會破壞DG Broker嗎?
測試驗證如下:
SQL> ALTER DATABASE SWITCHOVER TO gspro VERIFY;
ALTER DATABASE SWITCHOVER TO gspro VERIFY
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details
檢查告警日誌如下所示:
SWITCHOVER VERIFY: Send VERIFY request to switchover target GSPRO
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in 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 gspro VERIFY...
檢查備庫的log_archive_dest_2,發現備庫上參數log_archive_dest_1/log_archive_dest_2都為空
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
SQL>
此時需要修改相關參數才能滿足DG切換條件,但是可能會破壞DG Broker的數據. 所以結論是不建議手工做切換,這樣會破壞DB Broker的元數據.
alter system set log_archive_dest_1='location=/db19clog/gspro_arc_ valid_for=(all_logfiles,all_roles) db_unique_name=gspro' scope=both;
alter system set log_archive_dest_2='service=gsp, SYNC AFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name=gsp net_timeout=30 valid_for=(online_logfile,primary_role)' scope=both;
SQL> alter system set log_archive_dest_1='location=/db19clog/gspro_arc_ valid_for=(all_logfiles,all_roles) db_unique_name=gspro' scope=both;
System altered.
SQL> alter system set log_archive_dest_2='service=gsp, SYNC AFFIRM delay=0 optional compression=disable max_failure=0 reopen=300 db_unique_name=gsp net_timeout=30 valid_for=(online_logfile,primary_role)' scope=both;
System altered.
SQL>
- DG Broker切換可以在任意數據庫保護模式下?
可以.
參考資料
- 12c Dataguard Switchover Best Practices using DGMGRL(Dataguard Broker Command Prompt)
- https://docs.oracle.com/en/database/oracle/oracle-database/19/dgbkr/using-data-guard-broker-to-manage-switchovers-failovers.html#GUID-89BF9FC5-1E3F-4C0B-90CB-AF4B39B5245E
