Oracle backup and recovery best practices

Even though backing up your Oracle databases may be time consuming, the specter of lost transactions, lost revenue and dissatisfied customers loom over it. Backing up Oracle is becoming one of the most important tasks a backup/storage administrator can do today.

If we need to roll out a new database patch to Oracle, we'll take a snapshot first so we can roll back in case anything goes wrong. Matthew Staver, information technology managerPentel

There are several common Oracle backup issues users can watch out for. The first is that backing up an Oracle database involves not only backing up the physical data (the copies of database file) but also the logical data -- the data that is exported using SQL commands and both committed and uncommitted changes in the redo log buffers.

When a failure occurs, recovery of the database needs to occur. Recovery involves using the redo data to roll the backup forward to a new time and rolling back uncommitted transactions to a consistent pre-failure state. If a database or IT administrator has been taking snapshots of the database, that recovery may consist only of restoring the snapshot and applying redo data to it.

There are a number of files that need to be backed up in an Oracle database backup. They are datafiles, control files, online redo log files and archived redo log files. Oracle databases are organized into one or more physical files that are logically called tablespaces. The datafiles themselves are divided into data blocks. Control files contain the database name, creation timestamp, datafiles associated with the database, redo log files, a checkpoint and backup meta data. Redo log files are used to record all changes to the database.

Each database has a mechanism for rolling back and undoing changes. In Oracle, this is called Automatic Managed Undo. Undo records assist rollback transactions, recovery and read consistency.

Oracle backup methods

Oracle offers a number of methods for backing up its databases. Among them are Recovery Manager (RMAN), which automates the backup of a recovery operation; Oracle Data Pump, which makes logical backups; and, User Managed, which allows the database to be backed up manually via user operating system commands.

There is also a variety of backup and recovery software that augments Oracle's backup utilities such as Commvault's Simpana and IBM Corp.'s Tivoli Storage Manager.

Matthew Staver, information technology manager for Pentel in Torrance, Calif., uses a combination of tools. "We will take snapshots with HP's [Hewlett-Packard's] StorageWorks Business Copy EVA software when we are doing something outside of the ordinary," said Staver. "If we need to roll out a new database patch to Oracle, we'll take a snapshot first so we can roll back in case anything goes wrong."

Staver has an HP Enterprise Virtual Array and a variety of HP BladeSystem servers that host Oracle databases. For everyday backup operations, he uses Oracle's RMAN to back them up. Staver also relies on HP's Replication Solutions Manager to provide him with clones of his Oracle databases for testing and development.

"When we want to refresh our testing and development instances of Oracle, we will take a snapshot of our production database," said Staver. "We can do that while everything is running with scripts that HP has called Replication Solutions Manager. [This] lets you script actions with the storage area network, and puts the database in hot backup mode, takes the point-in-time snapshot, presents it a VMware host on the SAN, and then executes the Oracle Rapid Clone script and makes a copy of everything in a minute or two."

Staver said the Oracle Rapid Clone process takes a couple of hours for the DBA to get it online. This is a process that used to take a day or two to restore from tape and bring up.

Oracle Automatic Storage Manager

Another IT manager for a university in the Southeast who asked not to be identified also uses an Oracle tool to backup his databases.

"We're using the Automatic Storage Management (ASM) within Oracle's RAC environment since it allows us to make both copies of the data on our cluster visible to the Oracle installation at the same time," said this IT manager. "We can perform our backup from the data center to the least busy cluster node. When we backed up to the A node, we had slower backup times. So, since we've moved that over and we've been able to do the backup from the other node, the B node, it's decreased our backup time from about 12 hours to under two hours."

Oracle backup tips

Some helpful Oracle backup best practices from the experts include:

• Hot backups, which are taken when the database is active, don't back up active transactions.
• Compress your Oracle backups with either Oracle tools or third-party software such as the IBM/Tivoli Storage Manager. For instance, with Oracle RMAN, table compression can reduce the time required to perform large-table full-able scans by half.
• If maintaining storage space is more important that backup and recovery times, consider using binary compression.

About this author: Deni Connor is principal analyst with Storage Strategies NOW in Austin, TX.