Oracle DBA Place

Problem - Automatic Workload Repository (AWR) snapshots cannot be taken in a read-only standby environment. - Performance monitoring and analysis is limited to basic STATSPACK functionality . Solution In Oracle Database 12.2 , the AWR framework is enhanced to support capture of remote snapshots from any generic database, including Active Data Guard (ADG) databases. This framework is called the Remote Management Framework (RMF). • A target catalog database collects snapshots from the remote databases (sources). • Snapshots can be collected automatically or manually. • AWR tables on the catalog database accumulate snapshot data from all sources via database links. • Source databases must be registered on the catalog via new DBMS_WORKLOAD_REPOSITORY.REGISTER_REMOTE_DATABASE API These are the basic steps for setting up the RMF topology for generating AWR on the physical standby: 1. Configure database nodes to add to the topology. 2. Create the topology. 3. Register database...

As a DBA, I find myself trying to support customers remotely, who frequently ask for another Automatic Workload Repository (AWR) or a special query. I’ve found a cool feature that enables customer's DBAs to extract the AWR and then load it locally. Extracting 1. Login as SYS and at the SQL prompt, enter: SQL> @?/rdbms/admin/awrextr.sql 2. Enter your database id or press <Enter> to use the current default. Enter value for dbid: Using 3342354369 for Database ID 3. Specify the number of days for which you want to list snapshot Ids. Enter value for num_days: 14 4. After the list displays, enter the beginning and ending snapshot ID. Enter value for begin_snap: 2670 Enter value for end_snap: 2672 5. A list of directory objects is displayed. Specify the directory object pointing to the directory where the export dump file will be stored. Enter value for directory_name: DATA_PUMP_DIR 6. Specify the prefix for the default dump file name ...

How to display AWR snapshot settings Oracle database is gathering statistics periodically (snapshots), these statistics can be used for analyzing database performance. These statistics are kept in the Automatic Workload Repository (AWR).  Retention Interval: Amount of time to keep the snapshots. Snapshot Interval: How often to automatically take snapshots. SELECT 'Snapshot Interval' "Interval" ,      EXTRACT ( DAY FROM snap_interval ) days ,      EXTRACT ( HOUR FROM snap_interval ) hours ,      EXTRACT ( MINUTE FROM snap_interval ) minutes   FROM dba_hist_wr_control UNION ALL SELECT 'Retention Interval' "Interval" ,      EXTRACT ( DAY FROM retention ) days ,      EXTRACT ( HOUR FROM retention ) hours ,      EXTRACT ( MINUTE FROM retention ) minutes   FROM dba_hist_wr_contr...