Steps to perform switchover in physical standby

 Steps to switchover the Standby database to Primary and Primary database to Standby in Oracle10g

1.    Shutdown the primary database.

SQL> shutdown immediate

2.    Shutdown the standby database.

SQL> shutdown immediate

3.    Startup standby database

SQL> startup nomount

SQL> alter database mount standby database;

4.    Startup primary database

SQL> startup

5.    SQL> alter system archive log current;                     (On primary)

       6.  Start the managed recovery operation:          (On standby)

SQL> recover managed standby database disconnect from session;

7.    Check the MRP process                                            (On standby)

SQL> select process, status from v$managed_standby;

8.    If MRP process exist than stop it                                (On standby)

SQL>alter database recover managed standby database cancel;

9.    Stop and start the listener                               (On both)

$ lsnrctl stop

$ lsnrctl start

10.  Shutdown the primary database.

SQL> shutdown immediate

11.  Shutdown the standby database.

SQL> shutdown immediate

12.  Startup standby database

SQL> startup nomount

SQL>alter database mount standby database;

13.  Startup primary database

SQL> startup

14.  Check switchover status                                (On primary)

SQL>select switchover_status from v$database;

            It should be to_standby.

15.  Now run commands                                       (On primary)

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

SQL> alter database commit to switchover to physical standby;

SQL> shutdown immediate

SQL>startup nomount

SQL>alter database mount standby database;

SQL> alter system set log_archive_dest_state_2=defer;

SQL> recover managed standby database disconnect from session;

Now primary have converted in standby.

16.  Check switchover status                                (On old standby)

SQL>select switchover_status from v$database;

It should be switchover_pending.

17.  Run the command                                          (On old standby)

SQL> alter database commit to switchover to primary;

18.  Shutdown the database                                              (On old standby)

SQL> shutdown immediate

SQL> startup

SQL> alter database force logging;

SQL> alter system set log_archive_dest_state_2=enable;

Now old standby have converted in Primary database.

 NOTE:-   If you want to Check  that data of primary is applying on standby or not than you can check by these steps:-

1. Make a test table

            SQL> create table test(id number(20));         (On new primary)

2. Switch the log to send the redo data to the standby database:

SQL> alter  system switch logfile;

3. Start managed recovery, if necessary, and applies the archived redo logs by entering the following SQL statement:

SQL> alter database recover managed standby database;   (On new standby)

4. Cancel managed recovery operations.

                        SQL> alter database recover managed standby database cancel;

            This command will stop the MRP process. Now open the database in read only mode.

                        SQL> alter database open read only

5. After that if you want standby database in archive recover mode than firstly you will have to shutdown & startup the standby database.

                        SQL> shutdown immediate

                        SQL> startup nomount

                        SQL> alter database mount standby database;

6. Start the managed recovery operation on standby database:

SQL> recover managed standby database disconnect from session;