In my first job as a database administrator, working for a Fortune 100 company, I managed only three databases, each no more than 100MB in size, and had ample time for database maintenance. I performed regular database-maintenance procedures to keep the databases in peak operating condition, included data archiving and purging and rebuilding of tables and indexes. I also published graphs of database and application performance every week.

Gartner Research estimated in 2007 that the average amount of data managed by each DBA is 1TB. This allows very little time for database maintenance.

The Maintenance Plan

Here are the components of a simple maintenance plan—they should be automated as much as possible. The following sections of this chapter cover each component in detail.

• Regular backups are absolutely critical to the safety of the database (there may be exceptions, but they only prove the rule). Backup logs should be reviewed frequently, and a recovery exercise should be conducted at least once a year—more often if possible.
• The query optimizer relies on statistical information about the data. By default, statistics are automatically refreshed by Oracle every night, but some sites use a custom approach. Manual intervention may be occasionally needed.
• Data that is no longer needed should be archived and then purged from the database. Tables and indexes that contain a large amount of wasted space should be rebuilt to reclaim space.
• Log files should be reviewed regularly—daily if possible. Old log files should be removed.
• Disk capacity and system capacity should be reviewed regularly. Disk space should be proactively added to the database.
• User activities should be audited.
• Passwords should be changed regularly or when compromised. Unneeded accounts should be locked or removed.
• Patches should be applied to the database when necessary.

Backups

Backups are the most important aspect of database maintenance, but the honest truth is that most companies don’t pay enough attention to them. Here are some best practices for backups; please refer to Chapter 12 and Chapter 13 for specific information on methods of backups (and recovery).

Generic Best Practices for Database Backups

The following practices apply to all database backups, not just Oracle backups:

• Establish a service-level agreement (SLA) for backups and recovery. Clearly document the maximum amount of data loss that is permitted, backup-retention policies, and how much time is allowed for database recovery.
• Document the backup methodology, and have it formally reviewed. Publish the documentation to an audience that includes the database owner.
• Periodically test the recoverability of backups. Typically this is done in a testing environment. A recovery test establishes how long it will take to recover the database.
• Be prepared to recover from user error—for example, when a user inadvertently updates or deletes the wrong data. I present several options in Chapter 13.
• Have a contingency plan that covers damage to the operating system or hardware—be prepared to move the database to another server.
• Ensure the safety of backups. The traditional method is to send backup tapes offsite. Newer methods involve backups to a backup appliance over the network. A combination of near-line (disk) and far-line (tape or network) backups can be used. Near-line backups reduce the time needed for recovery, whereas far-line backups increase the safety of backups. The tape library should not be a single point of failure—ensure that data can be recovered even if the tape library is damaged.
• Retain multiple backups in case one set of backups is damaged.
• Backup scripts should incorporate error checking and an adequate amount of logging. The logs should be retained for an appropriate time. Notification of backup failures should be sent to the DBAs. Backup failures should be formally investigated.
• Reports of backup successes and failures, the amount of data backed up, and the time it took to perform backups should be generated and sent to an audience that includes the DBAs and the database owner.
• Changes to any aspect of backup procedures, whether temporary or permanent, should be performed under formal change-control procedures.

Best Practices for Oracle Database Backups

The following practices apply specifically to Oracle database backups:

• Use Recovery Manager (RMAN) for backups. The advantages of RMAN are so numerous and valuable that it would be hard to justify not using it. For example, RMAN checks data blocks for corruption while it is making a backup, single-block recovery is possible with RMAN, and so on.

• Prevent unlogged operations (that is, operations that are not recorded in the redo logs) in databases that use ARCHIVELOG mode; use the ALTER DATABASE FORCE LOGGING command to do this.
• Back up all aspects of the database, including the archived redo logs (for databases that use ARCHIVELOG mode), the control file, and the parameter file (spfile).
• Create logical backups (exports) to supplement physical backups. This creates a certain amount of protection from logical damage, such as data-entry errors. Use a setting such as FLASHBACK_TIME to ensure the consistency of the exported data.
• Make full use of Oracle’s flashback features by increasing the value of UNDO_RETENTION from the default value of 15 minutes to a more appropriate value such as 24 hours. This may allow recovery from logical damage without having to resort to physical backups.
• Databases that run in ARCHIVELOG mode should have LAG_ARCHIVE_TARGET set to an appropriate value, such as 15 minutes, to control maximum data loss.
• Incorporate the use of techniques that check for data corruption. These include initialization parameters such as DB_BLOCK_CHECKING, DB_BLOCK_CHECKSUM, and DB_LOST_WRITE_UPDATE, and commands that scan the database, such as VALIDATE DATABASE.

