• 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 cold backup is a backup performed while the database is off-line and unavailable to its users.

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;

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.

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;

	 alter system checkpoint;
	 shutdown abort
	 startup restrict
	 shutdown immediate

	 ALTER TABLESPACE xyz BEGIN BACKUP;
	 ! cp xyfFile1 /backupDir/
	 ALTER TABLESPACE xyz END BACKUP;

	 
	ALTER SYSTEM SWITCH LOGFILE;   -- Force log switch to update control file headers 	 
	ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';

	 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; 
	 }

	 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; 
	 }

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.

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;

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.

	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> }

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.

	 ALTER DATABASE DATAFILE '/path/filename' END BACKUP;

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;

        _allow_resetlogs_corruption = true

        _corrupted_rollback_segments = (rbs01,rbs01,rbs03,rbs04)

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.

	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;

	rman catalog rman/rman
	RMAN> create catalog tablespace tools;
	RMAN> exit;

 
	rman catalog rman/rman target backdba/backdba
	RMAN> register database;

Add to RMAN script: sql 'alter system archive log current';

Execute from RMAN: change archivelog all validate;

• - formerly ADSM

• - formerly from Spectralogic

• etc...

• This FAQ: Backup and Recovery Scripts

• This FAQ: Oracle Backup and Recovery Handbook (Rama Velpuri, McGraw-Hill Professional Publishing)