Home » RDBMS Server » Server Administration » very urgent ora-01157
very urgent ora-01157 [message #137083] Tue, 13 September 2005 02:34 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member

hii
i make a shut down to my database and delete a file in the datafiles session
i want to open again my database altough i cannot restore that file
thx

[Updated on: Tue, 13 September 2005 03:21]

Report message to a moderator

Re: very urgent ora-01157 [message #137127 is a reply to message #137083] Tue, 13 September 2005 06:59 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
$ oerr ora 01157
01157, 00000, "cannot identify/lock data file %s - see DBWR trace file"
// *Cause:  The background process was either unable to find one of the data
//         files or failed to lock it because the file was already in use.
//         The database will prohibit access to this file but other files will
//         be unaffected. However the first instance to open the database will
//         need to access all online data files. Accompanying error from the
//         operating system describes why the file could not be identified.
// *Action: Have operating system make file available to database. Then either
//         open the database or do ALTER SYSTEM CHECK DATAFILES.


Best regards.

Frank
Re: very urgent ora-01157 [message #137491 is a reply to message #137083] Thu, 15 September 2005 06:46 Go to previous messageGo to next message
Jay Munshi
Messages: 30
Registered: July 2004
Member
Dropping Oracle datafile! You cannot drop a datafile from oracle..there is not alter database drop datafile or alter tablespace drop datafile...hence you cannot drop a datafile. But there is a work around....

recreate the control file by 'alter database backup controlfile to trace;' this will create a .trc in your user_dump_dest path. Then fire 'shutdown immediate;'
delete your control files or for safety place move them on another location.
then give 'alter database nomount;'

and run the sytax given there after omitting the file that you have deleted.

then give 'alter database mount;'

then give 'alter database recover;'

and then after the recovery is completed give
'alter database open;'

Regards,
Jay Munshi
http://jaydba.tripod.com

[Updated on: Thu, 15 September 2005 06:49]

Report message to a moderator

Re: very urgent ora-01157 [message #137497 is a reply to message #137491] Thu, 15 September 2005 07:06 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
@jay
Quote:

Dropping Oracle datafile! You cannot drop a datafile from oracle..there is not alter database drop datafile or alter tablespace drop datafile...hence you cannot drop a datafile.

What am I missing, I don't see any mention of the OP saying that he dropped a datafile! He said tha he deleted a datafile....??
Smile
Jim
icon12.gif  Re: very urgent ora-01157 [message #137499 is a reply to message #137497] Thu, 15 September 2005 07:09 Go to previous messageGo to next message
Jay Munshi
Messages: 30
Registered: July 2004
Member
I agree...he dropped it from the operting system...deleted it...the point is that you can do so....but then you cant start you database normally...
Jay Munshi
http://jaydba.tripod.com

[Updated on: Thu, 15 September 2005 07:17]

Report message to a moderator

Re: very urgent ora-01157 [message #137501 is a reply to message #137491] Thu, 15 September 2005 07:12 Go to previous messageGo to next message
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
Member
Just as a side note...

Not sure if emadbsb actually said he alter drop datafile!!

However, amadbsh, what Jay said will work, but you will have lost all the information in that file (it will no longer be part of your database)

Instead you may be able to (and I do mean may be) recreate the datafile even if you don't have a backup..

Point 1 - you must have every archive(redolog) file since the datafile was created.
Point 2 - you mush NOT have recreated your controlfile since the datafile was created.

If both those things are true then you can try.

ALTER DATABASE CREATE DATAFILE 'filename';
(it will recreate the datafile using information from the controlfile, like it's location, create date and create SCN#)
ALTER DATABASE RECOVER DATAFILE;
(it will apply recovery from the create date SCN# to the current point in time - thats why you need all the redologs and archive logs)

Hope that helps.

Allie...
Re: very urgent ora-01157 [message #137504 is a reply to message #137501] Thu, 15 September 2005 07:14 Go to previous messageGo to next message
Jay Munshi
Messages: 30
Registered: July 2004
Member
yup allie...you make more sense...just go through the steps i suggested if there is no data in your datafile or allie has suggested a much better way.

regards,
Jay Munshi
http://jaydba.tripod.com
Re: very urgent ora-01157 [message #137505 is a reply to message #137499] Thu, 15 September 2005 07:15 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Hmmm, not to labour the point, but the OP does not mention the word drop and you pretty much slated him for it. You are the one who mentioned it, and I still disagree with you, you don't drop from the os, you delete it
Quote:

.he dropped it from the operation system...deleted it


could maybe read
Quote:


.he deleted it from the operation system...I guess that's kinda like dropping it even though the op NEVER MENTIONED IT AT ALL so I guess I shouldn't have either. I guess I just got mixed up. Sorry emadbsb


Smile Wink

[Updated on: Thu, 15 September 2005 07:16]

Report message to a moderator

Re: very urgent ora-01157 [message #137586 is a reply to message #137505] Thu, 15 September 2005 10:53 Go to previous messageGo to next message
Jay Munshi
Messages: 30
Registered: July 2004
Member
hmm...
Re: very urgent ora-01157 [message #327349 is a reply to message #137586] Mon, 16 June 2008 03:14 Go to previous messageGo to next message
abhishek00401
Messages: 2
Registered: February 2008
Junior Member
ora-01157-------------

the error states that
ORA-01157 cannot identify/lock datafile string - see DBWR trace file


this occurs when the control file cannot identify the required datafile, so the problem might be of two reasons,

1) Either the control file has been corrupted (or)
2) the datafile has been corrupted

so to resolve this issue try to restore the backup of the datafile and recover the database if the datafile is corrupted


or else

recreate a controlfile with all the datafiles and redo log files at nomount state

sql> startup nomount
sql>CREATE CONTROLFILE REUSE DATABASE "indb" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 5 'D:\ORACLE\ORADATA\INDB\REDO030.LOG' SIZE 10M,
GROUP 6 'D:\ORACLE\ORADATA\INDB\REDO010.LOG' SIZE 10M,
GROUP 4 'D:\ORACLE\ORADATA\INDB\REDO020.LOG' SIZE 10M
DATAFILE
'D:\ORACLE\ORADATA\INDB\SYSTEM01.DBF',
'D:\ORACLE\ORADATA\INDB\RBS01.DBF',
'D:\ORACLE\ORADATA\INDB\USERS01.DBF',
'D:\ORACLE\ORADATA\INDB\TEMP01.DBF',
'D:\ORACLE\ORADATA\INDB\INDX01.DBF',
'D:\ORACLE\ORADATA\INDB\DR01.DBF',
'D:\ORACLE\ORADATA\INDB\SYSAUX01.DBF',
'D:\ORACLE\ORADATA\INDB\T1.DBF',
'D:\ORACLE\ORADATA\INDB\T1A.DBF',
'D:\ORACLE\ORADATA\INDB\RMAN01.DBF'
CHARACTER SET WE8ISO8859P1
;

sql> alter database open;

####### THIS DEFINITELY WORKS#########

ENJOIIIIIIII
Re: very urgent ora-01157 [message #327355 is a reply to message #327349] Mon, 16 June 2008 03:33 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Why resurrect a 2.5 year old thread?
Previous Topic: TO drop a datafile
Next Topic: SYSTEM TABLESPACE RESIZE
Goto Forum:
  


Current Time: Sun Dec 04 21:06:47 CST 2016

Total time taken to generate the page: 0.09266 seconds