[Laskey99] Chapter 11. The Oracle Database

Stefen 2010-06-01

展开全文

In Chapter 10 , we introduced the Oracle instance, and explained that the terms instance and database are often used interchangeably. This chapter provides information about the physical structures that make up an Oracle database, which is accessed by means of the Oracle instance.

11.1. Types of Database Files

The Oracle database consists of a number of physical files, often referred to as operating system files, since they are usually created and maintained by the host operating system. These physical files are used by Oracle for parameter storage, database coordination, and data storage. The files shown in the following list, which are described in the following sections, are unique to a particular database; that is, each mounted and opened database must have all of these files, and the files are not shared across instances except in the special case of the Oracle Parallel Server, whose purpose is to share datafiles.

Parameter storage files:
- Initialization file (INIT.ORA)
- Configuration file (CONFIG.ORA)
Database coordination files:
- Control files
- Redo log files
Data storage files

11.1.1. Parameter Storage Files

Two types of parameter storage files are used by Oracle: the INIT.ORA file and the optional CONFIG.ORA file. These files, described in the following sections, collectively contain information provided by the DBA to configure and tune a particular Oracle instance.

11.1.1.1. Initialization file

The initialization file, usually referred to as the INIT.ORA file, is the primary file that contains configuration and tuning parameters. For detailed information on these parameters, see Chapter 12. An INIT.ORA file must exist for each Oracle instance. This file is used by Oracle when starting the database, and therefore must be located in a known location, or Oracle must be told where it is located through the use of a command-line parameter to Server Manager. In Unix systems, this file is found in the $ORACLE_HOME/dbs directory.

The INIT.ORA file is one of only two Oracle files (the other is the CONFIG.ORA file) that can be directly read and manipulated by a user—in this case, by the DBA. The INIT.ORA file is stored as plain text—ASCII on most systems—and is typically edited by the DBA, using a text editor. The file format is straightforward: it consists of multiple lines, each of which specifies a parameter in the following format:

parameter_name =
parameter_value

parameter_name: Is the name of the parameter to be assigned a value.
parameter_value: Is the value to be assigned—either numeric or text.

The following rules apply to entries in the INIT.ORA table:

Parameter names are not case-sensitive. For example, the name "DB_Block_Buffers" is the same as "DB_BLOCK_BUFFERS" or "DB_block_buffers".
There may be any number of spaces around the "=" sign.
Parameter names must be spelled exactly; misspellings will result in errors.
Text values may be provided without quotes.
Parameters must be specified one per line.
Comments begin with the # character (and are encouraged!).

Because the INIT.ORA file is a plain text file, it should not contain any special formatting, graphic, or control characters other than the newline character. Do not edit the INIT.ORA file with a word processing program, since these programs usually store extra control characters that will prevent the INIT.ORA file from being read properly by Oracle. Note that any error in the INIT.ORA file will prevent the database from being started.

Be sure that the INIT.ORA file ends with a newline character. If it does not, a syntax error will be indicated, and this error will be very difficult to find.

11.1.1.2. Configuration file

The configuration file, usually referred to as the CONFIG.ORA file, is an optional file that contains parameters, just like the INIT.ORA file. In fact, the CONFIG.ORA file is a subset of the INIT.ORA file, and can only be used if the INIT.ORA file contains an "include" line specifying the name of the CONFIG.ORA file. Since the CONFIG.ORA file is actually merged into the INIT.ORA file prior to processing by Oracle, all of the same syntax rules apply.

The CONFIG.ORA file is primarily used to segregate a particular set of standard initialization parameters. For example, when running the Oracle Parallel Server, there are many initialization parameters that must be set identically for each instance, and these are stored in a separate CONFIG.ORA file. Similarly, several Oracle instances running on the same host may share a common subset of parameters that is stored in a single CONFIG.ORA file, while each instance also has a set of specific initialization parameters stored in its INIT.ORA file.

At some Oracle installations, the database is configured differently for transaction processing (during the day, for example) and for batch processing (at night). In this case, the database is restarted with a different INIT.ORA file for each time period, but a single CONFIG.ORA file contains all common initialization parameters.

