dataguard 主备切换

dataguard 主备切换总结：

系统版本：

SQL> select * from v$version 
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0  Production

关于主备库的切换命令

在主库上

SQL> select switchover_status from v$database
  2  /

SWITCHOVER_STATUS
--------------------
FAILED DESTINATION

应该是主备库的监听没起来，启动主备库的监听，然后

SQL> select switchover_status from v$database
  2  /

SWITCHOVER_STATUS
--------------------
TO STANDBY

这说明是可以切换到备库的

SQL> alter database commit to switchover to physical standby
  2  /

Database altered.

测试数据库已经关闭了

SQL> select status from v$instance
  2  /
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 2994
Session ID: 191 Serial number: 9

查看预警日志， 因为在转为备库成功以后，oracle会有一个自我关闭的过程哦~

Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
USER (ospid: 2994): terminating the instance
Instance terminated by USER, pid = 2994
Completed: alter database commit to switchover to physical standby
Shutting down instance (abort)
License high water mark = 7
Thu Sep 07 11:11:25 2017
Instance shutdown complete
Thu Sep 07 11:11:44 2017

那我们就将数据库启动到mount状态吧

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1060585472 bytes
Fixed Size          2260000 bytes
Variable Size         666895328 bytes
Database Buffers      385875968 bytes
Redo Buffers            5554176 bytes
Database mounted.

再到备库上去做相关的操作：

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> alter database commit to switchover to primary
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

预警日志：

我们的预警日志详细记录了该过程。在转换完成以后,oracle 默认会被启动到mount 状态

alter database commit to switchover to primary
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl)
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
Thu Sep 07 11:22:38 2017
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/orcl_dg/orcl/trace/orcl_pr00_2428.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Thu Sep 07 11:22:38 2017
MRP0: Background Media Recovery process shutdown (orcl)
Role Change: Canceled MRP
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Thu Sep 07 11:22:39 2017
SMON: disabling cache recovery
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl_dg/orcl/trace/orcl_ora_2583.trc
SwitchOver after complete recovery through change 1195464
Online log /u01/app/oracle/oradata/orcl_dg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/orcl_dg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/orcl_dg/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1195462
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary

此时在原来的主库上再

alter database open

SQL> alter database recover managed standby database using current logfile disconnect from session
  2  /

Database altered.

测试一下功能：

在原备库上：

SQL> show parameter db_unique_name

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_unique_name               string  orcl_dg
SQL> conn hdp/123123
Connected.
SQL> desc t 
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                         NUMBER
 NAME                           VARCHAR2(200)

SQL> drop table t 
  2  /

Table dropped.

在原主库上查看

SQL> conn hdp/123123
Connected.
SQL> desc t 
ERROR:
ORA-04043: object t does not exist


SQL> show parameter db_unique_name

NAME                     TYPE    VALUE
------------------------------------ ----------- ------------------------------
db_unique_name               string  orcl_prd

发现该表也被自动drop掉了

后来在主备重新切换的时候还遇到一个问题，我觉得也很典型

SQL> select switchover_status from v$database
  2  /

SWITCHOVER_STATUS
--------------------
NOT ALLOWED

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select switchover_status from v$database
  2  /

SWITCHOVER_STATUS
--------------------
TO PRIMARY

记录下当时alert日志的 报错

alter database commit to switchover to primary
ALTER DATABASE SWITCHOVER TO PRIMARY (orcl)
Maximum wait for role transition is 15 minutes.
Database not available for switchover
  End-Of-REDO archived log file has not been recovered
  Incomplete recovery SCN:0:1196867 archive SCN:0:1218437
Database not available for switchover
  End-Of-REDO archived log file has not been recovered
  Incomplete recovery SCN:0:1196867 archive SCN:0:1218437
Switchover: Media recovery required - standby not in limbo

由于我意味终止了我的虚机：
造成了一个错误，在此也记录一下

In-flux buffer recovery was not started because datafiles were fuzzy beyond in-flux recovery target.
Highest datafile fuzzy SCN: 0.1230813
In-flux buffer recovery target SCN: 0.1218830
Managed Recovery: Not Active posted.
DDE rules only execution for: ORA 1110
----- START Event Driven Actions Dump ----
---- END Event Driven Actions Dump ----
----- START DDE Actions Dump -----
Executing SYNC actions
----- START DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (Async) -----
Successfully dispatched
----- END DDE Action: 'DB_STRUCTURE_INTEGRITY_CHECK' (SUCCESS, 0 csec) -----
Executing ASYNC actions
----- END DDE Actions Dump (total 0 csec) -----
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl_dg/system01.dbf'

我将主库起来以后，再尝试去备库 alter database open
就可以正常打开了。 DBA在日常工作的学习中，尤其在自己的本机环境，不断地试错，不断地总结经验！这样才会在真正的真实环境中做到心中有数! 且正式环境正式上线后，我们其实很少真正愿意去有大的动作了。 平时一直好好的，不要一场不必要的大手术以后，反而真正的动了元气就不好了。