2 Node RAC to Single Instance Dataguard Switchover (Oracle 11gR2 Physical Standby )

Many a times to save cost in building a DR solution for a certain application; you have to create a single instance database for your RAC primary. So you have gone ahead and build the 2-node to single instance setup and now comes the time to test it. Well it is not much different than any other switchover scneario but the difference is that the switchover will be done with one node active in the primary (I prefer to use the OCR master node, check for OCR master node using “ocrconfig -showbackup” command)
This procedure was done on Oracle 11gR2 11.2.0.3 on Solaris 64-bit SPARC servers.
Now let us begin our planned switchover. But before we begin I hope you have checked all the necessary parameters on both sides. Viz.

Log_archive_dest_1
Log_archive_dest_2
Log_archive_dest_state_1
Log_archive_dest_state_2
Fal_client
Fal_server
Local_listener
Remote_listener
Standby_archive_Dest
Standby_archive_management
service_names
db_unique_name
instance_name
db_file_name_convert
log_file_name_convert

Verify the logs on both sites are in sync and logs are properly applied on the standby database.

On Primary,
Select thread#,max(sequence#) from v$archived_log group by thread#;
On Standby,
Select thread#,max(sequence#) from v$log_history group by thread#;

Now we begin the actual switch over procedure
1. Shutdown RAC DB and STARTUP AGAIN TO CLEAR SESSIONS. I always prefer this method after shutting down the application

srvctl stop database -d primary
srvctl start database -d primary
-- Shutdown any service if you have defined one --
srvctl stop service -d swx -s primaryservice

2. Shutdown Standby database and Restart Recovery

shutdown immediate;
startup mount;
alter database recover managed standby database using current logfile disconnect from session;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
-- Create a Guaranteed Restore point on Standby Database in case of a rollback if something goes wrong --
CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

3. Shutdown Node 2 in cluster

srvctl stop instance -d primary -n primarynode2

4. Take Guaranteed Flashback Restore points on Primary

CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

5. Verify that the primary database can be switched to the standby role

SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG CURRENT;

— Actual SWAPOVER Procedure —
6. Initiate Shutdown on Primary( Ensure 2nd Instance is shutdown)

alter database commit to switchover to physical standby with session shutdown;
Shutdown immediate;
Startup mount;
select name,db_unique_name, log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database;

Make log_Archive_Dest_state_2 to DEFER

alter system set log_archive_dest_state_2='DEFER' sid='*';

7. On the (old) standby database.

select name,log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database;

On the (old) standby database switch to new primary role:

alter database commit to switchover to primary WITH SESSION SHUTDOWN;
shutdown immediate;
startup;

8. On new Primary database

select name,log_mode,open_mode,controlfile_type,switchover_status,database_role from v$database;

Make log_Archive_Dest_state_2 to ENABLE

alter system set log_archive_dest_state_2='ENABLE' sid='*';
alter system switch logfile;

On new primary,

select error from v$archive_Dest_status;
select max(sequence#) from v$archived_log;

9. On new Standby, Start Redo Apply

alter database recover managed standby database using current logfile disconnect;
Select max(sequence#) from v$log_history; (should be matching with Primary)

10. Now Start RAC databases services (both Primary – in open & Standby – in mount)

srvctl start instance –d primary -n primarynode2 -o mount

Check if the mrp0 process is saying “APPLYING_LOG” on the new standby database. Check with below query.
select process,status from v$managed_standby;
If the logs are shipping and applying if not check the alert logs for any errors. The general error in an assymetrical rac is due to password files. If the logs not applying and you seeing like below error in the alert log of new primary.
PING[ARC2]: Heartbeat failed to connect to standby ‘swx’. Error is 16191.
Error 1017 received logging on to the standby
Solution : RECREATE THE PASSWORD FILES FOR NODE1 if failover is happening from NODE1
User format as orapw$ORACLE_SID for Standby database
eg: orapwstandbydatabase
User format as orapw$ORACLE_SID for node 1
eg: orapwprimarynode1
User format as orapw$ORACLE_SID for node 2
eg: orapwprimarynode2
Create the password file in folder $ORACLE_HOME/dbs on all nodes and DR server as Oracle(Database) user