Oracle 11g dg switchover切換操作流程

潇湘隐者發表於2024-07-10
Oracle

主庫切換為物理備庫

  1. 檢視主庫的狀態

--獲取/確認主庫的狀態資訊以及保護模式

SQL> set linesize 720
SQL> col name for a10
SQL> col open_mode for a10
SQL> col database_role for a14
SQL> col switchover_status for a16
SQL> col force_logging for a8
SQL> col guard_status for a8
SQL> select name
  2        ,open_mode
  3        ,database_role
  4        ,protection_mode
  5        ,protection_level
  6        ,switchover_status 
  7        ,force_logging
  8        ,dataguard_broker
  9        ,guard_status
 10  from v$database;

NAME       OPEN_MODE  DATABASE_ROLE  PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATU FORCE_LO DATAGUAR GUARD_ST
---------- ---------- -------------- -------------------- -------------------- ---------------- -------- -------- --------
GSP        READ WRITE PRIMARY        MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO STANDBY       YES      DISABLED NONE

注意事項，檢查SWITCHOVER_STATUS欄位，如果值為"SESSION ACTIVE"或者"TO STANDBY", 則主資料庫角色可以切換為備庫角色。 如果欄位SWITCHOVER_STATUS是其它值的情況下，則不能切換，具體參考文件dg_parameter_summary.md。這裡略過。

  1. 主庫切換到備庫
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
或
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

如果上一步的SWITCH_STATUS欄位值為"TO STANDBY"，則 WITH SESSION SHUTDOWN 可以省略。如果SWITCHOVER_STATUS欄位值為"SESSION ACTIVE" 的話，則必須加上[WITH SESSION SHUTDOWN]。

在執行上面命名前，在SecureCRT或Putty中開啟另外一個視窗，觀察告警日誌的輸出資訊，以便觀察詳細輸出資訊。

$ tail -60f alert_<ORACLE_SID>.log
  1. 資料庫啟動到MOUNT狀態
SQL> SELECT STATUS FROM V$INSTANCE;
SQL> STARTUP MOUNT;

備庫切換為主庫

  1. 檢視備庫的狀態
SQL> set linesize 720
SQL> col name for a10
SQL> col open_mode for a10
SQL> col database_role for a14
SQL> col switchover_status for a16
SQL> col force_logging for a8
SQL> col guard_status for a8
SQL> select name
  2        ,open_mode
  3        ,database_role
  4        ,protection_mode
  5        ,protection_level
  6        ,switchover_status 
  7        ,force_logging
  8        ,dataguard_broker
  9        ,guard_status
 10  from v$database;

NAME       OPEN_MODE  DATABASE_ROLE  PROTECTION_MODE      PROTECTION_LEVEL     SWITCHOVER_STATU FORCE_LO DATAGUAR GUARD_ST
---------- ---------- -------------- -------------------- -------------------- ---------------- -------- -------- --------
GSP        READ ONLY  PHYSICAL STAND MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  TO PRIMARY       YES      DISABLED NONE
           WITH APPLY BY

需要檢查SWITCH_STATUS欄位的值，如果值為"SESSION ACTIVE"或"TO PRIMARY"，則備庫可以切換為主庫。

  1. 切換到主庫
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
或
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

注意：如果上一步的SWITCH_STATUS引數值為"TO PRIMARY"，則 WITH SESSION SHUTDOWN 可以省略。

  1. 將資料庫OPEN
SQL> ALTER DATABASE OPEN ;
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

新的備庫開啟日誌應用

SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

其實，這裡的方法也適用於Oracle 12/19c多租戶環境，如果是多租戶環境，則可以按下面命令實施。

SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 GSPPROD                        MOUNTED
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 GSPPROD                        MOUNTED
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

檢查主備的狀態

主備庫執行：

set linesize 720
col name for a10
col open_mode for a20
col database_role for a16
col switchover_status for a12 heading "SWITCHOVER |STATUS"
col force_logging for a8
col dataguard_broker for a8 heading "DATAGUARD|BROKER"
col guard_status for a8
select name
      ,open_mode
      ,database_role
      ,protection_mode
      ,protection_level
      ,switchover_status 
      ,force_logging
      ,dataguard_broker
      ,guard_status
from v$database;

備庫執行SQL,檢查同步狀態

set linesize 720;
col name for a24;
col source_db_unique_name for a16;
col value for a16;
col unit for a20;
col time_computed for a19;
col datum_time for a19;
select source_db_unique_name
     , name
     , value
     , unit
     , time_computed 
     , datum_time
from v$dataguard_stats;

最好還做一下資料同步的驗證，例如在主庫建立一個表，插入幾條資料，驗證資料庫是否同步到測試環境。

SQL> create table test(id number, name varchar2(16));

Table created.

SQL> insert into test
  2  select 1001,'kerry1' from dual union all
  3  select 1002,'kerry2' from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL>

總結

這裡的操作步驟是在引數配置正確的情況下的操作步驟，無需去驗證引數是否正確。如果有些引數配置不正確，那麼在切換過程中可能會遇到各種問題。

相關文章