What are the Prerequest and Postrequest of Dataguard Switchover

Answer:             
  the Primary database is "NOIDA"                                standby database is "RED"

Step 1 :  Verify whether it is possible to perform a switchover 
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY


Step  2  : Check that there is no active users connected to the databases.
SQL> select distinct osuser,username from v$session;


Step  3 :SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;  AT STDBY
         

Step  4 : Switch the current online redo log file on primary database and verify that it has been appleid 
            SQL>alter system switch logfile ;
                  System altered

Step 5 :SQL>SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


Step  6 : Verify that received redo has been applied on standby
           SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
           
The value of the APPLIED column for the most recently received log file will be
              either IN-MEMORY or YES if that log file has been applied.


Step 7:Verify there is no log file gap between the primary and the standby database.

                   7.1)You can determine the status by querying V$ARCHIVE_DEST_STATUS.
                       SQL> select status, gap_status from v$archive_dest_status where dest_id = 2;

                       STATUS    GAP_STATUS
                      --------- ------------------------
                        VALID     NO GAP

                  7.2)Verify there are no large GAPS.On primary
                        SQL>SELECT THREAD#, SEQUENCE# FROM V$THREAD;
                        On the standby the following query should be no more than 1-2 less than the primary query                             result
                        SQL>SELECT THREAD#, MAX(SEQUENCE#) FROM V$ARCHIVED_LOG val,                                V$DATABASE vdb WHERE APPLIED = 'YES' AND                                                                              val.RESETLOGS_CHANGE#=vdb.RESETLOGS_CHANGE#GROUP
                        BY THREAD#;

Step 8: Pre-Switchover Checks
Verify Managed Recovery is Running (non-broker) on the standby
SQL>select process from v$managed_standby where process like 'MRP%';

Cancel apply delay for the target standby using SQL
SQL>select delay_mins from v$managed_standby where process = 'MRP0';

if delay_mins >0 then
SQL>RECOVER MANAGED STANDBY DATABASE CANCEL
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Step 9 :SQL> select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
————–
           168

Step 10 : Verify Primary and Standby TEMP Files Match
SQL>select tmp.name FileName, bytes, ts.name Tablespace from v$tempfile tmp, v$tablespace ts where tmp.ts#=ts.ts#;
   If there is no match then you will correct the temp tbs problem after switchover




Step  11 : Connect with primary database and initiate the switchover 
C:\>sqlplus sys/xxxx@noida as sysdba
SQL> alter database commit to switchover to physical standby;
Database altered.

Now, the primary database is converted into standby database.The controlfile is backed up to the current SQL session trace
file before the switchover. This makes it possible to reconstruct a current control file,if necessary.If we try to perform a
switchover when other instances are running then we will get ORA-01105 as follows :

SQL>alter database commit to switchover to standby ;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ORA-01105: mount is incompatible with mounts by other instances

In order to perform a switchover, run below command on the primary database.

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

* The above statement first terminates all active sessions by closing the primary database. Then any non-archived redo log files
are transmitted and applied to standby database. Apart from that an end-of-redo marker is added to the header of the last log
file that was archived.A backup of current control file is created and the current control file is converted into a standby control file.

* After mounting the database notice that "alert.log" states that the database is now a Physical Standby database.
Thu Jun 03 11:12:34 2010
Successful mount of redo thread 1, with mount id 460768413
Physical Standby Database mounted.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area  250560512 bytes
Fixed Size                  2113848 bytes
Variable Size             218107592 bytes
Database Buffers           25165824 bytes
Redo Buffers                5173248 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter system set log_archive_dest_state_2=defer;
System altered.
SQL>

11.1) Following command needs to be done on standby database

SQL> select open_mode,database_role from v$database;
    OPEN_MODE  DATABASE_ROLE

     MOUNTED    PHYSICAL STANDB

SQL> select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
————–
           171

Step  12 : Shut down and restart the  primary instance(RED).
SQL>shutdown immediate;
SQL> startup mount ;

Step  13 : Verify the switchover status in the v$database view.
        After we change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, we should verify if the switchover notification was processed by the target standby database by querying the "switchover_status" olumn of the v$database fixed view on the target standby database.

  On old Primary database(noida)

SQL> select name,open_mode,db_unique_name from v$database;
  NAME      OPEN_MODE       DB_UNIQUE_NAME         SWITCHOVER_STATUS
------     -----------    --------------------    ----------------------
   NOIDA     MOUNTED             noida               TO PRIMARY

On old standby database (RED)
SQL> select name,open_mode,db_unique_name,switchover_status from v$database;
NAME        OPEN_MODE         DB_UNIQUE_NAME     SWITCHOVER_STATUS
------     ------------    -----------------    ---------------------
NOIDA       MOUNTED               red             TO PRIMARY

Step 14 : Switch the target physical standby database role to the primary role
We can switch a physical standby database from the standby role to the primary role when the standby database instance is either mounted in Redo Apply mode or open for read-only access. It must be in one of these modes so that the primary database switchover request can be coordinated. After the standby database is in an appropriate mode, issue the following sql statement on the physical standby database that we want to change to the primary role:

SQL>alter database commit to switchover to primary ;
Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area        263639040 bytes
Fixed Size                               1373964 bytes
Variable Size                           213911796 bytes
Database Buffers                     41943040 bytes
Redo Buffers                          6410240 bytes
Database mounted.
Database opened.

Step  15  : Check the new primary database(RED) and switch logfile :

SQL> select open_mode from v$database;
OPEN_MODE
---------------
READ WRITE
Note :  it's a good idea to perform a log switch on the primary .

SQL> alter system switch logfile;
System altered.

Step 16  :  Open new standby database(Noida) in read-write 

SQL> alter database open;
Database altered

SQL> select name,open_mode ,db_unique_name,switchover_status  from v$database;

  NAME        OPEN_MODE             DB_UNIQUE_NAME         SWITCHOVER_STATUS
   ------      -------------      ------------------        -----------------------
    NOIDA     READ ONLY        noida                        RECOVERY NEEDED

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select  name,open_mode  from  v$database;
NAME              OPEN_MODE        
---------          ---------------------------------
NOIDA            READ ONLY WITH APPLY


SQL> select max(sequence#) from v$log_history;
MAX(SEQUENCE#)
————–
           176

The switchover_status column of v$database can have the following values:
1.Not Allowed    :  Either this is a standby database and the primary database has not been switched first, or this is a primary database  and there are no standby databases.
2.Session Active :  Indicates that there are active SQL sessions attached to the primary or standby database that need to be disconnected before the switchover operation is permitted.
3.Switchover Pending : This is a standby database and the primary database switchover request has been received but not processed.
4.Switchover Latent :  The switchover was in pending mode, but did not complete and went back to the primary database.
5.To Primary :  This is a standby database, with no active sessions, that is allowed to switch over to a primary database.
6.To Standby :  This is a primary database, with no active sessions, that is allowed to switch over to a standby database.
7.Recovery Needed : This is a standby database that has not received the switchover request