一. 切换前检查
1. 参数检查
----------------------------主端----------------------------------------------------- --定义dataguard配置的有效db_unique_name
SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(prod,prod_std)
--
本地归档目的地及属性
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=+DATA_DG/arch valid_f
or=(all_logfiles,all_roles) db
_unique_name=prod
--
远程归档目的地及属性
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=prod_std lgwr async va
lid_for=(online_logfiles,prima
ry_roles) db_unique_name=prod_
std
--log_archive_dest_state_1
SQL> show parameter log_archive_dest_state_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_1 string ENABLE
--log_archive_dest_state_2
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
--当主库数据文件被添加或删除，自动配置备库
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto
--备库TNS
SQL> show parameter fal_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_server string PROD_STD
--主库TNS
SQL> show parameter fal_client
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string PROD_PRI
--service_names
SQL> show parameter service_names;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string prod, prod_std
--spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA_DG/PROD/PARAMETERFILE/sp
file.281.950980793
----------------------------备端----------------------------------------------------- --log_archive_config：与主端一致
--log_archive_dest_1
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=+DATA_DG/arch valid_f
or=(all_logfiles,all_roles) db
_unique_name=prod_std
--log_archive_dest_2
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=prod_pri lgwr async va
lid_for=(online_logfiles,prima
ry_roles) db_unique_name=prod
--log_archive_dest_state_1:与主端一致
--log_archive_dest_state_2:与主端一致
--standby_file_management:与主端一致
--fal_server
SQL> show parameter fal_server
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_server string PROD_PRI
--fal_client
SQL> show parameter fal_client
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string PROD_PRI
--db_unique_name
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string prod_std
--spfile
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA_DG/PROD/PARAMETERFILE/sp
fileprod.ora
2. 确认主备端网络连通情况
主端：
根据log_archive_dest_2的配置
[oracle@oracle-node1 ~]$ tnsping prod_std
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 03-AUG-2017 17:58:10
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION_LIST = (LOAD_BALANCE = off) (FAILOVER = on) (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = OFF) (FAILOVER = ON) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.19.108)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod) (Instance_name = prod1) (FAILOVER_MODE = (TYPE = session) (METHOD = basic) (RETRIES = 4) (DELAY = 1)))) (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = OFF) (FAILOVER = ON) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.19.109)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod) (Instance_name = prod2) (FAILOVER_MODE = (TYPE = session) (METHOD = basic) (RETRIES = 4) (DELAY = 1)))))
OK (10 msec)
备端：
[oracle@oracle-node3 ~]$ tnsping prod_pri
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 03-AUG-2017 17:59:20
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION_LIST = (LOAD_BALANCE = off) (FAILOVER = on) (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = OFF) (FAILOVER = ON) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.19.106)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod) (Instance_name = prod1) (FAILOVER_MODE = (TYPE = session) (METHOD = basic) (RETRIES = 4) (DELAY = 1)))) (DESCRIPTION = (ADDRESS_LIST = (LOAD_BALANCE = OFF) (FAILOVER = ON) (ADDRESS = (PROTOCOL = TCP)(HOST = 172.18.19.107)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prod) (Instance_name = prod2) (FAILOVER_MODE = (TYPE = session) (METHOD = basic) (RETRIES = 4) (DELAY = 1)))))
OK (0 msec)
3. 主备端standby logfile查看,保证日志实时应用
SQL> select * from v$logfile where type='STANDBY';
4. 主备端数据库状态检查
---主端
SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,
2 open_mode,switchover_status from gv$database;
DBID NAME OPEN_MODE CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING OPEN_MODE SWITCHOVER_STATUS
---------- --------- -------------------- ----------- -------------------- ---------------- --------------------------------------- -------------------- --------------------
362818713 PROD READ WRITE 2262262 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
362818713 PROD READ WRITE 2262262 MAXIMUM PERFORMANCE PRIMARY YES READ WRITE TO STANDBY
--备端
SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,
2 open_mode,switchover_status from gv$database;
DBID NAME OPEN_MODE CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING OPEN_MODE SWITCHOVER_STATUS
---------- --------- -------------------- ----------- -------------------- ---------------- --------------------------------------- -------------------- --------------------
362818713 PROD READ ONLY WITH APPLY 2263439 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLYNOT ALLOWED
362818713 PROD READ ONLY WITH APPLY 2263439 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY WITH APPLY NOT ALLOWED
5. 检查容灾端应用节点和应用情况
SQL> select inst_id,value
2 from gv$dataguard_stats
3 where value =
4 (select min(value) from gv$dataguard_stats where name = 'apply lag')
5 and name = 'apply lag'
6 ;
INST_ID VALUE
---------- ----------------------------------------------------------------
1 +00 00:00:00
二. switch over容灾切换
----------------------------主端-----------------------------------------------------
1. 关闭主端2号节点
--停掉两节点所有监听(关闭双节点所有监听，实时应用不缺少归档的情况下，生产的监听并不影响日志应用)
[root@oracle-node2 bin]# su - grid
Last login: Thu Aug 3 17:29:08 CST 2017 on pts/1
[grid@oracle-node2 ~]$ srvctl stop listener
--杀外部连接
[oracle@oracle-node2 ~]$ ps -ef|grep LOCAL=NO|awk '{print $2}'|xargs kill -9
--关闭数据库
[oracle@oracle-node2 ~]$ sqlplus / as sysdba
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
alter system checkpoint;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
2. 1号节点进行容灾切换
--杀外部连接
[root@oracle-node1 ~]# ps -ef|grep LOCAL=NO|awk '{print $2}'|xargs kill -9
--
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database commit to switchover to physical standby with session shutdown;(执行主切备)
Database altered.
--
SQL> startup
--检查主库状态
SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,
2 open_mode,switchover_status from gv$database;
DBID NAME OPEN_MODE CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING OPEN_MODE SWITCHOVER_STATUS
---------- --------- -------------------- ----------- -------------------- ---------------- --------------------------------------- -------------------- --------------------
362818713 PROD READ ONLY 2366359 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES
READ ONLY TO PRIMARY
--开启日志应用
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
3. 启动2号节点
SQL> startup
--检查主库状态，应为physical standby
SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,force_logging,
2 open_mode,switchover_status from gv$database;
DBID NAME OPEN_MODE CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING OPEN_MODE SWITCHOVER_STATUS
---------- --------- -------------------- ----------- -------------------- ---------------- --------------------------------------- -------------------- --------------------
362818713 PROD READ ONLY 2366359 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY TO PRIMARY
362818713 PROD READ ONLY 2366359 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES READ ONLY TO PRIMARY
--开启两节点监听
[grid@oracle-node2 ~]$ srvctl start listener
----------------------------备端----------------------------------------------------- 以日志应用在1号节点为例
1. 停容灾端2号节点
--停监听
[grid@oracle-node4 ~]$ srvctl stop listener -n oracle-node4
--杀外部连接
[root@oracle-node4 ~]# ps -ef|grep LOCAL=NO|awk '{print $2}'|xargs kill -9
--关闭数据库
SQL> shutdown abort;
2. 在容灾端1号节点进行备切主
--停监听
[grid@oracle-node3 ~]$ srvctl stop listener -n oracle-node3
--杀外部连接
[root@oracle-node3 ~]# ps -ef|grep LOCAL=NO|awk '{print $2}'|xargs kill -9
--检查数据库状态
SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,
2 force_logging,switchover_status from v$database;
DBID NAME OPEN_MODE CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING SWITCHOVER_STATUS
---------- --------- -------------------- ----------- -------------------- ---------------- --------------------------------------- --------------------
362818713 PROD READ ONLY WITH APPLY 2366359 MAXIMUM PERFORMANCE PHYSICAL STANDBY YES TO PRIMARY
--取消日志应用
SQL> alter database recover managed standby database cancel;
Database altered.
--备转主，转换过程包括自动重启操作
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
--检查数据库状态，此时已经启动到mount
SQL> select dbid,name,open_mode,current_scn,protection_mode,database_role,
2 force_logging,switchover_status from v$database;
DBID NAME OPEN_MODE CURRENT_SCN PROTECTION_MODE DATABASE_ROLE FORCE_LOGGING SWITCHOVER_STATUS
---------- --------- -------------------- ----------- -------------------- ---------------- --------------------------------------- --------------------
362818713 PROD MOUNTED 0 MAXIMUM PERFORMANCE PRIMARY YES NOT ALLOWED
3. 启动2号节点
--起库
SQL> startup
--开启监听
[grid@oracle-node4 ~]$ srvctl start listener
至此，switch over完成
