DBA Tips Archive for Oracle

  


Case Study #2 - (Backup / Recovery / No-Archivelog / Read-Only Tablespace)

by Jeff Hunter, Sr. Database Administrator


Contents

  1. Introduction to Oracle RMAN Case Studies
  2. Case Study Overview
  3. Configuring RMAN parameters
  4. Backup the Target Database
  5. Taking Backups of Read-Only Tablespaces
  6. Performing Validation
  7. Maintenance Commands
  8. Remove the TARGDB Database
  9. 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 that includes a read-only tablespace. All of the tablespaces within the TARGDB database will be normal, online, and read/write with the exception of one called READ_ONLY_TBS which is read-only.
SQL> ALTER TABLESPACE read_only_tbs READ ONLY;

Tablespace altered.

SQL> @dba_tablespaces

Status    Tablespace Name TS Type      Ext. Mgt.  Seg. Mgt.  Tablespace Size  Used (in bytes) Pct. Used
--------- --------------- ------------ ---------- --------- ---------------- ---------------- ---------
ONLINE    CWMLITE         PERMANENT    LOCAL      AUTO            52,428,800       15,532,032        30
ONLINE    DRSYS           PERMANENT    LOCAL      AUTO            52,428,800       10,158,080        19
ONLINE    EXAMPLE         PERMANENT    LOCAL      AUTO           157,286,400      142,082,048        90
ONLINE    INDX            PERMANENT    LOCAL      AUTO            52,428,800           65,536         0
ONLINE    ODM             PERMANENT    LOCAL      AUTO            52,428,800       10,027,008        19
ONLINE    PERFSTAT        PERMANENT    LOCAL      AUTO           262,144,000           65,536         0
READ ONLY READ_ONLY_TBS   PERMANENT    LOCAL      AUTO            15,728,640       14,745,600        94
ONLINE    SYSTEM          PERMANENT    LOCAL      MANUAL         838,860,800      395,509,760        47
ONLINE    TOOLS           PERMANENT    LOCAL      AUTO            52,428,800           65,536         0
ONLINE    UNDOTBS         UNDO         LOCAL      MANUAL         419,430,400       23,396,352         6
ONLINE    USERS           PERMANENT    LOCAL      AUTO           104,857,600       83,951,616        80
ONLINE    XDB             PERMANENT    LOCAL      AUTO            52,428,800       46,923,776        90
ONLINE    TEMP            TEMPORARY    LOCAL      MANUAL         524,288,000                0         0
                                                            ---------------- ---------------- ---------
avg                                                                                                  37
sum                                                            2,637,168,640      742,522,880

13 rows selected.


This case study is divided into the following sections:

  1. Create an RMAN script for configuring RMAN parameters.

  2. Back up the target database.

  3. Restore and Validate the Database:

    • Command to verify database is restorable.

    • Command to verify our read-only 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 and the read-only tablespace.



Connecting to the Target Database

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

% rman target backup_admin/backup_admin

Recovery Manager: Release 9.2.0.5.0 - Production

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

connected to target database: TARGDB (DBID=2528050866)

RMAN>



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 RETENTION POLICY TO REDUNDANCY 2;
  CONFIGURE DEFAULT DEVICE TYPE TO DISK;
  CONFIGURE CONTROLFILE AUTOBACKUP ON;
  CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup1/rman/TARGDB/%F';
  CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
  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;
}
exit



Taking Backups of Read-Only Tablespaces

The database does not have to be closed to back up a readonly tablespace in no-archivelog mode.

The following command can be used to backup a readonly tablespace:

run {
  backup tablespace read_only_tablespace_name;
}
In our example, we can use the following command to backup the read-only tablespace "READ_ONLY_TBS".
run {
  backup tablespace read_only_tbs;
}



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
  # -----------------------------------------------------------
  CROSSCHECK BACKUP OF DATABASE;

  # -----------------------------------------------------------
  # 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.
  # -----------------------------------------------------------
  REPORT NEED BACKUP;

  # -----------------------------------------------------------
  # 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.
  # -----------------------------------------------------------
  DELETE OBSOLETE;

  # -----------------------------------------------------------
  # Get a complete summary list of existing backups.
  # -----------------------------------------------------------
  LIST BACKUP SUMMARY;
}



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 were 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. (Use CHECK READONLY, to make sure all read-only files are correct. If not RMAN will not restore them!)
  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 UNTIL TIME 'SYSDATE-2';
  # -----------------------------------------------------------
  set controlfile autobackup format for device type disk to '/orabackup1/rman/TARGDB/%F';
  restore controlfile from autobackup;
  alter database mount;
  restore database check readonly;
  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";
}

exit


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-2017 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
Wednesday, 23-Jun-2004 00:00:00 EDT
Page Count: 5383