4 Database Management

This chapter introduces the concept of database management and contains the following sections.

Introduction to Database Management
Monitoring Databases
Administering Databases
Database Maintenance
Database Group Management

Introduction to Database Management

Database management involves the monitoring, administration, and maintenance of the databases and database groups in your enterprise. Enterprise Manager is the premier tool for managing your database environment.

With Enterprise Manager, you get:

A complete set of integrated features for managing Oracle databases
Unparalleled scalability that lets you manage a single database or thousands of instances
An intuitive management product that leads the industry in ease of deployment and use

Database Control Versus Grid Control

Enterprise Manager provides two configurations with which to monitor your database: Database Control and Grid Control. Database Control is the Enterprise Manager Web-based application for managing Oracle Database 10g Release 1 (10.1). The Database Control is installed and available with every Oracle Database 10g installation.

From the Database Control, you can monitor and administer a single Oracle Database instance or a clustered database.

The Grid Control is the Enterprise Manager console used for centrally managing your entire Oracle environment. Within Grid Control, you access the database targets using the Targets tab and clicking Databases.

See Also:

"Providing a Flexible Architecture" for additional information about Database Control and Grid Control

Database Home Page as the Management Hub

The Enterprise Manager Database Home page (Figure 4-1) allows you to view important performance and status information about your database instance from a single source, including:

Instance name, database version, Oracle home location, media recovery options, and other pertinent instance data
Current instance availability
Host CPU metrics and aggregate session activity data
High availability statistics
Outstanding alerts
Session and SQL-related performance information
Key space usage metrics

Starting from the Database Home page, you can access additional details and management functions by drilling down through the user interface.

In addition, the Database Home page provides a list of Related Links. From these links you can perform such activities as add and edit metric thresholds, analyze job activity and metric collection errors, and access a number of advisors to help you improve the performance of your database.

See Also:

"Oracle Database Home Page" in the Enterprise Manager online help

Figure 4-1 Enterprise Manager Database Home Page

Description of the illustration db_home_top.gif

Monitoring Databases

Comprehensive database monitoring allows you to identify the problem areas in your database environment that are affecting performance. Once you have identified the areas to improve, you can tune your databases' performance using the Enterprise Manager administration capabilities.

Note:

For more detailed information about the features on any page, click Help to access Enterprise Manager online help.

Database Performance Page

On the Database Performance page (Figure 4-2), accessible from the Database Home page, you can quickly view performance data for the instance and its underlying host. Several charts display current and recent metric information on a common time axis that enables visual correlation of metrics:

Host charts show paging and run queue information to assess overall memory and CPU issues.
Sessions: Waiting and Working chart quantifies session database work, as well as where waiting is negatively impacting performance.
Throughput charts allow correlation of user workload with performance indicators in the other charts.

Each of these charts has context-sensitive drill-down to additional details for rapid problem diagnosis.

Additional links to powerful real-time diagnostic capabilities allow you to identify top resource consumers (by session, SQL, or service/module/action) and locking issues.

The Sessions: Waiting and Working chart is the centerpiece of Oracle performance monitoring. It displays a profile of the amount of time sessions are either working or waiting to work in the database instance. Session time is categorized as either CPU (working) or one of the wait classes. The Maximum CPU line on the chart helps determine how much of the CPU resource is being utilized. Session wait time accumulates above the CPU line and the ratio of wait time to CPU time indicates how efficiently the system is working. Specific wait classes accumulating time help indicate where tuning efforts should be focused.

On Oracle 10g databases, the Sessions: Waiting and Working chart also shows a small icon for every Automatic Database Diagnostic Monitoring (ADDM) task within the time period, which can be clicked to get ADDM recommendations.

Figure 4-2 Top of the Database Performance Page

Description of db_performance_top.gif follows

Description of the illustration db_performance_top.gif

Database tuning becomes a lot easier and more effective when all the information for an in-depth performance diagnosis is made available on a single screen. Additionally, all critical performance metrics can be graphed for trend analysis as well as compared to those of other database instances.

Top Consumers

In the Additional Monitoring Links section of the Database Performance page, click the Top Consumers link for an overview of the top consumers of system resources (Figure 4-3). The top consumers include sessions, services, modules, and clients.

Use the Top Consumers page to help you pinpoint the most problematic areas on which to focus your database tuning efforts. From this page, click the property page associated with the resource you want to investigate in more detail, for example, Top Sessions.

Figure 4-3 Top Consumers Overview Page

Description of db_top_consumers.gif follows

Description of the illustration db_top_consumers.gif

Figure 4-4 shows the Top Sessions property page associated with Top Consumers. This page lists the most resource-consuming sessions for the database.

Figure 4-4 Top Sessions

Description of db_top_sessions.gif follows

Description of the illustration db_top_sessions.gif

