CONVERTING PHYSICAL STANDBY DATABASE TO SNAPSHOT
STANDBY DATABASE
All the below steps will be carried
out on Physical standby.
Step 1:
Check if Flashback is enabled. If not, enable it.
a)
SQL> Show parameter db_recovery_file_dest
NAME TYPE
VALUE
---------------------------------------------------------------
----------- ------------------------------
db_recovery_file_dest
string
db_recovery_file_dest_size
big integer 20G
Above two
parameters are mandatory to enable the flashback. Set them if they are not set
b)
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
The
flashback is not enabled so let us enable it first
c) SQL> recover managed standby database
cancel;
d)
SQL> Shutdown immediate;
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
e)
SQL> startup mount;
ORACLE
instance started.
Total System
Global Area 209715200 bytes
Fixed Size
788524 bytes
Variable
Size 162887636 bytes
Database
Buffers 41943040 bytes
Redo Buffers
4096000 bytes
Database
mounted.
f)
SQL> alter database flashback on;
Database
altered.
g)
SQL> select flashback_on from v$database;
FLASHBACK
---------
YES
h)
SQL> alter database open;
Database
altered.
i)
SQL> select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME
OPEN_MODE GUARD_S DATABASE_ROLE
----------------------------------------
---------- ------- ----------------
OLPTP1 READ
ONLY NONE PHYSICAL STANDBY
Step 2 :
Cancel recovery on Physical Standby
Database if it is running,
SQL>
alter database recover managed standby database cancel;
Database
altered.
Step 3 :
Converting Physical Standby database to Snapshot Standby database
SQL>
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
Database
altered.
Step 4:
Shutdown the Standby Database Normal
SQL>
shut immediate
ORACLE
instance shut down.
Step 5:
Startup Standby database Normal
SQL>
Startup
ORACLE
instance started.
Total System
Global Area 4175568896 bytes
Fixed Size
2166288 bytes
Variable
Size 2852127216 bytes
Database
Buffers 1308622848 bytes
Redo Buffers
12652544 bytes
Database
mounted.
Database
opened.
Step
6: Check Database role
SQL>
Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME
OPEN_MODE GUARD_S DATABASE_ROLE
--------------------------------------
---------- ------- -------------------------------
OLPTP1 READ
WRITE NONE SNAPSHOT STANDBY
At this point your physical standby
became primary. Database is in read/write mode enjoyJ
Now if you want to revert back it
again to standby configuration follow the below steps.
CONVERTING SNAPSHOT STANDBY DATABASE TO PHYSICAL
STANDBY DATABASE
Step 1:
Check for current database role
SQL>
Select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME
OPEN_MODE GUARD_S DATABASE_ROLE
-----------------------------------------
---------- ------- ----------------
OLPTP1 READ
WRITE NONE SNAPSHOT STANDBY
Step 2 :
Shutdown Snapshot Standby Database
SQL>
shut immediate
Database
closed.
Database
dismounted.
ORACLE
instance shut down.
Step 3 :
Start in Nomount mode
SQL>
Startup nomount
ORACLE instance
started.
Total System
Global Area 4175568896 bytes
Fixed Size
2166288 bytes
Variable
Size 2852127216 bytes
Database
Buffers 1308622848 bytes
Redo Buffers
12652544 bytes
Step 4:
Mounting Snapshot Standby Database
SQL>
alter database mount;
Database altered.
Step 5:
Converting Snapshot Standby to Physical Standby Database
SQL>
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database
altered.
Step 6:
Shutdown the database
SQL>
shut immediate
ORACLE
instance shut down.
Step 7:
Starting database in nomount mode
SQL>
startup nomount
ORACLE
instance started.
Total System
Global Area 4175568896 bytes
Fixed Size
2166288 bytes
Variable
Size 2852127216 bytes
Database
Buffers 1308622848 bytes
Redo Buffers
12652544 bytes
SQL>
alter database mount standby database;
Database
altered.
Step 8 : start
the media recovery
SQL>
recover managed standby database disconnect from session;
Media
recovery complete.
Note:- Do
some log switch in production and verify whether the log shipping /log apply is
progressing at DR
SQL>
select process, status ,sequence# from v$managed_standby;
PROCESS STATUS
SEQUENCE#
---------
------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 WAIT_FOR_LOG 16
Shows that
archives apply is under process as we are recovering standby database
(IMPORTANT
NOTE : RFS - implies redo transfer from primary to standby is under process.
MRP0 -
indicates it is applying archive logs in standby)
Step 10:
Check in primary database for any error
SQL>select
error from v$archived_dest;
Step 12 :
Check for database role
SQL>
select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;
NAME
OPEN_MODE GUARD_S DATABASE_ROLE
---------------------------------
---------- ------- ----------------
OLPTP1 READ
ONLY NONE PHYSICAL STANDBY
No comments:
Post a Comment