Learn The Steps To Make Oracle Online Backup Easy

An Oracle online backup (also known as a hot backup or an open database backup) is the process of taking a backup of Oracle whilst the database is open and users are making changes to the data.

You might wonder how you can recover the database if you take a backup of the database whilst changes are being made to the data. Well the simple answer is that, if you do it right, Oracle has mechanisms in place to protect the integrity of the database whilst the backup is in progress.

So how does this work? As you'll see in a minute there are 2 ways to do this: manually (known as user-managed) or using RMAN (the Recovery Manager). First, let's look at the user-managed process one step at a time.

User-Managed Oracle Online Backup

1. Ensure the database is in archivelog mode and that log files are archived automatically - issue this command in SQL*Plus

   SQL> archivelog list

   This will report the database log mode (must say Archive Mode) and whether or not automatic archiving is enabled (must say Enabled). If the database is not in archivelog mode you can't do an Oracle online backup (user-managed or using RMAN), instead you'll have to use some other method or shut down the database, put it in archivelog mode and then re-start it.

2. Ensure you have the mapping of tablespaces to data files - issue this command in SQL*Plus (or other sql tool)

   SQL> select ts.name tablespace,df.name file_name from v$tablespace ts,v$datafile df where ts.ts#=df.ts#;

   This will give you a list of all the tablespaces and the files comprising each tablespace.

1. Connect to the database (as sysdba) (via SQL*Plus or other tool)

   SQL> connect / as sysdba;

2. for the tablespace to be backed-up, issue the following command

   SQL> ALTER TABLESPACE BEGIN BACKUP;

   This causes a checkpoint to occur - all the dirty blocks are written to disk and the header of each datafile is stamped with the current SCN (system change number).

3. Once the checkpoint has finished, use o/s commands to copy all the data files comprising this tablespace to the archive directory. For example

   SQL> host copy c:\oracle\oradata\discov\users01.dbf c:\oracle\ora92\backup\uman\users01.dbf

4. When all the files for the current tablespace have been copied, issue the following command:

   SQL> ALTER TABLESPACE END BACKUP;

   which causes another checkpoint to occur so that the data file headers are updated with the latest scn.

5. Repeat steps 2-4 for the rest of the tablespaces in the database.

It is important to note that whilst the data files are being copied they are still being written to by Oracle, but the file headers are not updated with the SCN until the tablespace is taken out of backup mode.

What is also happening is that the database is having to write extra information to the redo logs (for recovery purposes). For this reason, an Oracle online backup is best performed when there is not too much activity taking place and one tablespace at a time. If more than one tablespace at a time is put into backup mode when there is a lot of changes occurring it could have a significant impact on performance.

Obviously the best way to do an Oracle online backup (especially for large databases) is to script it so that you avoid mistakes such as missing a tablespace or one of the data files making up a tablespace.

The other important thing to do is to backup the control file, the parameter file and the archived logs at the same time. Without the archived logs for rolling forward from the time of the backup, your backup is useless.

Using RMAN To Perform Oracle Online Backup

In other words, using RMAN means you don't have to worry about putting each tablespace into backup mode, then using o/s commands to copy the data files and then taking the tablespace out of backup mode.

For example the RMAN command:

RMAN> backup database plus archivelog;

can be used to make a backup of all the data files as well as the parameter file, control file and archived logs into one backup set for handy archiving and restore.

Summary

Using RMAN makes the process a lot simpler although you have to learn a new set of commands

Whichever method you use, the database has to be in archivelog mode (and the archived logs must be kept secured) otherwise you won't be able to recover the database.

For full details on RMAN and all the other backup options refer to the Oracle documentation: Oracle 11g Backup and Recovery

Return from Oracle online backup to Oracle backup and recovery