Managing Automatic Workload Repository in an RMF Topology

Note:

Starting Oracle Database 23ai, AWR has built-in support for Active Data Guard databases. This is enabled by default and replaces the AWR remote snapshots using RMF Topology that was used in earlier releases. Although you can still use RMF Topology, Oracle recommends using the new AWR built-in functionality.

AWR supports collecting performance statistics from databases that are participating in an RMF topology (Remote Management Framework). AWR snapshots for RMF nodes are called remote snapshots. A database node, called destination, is responsible for storing snapshots that are collected from remote ADG standby database nodes, called sources.

The remote snapshots are taken automatically at scheduled time intervals, but can also be taken manually. The remote snapshots are always started by the destination node. After the destination initiates the snapshot creation process, sources push their snapshot data to the destination using database links.

Before remote snapshots can be created, the source node must be registered in AWR using the dbms_workload_repository.register_remote_database procedure. After a source node is registered for remote snapshots, the source node will only be able to perform a limited set of operations. Most AWR operations (create snapshot, modify settings, remove snapshots, etc) may only be performed from the destination node.

A destination can be either an ADG primary database or a non-ADG database. If a destination is an ADG primary database, then it is also a source database, and its snapshots are local snapshots.

The snapshot data, or AWR data, stored on the destination can be accessed:

  • Using AWR reports

  • Oracle Database import and export functions

  • User-defined queries.

  • The Automatic Database Diagnostic Monitor (ADDM) application can use the AWR data for analyzing any database performance-related issues.

Destination Database Responsibilities

A destination database manages the following tasks:

  • Registering sources

  • Assigning unique identifier for each source

  • Creating database links between destination and sources

  • Scheduling and initiating automatic snapshots for sources

  • Managing destination workload by coordinating snapshots among sources

  • Managing snapshot settings for each source

  • Assigning identifiers to newly generated snapshots

  • Storing the performance data in the local AWR

  • Purging the AWR data of destination and sources

Source Database Responsibilities

A source database manages the following tasks:

  • Sending its AWR data to the destination

  • Responding to service requests from the destination

  • Extracting the AWR data from the destination

Manage RMF Sources in AWR

Before you start configuring AWR for RMF environment, make sure that the database links for all the RMF topology has been created and the required source nodes have already been registered in the RMF topology. Refer to Configuring the Remote Management Framework (RMF)

  • Register Remote Databases

    Registration of an existing source in the RMF topology must be performed by the Destination database. Make sure the source to be registered is open before attempting the operation.

    The following is a sample code for registering RMF source 'S1' into AWR:

    exec dbms_workload_repository.register_remote_database(node_name=>'S1');

    In the case RMF topology is removed or the source database is removed from the RMF topology, the remote databases remain registered in AWR and the existing AWR data for this database is retained. However, no further snapshots are allowed to be created.

  • Unregister the RMF Source

    Removing a remote database from AWR must be performed by the destination database. This procedure deletes all existing AWR data for that database from the destination.

    The following is a sample code for unregistering source 'S1' from AWR:

    exec dbms_workload_repository.unregister_remote_database(node_name=>'S1');

    Unregistration may be performed even if the RMF topology has been removed or the source database has been removed from the RMF Topology.