FAQ
Hi there,

Version: Oracle 10g

This is just a testing situation. I simulate the lost of the three control
files. I restore the control files from a binary copy and try to recover by
mounting the database and typing:

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

Then I get:

ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/opt/oracle/product/10.1.0/db_1/dbs/MISSING00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: '/opt/oracle/product/10.1.0/db_1/dbs/MISSING00005'

When I look at the last the dbwr process trace I see:
....
....
....
*** SERVICE NAME:() 2009-01-29 14:58:20.687

*** SESSION ID:(168.1) 2009-01-29 14:58:20.687

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/oradata/INFRA/index01.dbf'
ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory
Additional information: 3
*** 2009-01-29 15:01:09.385

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/oradata/INFRA/index01.dbf'
ORA-27037: unable to obtain file status

SVR4 Error: 2: No such file or directory
Additional information: 3
....
....

So then, I remembered that I had created a INDEXES tablespace on that date,
but the datafile is missing now (as I removed it then, but not the
tablespace - my mistake), so the tablespace is still there...

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA

---------- -------------------------------------------------- --- --- ---

0 SYSTEM YES NO YES

1 UNDOTBS1 YES NO YES

2 SYSAUX YES NO YES

4 QA_TBSP YES NO YES

5 USERS YES NO YES

7 INFRA YES NO YES

3 TEMP YES NO YES

* 11 INDEXES YES NO YES*

...but not the datafile...

SQL> select * from v$datafile;

FILE# NAME

---------- --------------------------------------------------
1 /u01/oradata/INFRA/system01.dbf
2 /usr/oradata/INFRA/undotbs01.dbf

3 /u01/oradata/INFRA/sysaux01.dbf
4 /u01/oradata/INFRA/qatbsp01.dbf

* 5 /opt/oracle/product/10.1.0/db_1/dbs/MISSING00005*
6 /u01/oradata/INFRA/users01.dbf
7 /u01/oradata/INFRA/infra03.dbf
8 /u01/oradata/INFRA/infra01.dbf

10 /u01/oradata/INFRA/infra02.dbf

I don't really need this tablespace anymore, so I want to remove it, but
obviously I can't because I'm not able to moun't the database...
Is there any way to solve this or the only solution left is restoring from
last backup?

Thanks for your help!

David

Search Discussions

  • Powell, Mark D at Feb 10, 2009 at 6:31 pm
    Try using the alter database command to remove the datafile in question
    from the database and then perform recovery.


    Once recovery is done drop the tablespace including contents.


    Mark D Powell --
    Phone (313) 592-5148

    From: oracle-l-bounce_at_freelists.org
    On Behalf Of David Pintor

    Sent: Tuesday, February 10, 2009 5:22 AM
    To: oracle-l@freelists.org
    Subject: recover from controlfile

    Hi there,

    Version: Oracle 10g

    This is just a testing situation. I simulate the lost of the

    three control files. I restore the control files from a binary copy and
    try to recover by

    mounting the database and typing:


    SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;


    Then I get:


    ORA-00283: recovery session canceled due to errors
    ORA-01111: name for data file 5 is unknown - rename to correct
    file
    ORA-01110: data file 5:
    '/opt/oracle/product/10.1.0/db_1/dbs/MISSING00005'
    ORA-01157: cannot identify/lock data file 5 - see DBWR trace
    file
    ORA-01111: name for data file 5 is unknown - rename to correct
    file
    ORA-01110: data file 5:

    '/opt/oracle/product/10.1.0/db_1/dbs/MISSING00005'


    When I look at the last the dbwr process trace I see:
    ....
    ....
    ....
    *** SERVICE NAME:() 2009-01-29 14:58:20.687
    *** SESSION ID:(168.1) 2009-01-29 14:58:20.687
    ORA-01157: cannot identify/lock data file 5 - see DBWR trace
    file
    ORA-01110: data file 5: '/u01/oradata/INFRA/index01.dbf'
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3
    *** 2009-01-29 15:01:09.385
    ORA-01157: cannot identify/lock data file 5 - see DBWR trace
    file
    ORA-01110: data file 5: '/u01/oradata/INFRA/index01.dbf'
    ORA-27037: unable to obtain file status
    SVR4 Error: 2: No such file or directory
    Additional information: 3
    ....
    ....

    So then, I remembered that I had created a INDEXES tablespace on

    that date, but the datafile is missing now (as I removed it then, but
    not the tablespace - my mistake), so the tablespace is still there...


    SQL> select * from v$tablespace;


    TS# NAME INC BIG FLA
    ---------- --------------------------------------------------
    --- --- ---
    0 SYSTEM YES NO YES
    1 UNDOTBS1 YES NO YES
    2 SYSAUX YES NO YES
    4 QA_TBSP YES NO YES
    5 USERS YES NO YES
    7 INFRA YES NO YES
    3 TEMP YES NO YES
    11 INDEXES YES NO YES

    ...but not the datafile...

    SQL> select * from v$datafile;

    FILE# NAME
    ---------- --------------------------------------------------
    1 /u01/oradata/INFRA/system01.dbf
    2 /usr/oradata/INFRA/undotbs01.dbf
    3 /u01/oradata/INFRA/sysaux01.dbf
    4 /u01/oradata/INFRA/qatbsp01.dbf
    5 /opt/oracle/product/10.1.0/db_1/dbs/MISSING00005
    6 /u01/oradata/INFRA/users01.dbf
    7 /u01/oradata/INFRA/infra03.dbf
    8 /u01/oradata/INFRA/infra01.dbf
    10 /u01/oradata/INFRA/infra02.dbf

    I don't really need this tablespace anymore, so I want to remove
    it, but obviously I can't because I'm not able to moun't the database...
    Is there any way to solve this or the only solution left is

    restoring from last backup?


    Thanks for your help!


    David

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 10, '09 at 10:21a
activeFeb 10, '09 at 6:31p
posts2
users2
websiteoracle.com

2 users in discussion

Powell, Mark D: 1 post David Pintor: 1 post

People

Translate

site design / logo © 2022 Grokbase