Manually Switchover and Failover of the Database From the Sql Command Prompt

To perform Manual Switchover and Failover your Primary and Standby Database should have configured with the following :-
1. Standby Redo log should be added into the database.
2. Flash back mode should be ON.

Here :
DB1 <---Primary Database
SDB1 <--- Standby Database

I'll perform the switchover and the new environment will be like..

SDB1 <--- Primary Database
DB1 <--- Standby Database

Step 1. (Runs on Prim DB DB1)

For no loss of Data during failover ,
Shut down the Prim Database and Flush the Redo to the Standby Database.

SQL> shut abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1033670656 bytes
Fixed Size                  1349476 bytes
Variable Size             595593372 bytes
Database Buffers          432013312 bytes
Redo Buffers                4714496 bytes
Database mounted.
SQL> alter system flush redo to 'sdb1'; <<--- Stby DB

System altered.

SQL> shut abort
ORACLE instance shut down.


at alert log

******************************************************************************************************
Mon Jun 16 19:31:05 2014
ALTER SYSTEM FLUSH REDO TO 'sdb1' CONFIRM APPLY
ALTER SYSTEM FLUSH REDO TO sdb1 CONFIRM APPLY [Process Id: 969] (db1)
ARCH: STARTING ARCH PROCESSES
Mon Jun 16 19:31:05 2014
ARC0 started with pid=21, OS id=971
ARC0: Archival started
ARCH: STARTING ARCH PROCESSES COMPLETE
Flush redo: No wait for non-current ORLs to be archived
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for dest_id 2 to become synchronized...
ARC0: STARTING ARCH PROCESSES
Mon Jun 16 19:31:06 2014
ARC1 started with pid=22, OS id=973
Mon Jun 16 19:31:06 2014
ARC2 started with pid=23, OS id=975
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Mon Jun 16 19:31:06 2014
ARC3 started with pid=24, OS id=977
Active, synchronized flush redo target has been identified
Managed Real Time Apply recovery running at physical standby
'LOG_ARCHIVE_DEST_2'
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Flush End-Of-Redo Log thread 1 sequence 40 has been fixed
Flush Redo: Primary highest seen SCN set to 0x0.0xdca5c
ARCH: Noswitch archival of thread 1, sequence 40
ARCH: End-Of-Redo Branch archival of thread 1 sequence 40
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log
switch
ARCH: Standby redo logfile selected for thread 1 sequence 40 for destination
LOG_ARCHIVE_DEST_2
Flush End-Of-Redo Log thread 1 sequence 40
Archived Log entry 68 added for thread 1 sequence 40 ID 0x5612bf35 dest 1:
ARCH: Archiving is disabled due to current logfile archival
Primary will wait for sdb1 standby to have applied all redo
Final check for a target standby that has recovered all redo. Check will be
made a few times.
LOG_ARCHIVE_DEST_2 is a potential flush redo target
LOG_ARCHIVE_DEST_2 has also applied all redo from primary
Active, synchronized target has been identified that has applied all the redo
from the primary.
Flush Redo: Primary redo moved to standby


******************************************************************************



Step 2. (Runs on Stby DB SDB1)

FYI... The DB_NUIQUE_NAME of the Standby DB is SDB1 and its DB_NAME is DB1.
So no need to confuse with the Prim Database.

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> recover managed standby database finish;
Media recovery complete.

The Status "TO PRIMARY" and "SESSION ACTIVE" shows that the standby database is ready to perform the switchover operation.

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO PRIMARY



SQL> alter database commit to switchover to primary with session shutdown;

Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DB1       MOUNTED              PRIMARY


*************************************************************
Note:
// If during The Recovery Finish operation there are errors then perform the failover opeartion from as below.

"ALTER DATABASE ACTIVATE STANDBY DATABASE;"

*************************************************************

SQL> alter database open;


Database altered.

SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DB1       READ WRITE           PRIMARY


 at alert log

************************************************************************************************************** 

ALTER DATABASE RECOVER  managed standby database cancel 
Mon Jun 16 19:33:06 2014
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/sdb1/db1/trace/db1_mrp0_876.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (db1)
Managed Standby Recovery Canceled (db1)
Completed: ALTER DATABASE RECOVER  managed standby database cancel 
Mon Jun 16 19:33:24 2014
ALTER DATABASE RECOVER  managed standby database finish 
Attempt to do a Terminal Recovery (db1)
Media Recovery Start: Managed Standby Recovery (db1)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 41
RECOVER FINISH applied through switchover EOR logs and stopped.
Media Recovery Complete: End-Of-REDO (db1)
Attempt to set limbo arscn 0:903772 irscn 0:903772
Completed: ALTER DATABASE RECOVER  managed standby database finish 
Mon Jun 16 19:34:19 2014
alter database commit to switchover to primary with session shutdown
ALTER DATABASE SWITCHOVER TO PRIMARY (db1)
Maximum wait for role transition is 15 minutes.
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Mon Jun 16 19:34:20 2014
SMON: disabling cache recovery
Backup controlfile written to trace file
/u01/app/oracle/diag/rdbms/sdb1/db1/trace/db1_ora_908.trc
SwitchOver after complete recovery through change 903772
Online log /u01/app/oracle/oradata/sdb1/redo01.log: Thread 1 Group 1 was
previously cleared
Online log /u01/app/oracle/oradata/sdb1/redo02.log: Thread 1 Group 2 was
previously cleared
Online log /u01/app/oracle/oradata/sdb1/redo03.log: Thread 1 Group 3 was
previously cleared
Standby became primary SCN: 903770
WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
AUDIT_TRAIL initialization parameter is changed back to its original value as
specified in the parameter file.
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary with session
shutdown
Mon Jun 16 19:34:33 2014
idle dispatcher 'D000' terminated, pid = (17, 1)
Mon Jun 16 19:35:09 2014
alter database open
Mon Jun 16 19:35:09 2014
Assigning activation ID 1444266628 (0x5615c284)
Thread 1 advanced to log sequence 42 (thread open)
Mon Jun 16 19:35:09 2014
ARC2: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 42
Mon Jun 16 19:35:09 2014
ARC3: Becoming the 'no SRL' ARCH
  Current log# 2 seq# 42 mem# 0: /u01/app/oracle/oradata/sdb1/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Jun 16 19:35:09 2014
