Managing Automatic Workload Repository in Active Data Guard Standby and True Cache
Starting Oracle Database 23ai, Automatic Workload Repository (AWR) has built-in support for Active Data Guard Standby and True Cache databases. This feature enables the collection and analysis of performance statistics in these databases for CDB root and all the PDBs.
Automatic snapshot collection for ADG Standby and True Cache databases is enabled by default (Oracle Database 23ai) and is collected once per hour. The snapshot retention is 8 days by default. To change the retention time, use dbms_workload_repository.modify_snapshot_settings(retention)
.
Note:
If you have already setup AWR Remote Snapshots with RMF in a prior release, AWR will continue using RMF to create remote snapshots and the new Oracle Database 23ai functionality will remain disabled. Refer to the Managing Automatic Workload Repository in an RMF Topology instead.
The AWR data collected in either an ADG Standby or True Cache database is transported and stored in the AWR of the primary database. The AWR data will become available to the ADG Standby or True Cache databases after they apply the redo log from the from Primary database.
AWR automatically assigns an unique identifier, AWR ID, to each of the ADG standby and True Cache database. This AWR ID is used by AWR to identify each database and is stored in the DBID column in the AWR tables.The AWR ID for the current database can be obtained using the dbms_workload_repository.get_awr_id
procedure.
The following AWR operations can be performed on the ADG Standby or True Cache database:
- Obtain it's AWR ID
- Create Manual Snapshots
- Modify Snapshot Settings (e.g. retention, snapshot interval, etc)
- Generate AWR reports
For more information, refer to: Managing Snapshots and Generating Automatic Workload Repository Reports
ADG Switch-Over / Fail-Over
AWR has built-in support for ADG switch-over. In the event of switch-over, the AWR data collected in the ADG standby databases will be automatically sent to the new Primary database. After an ADG a switch-over, snapshots work correctly for both Primary and Standby.