Home » RDBMS Server » Backup & Recovery » how to recover corrupted datafile
how to recover corrupted datafile [message #130094] Thu, 28 July 2005 02:53 Go to next message
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..
Re: how to recover corrupted datafile [message #130116 is a reply to message #130094] Thu, 28 July 2005 04:32 Go to previous messageGo to next message
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
Re: how to recover corrupted datafile [message #130129 is a reply to message #130094] Thu, 28 July 2005 06:03 Go to previous messageGo to next message
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
Re: how to recover corrupted datafile [message #130130 is a reply to message #130129] Thu, 28 July 2005 06:06 Go to previous messageGo to next message
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
Re: how to recover corrupted datafile [message #130136 is a reply to message #130094] Thu, 28 July 2005 06:22 Go to previous messageGo to next message
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
Re: how to recover corrupted datafile [message #300573 is a reply to message #130116] Sat, 16 February 2008 04:03 Go to previous messageGo to next message
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..

Re: how to recover corrupted datafile [message #300601 is a reply to message #300573] Sat, 16 February 2008 09:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try what has been posted?

Regards
Michel
Re: how to recover corrupted datafile [message #300637 is a reply to message #130094] Sun, 17 February 2008 04:16 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: how to recover corrupted datafile [message #328437 is a reply to message #328420] Fri, 20 June 2008 03:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: how to recover corrupted datafile [message #328481 is a reply to message #130094] Fri, 20 June 2008 05:41 Go to previous messageGo to next message
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


Re: how to recover corrupted datafile [message #328485 is a reply to message #328481] Fri, 20 June 2008 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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"
Re: how to recover corrupted datafile [message #328500 is a reply to message #328498] Fri, 20 June 2008 06:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search on your server for these directories.
Try "find / -name 'alert*log' -print"

Regards
Michel
Re: how to recover corrupted datafile [message #328521 is a reply to message #130094] Fri, 20 June 2008 06:48 Go to previous messageGo to next message
rohith
Messages: 4
Registered: June 2008
Location: Bangalore
Junior Member
its find ...... alert_orcl.log



Re: how to recover corrupted datafile [message #328523 is a reply to message #328521] Fri, 20 June 2008 06:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now look into it.

Regards
Michel
Re: how to recover corrupted datafile [message #649773 is a reply to message #130116] Wed, 06 April 2016 01:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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.

Re: how to recover corrupted datafile [message #649785 is a reply to message #649774] Wed, 06 April 2016 04:53 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

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

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


Current Time: Fri Dec 09 19:50:07 CST 2016

Total time taken to generate the page: 0.26454 seconds