Oraclue

Oracle internals, debugging and undocumented features

Manual Switchover involving RAC 11g using SQL*Plus

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;

Advertisements

5 responses to “Manual Switchover involving RAC 11g using SQL*Plus

  1. Nooresh Bajracharay September 27, 2010 at 6:50 pm

    I would like to ask you which is little bit unreleated to the above post. I am following your steps for switching over the RAC database but seem like my app team needs some more.
    My questiong is about patching the database but with low downtime.
    can we perform the following steps:
    step 1: Apply the binary patch only in standby database.
    setp 2: switchover the database which have patch, and then apply the post installaion patch.
    setp 3: once post installation patch is applied in new primary database then apply the patch in new standby database.

    do you think we can switchover the database when the database binaries patch are in 2 different version.

    Thanks
    Nooresh

  2. oraclue September 28, 2010 at 3:23 pm

    Hi Nooresh,

    Which Oracle version are you using?

    If you are talking about major database upgrades then you cannot just upgrade standby and switchover .
    What you have to do is to temporary convert Physical Standby to Logical standby , propagate changes and then switch..

    Here is good link for you :

    http://www.oracle.com/technetwork/database/features/availability/maa-090890.html

    You will find all documentation, cases and white papers related to your questions.

    If you have any specific question I will be glad to help you but I need details about your environment and what are you trying to do.

    Thanks,

    Miladin

    • Nooresh October 6, 2010 at 12:23 am

      I was reffering to PSU patching. In our RAC primary and standby server we are in 10.2.0.4.5. Since oct psu patch is going to release in next 10 days which we need to apply in our primary and standby RAC env with very small amout of downtime.

      I am not sure if we apply the binary patch in standby ( without running the post installation patch) and then switch over to standby and then run the post installation patch ( which just became primary after switchover). this way we can just have a downtime while running post installation script.

      Thanks
      Nooresh

  3. oraclue October 11, 2010 at 12:17 pm

    I think this Oracle Support note will give you good explanation:

    How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1]

    Thanks,

    Miladin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: