Recover Undo Tablespace

Undo tablespace is used to store the post modification data of every transaction for rollback or read consistency or for recovery in case of abort. All system transactions will use the rollback segments created in the system tablespace. It cannot be used by other schema operations, they should use the undo tablespace. So an Undo tablespace is a must for any transaction to occur. So, recovery of undo tablespace is more important in case of undo tablespace lost. There are few steps to recover undo tablespace:

[oracle10@localhost ~]$export_SID=luck
[oracle10@localhost ~]$sqlplus "/as sysdba"

SQL*Plus: Release - Production on Wed Nov 30 09:11:10 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to an idle instance.

ORACLE instance started.

Total System Global Area   314572800 bytes
Fixed Size                            1219160 bytes
Variable Size                       96470440 bytes
Database Buffers                213909504 bytes
Redo Buffers                         2973696 bytes
Database mounted.
ORA-01157 :cannot identify/lock datafile 2 - see DBWR trace file
ORA-01110 :datafile 2: '/opt/luck/undotbs.dbf'

we cannot open the database by taking the datafile offline but we need to use 'OFFLINE FOR DROP'.Datafile once marked offline for drop can never be brought online.

SQL>alter database datafile 2 offline for drop;
Database altered.

SQL>alter database open;
Database altered.

SQL>drop tablespace undotbs including contents and datafiles;
Tablespace dropped.

Now we need to create another undo tablespace for the transactions to use and once the new undo tablespace is created, shutdown the database and edit the init.ora file and change the  parameter undo_tablespace=new undo_tablespace

SQL>create undo tablespace undotbs1 datafile '/opt/luck/undotbs1.dbf' size 20m autoextend on;
Tablespace created.

SQL>shutdown immediate;

In simple steps:
a) startup
b) error ora:01157
    error ora:01110
c) alter system set undo_management = manual scope=spfile
d) shutdown immediate
e) startup 

f ) drop undo tablespace
g) Create UNDO Tablespace UNDOTBS datafile ...
h) alter system set undo_management = auto scope=spfile
i) Shutdown
j) Startup Database

People who read this post also read :


Post a Comment


Twitter Delicious Facebook Digg Stumbleupon Favorites More