6 Replies Latest reply on Jan 31, 2019 12:34 PM by AJ

    Missing file .. No standby, no backup ..

    3665213

      Hi all,

       

      I have an oracle database in 11.0.2.4 which contains about 4000 tables and 8800 indexes. A few days ago, I created a file and I made a mistake on his name.

      And instead of removing it right away, I put it offline. It is since in recover mode.

      So I recreated the controlfile (alter database backup controlfile to trace) by removing  the part concerning this recordir recover. Unfortunately, he went "missing".     

       

      FILE # ---------- NAME -------------------------------------------------- ------------------------------ STATUS -------      

      7                     /opt/oracle/product/11.2.0/dbhome_2/dbs/MISSING00007                     RECOVER

       

      and on the shell:

       

      ORA-01673: data file 7 has not been identified

      ORA-01111: name for data file is unknown - rename to correct file

      ORA-01110: data file 7: '/opt/oracle/product/11.2.0/dbhome_2/dbs/MISSING00007'

       

      Since, I can not make consistent backup, and most importantly, I can not switch to a standby (this database must be migrated to ODA).

      I am proposed to rebuild in tablespace where this file is filled by moving all datafiles to a new tablespace.

      Each manipulation leads to a new problem.

       

       

      Do you have any idea how I can migrate this now incoherent base to a cleaner base on my ODA?

        • 1. Re: Missing file .. No standby, no backup ..
          Dude!

          You cannot recover a tablespace with missing datafiles. The controlfile in your case has data in the data dictionary but no information about the datafile in question and you cannot even restore the "missing" datafile. You will have to restore the database or tablespace from an earlier version of the controlfile, prior to dropping the datafile or tablespace.

           

          You can however make a RMAN backup by excluding the tablespace, for example:

           

          RMAN> configure exclude for tablespace example;

          RMAN> show exclude;

          RMAN> backup database;

           

          RMAN> backup database noexclude;

          RMAN> configure exclude for tablespace example clear;

           

          https://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmconfa.htm#BRADV140

           

          If the tablespace is offline, you can also use:

          RMAN> backup database skip offline;

          • 2. Re: Missing file .. No standby, no backup ..
            top.gun

            If you have no backup to restore to a point in time, then the alternative solution is to datapump the tablespace to a new tablespace.

            • 3. Re: Missing file .. No standby, no backup ..
              3665213

              Hi

               

              I'm going to test all the purposed answers.

               

              Thank you.

              • 4. Re: Missing file .. No standby, no backup ..
                3665213

                What about :

                 

                alter database datafile 'datafile_X_MISSING' offline for drop;

                alter database open;

                select file_id from dba_data_files where file_name = 'datafile_X';

                delete from file$ where file#=<value FILE_ID should be X>;

                 

                 

                I've to backup all my database before. And restore it after ....

                 

                Really dangerous ....

                • 5. Re: Missing file .. No standby, no backup ..
                  Dude!

                  It is not possible for RMAN to restore and recover a missing datafile. If the datafile was in use by a tablespace and is missing, the tablespace is essentially destroyed and requires restore and recovery. You can restore an older controlfile, restore and recover the tablespace. If you don't have the necessary backup and the database runs in noarchivelog mode, you're out of luck. I'm not aware that there is an option to ignore the missing datafile and use the remaining tablespace regardless.

                   

                  Regarding your last response to use "alter database datafile ... offline for drop". According to the documentation at https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles004.htm#ADMIN11427  it is used to take a datafile offline when the database is in NOARCHIVELOG mode. It allows you to open the database and drop the tablespace.

                   

                  Perhaps you find DBPITR After Dropping a Tablespace useful to explain the scenario.

                   

                  You do not necessarily need to open a database to use RMAN or the OS to perform a backup and restore. If the database is shut down, you can use the OS to copy the files as is. You can also mount the database and use RMAN to backup the data, though you will have to tell RMAN to exclude the tablespace as previously mentioned.

                  • 6. Re: Missing file .. No standby, no backup ..
                    AJ

                    If you have all archived redo logs since the file was added you could try to recreate it.

                     

                    e.g

                     

                    SQL> alter database create datafile '/opt/oracle/product/11.2.0/dbhome_2/dbs/MISSING00007  ' as '<some_path_>/original_file_name.dbf' ;

                     

                    AJ