Oracle 19c two node RAC with Standby alone (single DB) - DR Drill

 RAC Configuration:

We have 2-node RAC configuration on Linux System with single instance Dataguard Setup but with ASM Grid.

Note: If you have 2-node setup on both primary and standby side or higher node setup on both nodes then it doesn’t matter whether you shut down all nodes or not, it will terminate rest of instances. Later you can startup these instances. But as per Oracle recommendation, one node from Primary and one node from standby server should be down.

               Step 1: Please perform the prerequisites before proceeding further:

•       Check whether both primary and standby databases are in sync.
•       Check whether you can access both primary and standby databases remotely via sqlplus connectivity.
•       Check tnsping connectivity from both primary and standby database servers.
•       Check Telnet connectivity whether Listener PORT is working fine.
•       See any critical ORA- error in alert log file.
•       Check all Cluster Services are up and running fine.
•       Check primary and standby destination status are valid.
•       Switch 2-3 logs from both primary to standby server to verify whether logs are getting applied on standby database successfully.

Step 2: Confirm with an Application Developer whether he has stopped all Application Services connecting to the respective database. Also check any remote connection to the database from non-oracle users.

Step 3: Inform an Application Developer and Client 15 minutes prior the activity that we will start switchover activity in next 15 minutes.

Step 4: As per Oracle Recommendation, all primary and standby instances must be down except one from primary and one from standby.

For Example:

we have 5 instances on Primary and 5 instances on standby server. We need to shut down 4 instances from primary and 4 instances from standby.

Once the switchover activity will be over, then we can start all instances (Primary and Standby) which had shut down previously.

In our configuration, we will shut down node2 instance only. In standby server, we have single instance setup and hence we are not shutting down any instance on standby.

Note:

In short, one primary and one standby database must be up and remaining must be down prior the switchover activity.

Step 5: Shutdown primary instance on node 2(rac2).

If you will not shut down the database, later Oracle will shut it down automatically once switchover command gets executed.

Run below command on any node by oracle:

$srvctl stop instance -d ORCL -i ORCL2

PRCD-1131 : Failed to stop database ORCL and its services on nodes rac2
PRCR-1133 : Failed to stop database ORCL and its running services
PRCR-1132 : Failed to stop resources using a filter
CRS-2974: unable to act on resource 'ora.orcl.db' on server 'rac2' because that would require stopping or relocating resource 'ora.orcl.orcl_srv.svc' but the appropriate force flag was not specified

[oracle@rac2 ~]$ srvctl status service -d ORCL -s orcl_srv
Service ORCL_SRV is running on instance(s) ORCL1,ORCL2

[oracle@rac2 ~]$ srvctl stop service -d ORCL -i ORCL2

Now you can stop database on instance 2 

$srvctl stop instance -d ORCL -i ORCL2

Step 6: Initiate switchover activity.

On Node1(rac1), by oracle user:

Primary:

SQL> select name,database_role,controlfile_type,open_mode from gv$database;

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

Standby:

SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;

SQL> select process,status,sequence# from v$managed_standby;

SQL> select THREAD#,STATUS,ENABLED,SEQUENCE# from v$thread order by 1;

   THREAD# STATUS ENABLED   SEQUENCE#

---------- ------ -------- ----------

        1 OPEN   PUBLIC      270

        2 OPEN   PRIVATE     144

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)

---------- --------------

        1           259

        2           141

On Primary:

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE

------------ ---------------- ----------------

OPEN         ORCL1            PRIMARY

On Standby:

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE

------------ ---------------- ----------------

MOUNTED       ORCLDG           PHYSICAL STANDBY

On Primary: Node1:- Run the below Verify command on Primary 

Verify that the target standby database is ready for switchover.

The new switchover statement has a VERIFY option that results in checks being performed of many conditions required for switchover. Some of the items checked are: whether Redo Apply is running on the switchover target; whether the release version of the switchover target is 12.1 or later; whether the switchover target is synchronized; and whether it has MRP running.

SQL> alter database switchover to ORCLDG verify;

alter database switchover to ORCLDG verify

2021-08-15T12:55:30.509138+02:00

SWITCHOVER VERIFY: Send VERIFY request to switchover target XPLIVEDG

SWITCHOVER VERIFY COMPLETE: READY FOR SWITCHOVER

Completed: alter database switchover to ORCLDG verify

OR

alter database switchover to ORCLDG verify
*
ERROR at line 1:
ORA-16475: succeeded with warnings, check alert log for more details

When the command is executed, an ORA-16475 error was encountered. For more details, lets walk through the PRIMARY and STANDBY databases alert.log file, and pay attention to the SWITCHOVER VERIFY WARNING

--primary database alert.log

Fri Oct 13 11:16:00 2017
SWITCHOVER VERIFY: Send VERIFY request to switchover target ORCLDG

SWITCHOVER VERIFY COMPLETE
SWITCHOVER VERIFY WARNING: switchover target has no standby database defined in LOG_ARCHIVE_DEST_n parameter.
If the switchover target is converted to a primary database, the new primary database will not be protected.

ORA-16475 signalled during:  alter database switchover to ORCL verify...

The LOG_ARCHIVE_DEST_2 parameter was not set on the STANDBY database and the VERIFY command produced the warning. After setting the parameter on the STANDBY, the verify command was re-ran, and it went well this time.

Step 7:- Switchover in 19c 
After successful validation and confirmation about the DBs readiness for the role transition, execute the actual switchover command on the primary database. (advised to view the alert.log files of PRIMARY and STANDBY instances).
SQL> alter database switchover to ORCLDG;

Note: Check alert log for primarny and standby
           primary database alert.log 
           standby database alert.log
Completed: alter database switchover to ORCLDG
Shutting down instance (abort)

Note: The new PRIMARY database is in MOUNT state, so you need to OPEN the database.
SQL> select status from v$instance;

STATUS
---------
MOUNTED

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS    INSTANCE_NAME    DATABASE_ROLE
--------- ---------------- ----------------
MOUNTED   ORCLDG            PRIMARY

SQL> alter database open;

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS   INSTANCE_NAME    DATABASE_ROLE 
-------- ---------------- --------------
OPEN     ORCLDG           PRIMARY

RAC Node1 and Node2:-
The former primary database (XPLIVE) is behaving as a physical standby database.


Startup the STANDBY database and enable MRP

SQL> startup nomount;
SQL> alter database mount standby database;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;


Step 8: Ask an Application Developer about the time till application will be running on standby side. Once gets confirmation from him then follow below procedures to switchback.


Note: Now your old primary became new standby and vice-versa.


Step 9: Please perform the prerequisites before proceeding further:
Check whether both primary and standby databases are in sync.
Check whether you can access both primary and standby databases remotely via sqlplus connectivity.
Check tnsping connectivity from both primary and standby database servers.
Check Telnet connectivity whether Listener PORT is working fine.
See any critical ORA- error in alert log file.
Check all Cluster Services are up and running fine.
Check primary and standby destination status are valid.
Switch 2-3 logs from both primary to standby server to verify whether logs are getting applied on standby database successfully.

Step 10: Initiate switchback activity.