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
成功!
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14338195/viewspace-1053224/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14338195/viewspace-1053224/