Printable View
Hi!
Today when I started our development server, oracle failed to open a datafile giving error ora - 01157 'cannot identify/lock datafile'.
We don't have backup of datafiles, so can some thing be done or will I have to created the new database and imort from the latest dump.
Also can somebody tell the possible reasons for why this happened so as to avoid such problem in future.
Thanks.
Shruti
[from Metalink]
Problem Explanation:
====================
Oracle tries to open all datafiles when you issue a STARTUP or ALTER DATABASE
OPEN. If a datafile cannot be found at this point, an ORA-01157 results.
There are two main possible causes for an ORA-01157 on startup:
1. The datafile does exist, but Oracle cannot find it.
1.A) It may have been renamed at the operating system level, moved to a
different directory, disk drive, etc. In this case, simply restoring the
datafile to its original name and location will solve the problem.
1.B) You intentionally moved the datafile to a different location, but forgot
to rename it at Oracle level. For example, you may have decided to move
the index tablespace datafiles to another disk to improve performance, but
forgot to rename them inside Oracle. In this case, you must rename the
datafile(s) from within Oracle.
2. The datafile has been physically removed or damaged to an extent that
Oracle cannot recognize it anymore.
In this case, that datafile is lost and the solution depends on the type of
datafile involved, i.e., on the tablespace to which the datafile belongs.
Solution Description:
=====================
There are two types of solutions for an ORA-01157:
I. THE DATAFILE IS LOST OR TOO DAMAGED
--------------------------------------
In this case, the solution depends on the tablespace to which the datafile
belongs. Look for a Solution Reference matching the type of datafile lost.
II. THE DATAFILE HAS SIMPLY BEEN MOVED OR RENAMED
-------------------------------------------------
If you originally wanted to change the name/location of the file, look for the
Solution Reference on how to rename datafiles inside Oracle. If the file has
been moved or renamed by mistake, simply restore it to its original
name/location and startup the database.
Thanks
Well the file is very much there and nobody tried to rename it. On looking thru the DBWR trace file I found it says that the header information is invalid. so does it means the file is lost. Is there any way by which oracle can auto-generate the datafile.
Database is 8i running on Win2000
Shruti
well, no generic solution so I guess we'll have to search :
1) issue a startup mount, and query :
select name from v$datafile;
now compare the filenames to your files, if one is different, here is your problem
2) did you migrate/convert/restore your DB or resize your datafile ???
Hi!
Files when compared were all in tune with were they existed and I didn't tried fiddle with any of the datafiles.
Shruti
Hi Shruti, 1st May 2001 18:53 hrs chennai
here is a slution for your problem if you are DB is in
running in Archivelog Mode.
If your DB is running in NOARCHIVE LOG MODE then you have no other option than restoring the DB from the last Cold Back up.
I would appreciate in future if you always mention along with the problem OS-Oracle Version-DB Mode etc.
============METHOD-I=============
You have to note that the file you are going to restore does not belong to SYSTEM or RB TS.
I)Query V$RECOVER_FILE to see d/files needing recovery.Column to view STATUS.
II)View V$ARCHIVED_LOG for viewing the list of all Archived logs.
III)View V$RECOVERY_LOG to view all the required archive logs for your D/files.
Now you will know what archives required for recovering the particular datafile.
IV)Ensure that the datafile to recover is offline so your case is offline.
Sometimes the last datafile while DB up then we have to bring the TS of the particular datafile OFFLINE IMMEDIATE.
V)Create a simillar datafile in the same name as the lost datafile.
>alter database create datafile '/disk2/data/df2.dbf' as '/disk1/data/df2.dbf'
VI)"recover" the DB.
VII)Check the log files are in proper dest as mentioned in LOG_ARCHIVE_DEST parameter.
============Method-II=================
This method wont workout since you say you dont have d/file bck.
1) startup up the database with mount command
svrmgrl>startup mount
2) Take the missing datafile offline.
svrmgrl> alter database datafile '\disk1\test.dbf'
offline;
note: you may need to use the offline immediate syntax.
Using this syntax requires that you recover all datafiles in this tablspace.
4) Open the database
Alter Database open;
5) Restore the damaged files from a backup.
6) Recover datfiles with redo log files.
svrmgrl> recover datafile '\disk1\test.dbf'
or
svrmgrl> recover tablspace 'TS1'
Can use Ent Man.
7) Place datafile on line.
svrmgrl>Alter tablespace TS1 online;
===========
Cheers
Padmam