Wednesday, 6 August 2014

Converting Physical standby to snapshot database

Converting Physical standby to snapshot database :

Steps:
1.Show parameter db_recovery_file_dest
NAME TYPE VALUE
--------------------------------------------------------------- ----------- ------------------------------
db_recovery_file_dest string u03/oradata/ODSP1/flash_recovery_area/
db_recovery_file_dest_size big integer 2G

2. select flashback_on from v$database;
  if not set it ON in mount mode:
alter database flashback on;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK
---------
YES

3.Verify DB mode
select NAME, OPEN_MODE, GUARD_STATUS, DATABASE_ROLE from v$database;

NAME OPEN_MODE GUARD_S DATABASE_ROLE
---------------------------------------- ---------- ------- ----------------
OLPTP1 READ ONLY NONE PHYSICAL STANDBY

4.  Cancel recovery from Primary Database to Physical Standby Database

SQL> alter database recover managed standby database cancel;

5.ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

6. Shutdown immediate.

7. Startup Standby database Normal

SQL> Startup
ORACLE instance started.

8. 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

9. DONE.
*****Reverse i.e back to Physical Standby.
1. Shutdown.
2. Startup mount
3.Converting Snapshot Standby to Physical Standby Database
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
Database altered.
4. Shutdown immediate
5. Startup mount
6. Start manual or MRP
7 or 5.5. 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