Oracle AI Database 26ai — Managing AWR in Active Data Guard
Starting with Oracle Database 12.2, generating AWR reports on an Active Data Guard (ADG) standby became possible by using the Remote Management Framework (RMF). In this model, AWR data had to be manually transported from the standby to the primary database. Although functional, the setup was complex and required multiple configuration steps, making AWR analysis on physical standby databases operationally challenging for DBAs.
With Oracle Database 26ai, AWR support for Active Data Guard Standby databases is fully integrated and enabled by default. AWR snapshots are automatically collected on the ADG standby and transparently transported to the primary database, without any manual intervention or RMF configuration. By default, snapshots are taken once per hour, and the retention period is 8 days.
This enhancement allows DBAs to work with AWR on an Active Data Guard standby in the same way as on the primary database.
AWR Architecture for ADG in 26ai
Each Active Data Guard standby database is automatically assigned a unique AWR ID.
This AWR ID is stored in the DBID column of the AWR tables and is used to distinguish AWR data coming from different databases.
The AWR ID of the current database can be obtained using:
— ADG Standby:
SQL> select dbms_workload_repository.get_awr_id() awr_id;
AWR_ID
----------
3586104896Although AWR data is physically stored in the primary database, it becomes visible and usable on the standby after redo apply.
Generating an AWR Report on ADG Standby
An AWR report can be generated directly on the Active Data Guard standby using the standard awrrpt.sql script. During report generation, Oracle automatically lists all available AWR repositories and prompts for the appropriate DBID, including the standby AWR ID.
SQL> @?/rdbms/admin/awrrpt.sql
Specify the Report Type
~~~~~~~~~~~~~~~~~~~~~~~
AWR reports can be generated in the following formats. Please enter the
name of the format at the prompt. Default value is 'html'.
'html' HTML format (default)
'text' Text format
'active-html' Includes Performance Hub active report
Enter value for report_type:
Type Specified: html
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance Container Name
-------------- -------------- -------------- -------------- --------------
4152038052 DB26AI 1 DG1 CDB$ROOT
Instances in this Workload Repository schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
------------ ---------- --------- ---------- ------
3586104896 1 DB26AI DG1 DG1
4152038052 1 DB26AI db26ai OEL9-DB2
Using 3586104896 for database Id
Using 1 for instance number
Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the most recent
(n) days of snapshots being listed. Pressing <return> without
specifying a number lists all completed snapshots.
Enter value for num_days:
Listing all Completed Snapshots
Instance DB Name Snap Id Snap Started Snap Level
------------ ------------ ---------- ------------------ ----------
DG1 DB26AI 1 09 Feb 2026 10:30 1
2 09 Feb 2026 10:40 1A portion of the AWR report output is shown below.
Viewing AWR Settings for ADG Standby
Each ADG standby database can have its own AWR snapshot settings.
These settings are visible in the AWR_CDB_WR_CONTROL view on the primary database.
Example:
SQL> select c.dbid,c.snap_interval,c.retention,c.topnsql,c.tablespace_name from awr_cdb_wr_control c where src_dbname='DG1';
DBID SNAP_INTERVAL RETENTION TOPNSQL TABLESPACE_NAME
---------- -------------------- -------------------- ---------- ------------------------------
3586104896 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT SYSAUXThis confirms the default 1-hour snapshot interval and 8-day retention
Modifying Snapshot Settings on ADG Standby
Snapshot settings can be modified directly on the physical standby.
For example, to change the snapshot interval from 60 minutes to 30 minutes:
— ADG Standby:
SQL> exec dbms_workload_repository.modify_snapshot_settings(interval => 30);
PL/SQL procedure successfully completed.
SQL> select c.dbid,c.snap_interval,c.retention,c.topnsql,c.tablespace_name from awr_cdb_wr_control c where src_dbname='DG1';
DBID SNAP_INTERVAL RETENTION TOPNSQL TABLESPACE_NAME
---------- -------------------- -------------------- ---------- ------------------------------
3586104896 +00000 00:30:00.0 +00008 00:00:00.0 DEFAULT SYSAUXModifying AWR Retention from the Primary Database
AWR settings for an ADG standby can also be modified from the primary database by specifying the standby’s AWR ID.
For example, to change the retention period from 8 days to 60 days:
— Primary Database
SQL> exec dbms_workload_repository.modify_snapshot_settings(dbid => 3586104896,retention => 86400);
PL/SQL procedure successfully completedAfter redo apply, the updated settings are visible on the standby:
— ADG Standby
SQL> select c.dbid,c.snap_interval,c.retention,c.topnsql,c.tablespace_name from awr_cdb_wr_control c where src_dbname='DG1';
DBID SNAP_INTERVAL RETENTION TOPNSQL TABLESPACE_NAME
---------- -------------------- -------------------- ---------- ------------------------------
3586104896 +00000 00:30:00.0 +00060 00:00:00.0 DEFAULT SYSAUXCreating Manual AWR Snapshots on ADG Standby
In addition to automatic snapshots, manual AWR snapshots can be created on an Active Data Guard standby, just like on a primary database:
— ADG Standby
SQL> exec dbms_workload_repository.create_snapshot();
PL/SQL procedure successfully completed.
Written by Vahid Yousefzadeh
I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com
Comments
Post a Comment