Backup and Recovery – Best Practices in Oracle10g

In Information Technology backup is a term used to refer to make additional copies of data called “backups”. Backups are used in the event of a disaster recovery procedure or data loss caused by accidental corruption or deletion of files.

Since a backup system consists of a copy of the whole data, its data storage requirements are huge and the procedure should be optimized to cause least impact in performance in the system. The backup and recovery methods should be completely tested and practiced before being implemented in production environment.

It is the role of DBA to decide on the backup type. The database backup type can be
of type, online database backup, offline database backup, whole database backup, tablespace backup, Datafile backup, Control file backup, Archived Redolog backup or Configuration files. In deciding the content to backed up, the type has to be prioritized based on the importance and degree to which data changes.

The backup methods provided by Oracle can be categorized as logical backups using Data Pump Export and Import, Logical Backups using Export and Import, physical file system backups done online or offline, Incremental physical file system backups by Recovery Manager(RMAN), or Oracle Enterprise Manager – A GUI Interface that invokes Recovery Manager.

As of Oracle Database 10g, Data pump export and import utilities can be used in the place of Export and Import utilities provided with previous versions of Oracle. This is a server based data extraction and import utility and offers significant architectural and functional benefits. It gives the user the facility to stop and restart the jobs, see the status of running jobs and restrict the data that needs to be exported and imported. Performance is enhanced as Data Pump runs as a server process and not as a client program. Data pumps extraction and loads can be parallelized increasing performance standard.

The Logical backup using export and import with prior versions of Oracle are also useful to perform backups. The export utility is used to query the database and write the output to a binary export dump file which is used later by the import utility to read and execute.

The physical file system backup made after the shut down of the database is referred to as offline backup or cold backup. During cold backups the files that are backed up are all data files, all control files and all online redo logs. This backup is reliable as it provides a consistent and complete image of the database as it existed just before the backup was initiated.

In case the database has to be available, then online backups using Oracle’s archivelog mode can be used to provide consistent data backup during transaction processing. When database is run in archivelog mode they can be fully recovered and through the archived redo logs, be rolled forward to any point in time.

A Recovery manager utility called RMAN is used to enable backup and recovery in an automated manner. It can be initiated in a command line mode or from within the Oracle Enterprise Manager. The Recovery manager allows performing full backup, cumulative datafile backup or incremental datafile backup. Using incremental and cumulative backups vastly improves the performance.

The enhancement to RMAN in Oracle Database 10g adds new capabilities. It has the additional features like ability to span resetlogs operations while recovery, to preview the backups needed for recovery, do faster incremental backups by the facility to create a file that will track the blocks that have changed, the facility to have incremental backups dynamically merged with prior backups and also to compress backup so the disk space requirement is considerably reduced.

Recovery Manager is commonly used as a command line utility. Oracle Enterprise Manager (OEM) is a graphical user interface (GUI) tool to enable back up via a point and click method.

It is important to develop and maintain the most appropriate strategy for backup and recovery as it constitutes one of the most important aspects of Database administration, ensuring availability and business continuity in the arena of Disaster Recovery.

| About Sequences and their attributes in Oracle10g | Accessing Remote data with Database Links in Oracle10g | A Guide to Iterative Processing with Loops in PL/SQL | A note on Dynamic SQL and its implementation in PL/SQL Application | Autonomous Transactions in Oracle – How to create and use if efficiently | Backup and Recovery – Best Practices in Oracle10g | Compiling Procedures, Functions and Packages during Application Development in Oracle10g | Introducing Publish-Subscribe Model in Oracle10g | Exploring Debugging Procedures in Oracle10g | External Procedures and their uses in Oracle10g | Guidelines for Locking Data in Oracle10g | How to customize an oracle10g Database using Database triggers | Specifying Constraints while creating table in Oracle10g to enhance Data Integrity |