SQL Monitoring

Enterprise Manager analyzes all SQL statements for performance and resource consumption. Suspect SQL statements are evaluated, and areas for possible tuning are identified in a plain-language SQL Assessment. You can view the SQL statement together with its execution statistics and execution plan.

To view the Top SQL statements, click the Top SQL link available in the Additional Monitoring Links section of the Database Performance page. There, you can also choose to view Blocking Sessions, Database Locks, and Instance Activity.

Administering Databases

As the Enterprise Manager comprehensive monitoring identifies problem areas in your database and database groups, you can administer your database using the Enterprise Manager administration tools.

Enterprise Manager allows you to manage the following:

Initialization parameters
Schema objects such as tables, indexes, and views
Storage entities such as tablespaces, datafiles, control files, rollback segments, redo log groups, and archive logs
Users, roles, and profiles
Resource consumer groups and resource plans
Source types such as packages, procedures, functions, and Java classes

Table 4-1 describes the administration categories in more detail. For additional information about any of the administration tools, click Help to access Enterprise Manager online help.

Table 4-1 Database Administration Categories

Administration Category	Description
Configuration Management	Helps you to compare the configuration of multiple databases. This allows you to duplicate the same configuration of a good performing database to databases that are not performing as well. From this category, you can also view the statistics of the last collected configuration, for example, tablespace information.
Enterprise Manager Administration	Supports tasks specific to the administration of Enterprise Manager. For example, you can define administrator accounts to perform management tasks. You can also define blackouts to suspend monitoring of one or more targets. Additionally, you can define notification schedules and monitor job activity.
High Availability	Provides access to Data Guard and the Add Standby Database wizard. By using Data Guard in conjunction with standby databases, you can protect your data from unforeseen circumstances, such as power outages and hardware malfunctions. Data Guard automates the creation, management, and monitoring of database standby environments.
Instance	Helps you manage the database instances and sessions in your Oracle environment. Using these tools, you can administer memory, recovery, and initialization parameters, start up and shut down a database, manage users' sessions, view currently running SQL, track space usage, and view instance alerts.
Resource Manager	Allows you to allocate resources among user groups. This makes it possible to give certain groups first access to system resources. For example, you can allocate to users running resource-intensive quarterly reports the resources that remain after all other groups have submitted their tasks. This ensures that the reports do not run at the expense of other functions that may be more time critical. Enterprise Manager provides resource administration tools that allow administrators to manage and optimize resource usage. Using these tools, you can monitor and analyze the status of the currently active resource plan; change the active resource plan; view user/group resource requirements; allocate or reallocate resources according to an established hierarchy.
Scheduler	Simplifies the scheduling and management of all jobs. The Scheduler ensures that jobs are processed at a predefined time in an efficient manner while maximizing the use of system resources. It lets you manage and monitor the jobs. Detailed information on all Scheduler activities is available, thus providing key information on resource usage that you can use to plan for future capacity requirements.
Schema	Provides several tools that allow you to manage and administer schemas. Using these tools, you can create indexes to improve the speed with which SQL statements execute against a table, create views to better reflect the needs of particular users, and create, alter, clone, or drop various database schema objects, including indexes, tables, views, synonyms, sequences, and database links. Schema administration also supports packages, procedures, functions, triggers, as well as Java sources and classes. You can also launch the Labor Statistics wizard for generating cost-based optimizer statistics.
Security	Provides easy, intuitive tools for managing security, including creating, cloning, and removing user accounts and permissions; creating and modifying roles; and setting profile parameters. Security administration also supports Oracle password management, which increases system security. Supported features include: account locking, password lifetime and expiration, password history, password complexity, verifications, and export/import of passwords.
Storage	Provides database storage administration tools that make it easier for administrators to optimize database performance. Using these tools, you can administer operations on control files, tablespaces, datafiles, rollback segments, redo log groups, and archive logs.
Warehouse	Helps you to configure an Oracle database to support business intelligence applications that perform complex multidimensional calculations, forecasting, and what-if analysis. Using Enterprise Manager, you can create Online Analytical Processing (OLAP) metadata for a star/snowflake schema. To fully configure your database for OLAP applications, you will also need to use OLAP tools such as Analytic Workspace Manager and packages such as DBMS_ODM.
Workload	Helps you to use the Automatic Workload Repository to store database statistics that are used for performance tuning. Use the SQL Tuning Set page to define a collection of SQL Statements that can be used for tuning purposes.

Database Maintenance

In addition to monitoring and administering your databases, Enterprise Manager allows you to perform maintenance operations on your databases to keep them up-to-date with the latest patches, upgrades, and data. You can access the following utilities and software management tools through the Maintenance tab on the Database Home page:

Table 4-2 Utility Options

