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

来源：互联网发布：寻秦ol源码编辑：程序博客网时间：2024/04/27 00:05

一、环境介绍：
参数规划：
数据库       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> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ----------------
READ ONLY WITH APPLY NOT ALLOWED PHYSICAL STANDBY
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
153
dg2：
SQL> select open_mode,switchover_status,database_role from v$database;

OPEN_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE TO STANDBY PRIMARY

0 0