The following methods are valid for backing-up an Oracle database:
Export/Import - Exports are "logical" database backups in that they
extract logical definitions and data from the database to a file. See the
Import/ Export FAQ for more details.
Cold or Off-line Backups - Shut the database down and backup up ALL
data, log, and control files.
Hot or On-line Backups - If the database is available and in ARCHIVELOG mode,
set the tablespaces into backup mode and backup their files. Also remember
to backup the control files and archived redo log files.
RMAN Backups - While the database is off-line or on-line, use the
"rman" utility to backup the database.
It is advisable to use more than one of these methods to backup your database.
For example, if you choose to do on-line database backups, also cover yourself
by doing database exports. Also test ALL backup and recovery scenarios
carefully. It is better to be safe than sorry.
Regardless of your strategy, also remember to backup all required software
libraries, parameter files, password files, etc. If your database is in
ARCHIVELOG mode, you also need to backup archived log files.
A hot backup is a backup performed while the database is on-line and available
for read/write. Except for Oracle exports, one can only do on-line backups
when running in ARCHIVELOG mode.
A cold backup is a backup performed while the database is off-line and
unavailable to its users.
Restoring involves copying backup files from secondary storage (backup media)
to disk. This can be done to replace damaged files or to copy/move a database to
a new location.
Recovery is the process of applying redo logs to the database
to roll it forward. One can roll-forward until a specific point-in-time (before
the disaster occurred), or roll-forward until the last transaction recorded in
the log files.
sql> connect SYS as SYSDBA
sql> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;
Oracle exports are "logical" database backups (not physical) as they extract
data and logical definitions from the database into a file. Other backup strategies
normally back-up the physical data files.
One of the advantages of exports is that one can selectively re-import tables,
however one cannot roll-forward from a restored export file. To
completely restore a database from an export file one practically needs to
recreate the entire database.
Always do full system level exports (FULL=YES). Full exports include more
information about the database in the export file than user level exports.
For more information about the Oracle export and import utilities, see the
Import/ Export FAQ.
Shut down the database from sqlplus or server manager. Backup all files
to secondary storage (eg. tapes). Ensure that you backup all data files,
all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As
workaround to this problem, shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if you database is in ARCHIVELOG mode, one can still use archived
log files to roll forward from an off-line backup. If you cannot
take your database down for a cold (off-line) backup at a convenient time,
switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
Each tablespace that needs to be backed-up must be switched into backup mode
before copying the files out to secondary storage (tapes). Look at this simple
example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces
in backup mode. Backing them up separately incurs less overhead. When done,
remember to backup your control files. Look at this example:
ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will
write complete database blocks instead of the normal deltas to redo log files
while in backup mode. This will lead to excessive database archiving and even
database freezes.
The biggest advantage of RMAN is that it will only backup used space in the database.
Rman doesn't put tablespaces in backup mode, saving on redo generation overhead.
RMAN will re-read database blocks until it gets a consistent image of it.
Look at this simple backup example.
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
backup
format '/app/oracle/db_backup/%d_t%t_s%s_p%p'
( database );
release channel t1;
}
Example RMAN restore:
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
# set until time 'Aug 07 2000 :51';
restore tablespace users;
recover tablespace users;
release channel t1;
}
The examples above are extremely simplistic and only useful for illustrating
basic concepts. By default Oracle uses the database controlfiles to store
information about backups. Normally one would rather setup an RMAN catalog
database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide
before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a
third-party media manager that integrates with RMAN to backup directly to
tape. Alternatively one can backup to disk and then manually copy the backups
to tape.
The main reason for running in archivelog mode is that one can provide 24-hour
availability and guarantee complete data recoverability. It is also necessary
to enable ARCHIVELOG mode before one can start to use on-line database backups.
To enable ARCHIVELOG mode, simply change your database startup command script,
and bounce the database:
SQLPLUS> connect sys as sysdba
SQLPLUS> startup mount exclusive;
SQLPLUS> alter database archivelog;
SQLPLUS> archive log start;
SQLPLUS> alter database open;
NOTE1: Remember to take a baseline database backup right after enabling
archivelog mode. Without it one would not be able to recover.
Also, implement an archivelog backup to prevent the archive log
directory from filling-up.
NOTE2: ARCHIVELOG mode was introduced with Oracle V6, and is essential
for database point-in-time recovery. Archiving can be used in combination with
on-line and off-line database backups.
NOTE3: You may want to set the following INIT.ORA parameters when
enabling ARCHIVELOG mode: log_archive_start=TRUE,
log_archive_dest=..., and log_archive_format=...
NOTE4: You can change the archive log destination of a database on-line
with the ARCHIVE LOG START TO 'directory'; statement. This statement is
often used to switch archiving between a set of directories.
NOTE5: When running Oracle Real Application Server (RAC), you need to shut down all
nodes before changing the database to ARCHIVELOG mode. See the
RAC FAQ for more details.
One can backup archived log files using RMAN or any operating system backup
utility. Remember to delete files after backing them up to prevent the archive
log directory from filling up. If the archive log directory becomes full, your
database will hang! Look at this simple RMAN backup script:
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4> format '/app/oracle/arch_backup/log_t%t_s%s_p%p'
5> (archivelog all delete input);
6> release channel dev1;
7> }
Oracle will stop updating file headers, but will continue to write data to the
database files even if a tablespace is in backup mode.
In backup mode, Oracle will write out complete changed blocks to the redo log
files. Normally only deltas (changes) are logged to the redo logs. This is
done to enable reconstruction of a block if only half of it was backed up
(split blocks). Because of this, one should notice increased log activity
and archiving during on-line backups.
If a database was terminated while one of its tablespaces was in BACKUP MODE
(ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required
when you try to restart the database. The DBA is then required to recover the database
and apply all archived logs to the database.
However, from Oracle7.2, you can simply take the individual datafiles out of backup
mode and restart the database.
ALTER DATABASE DATAFILE '/path/filename' END BACKUP;
One can select from V$BACKUP to see which datafiles are in backup mode.
This normally saves a significant amount of database down time.
See script end_backup2.sql in the script section of this FAQ.
Thiru Vadivelu contributed the following:
From Oracle9i onwards, the following command can be used to take all of the datafiles out
of hotbackup mode:
ALTER DATABASE END BACKUP;
The above command needs to be issued when the database is mounted.
Recovery without any backup is normally not supported, however,
Oracle Consulting can sometimes extract data from an off-line database using a
utility called DUL (Disk UnLoad). This utility reads data in the data files
and unloads it into SQL*Loader or export dump files. DUL does not care about
rollback segments, corrupted blocks, etc, and can thus not guarantee that the
data is not logically corrupt. It is intended as an absolute last resort and
will most likely cost your company a lot of money!!!
The following INIT.ORA parameter may be required if your current redologs are
corrupted or blown away. Caution is advised when enabling
this parameter as you might end-up losing your entire database. Please contact
Oracle Support before using it.
Re-start your database with the following INIT.ORA parameter if one of your
rollback segments is corrupted. You can then drop the corrupted rollback segments
and create it from scratch. Caution is advised when enabling
this parameter as uncommitted transactions will be marked as committed. One
can very well end up with lost or inconsistent data!!! Please contact Oracle
Support before using it.
Enterprise Backup Utility (EBU) is a functionally rich, high performance
interface for backing up Oracle7 databases. It is sometimes referred to as
OEBU for Oracle Enterprise Backup Utility.
The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above
is similar to Oracle7's EBU utility. However, there is no direct upgrade
path from EBU to RMAN.
Start by creating a database schema (usually called rman). Assign an appropriate
tablespace to it and grant it the recovery_catalog_owner role. Look at
this example:
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was
done by running the catrman.sql script.
The following Media Management Software Vendors have integrated their media
management software packages with Oracle Recovery Manager and Oracle7
Enterprise Backup Utility. The Media Management Vendors will provide first
line technical support for the integrated backup/recover solutions.
