[Laskey99] Chapter 11. The Oracle Database

Chapter 11.
InChapter 10, we introduced the Oracle instance,and explained that the terms instance anddatabase are often used interchangeably. Thischapter provides information about the physical structures that makeup an Oracle database, which is accessed by means of the Oracleinstance.
11.1. Types of Database Files
The Oracle database consists of anumber of physical files, often referred to as operatingsystem files, since they are usually created andmaintained by the host operating system. These physical files areused by Oracle for parameter storage, database coordination, and datastorage. The files shown in the following list, which are describedin the following sections, are unique to a particular database; thatis, each mounted and opened database must have all of these files,and the files are not shared across instances except in the specialcase of the Oracle Parallel Server, whose purpose is to sharedatafiles.
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 areused by Oracle: the INIT.ORA file and theoptional CONFIG.ORA file. These files, describedin the following sections, collectively contain information providedby the DBA to configure and tune a particular Oracle instance.
11.1.1.1. Initialization file
The initialization file, usually referred to asthe INIT.ORA file, is the primary file thatcontains configuration and tuning parameters. For detailedinformation on these parameters, seeChapter 12. AnINIT.ORA file must exist for each Oracleinstance. This file is used by Oracle when starting the database, andtherefore must be located in a known location, or Oracle must be toldwhere it is located through the use of a command-line parameter toServer Manager. In Unix systems, this file is found in the$ORACLE_HOME/dbs directory.
The INIT.ORA file is one of only two Oraclefiles (the other is the CONFIG.ORA file) thatcan be directly read and manipulated by a user—in this case, bythe DBA. The INIT.ORA file is stored as plaintext—ASCII on most systems—and is typically edited by theDBA, using a text editor. The file format is straightforward: itconsists of multiple lines, each of which specifies a parameter inthe 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 theINIT.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 controlcharacters other than the newline character. Do not edit theINIT.ORA file with a word processing program,since these programs usually store extra control characters that willprevent the INIT.ORA file from being readproperly by Oracle. Note that any error in theINIT.ORA file will prevent the database frombeing 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 asthe CONFIG.ORA file, is an optional file thatcontains parameters, just like the INIT.ORAfile. In fact, the CONFIG.ORA file is a subsetof the INIT.ORA file, and can only be used ifthe INIT.ORA file contains an"include" line specifying the name of theCONFIG.ORA file. Since theCONFIG.ORA file is actually merged into theINIT.ORA file prior to processing by Oracle, allof the same syntax rules apply.
The CONFIG.ORA file is primarily used tosegregate a particular set of standard initialization parameters. Forexample, when running the Oracle Parallel Server, there are manyinitialization parameters that must be set identically for eachinstance, and these are stored in a separateCONFIG.ORA file. Similarly, several Oracleinstances running on the same host may share a common subset ofparameters that is stored in a single CONFIG.ORAfile, while each instance also has a set of specific initializationparameters 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 databasecoordination files must exist for every Oracle instance: controlfiles and redo log files. These files are critical to the operationof Oracle, and the loss of or damage to either file could havecatastrophic effects on the database.
11.1.2.1. Control files
Every Oracle instance must have one ormore control files. The control file is a binary file that iscritical to Oracle, but is not directly readable by a user, nor is iteditable by a text editor. The control file can be thought of as asoftware "bootstrap" file; it contains information thatOracle requires to start. Information stored in the control fileincludes:
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 thecontrol file is lost or damaged, the only options available forrecovery are either to create a new control file (assuming that theDBA has access to all pertinent information required) or to rebuildthe database and restore from a backup. Because of the criticalnature of the control file, Oracle allows the DBA to maintainmultiple mirrored control files, as specified by theCONTROL_FILES parameter in theINIT.ORA file. For example, the following linefrom 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 humanreadable, and is used only by Oracle itself, we recommend that, foroperating systems with file protection, the control file be madereadable only by the Oracle owner. In Unix, control files should beowned by Oracle, be assigned to group DBA, and have a protection of600, which gives read/write access to the owner, but no access to thegroup or world.
Although the control file is in a binary format and is readable onlyby Oracle, a method is provided to create a script containing SQLstatements that can be used to recreate a control file. This textversion may be edited and used to create a new control file withmodified values.
To create a text version of the control file, the DBA may use thiscommand:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE NORESETLOGS;
After execution of this command, a trace file will be created in adirectory specified by the INIT.ORA parameterBACKGROUND_DUMP_DEST; by default, thiswill usually be $ORACLE_HOME/rdbms/log. You mustgo to the directory containing trace files and look for a file withan extension of .trc and a date/time stamp atthe time you executed the ALTER DATABASE command. This trace filewill be similar to the one shown in the following sample, which wascreated with Oracle Version 7.3.4 on an HP platform, and will containthe SQL statements required to create a new control file and restartthe 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 beedited (carefully!) by the DBA. You might change the value of one ofthe configuration parameters (MAXDATAFILES, for example), or perhapschange 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 adatabase that cannot be opened, we highly recommend that you back upthe database prior to creating a new control file.
11.1.2.2. Redo log files
Redo log files are operating system files usedby Oracle to maintain logs of all transactions performed against thedatabase. The primary purpose of these log files is to allow Oracleto recover changes made to the database in the case of a failure.
An Oracle database must have at least two redo log files, and mostdatabases have more than two. These files are written by theLGWR process in a circular fashion; thatis, 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 bewritten to file1 until it is filled; then that file is closed, andLGWR begins writing to file2 (this is called a logswitch). 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 logswitch, the ARCH process copies thecontents of the log file just filled to the ARCHIVE_LOG_DESTdirectory, giving the archived log file a unique name using asequential number. These archived log files may be used during adatabase recovery to restore transactions made after the lastcomplete backup of the database. If the ARCH process cannot finishcopying before Oracle needs to use the log file again, all databaseactivity stops until archiving is finished. Since this can have asignificant effect on performance, make sure to create enough logfiles to prevent this from happening.
Because redo log files are socritical to database recovery, you must protect redo log files fromloss due to a hardware failure. There are two ways this can beaccomplished. One method is to replace redo log files with mirroreddevices; with this method, the hardware or operating system (or both)ensures that redundant copies of the files are writtensimultaneously. The other method is to use redo log groups, amechanism provided by Oracle. With redo log groups, the LGWR processwrites to each member of the current redo log group at the same time,without incurring a significant amount of operating system overheador degradation of overall performance. Place members of redo loggroups on separate disks in order to avoid loss due to failure of asingle disk device and to reduce I/O contention, since redo log filesare written to sequentially.
11.1.3. Data Storage Files
The bulk of the storage allocated to anyOracle database is dedicated to (no surprise here) data. Data storageis arguably the most important component of the Oracle database, andit 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 andmanages data storage. In many systems, storage is maintained by theoperating system and allocated to a database as required. In anOracle database, the DBA allocates one or more blocks of storage toOracle in the form of one or more operating system files, and Oracleitself then manages the allocation of this storage. In fact, thephysical operating system files that make up the database areinvisible to database user—only the DBA knows the physicalmakeup of the database. In order to control storage allocation,Oracle manages the physical disk space by dividing it intoOracleblocks,and uses logical constructs called tablespaces,described in some detail in the next section.Figure 11.1 illustrates the relationships withinOracle's data storage structure.
Figure 11.1. Data storage components of an Oracle database


Create Bookmark (Key: b)Create Bookmark
Create Note or Tag (Key: t)Create Note or Tag
Download (Key: d)Download
Email This Page (Key: e)Email This Page
PrintPrint
Html View (Key: h)Html View
Zoom Out (Key: -)Zoom Out
Zoom In (Key: +)Zoom In
Toggle to Full Screen (Key: f)
Previous (Key: p)Previous
Next (Key: n)Next
Related Content

The Oracle Data Provider
From: ADO.NET Programming in Visual Basic™ .NET
Oracle CREATEs
From: Practical SQL Handbook, The: Using SQL Variants, Fourth Edition
Oracle
From: Practical SQL Handbook, The: Using SQL Variants, Fourth Edition
Oracle databases
From: Database Performance Tuning on AIX
Oracle Performance
From: Oracle Essentials, 4th Edition
Oracle Database Examples
From: Beginning Oracle Database 11g Administration: From Novice to Professional
Oracle Database Objects
From: Oracle Security
Oracle
From: Introduction to the New Mainframe: Security
Oracle and Security
From: Oracle Security
Installing Oracle and Creating a Database
From: Oracle Database Foundations