Oracle backup best practices

Learn Oracle backup best practices in this column from backup and recovery expert W. Curtis Preston.

Sixteen years ago, my lack of understanding about how to back up Oracle caused me to be unable to restore the purchasing database for a $32 billion dollar credit card company. This early mistake is what goaded me to learn everything I could about backups, especially Oracle data backups. It's not possible in the available space to cover everything you need to know about Oracle backup and recovery -- there's a reason there are entire books on the subject -- but I can at least provide you an overview of Oracle backup best practices that you can use to help begin your research.

User-managed backups in Oracle

The longest running method of doing backups in Oracle is referred to as user-managed backups. This is when the administrator is responsible for ensuring that Oracle has been put in the proper mode before performing the backup, and is also responsible for ensuring that they then back up all the appropriate files.

There are two states you can place Oracle in before performing a user-managed backup: offline or hot backup. Placing an Oracle database into offline is as easy as shutting down the instance it belongs to, which can be done by running the standard Oracle shutdown script. Placing Oracle into hot backup mode is a little bit harder, as it requires you to create a separate script for that purpose. However, it's also as simple as connecting to the database and issuing an alter database begin backup command. Once you've done either of these things, it's safe to back up the data files.

One thing that must be discussed is what happens when you place a database into hot backup mode. It is a common misconception that this stops all writes to the data files; this could not be further from the case. What does happen is that Oracle freezes the SCN header of each data file and it changes what it logs to the redo logs. The SCN header is an ever-increasing value that tells Oracle when a data file recovered from backup was put into hot backup mode, and the latter is what allows it to bring that data file up to date after recovering it to a point in time previous to the point to which you want to recover. The bulk of the data file, however, will continue to change as normal. The fact that you're backing up a changing data file is why Oracle switches what it's writing to the redo logs; the full blocks that it writes to the redo logs during backup mode allow it to synchronize any blocks that changed during a backup.

In addition to the data files, of course, there are other parts of an Oracle database that must be backed up if you are performing user-managed backups. The control file can be backed up using the backup controlfile command, and the archived redologs can be backed up using any standard backup utility. It is a good idea, however, to switch off the current redolog using the alter system archive log current command. (If you are scripting, you should not use the similar alter system switch logfile command, as it returns the prompt before the copying of the archived redolog is done.)

The main (if not only) advantage of user-managed backups is that it does not require a license, unlike all the plugins to RMAN. Unfortunately, the complexity of performing user-managed backups is a major disadvantage. In addition, it is not possible to perform an incremental backup using this method, as any backup of the data files will be a full backup.

Oracle backup with RMAN

Oracle Recovery Manager (RMAN) is the preferred method of backing up Oracle and is included with every copy of Oracle. Without any plug-ins/agents from a commercial backup software package, RMAN can back up by itself to a local disk. (You can then back up this disk backup using standard backup software.) To back up over the network or to a tape drive, you'll need a plug-in/agent from your commercial backup software company, and this can cost hundreds to thousands of dollars depending on the product. However, this cost does come with a lot of functionality such as advanced scheduling, reporting, and automated copying of the backups for offsite purposes.

RMAN also requires some scripting, but does not require as much querying of the database to know what needs to happen. Where a user-managed backup script needs to ask Oracle where all the data files, control files, and redo logs are, RMAN already knows all these things and simply needs to be told to back them up to the predetermined backup destination using commands like backup database, backup controlfile, or backup archivelog. There is also no need to put an Oracle database into backup mode, as Oracle is the one transferring the blocks to the backup destination and knows when each block was backed up, so there is no need to freeze the SCN or add additional data to the redo logs. Besides being less complex than user-managed backups, the big advantage to RMAN is being able to perform true incremental backups.

Oracle recovery best practices

The real advantage to RMAN over user-managed backups can be seen during a recovery. During a recovery from a user-managed backup, it is up to the user to know the proper time to restore data files from, put each part of the database in the appropriate mode, restore the data files from wherever they were backed up, then perform media recovery to bring those files up to date. With RMAN, you can simply say restore database and recover database. RMAN will figure out which data files need to be restored and recovered, and it knows where all of this data is stored and will grab it from the appropriate place.

It's appropriate to sum this column up with the following Oracle backup best practice: If you are not currently performing RMAN-based backups, you should be. It is easier during backups and recoveries. If you can't afford the agent, consider using it in a standalone fashion without an agent. All you need is a disk system large enough to hold a copy of your database and RMAN will back up to it free of charge.

Next Steps

Read an Oracle RMAN case study

Bookmark SearchOracle for info on Oracle backup and recovery

Learn Oracle backup and recovery strategies


Dig Deeper on Backup and recovery software