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