Advertisements
Skip to content
August 28, 2012 / Shivananda Rao P

Dataguard Failover

Failover is a one way process where your primary database goes down due to some reasons and to get back the production live without any loss, you convert your existing Physical Standby database to start behaving as Primary database.

I have my primary database as PRPRIM and standby database as PSSTBY

Primary Database: PRPRIM

Standby Database: PSSTBY

Primary Database Server:


SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS INSTANCE_NAME DATABASE_ROLE
------ ------------- -----------------------
OPEN   prprim        PRIMARY

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
TO STANDBY

Standby Database Server:


SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS  INSTANCE_NAME DATABASE_ROLE
------- ------------- -----------------------
MOUNTED psstby        PHYSICAL STANDBY

Now to simulate the failover, I bring down the primary database PRPRIM

Primary:


SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

On the standby database (PSSTBY) perform the below steps:

STEP 1:

Cancel the Managed Recovery Process


SQL> alter database recover managed standby database cancel;

Database altered.

Step 2:

Inform the standby database that the recovery is finished forever.


SQL> alter database recover managed standby database finish;

Database altered.

STEP 3:

Switchover the standby database to Primary role.


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

SQL> select status,instance_name from v$instance;

STATUS   INSTANCE_NAME
-------- --------------------
MOUNTED  psstby

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS  INSTANCE_NAME   DATABASE_ROLE
------- --------------- -----------------
OPEN    psstby          PRIMARY

Here above, you can see that the instance PSSTBY which was in the standby role earlier, has now been converted to behave as
Primary. Now, since PSSTBY is Primary database, there is no standby database available for it. A new standby database will have to be created for PSSTBY.

If flashback was enabled on both PRPRIM and PSSTBY instances, then now we can get back PRPRIM instance to behave as Standby database for PSSTBY which is behaving as Primary database.

Here are the steps to bring back old primary (PRPRIM) as standby database:

On the new Primary instance (PSSTBY):


SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS   INSTANCE_NAME  DATABASE_ROLE
-------- -------------  ----------------------
OPEN     psstby         PRIMARY

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

STEP 1:

Note down the SCN on the new primary database (PSSTBY) at which it started behaving as the Primary database:


SQL> select standby_became_primary_scn from v$database;

STANDBY_BECAME_PRIMARY_SCN
--------------------------
2023466

The SCN at which PSSTBY started behaving as Primary database is 2023466

STEP 2:

Now mount the old primary (PRPRIM) database:

[oracle@dev ~]$ sqlplus sys/oracle@prprim as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 25 21:02:23 2012

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 910266368 bytes
Fixed Size 2231808 bytes
Variable Size 851444224 bytes
Database Buffers 50331648 bytes
Redo Buffers 6258688 bytes
Database mounted.
SQL>
SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES

STEP 3:

Flashback the old primary (PRPRIM) database to the SCN at which PSSTBY became primary database.


SQL> flashback database to scn 2023466;

Flashback complete.

STEP 4:

Now convert the old primary (PRPRIM) to behave as Standby database for PSSTBY (new primary database)


SQL> alter database convert to physical standby;

Database altered.

SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 910266368 bytes
Fixed Size 2231808 bytes
Variable Size 851444224 bytes
Database Buffers 50331648 bytes
Redo Buffers 6258688 bytes
Database mounted.
SQL>

STEP 5:

Start the Managed Recovery Process (MRP) on the new standby database(PRPRIM) and check if MRP is started or not.


SQL> alter database recover managed standby database disconnect from session using current logfile;

Database altered.

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS  INSTANCE_NAME   DATABASE_ROLE
------- --------------- --------------------
MOUNTED prprim          PHYSICAL STANDBY

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS       SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED    0
ARCH      CONNECTED    0
ARCH      CONNECTED    0
ARCH      CONNECTED    0
RFS       IDLE         0

Here above, we can see that MRP is not present under the Process column which in turn means that MRP has not been started.
Let us check the alert log file of my new standby database (PRPRIM)


Sat Aug 25 21:09:59 2012
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +FRA/prprim/archivelog/2012_08_25/thread_1_seq_165.423.792277639
Identified End-Of-Redo (failover) for thread 1 sequence 165 at SCN 0x0.1ee02c
Resetting standby activation ID 0 (0x0)
Incomplete Recovery applied until change 2023468 time 08/25/2012 21:00:03
MRP0: Background Media Recovery applied all available redo. Recovery will be restarted once new redo branch is registered
Errors in file /u01/app/oracle/diag/rdbms/prprim/prprim/trace/prprim_mrp0_7388.trc:
ORA-19906: recovery target incarnation changed during recovery
Managed Standby Recovery not using Real Time Apply&lt
Recovery interrupted!

It says that MRP has been cancelled (Recovery interrupted). For this, we need to start the MRP using the keyword “Through All Switchover“.


SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS       SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING      3
ARCH      CONNECTED    0
ARCH      CONNECTED    0
ARCH      CONNECTED    0
RFS       IDLE         0
RFS       IDLE         0
RFS       IDLE         4
RFS       IDLE         0

8 rows selected.

SQL> alter database recover managed standby database through all switchover disconnect from session using current logfile;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS         SEQUENCE#
--------- ------------   ----------
ARCH      CLOSING        3
ARCH      CONNECTED      0
ARCH      CONNECTED      0
ARCH      CONNECTED      0
RFS       IDLE           0
RFS       IDLE           0
RFS       IDLE           4
RFS       IDLE           0
MRP0      APPLYING_LOG   1

