Oracle RAC standby 12.1.0.2 database switchover (without Data Guard Broker)

This blog post details database switchover in the following configuration created in my previous blog post:

primary node 1 primary node 2 standby node 1 standby node 2
hostname ol7tocn1 ol7tocn2 ol7tocn3 ol7tocn4
DB_NAME DOT DOT DOT DOT
INSTANCE_NAME DOT1 DOT2 DTF1 DTF2
DB_UNIQUE_NAME DOT DOT DTF DTF
SCAN listener name ol7toc-scan ol7toc-scan ol7toc2-scan ol7toc2-scan
datafile disk group name +DATA +DATA +DATA +DATA
fast recovery area disk group name +FRA +FRA +FRA +FRA

What is switchover

Data Guard Concepts and Administration says that switchover
allows the primary database to switch roles with one of its standby databases. There is no data loss during a switchover. After a switchover, each database continues to participate in the Oracle Data Guard configuration with its new role.

Oracle Data Guard 11g Handbook page 20 (written by several Oracle Corp. employees implementing the Data Guard product) describes switchover process the following way:

  • notifies the primary database that a switchover is about to occur.
  • disconnects all users from the primary.
  • generates a special redo record the signals the End Of Redo (EOR).
  • converts the primary database into a standby database.
  • once the standby database applies the final EOR record, guaranteeing that no data has been lost, converts the standby to the primary role.

No data loss with switchover means that no transaction committed on the primary before the EOR is lost: it must be applied on the standby otherwise switchover cannot successfully complete.

I have used the new 12.1 statements for switchover.

STEP 1 Verify that the target standby database is ready for switchover.

I have connected to primary database instance DOT1 with OPS$ORACLE (to whom DBA role has been granted) and I have run:

OPS$ORACLE@DOT1>alter database switchover to dtf verify;

Database altered.

OPS$ORACLE@DOT1>

DOT1 alert log says:

Thu Aug 13 20:01:40 2015
alter database switchover to dtf verify
Thu Aug 13 20:01:43 2015
SWITCHOVER VERIFY: Send VERIFY request to switchover target DTF
SWITCHOVER VERIFY COMPLETE
Completed: alter database switchover to dtf verify

DTF1 alert log says:

Thu Aug 13 20:01:59 2015
SWITCHOVER VERIFY BEGIN
SWITCHOVER VERIFY COMPLETE

STEP 2 Initiate the switchover on the primary database.

From same SQL*Plus session on primary database DOT1 I have run:

OPS$ORACLE@DOT1>alter database switchover to dtf;
alter database switchover to dtf
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 16298
Session ID: 74 Serial number: 48624

I have been disconnected because switchover has shutdown both primary database instances:

DOT1 alert log is here.

DOT2 alert log only says:

Thu Aug 13 20:05:36 2015
Switchover in progress in another database instance - Database is shutdown automatically
LGWR (ospid: 10324): terminating the instance due to error 16456
Thu Aug 13 20:05:37 2015
Instance terminated by LGWR, pid = 10324

DTF1 alert log is here.

DTF2 alert log is here.

At this step, new primary (former standby) instances are still in MOUNT mode:

