Oracle11g Data Guard manual switch over steps

The Manual Switch over steps were tested in oracle 11.2.0.4 and below steps were followed in Critical production database many times in the past and switch over were completed successfully!
We have primary and two standby database on this data guard environment. I tested the switch over between primary(devdb12) and first standby(devdb12_dg).
Step 1 Database verification

1. Verify the standby database and make sure standby database is in sync with primary database.
2. Tail the alert log on both primary and standby to monitor the database during the entire switch over time.  Use this command  :  tail -f  alert_$ORACLE_SID.log
3. Make sure below data guard parameters(log_archive_dest, log_archive_dest_state, log_archive_config, fal_client, fal_server etc) are correctly configured on standby site. This would help log  shipping go smooth after the switch over.

Let application team bring the application down.  Now DBA start switching over the database!
Step 2 Database Active Connection Verification

Make sure, no application connection on the database.
select count(*),username from v$session group by v$session
To be safer side, run the below script to kill any non local process to complete our switch over smoothly.
tokill=`ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'`; echo $tokill;

tokill=`ps -ef | grep LOCAL=NO | grep -v grep | awk '{print $2}'`; kill -9 $tokill;

Step 3 The primary and standby should show the status as below.

set linesize 200

col DB_UNIQUE_NAME form a30

col DATABASE_ROLE for a20

col OPEN_MODE for a30

col SWITCHOVER_STATU for a30

select DB_UNIQUE_NAME,Database_role,open_mode,switchover_status from v$database;

The Primary database SWITCHOVER_STATUS can be either TO STANDBY or SESSIONS ACTIVE.  The standby database SWITCHOVER_STATUS should be NOT ALLOWED.

Step 4: Convert Primary to Standby:

On Primary

Execute the below command on primary (devdb12)

alter database commit to switchover to physical standby with session shutdown;

At this stage, the primary database(devdb12) is not completely converted to standby.  The primary database is down and it is ready to covert for standby database. The standby database (devdb12_dg and devdb12_dr) are ready to convert to primary.  After completing Step 6, the primary(devdb12) will be turned to standby. Step 6 can be executed on this step. But it is always good practice to start standby after starting the primary to reduce the application down time. All three database status should show as below after running the above command.

The devdb12 SWITCHOVER_STATUS should be  RECOVERY NEEDED. The devdb12_dg,devdb12_dr  database SWITCHOVER_STATUS should be TO PRIMARY

Step 5:  Convert Standby to Primary

We are converting devdb12_dg to primary database. Hence login as devdb12_dg and run the below command.

alter database commit to switchover to primary with session shutdown;

alter database open;

After opening the database, the status should be as below. The switchover_status is FAILED_DESTINATION.

Now devdb12_dg becomes primary and ready to take the transactions.  Still it is not ready to ship the archive log file.  At this moment, DBA can ask application team to start the application. Now application is pointing to new primary(devdb12_dg). The old primary(devdb12) and second standby(devdb12_dr)  status should be as below. The  old primary(devdb12) switch over status is RECOVERY_NEEDED. The second standby(devdb12_dr) switch over status is NOT ALLOWED.

Step 6: - Convert Original Primary(devdb12)  to Standby
Login to old primary(devdb12) and run the below command.

shutdown immediate;

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect from session;
-- Wait for few minutes and make sure archive logs are shipping to standby database
alter database recover managed standby database cancel;

alter database open;

alter database recover managed standby database using current logfile disconnect;

Step 7  Verify the database mode for both primary and standby. Now devdb12 is standby and devdb12_dg is primary database. Devdb12_dr is still standby and no change on devdb12_dr.

Step 8 Verify that archive logs are shipping correctly.

Standby database UNNAMED file error issue

This Scenario was tested in Active Data Guard 11.2.0.4.

Some time, DBA create or add the data file on primary site with out verifying the file system size on standby site. In this case, standby file creation will fail and log shipping will break in standby database. 

Let us test this scenario. For this testing, devdb12 will be primary DB and devdb12_dg will be standby DB. The standby site /data01 file system size is lower than the primary site.

Let us add data file on primary site.

sys@devdb12> create tablespace test datafile

'/data01/oradata/devdb12/test.f01' size 3000m

/

Tablespace created.

sys@devdb12>

The data file was created successfully in primary. But it is failed in standby due to insufficient disk space. We get the below error message in standby alert log. MRP background process shutdown due to this issue.

The data file is created in primary site and not found in standby database.

sys@devdb12> select status,bytes/1024/1024 from
  2   v$datafile where name= '/data01/oradata/devdb12/test.f01';

STATUS                BYTES/1024/1024
--------------------- ---------------
ONLINE                           3000
sys@devdb12>

sys@devdb12_dg> select status,bytes/1024/1024 from
  2   v$datafile where name= '/data01/oradata/devdb12/test.f01';

no rows selected

sys@devdb12_dg>

However, the data file is created in standby site as below.

SQL> select name from v$datafile where name like '%UNNAME%';

NAME
----------------------------------------------------
/ora/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006

SQL>

Now the Data Guard replication is broken and file was not able to ship to standby due to sufficient space on the /data01 file system.

How do we fix this issue?

Step 1  Add More space in standby /data01 file system and run the below command in standby database. If you don't have luxury to add space, then we can  map the file to different file system in standby database.

Step 2 Execute the below command in standby database. If you decided to create the data file in different file system, then modify the script accordingly. 

shutdown immediate;
startup nomount;
alter database mount standby database;
alter system set standby_file_management='MANUAL' scope=both;
alter database create datafile  '/ora/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/data01/oradata/devdb12/test.f01';
alter system set standby_file_management='AUTO' scope=both;
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect;

Step 3   Verify the datafile in standby database.

devdb12_dg> select status,bytes/1024/1024 from
v$datafile where name= '/data01/oradata/devdb12/test.f01';
  2
STATUS                BYTES/1024/1024
--------------------- ---------------
ONLINE                           3000

devdb12_dg>

Step 4 Monitor the alert log on primary and standby and  make sure no issues