Statistics

The query optimizer requires statistical information about the data, such as the number of rows in each table, in order to generate efficient query plans. This information is collected using the procedures in the DBMS_STATS package. Beginning with Oracle Database 10g, statistical information about the data is refreshed automatically by a scheduled nightly job called GATHER_STATS_JOB; you are free to disable this job in favor of a custom approach. Oracle supplies a wide collection of procedures to collect and manage statistics, including GATHER, DELETE, IMPORT, EXPORT, LOCK, UNLOCK, PUBLISH, and RESTORE procedures. A complete list of them follows, and you can find details in Oracle Database PL/SQL Packages and Types Reference.

Archiving and Purging

Database performance commonly degrades as the database grows large. To keep the database performing optimally, you should remove unnecessary data. In some cases, it may be necessary to save a copy of the data in a data archive, such as flat files, XML documents, or the like, before the data is purged from the database. The archiving phase also involves a special backup that is tagged with a high-retention setting, such as seven years.

The freed space is automatically used by new data. Regular purging keeps the size of the database under control and helps keep performance stable. If a large amount of data is purged at a time, the sizes of the tables and indexes are not commensurate with the amount of data they actually contain, and additional maintenance is required to compact and shrink the bloated tables and indexes. This is discussed in the section on rebuilding tables and indexes.

The use of partitioning can completely eliminate the need for archiving, purging, and rebuilding. For example, a table can be divided into partitions in such a way that each month’s data is inserted into a separate partition. Queries that require the most recent data perform optimally because the required data is clustered in a relatively small number of data blocks instead of being randomly scattered throughout the table as happens when partitioning is not used. In the case of partitioned tables, purging the data requires only that a partition be dropped; this is a very quick operation. If the old partitions are in a separate tablespace, an alternative to purging is to convert the tablespaces to read-only mode and move them to cheap storage such as WORM drives or unmirrored SATA disks; this is a wonderful way to keep performance optimal without having to remove data. Even the overhead of backups can be kept under control; read-only tablespaces must be backed up only one more time with a high-retention setting and then no longer need to be backed up.

Rebuilding

The size of a table does not change when records are deleted from it—the indexes associated with the table do not shrink in size either. The wasted space causes three problems:

• The gains that come from caching blocks of data in memory are reduced because fewer records are packed into each block.
• Additional space on disk is required as the database grows.
• Backup and recovery require more time, more space, and more computing resources.

If you are licensed for Diagnostics Pack, you can use the Automatic Segment Advisor to identify tables that would benefit from rebuilding. Alternatively, you can conduct your own investigation of wasted space, using the SPACE_USAGE procedure. Listing 14-1 shows a sample run; you see that 1161 blocks are empty.

SQL> SET serveroutput on
SQL>
SQL> DECLARE
  2     l_full_blocks          NUMBER;
  3     l_fs1_blocks           NUMBER;
  4     l_fs2_blocks           NUMBER;
  5     l_fs3_blocks           NUMBER;
  6     l_fs4_blocks           NUMBER;
  7     l_unformatted_blocks   NUMBER;
  8     l_full_bytes           NUMBER;
  9     l_fs1_bytes            NUMBER;
 10     l_fs2_bytes            NUMBER;
 11     l_fs3_bytes            NUMBER;
 12     l_fs4_bytes            NUMBER;
 13     l_unformatted_bytes    NUMBER;
 14  BEGIN
 15     DBMS_SPACE.space:usage (segment_owner           => ’SYSMAN’,
 16                             segment_name            => ’MGMT_JOB_EXEC_SUMMARY’,
 17                             segment_type            => ’TABLE’,
 18                             full_blocks             => l_full_blocks,
 19                             fs1_blocks              => l_fs1_blocks,
 20                             fs2_blocks              => l_fs2_blocks,
 21                             fs3_blocks              => l_fs3_blocks,
 22                             fs4_blocks              => l_fs4_blocks,
 23                             unformatted_blocks      => l_unformatted_blocks,
 24                             full_bytes              => l_full_bytes,
 25                             fs1_bytes               => l_fs1_bytes,
 26                             fs2_bytes               => l_fs2_bytes,
 27                             fs3_bytes               => l_fs3_bytes,
 28                             fs4_bytes               => l_fs4_bytes,
 29                             unformatted_bytes       => l_unformatted_bytes
 30                            );
 31     DBMS_OUTPUT.put_line (’Full blocks:’   || l_full_blocks);
 32     DBMS_OUTPUT.put_line (’Upto 25% free:’ || l_fs1_blocks);
 33     DBMS_OUTPUT.put_line (’Upto 50% free:’ || l_fs2_blocks);
 34     DBMS_OUTPUT.put_line (’Upto 75% free:’      || l_fs3_blocks);
 35     DBMS_OUTPUT.put_line (’Upto 100% free:’     || l_fs4_blocks);
 36     DBMS_OUTPUT.put_line (’Unformatted blocks:’ || l_unformatted_blocks);
 37  END;
 38  /
