Oracle Essentials Oracle Database 11g (11 page)

Logically, youcan think of a redo log group as a single redo log file. However, Oracle allows youto 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.

Group 1

member 1

=

member 2

Group 2

member 1

=

member 2

Group 3

member 1

=

member 2

Members in a group are identical

Figure 2-5. A thread of redo

Deploying Physical Components

|

43

When multiple members are in a redo log group, Oracle maintains multiple copies of the redo log files. The same arguments used for multiplexing of control files apply here. However, though you can rebuild the static part of a control file if you lose it, there is no way to reproduce a lost redo log file. So, be sure to have multiple copies of the redo file. Simple redundant disk protection is not sufficient for cases in which human error results in the corruption or deletion of a redo log file.

Oracle writes
synchronously
to all redo log members. Oracle will wait for confirma-tion that all copies of the redo log have been successfully updated on disk before the redo write is considered done. If youput 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.

If a failure occurs that brings the system down and damages the primary log, Oracle might not have completed updating all the logs. At this point you have committed transactions that are lost—the 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.

How Oracle uses the redo logs

Once Oracle fills one redo log file, it automatically begins to use the next log file.

When 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 log—
redolog1.log—
it 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.

Remember that 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.

44

|

Chapter 2: Oracle Architecture

Group 1

Seq# =1

Seq# =4

redog1m1.log

=

redog1m2.log

Group 2

Seq# =2

redog2m1.log

=

redog2m2.log

Group 3

redog3m1.log

=

redog3m2.log

Seq# =3

Sequence number advances as logs fill and cycle

Figure 2-6. Cycling redo logs

Naming conventions for redo logs

The operating system names for the various files that make up a database are very important—at least to humans, who sometimes have to identify these files by their names. If you are not using Oracle Managed Files, 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 youfind meaningful and stick to them.

Archived redo logs

Youmay be wondering how to avoid losing the critical information in the redo log when Oracle cycles over a previously used redo log.

Deploying Physical Components

|

45

There are actually two ways to address this. 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 is overwritten. If a failure occurs that damages the datafiles, youmust restore the entire database to the point in time when the last backup occurred. Since no redo log history exists to reproduce the changes made since the last backup occurred, you will lose the effects of those changes. Very few Oracle shops make this choice, because the inability to recover to the point of failure is unacceptable—it 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 data-

base backups, as we’ll discuss in Chapter 11, 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.

ARCHIVELOG mode and automatic archiving

Starting with Oracle Database 10
g
, automatic archiving for an Oracle database is enabled with the following SQL command:

ALTER DATABASE ARCHIVELOG

46

|

Chapter 2: Oracle Architecture

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. The ALTER DATABASE ARCHIVELOG command will by default turn on automatic archiving and the archivers are started.

Prior to Oracle Database 10
g
, 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 an Oracle process to copy a full redo log to the archive log destination.

The 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%t_%s_%r.arc

defines the format Oracle will use for the archived redo log filenames. In this case, the filenames will begin with ORCL and will end with .arc. The parameters for the format wildcards are:

%t

Include thread number as part of the filename

%s

Include log sequence number as part of the filename

%r

Include resetlogs ID as part of the filename

If you want the archived redo log filenames to include the thread number, log sequence number and resetlogs ID with the numbers zero-padded, capitalize the parameters and set:

LOG_ARCHIVE_FORMAT = "ORCL%T_%S_%R.arc"

Since the initialization file is read when an Oracle instance is started, 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.

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 since it can’t archive additional redo log files.

Deploying Physical Components

|

47

Figure 2-7 illustrates redo log use with archiving enabled.

Archived Redo Logs

Group 1

Seq#=1

Seq#=4

ORCL0000000001.ARC

redog1m1.log

=

redog1m2.log

Group 2

Seq#=2

ORCL0000000002.ARC

redog2m1.log

=

redog2m2.log

Group 3

Seq#=3

ORCL0000000003.ARC

redog3m1.log

=

redog3m2.log

Logs are archived as they fill and Oracle switches to a new log
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, youcan also specify multiple archive log destinations. Oracle will copy filled redo logs to specified destinations. Youcan 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. Valid values are 1 through 10 if multiplexing and 1 or 2 if duplexing.

See your Oracle documentation for the additional parameters and views that enable and control this functionality.

Instance Memory and Processes

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 that we’ll examine in the next section. All the processes of an instance—system processes and user processes—share the SGA.

48

|

Chapter 2: Oracle Architecture

Prior to Oracle9
i
, the size of the SGA was set when the Oracle instance was started.

The only way to change the size of the SGA or any of its components was to change the initialization parameter and then stop and restart the instance. Since Oracle9
i
, youcan also change the size of the SGA or its components while the Oracle instance is running. Oracle9
i
also introduced the concept of the
granule
, which is the smallest amount of memory that you can add to or subtract from the SGA.

Oracle Database 10
g
introduced Automatic Shared Memory Management, while Oracle Database 11
g
added Automatic Memory Management for the SGA and PGA instance components. Whenever the MEMORY_TARGET (new to Oracle Database 11
g
) or SGA_TARGET initialization parameter is set, the database automatically distributes the memory among various SGA components providing optimal memory management. The shared memory components automatically sized include the shared pool (manually set using SHARED_POOL_SIZE), the large pool (LARGE_

POOL_SIZE), the Java pool (JAVA_POOL_SIZE), the buffer cache (DB_CACHE_

SIZE), and the streams pool (STREAMS_POOL_SIZE). Automatic memory management initialization parameters can be set through Oracle Enterprise Manager.

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.

Figure 2-8
illustrates the memory structures and background processes discussed in the following section.