Oracle Instances and Databases

Two entities are sometimes referred to as an Oracle database—the instance and the database—and people often confuse them.

In the Oracle world, the term database refers to the physical storage of information, while instance refers to the software executing on the server that provides access to the information in the database. The instance runs on the computer or server; the database is stored on the disks attached to the server, as shown in Figure 1-1.

Figure 1-1. An instance and a database

The database is physical: it consists of files stored on disks. The instance is logical: it consists of in-memory structures and processes on the server. An instance can connect to one and only one database. Instances are temporal, but databases, with proper maintenance, last forever.

Users do not directly access the information in an Oracle database. Instead, they pass requests for information to an Oracle instance.

The Components of a Database

A database consists of a collection of physical files and logical structures, described in the following sections.

A database has a specific name, assigned when you create it. You cannot change the name of a database once you have created it, although you can change the name of the instance that accesses the database.

Tablespaces

A tablespace is a logical structure, which exists only within the context of an Oracle database. Each tablespace is composed of physical structures called datafiles; each tablespace must consist of one or more datafiles, and each datafile can belong to only one tablespace. When you create a table, you can specify the tablespace in which you want to create it. Oracle will then find space for it in one of the datafiles that make up the tablespace.

Figure 1-2 shows the relationship of tablespaces to datafiles for a database. This figure shows two tablespaces within an Oracle database. When you create a new table in this Oracle database, you may place it in the DATA1 tablespace or the DATA2 tablespace. It will physically reside in one of the datafiles that make up the specified tablespace.

Figure 1-2. Tablespaces and datafiles

Physical Files in an Oracle Database

A tablespace is a logical view of the physical storage of information in an Oracle database. Three fundamental types of physical files make up an Oracle database:

Other files, such as password files and instance initialization files, are used within a database environment, but the three fundamental types listed represent the physical database itself. Figure 1-3 illustrates the three types of files and their interrelationships.

Figure 1-3. The files that make up a database

Oracle9i introduces the concept of Oracle managed files (OMFs). You indicate that you want to use OMFs by specifying values for the initialization parameters DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n (these and all of the Oracle initialization parameters are described in detail in Chapter 2). If you request OMFs, Oracle9i will automatically create, name, and delete (when appropriate) all the files for your Oracle database. OMFs are designed to reduce the maintenance overhead of naming and tracking the names for your Oracle database, as well as to avoid the problems that can occur when fallible human beings do not correctly identify a file in an Oracle database.

The following sections describe the role of these three types of files and their interactions.

Control Files

The control file contains a list of all the other files that make up the database, such as the datafiles and redo log files. It also contains key information about the contents and state of the database, such as:

Prior to Oracle8, control files were typically under a megabyte in size. With Oracle8, there is more information in the control file, such as the details of database backups. The control files in Oracle8 and beyond can easily grow to the 10 MB range or beyond. The size of a control file is influenced by a number of initialization parameters, including MAXLOGFILES, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES.

You can have your Oracle instance maintain multiple copies of control files. Although you can potentially rebuild a control file if it is damaged or deleted, this process takes time and in some scenarios you cannot rebuild the control file to its correct state. You cannot run an Oracle database without a control file, so having multiple copies of your control file can be an important safety option. You use the initialization parameter CONTROL_FILES to list the locations of multiple copies of the control file.

Datafiles

Datafiles contain the actual data stored in the database. This data includes the tables and indexes created by users of the database; the data dictionary, which keeps information about these data structures; and the rollback segments, which are used to implement Oracle's consistency scheme.

A datafile is composed of Oracle database blocks that are, in turn, composed of operating system blocks on a disk. Oracle block sizes range from 2K to 32K. If you're using Oracle with very large memory (VLM) support, you may use big Oracle blocks (BOBs), which can be as large as 64K in size.

Prior to Oracle9i, you could have only a single block size for an entire database. With Oracle9i, you still set a default block size for the database, but you can also have five different nonstandard block sizes in the database. Each datafile can support only one block size, but you can have mixed block sizes within the database.