Full blocks:294
Upto 25% free:0
Upto 50% free:2
Upto 75% free:1
Upto 100% free:1161
Unformatted blocks:46

PL/SQL procedure successfully completed.

Once you have identified tables and indexes that will benefit from compaction, you can reclaim the wasted space using the SHRINK SPACE command, as in Listing 14-2. Note that you have to give Oracle permission explicitly to relocate rows, using the ENABLE ROW MOVEMENT command—Oracle does not do this automatically, so as not to affect any application that has taken advantage of row addresses (ROWIDs).

SQL> ALTER TABLE sysman.mgmt_job_exec_summary
  2  ENABLE ROW MOVEMENT;

Table altered.

SQL> ALTER TABLE sysman.mgmt_job_exec_summary
  2  SHRINK SPACE;

Table altered.

Log File Maintenance

Log files of all kinds accumulate over time and can fill up the available space. For example, a trace file is created whenever certain errors occur, such as ORA-00060 (deadlock) and ORA-00600 (internal error). Listing 14-3 shows the first few lines from such a trace file.

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows XP Version V5.1 Service Pack 2
CPU                 : 2 - type 586
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:451M/2037M, Ph+PgF:2345M/3930M, VA:1243M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 7
Windows thread id: 5196, image: ORACLE.EXE (SHAD)

*** 2008-06-07 09:32:39.734
*** SESSION ID:(99.609) 2008-06-07 09:32:39.734
*** CLIENT ID:() 2008-06-07 09:32:39.734
*** SERVICE NAME:(SYS$USERS) 2008-06-07 09:32:39.734
*** MODULE NAME:(SQL*Plus) 2008-06-07 09:32:39.734
*** ACTION NAME:() 2008-06-07 09:32:39.734

*** 2008-06-07 09:32:39.734
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock: 

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-00011bc5-00000000         7      99    SX   SSX       31     105    SX   SSX
TM-00011bc5-00000000        31     105    SX   SSX        7      99    SX   SSX

session 99: DID 0001-0007-000028EA      session 105: DID 0001-001F-00000041
session 105: DID 0001-001F-00000041     session 99: DID 0001-0007-000028EA

Rows waited on:
  Session 99: no row
  Session 105: no row

----- Information for the OTHER waiting sessions -----
Session 105:
  sid: 105 ser: 881 audsid: 349519 user: 88/IGGY flags: 0x45
  pid: 31 O/S info: user: SYSTEM, term: IGGY, ospid: 5112
    image: ORACLE.EXE (SHAD)
  client details:
    O/S info: user: IGGYIGNATIUS, term: IGGY, ospid: 4548:6076
    machine: WORKGROUPIGGY program: sqlplus.exe
    application name: SQL*Plus, hash value=3669949024
  Current SQL Statement:
  DELETE FROM PARENT WHERE ID=2

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=9vym32vjhdgzc) -----
DELETE FROM PARENT WHERE ID=1
===================================================

The log files are organized into an Automatic Diagnostics Repository (ADR), whose location is controlled by the DIAGNOSTIC_DEST setting. Log files are automatically purged after 30 days—you can change the retention window using the SET CONTROL option of the adrci tool. You can also purge log files manually using the tool’s PURGE command, as in Listing 14-4.

[oracle@localhost ~]$ adrci

