Oracle 19c Physical Standby Switchover 最佳实践

备注：文末包含 Failover 步骤及示例。

1、环境检查阶段

-- 确认是否安装最新的 PSU/Bundle 补丁：Primary Note for Database Proactive Patch Program (Doc ID 888.1)

-- 安装和配置检查：确认主备的数据库版本一致、确认 alert 日志无报错。
    
-- 在主备库执行以下查询无报错记录
    
select * from v$database_block_corruption;

select * from v$nonlogged_block;
        
-- 在备库确认是否同步（ 检查 apply lag 和 transport lag 均为 null ）
    
col source_db_unique_name for a10
col name for a15
col value for a15
col unit for a30
col time_computed for a20
col datum_time for a20
set lines 300
--select * from gv$dataguard_stats where name in ('apply lag','transport lag') order by 1;
select name,value from gv$dataguard_stats where name in ('apply lag','transport lag') order by 1;

-- 检查主库 redo 日志传输状态

col dest_name for a20
col destination for a20
col error for a10
col alternate for a10
col type for a10
col status for a10
col valid_type for a15
col valid_role for a15
set lines 1000
select dest_name,destination,error,alternate,type,status,valid_type,valid_role from v$archive_dest where status <>'INACTIVE';

-- 检查主库最新的归档日志

select thread#, max(sequence#) "Last Primary Seq Generated" from gv$archived_log val, gv$database vdb
where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

-- 检查备库收到的主库最新的归档日志（ RAC 显示每一个 thread 的结果 ）

select thread#, max(sequence#) "Last Standby Seq Received" from gv$archived_log val, gv$database vdb
where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

-- 检查备库应用的最新归档日志序列号

select thread#, max(sequence#) "Last Standby Seq Applied" from gv$archived_log val, gv$database vdb
where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;  

-- 检查主备库初始化参数

log_archive_config : should include primary and standby database 
                    (if multiple standby databases are existing, then all the standby database details should be included)
fal_server         : remote server from where archivelog can be fetched
db_unique_name     : unique name under this configuration
log_archive_dest_n : for remote database to send archives
compatible         ：主备库设置一致

如果主备库的数据文件和 redo 文件的路径不同则需要设置参数 db_file_name_convert 和 log_file_name_convert。

2、Pre-Switchover 阶段：

-- 检查备库 redo 和 归档日志应用正常无 gap

检查备库应用的最新归档日志序列号（ 可能不包括主库当前的 sequence 号 详见视图 v$archived_log ）

select thread#, max(sequence#) "Last Standby Seq Applied" from gv$archived_log val, gv$database vdb
where val.resetlogs_change# = vdb.resetlogs_change# and val.applied in ('YES','IN-MEMORY') group by thread# order by 1;
              
检查 MRP（ Managed Recovery Process ）进程状态

select inst_id,process from gv$managed_standby where process like 'MRP%';      
或
host ps -ef|grep -i mrp|grep -v grep

检查数据文件和临时文件的状态（ 主备库 ）

SQL> SELECT NAME FROM V$DATAFILE WHERE STATUS=’OFFLINE’;
SQL> ALTER DATABASE DATAFILE 'datafile-name' ONLINE;

select tf.name filename, bytes, ts.name tablespace from v$tempfile tf, v$tablespace ts where tf.ts#=ts.ts#;

检查 Online 和 standby 的 redo 文件

set lines 150
col member for a50
select a.thread#,a.group#,a.bytes,a.blocksize,b.type,a.status,b.member from v$log a,v$logfile b where a.group#=b.group#;
select s.thread#,s.group#,s.status,s.bytes,l.type,l.member from v$logfile l,v$standby_log s where s.group#=l.group#;

注意 Standby redo 文件状态是 UNASSIGNED 或 ACTIVE。

主库检查归档日志状态（ 备库是否存在 gap ）

select status, gap_status from v$archive_dest_status where dest_id = 2;

如果延时被设置则需修改为实时
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;