| very urgent ora-01157 [message #137083] |
Tue, 13 September 2005 02:34  |
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  |
Frank Naude
Messages: 4581
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 |
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 #137501 is a reply to message #137491] |
Thu, 15 September 2005 07:12 |
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 #137505 is a reply to message #137499] |
Thu, 15 September 2005 07:15 |
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
|
 
[Updated on: Thu, 15 September 2005 07:16] Report message to a moderator |
|
|
|
|
|
| Re: very urgent ora-01157 [message #327349 is a reply to message #137586] |
Mon, 16 June 2008 03:14 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
