Convert Physical Standby Database to Snapshot standby Database
The difference between a read-only standby and a snapshot standby is that the snapshot standby is fully update-able. It was possible in Oracle 10g to open a standby database as read-write but as from version 11g now you have the snapshot feature. This new feature makes it simpler to make the standby read-write and to revert to back again with the use of the Broker, also it is advised to use the flashback database feature as it makes life a whole lot simpler.
This snapshot standby database is fully update-able database and it is opened for read write operation. When the snapshot database is created from physical standby database, redo logs are still transferred to standby site but are not applied.
SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- SBYPROD MOUNTED PHYSICAL STANDBY SQL> alter system set db_flashback_retention_target=1440; System altered. SQL> Show parameter db_recovery_file_dest NAME TYPE VALUE -------------------------------- ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flash_recovery _area db_recovery_file_dest_size big integer 3882M SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO Make DATABASE FLASHBACK ON SQL> alter system set db_flashback_retention_target=1440; System altered. SQL> alter system set db_recovery_file_dest_size=5g; System altered. SQL> alter database recover managed standby database cancel; Database altered. SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database flashback on; Database altered. SQL> alter database recover managed standby database using current logfile disconnect; Database altered. SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY; Database altered. SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- SBYPROD MOUNTED SNAPSHOT STANDBY SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 651378688 bytes Fixed Size 2216184 bytes Variable Size 390074120 bytes Database Buffers 255852544 bytes Redo Buffers 3235840 bytes Database mounted. Database opened. SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- SBYPROD READ WRITE SNAPSHOT STANDBY
*****Revert back to Physical Standby*******
First shutdown database
open the database in mount mode
convert database in physical standby mode
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 651378688 bytes Fixed Size 2216184 bytes Variable Size 390074120 bytes Database Buffers 255852544 bytes Redo Buffers 3235840 bytes Database mounted. SQL> alter database convert to physical standby; Database altered. AGAIN SHUTDOWN DATABASE AND OPEN IN MOUNT MODE SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 651378688 bytes Fixed Size 2216184 bytes Variable Size 390074120 bytes Database Buffers 255852544 bytes Redo Buffers 3235840 bytes Database mounted. SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- SBYPROD MOUNTED PHYSICAL STANDBY Recover Physical Standby database ------------------------------------------------------------ ORA-01153: an incompatible media recovery is active ------------------------------------------------------------ SQL> recover managed standby database disconnect from session; Media recovery complete.
Check for database role
SQL> select DB_UNIQUE_NAME, OPEN_MODE, DATABASE_ROLE from v$database; DB_UNIQUE_NAME OPEN_MODE DATABASE_ROLE ------------------------------ -------------------- ---------------- SBYPROD MOUNTED PHYSICAL STANDBY SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/flash_recovery_area/sbyprod/ Oldest online log sequence 0 Next log sequence to archive 0 Current log sequence 42
CHECK MRP PROCESS
SQL> select PROCESS,STATUS from v$managed_standby; PROCESS STATUS --------- ------------ ARCH CONNECTED ARCH CONNECTED ARCH CONNECTED ARCH CLOSING ARCH CONNECTED ARCH CONNECTED ARCH CONNECTED ARCH CONNECTED RFS IDLE RFS IDLE MRP0 WAIT_FOR_LOG 33 rows selected.