11G、12C Data Guard Physical Standby Switchover转换参考手册

时间:2023-12-05 09:59:44
Switchover转换
Step 1: switchover 切换先前检查
(1)确保主备两端log_archive_config和db_unique_name参数都已经正确设置。
需要注意的是,主备两端db_unique_name不能一样。
(2)在备库检查MRP进程是否正在运行
SQL> SELECT PROCESS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
(3)在主库检查是否是REAL TIME APPLY模式
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
如果不是,需要执行如下:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
(4)在备库检查LOG_ARCHIVE_MAX_PROCESSES 参数是否为4或者更高
SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES
(5)检查备库online redo log 文件已经被清除
SQL> SELECT DISTINCT L.GROUP# FROM V$LOG L, V$LOGFILE LF
WHERE L.GROUP# = LF.GROUP#
AND L.STATUS NOT IN ('UNUSED', 'CLEARING','CLEARING_CURRENT');
SQL>ALTER DATABASE CLEAR LOGFILE GROUP <ORL GROUP# from the query above>;
如果报如下错误:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
ALTER DATABASE CLEAR LOGFILE GROUP 2
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files
需要 执行 recover managed standby database cancel;
更改之后再执行 recover managed standby database using current logfile disconnect;
确定备库db_file_name_convert、log_file_name_convert两个参数已经设置,若未设置,可能会报如下错误:
SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 3 thread 1: '/data/crfdb11/redo03.log'
(6)确保没有大的GAP产生
在主库查询当前的归档序列
SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;
验证备库已应用到。
SQL> SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED = 'YES' AND RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION WHERE STATUS = 'CURRENT') GROUP BY THREAD#;
(7)检查备库临时表空间是否存在
SQL> SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACE FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;
(8)检查备库数据文件表空间是否有offline的
SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS='OFFLINE';
SQL> ALTER DATABASE DATAFILE ‘datafile-name’ ONLINE;
Step 2: 主库JOB的处理
SQL> SELECT * FROM DBA_JOBS_RUNNING; 
SQL> SELECT OWNER, JOB_NAME, START_DATE, END_DATE, ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE ENABLED='TRUE'
AND OWNER <> 'SYS';
SQL> SHOW PARAMETER job_queue_processes
SQL> ALTER SYSTEM SET job_queue_processes=0 SCOPE=BOTH SID=’*’;
SQL> EXECUTE DBMS_SCHEDULER.DISABLE( <job_name> );
Step 3: 停止中间件
Step 4: 查看主库switchover 状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
附: A:switchover_status出现session active/not allowed
当出现session active的时候表示还有活动的session,则运行
Alter database commit to switchover to physical standby with session shutdown;
当出现not allowed时,在官方文档说转换会不成功,但是我测试的时候成功了。
B.ora- 01153: an incompatible media recovery is active
运行下面代码
Alter database recover managed standby database disconnect from session;
Step 5: 将主库切换成备库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;
Step 6: 查看备库 switchover 状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
TO PRIMARY
附:若不是用此语句切换:
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown
补充:若出现:ORA-16139: media recovery required
是因为没有执行:
alter database recover managed standby database disconnect from session;
Step 7: 将备库切换成主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Step 8: 打开新主库
SQL> ALTER DATABASE OPEN;
Step 9: 更正不匹配的临时文件
SQL> SELECT TMP.NAME FILENAME, BYTES, TS.NAME TABLESPACE
FROM V$TEMPFILE TMP, V$TABLESPACE TS
WHERE TMP.TS#=TS.TS#;
Step 10: 重启新备库
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
 SQL> alter database open read only;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Step 11: 更改服务器IP地址
这个可以用xmanager 连过去,在界面上改,也可以直接用命令来修改。
vi /etc/sysconfig/network-scripts/ifcfg-eth0
/etc/init.d/network restart
Step 12: JOB的处理
SQL> ALTER SYSTEM SET job_queue_processes=<value> scope=both sid=’*’
SQL> EXECUTE DBMS_SCHEDULER.ENABLE(<for each job name>);
Step 13: 在主库查看log_archive_dest 参数的状态是否有效
因为这里与监听和启动顺序有关,如果值为error,则归档不能传送到备库。
SQL>select status,database_mode from v$archive_dest_status;
Status状态为valid即可。 如果有错误,一般都是启动顺序和监听问题。如果启动顺序没有问题,那么就把主库重新启动一下。
Step 14: 修改参数及tnsnames.ora文件
参考搭建文档在主库和备库修改以下参数
主库先修改此参数:
alter system set db_unique_name='orcl' scope=spfile;
重启后再修改下面参数:
alter system set log_archive_dest_1='location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=orcl' sid='*';
alter system set log_archive_dest_2='service=standby lgwr async COMPRESSION=ENABLE valid_for=(online_logfiles,primary_role) db_unique_name=standby' scope=both;
alter system set fal_server=orcl scope=both sid='*';
alter system set log_archive_config='dg_config=(orcl,standby)' scope=both sid='*';
备库先修改此参数:
alter system set db_unique_name='standby' scope=spfile;
重启后再修改下面参数:
alter system set log_archive_dest_1='location=/data/arch valid_for=(all_logfiles,all_roles) db_unique_name=standby' sid='*';
alter system set log_archive_dest_2='' scope=both;
alter system set fal_server=orcl scope=both sid='*';
alter system set log_archive_config='dg_config=(orcl,standby)' scope=both sid='*';
修改参数完之后,修改 tnsnames.ora文件,把 orcl 和 STANDBY 的 HOST对换。
Step 15: 验证同步状态
在主库运行:
SQL> alter system switch logfile;
然后在主备库分别运行:
SQL>select sequence#,applied from v$archived_log;
确保applied都为YES。
如果用:
SQL> select max(sequence#) from v$archived_log;
查看,如果主备库显示的一致,则只能表明归档已经传送到了备库,并不能表明该归档已经应用。而applied 状态为YES,就表明该归档已经应用到了备库。
如果等了很长时间applied 都无法应用,可以尝试一下方法:
SQL>alter database recover managed standby database cancel;
SQL>alter database recover managed standby database disconnect from session;
这2个语句是取消recover manager,在启动它。
Switchover切换失败处理步骤
Step 1: 先关闭主库
SQL>shutdown immediate;
Step 2: 关闭备库
SQL>recover managed standby database cancel;
SQL>shutdown immediate;
Step 3: 在主库上的操作
SQL>startup mount;
SQL> alter database commit to switchover to primary;
SQL>alter database open;
SQL>alter system set log_archive_dest_2='service=standby_db' scope=both;
Step 4: 在备库上的操作
SQL>startup mount;
SQL>alter database recover managed standby database disconnect from session;
Step 5: 主库切换日志,在备库上的alert日志上查看是否成功。