-
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
Success Consists of Getting Up Just One More Time Than You've Fallen Down
Be Blessed
-
Re: Datafile Error
Originally posted by Rohit
Can we drop the tablespace and create new tablespace.
Yes you can drop the tablespace and recreate it.
HTH
Amar
"There is a difference between knowing the path and walking the path."
-
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
Success Consists of Getting Up Just One More Time Than You've Fallen Down
Be Blessed
-
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
Last edited by adewri; 03-11-2003 at 12:07 PM.
Amar
"There is a difference between knowing the path and walking the path."
-
BTW what error message are you getting on create table command ?
Amar
"There is a difference between knowing the path and walking the path."
-
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
Success Consists of Getting Up Just One More Time Than You've Fallen Down
Be Blessed
-
What is the status of the datafile ??
Amar
"There is a difference between knowing the path and walking the path."
-
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.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
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.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|