3 Monitoring and Administration Tasks

After you have discovered Autonomous Databases, you can perform the following monitoring and administration tasks using Oracle Enterprise Manager for Autonomous Databases.

As you review the information available in the following table, note that:

  • For Autonomous Databases, Oracle Enterprise Manager provides a subset of the features that it provides for Oracle Databases. The "more information" links in the following table currently take you to generic information on Oracle Enterprise Manager support for Oracle Databases, and all the features may not be available for Autonomous Databases.
  • When you click the options in the user interface that take you to other Performance, Security, Schema, and Administration pages, the Database Login page is displayed and you must enter the Database Admin User credentials. These credentials can also be configured as named credentials. For information on named credentials, see Credentials Management in Oracle Enterprise Manager Cloud Control Security Guide.
Task Description

Monitor the state and workload of the Autonomous Database on the Database Home page

To go to the Database Home page:
  1. Click the Targets menu > Databases option.
  2. On the Databases page, click the name of the Autonomous Database.

The Database Home page enables you to proactively monitor:

  • Load and Capacity of the Autonomous Database.
  • Database Incidents that have occurred over the last 24 hours, if any.
  • Active session information in the Performance section, which includes:
    • The Activity Class chart that shows the average number of database sessions active for the past hour.
    • The Services chart that shows the average number of database sessions active for the past hour for database services.
  • Resource utilization on CPU, Active Sessions, Memory, and Data Storage charts in the Resources section.
  • SQL activity in the SQL Monitor section. The table in this section provides information on monitored SQL statement executions.

For information on:

Monitor performance and diagnose issues on the Performance Hub, SQL Monitoring, AWR, and Advisors pages

Using Oracle Enterprise Manager, you can monitor the performance of an Autonomous Database and ensure that it performs optimally.

From the Performance menu on the Database Home page, you can select one of the following options:

  • Performance Hub: View all the performance data available for a specified time period. Once a time period is selected, the performance information is collected and presented based on performance subject areas.
  • SQL: Perform SQL monitoring and tuning tasks. This includes options such as:
    • SQL Tuning Advisor to submit SQL statements and obtain recommendations on how to tune the statements, along with a rationale and expected benefit.
    • SQL Performance Analyzer to determine the effect of a change on a SQL workload by identifying performance divergence for each SQL statement.
    • SQL Tuning Sets to group SQL statements and related metadata into a single object, which you can use as input to SQL tuning tools.
  • AWR: Use Automatic Workload Repository (AWR) and automate database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning purposes. This includes options such as:
    • AWR Report to generate an AWR report between two snapshots (two points in time).
    • Compare Period Reports to compare database performance between two periods of time (or two AWR reports with a total of four snapshots).
  • Advisors Home: View and use SQL advisors to optimize the database's performance.
  • Automatic Indexing: Automate index management tasks for 19c-based Autonomous Databases.

    Automatic indexing automatically creates, rebuilds, and drops indexes in a database based on the changes in application workload, thereby improving database performance. This includes the following options:

    • Settings: On the Automatic Indexing Configuration Settings page, you can enable and disable automatic indexing, specify the retention period for unused indexes and automatic indexing logs, and specify the schemas to be included or excluded from using automatic indexing.
    • Activity Report: On the Automatic Indexing Activity Report page, you can enter the following details and click Generate Report to view the details of the auto indexes generated in the database:
      • Report Format: Select the format in which you want the report to be generated.
      • Sections: Select the sections that you want displayed in the report. The Summary, Index Details, Verification Details, and Errors options are selected by default in the Sections field, and you can opt to remove any of these sections.
      • Time Period: Select the monitoring time period for which you want the report to be generated.
      If you select the default options, namely the HTML report format and all the options in the Sections field for a specific time period, then the following sections are displayed in the Report Summary:
      • Overview of Executions: This section displays the overall performance improvement factor as a result of the auto indexes, the number of times the auto index operations were completed, the number of times the auto index operations were interrupted, and fatal errors, if any. Note that the Overview of Executions section is displayed irrespective of which other section is selected in the Sections field.
      • For the Summary section option, the following bar graphs are displayed:
        • Summary of Auto Indexes Actions
        • Summary of Auto Indexes SQL Actions
        • Summary of Manual Indexes
      • For the Errors section option, the Error Summary pie chart is displayed.
      • For the Index Details section option, the following sections are displayed:
        • Index: Created
        • Index: Dropped

        You can click a row in the Index: Created and Index: Dropped sections to view more details such as the ID of the index that was created or dropped, the Key, and Type. You can also use the Download All Index Created Details and Download All Index Dropped Details options given in these sections to download the index details in a .csv format.

      • For the Verification Details section option, the Verification Details section is displayed, which includes the details of the SQLs for which auto indexes were generated and used. You can click a row in this section to view more details such as SQL ID, SQL Text, and Improvement Factor.

      If you select the TEXT report format in the Report Format field, then the same information is displayed in a plain text format and can be downloaded by clicking theText Download option.

  • Blocking Sessions: Use to view the sessions that are blocking other sessions. The Blocking Sessions table provides information such as the Sessions Blocked, Session ID, and Serial Number.

    To view details about a specific session, click the Select option for that row and click View Session. To terminate a session, click the Select option, and then click Kill Session.

For information on:

Test migration from an on-premises database to an Autonomous Database using the SQL Performance Analyzer workflow

