News Stay informed about the latest enterprise technology news and product updates.

Oracle backup and recovery: Hot and cold, schema-based and incremental backups

Designing and maintaining a solid backup and recovery process is the most important task a DBA can do.'s resident Oracle experts have answered many user-submitted questions over the last year about the best ways to backup and recover mission-crtical data. Below, gurus Brian Peasland and Karen Morton offer explanations of hot and cold backups, schema-based and incremental backups, resources for scripts and hints for keeping backup performance at optimal levels.

brian peasland expert picture<>Designing and maintaining a solid backup and recovery process is the most important task a DBA can do.'s resident Oracle experts have answered many user-submitted questions over the last year about the best ways to backup and recover mission-crtical data. Below, gurus Brian Peasland and Karen Morton offer explanations of hot and cold backups, schema-based and incremental backups, resources for scripts and hints for keeping backup performance at optimal levels.


Hot and cold backups

We are developing a airline reservation system which should be a high availability system and should handle terabytes of data. Could you suggest the best strategy for data backup on a 9i database? Also what strategy should be adopted to move these backup files to storage devices?

Karen Morton: Since a cold backup requires the database to be shut down, it is not an option for a 24x7 database. "Hot" backups give you the ability to backup while the database is still up and functional. You can either put the tablespaces into backup mode and back them up using some OS utility (ocopy, copy, cp, tar, gzip, etc.) or you can use RMAN (Recovery Manager). Either way you get a good backup from which a full recovery can be made without sacrificing downtime.

As far as a strategy for moving backup files to storage devices, everybody has a different opinion. I tend to prefer to backup to disk as a first step (usually the fastest disk I have available) and then later move it off to a tape device or other storage media. Your decisions have to be made based on your environment and the limitations you have there. Everybody's "best method" is somewhat different from everybody else's. Look at your resources and make your decision accordingly.

I use Oracle 8i on Windows 2000 Advanced Server. Will I be able to take a .dmp backup using the EXP utility on my schema while other users are using the database?

Brian Peasland: Yes, you can run EXP for a schema while others are signed on to the database. But to make sure that everything is consistent, set the parameter CONSISTENT=Y. You run EXP from the command line. But I wouldn't rely on EXP for my backup/recovery strategy. Instead, you will probably want to employ cold backups. Please refer to the Oracle Backup & Recovery Guide for complete information.

You stated that one should rely on cold backups. What's wrong with hot backups?


Brian Peasland: I had to look back at my response to make sure we were both on the same page. I wanted to make sure because there is absolutely nothing wrong with hot backups! I use them all the time. And for some databases, hot backups are my only backups!

I stated, "But I wouldn't rely on EXP for my backup/recovery strategy." My comment here is that one should not rely on EXP/IMP for a backup and recovery strategy. At least not as the sole method of backup and recovery. One problem with this method is that there is no means of rolling forward any changes to the database. This may or may not be important to you. For many cases, but not all, EXP/IMP by itself is not sufficient. They do have their advantages, but I don't use them as my sole backup and recovery strategy.

Incremental and schema-based backups

Could you please give us an easy explanation of incremental backup?

Karen Morton: See the Oracle documentation Backup & Recovery Online Roadmap for more information but here are the basics from the guide:

An incremental backup reads the entire file and then backs up only those data blocks that have changed since a previous backup. Oracle allows you to create and restore incremental backups of datafiles, tablespaces or the whole database. Note that RMAN can include a control file in an incremental backup set, but the control file is always included in its entirety -- no blocks are skipped.

The primary reasons for making an incremental backup are:

  • To save network bandwidth when backing up over a network.
  • When the aggregate tape bandwidth available for tape write I/Os is much less than the aggregate disk bandwidth for disk read I/Os.
  • To be able to recover changes to objects created with the NOLOGGING option.

Can you please explain schema-based backups?

Brian Peasland: The only way in Oracle to do a "schema based backup" is with the export utility and the "OWNER=" parameter. This will dump the entire schema's contents to a dump file. The user does not have to be logged out for this to occur. Keep in mind, that export files cannot be used for rolling forward after the backup has occurred. You will only be able to restore from the dump file, up to the point in time that you took the dump.

More backup and recovery tips

What is the best backup strategy to use in a factory with no DBA? The 8i database is in NOARCHIVELOG mode and has no tape device. And in a fail situation, what shall I do?

Brian Peasland: If you are not running in Archive Log Mode, then incremental backups are not the most ideal. Personally, I'd put the database in Archive Log Mode and do full backups of the database. I'd also backup the archived redo logs as well.

