How to recover undo or rollback tablespace

While handing with undo tablespace you need to be extra cautious due to active transactions in the undo segments. You need to follow different approach depend upon scenario.

Scenario 1: Undo/rollback datafile damaged or dropped accidently when database is up
In this scenario the lost or damaged datafile may contain the active transactions and you cannot able to offline or drop the undo/rollback datafile.

• Startup mount
SQL> STARTUP MOUNT

• Check the status of the datfile
SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;
If the datafile is offline you must bring the datafile online before you recover

• Restore and Recover the datafile
$ rman TARGET / CATALOG rman/*****@rman
RMAN> Restore datafile 'fullpath_and_filename'
RMAN> Recover datafile 'fullpath_and_filename'

• Open the database.
SQL> ALTER DATABASE OPEN;

Scenario 2: Undo/rollback datafile damaged or dropped accidently when database is down

• If using automatic UNDO_MANAGMENT then comment out the parameter in init.ora file. If using rollback segments then comment out ROLLBACK_SEMGNETS parameter

• Mount the database in restricted mode
SQL> STARTUP RESTRICT MOUNT

• Offline the undo or rollback datafile and drop the file
SQL> ALTER DATABASE DATAFILE 'fullpath_and_filename' OFFLINE DROP;

• Open the database and drop the UNDO tablespace or the tablespace which contains the rollback segments
SQL> ALTER DATABASE OPEN
SQL> DROP TABLESPACE tablespace_name INCLUDING CONTENTS;

• Recreate the undo tablespace. If you are using rollback segments, recreate the rollback segment tablespace and rollback segments. Make sure to bring the rollback segments online.

If using Undo tablespace
SQL> CREATE UNDO TABLESPACE undotbs2 DATAFILE '/oradata/undotbs2_01.dbf' SIZE 100M REUSE AUTOEXTEND ON;

If using rollback segment tablespace
SQL> CREATE TABLESPACE rollback_tbs DATAFILE '/oradata/rollback_tbs01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL;
SQL> CREATE ROLLBACK SEGMENT rollback1 TABLESPACE rollback_tbs;
SQL> ALTER ROLLBACK SEGMENT rollback1 ONLINE;

• Modify the parameter file settings
If using UNDO tablespace
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=new_undo_tablespace_name

If you are using the rollback segment tablespace, include the rollback segments that you created in previous step in ROLLBACK_SEMGNETS parameter in init.ora file

• Take the database out of restricted mode.
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Oracle 11g introduced a new feature called RMAN UNDO Bypass. The RMAN backup command no longer backs up the UNDO data that is not needed for recovery. Prior to Oracle 11g, all UNDO transactions that were already committed also backed up. This backup undo optimization minimizes the backup time and storage.

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com

Thursday, May 27, 2010

Controlfile and Server parameter file (spfile) autobackup

RMAN can be configured to automatically backup the control file and server parameter file (spfile) whenever the database structure in the control file changes and whenever a backup record is added. The autobackup feature enables RMAN to recover the database even if the current control file, catalog, and server parameter file are lost.

The RMAN can search the autobackup path and restore the server parameter file from backup. Once the instance is started with restored spfile, use RMAN to restore the controlfile from autobackup. After you mount the DB using restored control file, use RMAN repository information from control file to restore and recover the data files.

You can turn ON or OFF the autobackup feature by using the following commands:
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP OFF;

If control file autobackups is ON and the backup includes datafile 1, RMAN writes the control file and SPFILE to a separate backup set. If autobackup is OFF and the backup includes datafile 1, then RMAN includes the current control file and SPFILE in the same backup set as the datafiles. After backup completion the database writes a message containing the complete path of the backup piece and the device type to the alert log.

The control file autobackup filename has a default format of %F for all device types, so that the RMAN can guess the file location and restore the controlfile without a repository. All autobackup formats must include the %F variable.

The format %F translates into c-IIIIIIIIII-YYYYMMDD-QQ, where
IIIIIIIIII - stands for DBID.
YYYYMMDD - Time stamp of the day the backup is generated
QQ is the hex sequence starts with 00 and has a maximum of FF

Use the following command to configure the Control file Autobackup format
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/oradata/cf _spfile_%F';

Use following command to write to an Automatic Storage Management disk group
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE DISK TO '+DGROUP1/%F';

Use the following commands to clear control file autobackup formats for a device:
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt CLEAR;

You can use SET CONTROLFILE AUTOBACKUP FORMAT command to override the configured autobackup format at session level
RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/cf_spfile_%F';
RMAN> BACKUP DATABASE;

If you have configured control file autobackup, you do not need a recovery catalog or target database control file to restore the control file in case if you lost all your control files.

Use the below command to restore the control file autobackup.
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;