ORACLE DG 两种切换方式

ORACLE DATAGUARD主备库切换两种方式

1. switchover
   switchover 切换：主库与备库数据同步正常情况下的切换，主要用于主备维护、切换演练等。
2. failover
   failover 切换：主库与备库在数据未同步情况下的强制切换，主要用于主库宕机、故障情况下切换。

switchover

主库与备库数据同步，且正常运行，将主库与备库的角色互换； 切换前，务必检查当前主库与备库的归档是否是同步的，确认同步后再执行切换

1.原主库操作

1. 查看库的角色，和可以切换到的角色

SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE         SWITCHOVER_STATUS
----------------      --------------------
PRIMARY               TO STANDBY

2. 对主库进行切换（如果SWITCHOVER_STATUS的值为TO STANDBY或者为SESSIONS ACTIVE都可以切换至备库）

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

3. 开启原先的主库至于MOUNT状态

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size            2252864 bytes
Variable Size          805310400 bytes
Database Buffers      452984832 bytes
Redo Buffers            8818688 bytes
Database mounted.

4. 查看切换后的状态

SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- -------------------- ------------------------------------
MOUNTED PHYSICAL STANDBY SESSIONS ACTIVE

2.原备库操作

1. 查看备库是否可以切换至主库（SWITCHOVER_STATUS的值为TO PRIMARY或者为SESSIONS ACTIVE都可以切换至主库）

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO PRIMARY

2. 将备库切换到主库并打开

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

3.新备库操作

1. 对新备库开始进行日志传输APPLY

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

切换完传给后查看数据库角色，切换状态，alter日志如否正常。

failover

假设物理主库宕机，无法启动，紧急启用备库。直接在备库上操作，将备库转换为主库角色。备库上执行下面四条命令即可

SQL > alter database recover managed standby database finish;
SQL > alter database commit to switchover to primary;
SQL > shutdown immediate;
SQL > startup;

具体操作

1. 直接备库failover切换

SQL> alter database recover managed standby database finish;
Database altered.

alert日志

Wed Jul 3 10:29:10 2013
alter database recover managed standby database finish
Wed Jul 3 10:29:10 2013
Terminal Recovery: Stopping real time apply
Wed Jul 3 10:29:12 2013
MRP0: Background Media Recovery cancelled with status 16037
Wed Jul 3 10:29:12 2013
Errors in file /Oracle/admin/sdk/bdump/sdk_mrp0_4113.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Wed Jul 3 10:29:14 2013
Errors in file /oracle/admin/sdk/bdump/sdk_mrp0_4113.trc:
ORA-16037: user requested cancel of managed recovery operation
Wed Jul 3 10:29:14 2013
MRP0: Background Media Recovery process shutdown (sdk)
Wed Jul 3 10:29:15 2013
Terminal Recovery: Stopped real time apply
SKIP STANDBY LOGFILE option no longer needed for RECOVERFINISH. Option ignored
Wed Jul 3 10:29:15 2013
Attempt to do a Terminal Incomplete Recovery (sdk)
Wed Jul 3 10:29:15 2013
Media Recovery Start: Managed Standby Recovery (sdk)
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 266
Terminal Recovery timestamp is '07/03/2013 10:29:15'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 266 redo required
Terminal Recovery: End-Of-Redo log allocation
MRP: Validating standby redo logfile 4
Wed Jul 3 10:29:15 2013
Media Recovery Log /oracle/oradata/sdk/redo04_standby
Terminal Recovery: log 4 reserved for thread 1 sequence 266
Wed Jul 3 10:29:15 2013
Recovery of Online Redo Log: Thread 1 Group 4 Seq 266 Reading mem 0
Mem# 0: /oracle/oradata/sdk/redo04_standby
Identified End-Of-Redo for thread 1 sequence 266
Terminal Recovery: Updated next available block for thread 1 sequence 266 lno 4 to value 2
Wed Jul 3 10:29:16 2013
Incomplete recovery applied all redo ever generated.
Recovery completed through change 1993928
Wed Jul 3 10:29:16 2013
Media Recovery Complete (sdk)
Terminal Recovery: successful completion
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Resetting standby activation ID 3126107239 (0xba549c67)
Completed: alter database recover managed standby database finish

2. 再直接备库转换为主库

SQL> alter database commit to switchover to primary;

3. 关闭备库

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

4. 开启备库

SQL> startup 

ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2084456 bytes
Variable Size 184549784 bytes
Database Buffers 226492416 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.

5. 查看转换角色

SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------------- -------------------- ------------------------------------
READ WRITE PRIMARY NOT ALLOWED

现在备库成为了主库角色，failover切换完成；

注意

现在只有一个主库了，alert日志里会说找不到备库：

Wed Jul 3 10:48:53 2013
Errors in file /oracle/admin/sdk/bdump/sdk_arc0_4211.trc:
ORA-12560: TNS:protocol adapter error
PING[ARC0]: Heartbeat failed to connect to standby 'sdk_primary'. Error is 12560.
Wed Jul 3 10:53:56 2013
Error 12560 received logging on to the standby

failover切换完成后，由于只有一个数据库了，建议立即对数据库做全备，并尽快处理原主库的故障，启动后，重新执行DataGuard数据同步，恢复物理主备模式；