Oracle DBA

In this article, we will see how to convert an existing Physical Standby Database into an Active Data Guard configuration in Oracle 19c.

The read-only physical standby database can be used to offload reporting and query workloads from the primary database. Users can execute SELECT statements and complex analytical queries on the standby, thereby reducing load on the primary system and improving overall performance.

However, while the standby database is opened in READ ONLY mode, the following operations are disallowed:

• Any Data Manipulation Language (DML) except for SELECT statements
• Any Data Definition Language (DDL)
• Access to local sequences
• DMLs on local temporary tables

Once you have configured the Physical Standby Database as described in Oracle 19c Physical Standby Database Configuration, follow the steps below to enable Active Data Guard.

---

Step 1: Check the Status of the Primary Database and Latest Sequence

Connect to the Primary Database and verify its role and the latest archived log sequence generated.

SQL> select status, instance_name, database_role from v$instance, v$database;

STATUS       INSTANCE_NAME  DATABASE_ROLE
------------ -------------- ----------------
OPEN         prod           PRIMARY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
40

---

Step 2: Check the Status of the Physical Standby Database and Sequence Applied

On the Standby Database, verify the role and the latest sequence number applied.

SQL> select status, instance_name, database_role from v$database, v$instance;

STATUS     INSTANCE_NAME  DATABASE_ROLE
---------- -------------- ---------------------
MOUNTED    stnd           PHYSICAL STANDBY

SQL> select max(sequence#) from v$archived_log where applied='YES';

MAX(SEQUENCE#)
--------------
40

---

Step 3: Verify the Managed Recovery Process (MRP) on the Standby Database

Check if the MRP process is running and waiting for the next log sequence.

SQL> select process, status, sequence# from v$managed_standby;

PROCESS   STATUS         SEQUENCE#
--------- --------------- ----------
ARCH      CONNECTED      0
ARCH      CONNECTED      0
ARCH      CONNECTED      0
ARCH      CONNECTED      0
RFS       IDLE           141
RFS       IDLE           0
RFS       IDLE           0
RFS       IDLE           0
MRP0      WAIT_FOR_LOG   141
9 rows selected.

Here, the MRP process is active and is waiting for log sequence 141.

---

Step 4: Cancel MRP and Open the Standby Database in Read-Only Mode

Before enabling Active Data Guard, you must cancel the MRP process and open the standby database in read-only mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status, instance_name, database_role, open_mode from v$database, v$instance;

STATUS  INSTANCE_NAME  DATABASE_ROLE       OPEN_MODE
------- -------------- ------------------- ---------------
OPEN    stnd           PHYSICAL STANDBY    READ ONLY

At this stage, the standby database is open in READ ONLY mode.

---

Step 5: Start the Managed Recovery Process (MRP) Again

Now start the MRP in the background while the database remains open for read operations.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process, status, sequence# from v$managed_standby;

PROCESS   STATUS         SEQUENCE#
--------- --------------- ----------
ARCH      CONNECTED      0
ARCH      CONNECTED      0
ARCH      CONNECTED      0
ARCH      CONNECTED      0
RFS       IDLE           141
RFS       IDLE           0
RFS       IDLE           0
RFS       IDLE           0
MRP0      WAIT_FOR_LOG   141
9 rows selected.

The MRP process is active and waiting for log sequence 141, while the standby database remains open in READ ONLY mode.

---

Conclusion

Your Physical Standby Database is now successfully converted to an Active Data Guard configuration.

• The Managed Recovery Process (MRP) continues applying redo in the background.
• The database remains open for reporting and read-only queries.
• This setup allows the standby to serve analytical workloads, reducing load on the primary database and improving overall system efficiency.