DBA Tips Archive for Oracle


Case Study #1 - (Backup / Recovery / No-Archivelog)

by Jeff Hunter, Sr. Database Administrator


  1. Introduction to Oracle RMAN Case Studies
  2. Case Study Overview
  3. Configuring RMAN parameters
  4. Backup the Target Database
  5. Performing Validation
  6. Maintenance Commands
  7. Remove the TARGDB Database
  8. Restoring and Recovering the Target Database

Introduction to Oracle RMAN Case Studies

Within this section (which I refer to as the RMAN 9i Case Study Series), I wrote several case studies that provide specific examples of how to backup, restore, and recovery different Oracle database configurations. These examples are crafted in much the same way as those found in the $ORACLE_HOME/rdbms/demo.

As with the example scripts that Oracle includes in $ORACLE_HOME/rdbms/demo, it is not meant for any of these case studies (scripts) to be run within a single RMAN session. Rather, these case studies were meant to serve as a basis for developing your own backup, maintenance, restore, and recovery scripts. You should use the various sections in this article as a reference in building individual RMAN scripts which can be run to configure, backup, restore, and recover your database.

The following assumptions should be made throughout each of the case studies:

Case Study Overview

This case study provides the RMAN commands (with detailed embedded comments) used to backup, maintain, restore, and recover a single database instance running in no-archivelog mode. All of the tablespaces within the TARGDB database will be normal, online, and read/write.

NOTE: Other examples within the RMAN Case Study Series will include instructions for handling read-only and excluded tablespaces.

This case study is divided into the following sections:

  1. Create an RMAN script for configuring RMAN parameters.

  2. Backup the target database.

  3. Validate the Database:

    • Command to verify database is restorable.

    • Command to verify tablespace is restorable.

  4. Run several Recovery Catalog maintenance against the target database's control file.

  5. Remove the TARGDB database to simulate and setup a disaster recovery situation.

  6. Restore and Recovery the TARGDB database.

Connecting to the Target Database

The following command will login to the target database, TARGDB with no recovery catalog:
% export ORACLE_SID

% rman target backup_admin/backup_admin

Recovery Manager: Release - Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: TARGDB (DBID=2528050866)


Configuring RMAN parameters

RMAN (Oracle9i and higher) allows the DBA to perform automated database backup and recovery. This feature is supported by RMAN with its ability to define default values for a number of settings, (i.e. channel configuration, parallelism, automated backup of the controlfile). Setting RMAN parameters is done using the configure command.

For the purpose of this case study, I will be setting the following parameters:

Script: configure_database.rcv
run {
  CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/orabackup1/rman/TARGDB/backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024m;
  CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/orabackup2/rman/TARGDB/backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024m;

NOTE: All configuration settings are stored persistently, and will be used by RMAN for all subsequent backup, restore, recovery, and maintenance operations.

NOTE: It is important that you save the database id (DBID) displayed in the RMAN output if you are taking RMAN backups in NOCATALOG mode or if the database name is ambigious in the Recovery Catalog. The DBID is required during disaster recovery! You will see the DBID in RMAN output when connecting to the target database as in the following output:
% rman target backup_admin/backup_admin

connected to target database: TARGDB (DBID=2528050866)

Backup the Target Database

For this case study, we will be backing up an Oracle database that is in no-archivelog mode. Keep in mind that the only backups that are allowed are:

Script: backup_cold.rcv
run {
  # -----------------------------------------------------------
  # The following RMAN commands are run each day.
  # The steps are:
  #   - Re-start the database to perform crash recovery, in 
  #     case the database is not currently open, and was not 
  #     shut down consistently. The database is started in DBA 
  #     mode so that normal users cannot connect.
  #   - Shut down with the IMMEDIATE option to close the 
  #     database consistently.
  #   - Startup and mount the database.
  #   - Backup database.
  #   - Open database for normal operation.
  # -----------------------------------------------------------
  startup force dba;
  shutdown immediate;
  startup mount;
  backup database;
  alter database open;

Performing Validation

The following commands can be run any time to check if RMAN is capable of restoring the database (or a tablespace) using existing backups.

Use the following to verify that a the entire database can be restored using the existing backups:

run {
  restore database validate;

You can also check on the ability to restore an individual tablespace:

run {
  restore tablespace read_only_tbs validate;

Maintenance Commands

RMAN provides several commands that can be used for maintenance:

Script: maintenance_commands.rcv
run {
  # -----------------------------------------------------------
  #  Verify that all backups on the backup media are intact
  # -----------------------------------------------------------

  # -----------------------------------------------------------
  # Display a list of files that need to be backed up based on 
  # the retention policy. For this case study, files that don't
  # have at least 2 backups will be reported.
  # -----------------------------------------------------------

  # -----------------------------------------------------------
  # Delete any un-necessary backups. This command deletes backups
  # based on the retention policy you configured. For this case study, all 
  # backups older than the 2 most recent backups of each 
  # datafile will be deleted.
  # -----------------------------------------------------------

  # -----------------------------------------------------------
  # Get a complete summary list of existing backups.
  # -----------------------------------------------------------

Remove the TARGDB Database

In this section, I want to remove the TARGDB database to simulate and setup a disaster recovery situation. If the instance is still running, it will need to be shutdown:
% rm /u03/app/oradata/TARGDB/*
% rm /u04/app/oradata/TARGDB/*
% rm /u05/app/oradata/TARGDB/*
% rm /u06/app/oradata/TARGDB/*

% sqlplus "/ as sysdba"

SQL> shutdown abort

SQL> exit

Restoring and Recovering the Target Database

In this case study, (running in no-archivelog mode), any user error or media failure would require a complete database recovery. You can, however, use the SET UNTIL command to recover to different points in time when incrementals are taken. (Keep in mind that in our example, we did not make use of incremental backups!)

NOTE: Because redo logs are not archived, only full and incremental backups (if you were taking incremental backups) are available for restore and recovery.

It is assumed that you have all the configuration files like:

are all in their appropriate places. It is also assumed that you can startup the Oracle instance in NOMOUNT MODE and connect from RMAN to the target instance.

The steps are:

  1. If not using a recovery catalog, or if the database name is ambiguous in, you need to start RMAN and set the DBID before restoring the controlfile from autobackup.
  2. Startup database in NOMOUNT mode. (You should have restored the initialization file for database, and listener files [only if connecting over SQLNET].)
  3. Restore controlfile.
  4. Mount the database.
  5. Restore all database files.
  6. Apply all incrementals. (In this example, we are not taking incremental backups, so this step is not required.)
  7. Open database with RESETLOGS mode to re-create the online log files.
  8. You will need to manually add any tempfiles back to the database after recovering the database.

Script: recover_cold.rcv
set dbid 2528050866;

connect target backup_admin/backup_admin;
startup nomount;

run {
  # -----------------------------------------------------------
  # Uncomment the SET UNTIL command to restore database to the
  # incremental backup taken two days ago.
  # -----------------------------------------------------------
  set controlfile autobackup format for device type disk to '/orabackup1/rman/TARGDB/%F';
  restore controlfile from autobackup;
  alter database mount;
  restore database;
  recover database noredo;
  alter database open resetlogs;
  sql "alter tablespace temp add tempfile ''/u06/app/oradata/TARGDB/temp01.dbf'' 
      size 500m autoextend on next 500m maxsize 1500m";


NOTE: Tempfiles are automatically excluded from RMAN backups. This requires them to be re-added at recovery time.

There is an enhancement, 1641989, requesting RMAN take care of these tempfiles in the future.

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 17-Jun-2004 00:00:00 EDT
Page Count: 6690