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 Home page

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

The 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:

Create a job to execute SQLs on your Autonomous Databases

Oracle Enterprise Manager's Job System enables you to create, manage, and edit a job, which is a schedulable unit of work that you define to automate commonly run tasks. For Autonomous Databases, you can use the Execute SQL job type to automate and schedule the execution of SQL commands.

To create an Execute SQL job for an Autonomous Database:

  1. Go to the Home page of the Autonomous Database and from the <Autonomous Database> menu, select Job Activity.

    The Jobs page is displayed with the Autonomous Database Target Name and Target Type selected in the Search Criteria.

  2. Click Create Job.
  3. In the Select Job Type - Oracle Enterprise Manager dialog box, select the Execute SQL job type and click Select.

    The Create 'Execute SQL' Job page is displayed.

  4. Enter the following information in the General tab:
    1. Name: Enter a unique name for the job.
    2. Description: Optionally, add a description.
    3. Automatic Attempts: Optionally, specify the maximum number of times the job must be attempted, if it fails, and the number of minutes between each attempt.
    4. Target Type: Select your Autonomous Database target type.
    5. Target: Click Add, select the Autonomous Database target in the Search and Select: Targets dialog box, and click Select.
    6. Maximum Parallel Executions: Optionally, specify the maximum number of parallel executions.
  5. In the Parameters tab, enter a single SQL or PL/SQL statement without a trailing ; or /, and make changes to the other options, if required.
  6. In the Credentials tab, provide the credentials to log in to the Autonomous Database.
  7. In the Schedule tab, define the schedule of the Execute SQL job.
  8. In the Access tab, review the Administrators and roles that have access to the job and click Add to add other administrators, if required.
  9. Click Submit.

A confirmation message that the job is created successfully is displayed on the Jobs page.

For information on Oracle Enterprise Manager Jobs, see Job System Purpose and Overview in Oracle Enterprise Manager Cloud Control Administrator's Guide.

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 Home page of the Autonomous Database, 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 Home page of the Autonomous Database and from the Performance menu, select SQL, and then select 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:

Copy a SQL Tuning Set (STS) from an on-premises database to an Autonomous Database

Oracle Enterprise Manager enables you to copy an STS from an on-premises database discovered in Oracle Enterprise Manager to an Autonomous Database discovered in Oracle Enterprise Manager by way of the Oracle Cloud Infrastructure Object Storage.

For information on how to copy the STS, see the Copying a SQL Tuning Set from an On-premises Database to an Autonomous Database tutorial.

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 Home page of the Autonomous Database, you can select one of the following options:

  • Users: Create a user with a valid user name 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.
  • Cloud Credentials Store: Store database user credentials along with other attributes as a database object in the Autonomous Database. The database credentials can be used to access a remote database or the Oracle Cloud Infrastructure Object Storage.

    On the Cloud Credentials Store page, select a database schema in the Schema drop-down list to view the credentials in the schema.

    To create a credential:

    1. Click Create on the Cloud Credentials Store page.
    2. In the Create Credential dialog box:
      1. Credential Name: Enter an intuitive name for the credential. Note that the name of the credential cannot be edited at a later stage.
      2. Username: Enter the database user name or the OCID of the Oracle Cloud Infrastructure user.
      3. Password: Enter the database user password or the Object Storage authentication token.
      4. Confirm Password: Confirm the password added in the Password field.
      5. Click OK.

    You can click the icon in the Action column and click Edit or Delete to edit or delete the credential. Note that if editing the credential, you can only edit the user name and password and not the name of the credential. Also, when editing the credential, the Username and Password fields cannot be left blank.

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 Home page of the Autonomous Database, 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.
  • Materialized Views: Work with materialized views and perform tasks such as creating materialized views, materialized view logs, refresh groups, and dimensions.

Work with Directory Objects

You can view the default and custom-created directories in an Autonomous Database on the Directory Objects page in Oracle Enterprise Manager. In addition, you can also create directory objects and transfer files from the directory to the Oracle Cloud Infrastructure Object Storage and from the Object Storage to the directory.

For information on managing directories in Autonomous Databases, see:

