How to set up Oracle Active Data Guard for read-only reporting
To configure Oracle Active Data Guard, you must first ensure the enterprise version environment, version compatibility, and network connectivity; enable forced logging in the main database and configure archiving parameters; create a physical standby database through RMAN replication from the active database; start the Redo application in the standby database, and then open and enable real-time applications in read-only mode; create and authorize reporting users, route queries to the standby database through Oracle Net, and monitor application latency and synchronization status to achieve read-write separation and performance optimization.
Setting up Oracle Active Data Guard for read-only reporting allows you to offload reporting workloads from your primary database to a physical standby, improving performance and availability. Here's how to configure it step by step.
Prerequisites and Environment Setup
Before enabling Active Data Guard, ensure the following:
- You're running Oracle Enterprise Edition (Active Data Guard is not available in Standard Edition).
- The primary and standby databases are on compatible versions and platforms.
- Network connectivity is established between the primary and standby servers.
- Oracle Net (listener) is configured on both systems.
- A valid backup of the primary database exists for duplicating the standby.
Enable Force Logging and Configure Redo Transport
On the primary database, enable force logging to ensure all changes are captured:
ALTER DATABASE FORCE LOGGING;Set primary database initialization parameters to support redo transport:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_db,standby_db)';ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary_db';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby_db';
ALTER SYSTEM SET FAL_SERVER=standby_db;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Create and Configure the Physical Standby
Duplicate the primary database to create a physical standby using RMAN:
RMAN TARGET sys@primary_db AUXILIARY sys@standby_dbDUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;
After duplication, start Redo Apply on the standby:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;Open Standby Database in Read-Only Mode with Active Data Guard
To allow read-only access while applying redo data, open the standby in read-only mode and start Real-Time Apply:
ALTER DATABASE OPEN READ ONLY;ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
This enables queries on the standby while continuously applying redo data from the primary—ideal for real-time reporting.
Configure Reporting Access and Monitoring
Create dedicated reporting users and grant limited access:
CREATE USER reporter IDENTIFIED BY password;GRANT CONNECT, CREATE SESSION, SELECT ANY TABLE TO reporter;
Use Oracle Net services to direct reporting applications to the standby. Monitor apply lag and performance:
SELECT SEQUENCE#, APPLIED, TIMESTAMP FROM V$ARCHIVED_LOG ORDER BY SEQUENCE# DESC;SELECT VALUE FROM V$DATAGUARD_STATS WHERE NAME = 'apply lag';
Basically just ensure redo transport is working, the standby is synchronized, and Real-Time Apply is active. Once that's in place, your reporting queries run without affecting the primary workload.
The above is the detailed content of How to set up Oracle Active Data Guard for read-only reporting. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undresser.AI Undress
AI-powered app for creating realistic nude photos
ArtGPT
AI image generator for creative art from text prompts.
Stock Market GPT
AI powered investment research for smarter decisions
Hot Article
Popular tool
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
20529
7
13639
4
How to use Oracle APEX to build a low-code app? (Rapid Development)
Mar 13, 2026 am 12:48 AM
OracleAPEXislow-glue,notno-code:itskipsinfrastructurebutrequiresSQL,PL/SQL,anddeclarativelogic;ApplicationProcesseshandleserver-sidevalidationandsideeffects,DynamicActionsmanageclient-sideinteractivity;InteractiveGridneedskey-preservedsourcesforediti
How to manage Flashback Data Archive_Flashback Data Archive table space allocation
Mar 28, 2026 pm 04:06 PM
The reason why the FlashbackDataArchive table space is full is that the hidden history table (SYS_FBA_HIST_XXXXXX) occupies the table space where the main table is located and does not go through ASSM cleaning; you need to use ALTERFLASHBACKARCHIVE...MODIFYTABLESPACE to migrate to the local management automatic segment space table space, and manually clean up the orphan history table.
How to implement Transparent Data Encryption (TDE) in Oracle? (Data Security)
Mar 13, 2026 am 12:14 AM
OracleTDE must first enable and open the encrypted wallet (Wallet), otherwise ORA-28365 will be reported when executing ALTERTABLESPACE...ENCRYPTION; Wallet needs to be created, opened and managed through the ADMINISTERKEYMANAGEMENT command, and the path must be explicitly configured in sqlnet.ora and permissions must be ensured.
How to grant flashback permission_GRANT FLASHBACK ON and FLASHBACK ANY TABLE
Apr 03, 2026 pm 11:54 PM
FLASHBACK permissions must be explicitly granted: GRANTFLASHBACKONschema.tableTOuser for a single table, and GRANTFLASHBACKANYTABLETOuser for all tables; basic permissions such as SELECT and ALTER and row movement enablement are also required.
How to grant SYSDBA permissions_sysdba management of password files and OS authentication
Apr 03, 2026 am 08:54 AM
Ordinary users can be authorized through GRANTSYSDBATOusername; provided that the database enables password file authentication (REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE) and has logged in with SYS; there is no need to restart after authorization, but the connection needs to explicitly specify assysdba, and the user credentials must exist in the V$PWFILE_USERS view.
How to call function _PRAGMA AUTONOMOUS_TRANSACTION with DML unbinding
Mar 17, 2026 pm 11:27 PM
The ORA-06519 error is caused by the autonomous transaction not being explicitly committed or rolled back: When calling a function containing PRAGMAAUTONOMOUS_TRANSACTION, if DML (such as INSERT/UPDATE) is executed, the transaction must be ended with COMMIT or ROLLBACK before each exit, otherwise an error will be reported; the autonomous transaction is completely isolated from the main transaction, and the data after COMMIT is not visible to the main transaction and cannot be rolled back by it.
How to shield exceptions from being thrown_null EXCEPTION WHEN OTHERS THEN NULL
Apr 03, 2026 am 08:45 AM
No, it does not really shield exceptions. It only swallows error information without handling inconsistencies such as transaction status and cursors, which can easily lead to data confusion and hidden logic failures.
How to block the public execution of system packages_REVOKE EXECUTE ON DBMS_SQL FROM PUBLIC
Apr 04, 2026 am 06:09 AM
The default granting of DBMS_SQL execution permission to PUBLIC is an Oracle compatibility design and is not a vulnerability; revoking it will cause abnormal system functions. Specific user permissions should be restricted instead of PUBLIC, and risks should be managed through auditing, access control, code review and other measures.
