By default, Oracle Database automatically generates snapshots once every hour. However, you may want to manually create snapshots to capture statistics at times different from those of the automatically generated snapshots.

Creating Snapshots Using the Command-Line Interface

To manually create snapshots, use the CREATE_SNAPSHOT procedure. The following example shows a CREATE_SNAPSHOT procedure call.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

In this example, a snapshot is created immediately on the local database instance. To view information about an existing snapshot, use the DBA_HIST_SNAPSHOT view.

By default, Oracle Database automatically purges snapshots that have been stored in AWR for over 8 days. However, you may want to manually drop a range of snapshots to free up space.

Dropping Snapshots Using the Command-Line Interface

To manually drop a range of snapshots, use the DROP_SNAPSHOT_RANGE procedure. The following example shows a DROP_SNAPSHOT_RANGE procedure call.

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id  => 22, 
                                                high_snap_id => 32, 
                                                dbid         => 3310949047);
END;
/

In the example, snapshots with snapshot IDs ranging from 22 to 32 are dropped immediately from the database instance with the database identifier of 3310949047. Any ASH data that were captured during this snapshot range are also purged.

Modifying Snapshot Settings Using the Command-Line Interface

You can modify snapshot settings using the following parameters of the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure:

The following example shows how to modify snapshot settings using the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention  => 43200, 
                                                     interval   => 30, 
                                                     topnsql    => 100, 
                                                     dbid       => 3310949047);
END;
/

In this example, snapshot settings for the database with the database identifier of 3310949047 are modified as follows:

• The retention period is specified as 43200 minutes (30 days).

• The interval between each snapshot is specified as 30 minutes.

• The number of top SQL to flush for each SQL criteria is specified as 100.

To get information about the current snapshot settings for your database, use the DBA_HIST_WR_CONTROL view as shown in the following example:

SQL> select snap_interval, retention from DBA_HIST_WR_CONTROL; 

SNAP_INTERVAL               RETENTION 
--------------------------------------- 
+00000 01:00:00.0        +00008 00:00:00.0 

The snap_interval and retention values are displayed in the format:

+[days] [hours]:[minutes]:[seconds].[nanoseconds] 

By default, Oracle Database automatically maintains a system-defined moving window baseline. However, you may want to manually create a fixed baseline that represents the system operating at an optimal level, so you can compare it with other baselines or snapshots captured during periods of poor performance.

To create baselines using command-line interface, use the CREATE_BASELINE procedure as shown in the following example:

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270, 
                                              end_snap_id   => 280, 
                                              baseline_name => 'peak baseline', 
                                              dbid          => 3310949047, 
                                              expiration    => 30);
END;
/

In this example, a baseline is created on the database instance with the database identifier of 3310949047 with the following settings:

• The start snapshot sequence number is 270.

• The end snapshot sequence number is 280.

• The name of baseline is peak baseline.

• The expiration of the baseline is 30 days.

Oracle Database automatically assigns a unique ID to the new baseline when the baseline is created.

To conserve disk space, consider periodically dropping a baseline that is no longer being used. The snapshots associated with a baseline are retained indefinitely until you explicitly drop the baseline or the baseline has expired.

To drop a baseline using command-line interface, use the DROP_BASELINE procedure as shown in the following example:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
                                          cascade       => FALSE, 
                                          dbid          => 3310949047);
END;
/

In the example, the baseline peak baseline is dropped from the database instance with the database identifier of 3310949047 and the associated snapshots are preserved.

To rename a baseline using command-line interface, use the RENAME_BASELINE procedure. The following example shows a RENAME_BASELINE procedure call.

BEGIN
    DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (old_baseline_name => 'peak baseline', 
                                              new_baseline_name => 'peak mondays', 
                                              dbid              => 3310949047);
END;
/

In this example, the name of the baseline on the database instance with the database identifier of 3310949047 is renamed from peak baseline to peak mondays.