To go to the Directory Objects page, from the Schema menu, select Database Objects, and then select Directory Objects. Note that you must have administrator privileges to access and perform tasks on the Directory Objects page.

To create a directory object:

  1. Click Create on the Directory Objects page.
  2. In the Create Directory Object dialog box:
    1. Directory Name: Enter a unique name for the directory or sub-directory.
    2. Relative Path: Specify a relative directory path. Note that you cannot provide the absolute path to the directory, and the relative path you specify will be appended to the absolute path.
    3. Click OK.

After you create a directory object, you can click the name of the directory in the Directory Name column and go to the corresponding Files page. On the Files page, you can view all the files in the directory and perform the following tasks:

  • Download files from the Object Storage to the database directory
    1. Click Copy from Object Storage.
    2. In the Copy from Object Storage dialog box:
      1. Database Credential for Object Storage: Select the database credentials used to connect to Object Storage. Note that the database credentials must be stored in the Cloud Credentials Store in Oracle Enterprise Manager. For information on Cloud Credentials Store, see Cloud Credentials Store.
      2. Object Storage URI: Specify the URI to the Object Storage bucket in which the file resides. The URI must be in the following format:
        https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/<object_name>
      3. Click OK.
  • Upload files from the database directory to the Object Storage
    1. Click the icon in the Action column and click Copy to Object Storage.
    2. In the Copy to Object Storage dialog box:
      1. Database Credential for Object Storage: Select the database credentials used to connect to Object Storage. Note that the database credentials must be stored in the Cloud Credentials Store in Oracle Enterprise Manager. For information on Cloud Credentials Store, see Cloud Credentials Store.
      2. Object Storage URI: Specify the URI to the Object Storage bucket in which the file resides. The URI must be in the following format:
        https://objectstorage.<region>.oraclecloud.com/n/<namespace>/b/<bucket>/o/<object_name>
      3. Click OK.

Create a database link from an Autonomous Database – Shared to a target database

A database link enables you to access objects and perform operations on a remote (target) database, and is stored as an object in the schema. You can create a database link to a target database only from Autonomous Databases – Shared, however, the target database can be an Autonomous Database, a Database Cloud Service instance, or an on-premises database in the schema. To use database links, the target database must be configured to use TCPS authentication, and the database link is created using a wallet (cwallet.sso). Autonomous Databases use TCPS authentication by default, and additional configuration is not required if your target database is an Autonomous Database. Other Oracle Databases must be configured to use TCPS authentication. For information, see Configuring Transport Layer Security Authentication in Oracle Database Security Guide.

Before you create a database link in Oracle Enterprise Manager, you must:

  • Store the credentials used to access the target database in the Cloud Credentials Store in Oracle Enterprise Manager. For information, see Cloud Credentials Store.
  • Add the target database wallet, cwallet.sso, to a directory object on the Directory Objects page in Oracle Enterprise Manager. For information, see Work with Directory Objects.

    If creating a database link to an on-premises database, you must ensure that the target database wallet (cwallet.sso) is first uploaded to the Oracle Cloud Infrastructure Object Storage and then downloaded to the Directory Objects page.

To go to the Database Links page, from the Schema menu, select Database Objects and then select Database Links. On the Database Links page, you can view previously created database links in a schema, if any, and create a database link.

To create a database link:

  1. Click Create on the Database Links page.
  2. In the Create Database Link dialog box, you must provide the following information to create a link to the target database:
    1. Name: Enter an intuitive name for the database link.
    2. Host Name: Enter the host name of the target database.
    3. Port Number: Enter the port number of the target database. The port number should be between 1521 and 1525.
    4. Service Name: Enter the service name of the target database.
    5. Distinguished Name (DN): Enter the DN value available in the server certificate.
    6. Credential Name: Select the credentials used to connect to the Object Storage.
    7. Directory Object: Select the directory object that contains the wallet of the target database.
    8. Click OK.

After you create a database link, click the icon in the Action column and click Test to test the connection to the database link. To delete a database link, click the icon in the Action column and click Delete.

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 Home page of an Autonomous Database – Dedicated target, 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: