OraFAQ Forum: Backup & Recovery » how to recover corrupted datafile

Home » RDBMS Server » Backup & Recovery » how to recover corrupted datafile

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to recover corrupted datafile [message #130094]

Thu, 28 July 2005 02:53

harmeet.gambhir
Messages: 30
Registered: June 2005

Member

hi everybody,

i'm getting the following error on starting oracle database throught the enterprise manager console :
ORA-01122 : database file 2 failed verification check
ORA-01110 : datafile 2 'D:\oracle\oradata\new1\undotbs01.dbf'
ORA-01200 : actual file size of 72733 is smaller than correct size of 72960 blocks.

the database mounts but is not able to open..(i don't have any backup of the databse nor the datafiles)

pl help

thanks in advance..

Report message to a moderator

Re: how to recover corrupted datafile [message #130116 is a reply to message #130094]

Thu, 28 July 2005 04:32

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

>> datafile 2 'D:\oracle\oradata\new1\undotbs01.dbf'

As the name of the datafile suggest, it's a datafile attached to undo tablespace and if it is so then only one concern is there --
whether last time ur database was shutdown properly or not.

First of all at mount stage check the datafile belongs to undo tablespace or not. Use this query from sqlplus at mount stage --

SQL> select D.name " Filename", T.name " Tablespace name" from
2 V$datafile D, V$tablespace T
3 where D.TS# = T.TS#;

FILE_NAME
------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
D:\ORACLE\ORADATA\TANUDB1\SYSTEM01.DBF
SYSTEM

D:\ORACLE\ORADATA\TANUDB1\UNDOTBS01.DBF
UNDOTBS1

D:\ORACLE\ORADATA\TANUDB1\DRSYS01.DBF
DRSYS

( on my machine)

As it shows that UNDOTBS01.DBF belongs to undotbs1 ( undo tablespace ) , if it is so on ur machine then do this :

--Take the specific datafile offline

SQL> alter database datafile 'D:\ORACLE\ORADATA\TANUDB1\UNDOTBS01.DBF' offline drop;

Database altered.

-- Open the database

SQL> alter database open;

Database altered.

-- create a new undo tablespace (like this)

SQL> create undo tablespace undotbs2
2 datafile 'D:\ORACLE\ORADATA\TANUDB1\UNDOTBS02.dbf' size 100M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

and u can use ur system freely, nothing is lost.
A word of caution ---

This scenario is going to work provided

-- That datafile belongs to undo tablespace.

Regards ,
tarun

Report message to a moderator

Re: how to recover corrupted datafile [message #130129 is a reply to message #130094]

Thu, 28 July 2005 06:03

harmeet.gambhir
Messages: 30
Registered: June 2005

Member

hi..

thanks a lot tarun for ur reply..

i was succesful in connecting to the database through the process u described in ur reply..

thanks a lot again

harmeet

Report message to a moderator

Re: how to recover corrupted datafile [message #130130 is a reply to message #130129]

Thu, 28 July 2005 06:06

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

Hi harmeet,

U are very fortunate that u lost a datafile attached to undo tablespace but this is not going to happen everytime so b careful.

Take regulare backups.

Regards,
Tarun

Report message to a moderator

Re: how to recover corrupted datafile [message #130136 is a reply to message #130094]

Thu, 28 July 2005 06:22

harmeet.gambhir
Messages: 30
Registered: June 2005

Member

hi tarun..

definitely the first thing i'm going to do is to take backup of the database..

thanks a lot.

regards

harmeet

Report message to a moderator

Re: how to recover corrupted datafile [message #300573 is a reply to message #130116]

Sat, 16 February 2008 04:03

tamilinfo
Messages: 1
Registered: February 2008
Location: Chennai

Junior Member

hi everybody,

i'm getting the following error on starting oracle database throught the enterprise manager console :
ORA-01122 : database file 2 failed verification check
ORA-01110 : datafile 2 'D:\oracle\oradata\new1\undotbs01.dbf'
ORA-01200 : actual file size of 72733 is smaller than correct size of 72960 blocks.

the database mounts but is not able to open..(i don't have any backup of the databse nor the datafiles)

Last time My database was shutdown not properly

pl help

thanks in advance..

Report message to a moderator

Re: how to recover corrupted datafile [message #300601 is a reply to message #300573]

Sat, 16 February 2008 09:31

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Did you try what has been posted?

Regards
Michel

Report message to a moderator

Re: how to recover corrupted datafile [message #300637 is a reply to message #130094]

Sun, 17 February 2008 04:16

varu123
Messages: 754
Registered: October 2007

Senior Member

Does with ora-01122,the rollback segments associatd with undo tablespace automatically goes offline?
If not then ,alter tablespace undotbs offline might result ni some dependency errors.
so first you might need to offline the segments individually.

[Updated on: Sun, 17 February 2008 08:25]

Report message to a moderator

Re: how to recover corrupted datafile [message #328420 is a reply to message #130094]

Fri, 20 June 2008 02:25

rohith
Messages: 4
Registered: June 2008
Location: Bangalore

Junior Member

As u mention solution,
i done same process

while execute command

sql> alter database open;

its giving error : oracle disconnected

for these give me solution

Report message to a moderator

Re: how to recover corrupted datafile [message #328437 is a reply to message #328420]

Fri, 20 June 2008 03:23

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Don't say what you have, copy and paste it.

Have a look at alert.log and trace file.

Regards
Michel

Report message to a moderator

Re: how to recover corrupted datafile [message #328481 is a reply to message #130094]

Fri, 20 June 2008 05:41

rohith
Messages: 4
Registered: June 2008
Location: Bangalore

Junior Member

where alert.log and trace file located.....

// below shows while starting up oracle

[root@linuxorcl root]# su - oracle
[oracle@linuxorcl oracle]$ sqlplus sys/syspwd as sysdba;

SQL*Plus: Release 10.1.0.3.0 - Production on Fri Jun 20 15:58:32 2008

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 778776 bytes
Variable Size 154148328 bytes
Database Buffers 130023424 bytes
Redo Buffers 262144 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

sql>

// please give me step wise description

Report message to a moderator

Re: how to recover corrupted datafile [message #328485 is a reply to message #328481]

Fri, 20 June 2008 05:43

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Have a look at alert.log and trace file.

They are in bdump and udump directories.
Use "show parameter dump".

Regards
Michel

[Updated on: Fri, 20 June 2008 05:43]

Report message to a moderator

Re: how to recover corrupted datafile [message #328498 is a reply to message #130094]

Fri, 20 June 2008 06:00

rohith
Messages: 4
Registered: June 2008
Location: Bangalore

Junior Member

its saying " ora- 01034 : ORACLE not available"

.......

as i mention previous post msg, its showing "oracle not connected"

Report message to a moderator

Re: how to recover corrupted datafile [message #328500 is a reply to message #328498]

Fri, 20 June 2008 06:02

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Search on your server for these directories.
Try "find / -name 'alert*log' -print"

Regards
Michel

Report message to a moderator

Re: how to recover corrupted datafile [message #328521 is a reply to message #130094]

Fri, 20 June 2008 06:48

rohith
Messages: 4
Registered: June 2008
Location: Bangalore

Junior Member

its find ...... alert_orcl.log

Report message to a moderator

Re: how to recover corrupted datafile [message #328523 is a reply to message #328521]

Fri, 20 June 2008 06:49

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Now look into it.

Regards
Michel

Report message to a moderator

Re: how to recover corrupted datafile [message #649773 is a reply to message #130116]

Wed, 06 April 2016 01:54

raniji123
Messages: 1
Registered: April 2016
Location: chennai

Junior Member

Hi,

but when i do follow these below steps, after creating tablespace , i am getting an error "database not open".

My error is " actual fil size of 1530880 is smaller than correct size of 1532160 blocks". (system01.dbf)

Please assist on this as it is very urgent to resolve/

[Updated on: Wed, 06 April 2016 01:55]

Report message to a moderator

Re: how to recover corrupted datafile [message #649774 is a reply to message #649773]

Wed, 06 April 2016 02:00

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

but when i do follow these below steps,

Which ones?
Copy and paste what you do and get.
Before, Please How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

Please assist on this and post the required information as it is very urgent to solve your problem.

Report message to a moderator

Re: how to recover corrupted datafile [message #649785 is a reply to message #649774]

Wed, 06 April 2016 04:53

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

So it is not so urgent as you didn't reply in 3 hours.

Report message to a moderator

Previous Topic:	RMAN How to restore completed backup without recover archive redo log
Next Topic:	Rman backup performance issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 02:19:03 CST 2024