[oracle@ol7tocn3 ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       ol7tocn3                 STABLE
               ONLINE  ONLINE       ol7tocn4                 STABLE
ora.FRA.dg
               ONLINE  ONLINE       ol7tocn3                 STABLE
               ONLINE  ONLINE       ol7tocn4                 STABLE
ora.LISTENER.lsnr
               ONLINE  ONLINE       ol7tocn3                 STABLE
               ONLINE  ONLINE       ol7tocn4                 STABLE
ora.OCRVD.dg
               ONLINE  ONLINE       ol7tocn3                 STABLE
               ONLINE  ONLINE       ol7tocn4                 STABLE
ora.asm
               ONLINE  ONLINE       ol7tocn3                 Started,STABLE
               ONLINE  ONLINE       ol7tocn4                 Started,STABLE
ora.net1.network
               ONLINE  ONLINE       ol7tocn3                 STABLE
               ONLINE  ONLINE       ol7tocn4                 STABLE
ora.ons
               ONLINE  ONLINE       ol7tocn3                 STABLE
               ONLINE  ONLINE       ol7tocn4                 STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       ol7tocn4                 STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       ol7tocn3                 STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       ol7tocn3                 STABLE
ora.MGMTLSNR
      1        ONLINE  ONLINE       ol7tocn3                 169.254.197.48 192.1
                                                             68.43.213,STABLE
ora.cvu
      1        ONLINE  ONLINE       ol7tocn3                 STABLE
ora.dtf.db
      1        ONLINE  INTERMEDIATE ol7tocn3                 Mounted (Closed),STA
                                                             BLE
      2        ONLINE  INTERMEDIATE ol7tocn4                 Mounted (Closed),STA
                                                             BLE
ora.mgmtdb
      1        ONLINE  ONLINE       ol7tocn3                 Open,STABLE
ora.oc4j
      1        ONLINE  ONLINE       ol7tocn3                 STABLE
ora.ol7tocn3.vip
      1        ONLINE  ONLINE       ol7tocn3                 STABLE
ora.ol7tocn4.vip
      1        ONLINE  ONLINE       ol7tocn4                 STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       ol7tocn4                 STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       ol7tocn3                 STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       ol7tocn3                 STABLE
--------------------------------------------------------------------------------
[oracle@ol7tocn3 ~]$

STEP 3 Open the new primary database

I have connected with SYSDBA on DTF1 database instance and run:

[oracle@ol7tocn3 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 13 20:10:28 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SYS@DTF1>select name, db_unique_name, database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
DOT       DTF                            PRIMARY

SYS@DTF1>alter database open;

Database altered.

SYS@DTF1>

DTF1 alert log is here.

DTF2 alert log only says:

Thu Aug 13 20:10:50 2015

* instance 1 validates domain 0

At this step, DTF2 instance is still in MOUNTED mode and must be switched to OPEN mode.

I have connected to DTF2 instance with SYSDBA privilege and run:

[oracle@ol7tocn4 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 13 20:14:23 2015

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SYS@DTF2>select name, db_unique_name, database_role from v$database;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
DOT       DTF                            PRIMARY

SYS@DTF2>select inst_id, open_mode from gv$database;

   INST_ID OPEN_MODE
---------- --------------------
         2 MOUNTED
         1 READ WRITE

SYS@DTF2>alter database open;

Database altered.

SYS@DTF12>

DTF2 alert log is here.

At this step both new primary database instances are now in OPEN mode:

SYS@DTF1>select inst_id, open_mode from gv$database;

   INST_ID OPEN_MODE
---------- --------------------
         1 READ WRITE
         2 READ WRITE

SYS@DTF1>

STEP 4 Mount the new physical standby database.

I have run on primary instance node:

[oracle@ol7tocn1 ~]$ srvctl start database -d DOT -o mount
[oracle@ol7tocn1 ~]$ srvctl status database -d DOT
Instance DOT1 is running on node ol7tocn1
Instance DOT2 is running on node ol7tocn2
[oracle@ol7tocn1 ~]$

DOT1 alert log is here.

DOT2 alert log is here.

STEP 5 Start Redo Apply on the new physical standby database.

I have connected with SYSDBA privileges to DOT1 and run (note NODELAY clause replacing USING CURRENT LOGFILE clause):

SYS@DOT1>alter database recover managed standby database nodelay disconnect;

Database altered.

SYS@DOT1>

DOT1 alert log is here.

DOT2 alert log only says:

Thu Aug 13 20:31:03 2015
Managed Standby Recovery starting Real Time Apply

STEP 6 update Oracle Cluster Registry (OCR).

Note that OCR has not been updated:

– new standby database has still former primary role:

[oracle@ol7tocn1 ~]$ srvctl config database -d DOT
Database unique name: DOT
Database name: DOT
Oracle home: /u01/app/12.1.0.2/db
Oracle user: oracle
Spfile: +DATA/DOT/PARAMETERFILE/spfile.289.887485975
Password file: +DATA/DOT/PASSWORD/pwddot.274.887484967
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DOT1,DOT2
Configured nodes: ol7tocn1,ol7tocn2
Database is administrator managed
[oracle@ol7tocn1 ~]$

– new primary database has still former standby role:

[oracle@ol7tocn3 ~]$ srvctl config database -d DTF
Database unique name: DTF
Database name:
Oracle home: /u01/app/12.1.0.2/db
Oracle user: oracle
Spfile: +DATA/DTF/spfileDTF.ora
Password file:
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DTF1,DTF2
Configured nodes: ol7tocn3,ol7tocn4
Database is administrator managed
[oracle@ol7tocn3 ~]$

This means that if cluster nodes are rebooted database instances will start with OCR start option and will not give the expected results.

To fix this I have run on primary cluster:

[oracle@ol7tocn3 ~]$ srvctl modify database -d DTF -r PRIMARY -s open
[oracle@ol7tocn3 ~]$ srvctl config database -d DTF
Database unique name: DTF
Database name:
Oracle home: /u01/app/12.1.0.2/db
Oracle user: oracle
Spfile: +DATA/DTF/spfileDTF.ora
Password file:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools:
Disk Groups: DATA,FRA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DTF1,DTF2
Configured nodes: ol7tocn3,ol7tocn4
Database is administrator managed

And I have run on standy cluster:

[oracle@ol7tocn1 ~]$ srvctl modify database -d DOT -r PHYSICAL_STANDBY -s mount
[oracle@ol7tocn1 ~]$ srvctl config database -d DOT
Database unique name: DOT
Database name: DOT
Oracle home: /u01/app/12.1.0.2/db
Oracle user: oracle
Spfile: +DATA/DOT/PARAMETERFILE/spfile.289.887485975
Password file: +DATA/DOT/PASSWORD/pwddot.274.887484967
Domain:
Start options: mount
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: FRA,DATA
Mount point paths:
Services:
Type: RAC
Start concurrency:
Stop concurrency:
OSDBA group: dba
OSOPER group: dba
Database instances: DOT1,DOT2
Configured nodes: ol7tocn1,ol7tocn2
Database is administrator managed
[oracle@ol7tocn1 ~]$

Now everything should be ready for cluster reboot except that redo apply will not start automatically on standy cluster: as far as I know this is not possible with Grid Infrastructure 12c.

Conclusion

RAC standby database switchover is very close to single instance database switchover (described here):

What has not changed in 12c:

  • A RAC standby database has only one instance applying redo
  • during switchover new standby database instances must be restarted manually

What has changed in 12c (in addition to the new SQL statements):

  • during switchover all former primary database instances are shutdown: documentation says :
    note that as of Oracle Database 12c Release 1 (12.1), when you perform a switchover from an Oracle RAC primary database to a physical standby database, it is no longer necessary to shut down all but one primary database instance
    .
Advertisements
Post a comment or leave a trackback: Trackback URL.

Comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: