完成組態
設定 Oracle Data Guard、註冊待命資料庫以及同步內部部署和待命資料庫密碼。
設定 Oracle Data Guard 中介
在主要和待命資料庫上啟用 dg_broker_config_file 參數來設定 Oracle Data Guard。對於 Oracle Automatic Storage Management (Oracle ASM)，請將中介組態檔放在個別的磁碟群組上。對於 Oracle Real Application Clusters (Oracle RAC)，中介組態檔必須在共用儲存體上。
- 檢查內部部署資料庫主機的
dg_broker_start值。它應該設為false。SQL> show parameter dg_broker_start;輸出看起來會像下面這樣：NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean FALSE - 在待命 Oracle Database Exadata Cloud Service 上，檢查 Oracle Data Guard 組態檔。
以下為輸出範例：SQL> show parameter dg_broker_config_file1;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string /u02/app/oracle/product/19.0.0.0/dbhome_5/dbs/dr1DB1_phx3g7.dat
以下為輸出範例：SQL> show parameter dg_broker_config_file2;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file2 string /u02/app/oracle/product/19.0.0.0/dbhome_5/dbs/dr2DB1_phx3g7.dat - 顯示資料檔，然後將位置變更為您要儲存檔案的位置。例如，Oracle ASM、Oracle RAC 或裝置。
SQL> select name from v$datafile;NAME -------------------------------------------------------------------------------- +DATAC1/DB1_PHX3G7/DATAFILE/system.348.1063638053 +DATAC1/DB1_PHX3G7/DATAFILE/sysaux.349.1063638055 +DATAC1/DB1_PHX3G7/DATAFILE/undotbs1.350.1063638057 +DATAC1/DB1_PHX3G7/DATAFILE/undotbs2.351.1063638057 +DATAC1/DB1_PHX3G7/DATAFILE/users.352.1063638059SQL> alter system set dg_broker_config_file1='+DATAC1/DB1_PHX3G7/dr1DB1_phx3g7.dat';SQL> alter system set dg_broker_config_file2='+RECOC1/DB1_PHX3G7/dr2DB1_phx3g7.dat'; - 在主要內部部署 Oracle Exadata Database Machine 上，檢查 Oracle Data Guard 組態檔。
以下為輸出範例：SQL> show parameter dg_broker_config_file1;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/dr1db1.dat.dat
以下是輸出範例：SQL> show parameter dg_broker_config_file2;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file2 string /u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/dr2db1.dat - 顯示資料檔，然後將位置變更為要儲存檔案的位置。
以下是輸出範例：SQL> select name from v$datafile;
執行下列命令來變更組態檔位置：NAME -------------------------------------------------------------------------------- +DATAC1/DB1/DATAFILE/system.277.1063487795 +DATAC1/DB1/DATAFILE/sysaux.270.1063487827 +DATAC1/DB1/DATAFILE/undotbs1.259.1063487843 +DATAC1/DB1/DATAFILE/undotbs2.275.1063487867 +DATAC1/DB1/DATAFILE/users.269.1063487879SQL> alter system set dg_broker_config_file1='+DATAC1/DB1/dr1db1.dat';SQL> alter system set dg_broker_config_file2='+RECOC1/DB1/dr2db1.dat';SQL> alter system set dg_broker_start=false; SQL> alter system set dg_broker_config_file1='+DATAC1/DB1/dr1db1.dat'; SQL> alter system set dg_broker_config_file2='+RECOC1/DB1/dr2db1.dat'; - 在主要內部部署 Oracle Exadata Database Machine 上，啟動 Oracle Data Guard Broker 程序。
SQL> alter system set dg_broker_start=true; SQL> show parameter dg_broker_startNAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUESQL> select pname from v$process where pname like 'DMON%';PNAME ----- DMON - 在待命資料庫上啟動 Oracle Data Guard Broker 處理作業。
SQL> alter system set dg_broker_start=true; SQL> show parameter dg_broker_startNAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_start boolean TRUESQL> select pname from v$process where pname like 'DMON%';PNAME ----- DMON
註冊待命資料庫
您可以使用 Oracle Data Guard 命令行介面 (DGMGRL)，將待命資料庫設定檔註冊或新增至主要內部部署主機上的代理程式組態。
- 以
sys身分登入主要主機。$ dgmgrl sys/sys password@net service name for primary database - 建立使用主要資料庫名稱的組態。
例如：DGMGRL> CREATE CONFIGURATION configuration_name AS PRIMARY DATABASE IS primary database name CONNECT IDENTIFIER IS primary database name;CREATE CONFIGURATION OnPremExaDR AS PRIMARY DATABASE IS DB1 CONNECT IDENTIFIER IS DB1; - 新增待命資料庫。確定您的 ID 與服務名稱相同，包括大寫和小寫。
例如：DGMGRL> ADD DATABASE standby unique database name AS CONNECT IDENTIFIER IS standby unique database name MAINTAINED AS PHYSICAL;ADD DATABASE DB1_PHX3G7 AS CONNECT IDENTIFIER IS DB1_phx3g7 MAINTAINED AS PHYSICAL;如果您收到 ORA-16698 錯誤訊息，請確認您的 ID 與服務名稱相同。錯誤：ORA-16698：成員具有已設定 SERVICE 屬性的 LOG_ARCHIVE_DEST_n 參數
- 變更日誌存檔目的地狀態。
SQL> alter system set log_archive_dest_state_10='DEFER';SQL> alter system set log_archive_dest_10=''; - 檢查日誌存檔目的地。
SQL> show parameter log_archive_dest;NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest string log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DB1_PHX3G7 log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_16 string log_archive_dest_17 string log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2 string SERVICE=DB1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB1 log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string log_archive_dest_23 string - 啟用組態。
DGMGRL> enable configuration; - 顯示組態。
DGMGRL> show configuration;輸出與下列類似：Configuration - onpremexadr Protection Mode: MaxPerformance Members: db1 - Primary database Warning: ORA-16809: multiple warnings detected for the member db1_phx3g7 - Physical standby database Error: ORA-16532: Oracle Data Guard broker configuration does not exist Fast-Start Failover: Disabled Configuration Status: ERROR (status updated 21 seconds ago) - 在待命 Oracle Database Exadata Cloud Service 上，登入以驗證組態。
[oracle@exacs82-vm3sv1 ~]$ dgmgrl DGMGRL> connectUsername: sys Password: Connected to "DB1_phx3g7" Connected as SYSDBA.
輸出與下列類似：DGMGRL> show configuration;Configuration - onpremexadr Protection Mode: MaxPerformance Members: db1 - Primary database Warning: ORA-16809: multiple warnings detected for the member db1_phx3g7 - Physical standby database Warning: ORA-16809: multiple warnings detected for the member Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 43 seconds ago
將重做日誌檔從內部部署複製到 OCI
將 redo 日誌檔和其他資料庫檔案從內部部署資料庫複製到 Oracle Cloud Infrastructure (OCI) 執行處理。
- 檢視內部部署資料庫檔案。
select name from v$datafile;輸出與下列類似：NAME -------------------------------------------------------------------------------- +DATAC1/DB1/DATAFILE/system.277.1063487795 +DATAC1/DB1/DATAFILE/sysaux.270.1063487827 +DATAC1/DB1/DATAFILE/undotbs1.259.1063487843 +DATAC1/DB1/DATAFILE/undotbs2.275.1063487867 +DATAC1/DB1/DATAFILE/users.269.1063487879 - 檢視內部部署資料檔的每個群組大小。
select bytes, group# from v$log;輸出與下列類似：BYTES GROUP# ---------- ---------- 209715200 1 209715200 2 209715200 3 209715200 4 - 檢視內部部署資料檔案的
redo日誌。select member from v$logfile;輸出與下列類似：MEMBER -------------------------------------------------------------------------------- +DATAC1/DB1/ONLINELOG/group_2.272.1063449917 +RECOC1/DB1/ONLINELOG/group_2.281.1063449917 +DATAC1/DB1/ONLINELOG/group_1.273.1063449917 +RECOC1/DB1/ONLINELOG/group_1.282.1063449919 +DATAC1/DB1/ONLINELOG/group_3.268.1063450555 +RECOC1/DB1/ONLINELOG/group_3.283.1063450557 +DATAC1/DB1/ONLINELOG/group_4.261.1063450557 +RECOC1/DB1/ONLINELOG/group_4.284.1063450557 - 為每個
redo日誌新增待命日誌檔群組。例如，如果您有三個redo日誌檔，請新增三個待命日誌檔群組。SQL> alter database add standby logfile group 5 ('+DATAC1','+RECOC1'); alter database add standby logfile group 6 ('+DATAC1','+RECOC1'); alter database add standby logfile group 7 ('+DATAC1','+RECOC1'); - 檢視日誌檔的成員。
SQL> select member from v$logfile;輸出看起來會像下面這樣：MEMBER -------------------------------------------------------------------------------- +DATAC1/DB1/ONLINELOG/group_2.272.1063449917 +RECOC1/DB1/ONLINELOG/group_2.281.1063449917 +DATAC1/DB1/ONLINELOG/group_1.273.1063449917 +RECOC1/DB1/ONLINELOG/group_1.282.1063449919 +DATAC1/DB1/ONLINELOG/group_5.264.1063812687 +DATAC1/DB1/ONLINELOG/group_3.268.1063450555 +RECOC1/DB1/ONLINELOG/group_3.283.1063450557 +DATAC1/DB1/ONLINELOG/group_4.261.1063450557 +RECOC1/DB1/ONLINELOG/group_4.284.1063450557 +RECOC1/DB1/ONLINELOG/group_5.422.1063812687 +DATAC1/DB1/ONLINELOG/group_6.262.1063812785 +RECOC1/DB1/ONLINELOG/group_6.445.1063812785 +DATAC1/DB1/ONLINELOG/group_7.263.1063812807 +RECOC1/DB1/ONLINELOG/group_7.446.1063812807 - 檢視日誌檔的群組編號和成員。
SQL> select group#,member from v$logfile;輸出看起來會像下面這樣：GROUP# MEMBER ------------ -------------------------------------------------- 2 +DATAC1/DB1/ONLINELOG/group_2.272.1063449917 2 +RECOC1/DB1/ONLINELOG/group_2.281.1063449917 1 +DATAC1/DB1/ONLINELOG/group_1.273.1063449917 1 +RECOC1/DB1/ONLINELOG/group_1.282.1063449919 5 +DATAC1/DB1/ONLINELOG/group_5.264.1063812687 3 +DATAC1/DB1/ONLINELOG/group_3.268.1063450555 3 +RECOC1/DB1/ONLINELOG/group_3.283.1063450557 4 +DATAC1/DB1/ONLINELOG/group_4.261.1063450557 4 +RECOC1/DB1/ONLINELOG/group_4.284.1063450557 5 +RECOC1/DB1/ONLINELOG/group_5.422.1063812687 6 +DATAC1/DB1/ONLINELOG/group_6.262.1063812785 6 +RECOC1/DB1/ONLINELOG/group_6.445.1063812785 7 +DATAC1/DB1/ONLINELOG/group_7.263.1063812807 7 +RECOC1/DB1/ONLINELOG/group_7.446.1063812807 - 檢視 Oracle Data Guard 中的組態。
DGMGRL> show configuration輸出看起來會像下面這樣：Configuration - onpremexadr Protection Mode: MaxPerformance Members: db1 - Primary database db1_phx3g7 - Physical standby database Warning: ORA-16809: multiple warnings detected for the member Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 8 seconds ago) - 在待命 Oracle Database Exadata Cloud Service 上，檢視重做日誌。
select member from v$logfile;輸出看起來會像下面這樣：MEMBER --------------------------------------------------- /opt/oracle/oradata/ORCLCDB/redo03.log /opt/oracle/oradata/ORCLCDB/redo02.log /opt/oracle/oradata/ORCLCDB/redo01.log - 使用
sys使用者名稱和密碼登入待命 Oracle Database Exadata Cloud Service 資料庫執行處理的 Oracle Data Guard 命令行介面 (DGMGRL) 階段作業。[oracle@droci admin]$ dgmgrl DGMGRL> connect - 檢視 OCI 中待命資料庫的組態。
DGMGRL> show configuration;輸出看起來會像下面這樣：Configuration - OnPremDr Protection Mode: MaxPerformance Members: db1 - Primary database db1_phx3g7 - Physical standby database Warning: ORA-16809: multiple warnings detected for the member Fast-Start Failover: Disabled Configuration Status: WARNING (status updated 38 seconds ago) - 在 OCI 中編輯待命資料庫執行處理，並將狀態設為
apply-off。當命令成功時，結束命令行介面。DGMGRL> edit database DB1_phx3g7 set state=apply-off;DGMGRL> exit - 以
sysdba身分登入 OCI 中的待命資料庫執行處理。[oracle@exacs82-vm3sv1 ~]$ sqlplus / as sysdba - 檢視日誌檔的群組編號和成員。
SQL> select group#,member from v$logfile;輸出看起來會像下面這樣：GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 2 +DATAC1/DB1_PHX3G7/ONLINELOG/group_2.354.1063638089 3 +DATAC1/DB1_PHX3G7/ONLINELOG/group_3.355.1063638089 1 +DATAC1/DB1_PHX3G7/ONLINELOG/group_1.353.1063638089 4 +DATAC1/DB1_PHX3G7/ONLINELOG/group_4.356.1063638089 - 檢視每個群組的大小。
select bytes, group# from v$log;輸出看起來會像下面這樣：BYTES GROUP# ---------- ---------- 209715200 1 209715200 2 209715200 3 209715200 4 - 將待命日誌檔群組新增至 OCI 中的資料庫。
[oracle@exacs82-vm3sv1 ~]$ sqlplus / as sysdbaalter database add standby logfile group 5 ('+DATAC1','+RECOC1'); alter database add standby logfile group 6 ('+DATAC1','+RECOC1'); alter database add standby logfile group 7 ('+DATAC1','+RECOC1'); - 檢視日誌檔的群組編號和成員。
SQL> select group#,member from v$logfile;輸出看起來會像下面這樣：GROUP# ---------- MEMBER -------------------------------------------------------------------------------- 2 +DATAC1/DB1_PHX3G7/ONLINELOG/group_2.354.1063638089 3 +DATAC1/DB1_PHX3G7/ONLINELOG/group_3.355.1063638089 1 +DATAC1/DB1_PHX3G7/ONLINELOG/group_1.353.1063638089 4 +DATAC1/DB1_PHX3G7/ONLINELOG/group_4.356.1063638089 5 +DATAC1/DB1_PHX3G7/ONLINELOG/group_5.358.1063813749 5 +RECOC1/DB1_PHX3G7/ONLINELOG/group_5.256.1063813749 6 +DATAC1/DB1_PHX3G7/ONLINELOG/group_6.359.1063813761 6 +RECOC1/DB1_PHX3G7/ONLINELOG/group_6.262.1063813761 7 +DATAC1/DB1_PHX3G7/ONLINELOG/group_7.360.1063813773 7 +RECOC1/DB1_PHX3G7/ONLINELOG/group_7.300.1063813773 - 在待命 OCI 資料庫執行處理上，使用
sys使用者名稱和密碼登入 Oracle Data Guard 命令行介面 (DGMGRL) 階段作業。[oracle@exacs82-vm3sv1 ~]$ dgmgrl DGMGRL> connect Username: sys Password:Connected to "DB1_phx3g7" Connected as SYSDBA. - 在 OCI 中編輯待命資料庫執行處理，並將狀態設為
apply-on。DGMGRL> edit database db1_phx3g7 set state=apply-on; - 在 OCI 中編輯待命資料庫執行處理，並將傳輸狀態設為
transport-on，然後結束命令行介面。當命令成功時，結束 DGMGRL。DGMGRL> edit database DB1 set state=transport-on;DGMGRL> exit - 檢視 OCI 上待命資料庫中的待命日誌檔以驗證待命資料庫。
SQL> select * from v$logfile where type='STANDBY'; - 檢視 OCI 上待命資料庫中的重做日誌檔。
SQL> select member from v$logfile where type != 'STANDBY';輸出看起來會像下面這樣：MEMBER -------------------------------------------------------------------------------- +DATAC1/DB1_PHX3G7/ONLINELOG/group_2.354.1063638089 +DATAC1/DB1_PHX3G7/ONLINELOG/group_3.355.1063638089 +DATAC1/DB1_PHX3G7/ONLINELOG/group_1.353.1063638089 +DATAC1/DB1_PHX3G7/ONLINELOG/group_4.356.1063638089 - 復原管理的待命資料庫。
SQL> recover managed standby database cancel;輸出看起來會像下面這樣：Media recovery complete - 使用
convert參數檢視檔案。SQL> show parameter convert;輸出看起來會像下面這樣：NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string DB1, DB1_phx3g7 log_file_name_convert string DB1, DB1_phx3g7 pdb_file_name_convert string - 檢視資料庫檔案。
select name from v$datafile;輸出看起來會像下面這樣：NAME -------------------------------------------------------------------------------- +DATAC1/DB1_PHX3G7/DATAFILE/system.348.1063638053 +DATAC1/DB1_PHX3G7/DATAFILE/sysaux.349.1063638055 +DATAC1/DB1_PHX3G7/DATAFILE/undotbs1.350.1063638057 +DATAC1/DB1_PHX3G7/DATAFILE/undotbs2.351.1063638057 +DATAC1/DB1_PHX3G7/DATAFILE/users.352.1063638059 - 檢視 Oracle Data Guard 存檔詳細資訊。
SQL> SELECT ROLE, THREAD#, SEQUENCE#, ACTION FROM V$DATAGUARD_PROCESS;輸出看起來會像下面這樣：ROLE THREAD# SEQUENCE# ACTION ------------------------ ---------- ---------- ------------ RFS ping 1 0 IDLE RFS ping 2 62 IDLE RFS async 2 62 RECEIVING broker instance worker 0 0 IDLE broker worker 0 0 IDLE archive redo 0 0 IDLE archive redo 0 0 IDLE archive redo 0 0 IDLE archive local 0 0 IDLE redo transport timer 0 0 IDLE gap manager 0 0 IDLE ROLE THREAD# SEQUENCE# ACTION ------------------------ ---------- ---------- ------------ broker monitor 0 0 IDLE broker net worker 0 0 IDLE redo transport monitor 0 0 IDLE log writer 0 0 IDLE RFS async 0 187 RECEIVING 16 rows selected. - 使用日誌檔停用受管理待命資料庫的復原。
SQL> recover managed standby database using current logfile disconnect;Media recovery complete. - 使用目的地參數顯示檔案並驗證。
SQL>show parameter dest;