Using the SQL Performance Analyzer workflow in Oracle Enterprise Manager, you can test the effects of a migration from an on-premises database to an Autonomous Database based on SQL Tuning Set performance.

As prerequisite steps, you must:

  • Ensure that the source on-premises database and the target Autonomous Database are discovered in Oracle Enterprise Manager.
  • Capture the representative SQL workload from the source on-premises database and create a SQL Tuning Set. For information, see Creating a SQL Tuning Set in Oracle Database 2 Day + Performance Tuning Guide.
  • Move the SQL Tuning Set to the target Autonomous Database. For information, see Transporting SQL Tuning Sets in Oracle Database 2 Day + Performance Tuning Guide.

To test the migration from an on-premises database to an Autonomous Database:

  1. Go to the Autonomous Database home page and select the Performance menu > SQL > SQL Performance Analyzer Home.

    If the Database Login page appears, then log in as a user with administrator privileges. For information on user privileges, see About User Accounts.

  2. Click Migrate to Oracle Autonomous Database.
  3. Enter the required information in the fields on the Migrate to Oracle Autonomous Database page.
    • Task Information: Enter task information such as the name of the task, the name of the SQL Tuning Set, and optionally a description of the task.
    • Pre-Migration Trial: The pre-migration trial is built from the SQL Tuning Set by default, and Build from SQL Tuning Set is the only available pre-migration trial option.
    • Post-Migration Trial: Select an option in the Creation Method and Per-SQL Time Limit lists. For information on these lists and what you must enter, see steps 4 and 5 in Testing Database Upgrades Using Cloud Control in Oracle Database Testing Guide.
    • Trial Comparison: In the Comparison Metric list, select the comparison metric to use for the comparison analysis.
    • Schedule: Select your time zone code and select Immediately or Later to schedule when the task should start.
  4. Click Submit.

    The SQL Performance Analyzer Home page is displayed. In the SQL Performance Analyzer Tasks section, the details of the task are displayed. The Last Run Status displays Processing while the SQL statements are being processed. To refresh the status of the task, click Refresh. After the task completes, the Last Run Status column is updated to Completed.

  5. Under SQL Performance Analyzer Tasks, select the task and click the link in the Name column.

    The SQL Performance Analyzer Task page is displayed and it has the following sections:

    • SQL Tuning Set: This section summarizes information about the SQL tuning set, including its name, owner, description, and the number of SQL statements it contains.
    • SQL Trials: This section includes a table that lists the SQL trials used in the SQL Performance Analyzer task.

    • SQL Trial Comparisons: This section contains a table that lists the results of the SQL trial comparisons

  6. Click the icon in the Comparison Report column.

    The SQL Performance Analyzer Task Result page appears.

  7. Review the results of the performance analysis. For information, see Reviewing the SQL Performance Analyzer Report Using Oracle Enterprise Manager in Oracle Database Testing Guide.

For information on:

Migrate workloads from an on-premises database to an Autonomous Database using the Database Migration Workbench

For information on the supported migration methods, prerequisite tasks, migration steps, and so on, see Database Migration in Oracle Enterprise Manager Cloud Control Database Lifecycle Management Administrator's Guide.

Keep the Autonomous Databases secure

Oracle Enterprise Manager provides security features that control how a database is accessed and used.

From the Security menu on the Database Home page, you can select one of the following options:

  • Users: Create a user with a valid username and password to prevent unauthorized use. You can also associate specified privileges, roles, and so on with a user.
  • Roles: Create a role to group together privileges and other roles. This facilitates granting multiple privileges and roles to users.
  • Profiles: Create a profile, which is a set of user authorizations and privileges. If you add a user to a profile, then the authorizations and privileges defined in that profile are acquired by the user.
  • Audit Settings: Set up and adjust audit settings to monitor and record selected user database actions.
  • Privilege Analysis: Perform a dynamic analysis of privileges and roles that a user account or database uses over time. You can then revoke unused grants and make other changes to better reflect the access a user requires.
  • Virtual Private Database: Create security policies to enforce row-level security policies at the object (table, view, or synonym) level, when the standard object privileges and associated database roles are insufficient to meet application security requirements.

Perform Schema Management tasks

Oracle Enterprise Manager provides a comprehensive set of tools that allows you to manage all aspects of database objects such as tables, indexes, and views.

From the Schema menu on the Database Home page, you can select one of the following options to perform fundamental tasks such as creating, editing, and viewing schema objects:

  • Database Objects: Create and manage all aspects of database directory objects such as tables and indexes.
  • Programs: Manage the procedures, functions, triggers and so on associated with the database.

Perform Database Administration tasks such as Storage Management and Automated Maintenance

Oracle Enterprise Manager allows you to view and manage the storage structures of Autonomous Databases.

From the Administration menu on the Database Home page for Autonomous Database – Dedicated targets, you can select one of the following options. Note that for Autonomous Database – Shared targets, the Storage option is not available.

  • Storage: Manage your datafiles and tablespaces by clicking the corresponding option.

    Use Automatic Undo Management to view:

    • Name and size of undo tablespace
    • Auto-extend tablespace setting
    • Auto-tuned undo retention period
    • Minimum retention period

    Note that for Autonomous Databases, you cannot configure the Undo setting. This is a read-only view to understand the Undo configuration.

  • Oracle Scheduler: Use the Automated Maintenance Tasks option to enable the following maintenance tasks, which are performed automatically during maintenance windows:
    • Optimizer Statistics Gathering: Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics.
    • Automatic SQL Tuning: Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements.

For information on: