2 Replies Latest reply: Jul 4, 2013 5:34 PM by Jolly Roger RSS

    Non-critical datafiles lost ; No backup available

    Max

      DB version: 11.2.0.3

      Platform : RHEL 5.8

       

      We have lost 5 non-critical db files in our DB. Confirmed the loss at OS level by running ls command.

      These 5 datafiles belong to 3 tablespaces. We don't have RMAN Backups or archive logs . The DB went down ( maybe due to the loss of these datafiles). Luckily the lost datafiles don't belong to SYSTEM, SYSAUX , UNDO or Temp tablespaces.

       

      Now , we can't open it. How can we bring up the DB ?

       

      BTW. v$recover_file.online column cannot be queried. I get the weird error ORA-00936 as show below. Seems to be a bug

       

      $ sqlplus  /  as sysdba

       

      SQL*Plus: Release 11.2.0.3.0 Production on Thu Jul 4 13:13:37 2013

       

      Copyright (c) 1982, 2011, Oracle.  All rights reserved.

       

      Connected to:

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

       

      SQL> select open_mode from v$database;

       

      OPEN_MODE

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

      MOUNTED

       

      SQL>

      SQL> alter database open;

      alter database open

      *

      ERROR at line 1:

      ORA-01157: cannot identify/lock data file 79 - see DBWR trace file

      ORA-01110: data file 79: '/u07/dataqa/oradata/hkcms/cis_data_01.dbf'

       

       

      ---v$recover_file.online column cannot be queried

      SQL> desc v$recover_file

      Name                                      Null?    Type

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

      FILE#                                              NUMBER

      ONLINE                                             VARCHAR2(7)

      ONLINE_STATUS                                      VARCHAR2(7)

      ERROR                                              VARCHAR2(18)

      CHANGE#                                            NUMBER

      TIME                                               DATE

       

      SQL>

      SQL> select FILE# , ONLINE , ONLINE_STATUS, error from v$recover_file;

      select FILE# , ONLINE , ONLINE_STATUS, error from v$recover_file

                     *

      ERROR at line 1:

      ORA-00936: missing expression

       

       

      --- What the hell ???

       

      SQL> select online from v$recover_file;

      select online from v$recover_file

             *

      ERROR at line 1:

      ORA-00936: missing expression

       

       

       

      SQL> select FILE# , ONLINE_STATUS, error from v$recover_file;

       

           FILE# ONLINE_ ERROR

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

              79 ONLINE  FILE NOT FOUND

              82 ONLINE  FILE NOT FOUND

              83 ONLINE  FILE NOT FOUND

              89 ONLINE  FILE NOT FOUND

              94 ONLINE  FILE NOT FOUND

            

       

      5 rows selected.

        • 1. Re: Non-critical datafiles lost ; No backup available
          Paul M.

          We don't have RMAN Backups or archive logs .

          Well, I hope that's not a production database... anyway, if your purpose is to open the database, you can try, for each lost datafile

           

          SQL> alter database datafile '<file name>' offline drop;

           

          then try to open the database.

          • 2. Re: Non-critical datafiles lost ; No backup available
            Jolly Roger

            ONLINE is a keyword so you should use something like this

             

            select a.FILE# , a."ONLINE" , a.ONLINE_STATUS, a.error from v$recover_file a;

             

            You can offline the datafiles and open the database

             

            Create a new tablespace and move all the objects to the new tablespace and drop the old tablespace

            once the old tablespace is dropped you can rename the tablespace

             

            ALTER TABLESPACE <Old_name> RENAME TO <New_name>;