TEST
Powered by Blogger.
Responsive Ad
Responsive Ad

Author Profile

About Me

Start Sharing

Start Sharing

Drive Traffic on Social Platforms

Blog Archive

Blog Journey

Blog Journey

Learn how to start a perfect Blog

Work Smart, Not Hard - Let's See

Make your Blogging experience faster and easiest …
Work Smart, Not Hard - Let's See
Design by - Blogger Templates | Distributed by Free Blogger Templates

Made with Love by

Material UI Template is Designed Theme for Giving Enhanced look Various Features are available Whic…

Random Posts

Ads Section

HOME > > Case 1: If undo data file lost after cleanly shutdown

Case 1: If undo data file lost after cleanly shutdown

Anup - Friday, 20 May 2011

When you are trying to start database:

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1279120 bytes
Variable Size              71306096 bytes
Database Buffers           92274688 bytes
Redo Buffers                2912256 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/DB/ANUP/undotbs01.dbf'

Steps 1 Shutdown immediate

Step 2 Set UNDO_MANAGEMENT=manual in init file.

Step 3 Mount the database in restricted mode.

SQL> STARTUP RESTRICT MOUNT

Step 4 Offline drops the lost undo data file.

SQL> alter database datafile '/DB/ANUP/undotbs01.dbf' offline drop;

Step 5 Open the database.

SQL> ALTER DATABASE OPEN

Step 6 Drop the undo tablespace which contains rollback segments to which the datafile belonged.

SQL> drop tablespace undotbs1 including contents;

Step 7 Recreate the undo tablespace.

SQL> create undo tablespace undotbs2 datafile '/DB/ANUP/undotbs02.dbf' size 100m;

Step 8 Edit the parameter file setting:

UNDO_MANAGEMENT=AUTO and UNDO_TABLESPACE=

Step 9 Shutdown and startup the the database.

Contact me

Get in Touch

Need to get touch with me? Please fill out the form with your enquiry.

Name
Anup Srivastav
Address
Lucknow - Utter Pradesh
Email
myindiandba@gmail.com
Message me