【Data guard】Switchover切換

主備庫切換操作驗證 （ switchover ）

1.   主庫執行切換（A機）

（ 1 ）   查看 主庫狀態

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME  OPEN_MODE    DATABASE_ROLE    SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

bhtc db               READ WRITE             PRIMARY             TO STANDBY

（ 2 ）主庫執行切換命令

SQL> alter database commit to switchover to physical standby with session shutdown wait;

Database altered.

（ 2 ）重啟主庫角色變為備庫（ mount 狀態 ）

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.3429E+10 bytes

Fixed Size                  2265944 bytes

Variable Size            6878661800 bytes

Database Buffers         6543114240 bytes

Redo Buffers                4612096 bytes

Database mounted.

（ 4 ）查看 主庫狀態

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME  OPEN_MODE    DATABASE_ROLE    SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

Bhtc db                MOUNTED     PHYSICAL STANDBY      RECOVERY NEEDED  

2.   備庫切換成主庫 （ B 機）

（ 1 ）查看備庫狀態

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME   OPEN_MODE   DATABASE_ROLE    SWITCHOVER_STATUS

---------------    --------------------     ----------------       --------------------

bhtcdg              MOUNTED          PHYSICAL STANDBY      TO PRIMARY

（ 2 ）將備庫切換成主庫

SQL> alter database commit to switchover to primary with session shutdown  wait ;

Database altered.

（ 3 ）查看備庫狀態

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME  OPEN_MODE     DATABASE_ROLE   SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

bhtcdg             MOUNTED           PRIMARY            NOT ALLOWED

（ 4 ）開啟

SQL> alter database open;

Database altered.

SQL>  select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME  OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STAT

--------------- ---------- ---------------- ---------------

bhtcdg         READ WRITE    PRIMARY          RESOLVABLE GAP

3.  打開數據庫（ A 機）

SQL> alter database open read only;

4.  應用日志（ A 機）

SQL> alter database recover managed standby database using current logfile disconnect from session;

3. 驗證數據

（ 1 ）在新主庫創建用戶（ B 機）

SQL> create user test1 identified by test1;

SQL> commit;

SQL> alter system switch logfile;

System altered.

（ 2 ）在新備庫查看（ A 機）

SQL> select username from dba_users where username='TEST1';

USERNAME

------------------------------

TEST1

4. 切換回 最初的狀態

(1) 新 主庫操作（ B 機 ）

SQL> alter database commit to switchover to physical standby with session shutdown;

Database altered.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 1.3429E+10 bytes

Fixed Size                  2265944 bytes

Variable Size            6878661800 bytes

Database Buffers         6543114240 bytes

Redo Buffers                4612096 bytes

Database mounted.

（2） 新 備庫 操作（ A 機）

SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME  OPEN_MODE    DATABASE_ROLE    SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

bhtcdb               READ WRITE            PRIMARY            RESOLVABLE GAP

（ 3 ） B 機 操作

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME   OPEN_MODE   DATABASE_ROLE    SWITCHOVER_STATUS

-------------------- -------------------- ---------------- --------------------

bhtcdg                MOUNTED      PHYSICAL STANDBY  NOT ALLOWED

（ 4 ）打開備庫開啟實時同步

SQL> alter database open read only;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME  OPEN_MODE  DATABASE_ROLE    SWITCHOVER_STAT

--------------- ---------- ---------------- ---------------

bhtcdg          READ ONLY  PHYSICAL STANDBY NOT ALLOWED

                WITH APPLY

（ 5 ）再次查看 A 機

SQL> select db_unique_name,open_mode,database_role,switchover_status from v$database;

DB_UNIQUE_NAME  OPEN_MODE    DATABASE_ROLE    SWITCHOVER_STATUS

--------------- -------------------- ---------------- --------------------

bhtc db             READ WRITE            PRIMARY           TO STANDBY

此時 ，已經切換回最初的狀態

（ 6 ）備庫查看日志應用情況

SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED

---------- ---------

         8 YES

         7 YES

         9 YES

        10 YES

        11 YES

        12 YES

        13 YES

        13 YES

        14 YES

        14 YES

        15 YES

 SEQUENCE# APPLIED

---------- ---------

        15 YES

        16 YES

        16 YES

        17 YES

        17 NO

        18 YES

        19 YES

        20 YES

        21 IN-MEMORY

---------- end ----------