Figure 1-4 illustrates the relationship of Oracle blocks to operating system blocks.

Figure 1-4. Oracle blocks and operating system blocks

Datafiles belong to only one database and to only one tablespace within that database. Data is read in units of Oracle blocks from the datafiles into memory as needed, based on the work users are doing. Blocks of data are written from memory to the datafiles stored on disk, as needed, to ensure that the database reliably records changes made by users.

Datafiles are the lowest level of granularity between an Oracle database and the operating system. When you lay a database out on the I/O subsystem, the smallest physical piece you place in any particular location is a datafile. Tuning the I/O subsystem to improve Oracle performance typically involves moving datafiles from one set of disks to another.

Datafile structure

The first block of each datafile is called the datafile header. It contains critical information used to maintain the overall integrity of the database. One of the most crucial pieces of information in this header is the checkpoint structure, a logical timestamp that indicates the last point at which changes were written to the datafile. This timestamp is critical for recovery situations. The Oracle recovery process uses the timestamp in the header of a datafile to determine which redo logs to apply to bring the datafile up to the current point in time.

Extents and segments

From a physical point of view, a datafile is stored as operating system blocks. From a logical point of view, datafiles have three intermediate organizational levels: data blocks, extents, and segments. An extent is a set of data blocks that are contiguous within an Oracle datafile. A segment is an object that takes up space in an Oracle database, such as a table or an index, that is comprised of one or more extents.

When Oracle updates data, it attempts to update the data in the same data block. If there is not enough room in the data block for the new information, Oracle will write the data to a new data block, which may be in a different extent.

Redo Log Files

Redo log files store a "recording" of the changes made to the database as a result of transactions and internal Oracle activities. In its normal operations, Oracle caches changed blocks in memory; in the event of an instance failure, some of the changed blocks may not have been written out to the datafiles. The recording stored in the redo log can be used to play back the changes that were lost when the failure occurred.

Multiplexing redo log files

Oracle uses specific terminology in describing the management of redo logs. Each Oracle instance records the changes it makes to the database in redo logs. You can have one or more redo logs, referred to as redo log members, in a redo log group.

Logically, you can think of a redo log group as a single redo log file. However, Oracle allows you to specify multiple copies of a redo log to protect the log against media failure. Multiple copies of the same log are grouped together in a redo log group. All redo log groups for an instance are referred to as a redo thread.

There are ways you can rebuild the static part of the control file if you lose it, but there is no way to reproduce a lost redo log file; be sure that you have multiple copies of the redo file.

How Oracle uses the redo logs

Once Oracle fills one redo log file, it automatically begins to use the next log file. Once the server cycles through all the available redo log files, it returns to the first one and reuses it. Oracle keeps track of the different redo logs by using a sequence number. As the server fills each redo log file and moves on to the next one, it increments an internal counter called the redo log sequence number. This sequence number is recorded inside the redo log files as they are used. Oracle uses this internal number to properly sequence the logs, even though a reused log file may have the name initially created for an earlier redo log.

Archived redo logs

While reading the previous explanation, you might have wondered how to avoid losing the critical information in the redo log when Oracle cycles over a previously used redo log.

There are actually two ways to address this issue. The first is quite simple: you don't avoid losing the information, and you suffer the consequences in the event of a failure. You will lose the history stored in the redo file when it's overwritten. If a failure occurs that damages the datafiles, you must restore the entire database to the point in time when the last backup occurred. No redo log history exists to reproduce the changes made since the time the last backup occurred, so you will be out of luck. Very few Oracle shops make this choice, because the inability to recover to the point of failure is unacceptable—it results in lost data.

The second and more practical way to address the issue caused by recycling redo logs is to archive the redo logs as they fill. To understand archiving redo logs, you must first understand that there are actually two types of redo logs for Oracle:

The operating system files that Oracle cycles through to log the changes made to the database

An Oracle database can run in one of two modes with respect to archiving redo logs:

NOARCHIVELOG

