What is snapshot standby database?
It allows to perform read-write operation on the standby database.ie converting the physical standby to snapshot standby database. On that, we can do all types of testing or it can be used as a development database. After our testing process is over we can convert our snapshot standby database in to physical standby database and changes done to the snapshot standby will be reverted.
A snapshot standby database receives and archives redo data but it does not apply the redo data from the primary database.
NOTE: FRA (Fast Recovery Area) must be configured in physical standby database but it is not necessary to have flashback enabled.
Overview steps:
Step:1 verify open_mode, database_role of the standby db it should be in read only with apply, physical standby
Step:2 cancel the recovery process(MRP)
Step:3 Enable the flashback_mode in standby, it can be enabled after specifying the location of the db_recovery_file_dest (db recovery area)
Step:4 Then we can convert our physical standby to snapshot standby database.
Step:1 check the open_mode, database_role of the database.
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY
Step:2 we must stop the redo apply process using the following command.
SQL> alter database recover managed standby database cancel; Database altered. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 243268216 bytes Fixed Size 8895096 bytes Variable Size 180355072 bytes Database Buffers 50331648 bytes Redo Buffers 3686400 bytes Database mounted.
Step:3 Enable the flashback to convert it into snapshot standby database.
SQL> alter database flashback on; Database altered. SQL> show parameter db_recovery_file_dest NAME TYPE VALUE -------------------------------- ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area/ db_recovery_file_dest_size big integer 8256M
SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY SQL> select status from v$instance; STATUS ------------ MOUNTED
Step:4 Convert the physical standby to snapshot standby using below command.
SQL> alter database convert to snapshot standby; Database altered
Step:5 Mount the db and open it.
SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database open; Database altered.
Step:6 select the name of the restore point, gurantee_flashback_database information’s from v$restore_point view, where gurantee_flashback_database parameter tells that whether flashback log files will be kept to ensure a flashback to this point.
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point; NAME GUA ---------------------------------------------------- ---------------- SNAPSHOT_STANDBY_REQUIRED_02/18/2021 01:27:55 YES
Step:7 check the role of the db whether it has converted into snapshot standby.
SQL> select database_role from v$database; DATABASE_ROLE ---------------- SNAPSHOT STANDBY
Step:8 Now I am performing some DML operations, creating a table student and adding 100000 rows in it.
SQL> create table student(id number(5)); Table created. SQL> begin for i in 1 .. 100000 loop insert into student values(1); end loop; end; PL/SQL procedure successfully completed. SQL> select count(*) from student; COUNT(*) ---------- 100000 SQL> commit; Commit complete. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount ORACLE instance started. Total System Global Area 243268216 bytes Fixed Size 8895096 bytes Variable Size 180355072 bytes Database Buffers 50331648 bytes Redo Buffers 3686400 bytes Database mounted.
Step:9 Converting snapshot standby db to physical standby.
SQL> alter database convert to physical standby; Database altered.
SQL> select database_role,open_mode from v$database; DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> shut immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 243268216 bytes Fixed Size 8895096 bytes Variable Size 180355072 bytes Database Buffers 50331648 bytes Redo Buffers 3686400 bytes Database mounted. Database opened. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY
Step:10 Hence we converted our snapshot standby to physical standby, the DML operations that we have done in snapshot standby cannot be retrieved here.
SQL> select * from student; select * from student * ERROR at line 1: ORA-00942: table or view does not exist
