Convert Physical Standby To Snapshot Standby Database ( Read / Write Mode ) in Oracle

I will explain how to Convert Physical Standby To Snapshot Standby Database in Oracle in this post.

Convert Physical Standby To Snapshot Standby Database in Oracle

I have explained you to open in synchronized dataguard in Read only mode as Active standby in the Following article. In this article, I will tell you how to convert Dataguard to write Read Write mode and then to convert it into dataguard mode again.

Flashback must be turned on in order to be able to active standby mode. To enable Flashback, we need to use the FRA parameter like following.

SQL> show parameter db_recovery_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/recovery_area
db_recovery_file_dest_size big integer 2022043K

Flashback must be enabled in the Standby database as follows. If Flashback is off, you can enable it in case the dataguard is off and in mount mode.

 SQL> alter database flashback on;

Database altered.

Check Standby database mode.

 SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

1 row selected.

Stop Redo Apply process

SQL> alter database recover managed standby database cancel;

Manually create an archive log in the primary database like following.

 SQL> alter system archive log current;
System altered.

We need to delay the redo transport from the Primary Database to the Standby database. So disable related log_archive_dest_state_2 parameter like following.

SQL> alter system set log_archive_dest_state_2=DEFER; 
SQL> alter system switch logfile;

Convert Dataguard to Read Write mode in Oracle

You can convert the Standby database to snapshot standby as follows.

SQL> alter database convert to snapshot standby;

Database altered.

Now Standby database has been converted in Read Write mode.

You can open standby database in read and write mode as follows.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 163580104 bytes
Database Buffers 360710144 bytes
Redo Buffers 7942144 bytes
Database mounted.



alter database open;

Database altered.

Check database role as follows.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
SNAPSHOT STANDBY

We opened the standby database in Read write mode namely Snapshot database mode.

This operation is done such as test database or Disaster recovery test purpose.

You can perform DDL & DML operation in this database as follows.

SQL> create user mehmet identified by salih;

User created.

SQL> grant dba to mehmet;

Grant succeeded.

Convert Snapshot Standby to Physical Standby

If Disaster tests or any other tests are completed, we can convert it into dataguard again like following steps.

Open it in Mount mode

SQL> startup mount force;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 163580104 bytes
Database Buffers 360710144 bytes
Redo Buffers 7942144 bytes
Database mounted.

Convert Snapshot database to Dataguard with restore point

select * from v$restore_point;

set pages 100
set line 200
column message format a50
select sid,message from v$session_longops where 
sofar <> totalwork;

set linesize 400
col DB_DATA for 999999999999
col FLASHBACK_SIZE for 999999999999
SELECT TO_CHAR(A.BEGIN_TIME,'DDMMYY HH24:MI') "FLASH BEGIN",TO_CHAR(A.END_TIME,'DDMMYY HH24:MI') "FLASH END",A.DB_DATA, B.FLASHBACK_SIZE, (A.DB_DATA/B.FLASHBACK_SIZE)*100 "% COMPLETE" FROM V$FLASHBACK_DATABASE_STAT A, V$FLASHBACK_DATABASE_LOG B;

Convert Snapshot database to the standby database ( Dataguard mode ).

SQL> alter database convert to physical standby;

Database altered.

The Standby database is reopened in Mount mode.

SQL> startup mount force;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2230072 bytes
Variable Size 163580104 bytes
Database Buffers 360710144 bytes
Redo Buffers 7942144 bytes
Database mounted.

Check database role as follows.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

1 row selected.

Redo Apply or MRP Process is started again like following.

Thus Dataguard will be enabled.

SQL> alter database recover managed standby database using current logfile disconnect;

Convert Physical Database to Snapshot database using DGMGRL

DGMGRL> CONVERT DATABASE STANDBY_DB_UNIQUE_NAME TO SNAPSHOT STANDBY;

If you want to learn how to install Oracle Dataguard, you should read the following post.

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/