|
I have had to deal with more archiving, backup,
and recovery issues this month than possibly the entire past year. So it
seems like that would be a good series over the next few weeks.
I would like to start with putting a
database into archivelog mode. The Oracle definition of archivelog mode,
from the
Oracle9i Database Master Glossary, is
The mode of the database in which Oracle copies
filled online redo logs to disk. Specify the mode at database
creation or by using the ALTER DATABASE
statement. You can enable automatic archiving either dynamically
using the ALTER SYSTEM statement or by
setting the initialization parameter LOG_ARCHIVE_START
to TRUE.
Running the database in ARCHIVELOG
mode has several advantages over NOARCHIVELOG mode. You
can:
- Back up the database while it is
open and being accessed by users.
- Recover the database to any
desired point in time.
To protect the ARCHIVELOG mode
database in case of failure, back up the archived logs.
Putting a database in archivelog mode requires making sure
three initialization parameters are set properly and issuing one command.
(It can be more complex than this, but I am only addressing the minimum
necessary requirements to enter archivelog mode.) Assuming the database is
not in archivelog mode, you need to tell Oracle where the archive logs will
be located, how the archive logs will be named, and to archive filled redo
logs automatically. These commands must be run from an Oracle account with
DBA privileges.
This parameter tells Oracle where to put the archived redo
logs. To ensure recovery, the archive logs should be located on a different
device from other database files.
ALTER SYSTEM SET
log_archive_dest='c:\oracle\oradata\alydan\archive' SCOPE=SPFILE;
The next parameter
tells Oracle how to name the archived redo
logs. The file name defined with the command below begins with the redo log
sequence number, zero filled to a fixed length, followed by the extension .arc.
I use the arc extension to ensure that archive logs are easily
distinguished from other database files.
ALTER SYSTEM SET log_archive_format='%S.arc'
SCOPE=SPFILE;
The final parameter tells Oracle to
automatically create the archive logs as redo logs are filled. It makes no
sense to not automatically create the archives, and, with Oracle10g, Oracle
not longer requires setting the parameter (and in fact, setting it can cause
problems).
ALTER SYSTEM SET log_archive_start=TRUE
SCOPE=SPFILE;
The scope=spfile clause in the above commands tells Oracle to
write these parameters to the spfile so they will take effect the next time
the database is restarted.
Now the database must be shutdown, started, mounted but not opened, put
in archivelog mode, and then opened using the following sequence of commands
in SQL*Plus from an Oracle account with SYSDBA privileges.
Shut down the database.
SHUTDOWN IMMEDIATE
Start the database processes and mount the database files, but do not
open the database for use.
STARTUP MOUNT
Put the database in archivelog mode.
ALTER
DATABASE ARCHIVELOG;
Open the database. .
ALTER
DATABASE OPEN;
The database should now be running in archivelog mode. You can test
archiving by switching log files. You should then see the archive log
created using the specified format in the specified directory.
ALTER
SYSTEM SWITCH LOGFILE;
Note: This tip was tested using
Oracle9i.
Was this tip useful? Did you
find any errors? Do you have any suggestions? Do you care? Click
here for the tip feedback page. Thank you.
| 
