RE: recover from controlfile

  • From: "Powell, Mark D" <mark.powell@xxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 10 Feb 2009 13:31:40 -0500

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of David Pintor
        Sent: Tuesday, February 10, 2009 5:22 AM
        To: oracle-l@xxxxxxxxxxxxx
        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
        

Other related posts: