Oracle 11g dataguard手动switchover

Oracle DG切换类型有两种：switchover和failover。

计划内的切换称为switchover，下面实际操作一次：

1.将主库切换为备库：

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

Database altered.

日志信息如下：

ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 18699] (orcl)
Wed Oct 10 09:43:04 2018
Thread 1 advanced to log sequence 80 (LGWR switch)
  Current log# 2 seq# 80 mem# 0: /u01/oradata/orcl/redo02.log
Wed Oct 10 09:43:04 2018
Stopping background process CJQ0
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
Active process 3815 user 'oracle' program 'oracle@qht115 (W000)'
CLOSE: all sessions shutdown successfully.
Waiting for all non-current ORLs to be archived...
Waiting for the ORL for thread 1 sequence 79 to be archived...
ORL for thread 1 sequence 79 has been archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential Physical Standby switchover target to become synchronized...
Active, synchronized Physical Standby switchover target has been identified
Switchover End-Of-Redo Log thread 1 sequence 80 has been fixed
Switchover: Primary highest seen SCN set to 0x3.0x70610aff
ARCH: Noswitch archival of thread 1, sequence 80
ARCH: End-Of-Redo Branch archival of thread 1 sequence 80
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_1
ARCH: Standby redo logfile selected for thread 1 sequence 80 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 516 added for thread 1 sequence 80 ID 0x5a3430cb dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received alls redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also received all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/db115/orcl/trace/orcl_ora_18699.trc
Clearing standby activation ID 1513369803 (0x5a3430cb)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 10 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl6.f' SIZE 536870912;
ALTER DATABASE ADD STANDBY LOGFILE 'srl7.f' SIZE 536870912;
Archivelog for thread 1 sequence 80 required for standby recovery
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN

主要注意到正常应该有“End-Of-Redo Branch archival”字样，并且最终成功切换到standby

提示：Database shutdown required，所以要手动来一个shutdown操作：

SQL> shutdown immediate;

 

2.备库切换为主库：

操作之前，可以看alert日志，也可以使用SQL查询是否可以切换：

SQL> select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY           YES DISABLED NONE

SWITCHOVER_STATUS：switch over 的状态,为'To PRIMARY'表示switch over时转换为primary。

开始切换

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.
--如果因为活动会话无法关闭可以考虑加上with session shutdown选项。

日志信息如下：

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
Wed Oct 10 08:56:45 2018
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/db131/orcl/trace/orcl_mrp0_12070.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (orcl)
Role Change: Canceled MRP
krsv_proc_kill: Killing 2 processes (all RFS)
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Wed Oct 10 08:56:49 2018
SMON: disabling cache recovery
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/db131/orcl/trace/orcl_ora_12061.trc
SwitchOver after complete recovery through change 14770309887
Online log /u01/oradata/orcl/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/oradata/orcl/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/oradata/orcl/redo03.log: Thread 1 Group 3 was previously cleared
Online log /u01/oradata/orcl/redo04.log: Thread 1 Group 4 was previously cleared
Online log /u01/oradata/orcl/redo05.log: Thread 1 Group 5 was previously cleared
Online log /u01/oradata/orcl/redo06.log: Thread 1 Group 6 was previously cleared
Standby became primary SCN: 14770309885
Switchover: Complete - Database mounted as primary
Completed: ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
Wed Oct 10 08:57:21 2018
ARC0: Becoming the 'no SRL' ARCH

出现一个ORA-16037错误，是由于取消日志应用之后，没有MRP0的进程了，这个可以忽略

最后注意到备库成功切换到主库，启动到mount状态。

 

3.把新主库open，新备库启动并开启MRP:

新主库打开：

SQL> ALTER DATABASE OPEN;

Database altered.

新备库开启同步模式：

SQL> shutdown immediate;
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@qht131 archivelog]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Oct 10 13:49:25 2018

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.


SQL> startup mount;
ORACLE instance started.

Total System Global Area 2588999680 bytes
Fixed Size                  2231072 bytes
Variable Size             956302560 bytes
Database Buffers         1610612736 bytes
Redo Buffers               19853312 bytes
Database mounted.
SQL>  alter database open read only;

Database altered.

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

Database altered.

4.测试ADG

新主库：

SQL> create table l5m.t_test2 as select * from dba_users;

Table created.

新备库：

SQL> select count(*) from l5m.t_test2;

  COUNT(*)
----------
        50

5.遇到的问题

主库切换为备库后，新备库的SWITCHOVER_STATUS为NOT ALLOWED了，以为出现了问题，其实这个是正常情况。

参考：http://www.dataguru.cn/thread-162420-1-1.html

文档的原话是：NOT ALLOWED - Either this is a standby database and the primary database has not been switched first or this is a primary database and there are no standby databases.

就是说主库还没有开始switch时，备库的状态就 应该是NOT ALLOWED

SQL>  select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED          YES DISABLED NONE

当主从再次做switchover时，主库执行完 ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN后，备库的SWITCHOVER_STATUS是变为“TO PRIMARY”。

SQL>  select OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS    FOR DATAGUAR GUARD_S
-------------------- ---------------- -------------------- --- -------- -------
MOUNTED              PHYSICAL STANDBY TO PRIMARY           YES DISABLED NONE