Switchover和Failover切换方案(实战)

数据库ADG切换方案
=========================================================================
#################准备工作#################
1.-- Primary & Standby 应使用相同版本的RDBMS
--看psu:
opatch lspatches--少,看大版本。
opatch lsinventory --多,全。

--看版本号:
select * from v$version;

--看组件:dba_registry_history
set pagesize500
set linesize 120
col COMP_NAME for a40
col STATUS for a30
col VERSION for a30
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version 
from dba_registry order by comp_name;

2.-- 检查归档是否正常
crontab -l
cd /home/oracle/del_arch/log
ls -lrt
cat del_arch_202310111406.log

3.-- 检查alter日志(主备库都检查)
tail -200f `adrci exec='show homepath'| grep "diag/rdbms/"|grep  $ORACLE_SID | awk  '{print  homepath"/"$1"/trace"}' homepath=$ORACLE_BASE|awk '{print $1"/alert_"oraclesid".log"}' oraclesid=$ORACLE_SID`

$ grep "ORA-" alert_<ORACLE_SID>.log 
$ tail -600 alert_<ORACLE_SID>.log  | grep "ORA-"

4.-- 检查数据文件(主备库都检查)
set lines 222 pages 222
col FILE_NAME for a50 
col TABLESPACE_NAME for a15
select FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 m,STATUS from dba_temp_files;

5.-- 主备库检查online logfile
set lines 222 pages 222
col member for a50
col status for a10
select a.thread#,a.group#,b.type,b.member,a.bytes/1024/1024 size_mb,a.status 
from v$log a,v$logfile b 
where a.group#=b.group#;

6.-- 主备库检查standby logfile
select a.thread#,a.group#,b.type,b.member,a.bytes/1024/1024 size_mb,a.status 
from v$standby_log a,v$logfile b 
where a.group#=b.group# 
order by 3,1,2;

7.-- 主备库状态检查
set lines 222 pages 222 
col protection_mode for a22 
col flashback_on for a12 
col open_mode for a20 
col log_mode for a20 
col database_role for a16 
col switchover_status for a15
col log_mode for a12 
select inst_id,name,open_mode,protection_mode,flashback_on,database_role,switchover_status from gv$database order by 1;

8.-- 主备库归档路径检查
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

SELECT thread#, dest_id, a.status, error, fail_sequence
  FROM gv$archive_dest a, gv$instance b
 WHERE a.inst_id = b.inst_id
   AND destination is NOT NULL
 ORDER BY thread#, dest_id;

--重点关注ERROR字段的输出信息,如果重做日志传输和应用有问题,就会有相关错误信息提示。

9.-- 主库检查是否存在GAP
select dest_name,status, gap_status 
from v$archive_dest_status 
where status<>'INACTIVE' 
order by dest_name;

10.-- 主库检查日志传输应用情况
select 'Primary :' "DB  Role", dest_id, thread#, max(sequence#)
  from v$archived_log
 where standby_dest = 'NO'
   and dest_id = 1
 group by dest_id, thread#
union
select 'Standby :' "DB  Role", dest_id, thread#, max(sequence#)
  from v$archived_log
 where standby_dest = 'YES'
   and applied = 'YES'
 group by dest_id, thread#
 order by thread#;

11.-- 备库检查日志应用情况
col process for a10
col status for a15
col client_process for a10
col client_pid for a10
col group# for a5
col sequence# for 9999999
col block# for 9999999
col blocks for 9999999
col delay_mins for 9999999
col thread# for 99
select process,pid,status,client_process,client_pid,group#,thread#,sequence#,block#,blocks,delay_mins 
from v$managed_standby;

12.-- 备库检查数据同步状态
set lines 222 pages 222
col name for a32
col value for a22
col time_computed for a25
col datum_time for a25
select name,value,time_computed,datum_time from v$dataguard_stats;

13.-- 备库检查坏块
select * from v$database_block_corruption; 
select * from v$nonlogged_block;
--注意：没有记录表示正常,如果有相关记录返回,则表示数据库存在坏块。

14.-- 主库检查数据库会话链接状态
select username,count(0) from gv$session group by username;

