Home » RDBMS Server » Backup & Recovery » ORA-0036: file 57 can not be read - Controlfile Recreate Not of Help (Oracle 9i R2 on HP-UX)
ORA-0036: file 57 can not be read - Controlfile Recreate Not of Help [message #488062] Tue, 04 January 2011 08:52 Go to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hi

I cloned PROD database and created DEV database from it using the rman

Since this is a regular activity for us I recreated controlfile using saved version of controlfile as
"CREATE CONTROLFILE set DATABASE "DEV" RESETLOGS  NOARCHIVELOG"

Then opened the database with 'resetlogs' and everything went fine

However application users started getting errors like
ORA-0036: file 57 can not be read at this time


I checked the restore logs and found that file no 57 was added at PROD database before backup and we were not informed
This file was restored during clone but then since we used the "saved" version of "create controlfile" script which did not have entry for the newly added datafile it produced an error

I saw that physically the file '/u06/oradata/index11.dbf' was restored
So added entry of that file in the controlfile, recreated the controlfile and opened the database with 'resetlogs' again

Alas!! the file '/u06/oradata/index11.dbf' got added as file_id 58 and the issue with file_id 57 remain the same

Now I am stuck up with the error "ORA-0036" and datafile entry "/opt/oracle/product/920/dbs/MISSING00057" in dba_data_files

Please advice

Regards,
OraKaran





Re: ORA-0036: file 57 can not be read - Controlfile Recreate Not of Help [message #488063 is a reply to message #488062] Tue, 04 January 2011 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
create DEV again including FILE 57 this time
Re: ORA-0036: file 57 can not be read - Controlfile Recreate Not of Help [message #488068 is a reply to message #488063] Tue, 04 January 2011 09:11 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello BlackSwan

Do you mean I will start from step 1 i.e restore and recover from PROD backup?

Don't we have any other option where we can only prevent the database from trying to read file_id 57?

I tried to drop the file but it wasn't successful only 'offline drop' was successful but it could not stop database trying to access it thus producing application errors

Neither do I can drop the tablespace.

Can't I swap file 58 with 57 and then drop unwanted one?
File_id  File_name
57       /opt/oracle/product/920/dbs/MISSING00057
58       /u06/oradata/index11.dbf


Regards,
OraKaran

Re: ORA-0036: file 57 can not be read - Controlfile Recreate Not of Help [message #488071 is a reply to message #488068] Tue, 04 January 2011 09:14 Go to previous messageGo to next message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Hello BlackSwan

Added to above questions please suggest if I can move the datafile to entirely new tablespace and 'offline drop' the file and the drop the tablespace itself as a last option?

Regards,
OraKaran
Re: ORA-0036: file 57 can not be read - Controlfile Recreate Not of Help [message #488075 is a reply to message #488071] Tue, 04 January 2011 09:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Is FILE 57 part of an existing tablespace?
If so, then dropping just this file leaves a "hole" in tablespace which is fatal error.
DROP the DEV database & create again from the start of the clone process.
Re: ORA-0036: file 57 can not be read - Controlfile Recreate Not of Help [message #488088 is a reply to message #488062] Tue, 04 January 2011 11:15 Go to previous message
OraKaran
Messages: 183
Registered: March 2008
Location: United Kingdom
Senior Member
Yes BlackSwan

File 57 belongs to existing tablespace

Anyways I have restarted the process from step1

Thanks for your help

Regards,
OraKaran
Previous Topic: Recovery ....
Next Topic: When was last full backup?
Goto Forum:
  


Current Time: Fri Mar 29 05:36:13 CDT 2024