"In a fail situation what shall I do?". Each failure presents itself with different options for recovering your database. And most of it depends on what type of backup strategy you employed. One of the best things you can do is to get a copy of Oracle 8i Backup & Recovery Handbook by Velpuri & Adkoli on Oracle Press (the 9i version is out too!). You can get a copy of this book in most bookstores or even off the Web (like Every good DBA that I've talked to has a copy of this book or has read it as some time. It discusses your different backup strategies along with pros and cons of each. And it goes through a number of different recovery scenarios.

Can I write scripts to back up and restore databases in Oracle 8.0.6?

Karen Morton: You can certainly write backup/restore scripts! There are a ton of great sites online that can give you a head start with lots of scripts that have already been written. How your scripts will need to be created will depend on things like whether you use a cold or hot backup method, your operating system (Windows, Unix, Linux....) and whether you use RMAN or not.

Here are a few good links for you to search:

I'd also recommend that you consider looking at the Oracle8/8i Backup and Recovery Handbook available through Oracle Press. It has several good fully documented backup/recovery scripts.

How does one import data from a .dmp file into an existing Oracle database with the same structure, using the imp command?

Karen Morton: You can restore as little as a single table or an individual schema from your .dmp file. Also if you are importing into a database with existing tables, you may run into referential integrity problems (foreign keys) or other constraint errors when loading data. It is often a good idea to disable those things before running your import and then re-enabling them after it is complete.

There are four basic import modes: FULL, TABLES, FROMUSER, and TRANSPORT_TABLESPACE. All modes (but FULL since you don't want to do a full import) can be used to accomplish what you desire at varying levels. Let's look at two in particular: TABLES and FROMUSER. TABLES is used to import only specified tables. FROMUSER allows import of all objects owned by a specified user.

If you use TABLES mode, you will simply list the tables you want to import.

 IMP id/password TABLES=(emp,dept,cust)

If you need to import the tables from a specific schema, you'd use the following:

 IMP id/password FROMUSER(scott) TABLES=(emp,dept) IMP id/password FROMUSER(adams) TABLES=(cust)

If you want to import just a given user's schema, you'd use:

 IMP id/password FROMUSER(scott)

What is the performance degradation during online backups in Oracle 8i in an OLTP environment?

Brian Peasland: When you do an online, or hot, backup in an Oracle database, then you tell the database which tablespaces to place in backup mode. This is done with the ALTER TABLESPACE BEGIN BACKUP command. As soon as you issue this command, Oracle handles updates to that tablespace a little differently. Instead of logging just change vectors in the online redo logs, Oracle logs the entire changed data block in the online redo log. So instead of just writing a few bytes for every change one entire block is written. The size of this block is equal to your DB_BLOCK_SIZE parameter.

If there are lots of changes, then more and more information is written to the online redo logs. This also means that more and more information needs to be archived when that redo log gets full and switches to the next available redo log.

It is the writing to the online redo logs and archiving the full redo logs which can take extra time when hot backups are being performed. This is why your hot backups should be scheduled for periods of lesser activity.

If your system is experiencing a high volume of updates during your hot backups, then you may get the "checkpoint not complete" message in your Alert log. This can be your performance killer. If you are not seeing these messages during your hot backups, then there is negligable performance degradation.

So schedule your backups during a period of lesser activity to minimize your impact!

I have to back up the archive logs regularly to recover from a crash. If I take a complete database backup, can I delete those previously backed up archive files and start with fresh archive log backups?

Brian Peasland: Your archived redo logs and your backup go hand in hand. Let's say that I backup my database on Saturday. Then the database crashes on Wednesday. In order to restore fully, I need Saturday's backup and all of the archived redo logs since then. So for that reason, I tend to think that the archived redo logs are tied to the backup. Without the backup, the archived redo logs are of little use. And without the archived redo logs, the backup is only as good as the day it was taken.

Many DBAs like to keep multiple backups on hand. We call these generations of backups. The latest backup is generation zero (0). The backup prior to that is generation minus 1 (-1). This type of terminology is from the old mainframe days. Many installations keep many generations available. How many generations? It all depends on the company's policy. Some only keep three generations. Some keep size generations. Some keep enough generations to hold backups for an entire year! It all depends.

I've already discussed how the archived redo logs are "tied" to the backup. If you decide to keep three generations of backups, then you need to keep the archived redo logs for those three backup generations. Once you delete a backup, the archived redo logs from that backup to the next backup are no longer valid. So you can delete those as well.

Could you tell me how to find the sequence of the last full backup?


Brian Peasland: You can query V$LOG_HISTORY to know the SCN numbers in a particular archived redo log, along with the time that the archived redo log holds its first SCN. A query similar to the following can help you:

 SELECT sequence#,first_change#,TO_CHAR(first_time,'MM/DD/YY HH24:MI:SS') AS time FROM v$log_history;


Dig Deeper on Oracle strategy and product roadmap