ARCHIVELOG

To enable ARCHIVELOG mode, you must turn on archive logging with the ALTER DATABASE ARCHIVELOG command in SQL*Plus and set the LOG_ARCHIVE_START initialization parameter to TRUE. This will start archiving logs to the location specified by the LOG_ARCHIVE_DEST parameters with names specified by the LOG_ARCHIVE_FORMAT parameter.

1.3 The Components of an Instance

An Oracle instance can be defined as an area of shared memory and a collection of background processes.

The area of shared memory for an instance is called the System Global Area, or SGA. The SGA is not really one large undifferentiated section of memory—it's made up of various components described in the next section, "Memory Structures for an Instance." All the processes of an instance—system processes and user processes—share the SGA.

Prior to Oracle9i, the size of the SGA was set when the Oracle instance started. The only way you could change the size of the SGA or any of its components was to change the appropriate initialization parameters and stop and restart the instance. With Oracle9i, you can now change the size of the SGA or its components while the Oracle instance is still running.

The background processes interact with the operating system and each other to manage the memory structures for the instance. These processes also manage the actual database on disk and perform general housekeeping for the instance.

Other physical files can be considered as part of the instance as well:

 

Instance initialization file

 

The initialization file contains a variety of parameters that configure how the instance will operate: how much memory it will use, how many users it will allow to connect, what database the instance actually provides access to, and so on. You can alter many of these parameters dynamically at either the systemwide or session-specific level. Up until Oracle9i, the initialization file was called INIT.ORA.^[1] Oracle9i introduced the SPFILE, which performs the same function as the INIT.ORA file but can also persistently store changes to initialization parameters that are made while Oracle9i is running. Refer to your operating system-specific documentation for the default location of the INIT.ORA file on your system.

^[1] The specific name of your INIT.ORA file will depend upon your instance name. See Chapter 2 for details.

 

Instance configuration file

 

The configuration file, called CONFIG.ORA, is an optional parameter file, included if you want to segregate a set of initialization parameters (for example, those used for Oracle Parallel Server or Real Application Clusters).

 

Password file

 

Oracle can use an optional password file, stored as an operating system file, to provide additional flexibility for managing Oracle databases. This file is encrypted and contains user IDs and passwords that can be used to perform administrative tasks, such as starting and stopping the instance. Use of a password file is a standard method for implementing remote access security in addition to access security by operating system. The latter is typically used locally (i.e., on the database server). For example, on a Unix system, any user in the DBA group can start up or shut down Oracle—the operating system group gives that user the authority. Validating a password against the value stored in the database for a user is not possible when the database is not open. The password file forces a user to authenticate himself with a password in order to start up the database.

Additional background processes may exist when you use certain other features of the database: for example, the Shared Server/Multi-Threaded Server (MTS), job queues, or replication.

1.3.1 Memory Structures for an Instance

The SGA is actually composed of four main areas: the database buffer cache, the shared pool, the redo log buffer, and the large pool, as shown in Figure 1-5.

Figure 1-5. An Oracle instance

1.3.1.1 Database buffer cache

The database buffer cache caches blocks of data retrieved from the database. This buffer between the users' requests and the actual datafiles improves the performance of the Oracle database. If a piece of data can be found in the buffer cache, you can retrieve it from memory without the overhead of having to go to disk. Oracle manages the cache using a least recently used (LRU) algorithm. This means that if a user requests data that has been recently used, the data is more likely to be in the database buffer cache and can be delivered immediately without having to execute a disk read operation.

Oracle7 had one pool of buffers for database blocks. Oracle8 introduced multiple buffer pools. Three pools are available in Oracle8 and beyond:

 

DEFAULT

 

The standard Oracle database buffer cache. All objects use this cache unless otherwise indicated.

 

KEEP

 

For frequently used objects you wish to cache.

 

RECYCLE

 

For objects that you're less likely to access again.

Both the KEEP and the RECYCLE buffer pools remove their objects from consideration by the LRU algorithm.

