Oracle Tips by Burleson

Oracle Archives – archived redo logs

Archive log files are only present if archive logging has been initiated in the database instance. Archive logs are copies of the filled redo logs. Archive logs are used to provide point-in-time recovery to time-of-failure. Archive logs can consume a large amount of disk resource in an active environment. In some situations, I have seen archive logs consume tens of gigabytes of disk area in a single day. I am sure there are installations where it is normal to use hundreds of gigabytes for archive logs.

If you want to be able to recover your database to point-of-failure, you have to use archive logging. The logs should be placed away from the other files in the system on a physically separate disk farm, if possible. In one situation, we had to perform an incomplete recovery after a disk failure because the archive logs were on the same disk farm as the other files and the failed disk had sections striped to datafiles, redo logs, and archive logs.

It is usual to keep all archive logs since at least the last backup in a quickly retrievable state. The storage area can be optical storage, tape, or disk for archive logs. However, I suggest that fast media be used for the initial site to which archive logs are written. The use of fast media for the initial archive log location is required to prevent waits caused by the system cycling back to the redo log being archived before it has been written out completely; this results in a hung database until the archive becomes “unstuck.” Once the archive log is written out, it can then be copied to its final location. Once a full cold or hot backup is taken, all previous archive logs can be erased or placed in long-term storage.

If you alter the setting of the COMPATIBLE initialization parameter, it will most likely render all previously generated archive logs as unusable for recovery. Always perform a complete backup after changing the value of the COMPATIBLE parameter. Anytime a startup is performed using the RESETLOGS parameter, all previous archive logs become invalid and may not be used to recover the instance after the startup.

If you are using Oracle parallel server (now called RAC), all archive logs generated from all redo threads must be available to all instances. Usually this is accomplished by using a cron script to copy the archive logs to multiple locations. Another possible solution is to copy the archive logs to a central NFS (Network File System) mounted disk farm; however, if the NFS mount point is lost, you won’t be able to recover, so be sure the NFS mount is on a high-reliability server. Never set LOG_ARCHIVE_DEST for archive logs to raw devices; remember that only one file is allowed per RAW area, so each new archive log would overwrite the previous in a raw destination.

In Oracle8i and Oracle9i, multiple archive log locations are allowed, even on other platforms reachable through the network. This makes administrating standby databases much easier to automate.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.