11.1.2. Database Coordination Files

Two types of database coordination files must exist for every Oracle instance: control files and redo log files. These files are critical to the operation of Oracle, and the loss of or damage to either file could have catastrophic effects on the database.

11.1.2.1. Control files

Every Oracle instance must have one or more control files. The control file is a binary file that is critical to Oracle, but is not directly readable by a user, nor is it editable by a text editor. The control file can be thought of as a software "bootstrap" file; it contains information that Oracle requires to start. Information stored in the control file includes:

Names and locations of data files
Names and locations of redo log files
Information on the status of archived log files
The current redo log sequence number
Redo log information required for recovery
Backup history (Oracle8 only)
Timestamp information on the instance creation and startup/shutdown
Essential parameters specified at database creation (e.g., MAXDATAFILES)

The information stored in the control file is so critical that if the control file is lost or damaged, the only options available for recovery are either to create a new control file (assuming that the DBA has access to all pertinent information required) or to rebuild the database and restore from a backup. Because of the critical nature of the control file, Oracle allows the DBA to maintain multiple mirrored control files, as specified by the CONTROL_FILES parameter in the INIT.ORA file. For example, the following line from INIT.ORA specifies two control files:

CONTROL_FILES = (/disk00/oracle/control01.ctl,/disk02/oracle/control02.ctl)

We strongly recommend that you maintain multiple mirrored control files on separate disks in case disk failure occurs, and, where possible, on different disk controllers in case controller failure occurs. Three or more mirrored control files are not unusual at well-administered Oracle installations.

Since the control file is not human readable, and is used only by Oracle itself, we recommend that, for operating systems with file protection, the control file be made readable only by the Oracle owner. In Unix, control files should be owned by Oracle, be assigned to group DBA, and have a protection of 600, which gives read/write access to the owner, but no access to the group or world.

Although the control file is in a binary format and is readable only by Oracle, a method is provided to create a script containing SQL statements that can be used to recreate a control file. This text version may be edited and used to create a new control file with modified values.

To create a text version of the control file, the DBA may use this command:

ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;

After execution of this command, a trace file will be created in a directory specified by the INIT.ORA parameter BACKGROUND_DUMP_DEST; by default, this will usually be $ORACLE_HOME/rdbms/log. You must go to the directory containing trace files and look for a file with an extension of .trc and a date/time stamp at the time you executed the ALTER DATABASE command. This trace file will be similar to the one shown in the following sample, which was created with Oracle Version 7.3.4 on an HP platform, and will contain the SQL statements required to create a new control file and restart the database.

Code View: Scroll / Show All

Dump file /disk00/oracle/product/7.3.4/rdbms/log/DW1/ora_13607.trc
Oracle7 Server Release 7.3.4.2.0 - Production
With the distributed, replication and parallel query options
PL/SQL Release 2.3.4.2.0 - Production
ORACLE_HOME = /disk00/oracle/product/7.3.4
System name:    HP-UX
Node name:      datasrv2
Release:        B.10.20
Version:        E
Machine:        9000/800
Instance name: DW1
Redo thread mounted by this instance: 1
Oracle process number: 24
UNIX process pid: 13607, image: oracleDW1

*** SESSION ID:(33.132) 1998.09.20.20.10.16.174
*** 1998.09.20.20.10.16.173
# The following commands will create a new control file and use it
# to open the database.
# No data other than log history will be lost. Additional logs may
# be required for media recovery of offline data files. Use this
# only if the current versions of all online logs are available.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DW1" NORESETLOGS NOARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 1000
    MAXINSTANCES 2
    MAXLOGHISTORY 200
LOGFILE
  GROUP 1 '/disk07/oracle/oradata/DW1/log01.log'  SIZE 2M,
  GROUP 2 '/disk10/oracle/oradata/DW1/log02.log'  SIZE 2M,
  GROUP 3 '/disk07/oracle/oradata/DW1/log03.log'  SIZE 2M,
  GROUP 4 '/disk10/oracle/oradata/DW1/log03.log'  SIZE 2M
