DG切换迁移Switch over切换文档

本篇文档，整体结构：
1.切换前检查确认
2.DG切换角色操作
3.切换后检查确认

目录：
一、切换前检查确认
1.数据库版本
2.DG架构，IP信息及切换角色前后
3.DG切换方式，switch over or fail over
4.DG迁移是否需要修改vip and scan or service_name ........（本质考虑是切换后应用的连接方式）
5.DG连通性，是否正常，主库switch_status is to standby database
6.DG相关参数，及切换后准备参数修改脚本
7.DG备库的数据文件路径
8.检查DG备库的口令文件，参数文件，sqlnet.ora配置文件是否正常
9.备库监听配置文件，切换后tnsnames.ora配置提前配置
10.crontab操作系统定时任务是否迁移

二、DG切换角色操作
1.观察主备延迟确认连通性OK
2.原主库切换角色被备库
3.将指定的备库切换为新主库
4.如果存在VIP修改，进行相关操作
5.如果存在crontab，可以进行迁移或者注释解除

三、切换后检查确认
1.新主库修改远程归档参数，传输归档至新的备库
2.新备库修改相关参数，接受并应用新的主库远程归档参数
3.确认DG连通性

一、切换前检查确认
1.数据库版本
SQL>sqlplus / as sysdba

2.DG架构，IP信息及切换角色前后
$cat /etc/hosts
SQL> show parameter log_archive_config
SQL> show parameter archive
$tnsping tnsnames_name
SQL> select dbid,name, database_role,open_mode,SWITCHOVER_STATUS,PROTECTION_MODE,FORCE_LOGGING from v$database;

3.DG切换方式，switch over or fail over
switch over

4.DG迁移是否需要修改vip and scan or service_name ........（本质考虑是切换后应用的连接方式）
否，应用修改连接IP，service_name也无需修改，应用程序修改连接数据库方式

5.DG连通性，是否正常，主库switch_status is to standby database
primary
SQL> select thread#,sequence# from v$log where status='CURRENT';
standby
SQL> select DEST_ID,THREAD#,max(SEQUENCE#) from v$archived_log where APPLIED='YES' group by DEST_ID,THREAD#;
primary
SQL> alter system archive log current;
standby
SQL> select DEST_ID,THREAD#,max(SEQUENCE#) from v$archived_log where APPLIED='YES' group by DEST_ID,THREAD#;
SQL> select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby;
SQL> select name from v$archived_log where (thread#,sequence#) in (select THREAD#,max(SEQUENCE#) from v$archived_log where dest_id=1 group by thread#);

6.DG相关参数，及切换后准备参数修改脚本
SQL> show parameter archive
log_archive_config
log_archive_dest_1 ......
log_archive_dest_state_1 ......
SQL> show parameter fal
fal_client
fal_server
SQL> show parameter convert
db_file_name_convert
log_file_name_convert
SQL> show parameter manage
standby_file_management【AUTO】
SQL> show parameter pass
remote_login_passwordfile【EXCLUSIVE】

7.主备文件路径
SQL> select name from v$datafile union select name from v$datafile;
SQL> select member from v$logfile;

8.检查DG备库的口令文件，参数文件，sqlnet.ora配置文件是否正常
--sqlnet.ora 检测，与主库一致，无需修改
--[oracle@cjptdb2 admin]$ cat sqlnet.ora
-#SQLNET.ALLOWED_LOGON_VERSION=8
-SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
-SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
口令文件主库scp
参数文件，切换后为新的主库RAC，需要关注RAC参数：
---*.cluster_database=true
---*.compatible='12.2.0'
---xxx1.instance_number=1
---xxx2.instance_number=2
---xxx1.thread=1
---xxx2.thread=2
---xxx1.undo_tablespace='UNDOTBS1'
---xxx2.undo_tablespace='UNDOTBS2'

9.备库监听配置文件，切换后tnsnames.ora配置提前配置
提前准备修改

10.crontab操作系统定时任务是否迁移
$crontab -l

二、DG切换角色操作
1.观察主备延迟确认连通性OK
SQL> select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');
SQL> select database_role,switchover_status from v$database;
SQL>alter system checkpoint global;

2.原主库切换角色被备库
1) TO STANDBY 状态主库切换到备库：
SQL>alter database commit to switchover to physical standby;

2) SESSION ACTIVE 状态主库切换到备库：
SQL>alter database commit to switchover to physical standby with session shutdown;

3.将指定的备库切换为新主库
备库确认是否为可切换状态（TO PRIMARY 或 SESSION ACTIVE状态可切换）：
select database_role,switchover_status from v$database;
1) TO PRIMARY 状态备库切换到主库：
alter database commit to switchover to primmary;
alter database open;
2) SESSIONN ACTIVE 状态备库切换到主库：
alter database commit to switchover to primary with session shutdown;
alter database open;

4.如果存在VIP修改，进行相关操作
5.如果存在crontab，可以进行迁移或者注释解除

三、切换后检查确认
1.新主库修改远程归档参数，传输归档至新的备库
SQL>alter system set log_archive_dest_2='SERVICE=cjptdb1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=cjptdb';
检查归档线程状态
log_archive_dest_state_2 enable

2.新备库修改相关参数，接受并应用新的主库远程归档参数
SQL>alter system set db_file_name_convert='+DATA/DATAFILE/','/u01/oradata/','+DATA/TEMPFILE/','/u01/oradata/' scope=spfile;
SQL>alter system set log_file_name_convert='CJPTDG','CJPTDB' scope=spfile;

观察告警日志、备库 v$archived_log 视图、主库 archive log list信息，确定日志能在备库应用。
SQL>alter database recover managed standby database disconnect from session;

3.确认DG连通性
SQL>select * from v$archive_gap;
SQL>select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag');
SQL>select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby;
SQL>set linesize 200
SQL>col dest_name for a40
SQL>select DEST_NAME , STATUS , RECOVERY_MODE from v$archive_dest_status;
SQL>select dest_id,error from v$archive_dest;

primary
SQL> select thread#,sequence# from v$log where status='CURRENT';
standby
SQL> select DEST_ID,THREAD#,max(SEQUENCE#) from v$archived_log where APPLIED='YES' group by DEST_ID,THREAD#;
primary
SQL> alter system archive log current;
standby
SQL> select DEST_ID,THREAD#,max(SEQUENCE#) from v$archived_log where APPLIED='YES' group by DEST_ID,THREAD#;
SQL> select process,client_process,sequence#,status,BLOCK#,BLOCKS from v$managed_standby;
SQL> select name from v$archived_log where (thread#,sequence#) in (select THREAD#,max(SEQUENCE#) from v$archived_log where dest_id=1 group by thread#);