9 rows selected.

Here above, we can see that MRP has been started on the new standby database (PRPRIM) and MRP is applying log sequence 1.
In 11g, when we perform Flashback operation, the log sequence would get started from sequence 1 on both Primary and Standby database just as when the database would be opened with RESETLOGS.

On the new Primary database (PSSTBY) perform a few log switches and check if they are getting applied on the new standby
database (PRPRIM).

PSSTBY:


SQL> alter system switch logfile;

System altered.

SQL> archive log list
Database log mode            Archive Mode
Automatic archival           Enabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   4
Next log sequence to archive 6
Current log sequence         6

PRPRIM:


SQL> select process,status,sequence# from v$managed_standby;

PROCESS      STATUS        SEQUENCE#
---------    -----------   ----------
ARCH         CLOSING       3
ARCH         CONNECTED     0
ARCH         CONNECTED     0
ARCH         CLOSING       5
RFS          IDLE          0
RFS          IDLE          0
RFS          IDLE          6
RFS          IDLE          0
MRP0         APPLYING_LOG  6
9 rows selected.

So, the log sequence 6 generated on the PSSTBY is getting applied to PRPRIM.

On PSSTBY, check the switchover status.

SQL> select status,instance_name,database_role,switchover_status from v$database,v$instance;

STATUS   INSTANCE_NAME    DATABASE_ROLE SWITCHOVER_STATUS
-------- ---------------- ------------- ------------------------
OPEN     psstby           PRIMARY       TO STANDBY

Here we go !!

COPYRIGHT

© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.

DISCLAIMER

The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.

Advertisements

11 Comments

Leave a Comment
  1. Alok / Nov 7 2012 8:54 am

    As usual, nice Article Shiva!! Thanks.

  2. Shivananda Rao P / Nov 7 2012 9:31 am

    Thank you.

  3. Jamsher / Apr 24 2013 7:35 am

    Hi Shiva,

    Thanks for such wonderful article.
    If flashback is off in my database so after failover, do i need to recreate the standby database for new primary ?.
    –Thanks & Regards

  4. Shivananda Rao P / Apr 24 2013 2:28 pm

    Yes Jamsher. You’ll have to recreate the standby database from scratch if flashback is not enabled.

    Regards,
    Shivananda

  5. Roger / May 3 2013 2:58 pm

    Hi Shivanand,
    In failover, dont we use the command
    >alter database activate standby database disconnect from session;

  6. Shivananda Rao P / May 3 2013 4:47 pm

    Hello Roger,

    “Alter database activate standby database” forcefully converts the standby database to primary. Also, using this command can lead to data loss during the failover operation. Please refer this http://docs.oracle.com/cd/B19306_01/server.102/b14239/sql_stmts.htm

    On the other hand, you can run the below two commands to perform failover securely:
    1. alter database recover managed standby database finish;
    This command first applies all available archived redo logs and then continues to recover available standby redo logs.
    2. alter database commit to switchover to primary;
    This switches the standby database to primary role.

    Regards,
    Shivananda

  7. Ashish / Jun 24 2013 11:09 am

    Hi Shiva,
    As you specify if we retained our old prim database as Prim/stdby .So for that Database FLASHBACK_ON should be ON..but in my case it’s off. So please guide me ..which step should i follow?

    Regards,
    Ashish

  8. Shivananda Rao P / Jun 24 2013 4:44 pm

    Hi Ashish,

    If flashback is not enabled and you perform a failover operation, then you’ll have to rebuild your standby database from scratch.

    Regards,
    Shivananda

  9. Ashish / Jun 26 2013 6:21 am

    Thanks Shiva.

    Mean to say we nedd to configured complete DG with backup of Prim Database?

    Regards,
    AShish

  10. Shivananda Rao P / Jun 26 2013 4:23 pm

    Yes Ashish, you are right.

    Regards,
    Shivananda

  11. Ashish / Jun 28 2013 10:48 am

    Hi Shiva,

    Today I configured EM where i seen below status of standby database (2 stdby DB configured) in EM:

    Standby Databases:

    [Add Standby Database]
    [Edit] [Remove] [SWITCHOVER@DGMsgBundle@servletRequest] [Failover] [Convert]

    Select Name Host Data Guard Status Role Real-time Query Last Received Log Last Applied Log Estimated Failover Time
    oas_dr scdl2 [Data Guard Status] Down Physical Standby Not available Not available Not available Not available
    oas_rep reporting [Data Guard Status] Normal Physical Standby Not available 9735 9735 Not available

    OAS_DR showing down

    Then i check all MRP process everything is in synch .

    I login in DGMGRL of PRod DAtbase :
    DGMGRL> show configuration

    Configuration – oas_dg

    Protection Mode: MaxPerformance
    Databases:
    oas – Primary database
    oas_dr – Physical standby database (disabled)
    oas_rep – Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    SUCCESS

    Then i login in OAS_DR:
    It’s taking time
    DGMGRL> show configuration

    Configuration – oas_dg

    Protection Mode: MaxPerformance
    Databases:
    oas – Primary database
    oas_dr – Physical standby database
    oas_rep – Physical standby database

    Fast-Start Failover: DISABLED

    Configuration Status:
    ORA-16665: timeout waiting for the result from a database
    DGM-17017: unable to determine configuration status

    Please suggest me what to do?
    FLASHBACK is ON.

    Regards,
    AShish

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: