5 Replies Latest reply: Feb 14, 2013 7:43 PM by Hemant K Chitale RSS

    Undo Datafile Dropped

    user11982706
      OS Version : AIX 6.1
      DB Version : 11.2.0.1

      I am testing few recovery cases. I have three datafiles in UNDO tablespace and i removed two files from OS level (file 3 & 7). I have level 0 backup & level 1 & archives. Now, when i am
      selecting from some tables i am getting below error:
      SQL> update yya_objects set owner='BBB';
      update yya_objects set owner='BBB'
             *
      ERROR at line 1:
      ORA-01116: error in opening database file 7
      ORA-01110: data file 7: '/u04/oradata/CORE/undotbs02.dbf'
      ORA-27041: unable to open file
      IBM AIX RISC System/6000 Error: 2: No such file or directory
      Additional information: 3
      Additional information: 4194304
      Now, why only one file is coming in recovery mode? I have deleted two files.
      SQL> select FILE_ID,STATUS,ONLINE_STATUS from dba_data_files;
      
         FILE_ID STATUS    ONLINE_
      ---------- --------- -------
               1 AVAILABLE SYSTEM
               2 AVAILABLE ONLINE
               3 AVAILABLE ONLINE
               4 AVAILABLE ONLINE
               5 AVAILABLE ONLINE
               6 AVAILABLE ONLINE
               7 AVAILABLE RECOVER
               8 AVAILABLE ONLINE
      
      8 rows selected.
      
      SQL> select * from v$recover_file;
      
           FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
      ---------- ------- ------- ----------------------------------------------------------------- ---------- -----------
               7 OFFLINE OFFLINE FILE NOT FOUND                                                             0
      also i tried to create a new UNDO tablespace but got error for second datafile.
      SQL> create undo tablespace undotbs_new datafile '/u04/oradata/CORE/undotbs_new01.dbf' size 100M;
      create undo tablespace undotbs_new datafile '/u04/oradata/CORE/undotbs_new01.dbf' size 100M
      *
      ERROR at line 1:
      ORA-00604: error occurred at recursive SQL level 1
      ORA-01116: error in opening database file 3
      ORA-01110: data file 3: '/u04/oradata/CORE/undots01.dbf'
      ORA-27041: unable to open file
      IBM AIX RISC System/6000 Error: 2: No such file or directory
      Additional information: 3
      Additional information: 4194304
      But still only one datafile in RECOVER state. Any suggestions on this.
        • 1. Re: Undo Datafile Dropped
          mseberg
          What I would do is create a new UNDO tablespace ASAP.

          Let me check my notes and I will post how to here in a moment

          The MOS document should cover this :



          How to Change the Existing Undo Tablespace to a New Undo Tablespace [ID 431652.1]

          Another example

          http://mdesouza.wordpress.com/2009/12/13/recreating-undo-tablespace/

          Best Regards

          mseberg

          Edited by: mseberg on Feb 14, 2013 10:07 AM
          • 2. Re: Undo Datafile Dropped
            user11982706
            Hi Mseberg,

            I already tried creatinga new UNDO tablespace but got error as below:
            SQL> create undo tablespace undotbs_new datafile '/u04/oradata/CORE/undotbs_new01.dbf' size 100M;
            create undo tablespace undotbs_new datafile '/u04/oradata/CORE/undotbs_new01.dbf' size 100M
            *
            ERROR at line 1:
            ORA-00604: error occurred at recursive SQL level 1
            ORA-01116: error in opening database file 3
            ORA-01110: data file 3: '/u04/oradata/CORE/undots01.dbf'
            ORA-27041: unable to open file
            IBM AIX RISC System/6000 Error: 2: No such file or directory
            Additional information: 3
            Additional information: 4194304
            Moreover, why only one datafile in RECOVER state, while i deleted 2 files.
            • 3. Re: Undo Datafile Dropped
              mseberg
              I see.

              This should explain why :

              RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE [ID 1013221.6]

              Best Regards

              mseberg
              • 4. Re: Undo Datafile Dropped
                Levi Pereira
                user11982706 wrote:
                OS Version : AIX 6.1
                DB Version : 11.2.0.1

                I am testing few recovery cases. I have three datafiles in UNDO tablespace and i removed two files from OS level (file 3 & 7). I have level 0 backup & level 1 & archives. Now, when i am
                selecting from some tables i am getting below error:
                SQL> update yya_objects set owner='BBB';
                update yya_objects set owner='BBB'
                *
                ERROR at line 1:
                ORA-01116: error in opening database file 7
                ORA-01110: data file 7: '/u04/oradata/CORE/undotbs02.dbf'
                ORA-27041: unable to open file
                IBM AIX RISC System/6000 Error: 2: No such file or directory
                Additional information: 3
                Additional information: 4194304
                Now, why only one file is coming in recovery mode? I have deleted two files.
                SQL> select FILE_ID,STATUS,ONLINE_STATUS from dba_data_files;
                
                FILE_ID STATUS    ONLINE_
                ---------- --------- -------
                1 AVAILABLE SYSTEM
                2 AVAILABLE ONLINE
                3 AVAILABLE ONLINE
                4 AVAILABLE ONLINE
                5 AVAILABLE ONLINE
                6 AVAILABLE ONLINE
                7 AVAILABLE RECOVER
                8 AVAILABLE ONLINE
                
                8 rows selected.
                
                SQL> select * from v$recover_file;
                
                FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
                ---------- ------- ------- ----------------------------------------------------------------- ---------- -----------
                7 OFFLINE OFFLINE FILE NOT FOUND                                                             0
                also i tried to create a new UNDO tablespace but got error for second datafile.
                SQL> create undo tablespace undotbs_new datafile '/u04/oradata/CORE/undotbs_new01.dbf' size 100M;
                create undo tablespace undotbs_new datafile '/u04/oradata/CORE/undotbs_new01.dbf' size 100M
                *
                ERROR at line 1:
                ORA-00604: error occurred at recursive SQL level 1
                ORA-01116: error in opening database file 3
                ORA-01110: data file 3: '/u04/oradata/CORE/undots01.dbf'
                ORA-27041: unable to open file
                IBM AIX RISC System/6000 Error: 2: No such file or directory
                Additional information: 3
                Additional information: 4194304
                But still only one datafile in RECOVER state. Any suggestions on this.
                I am testing few recovery cases. I have three datafiles in UNDO tablespace and i removed two files from OS level (file 3 & 7). I have level 0 backup & level 1 & archives. Now, when i am
                selecting from some tables i am getting below error:
                Just Restore Files 7 and apply archivelogs.
                RMAN >
                 Restore datafile  7;
                 Recover datafile 7;
                 sql ' alter tablespace <UNDO> online';
                Regards,
                Levi Pereira

                Edited by: Levi Pereira on Feb 14, 2013 2:43 PM
                • 5. Re: Undo Datafile Dropped
                  Hemant K Chitale
                  As mseberg has adviced , you should see and follow Oracle Support note : "RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE [ID 1013221.6]"

                  You need to restore and recover both the undo datafiles.


                  Hemant K Chitale