DATAFILE
  '/disk00/oracle/oradata/DW1/system01.dbf',
  '/disk05/oracle/oradata/DW1/temp01',
  '/disk00/oracle/oradata/DW1/tools01.dbf',
  '/disk04/oracle/oradata/DW1/ldata01.dbf',
  '/disk08/oracle/oradata/DW1/ldata02.dbf',
  '/disk07/oracle/oradata/DW1/user01.dbf',
  '/disk09/oracle/oradata/DW1/rbs01.dbf',
  '/disk14/oracle/oradata/DW1/data01.dbf',
  '/disk15/oracle/oradata/DW1/data02.dbf',
  '/disk02/oracle/oradata/DW1/index01.dbf'
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

This SQL text file may then be edited (carefully!) by the DBA. You might change the value of one of the configuration parameters (MAXDATAFILES, for example), or perhaps change the name or location of a LOGFILE. To replace a control file, perform the following steps:

Use the ALTER DATABASE BACKUP CONTROLFILE TO TRACE command to create a control file trace (.trc) file.
Locate the .trc file in the BACKGROUND_DUMP_DEST directory, and rename it to something meaningful.
Perform a NORMAL or IMMEDIATE shutdown of the database. It is best if all tablespaces are online at the time of the shutdown; otherwise, recovery will be required after the control file is recreated.
Edit the .trc file created in step 1. Be sure to remove the documentation lines at the top of the file.
Using Server Manager or SQL*DBA, CONNECT AS INTERNAL.
Execute the edited file, which will recreate the control file and start the database.

Since this is a critical operation and an error may result in a database that cannot be opened, we highly recommend that you back up the database prior to creating a new control file.

11.1.2.2. Redo log files

Redo log files are operating system files used by Oracle to maintain logs of all transactions performed against the database. The primary purpose of these log files is to allow Oracle to recover changes made to the database in the case of a failure.

An Oracle database must have at least two redo log files, and most databases have more than two. These files are written by the LGWR process in a circular fashion; that is, when the last log file is filled, the first log file is reused. For example, if a database has three redo log files, blocks will be written to file1 until it is filled; then that file is closed, and LGWR begins writing to file2 (this is called a log switch). When file2 is filled, LGWR switches to file3. When file3 is filled, file1 is reused, and so on.

If the database is being operated in archivelog mode, then at the time of a log switch, the ARCH process copies the contents of the log file just filled to the ARCHIVE_LOG_DEST directory, giving the archived log file a unique name using a sequential number. These archived log files may be used during a database recovery to restore transactions made after the last complete backup of the database. If the ARCH process cannot finish copying before Oracle needs to use the log file again, all database activity stops until archiving is finished. Since this can have a significant effect on performance, make sure to create enough log files to prevent this from happening.

Because redo log files are so critical to database recovery, you must protect redo log files from loss due to a hardware failure. There are two ways this can be accomplished. One method is to replace redo log files with mirrored devices; with this method, the hardware or operating system (or both) ensures that redundant copies of the files are written simultaneously. The other method is to use redo log groups, a mechanism provided by Oracle. With redo log groups, the LGWR process writes to each member of the current redo log group at the same time, without incurring a significant amount of operating system overhead or degradation of overall performance. Place members of redo log groups on separate disks in order to avoid loss due to failure of a single disk device and to reduce I/O contention, since redo log files are written to sequentially.

11.1.3. Data Storage Files

The bulk of the storage allocated to any Oracle database is dedicated to (no surprise here) data. Data storage is arguably the most important component of the Oracle database, and it may be the most complex as well.

Oracle differs from other database management systems in many ways: one of the most important differences is the way Oracle allocates and manages data storage. In many systems, storage is maintained by the operating system and allocated to a database as required. In an Oracle database, the DBA allocates one or more blocks of storage to Oracle in the form of one or more operating system files, and Oracle itself then manages the allocation of this storage. In fact, the physical operating system files that make up the database are invisible to database user—only the DBA knows the physical makeup of the database. In order to control storage allocation, Oracle manages the physical disk space by dividing it into Oracle blocks , and uses logical constructs called tablespaces, described in some detail in the next section. Figure 11.1 illustrates the relationships within Oracle's data storage structure.