DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ora 01157

  1. #1
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Unhappy

    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

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    [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.

  3. #3
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81

    Unhappy

    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

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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 ???

  5. #5
    Join Date
    Nov 2000
    Location
    New Delhi, India
    Posts
    81
    Hi!

    Files when compared were all in tune with were they existed and I didn't tried fiddle with any of the datafiles.

    Shruti

  6. #6
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Option

    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


    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width