Oracle backups

The types of backups you can perform for an Oracle database include the following:

• Hot backup

  Creating a backup of an Oracle database while the database instance is running is a hot backup. If you plan to perform a hot backup, you must operate your database in ARCHIVELOG mode.

  Enter the ALTER TABLESPACE > BEGIN BACKUP command prior to the backup of each tablespace; this tells Oracle that a hot backup is being performed. If this command is not issued, the hot backup appears to succeed but may be useless for restoring the database. To finish the hot backup, for each tablespace, enter the ALTER TABLESPACE > END BACKUP command.

  Changes to data are recorded and held in the rollback segment until they are no longer needed by any outstanding transaction. Taking a hot backup prevents the release of rollback segment data until the ALTER TABLESPACE … END BACKUP command is issued. Therefore, the rollback segment must be large enough to accommodate changes made during the hot backup. If the rollback segment runs out of space, a transaction will fail with an ORA-1555 error:

  ORA-1555: snapshot too old (rollback segment too small)

  While the hot backup succeeds despite this error, changes made to the database may need to be reentered.

  You can avoid this error by performing a hot backup during times of low database activity or by ensuring that your rollback segments are large enough to accommodate data changes made during backup.

  You do not need to shut down the ArcSDE server process (giomgr) prior to making a hot backup.

  For details on hot backup, refer to the Oracle Backup and Recovery Guide for your Oracle release.

• Cold backup

  Creating a backup of an Oracle database while the database instance is shut down is a cold backup. Managing a cold backup is simpler than a hot backup and less prone to error. If you run the database in NOARCHIVELOG mode, a cold backup is your only option. Running the database in ARCHIVELOG mode enables you to use a cold backup to recover a database to the latest committed transaction.

• Database export

  You can use Oracle's export utility to supplement a full backup. If changes are made to a known set of data objects between full backups, you can export the objects. However, the export utility should only be used on data objects that are not changing during the export and on all data objects that are closely related. For example, if you use export to back up a business table, you should also capture the related spatial index, feature, and delta tables in the same backup.

  Note:

  Esri does not recommend that you use the export utility as your only backup method.

  You can also back up the entire Oracle database with the Oracle export utility, then make cumulative and incremental backups. For more information on the export utility, refer to the Oracle utilities manual for your Oracle release.

You should make regular, full backups of the Oracle database. A full backup should include the Oracle database, control files, data files, redo log files, and (if you have started an ArcSDE service), the giomgr.defs, dbinit.sde, and services.sde files.

Note:

It is important that you also test and make sure the backups you have taken restore correctly and work as expected.

If your Oracle database is operating under ARCHIVELOG mode, you can add several variations to your backup strategy in addition to the periodic full backup. Databases operating under the NOARCHIVELOG mode are restricted to full backups with the possible addition of Oracle export files.

You should make at least three copies of the control files with each backup because of their importance in ensuring database consistency. Because control files are comparatively small, there is negligible cost in doing so.

The redo log files are essential for bringing the data files from an earlier state to a later state. Between any two points in time, the redo logs must be found in an unbroken sequence if database recovery is to succeed.

Esri recommends that you maintain at least two copies of all archived redo logs for as far back in time as may be reasonably necessary for database recovery. The two copies should be stored on physically distinct media-separate disk drives, for example, or a disk drive and a tape drive.

If you intend to purge the archived redo log files from their location on disk, be sure that you have a second backup copy of each archived redo log file before purging.

This strategy of multiple backups of the archived redo log files helps guard against multiple media failure, which is not as rare as it might seem. Some tape drives, for example, fail to detect bit errors until you attempt to restore a file, when it may be too late.

You only need to make a single copy of each data file with each backup as long as you carefully maintain multiple copies of archived redo logs.

Note:

The online redo log files are not necessary for backup. If the current online redo log file fails, committed information still exists in memory, which Oracle writes to the data files when a checkpoint is issued. Oracle issues a checkpoint automatically when you shut down a database instance with NORMAL, IMMEDIATE, or TRANSACTIONAL priority. Before shutting down a database with ABORT priority, you should force a checkpoint with the ALTER SYSTEM > CHECKPOINT command if at all possible.

It is important that you read the backup and recovery documentation available for Oracle. The documentation is as follows:

Backup and Recovery Guide

Oracle 10g online documentation list

Oracle 11g online documentation list

Tip:

If you have user-schema geodatabases, be sure to backup the master geodatabase as well as the user-schema geodatabases. There are some tables in the SDE user's schema that are utilized by user-schema geodatabases.