Utility	Description
Clone Database	Duplicates the current database by first backing up and then transferring the current database to a destination Oracle home. Use this utility to copy a database that is in a known and acceptable state. For example, the database has been configured, tuned, and tested.
Export to Files	Moves existing data in Oracle format to and from Oracle databases. For example, you can back up logical database objects while the database is open and available for use. Allows users to export the contents of databases, objects, and tables.
Gather Statistics	Generates and modifies optimizer statistics. Up-to-date statistics can greatly improve the performance of SQL queries against your objects.
Import from File(s)	Imports data that was exported using the Export to File tool. You can use this feature to import the contents of databases, tables, and objects.
Import from Database	Imports the contents of a database.
Load Data from File	Loads data from non-Oracle databases into an Oracle database.
Make Tablespace Locally Managed	Converts dictionary managed tablespaces to locally managed tablespaces.
Reorganize Objects	Rebuilds fragmented indexes or tables, moves objects to a different tablespace, or optimizes the storage attributes of specified objects.

Table 4-3 Backup and Recovery Options

Option	Description
Configure Backup Settings	Protects your database against data loss and to reconstruct the data should loss occur. A backup is a copy of data that can be used to reconstruct parts of your database in the case of data loss. This backup can include important parts of the database such as the control file and datafiles.
Configure Recovery Catalog Settings	Configures the current database with a recovery catalog. A recovery catalog is a supplemental logical schema, created in a database other than the one that you are backing up, that contains RMAN metadata. The recovery catalog is stored in the default tablespace of the schema.
Configure Recovery Settings	Establishes the parameters for crash recovery of instances and media. Recovery refers to the various strategies and procedures involved in protecting your database against data loss and reconstructing the data should that loss occur.
Manage Current Backups	Searches for and displays a list of backup sets or backup copies and performs management operations on selected copies, sets, or files.
Perform Recovery	Restores or recovers a database, tablespaces, datafiles, archived logs, or flashbacks tables or undrops objects.
Schedule Backup	Backs up the contents of your database to disk, tape or both. Online backups are available only when the database is in ARCHIVELOG mode. A backup copies important parts of the database such as the control file and datafiles. A backup is a safeguard against unexpected data loss and application errors. If you lose the original data, then you can reconstruct it by using a backup.

Table 4-4 Deployment Options

Option	Description
Patch	Keeps your databases up-to-date by searching for recent patches at OracleMetaLink, which you can then download and apply.
Search Configuration	Performs different types of searches that involve one or more targets in your enterprise configuration. Most of the search queries are pre-defined, but you can modify the search criteria to customize the different search queries.

Database Group Management

Today's enterprises usually consist of a great number of databases. Managing each database individually can be very time consuming for administrators. By combining databases that have certain common characteristics into a single group, administrators can easily and efficiently monitor the performance and availability of multiple databases at once.

With the Enterprise Manager group management capabilities, you can easily manage multiple databases by assigning them to groups. Oracle also provides a set of configuration recommendations against which your databases are automatically compared.

This section contains the following topics:

Managing Multiple Databases
Best Practice Configuration Recommendations

Managing Multiple Databases

The use of database groups makes monitoring and administration easier for administrators who manage multiple databases. For example, an administrator can place all production databases into a single group, allowing for quick and easy monitoring of those databases from a single screen. The Database Group page displays information about the databases that have the highest wait times (indicating potential bottlenecks), those with the most severe alert status, or those that are down.

The Database Group page (Figure 4-5) also provides direct links to the home pages of the databases that make up the group, allowing the administrator quick access to key availability and performance data about individual databases as well. The Enterprise Manager group management capability alleviates the need for cumbersome navigation between multiple instances and prevents performance degradation trends from going unnoticed.

Figure 4-5 Database Group Home Page

Description of db_groups_home.gif follows

Description of the illustration db_groups_home.gif

You can view a database's violations of the Oracle recommended settings from either tab of the Database Group page. The Databases table lists noncompliant databases in the Policy Violations column. Click the number in that column for more information about the violations for that database.

Best Practice Configuration Recommendations

Enterprise Manager automatically compares the configuration settings of all managed database instances against settings recommended by Oracle. For example, instances with an insufficient number of control file copies are flagged for further review. This automatic configuration check cuts down on manual audits and improves system availability and uniformity.

To view the Oracle configuration recommendations:

In the Targets tab, click Groups.
Select a database group by clicking the group name.
Once on the Database Group page, click Maintenance.

On the Configuration Advice page (Figure 4-6), study the recommendations provided in the Message column. If a database group does not comply with a recommended setting, apply the configuration advice.

Figure 4-6 Database Group Maintenance Page - Configuration Recommendations

Description of db_groups_maint_config_top.gif follows

Description of the illustration db_groups_maint_config_top.gif