如果如果需要清理session可以使用以下命令
--批量kill session
set line 200 pages 1000
select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''';' from gv$session 
where username = '$username'
order by last_call_et;

15.-- 查询主机名
select username,machine,count(*) 
from gv$session 
group by username,machine 
order by count(*);

 
16.-- 验证初始化参数
主库(primary)&备库(standby)检查:验证参数
show parameter log_archive_config;
show parameter fal_server;
show parameter fal_client;
show parameter db_unique_name;
show parameter log_archive_dest_;
show parameter compatible;
show parameter db_file_name_convert;
show parameter log_file_name_convert;
show parameter pdb_file_name_convert;
或者
set linesize 720 pagesize 60
col name for a30
col value for a120
SELECT 
    NAME,
    VALUE 
FROM 
    V$PARAMETER 
WHERE 
    NAME IN (
        'db_unique_name',
        'log_archive_config',
        'log_archive_dest_1',
        'log_archive_dest_2',
        'log_archive_dest_state_1',
        'log_archive_dest_state_2',
        'remote_login_passwordfile',
        'log_archive_format',
        'standby_file_management',
        'compatible',
        'fal_server',
        'db_file_name_convert',
        'log_file_name_convert'
        )
order by name; 

注意事项：--要确保兼容参数 compatible 和 noncdb_compatible 在主备上一致。

17.--最后切换主库归档
alter system switch logfile; --两个节点各执行2次

#################'一.切换步骤switchover'#################
=========================================================================
--主切备
=========================================================================
1.--停止主、备库第二节点
Shutdown immediate;

2.--主库状态检查
set lines 222 pages 222 
col protection_mode for a22 
col flashback_on for a12 
col open_mode for a20 
col log_mode for a20 
col database_role for a16 
col switchover_status for a15
col log_mode for a12 
select inst_id,name,open_mode,protection_mode,flashback_on,database_role,switchover_status 
from gv$database order by 1;

3.--主库切换为备库(切换1节点执行,启停需所有集群节点都执行)
## 如果 switchover_status 为 TO STANDBY 则执行 ##
alter database commit to switchover to physical standby;
startup;

## 如果 switchover_status 为 SESSIONS ACTIVE 则执行 ##
alter database commit to switchover to standby with session shutdown;
startup;

4.--启动主库第二节点
startup;

=========================================================================
--备切主
=========================================================================
1.--备库状态检查
set lines 222 pages 222 
col protection_mode for a22 
col flashback_on for a12 
col open_mode for a20 
col log_mode for a20 
col database_role for a16 
col switchover_status for a25 heading "SWITCHOVER |STATUS"
col force_logging for a8
col dataguard_broker for a8 heading "DATAGUARD|BROKER"
col guard_status for a8
col log_mode for a12 
select inst_id,name,open_mode,protection_mode,flashback_on,database_role,switchover_status,force_logging,dataguard_broker,guard_status 
from gv$database order by 1;             
                                                                                    SWITCHOVER               DATAGUAR
   INST_ID NAME    OPEN_MODE   PROTECTION_MODE        FLASHBACK_ON DATABASE_ROLE    STATUS         FORCE_LO  BROKER     GUARD_ST
---------- ------  ----------  -------------------  ------------ -------------- -----------  -------   --------   --------
         1 FWEIDB  READ WRITE  MAXIMUM PERFORMANCE  NO             PRIMARY        TO STANDBY   YES       DISABLED   NONE
         2 FWEIDB  READ WRITE  MAXIMUM PERFORMANCE  NO             PRIMARY        TO STANDBY   YES       DISABLED   NONE
         
--检查主库下switchover_status字段,如果值为"session active"或者"to standby", 则主数据库角色可以切换为备库角色。

2.--备库切换为主库(切换可在1节点执行,启停需所有集群节点都执行)
## 如果 switchover_status 为 to_primary 则执行 ##
alter database commit to switchover to primary;
alter database open;

## 如果 switchover_status 为 sessions active 则执行 ##
alter database commit to switchover to primary with session shutdown;
alter database open;

3.--启动备库第二节点
startup;

4.--备库应用日志
alter database recover managed standby database using current logfile disconnect from session;

=======================检查=================================
1.--查看备库是否有gap
select * from v$archive_gap;

2.--主、备库上执行对比时间小于5秒:
select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

3.--主、备库上查询max(sequence)#:
select max(sequence) from v$archive_log;
SELECT thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
select sequence#,name,standby_dest,applied,deleted from v$archived_log where archived='YES' order by sequence# desc;

4.--获取主、备库上的信息以及保护模式(主备库执行)
set line 200 pagesize 99
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col log_mode for a10
col name for a10
col switchover_status for a20
col database_role for a20
col open_mode for a20
col protection_mode for a20
select name,database_role,resetlogs_time,log_mode,open_mode,protection_mode,remote_archive,switchover_status,force_logging 
from v$database;

5.--备库：查询managed_standby:
select process,group#,thread#,sequence#,client_process from v$managed_standby;
select process,client_process,status,thread#,sequence#,block#,client_process from v$managed_standby;

6.--备库：检查其他两个备库的数据同步情况
set lines 222 pages 222
col name for a32
col value for a22
col time_computed for a25
col datum_time for a25
select name,value,time_computed,datum_time from v$dataguard_stats;

#################'二.切换步骤failover'#################
1.备库关闭第二节点
shutdown immediate;

2.备库状态检查
col switchover_status for a30
col open_mode for a20
col database_role for a20
set lin 200 pages 2000
select switchover_status,open_mode,database_role from gv$database;

3.备库断开实时同步
alter database recover managed standby database cancel;

4.备库开启mrp,再次确认数据同步
alter database recover managed standby database using current logfile disconnect from session;

5.切换状态
alter database recover managed standby database finish force;

##再次检查状态及角色##
select switchover_status,open_mode,database_role from v$database;

##如下状态则正常##
SWITCHOVER_STATUS              OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- --------------------
TO PRIMARY                     MOUNTED              PHYSICAL STANDBY

6.切换角色
alter database commit to switchover to primary with session shutdown;

##再次检查状态及角色##
select switchover_status,open_mode,database_role from v$database;

##如下状态则正常##
SWITCHOVER_STATUS              OPEN_MODE            DATABASE_ROLE
------------------------------ -------------------- --------------------
NOT ALLOWED                    MOUNTED              PRIMARY

7.打开数据库

##节点1#    
alter database open;

##节点2##
startup

##再次检查状态及角色##
select switchover_status,open_mode,database_role from gv$database;

##如下状态则正常##
SWITCHOVER_STATUS              OPEN_MODE                      DATABASE_ROLE
------------------------------ ------------------------------ ------------------------
FAILED DESTINATION             READ WRITE                     PRIMARY

8.备库检查同步
set lines 222 pages 222
col name for a32
col value for a22
col time_computed for a25
col datum_time for a25
select name,value,time_computed,datum_time from v$dataguard_stats;

=======================检查=================================
1.--查看备库是否有gap
select * from v$archive_gap;

2.--主、备库上执行对比时间小于5秒:
select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss') from v$database;

3.--主、备库上查询max(sequence)#:
select max(sequence) from v$archive_log;
SELECT thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
select sequence#,name,standby_dest,applied,deleted from v$archived_log where archived='YES' order by sequence# desc;

4.--获取主、备库上的信息以及保护模式(主备库执行)
set line 200 pagesize 99
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
col log_mode for a10
col name for a10
col switchover_status for a20
col database_role for a20
col open_mode for a20
col protection_mode for a20
select name,database_role,resetlogs_time,log_mode,open_mode,protection_mode,remote_archive,switchover_status,force_logging 
from v$database;

5.--备库：查询managed_standby:
select process,group#,thread#,sequence#,client_process from v$managed_standby;
select process,client_process,status,thread#,sequence#,block#,client_process from v$managed_standby;

6.--备库：检查其他两个备库的数据同步情况
set lines 222 pages 222
col name for a32
col value for a22
col time_computed for a25
col datum_time for a25
select name,value,time_computed,datum_time from v$dataguard_stats;