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 DCPROC and standby database as DRPROC
Primary
Database Server:
SQL> select
status,instance_name,database_role from v$database,v$instance;
STATUS
INSTANCE_NAME DATABASE_ROLE
------
------------- -------------
OPEN DCPROC
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
DRPROC PHYSICAL STANDBY
Now
to simulate the failover, I bring down the primary database DCPROC
Primary:
SQL> shut immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
On
the standby database (DRPROC) 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
DRPROC
SQL> alter database
open;
Database
altered.
SQL> select
status,instance_name,database_role from v$database,v$instance;
STATUS
INSTANCE_NAME DATABASE_ROLE
------
------------- -------------
OPEN DRPROC PRIMARY
Here
above, you can see that the instance DRPROC which was in the standby role
earlier, has now been converted to behave as Primary. Now, since DRPROC is
Primary database, there is no standby database available for it. A new standby
database will have to be created for DRPROC.
If
flashback was enabled on both DCPROC and DRPROC instances, then now we can get
back DCPROC instance to behave as Standby database for DRPROC which is behaving
as Primary database.
Here
are the steps to bring back old primary (DCPROC) as standby database:
On
the new Primary instance (DRPROC):
SQL> select
status,instance_name,database_role from v$database,v$instance;
STATUS
INSTANCE_NAME DATABASE_ROLE
------
------------- -------------
OPEN DRPROC PRIMARY
SQL> select
flashback_on from v$database;
FLASHBACK_ON
------------
YES
STEP
1:
Note
down the SCN on the new primary database (DRPROC) 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 DRPROC started behaving as Primary database is 2023466
STEP
2:
Now
mount the old primary (DCPROC) database:
[oracle@dev ~]$
sqlplus sys/oracle@DCPROC 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 (DCPROC) database to the SCN at which DRPROC became primary
database.
SQL> flashback
database to scn 2023466;
Flashback
complete.
STEP
4:
Now
convert the old primary (DCPROC) to behave as Standby database for DRPROC (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(DCPROC) 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
DCPROC 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 (DCPROC)
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/DCPROC/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/DCPROC/DCPROC/trace/DCPROC_mrp0_7388.trc:
ORA-19906:
recovery target incarnation changed during recovery
Managed Standby
Recovery not using Real Time Apply
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 CLOSING
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 (DCPROC)
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 (DRPROC) perform a few log switches and check if they
are getting applied on the new standby database (DCPROC).
DRPROC
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
DCPROC
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 DRPROC is getting applied to DCPROC.
On
DRPROC, 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 DRPROC
PRIMARY TO STANDBY
No comments:
Post a Comment