When used with adaptive thresholds, a baseline contains AWR data that the database can use to compute metric threshold values.

To display the summary statistics for metric values in a baseline period using the command-line interface, use the SELECT_BASELINE_METRICS function:

DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRICS (baseline_name IN VARCHAR2,
                                                  dbid          IN NUMBER DEFAULT NULL,
                                                  instance_num  IN NUMBER DEFAULT NULL)
   RETURN awr_baseline_metric_type_table PIPELINED;

By default, Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days. In certain circumstances, you may want to modify the window size of the default moving window baseline, such as increasing its size to more accurately compute threshold values for adaptive thresholds.

To modify the window size of the default moving window baseline using the command-line interface, use the MODIFY_BASELINE_WINDOW_SIZE procedure as shown in the following example:

BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (window_size => 30, 
                                                          dbid        => 3310949047);
END;
/

In this example, the default moving window is resized to 30 days on the database instance with the database identifier of 3310949047.

You can use a single baseline template to create a baseline during a single, fixed time interval in the future. For example, you can create a single baseline template to generate a baseline that is captured on April 2, 2012 from 5:00 p.m. to 8:00 p.m.

To create a single baseline template using command-line interface, use the CREATE_BASELINE_TEMPLATE procedure as shown in the following example:

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (start_time    => '2012-04-02 17:00:00 PST', 
                                                       end_time      => '2012-04-02 20:00:00 PST', 
                                                       baseline_name => 'baseline_120402', 
                                                       template_name => 'template_120402', 
                                                       expiration    => 30, 
                                                       dbid          => 3310949047);
END;
/

In this example, a baseline template named template_120402 is created that will generate a baseline named baseline_120402 for the time period from 5:00 p.m. to 8:00 p.m. on April 2, 2012 on the database with a database ID of 3310949047. The baseline will expire after 30 days.

You can use a repeating baseline template to automatically create baselines that repeat during a particular time interval over a specific period in the future. For example, you can create a repeating baseline template to generate a baseline that repeats every Monday from 5:00 p.m. to 8:00 p.m. for the year 2012.

To create a repeating baseline template using command-line, use the CREATE_BASELINE_TEMPLATE procedure as shown in the following example:

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (day_of_week => 'monday', 
                                                       hour_in_day => 17,
                                                       duration => 3, expiration => 30,
                                                       start_time => '2012-04-02 17:00:00 PST', 
                                                       end_time => '2012-12-31 20:00:00 PST', 
                                                       baseline_name_prefix => 'baseline_2012_mondays_', 
                                                       template_name => 'template_2012_mondays',
                                                       dbid => 3310949047);
END;
/

In this example, a baseline template named template_2012_mondays is created that will generate a baseline on every Monday from 5:00 p.m. to 8:00 p.m. beginning on April 2, 2012 at 5:00 p.m. and ending on December 31, 2012 at 8:00 p.m. on the database with a database ID of 3310949047. Each of the baselines will be created with a baseline name with the prefix baseline_2012_mondays_ and will expire after 30 days.

Periodically, you may want to remove baselines templates that are no longer used to conserve disk space.

To drop a baseline template using command-line, use the DROP_BASELINE_TEMPLATE procedure as shown in the following example:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (template_name => 'template_2012_mondays',
                                                   dbid          => 3310949047);
END;
/

In this example, the baseline template named template_2012_mondays is dropped from the database instance with the database identifier of 3310949047.

The awrextr.sql script exports AWR data for a range of snapshots from the database into a Data Pump export file. After it is created, you can transport this dump file to another database where you can import the exported AWR data. To run the awrextr.sql script, you must be connected to the database as the SYS user.

To export AWR data:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrextr.sql
   

   A list of the databases in the AWR schema is displayed.

2. Specify the database from which AWR data needs to be exported:

   Enter value for db_id: 1377863381
   

   In this example, the database with the database identifier of 1377863381 is specified.

