Oracle Tips by Burleson Consulting
Administer the Archived Redo Logs
An Oracle database can run in one of two modes. By
default, the database is created in NOARCHIVELOG mode. This command will check
to see if you have altered your database to run in ARCHIVELOG mode. See my
notes on how to check if
running in ARCHIVELOG mode.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> archive log list;
select log_mode from v$database;
When in NOARCHIVELOG mode
the database runs normally, but there is no capacity to perform any type of
point in time recovery operations or online backups. Thus, you have to shutdown
the database to back it up, and when you recover the database you can only
recover it to the point of the last backup. While this might be fine for a
development environment, the big corporate types tend to frown when a weeks
worth of current production accounting data is lost forever.
Using the ARCHIVELOG Mode
So, if you wish to avoid the wrath of the CEO and angry
end-users, you will want to run Oracle in ARCHIVELOG mode. In ARCHIVELOG mode,
the database will make copies of all online redo logs after they are filled.
These copies are called archived redo logs. The archived redo logs are created
via the ARCH process. The ARCH process copies the archived redo log files to one
or more archive log destination directories.
The use of ARCHIVELOG mode requires some configuration
of the database. First you must put the database in ARCHIVELOG mode and you must
also configure the ARCH process, and prepare the archived redo log destination
There are some down sides to running the database in
ARCHIVELOG mode. For example, once an online redo log has been filled, it cannot
be reused until it has been archived. If Oracle cannot archive the online redo
log (for example, the destination directory for the archived redo logs is filled
up), it will switch to the next online redo log and keep working. At the same
time, Oracle will continue to try to archive the log file.
Unfortunately, once the database runs out of available
online redo logs, we have a problem. If the log files can not be written
out, then Oracle would have to overwrite them. This is not good because it
means we would lose the data that was in those files since Oracle could not
archive the file. As a result of this, in an effort to protect the
database, Oracle will not overwrite data in an online redo log file until that
log file has been archived. Until the file has been archived, the database
will simply stop processing user requests. Once the log file has been
archived, the database will be freed, and processing can proceed as normal.
You can see how an incorrect configuration of the
database when it is in ARCHIVELOG mode can eventually lead to the database
suspending operations because it can not archive the current online redo logs.
In the next sections we will look at how to configure
the database for ARCHIVELOG mode and how to put the database in ARCHIVELOG mode.
Configuring the database for ARCHIVELOG Mode
One of the main features of a database that is in
ARCHIVELOG mode is that it generates copies of the online redo logs called
archived redo logs. By default in Oracle Database 10g and beyond, Oracle will send archived
redo logs to the flash recovery area and we recommend this configuration.
To properly setup the flash recovery area, you will want
to set two parameters as seen in the following list:
db_recovery_file_dest - ORACLE_BASE/flash_recovery_area
- This is the location of the flash recovery area.
db_recovery_file_dest_size - 2g - This is the maximum
size that can be used by the flash recovery area. If this size limit is
exceeded, you must clear out space or database operations will eventually stall.
Use the alter system command to set these parameters if
you do not want to use the default values. You will find examples of the
use of the alter system command to change parameters earlier in this chapter.
We recommend that the db_recovery_file_dest parameter be set to a directory
location that is separate from the location of the Oracle software, your redo
logs, and your data files. You do not want to accidentally fill up
ORACLE_HOME or cause performance issues due to contention.
When the flash recovery area is configured, a directory
for the database will be created in the location defined by the
db_recovery_file_dest parameter. For example, our database has a directory
Under this directory are individual directories for
various file types such as ARCHIVELOG where the archived redo logs will reside.
In earlier versions of Oracle you had to enable a
special Oracle process called ARCH by setting another parameter. Oracle Database
10g does not require this. When the database is in ARCHIVELOG mode, it will
start the ARCH process automatically.
Putting the database in ARCHIVELOG Mode
Once you have configured the flash recovery area, you
can put the database in ARCHIVELOG mode. Unfortunately, this requires that the
database be shutdown first with the shutdown command (however, from earlier in
the chapter, we note that shutdown immediate is the best option). Once you have
shutdown the database, you will start the database in mount mode with the
startup mount command. Then put the database in ARCHIVELOG mode, and finally
open the database. Here is an example of how this all works from the command
instance shut down.
System Global Area 272629760 bytes
alter database archivelog;
alter database open;
Once the database is in ARCHIVELOG mode, it will start
generating archived redo logs. It's always a good idea to make sure that the
archived redo logs are getting generated. To do this, first force a log switch
with the alter system switch logfile command. Then check the flash recovery area
to make sure an archived redo log is created.
Oracle flash recovery
area re-named to fast recovery area
The archived redo logs will be in the flash recovery
area in the ARCHIVELOG directory. Under that directory you will find individual
directories, each represents a different date such as 2005_03_09 for March 3,
1005. The directory structure on my computer looks like this:
It might look a little different on your computer
(sometimes Oracle does different things on different Operating Systems) but it
should be pretty easy to figure it out.
Now, go to the directory that is named for today?s date.
In my case, I?ll go to the 2005_03_16 directory. Next, we do a directory listing
in that directory and you should see archived redo logs in the directory. Here
is an example of what you will see on your computer:
in drive C has no label.
Serial Number is 50FD-2353
If you are seeing files get generated here, you know
archiving is working all right.
Archived Redo Log Data Dictionary Views
Oracle provides data dictionary views for the archived
redo logs as seen in this list:
about archived redo logs.
- Shows the
location of the flash recovery area where archived redo logs are created.
information on previous redo logs
NOTE: In RAC, a separate set of archive log files is created by each
instance. Since each RAC instance has its own redo log files, the corresponding
archive log files are produced when the log switch takes place. The archive log
files may be written to a local file system or to a cluster file system. Oracle
does not insist upon a particular type of file system. Writing to a clustered
file system has the added advantage of being available to archive all the nodes.
More information is available on RAC archive log files is available
From the Oracle? Database Installation Guide 10g Release 1 (10.1) for UNIX
Systems: AIX-Based Systems, hp HP-UX PA-RISC (64-bit), hp Tru64 UNIX, Linux x86,
and Solaris Operating System (SPARC), the stated benefits include the ability to
install different products with the same release number in the same Oracle base
directory as well as the ability to install the same product more than once in
the same Oracle base directory.
Also, see these related OFA notes:
This is an excerpt from the bestselling "Easy
Oracle Jumpstart" by Robert Freeman and Steve Karam (Oracle ACE and Oracle
Certified Master). It?s only $19.95 when you buy it directly from the
If you like Oracle tuning, you may enjoy the book
Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it directly from the publisher and save 30%, and get
instant access to the code depot of Oracle tuning scripts.