RAC集群环境 dataguard dg 主备切换详细步骤

oracle dg 切换整理文档进行记录一下，如有帮助，感谢老铁关注+点赞，制作不易！！！

1、切换前准备

1.1 检查主备集群、监听、实例状态

su - grid   
crsctl status res -t
lsnrctl status
select INST_ID,NAME,open_mode,LOG_MODE,DATABASE_ROLE,PROTECTION_MODE,DB_UNIQUE_NAME from  gv$database;

1.2 检查主备ADG参数

show parameter create_dest
show parameter standby_file_management
show parameter fal_server
show parameter log_archive_config
show parameter log_archive_dest_2
show parameter log_archive_dest_2_state

1.3 检查主备同步状态

select thread#,low_sequence#,high_sequence# from v$archive_gap;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');
select * from v$standby_log;
select process,status,sequence# from v$managed_standby;

1.4 检查主备数据文件状态

col FILENAME for a50
SELECT TMP.NAME FILENAME, BYTES/1024/1024 M, TS.NAME TABLESPACE FROM V$TEMPFILE TMP, V$TABLESPACE TS WHERE TMP.TS#=TS.TS#;
 
SELECT NAME FROM V$DATAFILE WHERE STATUS='OFFLINE';
如果存储offline的数据文件，且是切换为主库所需要的数据文件，需要online
SQL> ALTER DATABASE DATAFILE 'datafile-name' ONLINE;

1.5 检查主备switchover_status

主库 switchover_status状态，正常结果为TO STANDBY或SESSION ACTIVE
select switchover_status from v$database;
如果switchover_status为TO_STANDBY说明可以直接转换
alter database commit to switchover to physical standby;
如果switchover_status为SESSIONS ACTIVE ，但是查询V$SESSION会话，都是系统会话，可以通过如下命令在主库进行SWITCHOVER切换。
alter database commit to switchover to physical standby with session shutdown;

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
----------------------------------
TO STANDBY 或者SESSIONS ACTIVE
 
--备库状态
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
NOT ALLOWED

2、 开始切换

2.1 关闭应用并确认当前连接会话

select username,sid,status,event,program,machine,sql_id,logon_time from gv$session where username !='SYS' order by logon_time desc;
##系统层面kill远程连接
ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}' |xargs kill -9

2.2 主备库开启闪回并添加闪回点

主备执行：
alter system set db_recovery_file_dest_size=500G;
alter system set db_recovery_file_dest='+DATA';
alter database flashback on;
主库执行
select current_scn from v$database;
create restore point PRI_DB guarantee flashback database; 
select * from v$restore_point;  -记录闪回点scn

2.3 再次检查主备switchover_status

主库归档当前redo，两节点执行5次以上；
ALTER SYSTEM ARCHIVE LOG CURRENT ;
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
----------------------------------
TO STANDBY 或者SESSIONS ACTIVE
 
--备库状态
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
 
SWITCHOVER_STATUS
--------------------
NOT ALLOWED

2.4 主库关闭实例2

srvctl stop instance -d djsfdb -i djsfdb2 -o immediate

2.5 备库关闭实例2

srvctl stop instance -d djsfdbdg  -i djsfdb2 -o immediate

2.6 主库切换到standby

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2.7 验证备库的切换状态

SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY

2.8 切换备库为主库

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SQL>alter database open;

2.9 新备库重启监听

新备库此前配置静态监听后未重启，需要重启监听
su - grid
lsnrctl stop
lsnrctl start

2.10 打开新备库的日志同步进程

SQL> startup mount
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

2.11 验证切换后的结果

主库进行日志切换，查看备库的日志，看是否开始接收并应用。

2.12 备库数据库打开

SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> alter database open;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
column name format a13;
column value format a20;
column unit format a30;
column TIME_COMPUTED format a30;
select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');
select * from v$standby_log;
select process,status,sequence# from v$managed_standby;

2.13 打开RAC的另一个节点

主库和备库的另一个节点都可以打开。
startup

2.14 检查修改DBLINK

select * from dba_db_links;

2.15 修改scanip

当前/etc/hosts配置
主库：10.xx.xx.150 cxsfdb-scan
备库：10.xx.xx.177 cxsfdb1-scan
新备库：
在集群正常运行的情况下，首先修改新备库所有节点/etc/hosts文件到临时IP地址10.5.170.xx
10.5.170.xx cxsfdb-scan
srvctl config scan
srvctl stop scan_listener
srvctl stop scan
srvctl modify scan -n cxsfdb-scan
srvctl config scan
srvctl start scan
srvctl start scan_listener
#####新主库scanip修改完成后，新备库/etc/hosts中修改为
10.5.170.xxx cxsfdb-scan
新主库：
修改新主库所有节点/etc/hosts文件为：
10.5.170.xxx cxsfdb1-scan
srvctl config scan
srvctl stop scan_listener
srvctl stop scan
srvctl modify scan -n cxsfdb1-scan
srvctl config scan
srvctl start scan
srvctl start scan_listener

2.16 应用连接

SCANIP修改完成后，应用无需变更连接信息，启动应用进行验证

3、其他注意事项

3.1、主备库补丁版本差异

主库为19.20，备库为19.24，后续切换后的新备库如果确认继续作为灾备长期使用，建议将软件版本升级至19.24，之后在新主库环境运行以下语句，将补丁信息应用到数据库中：
cd $ORACLE_HOME/OPatch
./datapatch -verbose

3.2、主备切换失败

主备切换失败的情况，使用闪回点恢复原主备环境
原主库：
sqlplus / as sysdba;
shutdown immediate;
startup mount;
select * from v$restore_point;
flashback database to restore point PRI_DB;
alter database open resetlogs;
原备库：
flashback database to scn SCN_LESS_THAN_PRI;  --指定闪回点PRI_DB的SCN-1的SCN号执行闪回
##Oracle19c闪回点的创建回同步至备库中，此处也可闪回至备库闪回点
alter database open;
recover managed standby database using current logfile disconnect from session;