3. Specify the number of days for which you want to view all the snapshot IDs:

   Enter value for num_days: 2
   

   In this example, all the snapshots captured in the last 2 days are displayed.

4. Define the range of snapshots for which AWR data needs to be exported by specifying the beginning and the ending snapshot IDs:

   Enter value for begin_snap: 30
   Enter value for end_snap: 40
   

   In this example, the snapshot ID of 30 is specified as the beginning snapshot, and the snapshot ID of 40 is specified as the ending snapshot.

   A list of directory objects is displayed.

5. Specify the directory object pointing to the directory where the export dump file needs to be stored:

   Enter value for directory_name: DATA_PUMP_DIR
   

   In this example, the directory object DATA_PUMP_DIR is specified that points to the directory ORACLE_HOME/rdbms/log, where ORACLE_HOME is /u01/app/oracle/product/database_release_number/dbhome_1.

6. Specify a name for the export dump file without the file extension. By default, the file extension of .dmp is used.

   Enter value for file_name: awrdata_30_40
   

   In this example, an export dump file named awrdata_30_40.dmp is created in the directory specified in the directory object DATA_PUMP_DIR:

   Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
   /u01/app/oracle/product/database_release_number/dbhome_1/rdbms/log/awrdata_30_40.dmp
   Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:58:20
   

   Depending on the amount of AWR data that must be exported, the AWR export operation may take a while to complete. After the dump file is created, you can use Data Pump to transport the file to another system.

After the export dump file is transported to the target system, import the exported AWR data using the awrload.sql script. The awrload.sql script creates a staging schema where the snapshot data is transferred from the Data Pump file into the database. The data is then transferred from the staging schema into the appropriate AWR tables. To run the awrload.sql script, you must be connected to the database as the SYS user.

To import AWR data:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrload.sql
   

   A list of directory objects is displayed.

2. Specify the directory object pointing to the directory where the export dump file is located:

   Enter value for directory_name: DATA_PUMP_DIR
   

   In this example, the directory object DATA_PUMP_DIR is specified that points to the directory where the export dump file is located.

3. Specify the name of the export dump file without the file extension. By default, the file extension of .dmp is used.

   Enter value for file_name: awrdata_30_40
   

   In this example, the export dump file named awrdata_30_40.dmp is selected.

4. Specify the name of the staging schema where the AWR data needs to be imported:

   Enter value for schema_name: AWR_STAGE
   

   In this example, a staging schema named AWR_STAGE is created.

5. Specify the default tablespace for the staging schema:

   Enter value for default_tablespace: SYSAUX
   

   In this example, the SYSAUX tablespace is specified.

6. Specify the temporary tablespace for the staging schema:

   Enter value for temporary_tablespace: TEMP
   

   In this example, the TEMP tablespace is specified.

7. First the AWR data is imported into the AWR_STAGE schema and then it is transferred to the AWR tables in the SYS schema:

   Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   Completed 113 CONSTRAINT objects in 11 seconds
   Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
   Completed 1 REF_CONSTRAINT objects in 1 seconds
   Job "SYS"."SYS_IMPORT_FULL_03" successfully completed at 09:29:30
   ... Dropping AWR_STAGE user
   End of AWR Load
   

   Depending on the amount of AWR data that must be imported, the AWR import operation may take a while to complete. After AWR data is imported, the staging schema will be dropped automatically.

Managing Snapshots

Starting with Oracle Database 12c Release 2 (12.2), you can take an AWR snapshot at a CDB-level, that is, on a CDB root, as well as at a PDB-level, that is, on an individual PDB. By default, the CDB-level snapshot data is stored in the SYSAUX tablespace of a CDB root and the PDB-level snapshot data is stored in the SYSAUX tablespace of a PDB.

A CDB-level snapshot contains information about the CDB statistics as well as all the PDB statistics, such as ASH, SQL statistics, and file statistics. The CDB administrator can perform CDB-specific management operations, such as setting AWR data retention period, setting snapshot schedule, taking manual snapshots, and purging snapshot data for a CDB root.

