COVERT ORACLE DATABASE FROM PHYSICAL STANDBY TO SNAPSHOT STANDBY

I- COVERT ORACLE DATABASE FROM PHYSICAL STANDBY TO SNAPSHOT STANDBY

1) STOP DATAGUARD
Code:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2) CREATE RESTORE POINT BEFORE CONVERT DATABASE
Code:
SQL> CREATE RESTORE POINT DBNAME_YYYYMMMDD_B4_SNAPSHOT_SEQ_XXXXX GUARANTEE FLASHBACK DATABASE;

3) CHECK OPEN_MODE AND DATABASE_ROLE OF DATABASE
Code:
SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY PHYSICAL STANDBY

4) CHECK CONTROLFILE_TYPE OF DATABASE
Code:
SQL> SELECT CONTROLFILE_TYPE FROM V$DATABASE;

CONTROL
-------
STANDBY

5) RESTART DATABASE
Code:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT

6) CONVERT DATABASE TO SNAPSHOT STANDBY
Code:
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;


Alert Log of Convert Database To SNAPSHOT STANDBY

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_07/20/2013 19:44:58
krsv_proc_kill: Killing 3 processes (all RFS)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after complete recovery through change 9905799926
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Resetting resetlogs activation ID 177460440 (0xa93d4d8)
Online log /Redolog/DBNAME/redo01_ds.log: Thread 1 Group 1 was previously cleared
Online log /Redolog/DBNAME/redo01_os.log: Thread 1 Group 1 was previously cleared
Online log /Redolog/DBNAME/redo02_ds.log: Thread 1 Group 2 was previously cleared
Online log /Redolog/DBNAME/redo02_os.log: Thread 1 Group 2 was previously cleared
Online log /Redolog/DBNAME/redo03_ds.log: Thread 1 Group 3 was previously cleared
Online log /Redolog/DBNAME/redo03_os.log: Thread 1 Group 3 was previously cleared
Online log /Redolog/DBNAME/redo04_ds.log: Thread 1 Group 4 was previously cleared
Online log /Redolog/DBNAME/redo04_os.log: Thread 1 Group 4 was previously cleared
Standby became primary SCN: 9905799924
Sat Jul 20 19:45:01 2013
Setting recovery target incarnation to 67
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: ALTER DATABASE CONVERT TO SNAPSHOT STANDBY

7) RESTART DATABASE
Code:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

8) CHECK OPEN_MODE AND DATABASE_ROLE OF DATABASE AFTER CONVERT
Code:
SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE SNAPSHOT STANDBY

9) CHECK CONTROLFILE_TYPE OF DATABASE
Code:
SQL> SELECT CONTROLFILE_TYPE FROM V$DATABASE;

CONTROL
-------
CURRENT

10) TEST WITH CREATE, UPDATE AND DELETE ON SNAPSHOT DATABASE

II- COVERT ORACLE DATABASE FROM PHYSICAL STANDBY TO SNAPSHOT STANDBY

1) RESTART DATABASE
Code:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT


2) CONVERT DATABASE TO PHYSICAL STANDBY
Code:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

Alert Log Convert Database To Physical STANDBY

ALTER DATABASE CONVERT TO PHYSICAL STANDBY
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (DBNAME)
krsv_proc_kill: Killing 3 processes (all RFS)
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Sat Jul 20 19:59:31 2013
Setting recovery target incarnation to 65
started logmerger process
Sat Jul 20 19:59:35 2013
Parallel Media Recovery started with 32 slaves
Flashback Media Recovery Log /Achive/1_41094_811729788.arc
Flashback Media Recovery Log /Achive/1_41095_811729788.arc
Flashback Media Recovery Log /Achive/1_41096_811729788.arc
Sat Jul 20 19:59:36 2013
Incomplete Recovery applied until change 9905799926 time 07/20/2013 19:41:17
Flashback Media Recovery Complete
Setting recovery target incarnation to 67
Drop guaranteed restore point 
Guaranteed restore point dropped
Clearing standby activation ID 186094306 (0xb1792e2)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 12 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 157286400;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 157286400;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 157286400;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 157286400;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 157286400;
Completed: ALTER DATABASE CONVERT TO PHYSICAL STANDBY

3) RESTART DATABASE
Code:
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

4) CHECK OPEN_MODE AND DATABASE_ROLE OF DATABASE AFTER CONVERT
Code:
SQL> SELECT OPEN_MODE, DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY PHYSICAL STANDBY

5) CHECK CONTROLFILE_TYPE OF DATABASE
Code:
SQL> SELECT CONTROLFILE_TYPE FROM V$DATABASE;

CONTROL
-------
STANDBY

6) START CONNECT DATAGUARD
Code:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;