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