SMON: enabling cache recovery
Archived Log entry 31 added for thread 1 sequence 41 ID 0x5615c284 dest 1:
ARC2: Becoming the 'no SRL' ARCH
Mon Jun 16 19:35:09 2014
NSA2 started with pid=22, OS id=920
Mon Jun 16 19:35:09 2014
Error 1034 received logging on to the standby
PING[ARC1]: Heartbeat failed to connect to standby 'db1'. Error is 1034.
Error 1034 received logging on to the standby
FAL[server, ARC2]: Error 1034 creating remote archivelog file 'db1'
FAL[server, ARC2]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance db1 - Archival Error. Archiver continuing.
[908] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:7821244 end:7821544 diff:300 (3
seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Starting background process SMCO
Database Characterset is WE8MSWIN1252
No Resource Manager plan active
Mon Jun 16 19:35:09 2014
SMCO started with pid=23, OS id=922
Thread 1 advanced to log sequence 43 (LGWR switch)
  Current log# 3 seq# 43 mem# 0: /u01/app/oracle/oradata/sdb1/redo03.log
ARC3: Becoming the 'no SRL' ARCH
Archived Log entry 32 added for thread 1 sequence 42 ID 0x5615c284 dest 1:
Starting background process QMNC
Mon Jun 16 19:35:12 2014
QMNC started with pid=24, OS id=924
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open
Mon Jun 16 19:35:15 2014
Starting background process CJQ0
Mon Jun 16 19:35:15 2014
CJQ0 started with pid=32, OS id=940
Mon Jun 16 19:35:44 2014
ARC1: STARTING ARCH PROCESSES
Mon Jun 16 19:35:44 2014
ARC4 started with pid=34, OS id=950
ARC4: Archival started
ARC1: STARTING ARCH PROCESSES COMPLETE
Shutting down archive processes
ARCH shutting down
ARC4: Archival stopped
***********************************************************************

Step 3. (Runs on New Standby DB DB1).

Gather the Standby_Became_Primary_Scn value from the New Primary Database SDB1

At SDB1 Site.(New Primary DB)
SQL> select to_char(standby_became_primary_scn) from v$database;

TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
903770

Now AT DB1 Site(New Standby DB)

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1033670656 bytes
Fixed Size                  1349476 bytes
Variable Size             595593372 bytes
Database Buffers          432013312 bytes
Redo Buffers                4714496 bytes
Database mounted.



SQL> flashback database to scn 903770;

Flashback complete.

SQL> alter database convert to physical standby;

Database altered.



at alert log
*************************************************************************** 

ALTER DATABASE   MOUNT
Successful mount of redo thread 1, with mount id 1444289989
Allocated 3981204 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Mon Jun 16 19:42:01 2014
RVWR started with pid=20, OS id=1219
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Mon Jun 16 19:42:30 2014
flashback database to scn 903770
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 2 Seq 39 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/db1/redo02.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 40 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/db1/redo01.log
Identified End-Of-Redo (move redo) for thread 1 sequence 40 at SCN 0x0.dca5c
Incomplete Recovery applied until change 903771 time 06/16/2014 19:30:32
Flashback Media Recovery Complete
Completed: flashback database to scn 903770
Mon Jun 16 19:42:46 2014
Using STANDBY_ARCHIVE_DEST parameter default value as
/u01/app/oracle/fast_recovery_area/db1/archivelog/
RFS[1]: Assigned to RFS process 1222
RFS[1]: Database mount ID mismatch [0x5615c284:0x56161dc5]
(1444266628:1444289989)
RFS[1]: Not using real application clusters
Mon Jun 16 19:44:22 2014
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (db1)
Flush standby redo logfile failed:1649
Clearing standby activation ID 1444069173 (0x5612bf35)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Archivelog for thread 1 sequence 40 required for standby recovery
Shutting down archive processes
Archiving is disabled
Completed: alter database convert to physical standby
destination database instance is 'started' not 'mounted'

*********************************************************************
Step 4.

At New Standby DB (DB1)

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1033670656 bytes
Fixed Size                  1349476 bytes
Variable Size             595593372 bytes
Database Buffers          432013312 bytes
Redo Buffers                4714496 bytes
Database mounted.
SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DB1       MOUNTED              PHYSICAL STANDBY

SQL> select process from v$managed_standby;

PROCESS
---------
ARCH
MRP0
ARCH
ARCH
ARCH
RFS
RFS
RFS
RFS

9 rows selected.

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> recover managed standby database using current logfile disconnect from
session;
Media recovery complete.
SQL> select name,open_mode,database_role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
DB1       READ ONLY WITH APPLY PHYSICAL STANDBY