You can mark a table or index for caching in a specific buffer pool. This helps to keep more desirable objects in the cache and avoids the "churn" of all objects fighting for space in one central cache.

1.3.1.2 Shared pool

The shared pool caches various constructs that can be shared among users. For example, SQL statements issued by users are cached so that they can be reused if the same statement is submitted again. Another example is stored procedures— pieces of code stored and executed within the database. These are loaded into the shared pool for execution and then cached, again using an LRU algorithm. The shared pool is also used for caching information from the Oracle data dictionary, which is the metadata (or data describing data structures) that describes the structure and content of the database itself.

1.3.1.3 Redo log buffer

The redo log buffer caches redo information until it is written to the physical redo log files stored on a disk. Use of this buffer improves performance. Oracle caches the redo until it can be written to disk at a more optimal time, which avoids the overhead of constantly writing to the disk with the redo logs.

1.3.1.4 Large pool

The large pool, introduced with Oracle8, is an optional area of the SGA used for buffering I/O for various server processes, including those used for backup and recovery. The area is also used to store session memory for the Multi-Threaded Server and when using the XA protocol for distributed transactions.

1.3.2 Background Processes for an Instance

The background processes shown in Figure 1-5 are:

 

Database Writer (DBWR)

 

This process writes database blocks from the database buffer cache in the SGA to the datafiles on disk. An Oracle instance can have up to 10 DBWR processes, named DBW0 through DBW9, if needed, to handle the I/O load to multiple datafiles. Most instances run one DBWR. DBWR writes blocks out of the cache for two main reasons:

 

Log Writer (LGWR)

 

This process writes the redo information from the log buffer in the SGA to all copies of the current redo log file on disk. As transactions proceed, the associated redo information is stored in the redo log buffer in the SGA. When a transaction is committed, Oracle makes the redo information permanent by invoking the Log Writer to write it to disk.

 

System Monitor (SMON)

 

This process maintains overall health and safety for an Oracle instance. SMON performs crash recovery when the instance is started after a failure. SMON coordinates and performs recovery for a failed instance when you have more than one instance accessing the same database, as with Oracle Parallel Server/Real Application Clusters. SMON also cleans up adjacent pieces of free space in the datafiles by merging them into one piece, and it gets rid of space used for sorting rows when that space is no longer needed.

 

Process Monitor (PMON)

 

This process watches over the user processes that access the database. If a user process terminates abnormally, PMON is responsible for cleaning up any of the resources left behind (such as memory) and for releasing any locks held by the failed process.

 

Archiver (ARCH)

 

This process reads the redo log files once Oracle has filled them and writes a copy of the used redo log files to the specified archive log destination(s).Oracle8i and beyond support up to 10 ARCH processes, named ARC0 through ARC9. LGWR will start additional archivers as needed, based on the load, up to the limit specified by the initialization parameter LOG_ARCHIVE_MAX_PROCESSES.

 

Checkpoint (CKPT)

 

This process works with DBWR to perform checkpoints. CKPT updates the control file and database file headers to update the checkpoint data when the checkpoint is complete.

 

Recover (RECO)

 

This process automatically cleans up failed or suspended distributed transactions.

1.4 Oracle Versions

Oracle version numbers, like the product packaging we discuss in the next section, have more to do with the marketplace than the technology. The Oracle database, like all software products, has a periodic release cycle, but the exact timing and contents of those releases is shaped, to some degree, by sales requirements as well as the development cycle. Because of nontechnical factors, the naming conventions for versions can change at any time.

As of this writing, Oracle seems to have settled on a consistent version naming scheme. Up until Oracle8, each major version was named with an increasing version number (e.g., Oracle6, Oracle7, Oracle8). However, the release after Oracle8 was named Oracle8i, connecting it with the Internet (and causing formatting problems for authors ever since!). The next version was named Oracle9i, and the upcoming version is slated to be called Oracle10i—although that may change by the time the version is released.

