Oracle DataGuard主备切换(switchover)
- 2020 年 7 月 18 日
- 筆記
- ----150 DataGuard
Oracle DataGuard主备切换可以使用传统的手动命令切换，也可以使用dgmgr切换，本文记录手动切换。
（一）将主库切换为物理备库
STEP1：查看主库状态
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S -------------------- ---------------- -------------------- --- -------- ------- READ WRITE PRIMARY SESSIONS ACTIVE YES DISABLED NONE
注意：需要检查SWITCHOVER_STATUS参数，如果值为”SESSION ACTIVE”或者”TO STANDBY”, 则主数据库角色可以切换为备库角色。
STEP2：将其切换到备库，切换后，数据库会关闭
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [WITH SESSION SHUTDOWN];
注意：如果上一步的SWITCH_STATUS参数值为”TO STANDBY”，则 WITH SESSION SHUTDOWN 可以省略。
STEP3：启动到mount状态
SQL> SHUTDOWN ABORT SQL> STARTUP MOUNT
注意：11.2.0.4版本及其以上版本不需要执行”SHUTDOWN ABORT”，因为数据库已经在STEP2命令中关闭了。
(二) 将备库切换成主库并启动到open
STEP1：查看备库状态
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S -------------------- ---------------- -------------------- --- -------- ------- READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY YES DISABLED NONE
注意：需要检查SWITCH_STATUS参数，如果值为”SESSION ACTIVE”或”TO PRIMARY”，则备库可以切换为主库。
STEP2： 切换到主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WITH SESSION SHUTDOWN];
注意：如果上一步的SWITCH_STATUS参数值为”TO PRIMARY”，则 WITH SESSION SHUTDOWN 可以省略。
STEP3： 此时数据库为mount状态，需打开数据库
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S -------------------- ---------------- -------------------- --- -------- ------- MOUNTED PRIMARY NOT ALLOWED YES DISABLED NONE SQL> ALTER DATABASE OPEN ; Database altered. SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S -------------------- ---------------- -------------------- --- -------- ------- READ WRITE PRIMARY TO STANDBY YES DISABLED NONE
(三)新的备库开启日志应用
SQL> ALTER DATABASE OPEN; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
【完】
