Sidebar 2

Sidebar: Should I Use Archiving?

In one word, yes! Having logs available during a recovery is a tremendous help. There are several things that you can do with logs that simply are not possible without them:

Restore up to the point of failure. Otherwise, you will only be able to restore up to your last good backup. That means if a failure occurred at 4 p.m., you would lose an entire day's work.

Restore to the point just before the failure. If the failure was caused by human error (delete table), you can restore the database to the way it appeared just before the error.

Restore the database using an old backup. If you have logs, it does not matter if last night's backup failed. You can use a backup that is days old, or even weeks old, and restore up to the last redo log.

Restore only the damaged disk or files. Without redo logs, if you have a 60 Gb database, and lose one 2 Gb disk, you must restore the entire 60 Gb. With redo logs, you only have to restore that disk. You can then use redo logs to sync the restored files with the undamaged files. This feature of archiving saves hours in a typical restore.

Hot backups are not possible if archiving is not enabled.

There are two common arguments for not using archiving. The first is that it would decrease performance. The performance loss is relatively minor, unless you have some parameters set incorrectly. It is important to know that redo logs are always being created in every Oracle instance. Enabling archiving simply copies the closed redo log to another directory for recovery purposes. Before you make any decision about archiving, you should benchmark your instance with and without archiving. (A recent benchmark I performed showed a 2.5 percent performance loss during continuous loads.)

The second argument is that archiving is superfluous. Some databases are read-only and are updated only occasionally. Users might argue that archiving is not needed. They often tell you to backup the database manually when it changes, after a load or import. This defeats one of the main purposes of automating: cron never forgets! If the database does not need archiving because there is little activity, then enabling it won't hurt. The CPU usage is negligible, and archiving will allow you to do hot backups and use all the features listed above.

There is one type of database that I think would be significantly hampered by archiving: the test database. I still believe that logs should be on whenever possible, but when a DBA is testing a new database, he/she may be loading and unloading tables all day long. This generates a huge amount of archived redo logs that have no value. It was for this type of database that I added the ability to do a nightly cold backup of an instance that does not have archiving.

In summary, every production and development database should use archiving, unless continuous imports make it unrealistic.