Basics of Oracle 12c Archives - dummies

Basics of Oracle 12c Archives

By Chris Ruel, Michael Wessler

Archiving is the database’s ability to track all data changes. You can turn archiving on or off. Luckily, Oracle 12c has designed archiving to cause minimal overhead. In the end, the price is worth the peace of mind you have of having a 24/7 operation with darn-near-guaranteed zero data loss.

How to turn archiving on and off in Oracle 12c

With archiving off, you can take backups of the database only when it’s closed (also called consistent backups). You do so by shutting down that database and starting it in mount mode. As a result, no changes are allowed to the data.

This method allows you to take a consistent copy of the data as it exists at that point in time. If you ever have to restore this backup, your database will look exactly as it did when the backup was taken . . . even if it was a year ago.

You might begin to see some inconveniences if you turn archiving off:

  • Inconveniences are unacceptable if your database requires 24/7 availability. You can’t just shut it down and disallow changes for as long as your backup takes.

  • Even if you do consistent backups every day, what happens to the changes that occur between backups? They’re lost if you have to restore from a previous backup.

With archiving turned on, you get the following benefits:

  • All data changes are tracked.

  • You can do backups with the database open and available to all users.

  • If you ever have to restore a backup that was taken the night before, you can apply the archives that were tracked up until the point of failure.

In reality, archiving is a must for almost all live production databases. It’s rare that you can afford to take the database offline for significant periods of time or afford to lose data in the event that a backup has to be restored.

Basics of archive logs in Oracle 12c

Besides the impact of the archiving process, you have to consider what to do with all the archive log files being created. Again, your database size and number of changes determine how much archive data you will create.

You have two choices for where to store the archive logs:

  • Fast Recovery Area: If you store the archive logs here, Oracle neatly organizes them by database and date. This solution results in less work and fewer parameters to configure.

  • LOG_ARCHIVE_DEST_n initialization parameter: This is actually 30 parameters. The n represents a number from 1 to 30. That’s right: You can store up to 30 copies of your archive logs (but doing so would be overkill). DBAs commonly have two, maybe three copies. Here’s an example of how you might set the LOG_ARCHIVE_DEST_1 parameter in your spfile:

    alter system set log_archive_dest_1='LOCATION=/u01/oradata/dev12c/archive';

The bottom line is that you need to monitor the creation, storage, and backup of the archive logs. It’s a fact of DBA life.

The good news is you need to keep the archive logs only for recovery between backups. Does that mean if you back up every night, you can trash all archive logs created prior to that backup? No. Do not trash them every day. There isn’t a general rule of thumb for how long to keep them, but consider the following situation:

It’s Wednesday. You’re taking a full backup of your database every night and running in archive log mode. After the backup is complete, you delete all the archive logs created prior to that backup.

At noon, you have a catastrophic disk failure and must restore backups from the previous night (Tuesday). You discover that the backup tape from the previous night had coffee spilled on it (tsk tsk) and is no longer good.

See where we’re going with this?

  • You have to go back to the backup tape from two nights ago (Monday).

  • You restore that backup and find that you can’t roll forward to the time that your disk failed today because you trashed all the archive logs after each nightly backup.

Again, it is recommended not only to keep archive logs for some time but also including them as part of your backup.

With no other requirements, you may want to keep archive logs for at least 30 days. This system gives you plenty of time to go back in the event that daily or even weekly backups incur some sort of unfortunate mishap.

Look at it this way: If you back up archive logs, you can remove them from the system to conserve space. Another reason to keep archive log backups: It allows you to restore your database to periods from long ago.