9 Replies Latest reply: Aug 10, 2012 7:23 AM by Mustafa KALAYCI RSS

    Accidentally deleted DBF file

    Suri
      Hi,

      I have installed Oracle 11g in my laptop for practice purpose. (Operating system is windows7).

      Accidentally I've deleted one DBF file (which was created by me - file name is VIJI.DBF ). Now when I was logging to database, I'm getting error like
       ORA-01033: ORACLE initialization or shutdown in progress
       Process ID :0
       Session ID: 0 Serial number:0
      After that I executed below commands from SQl*Plus
        conn SYS as SYSDBA
        Password : *******
      
        SQL> SHUTDOWN IMMEDIATE;
                 ORA-01109: database not open
      
      Database dismounted.
      ORACLE instance shut down.
      
      -- After that I've excuted STARTUP command
      
      SQL> STARTUP;
      ORACLE instance started.
      
      Total System Global Area---
      ----------------------
      ---------------------
      Database mounted.
      
      ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
      ORA-01110: data file 6: 'C:\VIJI.DBF'
      Please help me to resolve this issue.

      Many thanks for the help. Kindly let me know if you need any further information.

      Cheers,
      Suri
        • 1. Re: Accidentally deleted DBF file
          Mustafa KALAYCI
          shutdown immediate; -- if you have trouble with that, run shutdown abort;
          startup mount;
          alter database datafile 6 offline;
          alter database open;
          that will help you open your database, after that you need to restore and recover your datafile from backups.

          why you had these ? because of deletion of datafile, oracle can not open database properly. to open a database oracle must be sure that everything is ok!

          Edited by: Mustafa KALAYCI on Aug 10, 2012 1:54 AM
          • 2. Re: Accidentally deleted DBF file
            saurabh
            you need to do is restore the datafile from backup and recover the datafile.
            • 3. Re: Accidentally deleted DBF file
              Suri
              Mustafa KALAYCI wrote:
              shutdown immediate; -- if you have trouble with that, run shutdown abort;
              startup mount;
              alter database datafile 6 offline;
              alter database open;
              that will help you open your database, after that you need to restore and recover your datafile from backups.

              why you had these ? because of deletion of datafile, oracle can not open database properly. to open a database oracle must be sure that everything is ok!

              Edited by: Mustafa KALAYCI on Aug 10, 2012 1:54 AM
              Hi Mustafa, Saurabh

              Thank you very much for the reply.

              I've executed above commands and got below error.
              ORA-01145: offline immediate disallowed unless media recovery enabled
              I'm a developer, and I dont know how to restore the datafile. If it simple let me know the steps. Else, I'll reinstall the database again.

              Cheers,
              Suri
              • 4. Re: Accidentally deleted DBF file
                Mustafa KALAYCI
                hmm I guess, your database is not in archivelog mode. so you can not open your database as i said, do you have backups ? can you connect with RMAN ?
                • 5. Re: Accidentally deleted DBF file
                  saurabh
                  your database is not in archive log mode. So that is why you are getting this error. in order to open your database do the following step

                  1. startup mount

                  2. alter database datafile 6 offline drop;

                  3. alter database open
                  • 6. Re: Accidentally deleted DBF file
                    Suri
                    saurabh wrote:
                    your database is not in archive log mode. So that is why you are getting this error. in order to open your database do the following step

                    1. startup mount

                    2. alter database datafile 6 offline drop;

                    3. alter database open
                    Hi Saurabh,

                    Thanks for the solution. I was able to connect to the database without any issue now. :-) Thanks again for the help.

                    Cheers,
                    Suri
                    • 7. Re: Accidentally deleted DBF file
                      Mustafa KALAYCI
                      I hope that datafile does not contain any important information (tables). you won't be able to reach them.
                      • 8. Re: Accidentally deleted DBF file
                        Suri
                        Mustafa KALAYCI wrote:
                        I hope that datafile does not contain any important information (tables). you won't be able to reach them.
                        Hi Mustafa,

                        I created those files just for practice purpose, thats why I've deleted those.

                        Thank you.

                        Edited by: Suri on Aug 10, 2012 5:26 PM
                        • 9. Re: Accidentally deleted DBF file
                          Mustafa KALAYCI
                          okey then, from now on, use
                            drop tablespace tbs_name;
                          command to drop ;-)