12cR1 RAC Posts -- 8i : Switchback from SingleInstance to RAC

Earlier this week (10-April), I had done a Switchover from the 2node RAC database to a SingleInstance database.

It is time now to Switchback from SingleInstance to RAC.

First, I check the status of the two databases :

On STBY (the current Primary) :

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> select instance_name, host_name from v$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STBY
oem132.racattack


SQL>

Next, I check on the RAC database instance RAC1 (the current Standby) :

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

SQL> select instance_name, status, host_name from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
HOST_NAME
----------------------------------------------------------------
RAC1             MOUNTED
collabn1.racattack

RAC2             MOUNTED
collabn2.racattack


SQL>

I also confirm that only one of the two RAC instances is doing recovery (in 12.1 we have only 1 instance in RAC doing recovery) by verifying the (automatic) message in the alert log for RAC1 :

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
Sun Apr 16 23:05:50 2017
Attempt to start background Managed Standby Recovery process (RAC1)
Starting background process MRP0
Sun Apr 16 23:05:50 2017
MRP0 started with pid=52, OS id=16739
Sun Apr 16 23:05:50 2017
MRP0: Background Managed Standby Recovery process started (RAC1)
Sun Apr 16 23:05:55 2017
 Started logmerger process
Sun Apr 16 23:05:56 2017
Managed Standby Recovery starting Real Time Apply
Sun Apr 16 23:06:10 2017
Parallel Media Recovery started with 2 slaves
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY

Now, I add some data to the PDB Pluggable Database currently running in STBY :

[oracle@oem132 ~]$ sqlplus hemant/hemant@PDB

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 16 23:29:37 2017

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

Last Successful login time: Mon Apr 10 2017 23:43:30 +08:00

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

SQL> select count(*) from my_data;

  COUNT(*)
----------
       300

SQL> create table my_new_table as select * from my_data;

Table created.

SQL>

I am now ready to switchover from STBY to RAC. However, I have a problem because I started the RAC database (and , therefore, DataGuard Broker) before I  started the SingleInstance node and database.  I have drcRAC1.log and drcRAC2.log both reporting :

04/16/2017 23:05:38
Failed to connect to remote database stby. Error is ORA-12543
Failed to send message to site stby. Error code is ORA-12543.
database rac unable to contact primary database for version check; status ORA-12543
      completing bootstrap of this database

The fix is to have the Standby RAC database started *after* the singleInstance Primary and verify that Managed Recovery is restarted in RAC1 :

[oracle@collabn1 ~]$ srvctl stop database -d RAC
[oracle@collabn1 ~]$ srvctl start database -d RAC


ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
Sun Apr 16 23:43:58 2017
Attempt to start background Managed Standby Recovery process (RAC1)
Starting background process MRP0
Sun Apr 16 23:43:59 2017
MRP0 started with pid=53, OS id=2033
Sun Apr 16 23:43:59 2017
MRP0: Background Managed Standby Recovery process started (RAC1)
Sun Apr 16 23:44:03 2017
RFS[2]: Assigned to RFS process (PID:1922)
RFS[2]: Selected log 5 for thread 1 sequence 76 dbid 2519807290 branch 931825279
Sun Apr 16 23:44:04 2017
 Started logmerger process
Sun Apr 16 23:44:07 2017
Managed Standby Recovery starting Real Time Apply
RFS[1]: Selected log 7 for thread 1 sequence 78 dbid 2519807290 branch 931825279
Sun Apr 16 23:44:20 2017
Archived Log entry 144 added for thread 1 sequence 77 ID 0x96bd0722 dest 1:
Sun Apr 16 23:44:25 2017
Archived Log entry 145 added for thread 1 sequence 76 ID 0x96bd0722 dest 1:
Sun Apr 16 23:44:27 2017
Parallel Media Recovery started with 2 slaves
Sun Apr 16 23:44:27 2017
Waiting for all non-current ORLs to be archived...
Sun Apr 16 23:44:27 2017
All non-current ORLs have been archived.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY

Now, recheck the configuration from the SingleInstance node and then  Switchover to RAC :

[oracle@oem132 ~]$ dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/racattack@STBY
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - rac

  Protection Mode: MaxPerformance
  Members:
  stby - Primary database
    rac  - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 16 seconds ago)

DGMGRL> switchover to rac;
Performing switchover NOW, please wait...
Operation requires a connection to instance "RAC1" on database "rac"
Connecting to instance "RAC1"...
Connected as SYSDBA.
New primary database "rac" is opening...
Operation requires start up of instance "STBY" on database "stby"
Starting instance "STBY"...
Unable to connect to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oem132.racattack)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBY_DGMGRL)(INSTANCE_NAME=STBY)(SERVER=DEDICATED)))
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        start up and mount instance "STBY" of database "stby"

DGMGRL>

The ORA-12514 error here is acceptable. I only need to startup STBY manually.

DGMGRL> exit
[oracle@oem132 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 16 23:51:25 2017

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             289409776 bytes
Database Buffers          541065216 bytes
Redo Buffers                5455872 bytes
SQL> alter database mount standby database;

Database altered.

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

SQL> select instance_name, status, host_name from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
HOST_NAME
----------------------------------------------------------------
STBY             MOUNTED
oem132.racattack


SQL>

STBY has now reverted to being a Standby.

Let me check the RAC database instances

[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 16 23:55:28 2017

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

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> select instance_name, status, host_name from gv$instance;

INSTANCE_NAME    STATUS
---------------- ------------
HOST_NAME
----------------------------------------------------------------
RAC2             OPEN
collabn2.racattack

RAC1             OPEN
collabn1.racattack


SQL>

Yes, RAC is now Primary with both instances OPEN.

Let me (now on RAC) verify the new table created and populated when STBY was the Primary. 

SQL> alter pluggable database pdb open;
alter pluggable database pdb open
*
ERROR at line 1:
ORA-65019: pluggable database PDB already open


SQL> alter session set container=PDB;

Session altered.

SQL> select table_name from dba_tables where owner = 'HEMANT';

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA
MY_NEW_TABLE

SQL> select count(*) from hemant.my_new_table;

  COUNT(*)
----------
       300

SQL>

Yes, the data in the Pluggable Database PDB has also come over to RAC.

So, on 10-April, I did a Switchover from RAC (2nodes) to STBY (SingleNode).  Today, I have done a Switchover from STBY to RAC.
.
.
.