1. Introduction
Oracle Data Guard is a high-availability and disaster recovery solution that maintains synchronized standby databases for a primary Oracle database. In a typical Data Guard environment, a Physical Standby database maintains a block-for-block replica of the primary database by continuously applying redo logs using Redo Apply.
While physical standby databases are ideal for disaster recovery, some enterprise environments require the standby database to support additional workloads such as reporting, data transformation, or selective data access. In such cases, converting a physical standby database to a Logical Standby database provides greater flexibility.
A Logical Standby database uses SQL Apply technology, which converts redo data into SQL statements and applies them to the standby database. This allows the standby system to remain synchronized with the primary database while also supporting read-write operations for specific database objects and reporting workloads.
This document demonstrates a complete step-by-step procedure for converting the STDB_FR physical standby database into a logical standby database, including prerequisite checks, LogMiner configuration, conversion steps, and post-conversion validation.
2. Architecture Overview
The architecture consists of a primary database and a standby database configured using Oracle Data Guard.
Primary Database
Handles production workloads and generates redo data.
Physical Standby Database (STDB_FR)
Receives redo logs from the primary database and applies them using Redo Apply.
Conversion Goal
Convert the STDB_FR physical standby database into a logical standby database so that it can apply changes using SQL Apply and support additional reporting workloads.
Key components involved in the architecture include:
-
Primary Database
-
Physical Standby Database
-
Oracle Data Guard Redo Transport
-
LogMiner Dictionary
-
SQL Apply Engine
-
Data Guard Broker
| Component | Details |
|---|---|
| Primary Database | Oracle Database (Primary Role) |
| Standby Database | STDB_FR (Physical Standby → Logical Standby) |
| Data Guard Configuration | Primary + Standby Architecture |
| Replication Method | Redo Apply (Physical Standby) / SQL Apply (Logical Standby) |
| Technology Used | Oracle Data Guard |
| Operating System | Linux Environment |
3. Overview of Logical Standby Database
A Logical Standby database in Oracle Data Guard uses SQL Apply technology to transform redo data received from the primary database into SQL statements, which are then executed on the standby database.
Unlike physical standby databases that maintain an exact block-level copy of the primary database, logical standby databases provide additional flexibility such as:
-
Supporting read-write operations on specific tables
-
Allowing reporting workloads on the standby system
-
Enabling selective data replication
-
Providing more flexible disaster recovery configurations
This makes logical standby databases useful in environments where standby systems are also required to support reporting or analytical workloads.
4 . Implementation
we are going to convert STDB_FR physical standby database to Logical Standby database.
- List unsupported objects
If any unsupported objects retrieved, investigate the reason why they are unsupported
2. Ensure Table Rows in the Primary Database Can Be Uniquely Identified
3. Stop Redo Apply on the physical standby database that will be converted into Logical Standby Database
We are going to convert STDB_FR into Logical Standby database. Stop log apply on STDB_FR and confirm it by issuing “show database” command as shown below. Log apply has been stopped.
4. On Primary Database create a LogMiner dictionary
5. Set the UNDO_RETENTION parameter to 3600 on primary and the standby database that will be converted
6. Convert Physical Standby database to Logical Standby Database
7. Shutdown the database, mount it and open with RESETLOGS
the stdb3 standby database is now in LOGICAL STANDBY role.
8. Start Log Apply
9. Check Broker
After changing the role of the stdb3 (STDB_FR) standby database, on broker we receive the error shown above. We need to remove the stdb3 database from the configuration and re-add it.
5. Conclusion
Converting a physical standby database to a logical standby database provides greater flexibility in a Data Guard environment. While physical standby databases focus primarily on disaster recovery through block-level replication, logical standby databases allow organizations to use standby systems for reporting, data access, and selective data replication.
This document demonstrated a complete step-by-step procedure for converting the STDB_FR physical standby database into a logical standby database, including prerequisite validation, unsupported object checks, LogMiner dictionary preparation, conversion steps, and post-conversion verification.
When implemented correctly, logical standby databases can significantly improve system utilization while still maintaining synchronization with the primary database.
6. Risks and Mitigation
Risk 1 – Unsupported Database Objects
Some database objects may not be supported by SQL Apply.
Mitigation
Identify unsupported objects before conversion and modify or exclude them if necessary.
Risk 2 – Data Synchronization Issues
Improper configuration can lead to replication delays or data inconsistency.
Mitigation
Ensure redo transport and SQL Apply processes are properly monitored.
Risk 3 – Data Guard Broker Configuration Errors
After conversion, broker configuration may become invalid.
Mitigation
Remove and re-add the standby database to the broker configuration.
Risk 4 – Performance Impact
SQL Apply can consume additional system resources.
Mitigation
Monitor SQL Apply performance and optimize database parameters.