Blog

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.

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.