Oracle 11g dg switchover切换操作流程
主库切换为物理备库
查看主库的状态
--获取/确认主库的状态信息以及保护模式
SQL> set linesize 720
SQL> col name for a10
SQL> col open_mode for a10
SQL> col database_role for a14
SQL> col switchover_status for a16
SQL> col force_logging for a8
SQL> col guard_status for a8
SQL> select name
2 ,open_mode
3 ,database_role
4 ,protection_mode
5 ,protection_level
6 ,switchover_status
7 ,force_logging
8 ,dataguard_broker
9 ,guard_status
10 from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATU FORCE_LO DATAGUAR GUARD_ST
---------- ---------- -------------- -------------------- -------------------- ---------------- -------- -------- --------
GSP READ WRITE PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO STANDBY YES DISABLED NONE
注意事项,检查SWITCHOVER_STATUS字段,如果值为"SESSION ACTIVE"或者"TO STANDBY", 则主数据库角色可以切换为备库角色。 如果字段SWITCHOVER_STATUS是其它值的情况下,则不能切换,具体参考文档dg_parameter_summary.md。这里略过。
主库切换到备库
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
或
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
如果上一步的SWITCH_STATUS字段值为"TO STANDBY",则 WITH SESSION SHUTDOWN 可以省略。如果SWITCHOVER_STATUS字段值为"SESSION ACTIVE" 的话,则必须加上[WITH SESSION SHUTDOWN]。
在执行上面命名前,在SecureCRT或Putty中打开另外一个窗口,观察告警日志的输出信息,以便观察详细输出信息。
$ tail -60f alert_<ORACLE_SID>.log
数据库启动到MOUNT状态
SQL> SELECT STATUS FROM V$INSTANCE;
SQL> STARTUP MOUNT;
备库切换为主库
查看备库的状态
SQL> set linesize 720
SQL> col name for a10
SQL> col open_mode for a10
SQL> col database_role for a14
SQL> col switchover_status for a16
SQL> col force_logging for a8
SQL> col guard_status for a8
SQL> select name
2 ,open_mode
3 ,database_role
4 ,protection_mode
5 ,protection_level
6 ,switchover_status
7 ,force_logging
8 ,dataguard_broker
9 ,guard_status
10 from v$database;
NAME OPEN_MODE DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATU FORCE_LO DATAGUAR GUARD_ST
---------- ---------- -------------- -------------------- -------------------- ---------------- -------- -------- --------
GSP READ ONLY PHYSICAL STAND MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE TO PRIMARY YES DISABLED NONE
WITH APPLY BY
需要检查SWITCH_STATUS字段的值,如果值为"SESSION ACTIVE"或"TO PRIMARY",则备库可以切换为主库。
切换到主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
或
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
注意:如果上一步的SWITCH_STATUS参数值为"TO PRIMARY",则 WITH SESSION SHUTDOWN 可以省略。
将数据库OPEN
SQL> ALTER DATABASE OPEN ;
SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;
新的备库开启日志应用
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
其实,这里的方法也适用于Oracle 12/19c多租户环境,如果是多租户环境,则可以按下面命令实施。
SQL> SHOW PDBS;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED MOUNTED
3 GSPPROD MOUNTED
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> SHOW PDBS;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 GSPPROD MOUNTED
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
Pluggable database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
检查主备的状态
主备库执行:
set linesize 720
col name for a10
col open_mode for a20
col database_role for a16
col switchover_status for a12 heading "SWITCHOVER |STATUS"
col force_logging for a8
col dataguard_broker for a8 heading "DATAGUARD|BROKER"
col guard_status for a8
select name
,open_mode
,database_role
,protection_mode
,protection_level
,switchover_status
,force_logging
,dataguard_broker
,guard_status
from v$database;
备库执行SQL,检查同步状态
set linesize 720;
col name for a24;
col source_db_unique_name for a16;
col value for a16;
col unit for a20;
col time_computed for a19;
col datum_time for a19;
select source_db_unique_name
, name
, value
, unit
, time_computed
, datum_time
from v$dataguard_stats;
最好还做一下数据同步的验证,例如在主库创建一个表,插入几条数据,验证数据库是否同步到测试环境。
SQL> create table test(id number, name varchar2(16));
Table created.
SQL> insert into test
2 select 1001,'kerry1' from dual union all
3 select 1002,'kerry2' from dual;
2 rows created.
SQL> commit;
Commit complete.
SQL>
总结
这里的操作步骤是在参数配置正确的情况下的操作步骤,无需去验证参数是否正确。如果有些参数配置不正确,那么在切换过程中可能会遇到各种问题。