A PDB-level snapshot contains the PDB statistics and also some global statistics that can be useful for diagnosing the performance problems related to the PDB. The PDB administrator can perform PDB-specific management operations, such as setting AWR data retention period, setting snapshot schedule, taking manual snapshots, and purging snapshot data for a PDB.

The CDB-level and PDB-level snapshot operations, such as creating snapshots and purging snapshots, can be performed in either the automatic mode or the manual mode.

The automatic snapshot operations are scheduled, so that they get executed automatically at a particular time. The AWR_PDB_AUTOFLUSH_ENABLED initialization parameter enables you to specify whether to enable or disable automatic snapshots for all the PDBs in a CDB or for individual PDBs in a CDB. The automatic snapshot operations are enabled by default for a CDB, but are disabled by default for a PDB. To enable automatic snapshots for a PDB, the PDB administrator must connect to that PDB, set the value for the AWR_PDB_AUTOFLUSH_ENABLED parameter to true, and set the snapshot generation interval to a value greater than 0.

The manual snapshot operations are explicitly initiated by users. The automatic snapshots and manual snapshots capture the same AWR information. Oracle recommends to generally use manual snapshots for a PDB. You should enable automatic snapshots only selectively for a PDB for performance reasons.

The primary interface for managing snapshots is Oracle Enterprise Manager Cloud Control (Cloud Control). If Cloud Control is not available, then you can use the procedures in the DBMS_WORKLOAD_REPOSITORY package to manage snapshots. The Oracle DBA role is required to use the procedures in the DBMS_WORKLOAD_REPOSITORY package. The SQL procedures to create, drop, and modify snapshots for a CDB root and a PDB are the same as that for a non-CDB. These SQL procedures perform their operations on the local database by default, if the target database information is not provided in their procedure call.

Exporting and Importing AWR Data

The process of exporting and importing AWR data for a CDB root and a PDB in a multitenant environment is similar to the process of exporting and importing AWR data for a non-CDB.

AWR Reports

The primary interface for generating AWR reports is Oracle Enterprise Manager Cloud Control (Cloud Control). Whenever possible, generate AWR reports using Cloud Control.

If Cloud Control is unavailable, then you can generate the AWR reports by running SQL scripts as described below. The DBA role is required to run these scripts.

• You can generate a CDB-specific AWR report from a CDB root that shows the global system data statistics for the whole multitenant environment. You can generate this AWR report using the SQL scripts described in the section "Generating an AWR Report for the Local Database".

• You can generate a PDB-specific AWR report from a PDB that shows the statistics related to that PDB. You can generate this AWR report using the SQL scripts described in the section "Generating an AWR Report for the Local Database".

• You can generate a PDB-specific AWR report from a CDB root that shows the statistics related to a specific PDB. You can generate this AWR report using the SQL scripts described in the section "Generating an AWR Report for a Specific Database".

AWR Views

The following table lists the Oracle Database views for accessing the AWR data stored on the CDB root and the individual PDBs in a multitenant environment.

Setting Up the RMF Topology

You need to set up the RMF topology for collecting performance statistics for an Oracle database.

The following are the prerequisites for setting up the RMF topology:

• You must create destination to source and source to destination database links for all the database nodes to be registered in the RMF topology. This setup should be done during the ADG deployment.

The following are the steps for setting up the RMF topology:

1. Configure database nodes to add to the topology.

2. Create the topology.

3. Register database nodes with the topology.

4. (Optional) Register database links between the nodes in the topology. This configuration is required when a destination becomes unavailable and a candidate destination needs to connect to the remaining nodes in the topology using database links.

Example for Setting Up the RMF Topology

In this example, the three database nodes T, S0, and S1 are added to the topology Topology_1. Node T is the destination node and nodes S0 and S1 are the source nodes. Node S1 is a candidate destination, that is, when the original destination T is not available, node S1 becomes the new destination. The AWR service is enabled for all the sources in the topology.

