Oracle Database Tips by Donald Burleson

Hot Backups

A hot backup, or one taken while the database is active, can only give a read-consistent copy; it doesn't handle active transactions. You must ensure that all redo logs archived during the backup process are also backed up. The hot backup differs from the cold backup in that only sections of the database are backed up at one time. This is accomplished by using the ALTER command to modify a tablespace's status to backup. Be sure that you restore the status to normal once the database is backed up or else redo log mismatch and improper archiving/rollbacks can occur.       

While it is quite simple (generally speaking) to do a cold backup by hand, a hot backup can be quite complex, hence should be automated. The automated procedure should then be thoroughly tested on a dummy database for both proper operation and the ability to restore prior to its use on the production database(s).       

The following are limitations on hot, or online, backups: 

• The database must be operating in ARCHIVELOG mode for hot backups to work.

• Hot backups should only be done during off-hours or low-use periods.

• During the hot backups, the entire block containing a changed record, not just the changed record, is written to the archive log, requiring more archive space for this period.      

The hot backup consists of three processes: 

1. The tablespace datafiles are backed up.

2. The archived redo logs are backed up.

3. The control file is backed up.      

The first two parts have to be repeated for each tablespace in the database. For small databases, this is relatively easy. For large, complex databases with files spread across several drives, this can become a nightmare if not properly automated in operating system-specific command scripts. An example of this type of a backup shell script is shown in Source 15.1.      

As you can see, this is a bit more complex than a full cold backup and requires more monitoring than a cold backup. Recovery from this type of backup consists of restoring all tablespaces and logs and then recovering. You only use the backup of the control file if the current control file was also lost in the disaster; otherwise, be sure to use the most current copy of the control file for recovery operations.

Tip 

In a number of computer facilities, backups are kept close at hand, sometimes in the same room as the computer. What would happen if a site disaster destroyed the computer room? Not only the hardware, but all of the system backups and your data, could be lost. The point is: Store backups in another building or even totally off-site. This assures that come fire, flood, or typhoon, you should be able to get backup one way or another.

See Code Depot

www.dba-oracle.com/oracle_scripts.htm