ADRCI: Release 12.1.0.1.0 - Production on Mon May 11 01:45:41 2015

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/u01/app/oracle"
adrci> show homepath
ADR Homes:
diag/tnslsnr/localhost/listener
diag/rdbms/orcl/orcl
diag/clients/user_oracle/host_61728193_80
adrci> set homepath diag/rdbms/orcl/orcl
adrci> purge -age 10
adrci> show tracefile
     diag/rdbms/orcl/orcl/trace/orcl_lgwr_24237.trc
     diag/rdbms/orcl/orcl/trace/orcl_dbrm_24231.trc
     diag/rdbms/orcl/orcl/trace/orcl_mmon_24247.trc
     diag/rdbms/orcl/orcl/trace/alert_orcl.log
adrci> exit

Auditing

Oracle provides the ability to audit all aspects of database usage. Audit records can be stored in the AUD$ table owned by SYS or in files outside the database—this is controlled by the AUDIT_TRAIL and AUDIT_FILE_DEST settings. There are a number of useful views on the AUD$ table, the chief ones being DBA_AUDIT_TRAIL and DBA_AUDIT_SESSION. Listing 14-5 shows a sample of the data in the DBA_AUDIT_SESSION table.

SELECT   TIMESTAMP AS "LOGON",
         logoff_time AS "LOGOFF",
         logoff_lread AS "GETS",
         logoff_pread AS "READS",
         logoff_lwrite AS "WRITES",
         session_cpu AS "CPU"
    FROM dba_audit_session
   WHERE username = ’IGGY’
     AND logoff_time IS NOT NULL
ORDER BY TIMESTAMP;

LOGON       LOGOFF            GETS      READS     WRITES        CPU
----------- ----------- ---------- ---------- ---------- ----------
11/25 19:07 11/25 19:08       1187         11        364         32
11/25 19:08 11/25 19:16       2105         12        417         32
11/25 19:16 11/25 19:17       1138          3        431         20
11/25 19:18 11/25 19:18        849          0        356         24
11/25 19:18 11/25 19:19       1266          0        516         17
11/25 19:20 11/25 19:29       2655          0       1136         47
11/25 19:33 11/25 19:35       3894          6       1597         49
11/25 19:38 11/25 19:38        154          0         15          3
11/25 19:40 11/25 19:41         71          0          6         11
11/25 19:41 11/25 20:13       2778         10       1059         50
11/25 20:14 11/25 20:28     319540       1123        643       1011
11/28 11:30 11/28 11:30        383         13          6          5
05/26 11:31 05/26 20:10       2427        101        726        100
05/26 14:29 05/26 14:29         58          0          6          0
05/26 14:31 05/26 14:31         58          0          6          0
05/26 14:32 05/26 20:09        258          8         12         21
06/07 09:21 06/07 17:21       3049        114        732         83
06/07 09:32 06/07 17:21        274         15         49          3

18 rows selected.

When creating a database using DBCA, you can choose to enable auditing for logon and logoff events and certain privileged activities such as ALTER DATABASE and ALTER USER. A number of views can be used to determine which activities are being audited, the chief ones being DBA_STMT_AUDIT_OPTS (which SQL commands are being monitored) and DBA_OBJ_AUDIT_OPTS (which tables are being monitored). Listing 14-6 shows a sample of the data in the DBA_STMT_AUDIT_OPTS table.

SELECT   audit_option,
         CASE
            WHEN user_name IS NOT NULL
              THEN user_name
            ELSE ’ALL USERS’
         END AS username
    FROM dba_stmt_audit_opts
ORDER BY audit_option, user_name;

AUDIT_OPTION                             USERNAME
---------------------------------------- ------------------------------
ALTER ANY PROCEDURE                      ALL USERS
ALTER ANY TABLE                          ALL USERS
ALTER DATABASE                           ALL USERS
ALTER PROFILE                            ALL USERS
ALTER SYSTEM                             ALL USERS
ALTER USER                               ALL USERS
CREATE ANY JOB                           ALL USERS
CREATE ANY LIBRARY                       ALL USERS
CREATE ANY PROCEDURE                     ALL USERS
CREATE ANY TABLE                         ALL USERS
CREATE EXTERNAL JOB                      ALL USERS
CREATE PUBLIC DATABASE LINK              ALL USERS
CREATE SESSION                           ALL USERS
CREATE USER                              ALL USERS
DROP ANY PROCEDURE                       ALL USERS
DROP ANY TABLE                           ALL USERS
DROP PROFILE                             ALL USERS
DROP USER                                ALL USERS
EXEMPT ACCESS POLICY                     ALL USERS
GRANT ANY OBJECT PRIVILEGE               ALL USERS
GRANT ANY PRIVILEGE                      ALL USERS
GRANT ANY ROLE                           ALL USERS
ROLE                                     ALL USERS
SYSTEM AUDIT                             ALL USERS

