Oracle database 9i – Data Guard：强制切换备库为主库
前面的文章里写过了关于DG的正常切换，正常切换不会破坏DG的环境，是可逆的。
可以看到，在正常切换的时候，主库也需要做出一些配置。
但有时候主库已经无法正常使用，这时候无法像正常切换那样，对主库做出配置，而需要强制将备库切换为主库。
这种情况下的DG切换，将会破坏DG环境，该操作不可逆。
该操作只涉及备库的操作。
具体如下：
先查看下当前备库的状态：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
[oracle@dgstandby ~]$ export ORACLE_SID=primary [oracle@dgstandby ~]$ sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.8.0 - Production on Mon Jun 19 23:56:21 2017 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.8.0 - Production SQL> SQL> select name,database_role,open_mode,switchover_status from v$database; NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS --------- ---------------- ---------- ------------------ PRIMARY PHYSICAL STANDBY MOUNTED SESSIONS ACTIVE SQL> SQL> show parameter log_archive_dest_state_2 NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string DEFER SQL> SQL> SQL> show parameter fal_ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string standby fal_server string primary SQL> |
开始切换：
停止日志应用：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SQL> alter database recover managed standby database cancel; Database altered. SQL> SQL> select name,database_role,open_mode,switchover_status from v$database; NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS --------- ---------------- ---------- ------------------ PRIMARY PHYSICAL STANDBY MOUNTED SESSIONS ACTIVE SQL> |
这个过程中的ALERT日志：
|
1 2 3 4 5 6 7 8 9 |
Tue Jun 20 01:11:49 2017 alter database recover managed standby database cancel Tue Jun 20 01:11:55 2017 MRP0: Background Media Recovery user canceled with status 16037 Recovery interrupted. MRP0: Background Media Recovery process shutdown Tue Jun 20 01:11:55 2017 Managed Standby Recovery Cancelled Completed: alter database recover managed standby database ca |
FINISHED Applay：
对于10gR2与之后的版本：
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH [force|wait|nowait];
对于10gR2之前的版本：
alter database recover managed standby database finish skip standby logfile;
alter database recover managed standby database finish;
这一步执行完了之后，就无法再【recover standby database;】
|
1 2 3 4 5 6 7 8 9 |
SQL> alter database recover managed standby database finish; alter database recover managed standby database finish * ERROR at line 1: ORA-16043: managed recovery session canceled ORA-16137: No terminal recovery is required SQL> |
Alert日志：
|
1 2 3 4 5 6 7 8 9 10 11 |
Tue Jun 20 01:22:48 2017 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH Attempt to do a Terminal Recovery Media Recovery Start: Managed Standby Recovery Media Recovery Waiting for thread 1 seq# 131 There are no standby current logs; terminal recovery is not required. Recovery interrupted. Tue Jun 20 01:23:03 2017 Media Recovery user canceled with status 16137 ORA-16043 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FI... |
将备库切换成主库：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> select name,database_role,open_mode,switchover_status from v$database; NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS --------- ---------------- ---------- ------------------ PRIMARY PHYSICAL STANDBY MOUNTED SESSIONS ACTIVE SQL> SQL> alter database commit to switchover to primary with session shutdown; alter database commit to switchover to primary with session shutdown * ERROR at line 1: ORA-16139: media recovery required SQL> |
Alert日志：
|
1 2 3 4 5 6 7 8 |
Tue Jun 20 01:26:54 2017 alter database commit to switchover to primary with session shutdown Tue Jun 20 01:26:54 2017 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY Database not recovered through End-Of-REDO Database not recovered through End-Of-REDO Switchover: Media recovery required - standby not in limbo ORA-16139 signalled during: alter database commit to switchover to primary wit... |
可以看到，提示需要介质恢复？
这时候可以选择两种方式：
1. 正常的强制切换
2. 非正常的强制切换（直接关库然后起来）：shutdown immediate; startup;
如果想要正常的强制切换，可以按照下面的方式操作：
主库，查看最后的归档日志：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SQL> select * from v$log_history where recid=(select max(recid) from v$log_history); RECID STAMP THREAD# SEQUENCE# FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# ---------- ---------- ---------- ---------- ------------- --------- ------------ 130 947118939 1 130 400346 20-JUN-17 400348 SQL> SQL> SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#) 2 OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG; THREAD LAST ---------- ---------- 1 130 SQL> |
备库：
|
1 2 3 4 5 6 7 8 9 10 |
SQL> recover standby database until cancel; ORA-00279: change 411584 generated at 06/20/2017 01:44:26 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/oradata/primary/archive1_135.dbf ORA-00280: change 411584 for thread 1 is in sequence #135 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> |
激活：
|
1 2 3 4 5 |
SQL> alter database activate standby database; Database altered. SQL> |
Alert日志：
|
1 2 3 4 5 6 7 8 9 10 |
Tue Jun 20 01:37:43 2017 alter database activate standby database Tue Jun 20 01:37:43 2017 ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE RESETLOGS after incomplete recovery UNTIL CHANGE 411584 Resetting resetlogs activation ID 1784548476 (0x6a5e0c7c) Online log 4 of thread 0 was previously cleared Online log 5 of thread 0 was previously cleared Activation complete - Database shutdown not required Completed: alter database activate standby database |
打开到OPEN阶段：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
SQL> select instance_name,status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ primary STARTED SQL> SQL> alter database mount; Database altered. SQL> SQL> select name,database_role,open_mode,switchover_status from v$database; NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS ----------------------------------------------------------------- ---------------- ---------- ------------------ PRIMARY PRIMARY MOUNTED SESSIONS ACTIVE SQL> SQL> alter database open; Database altered. SQL> SQL> select name,database_role,open_mode,switchover_status from v$database; NAME DATABASE_ROLE OPEN_MODE SWITCHOVER_STATUS ----------------------------------------------------------------- ---------------- ---------- ------------------ PRIMARY PRIMARY READ WRITE SESSIONS ACTIVE SQL> |
——————————————————
Done。
