Moneycontrol Brokerage Recos

Thursday, February 28, 2013

Recover UNDO Tablespace when No Backups available


Here I'll show you how to make our Oracle database up and running after loosing UNDO tablespace/UNNDO datafile at OS level. First of all to simulate this situation let's us shutdown the database and delete the undotbs01.dbf file manually from OS level.

After droping the UNDO datafile then following error will appear on your screen that you have lost the UNDO tablespace's datafile and is no longer available.

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             654311888 bytes
Database Buffers          411041792 bytes
Redo Buffers                4603904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'E:\APP\RAJKUMAR\ORADATA\ORCL\UNDOTBS01.DBF'
 SQL>

2nd step would be to make undo management parameter to manual from automatic in the parameter file.

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>

SQL> alter system set undo_management=manual scope=spfile;
System altered.

To make it effective, we have to restart the database instance.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             654311888 bytes
Database Buffers          411041792 bytes
Redo Buffers                4603904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: 'E:\APP\RAJKUMAR\ORADATA\ORCL\UNDOTBS01.DBF'
SQL>

Now you can check here undo management.

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------
undo_management                      string      MANUAL
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1
SQL>

SQL> alter database datafile 3 offline drop;
Database altered.

SQL> alter database open;
Database altered.

SQL> select name from v$tablespace; 
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
DEMO_DATA
DEMO_INDX
MIGRATION_DATA
TRIRIGAQA_DATA
TRIRIGAQA_INDX

10 rows selected.

SQL> drop tablespace undotbs1;
Tablespace dropped.

SQL> create undo tablespace undotbs1
  2  datafile 'e:\app\rajkumar\oradata\orcl\undotbs01.dbf' size 2048m autoextend on next 10m maxsize unlimited; 
Tablespace created.

SQL> alter system set undo_management=auto scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             654311888 bytes
Database Buffers          411041792 bytes
Redo Buffers                4603904 bytes
Database mounted.
Database opened.
SQL> 

Hope it would help !

3 comments:

  1. Hiya! I know this is kinda off topic however I'd figured I'd ask.

    Would you be interested in trading links or maybe guest writing a blog post or vice-versa?
    My site discusses a lot of the same subjects as yours and I feel we could greatly benefit from each other.

    If you are interested feel free to shoot me an email. I look forward to hearing from you!

    Excellent blog by the way!

    Feel free to visit my webpage: plus de twitter followers
    My site: acheter retweet

    ReplyDelete
  2. Heya i am for the first time here. I found this board and I find It really useful & it
    helped me out much. I hope to give something back and help others like
    you aided me.

    my webpage ... acheter followers
    Also see my web site: acheter follower

    ReplyDelete
  3. It's amazing to go to see this web site and reading the views of all colleagues on the topic of this post, while I am also zealous of getting experience.

    Also visit my weblog - Achat Retweet pas cher

    ReplyDelete