When you create a database, you assign a specific name to it. You cannot change the database name once you have created it, although you can change the name of the instance that accesses the database.
This section covers the different types of files and other components that make up a complete database.
Before you examine the physical files of the actual database, you need to understand a key logical structure within a database, the tablespace. All the data stored in a database must reside in a tablespace.
A tablespace is a logical structure; you cannot look at the operating system and see a tablespace. 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 to create it. Oracle will then find space for it in one of the datafiles that make up the tablespace.
Figure 2-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.
Oracle recommends the use of locally managed tablespaces (introduced in Oracle9i), which avoid some of the overhead of managing the tablespace. Locally managed tablespaces also enable creation of bigfile tablespaces (introduced in Oracle Database 10g), tablespaces that can be up to 8 exabytes in size (an exabyte is equivalent to a million terabytes).[1] Note that by default, whenever the type of extent management is not specified in Oracle Database 10g, permanent tablespaces that are non-SYSTEM are locally managed.
[1] The ultimate size of a bigfile depends on the limitations of the underlying operating system.
A tablespace is a logical view of the physical storage of information in an Oracle database. There are actually three fundamental types of physical files that make up an Oracle database:
Control files
Datafiles
Redo log files
Other files are used within a database environment, such as password files and instance initialization files, but the three fundamental types just listed represent the physical database itself. Figure 2-3 illustrates the three types of files and their interrelationships.
Oracle9i introduced the concept of Oracle Managed Files (OMFs). You can indicate the use of OMFs through an initialization parameter, and your database will automatically create, name, and delete, where appropriate, all the files that make up your database. OMFs are designed to reduce the maintenance overhead of naming and tracking the filenames for your database, as well as avoiding the problems that can result from human errors in performing these tasks. With Oracle Database 10g OMFs, bigfile tablespaces make datafiles appear completely transparent to users.
The next sections describe the roles of these three types of files and their interactions.
The control file contains a list of files that make up the database including datafiles and redo log files. It also contains key information about the contents and state of the database, such as:
The name of the database
When the database was created
Tablespace information
Datafile offline ranges
The log history and current log sequence information
Archived log information
Backup set, pieces, datafile, and redo log information
Datafile copy information
Checkpoint information
|
The size of a control file is influenced by the following initialization parameters, which are part of the initialization file and are set at the time you create a database (see "The Components of an Instance" later in this chapter for a discussion of the initialization file):
MAXLOGfiles
The maximum number of redo log file groups for the database. Redo log files are described later in this chapter in Section 2.2.5.
MAXLOGMEMBERS
The maximum number of members for each redo log file group.
MAXLOGHISTORY
The number of redo log history files the control file can contain. This history is used to simplify automatic recovery, which uses the redo logs by identifying the range of transactions held in an archived redo log.
MAXDATAfiles
The number of datafiles the control file can track. In Oracle7, if you tried to exceed this limit you had to rebuild the control file to increase the number of datafiles that Oracle could track. In more recent Oracle database versions, this parameter determines the amount of space set aside in the control file for datafiles at the time you create the database. If you add more datafiles than specified in the MAXDATAfiles parameter, the control file will expand automatically.
MAXINSTANCES
The amount and number of instances the control file can track. This is relevant for Real Application Clusters (or Oracle Parallel Server prior to Oracle9i), which is discussed in Chapter 6.
For more details about these parameters, please see your Oracle documentation.
In general, it's far simpler to set these MAX values to high levels to avoid encountering any problems later on. It's much easier to set the parameters for a control file a little high initially than to go through the time-consuming process of rebuilding control files when your database grows.
Of more significance, do not limit MAXLOGHISTORY in the interest of saving space. If the control file does not have enough room for redo log history, recovery will be more complex and time-consuming. When you are recovering a database, you generally want to finish the task as quickly as possible. Trying to save a little disk space by minimizing log history entries could result in more time recovering from a disaster.
Redo logs are discussed in more detail later in this chapter.
A database should have at least two control files on different physical disks. Without a current copy of the control file, you run the risk of losing track of portions of your database. Losing control files is not necessarily fatalthere are ways to rebuild them. However, rebuilding control files can be difficult and introduces risk, and can be easily avoided by keeping multiple copies of the control file. You can enable multiple copies of control files by specifying multiple locations for the control files in the CONTROL_files parameter in the initialization file for the instance, as shown here:
CONTROL_files = (/u00/oradata/prod/prodctl1.ctl, /u01/oradata/prod/prodctl2.ctl, /u02/oradata/prod/prodctl3.ctl)
This parameter tells the instance where to find the control files. Oracle will ensure that all copies of the control file are kept in sync so all updates to the control files will occur at the same time.
Many Oracle systems use some type of redundant disk solution such as RAID-1 or RAID-5 to avoid data loss when a disk fails. (RAID, which stands for "redundant array of inexpensive disks," is covered in more detail in Chapter 6.) You might conclude that storing the control file on protected disk storage eliminates the need for maintaining multiple copies of control files and that losing a disk won't mean loss of the control file. But there are two reasons why this is not an appropriate conclusion:
If you lose more than one disk in a given striped array or mirror-pair (these are discussed in more detail in Chapter 6), you will lose the data on those disks. Statistically speaking, losing two disks in a short period of time is unlikely to happen. But this type of disastrous event can occur, and it's possible that at some point you will suffer a failure or series of failures resulting in a damaged or lost control file. Because you will no doubt have your hands full recovering from the multiple disk failures, you should avoid the overhead of rebuilding control files during the recovery process. Multiplexing your control files, even when each copy is on redundant disk storage, provides an additional level of physical security for your Oracle database.
Redundant disk storage does nothing to protect you from the unfortunately perpetual threat of human error. Someone may inadvertently delete or rename a control file, copy another file over it, or move it. A mirrored disk will faithfully mirror these actions, and multiplexed control files will leave you with one or more surviving copies of the control file when one of the copies is damaged or lost.
You do not need to be concerned with the potential performance impact of writing to multiple control files versus one control file. Updates to the control files are insignificant compared to other disk I/O that occurs in an Oracle environment.
Datafiles contain the actual data stored in the database. This data includes the tables and indexes that store data; the data dictionary, which maintains information about these data structures; and the rollback segments, which are used to implement the concurrency scheme you will learn about in Chapter 7.
A datafile is composed of Oracle database blocks that, in turn, are composed of operating system blocks on a disk. Oracle block sizes range from 2 KB to 32 KB.
Prior to Oracle9i, only a single block size could be present in the entire database. In versions of the database since the introduction of Oracle9i, you still set a default block size for the database, but you can also have up to five other block sizes in the database. You can only have a single block size for each tablespace, but you can mix block sizes within a database. Figure 2-4 illustrates the relationship of Oracle blocks to 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 piece you place in any location is a datafile. Tuning the I/O subsystem to improve Oracle performance typically involves moving datafiles from one set of disks to another. Oracle Database 10g's Automated Storage Management (ASM) with automatic striping eliminates manual effort in this tuning task.
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 critical pieces of information in this header is the checkpoint structure. This is 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.
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.
For more information on segments and extents and how they affect performance, refer to Section 5.4. This will be especially important if you are running an older release of Oracle. Oracle Database 10g provides a Segment Advisor that greatly simplifies segment management.
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 of the changes in the redo logs can be used to play back the changes that were lost when the failure occurred, thus protecting transactional consistency.
|
In addition, redo log files are used for "undo" operations when a ROLLBACK statement is issued and uncommitted changes to the database are rolled back to the database image at the last commit. Automatic Undo Management automatically allocates undo (rollback) space among active sessions and first appeared in Oracle9i. Oracle Database 10g automatically determines optimal undo retention time based on system activity maximizing usage of the UNDO tablespace, where undo information is stored to roll away uncommitted transactions. Oracle Database 10g also introduces "flashback" commands that leverage undo information to flash back to a previous state of the database or tables, before tables were dropped, or for specific transaction or row history.
Oracle uses specific terminology to manage redo logs. Each Oracle instance uses a thread of redo to record the changes it makes to the database. A thread of redo is composed of redo log groups, which are composed of one or more redo log members.
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 all-important integrity of the redo log. By creating multiple copies of each redo log file, you protect the redo log file from disk failure and other types of disasters.
Figure 2-5 illustrates a thread of redo with groups and members. The figure shows two members per group, with each redo log mirrored.
When multiple members are in a redo log group, Oracle maintains multiple copies of the redo log files. The same arguments for multiplexing control files apply here. Redo logs are critical to the health and safety of your Oracle database and you should protect them. Simple redundant disk protection is not sufficient for cases in which human error results in the corruption or deletion of a redo log file.
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 to have multiple copies of the redo file.
Oracle writes synchronously to all redo log members. Oracle will wait for confirmation that all copies of the redo log have been successfully updated on disk before the redo write is considered done. If you put one copy on a fast or lightly loaded disk, and one copy on a slower or busier disk, your performance will be constrained by the slower disk. Oracle has to guarantee that all copies of the redo log file have been successfully updated to avoid losing data.
Consider what could happen if Oracle were to write multiple redo logs asynchronously, writing to a primary log and then updating the copies later in the background. A failure occurs that brings the system down and damages the primary log, before Oracle has completed updating all the logs. At this point you have committed transactions that are lostthe primary log that recorded the changes made by the transactions is gone, and the copies of the log are not yet up to date with those changes. To prevent this from occurring, Oracle always waits until all copies of the redo log have been updated.
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 redo log sequence number. This sequence number is recorded inside the redo log files as they are used.
To understand the concepts of redo log filenames and redo log sequence numbers, consider three redo log files called redolog1.log, redolog2.log, and redolog3.log. The first time Oracle uses them the redo log sequence numbers for each will be 1, 2, and 3, respectively. When Oracle returns to the first redo logredolog1.logit will reuse it and assign it a sequence number of 4. When it moves to redolog2.log, it will initialize that file with a sequence number of 5.
Be careful not to confuse these two concepts. The operating system uses the redo log file to identify the physical file, while Oracle uses the redo log file sequence number to determine the order in which the logs were filled and cycled. Because Oracle automatically reuses redo log files, the name of the redo log file is not necessarily indicative of its place in the redo log file sequence.
Figure 2-6 illustrates the filling and cycling of redo logs.
The operating system names for the various files that make up a database are very importantat least to humans, who sometimes have to identify these files by their names (if you are not using Oracle Managed Files). To add meaning and avoid errors, you should use naming conventions that capture the purpose and some critical details about the nature of the file. Here's one possible convention for the names of the actual redo log files shown in Figure 2-6:
redog1m1.log, redog1m2.log, ...
The redo prefix and .log suffixes indicate that this is redo log information. The g1m1 and g1m2 character strings capture the group and member numbers. This convention is only an example; it's best to set conventions that you find meaningful and stick to them.
You may be wondering 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 last backup occurred, so you will simply lose the effects of those changes. Very few Oracle shops make this choice, because the inability to recover to the point of failure is unacceptableit results in lost work.
The second and more practical way to address the issue 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:
Online redo logs
The operating system files that Oracle cycles through to log the changes made to the database
Archived redo logs
Copies of the filled online redo logs made to avoid losing redo data as the online redo logs are overwritten
An Oracle database can run in one of two modes with respect to archiving redo logs:
NOARCHIVELOG
As the name implies, no redo logs are archived. As Oracle cycles through the logs, the filled logs are reinitialized and overwritten, which erases the history of the changes made to the database. This mode essentially has the disadvantage mentioned above, where a failure could lead to unrecoverable data.
Choosing not to archive redo logs significantly reduces your options for database backups, as we'll discuss in Chapter 10, and is not advised by Oracle.
ARCHIVELOG
When Oracle rolls over to a new redo log, it archives the previous redo log. To prevent gaps in the history, a given redo log cannot be reused until it is successfully archived. The archived redo logs, plus the online redo logs, provide a complete history of all changes made to the database. Together, they allow Oracle to recover all committed transactions up to the exact time a failure occurred. Operating in this mode enables tablespace and datafile backups.
The internal sequence numbers discussed earlier act as the guide for Oracle while it is using redo logs and archived redo logs to restore a database.
In Oracle Database 10g, automatic archiving for an Oracle database is enabled with the following SQL statement:
ALTER DATABASE ARCHIVELOG
If the database is in ARCHIVELOG mode, Oracle marks the redo logs for archiving as it fills them. The full log files must be archived before they can be reused.
Prior to Oracle Database 10g, log files marked as ready for archiving did not mean they would be automatically archived. You also needed to set a parameter in the initialization file with the syntax:
LOG_ARCHIVE_START = TRUE
Setting this parameter started a process that is called by Oracle to copy a full redo log to the archive log destination. Oracle Database 10g now automatically does this. This archive log destination and the format for the archived redo log names are specified using two additional parameters, LOG_ARCHIVE_DEST and LOG_ARCHIVE_FORMAT. A setting such as the following:
LOG_ARCHIVE_DEST = C:\ORANT\DATABASE\ARCHIVE
specifies the directory to which Oracle writes the archived redo log files, and:
LOG_ARCHIVE_FORMAT = "ORCL%S.ARC"
specifies the format Oracle will use for the archived redo log filenames. In this case, the filenames must begin with ORCL and will end with .ARC. Oracle expands the %S automatically to the sequence number of the redo log padded with zeros on the left. The other options for the format wildcards are:
%s
Replaced by the sequence number without zero-padding on the left
%T
Replaced by the redo thread number with zero-padding
%t
Replaced by the redo thread number without zero-padding
If you want the archived redo log filenames to include the thread and the sequence numbers with both numbers zero-padded, set:
LOG_ARCHIVE_FORMAT = "ORCL%T%S.ARC"
The initialization file is read every time an Oracle instance is started, so changes to these parameters do not take effect until an instance is stopped and restarted. Remember, though, that turning on automatic archiving does not put the database in ARCHIVELOG mode. Similarly, placing the database in ARCHIVELOG mode does not enable the automatic archiving process.
Prior to Oracle Database 10g, the LOG_ARCHIVE_START parameter defaulted to FALSE. You could see an Oracle instance endlessly waiting because of dissonance between the ARCHIVELOG mode setting and the LOG_ARCHIVE_START setting. If you had turned archive logging on but had not started the automatic process, the Oracle instance would stop because it could not write over an unarchived log file marked as ready for archiving. In other words, the automatic process of archiving the file had not been done. To avoid such problems prior to Oracle Database 10g, you would first modify the initialization parameter and then issue the ALTER DATABASE ARCHIVELOG statement.
In Oracle Database 10g, the ALTER DATABASE ARCHIVELOG statement will, by default, turn on automatic archiving and the archivers are started.
You should also make sure that the archive log destination has enough room for the logs Oracle will automatically write to it. If the archive log file destination is full, Oracle will hang because it can't archive additional redo log files.
Figure 2-7 illustrates redo log use with archiving enabled.
The archived redo logs are critical for database recovery. Just as you can duplex the online redo logs, you can also specify multiple archive log destinations. Oracle will copy filled redo logs to specified destinations. You can also specify whether all copies must succeed or not. The initialization parameters for this functionality are as follows:
Specifies an additional location for redundant redo logs
Indicates whether the redo log must be successfully written to one or all of the locations
Starting with Oracle8i, you could specify up to five mandatory or optional archive log destinations, including remote systems for use in disaster recovery situations (covered in Chapter 10). Oracle8i also introduced automated support for multiple archiving processes to support the additional load of archiving to multiple destinations. See your Oracle documentation for the additional parameters and views that enable and control this functionality.