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 10.2.0.1.0 - Production on Wed Nov 30 09:11:10 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL>startup
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
[oracle10@localhost ~]$sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 30 09:11:10 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL>startup
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
Related Post:- Undo and Rollback segment
0 comments:
Post a Comment