1、执行物理备用数据库切换
本章描述如何执行到物理备用数据库的切换。切换从主数据库开始在从数据库目标上结束。
Setp 1确认主数据库可以切换为standby role
在主数据库上查询V$database这个视图,例如:
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected
如果这个值是TO STANDBY或者SESSIONS ACTIVE表明主数据库是可以切换成Standby role的。如果是其他的值将不可以执行切换,因为Redo传输即缺乏配置项也没有正确的结构。
Step 2在主数据库上开始切换
在主数据库上执行如下SQL语句,切换到备用数据库上:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH -
> SESSION SHUTDOWN;
这个语句能转换主数据库为一个物理备用数据库。在转换前,当前的控制文件被备份到Current SQL Session跟踪文件。必要时,他能重新构建一个当前的控制文件。
Step 3关闭然后启动主数据库到mount阶段
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
该步后,源主数据库将会成为物理备用数据库。
Step 4查询备用数据库的切换状态
同理,执行如下:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
-----------------
TO_PRIMARY
1 row selected
与主数据库类似,该值只能是TO PRIMARY或者 SESSION ACTIVE。
Step 5将目标备用数据库切换为Primary Role.
执行如下:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
注意:与主库切换到备库类似,WITH SESSION SHUTDOWN这个参数会被忽略,如果V$database视图中的SWITCHOVER_STATUS是TO PRIMARY时。
Step 6开启新的物理主库
SQL> ALTER DATABASE OPEN;
Step 7在物理备用数据库上开启Redo Apply应用.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE -
> DISCONNECT FROM SESSION;
Step 8重启Redo Apply在物理备库上,如果你有多个standby数据库.
2、切换实录:
主库上执行:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
SQL> alter database commit to switchover to physical standby with session shutdown;
alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-01105: mount is incompatible with mounts by other instances
出现ORA-01105错误,查找资料发现RAC做standby database的时候只能有一个节点是ACTIVE的。
关闭第二个节点后,在节点一上执行:
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> shutdown immediate
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 7549435904 bytes
Fixed Size2243072 bytes
Variable Size1375733248 bytes
Database Buffers6157238272 bytes
Redo Buffers14221312 bytes
Database mounted.
目标备份库上做:
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Database altered.
SQL> alter database open;
Database altered.
SQL>SELECT SWITCHOVER_STATUS,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
SWITCHOVER_STATUSOPEN_MODE DATABASE_ROLEPROTECTION_MODE
-------------------- -------------------- ---------------- --------------------
TO STANDBYREAD WRITE PRIMARYMAXIMUM PERFORMANCE
回到original primary database上:
刚开始的时候该数据库需要恢复然后自动关闭数据库:
SQL> SELECT SWITCHOVER_STATUS,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;
SWITCHOVER_STATUSOPEN_MODE DATABASE_ROLEPROTECTION_MODE
-------------------- -------------------- ---------------- --------------------
RECOVERY NEEDEDREAD WRITE PHYSICAL STANDBY MAXIMUM AVAILABILITY
SQL> shutdown immediate
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL>conn / as sysdba
SQL>startup
开启real time redo apply 应用:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.
查看相关日志,确认切换无误。