Monitor the state and workload of the Autonomous
Database on the Database Home page
|
To go to the Database Home page:
- Click the Targets menu >
Databases option.
- 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:
- 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:
- 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.
- Click Migrate to Oracle Autonomous
Database.
- 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.
- 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.
- 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
- Click the icon in the Comparison Report
column.
The SQL Performance Analyzer Task
Result page appears.
- 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:
|