Assume that the following database links are already created during the ADG deployment:

• DBLINK_T_to_S0: Database link from T to S0.

• DBLINK_T_to_S1: Database link from T to S1.

• DBLINK_S0_to_T: Database link from S0 to T.

• DBLINK_S0_to_S1: Database link from S0 to S1.

• DBLINK_S1_to_T: Database link from S1 to T.

• DBLINK_S1_to_S0: Database link from S1 to S0.

The following is a sample code for setting up the RMF topology:

/* Configure the nodes T, S0, and S1 by executing these procedures */

/* Execute this procedure on node T */
SQL> exec DBMS_UMF.configure_node ('T');

/* Execute this procedure on node S0 */
SQL> exec DBMS_UMF.configure_node ('S0', 'DBLINK_S0_to_T');

/* Execute this procedure on node S1 */
SQL> exec DBMS_UMF.configure_node ('S1', 'DBLINK_S1_to_T');

/* Execute all the following procedures on the destination node T */

/* Create the topology 'Topology_1' */
SQL> exec DBMS_UMF.create_topology ('Topology_1');

/* Register the node S0 with the topology 'Topology_1' */
SQL> exec DBMS_UMF.register_node ('Topology_1',
                                  'S0',
                                  'DBLINK_T_to_S0',
                                  'DBLINK_S0_to_T',
                                  'TRUE'  /* Set it as a source */,
                                  'FALSE' /* Set it as not a candidate destination */);

/* Register the node S1 with the topology 'Topology_1' */
SQL> exec DBMS_UMF.register_node ('Topology_1',
                                  'S1',
                                  'DBLINK_T_to_S1',
                                  'DBLINK_S1_to_T', 
                                  'TRUE'  /* Set it as a source */,
                                  'TRUE'  /* Set it as a candidate destination */);

/* Register the database links between the nodes S0 and S1 in the topology 'Topology_1'. 
 * When destination T is unavailable at the time of failover, the source S0 can connect
 * to the candidate destination S1 using this database link.
 */
SQL> exec DBMS_UMF.create_link ('Topology_1',
                                'S0',
                                'S1',
                                'DBLINK_S0_to_S1',
                                'DBLINK_S1_to_S0');

/* Enable the AWR service on the node S0 in the topology 'Topology_1' */
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'S0');

/* Enable the AWR service on the node S1 in the topology 'Topology_1' */
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'S1');

SQL> exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database(node_name)

Managing ADG Role Transition

An ADG role transition occurs when the ADG Primary or original destination fails (failover event) or when an ADG standby database or candidate destination takes over the role of the ADG Primary during the maintenance phase (switchover event).

Oracle recommends that you perform the following configuration steps before making the role change, that is, before making the candidate destination as the new destination due to the failover or switchover event:

1. Create database links between the sources and the candidate destination. This configuration must be done for all the sources by executing the following procedure on each source:

   SQL> EXEC DBMS_UMF.CREATE_LINK (topology name, 
                                   source name, 
                                   candidate destination name,
                                   source to candidate destination database link,
                                   candidate destination to source database link);

   Note:

   Oracle recommends that you create database links among all the nodes in a topology to avoid any unanticipated issues that may arise at the time of role change.

2. Take an AWR snapshot on the candidate destination.

   Note:

   To generate an AWR report for the candidate destination after the role change, take at least one snapshot for the candidate destination before the role change.

3. Restart the candidate destination as well as all the sources.

After completing the preceding configuration steps, you can make the role change by executing the following procedure on the candidate destination:

SQL> EXEC DBMS_UMF.SWITCH_DESTINATION(topology name, force_switch=>FALSE);

Getting the Details of Registered RMF Topologies

The RMF views described below show the configuration information about all the registered RMF topologies in a multi-database environment.

Creating, Modifying, and Deleting Remote Snapshots

The APIs for creating, modifying, and deleting remote snapshots are same as that for the local snapshots.

Managing Baselines for Remote Snapshots

The APIs for managing baselines for remote snapshots are same as that for the local snapshots.

Exporting and Importing Remote Snapshots

The process of exporting and importing AWR data for remote snapshots is same as that for the local snapshots. Starting with Oracle Database 12c Release 2 (12.2), the AWR data export and import scripts awrextr.sql and awrload.sql use the source name identifier to distinguish snapshots originating from a particular source. A source name is stored in a dump file during an export operation and is used as a default source name during an import operation.

Exporting Remote Snapshots Using the awrextr.sql Script

The process of exporting remote snapshots is similar to exporting local snapshots using the awrextr.sql script described in the section "Exporting AWR Data" with the following differences:

• The default export log file directory is the same as that of the dump file, but you can also specify any other directory for an export log file.

• The .dmp suffix can be specified to the name of the dump file to export.

• The export script displays the values of SOURCE_DBID and SOURCE_NAME columns of AWR tables before prompting for the Mapped Database ID value to export.

Importing Remote Snapshots Using the awrload.sql Script

The process of importing remote snapshots is similar to importing local snapshots using the awrload.sql script described in the section "Importing AWR Data" with the following differences:

• The default import log file directory is the same as that of the dump file, but you can also specify any other directory for an import log file. This is particularly useful when the dump file resides in a read-only directory.

• The .dmp suffix can be specified to the name of the dump file to import.

• The import script uses the values of SOURCE_DBID and SOURCE_NAME columns present in the dump file to determine the appropriate Mapped Database ID to use for storing the snapshot data in AWR.

Viewing AWR Data Using AWR Views

You can view the historical data stored in AWR using the DBA_HIST views described in the section "Using Automatic Workload Repository Views".

Viewing AWR Data Using AWR Reports

You can view the performance statistics related to ADG standby databases using AWR reports. The primary interface for generating AWR reports is Oracle Enterprise Manager Cloud Control (Cloud Control). Whenever possible, generate AWR reports using Cloud Control. If Cloud Control is unavailable, then generate AWR reports using the Oracle supplied SQL scripts. The DBA role is required to run these scripts.

The AWR data can be queried for a particular source using the source name-mapped database ID pair. The mapped database ID is similar to the database identifier (DBID) that is used by AWR to identify a database instance and is stored in the DBID column in the AWR tables. The AWR DBID value is derived as follows for the ADG standby databases:

• For a destination, the AWR DBID value is the value of V$DATABASE.CON_DBID.

• For a source, the AWR DBID value is the value of DBMS_UMF.GET_NODE_ID_LOCAL() or the value of the column NODE_ID in the DBA_UMF_REGISTRATION view.

As snapshot IDs are not unique across sources, the pair of snapshot ID-mapped database ID identifies a snapshot for a particular source.

The awrrpt.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs.

To generate an AWR report on the local database instance using the command-line interface:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrrpt.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: text
   

   In this example, a text report is chosen.

3. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 2
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

4. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 150
   Enter value for end_snap: 160
   

   In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

5. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_1_150_160
   

   In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated.

The awrrpti.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs using a specific database instance. This script enables you to specify a database identifier and instance for which the AWR report will be generated.

To generate an AWR report on a specific database instance using the command-line interface:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrrpti.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: text
   

   In this example, a text report is chosen.

   A list of available database identifiers and instance numbers are displayed:

   Instances in this Workload Repository schema
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      DB Id    Inst Num DB Name      Instance     Host
   ----------- -------- ------------ ------------ ------------
    3309173529        1 MAIN         main         examp1690
    3309173529        1 TINT251      tint251      samp251
   

3. Enter the values for the database identifier (dbid) and instance number (inst_num):

   Enter value for dbid: 3309173529
   Using 3309173529 for database Id
   Enter value for inst_num: 1
   

   Note:

   For an ADG standby database, the value for dbid can be determined as follows:

   • For a Destination node, use the value of v$database.con_dbid .

   • For a Source node, use the value of dbms_umf.get_node_id_local().

4. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 2
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

5. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 150
   Enter value for end_snap: 160
   

   In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

6. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_1_150_160
   

   In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated on the database instance with a database ID value of 3309173529.

The awrgrpt.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs using the current database instance in an Oracle Real Application Clusters (Oracle RAC) environment.

To generate an AWR report for Oracle RAC on the local database instance using the command-line interface:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrgrpt.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

3. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 2
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last day are displayed.

4. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 150
   Enter value for end_snap: 160
   

   In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

5. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_rac_150_160.html
   

   In this example, the default name is accepted and an AWR report named awrrpt_rac_150_160.html is generated.

The awrgrpti.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs using specific databases instances running in an Oracle RAC environment. This script enables you to specify database identifiers and a comma-delimited list of database instances for which the AWR report will be generated.

To generate an AWR report for Oracle RAC on a specific database instance using the command-line interface:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrgrpti.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

   A list of available database identifiers and instance numbers are displayed:

   Instances in this Workload Repository schema
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      DB Id    Inst Num DB Name      Instance     Host
   ----------- -------- ------------ ------------ ------------
    3309173529        1 MAIN         main         examp1690
    3309173529        1 TINT251      tint251      samp251
    3309173529        2 TINT251      tint252      samp252
   

3. Enter the value for the database identifier (dbid):

   Enter value for dbid: 3309173529
   Using 3309173529 for database Id
   

4. Enter the value for the instance numbers (instance_numbers_or_all) of the Oracle RAC instances you want to include in the report:

   Enter value for instance_numbers_or_all: 1,2
   

5. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 2
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

6. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 150
   Enter value for end_snap: 160
   

   In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

7. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_rac_150_160.html
   

   In this example, the default name is accepted and an AWR report named awrrpt_rac_150_160.html is generated on the database instance with a database ID value of 3309173529.

The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement from a range of snapshot IDs. Run this report to inspect or debug the performance of a SQL statement.

To generate an AWR report for a SQL statement on the local database instance using the command-line interface:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

3. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 1
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.

4. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 146
   Enter value for end_snap: 147
   

   In this example, the snapshot with a snapshot ID of 146 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 147 is selected as the ending snapshot.

5. Specify the SQL ID of a particular SQL statement to display statistics:

   Enter value for sql_id: 2b064ybzkwf1y
   

   In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.

6. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_1_146_147.html
   

   In this example, the default name is accepted and an AWR report named awrrpt_1_146_147 is generated.

The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement from a range of snapshot IDs using a specific database instance.This script enables you to specify a database identifier and instance for which the AWR report will be generated. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

To generate an AWR report for a SQL statement on a specific database instance using the command-line interface:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

   A list of available database identifiers and instance numbers are displayed:

   Instances in this Workload Repository schema
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      DB Id    Inst Num DB Name      Instance     Host
   ----------- -------- ------------ ------------ ------------
    3309173529        1 MAIN         main         examp1690
    3309173529        1 TINT251      tint251      samp251
   

3. Enter the values for the database identifier (dbid) and instance number (inst_num):

   Enter value for dbid: 3309173529
   Using 3309173529 for database Id
   Enter value for inst_num: 1
   Using 1 for instance number
   

4. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 1
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.

5. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 146
   Enter value for end_snap: 147
   

   In this example, the snapshot with a snapshot ID of 146 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 147 is selected as the ending snapshot.

6. Specify the SQL ID of a particular SQL statement to display statistics:

   Enter value for sql_id: 2b064ybzkwf1y
   

   In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.

7. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_1_146_147.html
   

   In this example, the default name is accepted and an AWR report named awrrpt_1_146_147 is generated on the database instance with a database ID value of 3309173529.