Data Guard角色转换之switchover

1、 检查Primary数据库是否支持switchover操作：

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

SESSIONS ACTIVE

上面查询结果最理想应为TO STANDBY，表示Primary数据库支持转换为Standby角色；还有一种查询结果就如上所述为SESSION ACTIVE，说明当前由用户仍在连接到Primary数据库，出现这种情况时也能进行转换，方法有2：断开连接到Primary数据库的连接或者在启动switchover命令转换角色时增加with session shutdown子句（见下一步实例）；否则就需要重新检查Data Guard的配置，如LOG_ARCHIVE_DEST_n之类的参数值是否正确有效。

2、 将Primary数据库转换为Standby角色：

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

Database altered.

3、 将原Primary数据库重新启动到MOUNT状态：

SQL> shutdown immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount

……

Database mounted.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

4、 检查待转换standby数据库是否支持switchover操作：

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

TO PRIMARY

5、 将待转换Standby数据库转换为Primary角色：

SQL> alter database commit to switchover to primary;

Database altered.

6、 完成转换并打开新的Primary数据库：

SQL> alter database open;

Database altered.

7、 验证：

新Primary：

SQL> SELECT SEQUENCE#, DEST_ID, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE -1 ORDER BY SEQUENCE#;

SEQUENCE# DEST_ID FIRST_TIM NEXT_TIME

---------- ---------- --------- ---------

6 1 30-JUL-11 30-JUL-11

7 1 30-JUL-11 30-JUL-11

8 2 30-JUL-11 30-JUL-11

9 1 30-JUL-11 30-JUL-11

9 2 30-JUL-11 30-JUL-11

新Standby：

SQL> SELECT SEQUENCE#, DEST_ID, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE -1 ORDER BY SEQUENCE#;

SEQUENCE# DEST_ID FIRST_TIM NEXT_TIME

---------- ---------- --------- ---------

6 1 30-JUL-11 30-JUL-11

6 2 30-JUL-11 30-JUL-11

7 1 30-JUL-11 30-JUL-11

7 2 30-JUL-11 30-JUL-11

8 1 30-JUL-11 30-JUL-11

8 2 30-JUL-11 30-JUL-11

9 1 30-JUL-11 30-JUL-11

新Primary：

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> SELECT SEQUENCE#, DEST_ID, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE -1 ORDER BY SEQUENCE#;

SEQUENCE# DEST_ID FIRST_TIM NEXT_TIME

---------- ---------- --------- ---------

6 1 30-JUL-11 30-JUL-11

7 1 30-JUL-11 30-JUL-11

8 2 30-JUL-11 30-JUL-11

9 1 30-JUL-11 30-JUL-11

9 2 30-JUL-11 30-JUL-11

10 1 30-JUL-11 30-JUL-11

10 2 30-JUL-11 30-JUL-11

新Standby：

SQL> SELECT SEQUENCE#, DEST_ID, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG WHERE FIRST_TIME > SYSDATE -1 ORDER BY SEQUENCE#;

SEQUENCE# DEST_ID FIRST_TIM NEXT_TIME

---------- ---------- --------- ---------

6 1 30-JUL-11 30-JUL-11

6 2 30-JUL-11 30-JUL-11

7 1 30-JUL-11 30-JUL-11

7 2 30-JUL-11 30-JUL-11

8 1 30-JUL-11 30-JUL-11

8 2 30-JUL-11 30-JUL-11

9 1 30-JUL-11 30-JUL-11

10 1 30-JUL-11 30-JUL-11

新Primary：

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oracle/app/oracle/oradata/oracleDG/system01.dbf

/oracle/app/oracle/oradata/oracleDG/undotbs01.dbf

/oracle/app/oracle/oradata/oracleDG/sysaux01.dbf

/oracle/app/oracle/oradata/oracleDG/users01.dbf

/oracle/app/oracle/oradata/oracleDG/testdg.dbf

新Standby：

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oracle/app/oracle/oradata/oracleDG/system01.dbf

/oracle/app/oracle/oradata/oracleDG/undotbs01.dbf

/oracle/app/oracle/oradata/oracleDG/sysaux01.dbf

/oracle/app/oracle/oradata/oracleDG/users01.dbf

/oracle/app/oracle/oradata/oracleDG/testdg.dbf

新Primary：

SQL> drop tablespace testdg including contents and datafiles;

Tablespace dropped.

SQL> create tablespace testdb2 datafile '/oracle/app/oracle/oradata/oracleDG/testdb2.dbf' size 1M;

Tablespace created.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

System altered.

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

USERS

TEMP

TESTDB2

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oracle/app/oracle/oradata/oracleDG/system01.dbf

/oracle/app/oracle/oradata/oracleDG/undotbs01.dbf

/oracle/app/oracle/oradata/oracleDG/sysaux01.dbf

/oracle/app/oracle/oradata/oracleDG/users01.dbf

/oracle/app/oracle/oradata/oracleDG/testdb2.dbf

新Standby：

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

USERS

TEMP

TESTDB2

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/oracle/app/oracle/oradata/oracleDG/system01.dbf

/oracle/app/oracle/oradata/oracleDG/undotbs01.dbf

/oracle/app/oracle/oradata/oracleDG/sysaux01.dbf

/oracle/app/oracle/oradata/oracleDG/users01.dbf

/oracle/app/oracle/oradata/oracleDG/testdb2.dbf

成功！