随笔- 290 文章- 0 评论- 433 阅读- 334万

Oracle Dataguard之switchover

Oracle Dataguard的角色转换包含两类：Switchover和Failover。Switchover指主备之间角色转换，主库降为备库，备库升级为主库。而failover则是指主库出现问题时，备库切换为主库。下面我们来看看官方的定义。

Switchover

Allows the primary database to switch roles with one of its standby databases.There is no data loss during a switchover.After a switchover,each database continues to participate in the Data Guard configuration with its new role.

Failover

Changes a standby database to the primary role in response to a primary database failure.If the primary database was not operating in either maximum protection mode or maximum availability mode before the failure,some data loss may occur.If Flashback Database is enabled on the primary database,it can be reinstated as a standby for the new primary database once the reason for the failure is corrected.

在生产环境中，用的较多的是最大可用模式。为了进行后续的实验，我们将默认的最大性能模式转换为最大可用模式。

一、转换模式

1> 在主库上查询当前数据的保护模式 -->> node1 上操作

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

2> 在主库上转换模式

SQL> alter database set standby database to maximize availability;

二、主库环境准备 -->> node1 上操作

1> 添加standby redo log

[oracle@node1 ~]$ mkdir /u01/standby

SQL> alter database add standby logfile '/u01/standby/standby01.log' size 50M;

SQL> alter database add standby logfile '/u01/standby/standby02.log' size 50M;

SQL> alter database add standby logfile '/u01/standby/standby03.log' size 50M;

SQL> alter database add standby logfile '/u01/standby/standby04.log' size 50M;

2> 修改主库的配置参数

SQL> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=orcl';

-->> 原来的参数是valid_for=(online_logfiles,primary_role),现修改为(all_logfiles,all_roles),代表在主库降为备库角色时，该地址也可作为standby redo log 的归档地址

SQL> alter system set fal_server=to_victor;

SQL> alter system set db_file_name_convert='victor','orcl' scope=spfile;

SQL> alter system set log_file_name_convert='victor','orcl' scope=spfile;

SQL> alter system set standby_file_management='auto';

三、备库环境准备 -->> node2 上操作

SQL> alter system set log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=victor';

-->> 原来的参数是valid_for=(standby_logfiles,standby_role),现改为(all_logfiles,all_roles),即当node2升为主库时，该地址也可作为online redo log的归档地址

SQL> alter system set log_archive_dest_2='service=to_orcl lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=orcl';

SQL> alter system set log_archive_dest_state_2='enable';

四、校验主备之间是否有日志传输错误或者redo gap

1> 主库上查询 -->> node1 上操作

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

STATUS GAP_STATUS
-------- ------------------------
VALID NO GAP

status代表日志传输的状态，gap_status代表主备归档日志是否有gap

注意：status必须为valid且gap_status必须为no gap

2> 备库上查询 -->> node2上操作

在日志传输ok且没有日志gap的情况下，看日志是够被应用

SQL> select sequence#,applied from v$archived_log;

五、查询主备的switchover状态

1> 主库上查询

SQL> select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
TO STANDBY PRIMARY

2> 备库上查询

SQL> select switchover_status,database_role from v$database;

SWITCHOVER_STATUS DATABASE_ROLE
-------------------- ----------------
NOT ALLOWED PHYSICAL STANDBY

六、开始switchover

1> 主库切换到standby角色 -->> node1 上操作

SQL> alter database commit to switchover to physical standby with session shutdown;

SQL> shutdown immediate

SQL> startup

2> 备库上查询switchover的状态

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

-->> To Primary代表The database is ready to switch to the primary role

3> 备库切换到primary的角色 -->> node2 上操作

SQL> alter database commit to switchover to primary with session shutdown;

SQL> alter database open;

4> node1（原主库）上启用redo apply

SQL> alter database recover managed standby database using current logfile disconnect from session;

七、查看switchover后主备状态

1> node1 上操作

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY NOT ALLOWED

2> node2 上操作

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY TO STANDBY

八、测试

见前几篇测试方法

至此，主备switchover OK！

总结：

1> 官档上还是有点bug的，在执行switchover之前，它建议将备库关掉，启动到mount状态。如果是这样的话，当node1执行完switchover to physical standby的命令后，node2的switchover_status是SWITCHOVER PENDING,this status indicates that a switchover request has been received from the primary database and is being processed. A physical standby database cannot switch to the primary role while in this transient state.此时，无论是重启主库还是备库，node2的switchover_status始终是SWITCHOVER PENDING，这时在备库上应用redo apply即可解决问题，即执行 alter database recover managed standby database using current logfile disconnect from session即可

2> 关于switchover_status的参数说明，官档如下