10G DG SWITCH OVER

本文讨论10G DATA GUARD进行角色切换的步骤。

服务器信息：
primary：原主库，切换后就是备库
standby：原备库，切换后就是主库

1、检查primary的SWITCHOVER状态（当前是主库）
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

如果SWITCHOVER_STATUS不是TO STANDBY，则表示当前主库不能执行switchover，这时候需要检查主库的设置了。

2、在primary执行（当前是主库）
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

Database altered.

此时后台会做一系统的操作，如停止相关进程、创建standby 控制文件等。

后台日志如下：
Wed Jun 27 22:50:18 2007
Stopping background process CJQ0
Wed Jun 27 22:50:18 2007
SMON: disabling tx recovery
Wed Jun 27 22:50:18 2007
Stopping background process QMNC
Wed Jun 27 22:50:20 2007
Stopping Job queue slave processes
Wed Jun 27 22:50:20 2007
Job queue slave processes stopped
Wed Jun 27 22:50:21 2007
SMON: disabling cache recovery
Wed Jun 27 22:50:21 2007
Shutting down archive processes
Archiving is disabled
Wed Jun 27 22:50:26 2007
ARCH shutting down
ARC3: Archival stopped
Wed Jun 27 22:50:31 2007
ARCH shutting down
ARC2: Archival stopped
Wed Jun 27 22:50:36 2007
ARCH shutting down
ARC1: Archival stopped
Wed Jun 27 22:50:41 2007
ARCH shutting down
ARC0: Archival stopped
Wed Jun 27 22:50:42 2007
Thread 1 closed at log sequence 34
Successful close of redo thread 1
Wed Jun 27 22:50:42 2007
ARCH: Noswitch archival of thread 1, sequence 34
ARCH: End-Of-Redo Branch archival of thread 1 sequence 34
ARCH: Archiving is disabled due to current logfile archival
Clearing standby activation ID 1463601975 (0x573ccb37)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Archivelog for thread 1 sequence 34 required for standby recovery
MRP0 started with pid=10, OS id=2963
Wed Jun 27 22:50:44 2007
MRP0: Background Managed Standby Recovery process started (primary)
Managed Standby Recovery not using Real Time Apply
parallel recovery started with 2 processes
Online logfile pre-clearing operation disabled by switchover
Media Recovery Log /u01/archivelog/1_34_626106231.arc
Identified End-Of-Redo for thread 1 sequence 34
Wed Jun 27 22:50:50 2007
Media Recovery End-Of-Redo indicator encountered
Wed Jun 27 22:50:50 2007
Media Recovery Applied until change 162373
Wed Jun 27 22:50:50 2007
MRP0: Media Recovery Complete: End-Of-REDO (primary)
Resetting standby activation ID 1463601975 (0x573ccb37)
Wed Jun 27 22:50:51 2007
MRP0: Background Media Recovery process shutdown (primary)
Wed Jun 27 22:50:52 2007
Switchover: Complete - Database shutdown required (primary)
Wed Jun 27 22:50:52 2007
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY

从alert信息可以看到，oracle还要求在primary建立standby redo log，这里由于我们之前在primary上已经建立的standby redo log，这一步我们就不用做了。

3、重启primary（目前是备库）

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 88081256 bytes
Database Buffers 75497472 bytes
Redo Buffers 2932736 bytes
Database mounted.

4、验证primary的状态（当前是备库）
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

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

如果SWITCHOVER_STATUS是TO PRIMARY，表示当前数据库已经处于standby角色中了。

5、在standby上执行（当前是备库，即将转为主库）

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

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

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

此时后台日志如下：
Wed Jun 27 22:49:10 2007
ALTER DATABASE SWITCHOVER TO PRIMARY (primary)
Wed Jun 27 22:49:10 2007
If media recovery active, switchover will wait 900 seconds
SwitchOver after complete recovery through change 162373
Online log /u01/oracle/oradata/primary/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oracle/oradata/primary/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oracle/oradata/primary/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 162371
Wed Jun 27 22:49:10 2007
Switchover: Complete - Database shutdown required (primary)
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
Wed Jun 27 22:49:13 2007
ARC1: Archival disabled due to instance shutdown
Shutting down archive processes
Archiving is disabled
Wed Jun 27 22:49:23 2007
ARCH shutting down
ARC0: Archival stopped
Wed Jun 27 22:49:28 2007
ARCH shutting down
ARC1: Archival stopped

6、重启standby（当前是主库）

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 79692648 bytes
Database Buffers 83886080 bytes
Redo Buffers 2932736 bytes
Database mounted.
Database opened.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

可以看到，SWITCHOVER_STATUS已经变成TO STANDBY了。

7、验证切换是否正常
--在primary执行
SQL> alter database recover managed standby database disconnect from session;

Database altered.

--在standby执行
SQL> create table test1(id int);

Table created.

SQL> insert into test1 values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system switch logfile;

System altered.


--在primary执行
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select * from test1;

ID
----------
10

可见，DG运行正常。如果需要再切换恢复，按照上面的步骤再走一遍就可以。


这里我们其实忽略了很多步骤，包含在新备库添加standby redo log，修改新备库的参数文件等等，但由于我们之前在配置DG的时候已经预先做了这些工作了，使得切换步骤很简单。
在管理DG时的一个好习惯就是在修改主库或者备库的任何东西都要考虑switch over或者failover，尽量在修改一端的同时修改另一端的对应信息，使得switch over或者failover尽量方便。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/231499/viewspace-63828/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/231499/viewspace-63828/