Oracle Database Backup

What strategies are available for backing-up an Oracle database? (For DBA)

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

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 databases are 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 saving than sorry.

Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files, etc. If your database is in ARCGIVELOG mode, you also need to backup archived log files.

What is Archive and noarchive log mode?

An Oracle database can be either in archive or noarchive mode. A production database should definitely run in archive log mode. Otherwise, the chances to loose data approach 100%.
Noarchive log mode:
If the database is in noarchive log mode, redo logs can (and will) be overwritten without making sure they are saved. This implies that a database cannot be recovered even if backups were made.
Archive log mode:
If the database is in log archive mode, the database makes sure that redo logs are not overwritten before the have been archived.

Steps for Switching from noarchive to archivelog mode

1 ) The database is currently in noarchivelog mode:

SQL> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

2) Of course, archive is not started. It wouldn’t make sense, after all.

SQL> show parameter log_archive_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE

3) Now, trying to put the db in archivelog:

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted EXCLUSIVE and not open for this operation

4) Ok, we shut the database down und mount it only (that is we don't open it) to change the log mode. After having changed it, we open the database:

adpdb: /users/dba/oracle/dba >sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.2.0 - Production on Tue Jul 29 19:15:42 2003
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.
Total System Global Area 104694832 bytes
Fixed Size 730160 bytes
Variable Size 54525952 bytes
Database Buffers 49152000 bytes
Redo Buffers 286720 bytes
Database mounted.

SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.

SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG

SQL> show parameter log_archive_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_start boolean FALSE
SQL>

5) Still, the arch process has to be started. This is done via making the following change in the pfile that should be in either $ORACLE_BASE/admin/SID/pfile or $ORACLE_HOME/admin/SID/pfile. The following lines are added to the end of the file:

log_archive_dest=/backup/adpdb/logarch/adpdb_

log_archive_start =TRUE