Datafile Error

Printable View

03-11-2003, 09:20 AM
Rohit

Datafile Error

Hi all,
We have a test database , the one and only one datafile of the users tablespace was replaced with a old backupfile backedup before the resetlogs. now we are not able to recover this file. So what can be done to continue using the tablespace. We added another datafile. but still users are not able to create tables onm this datafile. it stills points the error to the corrupt datafile only. Can we drop the tablespace and create new tablespace. Or is there any other method , pls do advice

thanks
03-11-2003, 09:34 AM
adewri

Re: Datafile Error

Quote:

Originally posted by Rohit
Can we drop the tablespace and create new tablespace.

Yes you can drop the tablespace and recreate it.

HTH
03-11-2003, 11:26 AM
Rohit

Why does the tablespace not continue to work .Becaue i have added a new datafile and only creatung new tables. Its obvious we cannot access the old ones, but why creation of new segments generating errors refering to the corrupted datafile.

Thanks
03-11-2003, 11:52 AM
adewri

Code:

Tablespace user before adding datafile Tablespace user after adding datafile --------- --------------------- | | | | | | | | | | | | | | | | | | | | | | | | --------- --------------------- Datafile Two Datafiles ----- ----- ----- | | | | | | | | | | | | | | | | | | ----- ----- ----- ___________________________________________________________________________ USER Tablespace USER Tablespace You try to create a table This is --------- --------------------- corrupt |_ | This is |_ | ----->|_| | still ----> |_| | | | corrupt | | | | | | | | | | | | | | | | | | --------- ---------------------

I believe that ORACLE is trying to access the same block for creating the table which is corrupt.

HTH
03-11-2003, 12:05 PM
adewri

BTW what error message are you getting on create table command ?
03-11-2003, 12:26 PM
Rohit

ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: 'D:\ORACLE\ORADATA\GLOBAL\USERS01.DBF'

but i see one more thing if i loginto the sever form a client and create a table it is successfully creating , sitting in the same system and creating does not allow .

thanks
03-11-2003, 12:45 PM
adewri

What is the status of the datafile ??
03-12-2003, 12:06 AM
SANJAY_G

Since you cannot recover that datafile DROP the tablespace and create again. That is the correct way.
But if you still want keep the corrupt file do OFFLINE DROP for that file and add a new datafile.
03-12-2003, 02:39 AM
abhaysk

Sanjay:

he is able to create tables from client but not from server...will that suggest that datafile may be corrupted...i guess the datafile have gone offline due som reason...
S it may be corrupted but in that case, how is he able to create tables from client?

as amar said , post the status of datafile...

select status from DBA_DATA_FILES where FILE_NAME='D:\ORACLE\ORADATA\GLOBAL\USERS01.DBF'

Abhay.
03-12-2003, 02:48 AM
SANJAY_G

Quote:

Originally posted by abhaysk
Sanjay:

he is able to create tables from client but not from server...will that suggest that datafile may be corrupted...

No it doesn't. The datafile is unusable because he restored it from the backup taken before the RESETLOGS.

Quote:

i guess the datafile have gone offline due som reason...
S it may be corrupted but in that case, how is he able to create tables from client?

call it corrupted or unusable, fact is he can't create any segment in that file, neither from client connection nor from local connection.
03-12-2003, 02:54 AM
abhaysk

Quote:

Originally posted by sanjay_g

No it doesn't. The datafile is unusable because he restored it from the backup taken before the RESETLOGS.

this is wat Rohit said...

Quote:

Originally posted by Rohit

but i see one more thing if i loginto the sever form a client and create a table it is successfully creating , sitting in the same system and creating does not allow .

thanks
03-12-2003, 03:00 AM
SANJAY_G

There is no way one can create segments in offline datafile, and I believe it is offline. Let's wait for his reply.
03-12-2003, 03:05 AM
adewri

Quote:

Originally posted by SANJAY_G
There is no way one can create segments in offline datafile, and I believe it is offline. Let's wait for his reply.

Hi Sanjay,

Here rohit has already added a datafile to the USERS tablespace and hence able to create the tables from the client.
What i find confusing is that, he is able to create tables from client connection but not from the server but why ??

Regards
03-12-2003, 03:21 AM
aspdba

gurus..pls correct me if I am wrong.

1. Dropping the datafile and recreating a new one , wont you loose the data?

2.Wont "recover tablespace *tablespace_name*;" work for such a scenario?

3.Export the entire schema and import it to new user with new datafiles..would this help?

Thank you
03-12-2003, 03:22 AM
SANJAY_G

Quote:

Originally posted by adewri

What i find confusing is that, he is able to create tables from client connection but not from the server but why ??

Regards

Exactly. That makes me wonderring too. But may be, he is missig something and it's not just the local connection or remote connection.
03-12-2003, 03:27 AM
SANJAY_G

Quote:

Originally posted by aspdba
gurus..pls correct me if I am wrong.

1. Dropping the datafile and recreating a new one , wont you loose the data?

Of course, you will lose the data. It is advisable only if the datafile is unrecoverable and you are prepared to lose the data.

Quote:

2.Wont "recover tablespace *tablespace_name*;" work for such a scenario?

He can't recover the tablespace because the backup he has is taken before RESETLOGS.

Quote:

3.Export the entire schema and import it to new user with new datafiles..would this help?

Thank you

If datafile is offline you can't export the objects in it.

HTH
03-12-2003, 03:28 AM
adewri

Quote:

1. Dropping the datafile and recreating a new one , wont you loose the data?

Yes.

Quote:

2.Wont "recover tablespace *tablespace_name*;" work for such a scenario?

No, datafile restored is before resetlog.

Quote:

3.Export the entire schema and import it to new user with new datafiles..would this help?

Datafile is corrupted or unusable, so cannot export.
03-12-2003, 03:30 AM
adewri

Sanjay you beated me by a minute :D
03-13-2003, 12:44 PM
Rohit

Hi all ,
Thanks for all the replies. But i still dont understand the problem?
I want to expalin it again.
We copied a datafile called users01.dbf from a backup which was taken before resetlogs. This datafile was the only datafile in the Users tablespace. So we could not recover it and we do not have any other copy. So its obvious we were not able to browse any records of the users nor able ot create new tables. So we added a new datafile ot the existing tablespace without dropping the Tablespace and the corrupt datafile.
Now we have tow datafile users02.dbf and users01.dbf(Corrupted) in the tablespace users. We try to create tables from the system whihc has the database , it does not allow pointing to the error that users01.dbf is not accessible But to our surprise we were able to create tables from another machine . How we came ot know this was we craeted a recovery catalog for a system in this tablespace and all tables get created . We also tried creating a test table from anotehr client and it works , but tables are not getting created from the server. Its so confusing . Pls Do Advice.

Thanks
03-14-2003, 08:24 AM
AJW_ID01

I don't believe it is possible to create a table from one place and not from another (server/client) - unless a different userid is being used, that has a setting of a different default tablespace that is available.

That's the only explanation 'I' can think of.

- Tony.
03-14-2003, 08:27 AM
AJW_ID01

Really though, overall, I don't understand why you didn't just drop the tablespace - it's obviously not SO important... and just recreate it, as advised.

Throwing good datafiles after bad can just never work so well.

- T.