Archiving Rule #1...

If you are not running your Oracle database in archivelog mode, someday you will lose data!

That being said, I know of a number of companies that choose not to run their databases in archivelog mode. The justifications vary, but the bottom line is that (hopefully) they understand the risk and have a plan in place in case the inevitable does happen.

For those who want to change a noarchivelog mode database to archivelog mode, the basics are easy. You update the initialization parameters that control archiving so that on a database restart they take effect, and then restart the database, putting it in archivelog mode. The steps are:

1) With the database open, make these parameter changes:

ALTER SYSTEM SET log_archive_start = TRUE SCOPE=SPFILE;

ALTER SYSTEM SET log_archive_format = 'file_name_format' SCOPE=SPFILE;

-- Standard Edition

ALTER SYSTEM SET log_archive_dest = 'archive_directory_path' SCOPE=SPFILE;

-- Enterprise Edition

ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=archive_directory_path' SCOPE=SPFILE;

• Setting log_archive_start to TRUE tells Oracle to automatically copy redo logs to the archive destination when the database is in archivelog mode.

• log_archive_format defines the file name. My preferred format is %S_%T.arc. The %S specifies the redo log sequence number, and using a capital S zero pads to the left. The underscore is a constant. The %T is the thread number for the redo log, and again the capital T zero pads to the left. I like to use the arc (for archive) as the file extension to provide another indication of the type of file. The result is a file name that looks like 00008_001.arc.

• log_archive_dest defines the directory path for the archive logs. An example might be C:\oracle\oradata\orcl\archive. The above commands are part of the basic setup for archiving. However, the Enterprise Edition database provides many other options.

2) Restart the database using the commands:

• shutdown immediate - Clean database shutdown

• startup mount - Start the database, but do not open it.

• alter database archivelog; - Change the database to run in archivelog mode.

• alter database open; - Open the database

A  few other archivelog thoughts:

• Store archive logs, database data files (tablespace files), and backed up database files on different disks to improve chances of successful recovery in case of disk failure.

• Save your archive logs for as long as you save your database file backups.

• Test your database recovery!

At this point, your database is up and running in archivelog mode, and you will find sleep to come much more easily.

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.