Oracle DataGuard主备切换(switchover) 首先确认现在的dataguard是否正确 主库
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ WRITE PRIMARY TO STANDBY MAXIMUM PERFORMANCE
备库
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED MAXIMUM PERFORMANCE
备库为以下状态，说明现阶段dataguard正常
READ ONLY WITH APPLY
（一）将主库切换为物理备库 STEP1：查看主库状态
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ WRITE PRIMARY TO STANDBY MAXIMUM PROTECTION
注意：需要检查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> STARTUP MOUNT 注意：11.2.0.4版本及其以上版本不需要执行"SHUTDOWN ABORT"，因为数据库已经在STEP2命令中关闭了。 (二) 将备库切换成主库并启动到open STEP1：查看备库状态
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ ONLY PHYSICAL STANDBY RECOVERY NEEDED MAXIMUM PROTECTION
注意：需要检查SWITCH_STATUS参数，如果值为"SESSION ACTIVE"或"TO PRIMARY"，则备库可以切换为主库。 此处SWITCH_STATUS参数为 RECOVERY NEEDED，或者NOT ALLOWED 需要执行
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Media recovery complete. SQL>
再次检查备库状态
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY MAXIMUM PROTECTION
此时已经是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 ; 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 ;
或者
SQL> alter database recover managed standby database disconnect from session;
###################两种注意点######################################################################### 启动redo 应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
启动实时应用
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
提示：disconnect from session子句并非必须，该子句用于指定启动完应用后自动退出到命令操作符前， 如果不指定的话，当前session 就会一直停留处理redo 应用，如果想做其它操作，就只能新建一个连接。 两者切换需要用到语句
SQL> alter database recover managed standby database cancel;
如果是启动的实时应用，主库插入一条语句，备库就能查询到 如果是启用的redo应用，备库需要过一会才会查询到。 ####################################################################################################### 查看备库状态
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS,protection_level FROM V$DATABASE; OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS PROTECTION_LEVEL -------------------- ---------------- -------------------- -------------------- READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED MAXIMUM PROTECTION
参考文档：https://www.cnblogs.com/lijiaman/p/13335406.html
