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.

1
2
3
4
5
6
7
8
9
10
11
12
13 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

Shutdown database to enable flashback

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26 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.

admin

July 16, 2025