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 ---------- ---------------------------- -------------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 12 OPERATIONS OPERATIONS 13 OPERATIONS OPERATIONS 6 rows selected. Primay>INSERT INTO "SCOTT"."DEPT" ("DEPTNO", "DNAME", "LOC") VALUES ('14', 'OPERATIONS', 'OPERATIONS'); 1 row created. StandBY>commit; Commit complete. Primay>select * from scott.dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 14 OPERATIONS OPERATIONS 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 12 OPERATIONS OPERATIONS 13 OPERATIONS OPERATIONS 7 rows selected.
2、当前的StandBy启用redo应用
实时redo应用的情况
Primary>alter database recover managed standby database using current logfile disconnect from session; Database altered.
非实时redo应用的情况
-----应用redo
alter database recover managed standby database disconnect from session; -----暂停redo应用
alter database recover managed standby database cancel;
查询
Primary>select * from scott.dept; DEPTNO DNAME LOC ---------- ---------------------------- -------------------------- 14 OPERATIONS OPERATIONS 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 12 OPERATIONS OPERATIONS 13 OPERATIONS OPERATIONS 7 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中的是否相同
