手动switchover 常用于主备库轮流切换打补丁,或者正常的主备库切换演练。切换前务必检查当前主库与备库的归档是否是同步的,确认同步正常后再执行切换。
一. 环境信息
DATABASE_ROLE | DB_NAME | IPADDR | OS Version | DB version |
Primary | wafu | 192.168.40.56 | Red Hat7.6 | 11.2.0.4.0 |
Standby | fuwa | 192.168.40.55 | Red Hat7.6 | 11.2.0.4.0 |
二. 检查当前环境配置及状态
2.1. 查看当前主库参数配置
select database_role,protection_mode,protection_level,open_mode from v$database;
archive log list
show parameter log_archive_config
show parameter log_archive_dest_2
show parameter LOG_FILE_NAME_CONVERT
show parameter DB_FILE_NAME_CONVERT
show parameter FAL_SERVER
详细过程如下:
--查看主库状态
select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE
--查看主库参数
idle 26-JUL-24> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(wafu,fuwa)
idle 26-JUL-24> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=fuwa ASYNC
VALID_FOR=(ONLINE_LOGFILES,P
RIMARY_ROLE)
DB_UNIQUE_NAME=fuwa
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
idle 26-JUL-24> show parameter LOG_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string fuwa, wafu
idle 26-JUL-24> show parameter DB_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string fuwa, wafu
idle 26-JUL-24> show parameter FAL_SERVER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_server string fuwa
2.2. 查看当前备库参数配置
select database_role,protection_mode,protection_level,open_mode from v$database;
show parameter log_archive_config
show parameter log_archive_dest_2
show parameter LOG_FILE_NAME_CONVERT
show parameter DB_FILE_NAME_CONVERT
show parameter FAL_SERVER
详细过程如下:
--查看备库状态
select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY WITH APPLY
--查看备库参数
idle 2024-07-26 16:11:42> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string DG_CONFIG=(fuwa,wafu)
idle 2024-07-26 16:12:43> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string SERVICE=wafu ASYNC
VALID_FOR=(ONLINE_LOGFILES,P
RIMARY_ROLE)
DB_UNIQUE_NAME=wafu
log_archive_dest_20 string
log_archive_dest_21 string
log_archive_dest_22 string
log_archive_dest_23 string
log_archive_dest_24 string
log_archive_dest_25 string
log_archive_dest_26 string
log_archive_dest_27 string
log_archive_dest_28 string
log_archive_dest_29 string
idle 2024-07-26 16:12:48> show parameter LOG_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_file_name_convert string wafu, fuwa
idle 2024-07-26 16:13:06> show parameter DB_FILE_NAME_CONVERT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string wafu, fuwa
idle 2024-07-26 16:13:10> show parameter FAL_SERVER
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_server string wafu
2.3. 查看主备日志同步情况
2.3.1.主备库查archive log list
archive log list
详细过程如下:
--主库
idle 2024-07-26 16:29:25> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oradata/archivelog/
Oldest online log sequence 91
Next log sequence to archive 92
Current log sequence 92 #记住这个和备库对比,备库和主库一致说已实时同步
--备库
idle 2024-07-26 16:29:25> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oradata/archivelog/
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 92 #记住这个和主库对比,备库和主库一致说已实时同步
2.3.2.查备库视图v$dataguard_stats
该动态性能视图显示出在主库产生了多少重做日志数据,但是没有被备库所应用,所以通过该视图基本可以确定当主库崩溃的话,备库将丢失多少重做日志数据,同时我们可以估算failover的时间(apply finish time + estimated startup time).
set linesize 999
col name for 50
col values for a30
select * from v$dataguard_stats;
NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
-------------------------------------------------- ---------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 07/29/2024 10:29:26 07/29/2024 10:29:25
apply lag day(2) to second(0) interval 07/29/2024 10:29:26
apply finish time day(2) to second(3) interval 07/29/2024 10:29:26
estimated startup time 8 second 07/29/2024 10:29:26
v$dataguard_stats视图说明
name字段
transport lag:没有传到备库的redo量或者在已经传输到备库但是备库没有应用的redo量。
apply lag:备库通过应用主库传过来的redo日志与主库同步所延迟的时间。
apply finish time :表示在备库上完成redo应用所需要的时间。
estimated startup time:启动和打开物理备库需要的时间,不适用逻辑备库
value字段
参数的值
unit字段
各个参数的时间单元
TIME_COMPUTED字段
物理备库上估算各个参数的本地时间
三. 切换原主库至新备库
3.1. 查看主库当前可切换状态
对主库进行切换(如果SWITCHOVER_STATUS的值为TO STANDBY或者为SESSIONS ACTIVE
都可以切换至备库)
select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY
3.2. 切换主库至备库
命令执行完,数据库实例处于关闭状态
alter database commit to switchover to physical standby with session shutdown;
详细过程如下:
idle 26-JUL-24> alter database commit to switchover to physical standby with session shutdown;
Database altered.
idle 26-JUL-24> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 11937
Session ID: 1 Serial number: 5
3.3. 启动新备库至mount状态
startup mount;
详细过程如下:
idle 26-JUL-24> startup mount;
ORACLE instance started.
Total System Global Area 2638954496 bytes
Fixed Size 2256152 bytes
Variable Size 620757736 bytes
Database Buffers 2013265920 bytes
Redo Buffers 2674688 bytes
Database mounted.
3.4. 检查新备库状态
select open_mode,database_role,switchover_status from v$database;
详细过程如下:
idle 26-JUL-24> select open_mode,database_role,switchover_status from v$database;
OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- ---------------- --------------------
MOUNTED PHYSICAL STANDBY RECOVERY NEEDED
四. 切换原备库至新主库
4.1. 查看备库当前可切换状态
查看备库是否可以切换至主库( SWITCHOVER_STATUS的值为TO PRIMARY或者为SESSIONS
ACTIVE都可以切换至主库)
select switchover_status from v$database;
详细过程如下:
idle 2024-07-26 16:16:11> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
4.2. 切换原备库至新主库
命令执行完,数据库实例处于mount状态
alter database commit to switchover to primary with session shutdown;
详细过程如下:
idle 2024-07-26 16:23:36> alter database commit to switchover to primary with session shutdown;
Database altered.
idle 2024-07-26 16:25:18> select status from v$instance;
STATUS
------------
MOUNTED
4.3. 开启新主库至open 状态,并检查新主库状态
alter database open;
select database_role,protection_mode,protection_level,open_mode from v$database;
详细过程如下:
idle 2024-07-26 16:25:43> alter database open;
Database altered.
idle 2024-07-26 16:26:57> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE
五. 切换完成状态检查验证
5.1. 新主库手动切换归档日志
alter system switch logfile;
详细过程如下:
idle 2024-07-26 16:27:07> alter system switch logfile;
System altered.
5.2. 查看新主库当前归档序号
archive log list;
详细过程如下:
idle 2024-07-26 16:28:26> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oradata/archivelog/
Oldest online log sequence 91
Next log sequence to archive 94
Current log sequence 94
5.3. 新备库启用日志实时应用
本文档采用打开备库、备库open read only方式启用日志实时应用
开启备库,并启用日志实时应用
##打开备库
alter database open;
select open_mode from v$database; #READ ONLY
##备库启用日志实时应用
--备库open read only用
alter database recover managed standby database parallel 10 using current logfile disconnect from session;
--备库mount模式用
alter database recover managed standby database parallel 10 disconnect from session;
#补充:备库关闭日志实时应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
详细过程如下:
idle 26-JUL-24> select status from v$instance;
STATUS
------------
MOUNTED
idle 26-JUL-24> alter database open;
Database altered.
idle 26-JUL-24> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY
idle 26-JUL-24> alter database recover managed standby database parallel 10 using current logfile disconnect from session;
Database altered.
idle 26-JUL-24> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
5.4. 检查新备库当前接收,以及正在应用的日志序号。与新主库一致
select process,client_process,sequence#,status from v$managed_standby;
详细过程如下:
idle 26-JUL-24> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 93 CLOSING
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 91 CLOSING
ARCH ARCH 92 CLOSING
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 94 IDLE
RFS UNKNOWN 0 IDLE
MRP0 N/A 94 APPLYING_LOG #备库已开始应用日志
35 rows selected.
5.5. 查看新备库当前归档序号
archive log list;
详细过程如下:
idle 2024-07-26 16:28:26> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /data/oradata/archivelog/
Oldest online log sequence 0
Next log sequence to archive 0
Current log sequence 94
5.6. 检查新主库及新备库alert日志,观察是否有告警及其他需要注意的日志。
--新主库alert日志
cd $ORACLE_BASE/diag/rdbms/fuwa/fuwa/trace/
tail -300f alert_fuwa.log
--新备库alert日志
cd $ORACLE_BASE/diag/rdbms/wafu/wafu/trace
tail -300f alert_wafu.log