Oracle DG主备架构两种切换方式总结switch over和fail over一、DG主备正常切换即有计划的切换主备正常switch over切换即有计划的切换这种切换方式无数据丢失备注1Oracle10g版本RAC环境下主备都只能保留一个实例才能进行switch over否则不容许。而且备库上保留的实例必须是MPR进程存在的实例。211g版本以上上面的限制已经取消只需确保备库切换为主库时必须MPR进程存在的实例。如果条件允许保留一个节点来切换影响最小化1、查看主库状态是否能够进行switch over切换主库上执行 set linesize 200 pagesize 999 col open_mode format a10 select open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from v$database; 查询结果类似如下 OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR ---------- ---------------- -------------------- -------------------- -------------------- --- READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY YES根据SWITCHOVER_STATUS列判断是否可以切换To standby 或者session active表明可以进行切换session active表示主备库有活动的session关联在switchover前需要将这些session关闭执行切换命令时加上with session shutdown2、主库上发起切换命令切换为备库主库上执行 Alter database commit to switchover to standby with session shutdown ;这个命令完成后主库自动down集群环境所有节点都会自动停止3、备库切换为主库1如果备库是集群判断MPR日志应用进程在哪个节点需要在MPR进程上完成备库切换为主库备库上执行 查询MPR进程在哪个节点的语句 SELECT INST_ID,PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY where PROCESS like %MRP%;2在MPR节点上查看备库状态是否能够切换为主库备库上执行 set linesize 200 pagesize 999 col open_mode format a10 select open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from v$database; 查询结果类似如下 OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR -------------------- ---------------- -------------------- -------------------- -------------------- --- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY YES根据SWITCHOVER_STATUS列判断是否可以切换TO PRIMARY表名可以进行切换3执行切换命令备库上执行 alter database commit to switchover to primary with session shutdown ;备注集群环境切换完成后所有节点都会变为mount状态数据库挂载状态4切换后检查备库状态set linesize 200 pagesize 999 col open_mode format a10 select open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR ---------- ---------------- -------------------- -------------------- -------------------- --- MOUNTED PRIMARY UNPROTECTED MAXIMUM PERFORMANCE NOT ALLOWED YES5打开新的主库源备库alter database open;如果是集群环境所有节点都需要执行数据库打开操作这一点不要遗漏6新主库检查状态set linesize 200 pagesize 999 col open_mode format a10 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database; INST_ID OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR ---------- ---------- ---------------- -------------------- -------------------- -------------------- --- 2 READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE FAILED DESTINATION YES 1 READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE FAILED DESTINATION YES4、原先的主库再次打开后直接转变为standby db直接应用日志即可1打开原先的主库单库环境 sqlplus /as sysdba startup 集群环境 srvctl start database -d orcl2检查状态set linesize 200 pagesize 999 col open_mode format a10 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database; INST_ID OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR 2 READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE RECOVERY NEEDED YES 1 READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE RECOVERY NEEDED YES3执行日志应用alter database recover managed standby database using current logfile disconnect from session;4检查状态set linesize 200 pagesize 999 col open_mode format a10 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database; INST_ID OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR 1 READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED YES 2 READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED YES至此switch over主备切换完成二、DG主备异常切换主库意外挂掉切换至备库接手对外服务主备异常切换特指fail over切换切换后主备环境破坏备库需要重新搭建大概率有数据丢失RAC环境中关闭除了运行MRP进程的其他实例然后再MRP存在的实例上执行。执行切换前备库最好是处于mount状态可以提高切换速度。一老版本切换方法12c开始有新语法但是老方法仍旧可用1、如果主库可以mount将主库中所有未到备库的归档日志和redo刷新到备库ALTER SYSTEM FLUSH REDO TO target_db_name; 备注 1target_db_name:用于指定目标备库的DB_UNIQUE_NAME必须使用单引号括起来 2语句必须在mount状态下执行 具体如下 ALTER SYSTEM FLUSH REDO TO orcl_st;2、如果主库不可以mount使用下面方式尽可能将主库数据同步至备库查询备库上的v$archivd_log视图以获取redo的最大sequence号。 su - oracle sqlplus /as sysdba set linesize 20o pagesize 999 col standby_tns_name format a30 select inst_id,max(sequence#) ,min(sequence#) from gv$archived_log where appliedNO group by inst_id order by 1; 将主库的最新归档日志传输到备库然后注册该归档文件 ALTER DATABASE REGISTER PHYSICAL LOGFILE 归档日志文件名绝对路径; 继续查询备库上的v$archived_GAP视图在确定备库上是否存在gap SELECT inst_id,THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM gV$ARCHIVE_GAP; 如果有gap,请将gap的归档日志从主库传输到备库然后在备库中继续注册归档文件。 ALTER DATABASE REGISTER PHYSICAL LOGFILE 归档日志文件名绝对路径; 重复执行直至所有的gap间隙都得到解决如果上面的1、2步骤都不能达成此问题不能解决则在failover期间可能会丢失一些数据。3、备库取消日志应用alter database recover managed standby database cancel;4、备库执行如下SQL语句应用完成所有接收到的归档日志。alter database recover managed standby database finish force; 备注如果上面的命令出错无法解决错误例如gap间隙可以通过在目标备库上执行下面的SQL语句来强制切换会丢失一些数据 alter database activate physical standby database;5、确认目标备库已经准备好切换成主库SELECT SWITCHOVER_STATUS FROM V$DATABASE; TO PRIMARY或SESSIONS ACTIVE的值均表示目标备库已准备好切换到主库。如果返回结果不是这两个值大概率只能执行强制切换。6、目标备库上执行以下SQL语句将物理备库切换成主库alter database commit to switchover to primary with session shutdown;7、如果4-6步骤无问题则表示failover切换完成跳过这一步骤如果4-6步骤切换出现问题则只能强制切换会丢失一些数据alter database activate physical standby database;8、打开新主库sqlplus /as sysdba alter database open; 检查 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database;9、建议立即对新主库执行完整备份二12c版本开始新的failover方法旧方法也可以继续使用12c版本开始failover有新语方法如下1-3步骤保持不变4、目标备库上执行语法有变动ALTER DATABASE FAILOVER TO targe_db_name; 备注 1target_db_name用于指定目标备库的 DB_UNIQUE_NAME必须使用单引号括起来 如果上面的命令执行报错下面的命令忽略与原主库交互时遇到的任何故障并在可能的情况下继续进行failover如下 ALTER DATABASE FAILOVER TO targe_db_name FORCE;5、如果4步骤无问题则表示failover切换完成跳过这一步骤即可如果步骤4切换出现问题则只能强制切换会丢失一些数据alter database activate physical standby database6、打开新主库sqlplus /as sysdba alter database open; 检查 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database;7、建议立即对新主库执行完整备份三FAILOVER切换总结1、主库尽量将日志传输至备库中2、备库取消日志一个应用取消同步alter database recover managed standby database cancel;3、尝试常规切换常规切换不行、报错则直接使用步骤四alter database recover managed standby database finish force; alter database commit to switchover to primary with session shutdown; shutdown immediate;4、如果常规切换成功步骤四无需执行如果fail over时存在gap常规切换不起作用直接使用强制切换会丢失一部分数据alter database activate physical standby database; shutdown immediate;5、打开新主库startup;如果时间紧迫必须第一时间打开备库则上述步骤只需要执行2、4、5即可这是最快打开备库的方法四DG BROKER工具下的failover切换记录一下实际很少使用1、通过dg broker工具检查主备的同步状态show database verbose 目标主库; show database verbose 目标备库; show configuration verbose;返回结果中有报错尽量处理2、通过dg broker工具执行failover(主备库皆可执行)failover to 备库名称。
Oracle DG主备架构两种切换方式总结（switch over和fail over）
Oracle DG主备架构两种切换方式总结switch over和fail over一、DG主备正常切换即有计划的切换主备正常switch over切换即有计划的切换这种切换方式无数据丢失备注1Oracle10g版本RAC环境下主备都只能保留一个实例才能进行switch over否则不容许。而且备库上保留的实例必须是MPR进程存在的实例。211g版本以上上面的限制已经取消只需确保备库切换为主库时必须MPR进程存在的实例。如果条件允许保留一个节点来切换影响最小化1、查看主库状态是否能够进行switch over切换主库上执行 set linesize 200 pagesize 999 col open_mode format a10 select open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from v$database; 查询结果类似如下 OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR ---------- ---------------- -------------------- -------------------- -------------------- --- READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY YES根据SWITCHOVER_STATUS列判断是否可以切换To standby 或者session active表明可以进行切换session active表示主备库有活动的session关联在switchover前需要将这些session关闭执行切换命令时加上with session shutdown2、主库上发起切换命令切换为备库主库上执行 Alter database commit to switchover to standby with session shutdown ;这个命令完成后主库自动down集群环境所有节点都会自动停止3、备库切换为主库1如果备库是集群判断MPR日志应用进程在哪个节点需要在MPR进程上完成备库切换为主库备库上执行 查询MPR进程在哪个节点的语句 SELECT INST_ID,PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY where PROCESS like %MRP%;2在MPR节点上查看备库状态是否能够切换为主库备库上执行 set linesize 200 pagesize 999 col open_mode format a10 select open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from v$database; 查询结果类似如下 OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR -------------------- ---------------- -------------------- -------------------- -------------------- --- READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY YES根据SWITCHOVER_STATUS列判断是否可以切换TO PRIMARY表名可以进行切换3执行切换命令备库上执行 alter database commit to switchover to primary with session shutdown ;备注集群环境切换完成后所有节点都会变为mount状态数据库挂载状态4切换后检查备库状态set linesize 200 pagesize 999 col open_mode format a10 select open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from v$database; OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR ---------- ---------------- -------------------- -------------------- -------------------- --- MOUNTED PRIMARY UNPROTECTED MAXIMUM PERFORMANCE NOT ALLOWED YES5打开新的主库源备库alter database open;如果是集群环境所有节点都需要执行数据库打开操作这一点不要遗漏6新主库检查状态set linesize 200 pagesize 999 col open_mode format a10 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database; INST_ID OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR ---------- ---------- ---------------- -------------------- -------------------- -------------------- --- 2 READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE FAILED DESTINATION YES 1 READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE FAILED DESTINATION YES4、原先的主库再次打开后直接转变为standby db直接应用日志即可1打开原先的主库单库环境 sqlplus /as sysdba startup 集群环境 srvctl start database -d orcl2检查状态set linesize 200 pagesize 999 col open_mode format a10 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database; INST_ID OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR 2 READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE RECOVERY NEEDED YES 1 READ ONLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE RECOVERY NEEDED YES3执行日志应用alter database recover managed standby database using current logfile disconnect from session;4检查状态set linesize 200 pagesize 999 col open_mode format a10 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database; INST_ID OPEN_MODE DATABASE_ROLE PROTECTION_LEVEL PROTECTION_MODE SWITCHOVER_STATUS FOR 1 READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED YES 2 READ ONLY WITH APPLY PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE NOT ALLOWED YES至此switch over主备切换完成二、DG主备异常切换主库意外挂掉切换至备库接手对外服务主备异常切换特指fail over切换切换后主备环境破坏备库需要重新搭建大概率有数据丢失RAC环境中关闭除了运行MRP进程的其他实例然后再MRP存在的实例上执行。执行切换前备库最好是处于mount状态可以提高切换速度。一老版本切换方法12c开始有新语法但是老方法仍旧可用1、如果主库可以mount将主库中所有未到备库的归档日志和redo刷新到备库ALTER SYSTEM FLUSH REDO TO target_db_name; 备注 1target_db_name:用于指定目标备库的DB_UNIQUE_NAME必须使用单引号括起来 2语句必须在mount状态下执行 具体如下 ALTER SYSTEM FLUSH REDO TO orcl_st;2、如果主库不可以mount使用下面方式尽可能将主库数据同步至备库查询备库上的v$archivd_log视图以获取redo的最大sequence号。 su - oracle sqlplus /as sysdba set linesize 20o pagesize 999 col standby_tns_name format a30 select inst_id,max(sequence#) ,min(sequence#) from gv$archived_log where appliedNO group by inst_id order by 1; 将主库的最新归档日志传输到备库然后注册该归档文件 ALTER DATABASE REGISTER PHYSICAL LOGFILE 归档日志文件名绝对路径; 继续查询备库上的v$archived_GAP视图在确定备库上是否存在gap SELECT inst_id,THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM gV$ARCHIVE_GAP; 如果有gap,请将gap的归档日志从主库传输到备库然后在备库中继续注册归档文件。 ALTER DATABASE REGISTER PHYSICAL LOGFILE 归档日志文件名绝对路径; 重复执行直至所有的gap间隙都得到解决如果上面的1、2步骤都不能达成此问题不能解决则在failover期间可能会丢失一些数据。3、备库取消日志应用alter database recover managed standby database cancel;4、备库执行如下SQL语句应用完成所有接收到的归档日志。alter database recover managed standby database finish force; 备注如果上面的命令出错无法解决错误例如gap间隙可以通过在目标备库上执行下面的SQL语句来强制切换会丢失一些数据 alter database activate physical standby database;5、确认目标备库已经准备好切换成主库SELECT SWITCHOVER_STATUS FROM V$DATABASE; TO PRIMARY或SESSIONS ACTIVE的值均表示目标备库已准备好切换到主库。如果返回结果不是这两个值大概率只能执行强制切换。6、目标备库上执行以下SQL语句将物理备库切换成主库alter database commit to switchover to primary with session shutdown;7、如果4-6步骤无问题则表示failover切换完成跳过这一步骤如果4-6步骤切换出现问题则只能强制切换会丢失一些数据alter database activate physical standby database;8、打开新主库sqlplus /as sysdba alter database open; 检查 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database;9、建议立即对新主库执行完整备份二12c版本开始新的failover方法旧方法也可以继续使用12c版本开始failover有新语方法如下1-3步骤保持不变4、目标备库上执行语法有变动ALTER DATABASE FAILOVER TO targe_db_name; 备注 1target_db_name用于指定目标备库的 DB_UNIQUE_NAME必须使用单引号括起来 如果上面的命令执行报错下面的命令忽略与原主库交互时遇到的任何故障并在可能的情况下继续进行failover如下 ALTER DATABASE FAILOVER TO targe_db_name FORCE;5、如果4步骤无问题则表示failover切换完成跳过这一步骤即可如果步骤4切换出现问题则只能强制切换会丢失一些数据alter database activate physical standby database6、打开新主库sqlplus /as sysdba alter database open; 检查 select inst_id,open_mode,database_role,protection_level,protection_mode,switchover_status,force_logging from gv$database;7、建议立即对新主库执行完整备份三FAILOVER切换总结1、主库尽量将日志传输至备库中2、备库取消日志一个应用取消同步alter database recover managed standby database cancel;3、尝试常规切换常规切换不行、报错则直接使用步骤四alter database recover managed standby database finish force; alter database commit to switchover to primary with session shutdown; shutdown immediate;4、如果常规切换成功步骤四无需执行如果fail over时存在gap常规切换不起作用直接使用强制切换会丢失一部分数据alter database activate physical standby database; shutdown immediate;5、打开新主库startup;如果时间紧迫必须第一时间打开备库则上述步骤只需要执行2、4、5即可这是最快打开备库的方法四DG BROKER工具下的failover切换记录一下实际很少使用1、通过dg broker工具检查主备的同步状态show database verbose 目标主库; show database verbose 目标备库; show configuration verbose;返回结果中有报错尽量处理2、通过dg broker工具执行failover(主备库皆可执行)failover to 备库名称。
