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