Oracle Essentials Oracle Database 11g (27 page)

Oracle Enterprise Manager

|

129

Figure 5-3. Oracle Enterprise Manager Database home page
In the Oracle Database 11
g
version, some of the key manageability functionality in each of the tabs includes:

Home page

Provides a quick view of database status including whether the database is up, the database version, the hostname, and listener. Key metrics describing status of the host CPU, active sessions, and SQL response time are typically displayed in graphical form. Summaries of diagnostics, space utilized, and high availability status, alerts, and policy violations are also typically displayed. Related links are provided from this page to Advisor Central (a page for quick access to the advisors) and other key metrics such as the alert log content.

Performance page

Includes a summary of important performance statistics such as CPU utilization, average active sessions, disk I/O, and instance throughput.

Availability page

Here you can manage backup and recovery using tools such as RMAN and LogMiner.

Server page

Includes links to automated maintenance features, such as Automatic Memory Management, the AWR, and scheduling.

130

|

Chapter 5: Managing Oracle

Schema page

Here youcan manage users and privileges, Oracle tables, indexes, views, synonyms, sequences, and database links, and can initiate related management functions such as Flashback.

Data Movement page

Manages data movement features such as Streams and transportable tablespaces.

Software and Support page

Provides access to the Support Workbench for reporting problems to Oracle Support that you observe in the AWR.

The sophistication of Enterprise Manager continues to grow, as illustrated by the Real Applications Testing Option’s database workload capture and replay capability introduced in Oracle Database 11
g
. Youcan now use Enterprise Manager to re-create your production environment in a test environment and test your changes in the test environment before propagating those changes back into production.

You will find this new capability under the Software and Support tab. Here you can define and start or schedule the capture of the production workload (e.g., the load and concurrency in the production system). You can also view other previous captured workloads, manage replays, and stop an active capture or replay. You then move the captured workload, in replay format, to the test system. You can then replay the production workload against the changes you make to the test system while checking for errors, data divergence, and performance changes.

EM2Go

EM2Go is a mobile version of Enterprise Manager introduced with Oracle Database 10
g
. It can be used for remote wireless management of Oracle database instances and Oracle Application Servers. Providing a subset of the functionality in Enterprise Manager, EM2Go leverages the previously described OMS, associated Management Repository, and Oracle Agents in the EM architecture. The Enterprise Manager Console is accessed through a Microsoft Pocket PC Internet Explorer browser on a PDA device. Communication between the console and OMS and between OMS and the Agents is via HTTP.

The administrator begins by logging into Enterprise Manager from the EM2Go Home page by entering the appropriate EM username and password. Upon logging in, administrators are presented with a summary of alerts and targets. Each is a link that you can drill to for more detail.

You can set up EM2Go to forward alert notifications by way of email directly to your PDA. It supports ad-hoc SQL and operating system commands. Performance monitoring includes metrics history graphing of warnings and alerts from the Oracle database and Oracle Application Server and access to the database home page.

Oracle Enterprise Manager

|

131

Fragmentation and Reorganization

Fragmentation is a problem that can negatively impact performance—and one that many DBAs have struggled to manage in the past. Fragmentation can be an unwanted phenomenon if it results in small parts of noncontiguous “free space” that cannot be reused.

In Oracle, a collection of contiguous blocks is referred to as an
extent
. A collection of extents is referred to as a
segment
. Segments can contain anything that takes up space—for example, a table, an index, or a rollback segment. Segments typically consist of multiple extents. As one extent fills up, a segment begins to use another extent. As fragmentation occurs, by database activity that leaves “holes” in the contiguous space represented by extents, segments acquire additional extents. As fragmentation grows, increased I/O activity results in reduced performance.

Resolving Fragmentation

As of Oracle Database 10
g
, resolving fragmentation issues became fairly trivial. You can perform an online segment shrink using the Segment Advisor interface accessible through EM. ADDM recommends segments to shrink, and you simply choose to accept the recommendations.

For Oracle9
i
databases, a common means of reducing fragmentation was through an online reorganization accomplished through a CREATE TABLE...AS SELECT

online operation—that is, the copying of the contents of one table to another while the original table is updated. Changes to the original table were tracked and applied to the new table. Physical and logical attributes of the table could be changed during this online operation, thus allowing an online reorganization.

Prior to Oracle9
i
, reducing fragmentation was more difficult. The general recommendation was to avoid fragmentation through careful planning. But the usual way to solve fragmentation was to reorganize a table by exporting the table, dropping it, and importing it. The data was unavailable while the table was in the process of being reorganized. Many DBAs claimed that they saw improved performance after reorganizing segments into a single extent. Over time, a decrease in performance reoccurred as the number of extents the table occupied increased.

Oracle performance increased as a result of these reorganization operations, but this improvement was
not
due to a decrease in the number of extents. When a table is dropped and re-created, several things happened that increased performance:

• Each block was loaded as full of rows as possible.

• As a consequence, the high-water mark of the table (the highest block that has ever had data in it) was set to its lowest point.

132

|

Chapter 5: Managing Oracle

