Step by Step Converting a Failed Primary Into a Standby Database Using Flashback Database
On
Primary
Flashback must enable on the primary
database to bring back the failed primary database in the data guard
configuration without rebuilding it. Also minimum flashback logs must be
available to flashback the failed primary database to a SCN (ie standby_became_primary_scn;)
SQL>
select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
---------
-------------------- ----------------
APXITR2 READ WRITE PRIMARY
SQL>
show parameter recovery
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
db_recovery_file_dest string
/apxrdbms/product/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 20G
If db_recovery_file_des
and db_recovery_file_dest_size is not
set follow the below step to set it.
SQL>
alter system set db_recovery_file_dest_size=10G;
SQL>
alter system set db_recovery_file_dest =< some location where the flashback
logs will be created>
Check whether
the flashback is enabled or not. And enable the flashback if it not enabled
SQL>
select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
Enable the
flashback at database level
SQL>
shut immediate
Database
closed.
Database
dismounted.
ORACLE instance
shut down.
SQL>
startup mount
ORA-32004:
obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE
instance started.
Total System
Global Area 2154590208 bytes
Fixed
Size 2215104 bytes
Variable
Size 872416064 bytes
Database
Buffers 1258291200 bytes
Redo
Buffers 21667840 bytes
Database
mounted.
SQL>
alter database flashback on;
Database
altered.
SQL>
select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
SQL>
alter database open;
Database
altered.
SQL>
alter system switch logfile;
System
altered.
(Note:-Before
simulating the failover makes sure your primary and physical standby is in sync
to avoid any data loss. In real scenarios we don’t have manual control on this
step as the failover can occur at any time. And we need not to do the failover manually)
SQL>
shut abort
ORACLE
instance shut down.
ON
PHYSICAL STANDBY
On physical
standby database check the media recovery is going on , if not start it and
apply the maximum possible archives.
SQL> recover managed standby database disconnect
from session;
Media
recovery complete.
SQL>
select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
---------
-------------------- ----------------
APXITR2 MOUNTED PHYSICAL STANDBY
SQL>
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database
altered.
SQL>
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
Database
altered.
SQL>
select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
---------
-------------------- ----------------
APXITR2 MOUNTED PRIMARY
Note :- if
you check the alertlog of standby database you will get a line like this
"Standby became primary SCN: 7497799799226"
SQL> ALTER DATABASE OPEN;
Database
altered.
SQL>
select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
---------
-------------------- ----------------
APXITR2 READ WRITE PRIMARY
Note:- At
this point your physical standby database has become primary and the original
primary is no longer available
Now start
your application from DR site and enjoy….Let us after 10Days you got your
primary back and you want it bring it back in DC/DR configuration without rebuilding
it. Just follow the below steps.
Identify the STANDBY_BECAME_PRIMARY_SCN
from the current primary database and
note the SCN
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
FROM V$DATABASE;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
----------------------------------------
7497799799226
On the Old primary database (the original primary
database that was failed)
SQL>
shut immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
SQL>
startup mount;
ORA-32004:
obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE
instance started.
Total System
Global Area 2154590208 bytes
Fixed
Size 2215104 bytes
Variable
Size 872416064 bytes
Database
Buffers 1258291200 bytes
Redo
Buffers 21667840 bytes
Database
mounted.
SQL>
FLASHBACK DATABASE TO SCN 7497799799226;ßThis is the SCN that you got in the
above step
Flashback
complete.
SQL>
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database
altered.
SQL>
SHUTDOWN IMMEDIATE;
ORA-01507:
database not mounted
ORACLE
instance shut down.
SQL>
STARTUP MOUNT;
ORA-32004:
obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE
instance started.
Total System
Global Area 2154590208 bytes
Fixed
Size 2215104 bytes
Variable
Size 872416064 bytes
Database
Buffers 1258291200 bytes
Redo
Buffers 21667840 bytes
Database
mounted.
SQL>
select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
---------
-------------------- ----------------
APXITR2 MOUNTED PHYSICAL STANDBY
You are done.
The failed primary now successfully became the standby of the current primary. Now
configure the log shipping from the current primary to the current standby and
start the media recovery on the standby.
SQL> recover managed standby database disconnect
from session;
Media
recovery complete.
Check the log
progress on the standby.
select
process,sequence#,status from v$managed_standby;
PROCESS SEQUENCE# STATUS
---------
---------- ------------
ARCH 7 CLOSING
ARCH 0 CONNECTED
ARCH 0 CONNECTED
ARCH 0 CONNECTED
MRP0 8 WAIT_FOR_LOG
RFS 0 IDLE
RFS 0 IDLE
RFS 0 IDLE
RFS 8 IDLE
At this point
of time your original primary is in standby mode and the original standby is in
primary mode.
If you want
to bring the original primary as a PRIMARY and the original standby as STANDBY
only do a switchover.
Steps to
perform the switchover will be covered in the next post….Enjoy JJ
Leave your
questions/comments here. I will be happy to answer your queries
Thanks Anand.. It is Very Helpful..
ReplyDelete