Typically, the OracleNi versions have had an intermediate release, also typically identified as Release 2. Release 2s generally are the first major maintenance release; they follow the main release and frequently contain enhancements that were planned for the main release but that could not make the deadline.

This book assumes that Oracle8 was the beginning of time. Any features that were not present in that version are not discussed. Any features that were added or dropped since then are duly noted.

1.5 Oracle Packaging

Even more so than versions, the actual packaging of Oracle releases is controlled primarily by sales forces, rather than by technical forces. The decision to include a feature in one or more versions of the database, as well as the decision to segregate functionality in an extra-cost option, is, for the most part, arbitrary.

1.5.1 Editions

There are four basic editions of the Oracle database:

 

Standard Edition

 

The lowest cost server edition of Oracle, which does not include all the capabilities of the Oracle database. The extra options described in the next section cannot be used with Standard Edition.

 

Enterprise Edition

 

The complete Oracle database, which is required for the use of the extra options described in the next section.

 

Personal Edition

 

A single-user version of the Oracle database, available only for Windows. This edition includes all the functionality of all the options for Enterprise Edition, where appropriate.

 

Lite

 

A trimmed-down version of the Oracle database designed for mobile use.

Although Oracle sometimes decides to fold formerly extra-cost options into one or more editions of the standard product, the company has never yet decided to take functionality out of a lesser edition in order to force upgrades.

Most of the functionality described in this book is included in all versions of the database. The following functionality is available in Enterprise Edition, but not Standard Edition, as of the time of this writing:

 

Data Guard

 

Provides a set of routines and a management tool that make it easier to implement standby databases.

 

Fast-start recovery

 

Implements a method of recovery that allows the database to be opened as soon as the rollback is applied from the log files.

 

Online index build and coalesce

 

Allows index build and coalesce operations to be performed without requiring the index to be taken offline.

 

Online table reorganization and redefinition

 

Allows table reorganization and redefinition operations to be performed without requiring the index to be taken offline.

 

Block-level media recovery

 

Enables the recovery of specific blocks on the media, reducing the need to recover an entire file.

 

Incremental backup and recovery

 

Allows the backup and recovery of data that has been changed since the time of the last full or incremental backup; this approach can significantly reduce the time for these operations.

 

Parallel backup and recovery

 

Allows backup and recovery operations that use parallel execution, which can significantly reduce the time for these operations.

 

Tablespace point-in-time recovery

 

Gives you the ability to recover a tablespace to a particular point in time; this feature can be used to recover data from a damaged log file or to recover to a point in time just before a data error occurred.

 

Trial recovery

 

Lets you test the validity of a backup without having to run the entire process.

 

Virtual Private Database (VPD)

 

Gives you the ability to define security on a row based on the outcome of a security procedure. This capability allows you to grant access to a row based on the value of the data in the row, among other implementation strategies.

 

Fine-grained auditing

 

Allows auditing to be performed based on the content of a row.

 

Database Resource Manager (DRM)

 

Allows you to limit the amount of database resources allocated to a single user or group of users.

 

Bitmapped index and bitmapped join index

 

Supports additional types of indexes that can speed the performance of data warehouse-type queries.

 

Automated parallel query degree

 

Allows Oracle to choose the best degree of parallelism for a particular query.

 

Parallel operations

 

Allow parallel operations (e.g., query, load, analyze, bitmap star query optimization, DML, index build, and index scan) to run faster, because they can take advantage of multiple processes.

 

Export of transportable tablespaces

 

Supports transportable tablespaces, which allow you to move a tablespace as easily as copying a file. This approach can be more efficient than an import/export process.

 

Advanced replication

 

Allows the replication of changed data from one database instance to another. This feature allows you to build custom conflict resolution methods.

1.5.2 Options

Oracle provides a number of options that can be purchased with Oracle Enterprise Edition. As with the packaging in general, the content, price, and availability of these options are subject to change.

At the time of this writing (with Oracle9i Release 2), the following options are available:

 

Real Application Clusters

Partitioning

OLAP

Data Mining

Spatial

Advanced Security

Label Security