An Oracle backup is a copy of data that can be used to restore the original data if it is lost. The backup may include the control file, Archive logs and datafiles. Without a proper backup strategy, recovery of the database due to failure may not be possible. You should tailor your backup strategy to the business requirements of the data. For example, if you are charged with developing a backup strategy for a development database that is refreshed often from a consistent set of test data, you may not need to perform frequent backups. On the other hand if your database is a 24 x 7 catalog sales database you would want to perform frequent backups to minimize down time.
This topic addresses:
Every database is subject to failure. The following are the different types of failures that may occur:
This section addresses when to perform database backups and what parts of a database need to be backed up. Before you create any database, you should design a backup and recovery plan based on the business need for the data. If this step is not completed, there is no guarantee the data can be recovered as required.
The following are the physical database structures of an Oracle database that are included in a backup:
There is no need to backup the online redo log files as part of a normal backup strategy. If the database crashes and all copies of the online redo logfiles are lost, recovery depends on whether your database is in ARCHIVELOG mode or not.
When performing disk maintenance or moving a database from one computer to another, you can backup the redo logfiles and use them so the database does not have to be opened with the RESETLOGS option. This allows the backup taken before the move to be used for recovery and saves the time of doing another whole backup of the database.
A single segment extent cannot span multiple datafiles and must be contiguous. Datafiles should be included in every backup strategy.
Archive logfiles, if kept on disk, should be backed up up to tape before deleting them to make room for more archive logfiles. You should keep archive logfiles until they are older than the latest database backup you may want to use for recovery. For example, if you backup your tablespaces once a week and keep four weeks' worth of tablespace backups, archive logfiles older than four weeks are no longer needed.
Whole database and tablespace backups are part of all backup strategies. The frequency of performing backups should be based on the amount of changes made to the data and the business needs of the data. If the data changes a lot then the frequency of backups should also be high. On the other hand, if downtime is not an issue and loss of some data is acceptable, you can perform backups less frequently. If the database is read-only and refreshed on a monthly basis, you need to perform backups only once a month after the scheduled refresh.
When structural changes are made to your database a backup should be done both before and after the change. Structural changes include:
If the database is in ARCHIVELOG mode, you need to back up only the control file with an ALTER DATABASE BACKUP CONTROLFILE command. If the database is in NOARCHIVELOG mode, you need to perform a consistent whole database backup before and after the structural changes.
If your database is operated in ARCHIVELOG mode, you can back up tablespaces individually by backing up the datafiles associated with them. This can be very useful if parts of your database change often and other parts do not. This allows you to have a more frequent backup schedule for the more active parts of your database and a less frequent backup schedule for the less active parts. This type of strategy can reduce both recovery and back up time.
When database operations are performed using the UNRECOVERABLE option, backups should be taken after the objects are created. When the UNRECOVERABLE option is used to create tables or indexes, no redo information is recorded, and the object cannot be recovered until the tablespaces they are in are backed up. The same holds true for direct path loads with SQL*Loader.
Read-only tablespaces should be backed up immediately after making the tablespace read-only. As long as the tablespace is read-only, it does not need to be backed up again. When backing up a read-only tablespace, it is not necessary to use the BEGIN and END BACKUP commands; in fact these commands will cause an error if used on a read-only tablespace. Once the tablespace is made read-write again, you should resume backups of the tablespace.
How long database backups are kept depends on the needs of the data. If there is a possibility that the database will need to be recovered to a previous point in time, a backup from before that point in time will be used in the recovery. In NOARCHIVELOG mode, this means keeping consistent whole database backups. In ARCHIVELOG mode, you will need a whole database backup, with the control file from the point in time the recovery is needed, and all archive logs required.
Important Note: Whenever the physical structure of the database is changed in NOARCHIVELOG mode the database should be shutdown and backed up immediately before and after the change is made.
Once a database is created, put it in ARCHIVELOG mode, then shut it down and perform a complete backup. The database can then be restarted and run continuously without doing a consistent whole backup again. From this point, you can perform open or closed tablespace backups to keep the backups current and reduce recovery time. Note too, all tablespaces do not have to be backed up on the same frequency. The backup schedule should be determined by the rate at which the data changes and the desired recovery time.
When structural changes are made in ARCHIVELOG mode, the control file should be backed up using the ALTER DATABASE BACKUP CONTROLFILE command. Do not use the operating system to backup the control file unless the database is shutdown.
The Oracle Export utility can be used to add flexibility to your backup strategy. It is not a substitute for operating system backups because it is a logical backup and cannot give the same level of recovery as other Oracle backup scenarios. The Export and Import utility can selectively backup and restore specific database objects. This type of backup may be used for a set of tables that never change. If user error causes the data in one of these tables to be lost, the whole tablespace does not need to be taken offline for recovery. The import utility can simply re-import the data for the table, and no other recovery is needed.