• All indexes on the table were rebuilt, which meant that the index blocks were as full as possible. The depth of the index, which determined the number of I/Os it takes to get to the leaf blocks or the index, was sometimes minimized.

By eliminating fragments and shrinking segments in a much more automated and online fashion, database releases since Oracle Database 10
g
greatly simplify solving fragmentation problems; the result is that optimal conditions exist for performance.

Backup and Recovery

Even if you’ve taken adequate precautions, critical database records can sometimes be destroyed as a result of human error or hardware or software failure. The only way to prepare for this type of potentially disastrous situation is to perform regular backup operations.

Two basic types of potential failures can affect an Oracle database:
instance failure
, in which the Oracle instance terminates without going through the shutdown process; and
media failure
, in which the disks that store the information in an Oracle database are corrupted or damaged.

After an instance failure, Oracle will automatically perform crash recovery. For example, you can use Real Application Clusters to automatically perform instance recovery when one of its instances crashes. However, DBAs must initiate recovery from media failure. The ability to recover successfully from this type of failure is the result of careful planning. The recovery process includes restoring older copies of the damaged datafile(s) and rolling forward by applying archived and online redo logs.

To ensure successful recovery, the DBA should have prepared for this eventuality by performing the following actions:

• Multiplexing online redo logs by having multiple log members per group on different disks and controllers

• Running the database in ARCHIVELOG mode so that redo log files are archived before they are reused

• Archiving redo logs to multiple locations

• Maintaining multiple copies of the control file(s)

• Backing up physical datafiles frequently—ideally, storing multiple copies in multiple locations

Running the database in ARCHIVELOG mode ensures that you can recover the database up to the time of the media failure; in this mode, the DBA can perform online datafile backups while the database is available for use. In addition, archived redo logs can be sent to a standby database (explained in
Chapter 10)
to which they may be applied.

Backup and Recovery

|

133

Recovery Manager, also known as RMAN, first introduced in Oracle8 and greatly enhanced since, provides an easy-to-use frontend to manage this process. RMAN is accessible today through Enterprise Manager.

Types of Backup and Recovery Options

There are two major categories of backup:

Full backup

Includes backups of datafiles, datafile copies, tablespaces, control files (current or backup), or the entire database (including all datafiles and the current control file). Reads entire files and copies all blocks into the backup set, skipping only datafile blocks that have never been used (with the exception of control files and redo logs where no blocks are skipped).

Incremental backup

Includes backups of datafiles, tablespaces, or the whole database. Reads entire files and backs up only those data blocks that have changed since a previous backup.

You can begin backups through the Recovery Manager or the Oracle Enterprise Manager interface to RMAN, which uses the database export facility, or you can initiate backups via standard operating system backup utilities.

In general, RMAN supports most database backup features, including open or online backups, closed database backups, incremental backups at the Oracle block level, corrupt block detection, automatic backups, backup catalogs, and backups to sequential media. RMAN added capabilities in Oracle9
i
for one-time backup configuration, recovery windows to determine and manage expiration dates of backups, and restartable backups and restores. Also added was support for testing of restores and recovery.

Since Oracle Database 10
g
, RMAN can perform image copy backups of the database, tablespaces, or datafiles. RMAN can be used to apply incremental backups to datafile image backups. The speed of incremental backups is increased through a change-tracking feature by reading and backing up only changed blocks.

Recovery options include the following:

• Complete database recovery to the point of failure

• Tablespace point-in-time recovery (recovery of a tablespace to a time different from the rest of the database)

• Time-based or point-in-time database recovery (recovery of the entire database to a time before the most current time)

• Recovery until the CANCEL command is issued

• Change-based or log sequence recovery (to a specified System Change Number, or SCN)

134

|

Chapter 5: Managing Oracle

You can recover through RMAN, using either the recovery catalog or control file or via SQL or SQL*Plus.

RMAN in Oracle Database 10
g
improved the reliability of backups and restores through a number of added features. This version added backup and restore of standby control files. RMAN now can automatically retry a failed backup or restore operation. During recovery, RMAN can automatically create and recover datafiles not in the most recent backup. Where backups are missing or corrupt during the restore process, RMAN automatically uses an older backup.

To speed backups and restore operations, Oracle Database 10
g
introduced the Flash Recovery Area, thus organizing recovery files to a specific area on disk. These files include a copy of the control file, archived log files, flashback database logs, datafile copies, and RMAN backups. You can set a RETENTION AREA parameter to retain needed recovery files for specific time windows. As backup files and archivelogs age beyond the time window, they are automatically deleted. ASM (described earlier in this chapter) can configure the Flash Recovery Area. If availability of disk space is an issue, you can also take advantage of RMAN’s ability to compress backup sets.

Making Sure the Backup Works

The key to providing an adequate backup and recovery strategy is to simulate recovery from failure using the backups from your test system before using the backups to restore a live production database. Many times, backup media that were thought to be reliable prove not to be, or backup frequencies that were thought to be adequate prove to be too infrequent to allow for timely recoveries. It’s far better to discover that recovery is slow or impossible in test situations than after your business has been impacted by the failure of a production system.