- 11 Jun, 2019
- 0 Comments
- 2 Mins Read
How to convert Snapshot Standby Database to Physical Standby Database-Oracle Dataguard
On Standby Server
In this blog, We will see how to convert physical standby database to snapshot standby database.
On that, we can do all types of testing or can be used as a development database (which is an exact replication of production ). Once the testing is over we can again convert the snapshot database to physical standby. Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.
SQL> select database_role from v$database; DATABASE_ROLE ---------------- PHYSICAL STANDBY 1 row selected. SQL> select open_mode from v$database; OPEN_MODE -------------------- READ ONLY WITH APPLY
Cancel the Recovery Process
SQL> alter database recover managed standby database cancel; Database altered. 1 row selected.
Shutdown database to enable flashback
SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> startup mount ORACLE instance started. Total System Global Area 335540560 bytes Fixed Size 9134416 bytes Variable Size 272629760 bytes Database Buffers 50331648 bytes Redo Buffers 3444736 bytes Database mounted. SQL> SQL>
Check if recovery area is already set with required size
SQL> show parameter recover;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /data/app/oracle/fast_recovery
_area
db_recovery_file_dest_size big integer 4800M
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
If recovery area is not set then , We can configure like this
SQL> alter system set db_recovery_file_dest_size=4g;
System altered.
SQL> alter system set db_recovery_file_dest='/data/testdb/recover';
System altered.
SYS@TESTER1 SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /data/testdb/recover
db_recovery_file_dest_size big integer 4G
SQL> alter database flashback on;
Database altered.
Now the below command will convert it to snapshot standby
SQL> alter database convert to snapshot standby; Database altered. SQL> SQL> SQL> alter database open; Database altered. SQL> SQL> SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- PRIM READ WRITE SQL> SQL> SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE from v$restore_point; NAME -------------------------------------------------------------------------------- GUA --- SNAPSHOT_STANDBY_REQUIRED_08/24/2021 09:53:14 YES
Let convert it again to physical standby again.
Shutdown database to enable flashback SQL> shu immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 335540560 bytes Fixed Size 9134416 bytes Variable Size 272629760 bytes Database Buffers 50331648 bytes Redo Buffers 3444736 bytes Database mounted. SQL> SQL> SQL> alter database convert to physical standby; Database altered. SQL> alter database open; Database altered.