Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

2.2 The Components of a Database

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.

2.2.1 Tablespaces

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.

Figure 2-2. Tablespaces and datafiles

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.

2.2.2 Physical Files in an Oracle Database

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.

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

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.

2.2.3 Control Files

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

Oracle Database 10g introduces a new DROP DATABASE command that can be used to delete a database and all of the files listed in the database control file.

2.2.3.1 Control file parameters

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.

2.2.3.2 Multiple control files

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.

2.2.4 Datafiles

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.

Figure 2-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 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.

2.2.4.1 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 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.

2.2.4.2 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.

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.

2.2.5 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
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.

These files are sometimes confused with rollback buffers supporting concurrency and described in Chapter 7. They are not the same!

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.

2.2.5.1 Multiplexing redo log files

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.

Figure 2-5. A thread of redo

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.

2.2.5.2 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 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.

Figure 2-6. Cycling redo logs

2.2.5.3 Naming conventions for 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.

2.2.5.4 Archived redo logs

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.

2.2.5.5 ARCHIVELOG mode and automatic archiving

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.

Figure 2-7. Cycling redo logs with archiving

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:

LOG_ARCHIVE_DUPLEX_DEST

Specifies an additional location for redundant redo logs

LOG_ARCHIVE_MIN_SUCCEED_DEST

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.