oracle 11g physical standby switchover

简介

SWITCHOVERS主要是在计划停机维护时用来降低DOWNTIME，如硬件维护、操作系统升级或是数据库rolling upgrade,

也可用来进行特殊情况下的数据库迁移。

SWITCHOVERS主要分为两个阶段，阶段1：主库转换为备库角色；阶段2：备库转换为主库角色

环境：

switchover 前
db_unique_name : ogg: primary 
db_unique_name : tgg: physical standby

前提条件检查

1. standby log /redo log 在主备库都存在

select * from V$STANDBY_LOG;

select * from v$log
2. 确认主备处于归档模式

archive log list

3. 确认redo传输无错误、无GAP

SQL> SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;

STATUS      GAP_STATUS
--------- ------------------------
VALID      NO GAP

4.确认主备库temprory file一样

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/oradata/ogg/temp01.dbf

5.确认log_archive_dest_n参数设置正确

show parameter log_archive_dest_2

主库

SQL> show parameter log_archive_dest_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string     SERVICE=tgg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=tgg

备库

SQL> show parameter log_archive_dest_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string     service=ogg ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=ogg


开始SWITCHOVER
将主库ogg switchover to 备库
检查主库是否准备好进行角色转换
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

SWITCHOVER_STATUS为SESSIONS ACTIVE，是因为还有有SESSION 连接到数据库，断开连接SESSION在查询如下

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

状态在SESSIONS ACTIVE 或者 TO STANDBY 都可以进行switchover操作
进行switchover
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

将数据库重启到mount状态

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup mount;
ORACLE instance started.
SQL> alter database open;

Database altered.

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

DATABASE_ROLE     DB_UNIQUE_NAME         OPEN_MODE
---------------- ------------------------------ --------------------
PRIMARY      tgg                READ WRITE

SQL> alter database open;

Database altered.

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

Database altered.

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

DATABASE_ROLE     DB_UNIQUE_NAME         OPEN_MODE
---------------- ------------------------------ --------------------
PHYSICAL STANDBY ogg                READ ONLY WITH APPLY

将备库tgg switchover to 主库
检查主库是否准备好进行角色转换


SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY


进行switchover 操作

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

开启新主库


SQL> alter database open;

Database altered.

检查角色转换结果


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

DATABASE_ROLE     DB_UNIQUE_NAME         OPEN_MODE
---------------- ------------------------------ --------------------
PRIMARY      tgg                READ WRITE


将新备库ogg 开启为read only --real time apply
SQL> alter database open;

Database altered.

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

Database altered.

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

DATABASE_ROLE     DB_UNIQUE_NAME         OPEN_MODE
---------------- ------------------------------ --------------------
PHYSICAL STANDBY ogg                READ ONLY WITH APPLY