DATAGUARD FAILOVER STEPS

-

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 primary and standby database as standby

Primary Database: primary

Standby Database: standby

Primary Database Server:


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

STATUS INSTANCE_NAME DATABASE_ROLE
------ ------------- -----------------------
OPEN   primary        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 standby        PHYSICAL STANDBY

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

Primary:


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

SQL>


On the standby database (standby) 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  standby

SQL> alter database open;

Database altered.

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

STATUS  INSTANCE_NAME   DATABASE_ROLE
------- --------------- -----------------
OPEN    standby          PRIMARY

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

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

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

On the new Primary instance (standby):

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

STATUS   INSTANCE_NAME  DATABASE_ROLE
-------- -------------  ----------------------
OPEN     standby         PRIMARY

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
YES
STEP 1:

Note down the SCN on the new primary database (standby) 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 standby started behaving as Primary database is 2023466

STEP 2:

Now mount the old primary (primary) database:

[oracle@dev ~]$ sqlplus sys/oracle@primary 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 (primary) database to the SCN at which standby became primary database.


SQL> flashback database to scn 2023466;

Flashback complete.
STEP 4:

Now convert the old primary (primary) to behave as Standby database for standby (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(primary) 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 primary          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 (primary)


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/primary/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/primary/primary/trace/primary_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 (primary) 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 (standby) perform a few log switches and check if they are getting applied on the new standby
database (primary).

standby:

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
primary:


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 standby is getting applied to primary.

On standby, 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     standby           PRIMARY       TO STANDBY

Here we go !!

Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify the standby database and see if the co
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12        HEADING 'SID,Serial' COLUMN unix_pid     FORMAT a6         HEADING 'O/S|PID' COLUMN Client_User  FORMAT a14        HEADING 'Cl
Read more