介绍一下RAC环境下STANDBY数据库的SWITCHOVER切换。
PRIMARY数据库和STANDBY数据库采用的都是RAC 11.1.0.6 for Solaris10 sparc,共享存储PRIMARY数据库采用VOLUMN CLUSTER MANAGER,而STANDBY数据库使用ASM。
STANDBY数据库环境已经建立完成,下面准备实施SWITCHOVER切换:
SWITCHOVER切换是计划中的切换,在切换后不会丢失任何的数据,而且过程可逆,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
在进行DATA GUARD的物理STANDBY切换前需要注意:
确认主库和从库间网络连接通畅;
确认没有活动的会话连接在数据库中;
PRIMARY数据库处于打开的状态,STANDBY数据库处于MOUNT状态;
确保STANDBY数据库处于ARCHIVELOG模式;
如果设置了REDO应用的延迟,那么将这个设置去掉;
确保配置了主库和从库的初始化参数,使得切换完成后,DATA GUARD机制可以顺利的运行。
检查主数据库,确保所有的会话全部断开:
SQL> select inst_id,
2 username,
3 program,
4 module
5 from gv$session
6 where username is not null;
INST_ID USERNAME PROGRAM MODULE
---------- --------------- ------------------------------ ------------------------------
1 SYS sqlplus@newtrade1 (TNS V1-V3) sqlplus@newtrade1 (TNS V1-V3)
1 SYSMAN OMS OEM.SystemPool
1 SYSMAN OMS OEM.DefaultPool
1 SYS oracle@newtrade1 (PZ99) sqlplus@newtrade1 (TNS V1-V3)
1 SYS racgimon@newtrade1 (TNS V1-V3) racgimon@newtrade1 (TNS V1-V3)
1 SYS racgimon@newtrade1 (TNS V1-V3) racgimon@newtrade1 (TNS V1-V3)
1 SYSMAN OMS OEM.SystemPool
1 SYS racgimon@newtrade1 (TNS V1-V3) racgimon@newtrade1 (TNS V1-V3)
1 DBSNMP emagent@newtrade1 (TNS V1-V3) emagent@newtrade1 (TNS V1-V3)
1 DBSNMP emagent@newtrade1 (TNS V1-V3) emagent@newtrade1 (TNS V1-V3)
1 SYS racgimon@newtrade1 (TNS V1-V3) racgimon@newtrade1 (TNS V1-V3)
2 SYS sqlplus@newtrade2 (TNS V1-V3) sqlplus@newtrade2 (TNS V1-V3)
2 SYS racgimon@newtrade2 (TNS V1-V3) racgimon@newtrade2 (TNS V1-V3)
2 SYS racgimon@newtrade2 (TNS V1-V3) racgimon@newtrade2 (TNS V1-V3)
2 SYSMAN OMS OEM.DefaultPool
2 SYSMAN OMS OEM.BoundedPool
2 SYS oracle@newtrade2 (PZ99) sqlplus@newtrade1 (TNS V1-V3)
2 SYS racgimon@newtrade2 (TNS V1-V3) racgimon@newtrade2 (TNS V1-V3)
2 SYSMAN OMS OEM.SystemPool
已选择19行。
上面的sqlplus进程是当前用户连接,racgimon进程是检测RAC环境的,也没有关系,需要关闭的是SYSMAN用户的连接,可以通过emctl命令进行关闭。
bash-3.00$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.1.0.6.0
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
https://newtrade1:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.
对于RAC数据库来说,切换的时候只能保留一个实例,其他的实例必须关闭:
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac11g2
SQL> select name,
2 open_mode,
3 database_role,
4 guard_status,
5 db_unique_name,
6 primary_db_unique_name
7 from v$database;
NAME OPEN_MODE DATABASE_ROLE GUARD_S DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME
--------- ---------- ---------------- ------- --------------- -------------------------
RAC11G READ WRITE PRIMARY NONE rac11g
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
下面再次检查主数据库:
SQL> select instance_name from gv$instance;
INSTANCE_NAME
----------------
rac11g1
SQL> select name, switchover_status from v$database;
NAME SWITCHOVER_STATUS
--------- --------------------
RAC11G SESSIONS ACTIVE
SQL> select inst_id,
2 username,
3 program,
4 module
5 from gv$session
6 where username is not null;
INST_ID USERNAME PROGRAM MODULE
---------- --------------- ------------------------------ ------------------------------
1 SYS sqlplus@newtrade1 (TNS V1-V3) sqlplus@newtrade1 (TNS V1-V3)
1 SYS racgimon@newtrade1 (TNS V1-V3) racgimon@newtrade1 (TNS V1-V3)
1 SYS racgimon@newtrade1 (TNS V1-V3) racgimon@newtrade1 (TNS V1-V3)
1 SYS racgimon@newtrade1 (TNS V1-V3) racgimon@newtrade1 (TNS V1-V3)
1 SYS racgimon@newtrade1 (TNS V1-V3) racgimon@newtrade1 (TNS V1-V3)
现在主库只剩下一个实例了,虽然数据库的状态仍然是SESSIONS ACTIVE,但是会话中只有racgimon程序和当前的sqlplus程序,已经可以进行切换了。
下面检查STANDBY数据库:
bash-3.00$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on 星期四 9月 11 14:32:08 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac11g2
SQL> select name,
2 open_mode,
3 database_role,
4 guard_status,
5 db_unique_name,
6 primary_db_unique_name
7 from v$database;
NAME OPEN_MODE DATABASE_ROLE GUARD_S DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME
--------- ---------- ---------------- ------- --------------- -------------------------
RAC11G READ ONLY PHYSICAL STANDBY NONE rac11g_s rac11g
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
关闭实例二之后,将实例一取消应用日志,置于MOUNT状态下:
SQL> select instance_name from gv$instance;
INSTANCE_NAME
----------------
rac11g1
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 1.7108E+10 bytes
Fixed Size 2101632 bytes
Variable Size 3344420480 bytes
Database Buffers 1.3757E+10 bytes
Redo Buffers 4431872 bytes
数据库装载完毕。
下面可以开始正式切换,首先登陆到PRIMARY数据库:
SQL> alter database commit to switchover to physical standby with session shutdown;
数据库已更改。
SQL> shutdown immediate
ORA-01507: 未装载数据库
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 1.7108E+10 bytes
Fixed Size 2101632 bytes
Variable Size 3545747072 bytes
Database Buffers 1.3556E+10 bytes
Redo Buffers 4431872 bytes
数据库装载完毕。
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
下面在STANDBY数据库执行:
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
SWITCHOVER PENDING
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
第 1 行出现错误:
ORA-16139: 需要介质恢复
由于RAC环境的特殊性,造成了这个问题,下面只需要执行一下RECOVER MANAGED STANDBY DATABASE语句,确保所有的日志都已经应用就可以了:
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
SQL> select sequence#, thread#, name, applied from v$archived_log;
SEQUENCE# THREAD# NAME APP
---------- ---------- ------------------------------------------------------------ ---
533 1 +DATA/rac11g/1_533_660235173.dbf YES
198 2 +DATA/rac11g/2_198_660235173.dbf YES
532 1 +DATA/rac11g/1_532_660235173.dbf YES
199 2 +DATA/rac11g/2_199_660235173.dbf YES
534 1 +DATA/rac11g/1_534_660235173.dbf YES
535 1 +DATA/rac11g/1_535_660235173.dbf YES
536 1 +DATA/rac11g/1_536_660235173.dbf YES
537 1 +DATA/rac11g/1_537_660235173.dbf YES
538 1 +DATA/rac11g/1_538_660235173.dbf YES
539 1 +DATA/rac11g/1_539_660235173.dbf YES
540 1 +DATA/rac11g/1_540_660235173.dbf YES
541 1 +DATA/rac11g/1_541_660235173.dbf YES
200 2 +DATA/rac11g/2_200_660235173.dbf YES
542 1 +DATA/rac11g/1_542_660235173.dbf YES
201 2 +DATA/rac11g/2_201_660235173.dbf YES
543 1 +DATA/rac11g/1_543_660235173.dbf YES
202 2 +DATA/rac11g/2_202_660235173.dbf YES
203 2 +DATA/rac11g/2_203_660235173.dbf YES
204 2 +DATA/rac11g/2_204_660235173.dbf YES
205 2 +DATA/rac11g/2_205_660235173.dbf YES
544 1 +DATA/rac11g/1_544_660235173.dbf YES
207 2 +DATA/rac11g/2_207_660235173.dbf YES
206 2 +DATA/rac11g/2_206_660235173.dbf YES
545 1 +DATA/rac11g/1_545_660235173.dbf YES
546 1 +DATA/rac11g/1_546_660235173.dbf YES
547 1 +DATA/rac11g/1_547_660235173.dbf YES
已选择26行。
SQL> alter database recover managed standby database cancel;
alter database recover managed standby database cancel
*
第 1 行出现错误:
ORA-16136: 受管备用恢复未激活
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
Oracle应用了所有日志后,自动停止了恢复,且此时数据库已经变为TO PRIMARY状态,下面就可以切换到PRIMARY了:
SQL> alter database commit to switchover to primary;
数据库已更改。
SQL> alter database open;
数据库已更改。
下面回到原来的PRIMARY数据库,也就是切换后的STANDBY数据库:
SQL> select instance_name, status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
rac11g1 MOUNTED
SQL> select name,
2 open_mode,
3 database_role,
4 guard_status,
5 db_unique_name,
6 primary_db_unique_name
7 from v$database;
NAME OPEN_MODE DATABASE_ROLE GUARD_S DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME
--------- ---------- ---------------- ------- -------------------- ----------------------
RAC11G MOUNTED PHYSICAL STANDBY NONE rac11g rac11g_s
SQL> alter database recover managed standby database disconnect from session;
数据库已更改。
至此SWITCHOVER切换已经完成,检查一下远端的归档能否应用到本地环境即可。
首先启动实例2,执行日志的切换:
SQL> startup
ORACLE 例程已经启动。
Total System Global Area 1.7108E+10 bytes
Fixed Size 2101632 bytes
Variable Size 3478638208 bytes
Database Buffers 1.3623E+10 bytes
Redo Buffers 4431872 bytes
数据库装载完毕。
数据库已经打开。
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
rac11g2
SQL> select name,
2 open_mode,
3 database_role,
4 guard_status,
5 db_unique_name,
6 primary_db_unique_name
7 from v$database;
NAME OPEN_MODE DATABASE_ROLE GUARD_S DB_UNIQUE_NAME PRIMARY_DB_UNIQUE_NAME
--------- ---------- ---------------- ------- --------------- -------------------------
RAC11G READ WRITE PRIMARY NONE rac11g_s rac11g
SQL> select group#, thread#, sequence#, status from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 548 INACTIVE
2 1 549 CURRENT
3 2 208 INACTIVE
4 2 209 CURRENT
SQL> alter system archive log current;
系统已更改。
SQL> select group#, thread#, sequence#, status from v$log;
GROUP# THREAD# SEQUENCE# STATUS
---------- ---------- ---------- ----------------
1 1 550 CURRENT
2 1 549 ACTIVE
3 2 210 CURRENT
4 2 209 ACTIVE
下面检查新的STANDBY数据库是否可以正常接收归档:
SQL> select sequence#, thread#, name, applied from v$archived_log
2 where (thread# = 1 and sequence# = 549)
3 or (thread# = 2 and sequence# = 209);
SEQUENCE# THREAD# NAME APP
---------- ---------- --------------------------------------------------------------- ---
209 2 /data/oracle/oradata/rac11g/archivelog/2_209_660235173.dbf YES
549 1 /data/oracle/oradata/rac11g/archivelog/1_549_660235173.dbf NO
至此SWITCHOVER切换完成。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-611058/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-611058/