DataGuard---->物理StandBy的角色切换之switchover

Switchover，无损切换，通常是用户手动触发或者有计划地让其自动触发，如硬件升级等。

步骤：

1、Primary数据库转换为StandBy角色

2、StandBy数据库（之一）转换为Primary角色

准备工作：

1、检查待转换角色的数据库参数配置正确

2、检查即将成为Primay的物理Standby服务器是否是归档模式

3、确保所有的归档日志已经传送到物理StandBy数据库

主库可以手动切换一次logfile

Primary>alter system switch logfile;

查询主库当前的归档日志sequence#

Primary>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
        22

查询备库当前的归档日志sequence#

StandBY>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
        22

两者相同，代表已经完全同步

然后再暂停备库的redo应用

StandBy>alter database recover managed standby database cancel;

Database altered.

一、查询是否可以转换

查询主库是否支持switchover操作

Primary> select file_name, bytes from dba_temp_files;
SWITCHOVER_STATUS
----------------------------------------
TO STANDBY

如果是SWICHOVER_STATUS显示为SESSIONS ACTIVE, 说明当前有人连接Primary数据库

  查询备库是否支持switchover操作

StandBy> select switchover_status from v$database;

SWITCHOVER_STATUS
----------------------------------------
NOT ALLOWED

NOT ALLOWED是因为主库还未变切换为standby

二、primary数据库切换为物理standby

Primary—>StandBy

Primary>alter database commit to switchover to physical standby;

Database altered.

此时Primary数据库变为mount状态

Primary>select open_mode from v$database;

OPEN_MODE
----------------------------------------
MOUNTED

角色也变为physical standby

Primary>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PHYSICAL STANDBY

如果此时打开数据库，为只读状态

Primary>alter database open;

Database altered.

Primary>select open_mode from v$database;

OPEN_MODE
----------------------------------------
READ ONLY

Primary>

三、StandBy数据库转换为Primary角色

StandBy--->Primary

StandBy>select switchover_status from v$database;

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

StandBy> alter database commit to switchover to primary;

Database altered.

StandBy>alter database open;

Database altered.

StandBy>select open_mode from v$database;

OPEN_MODE
----------------------------------------
READ WRITE

StandBy>select database_role from v$database;

DATABASE_ROLE
--------------------------------
PRIMARY

StandBy>

四、同步测试

1、当前的Primay数据库插入一条数据

Primay>select * from scott.dept;

    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
ACCOUNTING            NEW YORK
RESEARCH            DALLAS
SALES            CHICAGO
OPERATIONS            BOSTON
OPERATIONS            OPERATIONS
OPERATIONS            OPERATIONS
rows selected.

Primay>INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('14', 'OPERATIONS', 'OPERATIONS');
row created.

StandBY>commit;

Commit complete.

Primay>select * from scott.dept;

    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
OPERATIONS            OPERATIONS
ACCOUNTING            NEW YORK
RESEARCH            DALLAS
SALES            CHICAGO
OPERATIONS            BOSTON
OPERATIONS            OPERATIONS
OPERATIONS            OPERATIONS
rows selected.

2、当前的StandBy启用redo应用

实时redo应用的情况

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

Database altered.

非实时redo应用的情况

 
alter database recover managed standby database disconnect from session;   --应用redo

alter database recover managed standby database cancel;  --暂停redo应用

查询

Primary>select * from scott.dept;

    DEPTNO DNAME            LOC
---------- ---------------------------- --------------------------
OPERATIONS            OPERATIONS
ACCOUNTING            NEW YORK
RESEARCH            DALLAS
SALES            CHICAGO
OPERATIONS            BOSTON
OPERATIONS            OPERATIONS
OPERATIONS            OPERATIONS
rows selected.

无法同步的情况问题解决

1、如果无法同步，切换日志试试

StandBY>alter system switch logfile;

查询Priamry和StandBy的归档日志编号是否相同

StandBY>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
        22

2、查看配置的服务名中的service_name 和lsnrctl status中的是否相同