Oracle 11gR2(MAA) Physical Standby Databases Role Transfer

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;

 

SWITCHOVER_STATUS

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

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 Size            2243072 bytes

Variable Size             1375733248 bytes

Database Buffers   6157238272 bytes

Redo Buffers              14221312 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_STATUS   OPEN_MODE                 DATABASE_ROLE   PROTECTION_MODE

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

TO STANDBY       READ WRITE          PRIMARY      MAXIMUM PERFORMANCE

 

回到original primary database上：

刚开始的时候该数据库需要恢复然后自动关闭数据库：

SQL> SELECT SWITCHOVER_STATUS,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE FROM V$DATABASE;

 

SWITCHOVER_STATUS   OPEN_MODE                 DATABASE_ROLE   PROTECTION_MODE

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

RECOVERY NEEDED     READ 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.

 查看相关日志，确认切换无误。