什么是switchover?

dataguard switchover指的就是将dataguard 主库和备库进行角色互换，同时保证没有数据丢失。

实验具体步骤：

数据库版本：

Primary Database : Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

Physical Standby Database: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

1. 检查是否有Log file gap(主库和备库操作)

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

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

2. 检查备库上有与主库相匹配的临时表空间文件

主库：

SQL> select file_name from dba_temp_files;

FILE_NAME
------------------------------------------------------------
/data/oracle/oradata/phyprimary/temp01.dbf

备库：

SQL> select file_name from dba_temp_files;

FILE_NAME
--------------------------------------------------------------------------------
/data/oracle/oradata/phystandby/temp01.dbf

3. 确保主库应用到归档日志没有延迟(主库操作)

SQL> select delay_mins from v$archive_dest where dest_id = 2;

DELAY_MINS
----------
0

如果存在，使用如下语句清除延迟

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY;

4. 确保主库和备库的切换状态

主库：

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS,PRIMARY_DB_UNIQUE_NAME,PROTECTION_MODE from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS PRIMARY_DB_UNIQUE_NAME PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA phyprimary PRIMARY TO STANDBY MAXIMUM AVAILABILITY

备库：

SQL> set linesize 200
SQL> /

NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS PRIMARY_DB_UNIQUE_NAME PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA phystandby PHYSICAL STANDBY NOT ALLOWED phyprimary MAXIMUM AVAILABILITY

NOT ALLOWED：表示还没有从主库收到switch over的请求

5. 把主库切换成备库，并重启主库到mount

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

Database altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             335546752 bytes
Database Buffers           79691776 bytes
Redo Buffers                6094848 bytes
Database mounted.

查看日志文件，此时数据库已经变成备库状态：

Successful mount of redo thread 1, with mount id 324279998
Physical Standby Database mounted

6. 查看备库的切换状态

SQL> /

NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS PRIMARY_DB_UNIQUE_NAME PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA phystandby PHYSICAL STANDBY SESSIONS ACTIVE phyprimary MAXIMUM AVAILABILITY

由于SWITCHOVER_STATUS 状态是SESSIONS ACTIVE，所以切换时要加上WITH SESSION SHUTDOWN

7. 切换备库到主库（备库操作），并打开新的主库(原备库)

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

Database altered.

SQL> alter database open;

Database altered.

8. 查看当前主库（原备库）和备库（原主库）的状态

当前主库：

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS,PRIMARY_DB_UNIQUE_NAME,PROTECTION_MODE from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS PRIMARY_DB_UNIQUE_NAME PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA phystandby PRIMARY TO STANDBY phyprimary MAXIMUM AVAILABILITY

当前备库：

SQL> select NAME,DB_UNIQUE_NAME,DATABASE_ROLE,SWITCHOVER_STATUS,PRIMARY_DB_UNIQUE_NAME,PROTECTION_MODE from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS PRIMARY_DB_UNIQUE_NAME PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA phyprimary PHYSICAL STANDBY RECOVERY NEEDED phystandby MAXIMUM AVAILABILITY

9. 最后一步，在新备库应用Redo log

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

Database altered.

10. 验证切换是否成功

新主库：

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

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

SQL> !date
Thu Aug 7 19:19:35 PDT 2014

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> SELECT SEQUENCE#, to_char(FIRST_TIME,'YYYY-MM-DD HH24:mi:ss'), to_char(NEXT_TIME,'YYYY-MM-DD HH24:mi:ss'),applied
2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc;

SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' APPLIED
---------- ------------------- ------------------- ---------
40 2014-08-07 19:19:41 2014-08-07 19:19:42 NO
40 2014-08-07 19:19:41 2014-08-07 19:19:42 YES

新备库：

SQL> !date
Thu Aug 7 19:21:42 PDT 2014

SQL> SELECT SEQUENCE#, to_char(FIRST_TIME,'YYYY-MM-DD HH24:mi:ss'), to_char(NEXT_TIME,'YYYY-MM-DD HH24:mi:ss'),applied
2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# desc;

SEQUENCE# TO_CHAR(FIRST_TIME, TO_CHAR(NEXT_TIME,' APPLIED
---------- ------------------- ------------------- ---------
40 2014-08-07 19:19:41 2014-08-07 19:19:42 YES
39 2014-08-07 19:12:01 2014-08-07 19:19:41 YES

ITPub博客

Dataguard Physical Standy Switchover

最新文章