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:5 Convert the physical standby to snapshot standby using below command.

SQL> alter database convert to snapshot standby;

Database altered

Step:6 Mount the db and open it.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

Step:7 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:8 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:9 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:10 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:11 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

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8