24 rows selected.

Reviewing audit records regularly is a good practice. Note that Oracle does not delete old audit data automatically—that is the responsibility of the DBA. You should consider archiving the data using the exp or expdp utility before you delete it.

User Management

The security of the database is the DBA’s responsibility. Here are some simple security rules:

• Passwords should be changed regularly. This is controlled by the PASSWORD_LIFE_TIME, PASSWORD_REUSE_TIME, and PASSWORD_REUSE_MAX settings in the DEFAULT profile. Resist the temptation to relax or disable these settings.
• Shared passwords should be changed when employees are relieved of their responsibilities or leave the company. For example, DBAs typically share the passwords to the SYS and SYSTEM accounts. These passwords should be changed whenever a DBA leaves the organizational group or the company.
• Users should be required to have strong passwords; this is not required by default. Execute the utlpwdmg.sql script from the SYS account to enforce the requirement.
• When a user’s job responsibilities change, their database privileges should be changed appropriately. When a user leaves the organization, their account should be removed from the database.

Capacity Management

Regular capacity reviews are part of good database management. Database size and free space should be recorded at regular intervals—the required information can be found in the DBA_DATA_FILES and DBA_FREE_SPACE views, as shown in Listing 14-7. Database size and free space can also be checked using Enterprise Manager. Space should be proactively added to the database as necessary. You should make growth projections and identify opportunities to reclaim disk space. Capacity management also refers to use of system resources such as CPU and memory. The information in the Statspack reports and tables can be used to track system utilization, or you can develop a simple system that periodically captures system utilization metrics from the V$SYSSTAT table.

SELECT   tablespace:name,
         file_name,
         file_id,
         BYTES / 1024 AS kb,
         autoextensible,
         increment_by * 8192 / 1024 AS next_kb,
         maxbytes / 1024 AS max_kb
    FROM dba_data_files
ORDER BY tablespace:name, 
         file_id;

TABLESPACE FILE_NAME               FILE_ID         KB AUT    NEXT_KB     MAX_KB
---------- -------------------- ---------- ---------- --- ---------- ----------
SYSAUX     /u01/app/oracle/orad          3    1075200 YES      10240   33554416
           ata/ORCL/datafile/o1
           _mf_sysaux_9fxmvhl3_
           .dbf

SYSTEM     /u01/app/oracle/orad          1     819200 YES      10240   33554416
           ata/ORCL/datafile/o1
           _mf_system_9fxmx6s1_
           .dbf

UNDOTBS1   /u01/app/oracle/orad          4     604160 YES       5120   33554416
           ata/ORCL/datafile/o1
           _mf_undotbs1_9fxn0vg
           g_.dbf

USERS      /u01/app/oracle/orad          6       5120 YES       1280   33554416
           ata/ORCL/datafile/o1
           _mf_users_9fxn0t8s_.
           dbf

Time Series

A time series is a sequence of measurements made at periodic intervals. The Statspack tables contain much time series data and can be used for capacity reviews. The Statspack tables STATS$OSSTAT, STATS$SYSSTAT, STATS$SYSTEM_EVENT, and STATS$SYS_TIME_MODEL are of particular interest—they store snapshots of the dynamic performance views V$OSSTAT, V$SYSSTAT, V$SYSTEM_EVENT, and V$SYS_TIME_MODEL, respectively. As illustrated in Listing 14-8, V$OSSTAT offers cumulative values of various OS metrics such as CPU usage, and STATS$OSSTAT contains snapshots of this data; the snapshots are identified by the SNAP_ID column.

SQL> SELECT stat_name, value FROM v$osstat;

STAT_NAME                                                             VALUE
---------------------------------------------------------------- ----------
NUM_CPUS                                                                  2
IDLE_TIME                                                           3769723
BUSY_TIME                                                            668049
USER_TIME                                                            463348
SYS_TIME                                                             204701
AVG_IDLE_TIME                                                       1884476
AVG_BUSY_TIME                                                        333641
AVG_USER_TIME                                                        231300
AVG_SYS_TIME                                                         101963
RSRC_MGR_CPU_WAIT_TIME                                                 1278
PHYSICAL_MEMORY_BYTES                                            2136969216
VM_IN_BYTES                                                      1393717248
VM_OUT_BYTES                                                      394657792

