Problem solve Get help with specific problems with your technologies, process and projects.

Step-by-step backup procedures

Brian Peasland provides step-by-step instructions for performing hot and cold backups.

I am comfortable with import/export procedures, but please respond as if I am a layman. I need some step-by-step backup procedures so I can gain practice and confidence.

Backups are nothing without good recovery as well. So I like to think of both backup and recovery in concert with each other. It is vital that every DBA practice the various backup and recovery methods and scenarios at their disposal. This is not an Oracle-specific way of thinking.

Basically, you have two types of backups, cold and hot. Cold backups require that the database be down. Hot backups can be performed while the database is up and running. A cold backup is the easiest, but has costly downtime. A hot backup is more complex, but lets you leave the database up and running.

A cold backup is real easy. You peform these basic steps:

1. Shutdown the database (IMMEDIATE or NORMAL option).
2. Use OS commands or utilities to copy the database datafiles and control files to your backup destination.
3. Startup the database.

More about Oracle database backup

Learn to use Oracle Data Recovery Advisory for database backup and recovery

Find out how to make an exact copy of an Oracle database

Check out this excerpt from 'Effective MySQL Backup and Recovery'

The larger your database, the longer step 2 will take. Therefore, the more downtime you will require for a cold backup. For large databases, this can be too costly. So a hot backup must be performed.

A hot backup will still use OS command or utilities to copy the database datafiles and control files to the backup destination.

However, these files are "active". In the middle of copying a datafile, the database can make changes to that datafile. This results in a backup that is said to be "inconsistent". You will need a method to resolve the inconsistencies in order to be able to recover from your backup. The Oracle mechanism to resolve these inconsistencies is the archived redo logs. This is why your database must be in ArchiveLog mode in order to take a hot backup. To take a hot backup, the procedure is basically as follows:

1. Place your tablespaces in backup mode with the ALTER TABLESPACE BEGIN BACKUP command.
2. Use OS commands or utilities to copy the database datafiles and control files to your backup destination.
3. Take the tablespaces out of backup mode with the ALTER TABLESPACE END BACKUP command.

Those are the basics for hot and cold backups. Oracle has a nice utility called Recovery Manager (RMAN), which can handle both cold and hot backups. And you can use RMAN through Oracle's Enterprise Manager (OEM) for GUI-based backups.

I would highly recommend that you purchase a copy of the "Oracle Backup and Recovery Handbook" by Velpuri and Adkoli on Oracle Press. It contains lots of information on how to backup your Oracle databases, including many examples. Furthermore, it covers many recovery scenarios that you can encounter. You can follow along, simulate the same failures, and practice sound recovery techniques as well. I highly recommend this book for Oracle DBAs.

Dig Deeper on Oracle database backup and recovery