【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 ----------