13 rows selected.

SQL> SELECT snap_id, osstat_id, value
  2    FROM stats$osstat
  3   WHERE  snap_id IN (3000, 3001);

   SNAP_ID  OSSTAT_ID      VALUE
---------- ---------- ----------
      3000          0          2
      3000          1  654407998
      3000          2   72816975
      3000          3   60092930
      3000          4   12053254
      3000          6     670791
      3000         14          0
      3000         15       1.25
      3000       1008      16396
      3001          0          2
      3001          1  655068971
      3001          2   72864457
      3001          3   60137335
      3001          4   12056331
      3001          6     670791
      3001         14          0
      3001         15 .309570313
      3001       1008     494964

18 rows selected. 

The time series data in the Statspack tables can be manipulated with SQL queries into a format that is suitable for reports and graphs. First you use the PIVOT operator to create a two-dimensional table of the cumulative values (Listing 14-9). Then you use an analytic function called LAG to compute the difference between the values in successive rows (Listing 14-10). The CPU utilization graph shown in Figure 14-1 was generated using the data produced by the SQL query shown in Listing 14-11.

SQL> COLUMN busy_time FORMAT 9999999990
SQL> COLUMN idle_time FORMAT 9999999990
SQL>
SQL> SELECT *
  2    FROM (SELECT snap_id, osstat_id, value FROM stats$osstat
  3           WHERE SNAP_ID IN (3000, 3001))
  4         PIVOT
  5         (SUM(value) FOR osstat_id IN (1 AS idle_time, 2 AS busy_time));

   SNAP_ID   IDLE_TIME   BUSY_TIME
---------- ----------- -----------
      3000   654407998    72816975
      3001   655068971    72864457

SQL> COLUMN busy_time FORMAT 9999999990
SQL> COLUMN idle_time FORMAT 9999999990
SQL>
SQL> SELECT snap_id,
  2         idle_time - LAG (idle_time) OVER (ORDER BY snap_id) AS idle_time,
  3         busy_time - LAG (busy_time) OVER (ORDER BY snap_id) AS busy_time
  4    FROM (SELECT snap_id, osstat_id, value FROM stats$osstat
  5           WHERE SNAP_ID IN (3000, 3001))
  6         PIVOT
  7         (SUM(value) FOR osstat_id IN (1 AS idle_time, 2 AS busy_time));

   SNAP_ID   IDLE_TIME   BUSY_TIME
---------- ----------- -----------
      3000
      3001      660973       47482

Figure 14-1. CPU utilization graph produced from the data in the STATS$OSSTAT table

WITH osstat AS
     (SELECT snap_id,
             LAG (snap_id) OVER (ORDER BY snap_id) AS prev_snap_id,
             idle_time - LAG (idle_time) OVER (ORDER BY snap_id) AS idle_time,
             busy_time - LAG (busy_time) OVER (ORDER BY snap_id) AS busy_time
        FROM (SELECT snap_id, osstat_id, value FROM stats$osstat)
             PIVOT
             (SUM(value) FOR osstat_id IN (1 AS idle_time, 2 AS busy_time)))
SELECT   s1.snap_time,
         o.busy_time / (o.idle_time + o.busy_time) AS cpu_utilization
    FROM osstat o,
         stats$snapshot s1,
         stats$snapshot s2
   WHERE s1.snap_id = o.snap_id
     AND s2.snap_id = o.prev_snap_id
     AND s2.startup_time = s1.startup_time
ORDER BY s1.snap_id;

There is a wealth of other useful data in the Statspack tables. For example, STATS$SYSSTAT offers cumulative values for Oracle metrics such as logical reads and physical reads; STATS$SYSTEM_EVENT reports the time spent waiting for I/O, locks, and other resources; and STATS$SYS_TIME_MODEL offers summary information such as the total of the elapsed times of all SQL queries processed by Oracle.

Patching

