Miladin Modrakovic's Blog: Oraclue

1.    Verify that it is possible to perform a switchover operation. On the primary query the switchover_status column of v$database to verify that switchover to standby is possible.

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————
TO STANDBY ( or SESSIONS ACTIVE is also ok)


SESSIONS 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. Query the V$SESSION view to identify the specific processes that need to be terminated.

In order to perform a switchover all sessions to the database need to be disconnected.Nothing to worry because ( with session shutdown) will take care of it.

Shutdown ALL RAC instances BUT ONE before attempt a switchover!

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

select thread#, max(sequence#) “Last Primary Seq Generated”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

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

2.  Shutdown the former primary and mount as a standby database:

SQL> shutdown immediate
SQL> startup mount

3.  Defer the remote archive destination on the old primary( new standby):

SQL> alter system set log_archive_dest_state_2=defer;

4.  Verify that the physical standby can be converted to the new primary:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
——————
TO PRIMARY

Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause.

5.  Convert the physical standby to the new primary:

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

This will start ALL INSTANCES on PRIMARY database!

6.  Shutdown and startup the new primary:

SQL> shutdown immediate
SQL> startup

7.    Enable remote archiving on the new primary to the new standby( former primary ):

SQL > alter system set log_archive_dest_state_2=enable;

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Verify that logs are received on new standby ( old primary ):

SQL> select thread#, max(sequence#) “Last Standby Seq Received”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
group by thread# order by 1;

8.  Start managed recover on the new standby ( old primary ) database:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE;

Verify that logs are applied:

PhyStdby:SQL>select thread#, max(sequence#) “Last Standby Seq Applied”
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#
and val.applied=’YES’
group by thread# order by 1;