dataguard主备切换（无损切换switchover）

一、环境介绍： 参数规划： 数据库 db_name sid instance_name service_names db_unique_name fal_server fal_client 主 库 dg1 dg1 dg1 dg1 dg1 bdg2 dg1 备库 dg1 dg1 dg1 bdg2 bdg2 dg1 bdg2 操作系统：CentOS Linux release 6.0 (Final) 数据库：Oracle

一、环境介绍：
参数规划：
数据库 db_name sid instance_name service_names db_unique_name fal_server fal_client
主 库 dg1 dg1 dg1 dg1 dg1 bdg2 dg1
备库 dg1 dg1 dg1 bdg2 bdg2 dg1 bdg2

操作系统：CentOS Linux release 6.0 (Final)

数据库：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

dg模式：物理dg

SQL> select protection_mode, protection_level from v$database;
PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

二、正常主备切换（switchover）
主库：
在primary数据库检查是否支持switchover操作
1、 SQL>select open_mode,switchover_status,database_role from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY
如果该列值为”TO STANDBY”则表示primary数据库支持转换为STANDBY角色
注意:1、如果是第一次做Switchover的话,这里应该是SESSIONS ACTIVE状态,不用理会他,继续下面的操作.
2、在primary数据库启动switchover，先把primary转换为standby的角色
当switchover_status为SESSIONS ACTIVE时，（说明有会话连接）
alter database commit to switchover to physical standby with session shutdown;
否则执行
SQL> alter database commit to switchover to physical standby;
Database altered.
执行完上个语句，primary会变成standby数据库，并备份控制文件到trace
此时查看dg2状态，备库dg2状态会由NOT ALLOWED变成TO PRIMARY
SQL> select open_mode,switchover_status,database_role from v$database;
OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED TO PRIMARY PHYSICAL STANDBY

3、在primary数据库重启动到mount
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
此时查看dg1，成功切换成备库了，同dg2状态一样，见上图。
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED TO PRIMARY PHYSICAL STANDBY
4、(开启standby应用恢复模式)
SQL> alter database recover managed standby database disconnect;--接收日志
Database altered.
5、再次查看状态
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED TO PRIMARY PHYSICAL STANDBY

备库：
在STANDBY数据库上检查是否支持switchover操作
[oracle@dg2 ~]$ sqlplus / as sysdba
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED TO PRIMARY PHYSICAL STANDBY

值为”TO PRIMARY”,说明支持转换.

2、 在STANDBY数据库转换角色到Primary
[oracle@dg2 ~]$ sqlplus / as sysdba
SQL> alter database commit to switchover to primary;
Database altered.

SQL> select open_mode,switchover_status,database_role from v$database;
OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED NOT ALLOWED PRIMARY

SQL> alter database open; //如果处于read-only状态,需要先shutdown再startup;
Database altered.
查看下dg2状态：
SQL> select open_mode,switchover_status,database_role from v$database;
OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE NOT ALLOWED PRIMARY

最后验证一下：
验证数据是否可以同步,按照以前的测试方法进行测试
在新的primary数据库上执行
SQL> show parameter db_unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string bdg2

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
143

SQL> alter system switch logfile;
System altered.

切换成功
dg1：
SQL>从 v$database 选择 open_mode,switchover_status,database_role;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ---------- ------
仅在应用时阅读，不允许物理待机
SQL>从 v$archived_log 中选择 max(sequence#);

MAX(SEQUENCE#)
--------------
153
dg2：
SQL>从 v$database 选择 open_mode,switchover_status,database_role;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ---------- ------
读取写入备用主