Oracle periodically releases fixes, known as patches, for software bugs. A one-off patch is a fix for an individual bug, whereas a patchset is a collection of numerous bug fixes. A critical patch update (CPU) is a collection of fixes for security-related bugs; Oracle releases one such CPU every quarter. A patchset has an Oracle database version string associated with it; for example, 11.2.0.4 is a patchset associated with Oracle 11g Release 2. By contrast, one-off patches and CPUs are identified by a single number; for example, 7210195 was the number of the July 2008 CPU. One-off patches, patches, and patchsets can be downloaded from the Patches & Updates section of the Metalink customer portal.

Some organizations adopt a policy of applying software patches and CPUs whenever they become available. Listing 14-12 is a typical notice regarding the availability of a new CPU that you will receive from Oracle if you have a support contract.

July 15th, 2008

Oracle Critical Patch Update July 2008

Dear Oracle customer,

The Critical Patch Update for July 2008 was released on July 15, 2008. Oracle
strongly recommends applying the patches as soon as possible.

The Critical Patch Update Advisory is the starting point for relevant information.
It includes the list of products affected, pointers to obtain the patches, a summary
of the security vulnerabilities for each product suite, and links to other important
documents. Supported products that are not listed in the "Supported Products and
Components Affected" section of the advisory do not require new patches to be
applied.

Also, it is essential to review the Critical Patch Update supporting documentation
referenced in the Advisory before applying patches, as this is where you can find
important pertinent information.

The Critical Patch Update Advisory is available at the following location:

Oracle Technology Network:
http://www.oracle.com/technology/deploy/security/alerts.htm

The next four Critical Patch Update release dates are:

*  October 14, 2008
*  January 13, 2009
*  April 14, 2009
*  July 14, 2009

Sincerely, Oracle Security Alerts

The notification about a CPU directs you to a web page where you can find more information. Once you have determined the specific patch number applicable to your OS platform, you can download the CPU from My Oracle Support.

CPUs are applied using the opatch utility. Complete instructions are provided with the patch.

Summary

Here is a short summary of the concepts touched on in this chapter:

• Database maintenance is required to keep the database in peak operating condition. Most aspects of database maintenance can be automated. Oracle performs some maintenance automatically: collecting statistics for the query optimizer to use.
• Database backups are the most important responsibility of the DBA. RMAN is the preferred tool for numerous reasons. Recovery testing is vital to validate the backup procedures.
• The query optimizer requires statistical information about the data—for example, the number of rows in each table—in order to generate efficient query plans. This information is collected using the procedures in the DBMS_STATS package. Beginning with Oracle Database 10g, statistical information about the data is collected automatically by a scheduled nightly job; you are free to disable this job in favor of a custom approach.
• Archiving refers to the task of making a copy of old data in preparation for deleting it from the database.
• Purging refers to the task of deleting data that has already been archived.
• Rebuilding refers to the task of compacting tables and indexes from which large amounts of data have been purged.
• Log files and trace files are stored in the Automatic Diagnostics Repository (ADR) and managed using the adrci utility.
• Audit records should be periodically reviewed, archived, and purged.
• Passwords should be changed regularly, shared passwords should be changed when they are compromised, password complexity should be enforced, and accounts should be removed when the owners are relieved of their responsibilities or leave the company.
• The information in DBA_DATA_FILES and DBA_FREE_SPACE can be used to set up a simple system managing disk capacity.
• The Statspack tables STATS$OSSTAT, STATS$SYSSTAT, STATS$SYSTEM_EVENT can be used to produce graphs of system capacity.

Exercises

• Scan your database for corruption using the VALIDATE DATABASE command.
• Determine the start and end times of the maintenance windows in your database. Which maintenance jobs run during these windows?
• Temporarily disable auditing, and move the AUD$ table to a dedicated tablespace.
• Develop a procedure to track the growth of tablespaces and free space. Use the information in the DBA_DATA_FILES and DBA_FREE_SPACE views. Capture the information once a week.
• Explain the autoextensible feature of data files. Is it enabled in your database? What are its advantages? What are its disadvantages?
• Develop a procedure to track the growth of the 20 biggest tables in your database. Use the information in the DBA_SEGMENTS view. Capture the information once a week.
• Using the data in the STATS$SYSSTAT table, create graphs of the following workload metrics: logical reads, physical reads, logical reads per transaction, and physical reads per transaction.
• Download the latest critical patch update, and apply it to your database.
• Locate your database’s alert log and listener log. What do they contain? Create an automated process to manage their size.