8 Replies Latest reply: Apr 19, 2012 6:37 AM by gk RSS

    CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE

    Muhammad.Usman
      HI!

      I CREATED TABLESPACE WITH ITS DATAFILE.

      SQL>create smallfile tablespace usmantbs datafile 'E:\oracle\product\10.2.0\oradata\orcl\usman.dbf' size 10M logging extent management local segment space management auto;

      THEN I CREATED A USER AND ASSIGN THIS TABLESPACE TO HIM.

      sql>create user usmanali profile default identified by usmanali default tablespace usmantbs account unlock;
      sql>grant connect,resource to usmanali;

      I CONNECTED WITH usmanali as USER AND CREATED A TABLE.

      sql>conn usmanali/usmanali
      sql>create table baseball(id number(9));

      sql>select current_scn from v$database;

      CURRENT_SCN
      ---------------------
      545863

      Next i deleted the tablespace including contents and datafiles.....

      sql>drop tablespace usmantbs including contents and datafiles;

      i dont have any backup of this datafile but my database is in archive log....

      so can i .....flashback database to  scn 545863  as it was  before drop.....to get back my datafile along its tablespace
      wil i get my datafile back or not? please help.............
        • 1. Re: CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE
          UweHesse
          No. Flashback Database will not solve a problem with accidentally drop tablespace. Sorry.

          Kind regards
          Uwe

          http://uhesse.wordpress.com
          • 2. Re: CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE
            Pavan DBA
            hi it is not possible to get back datafiles or tablespaces which were dropped. for quick learning goto http://www.oracle-base.com/articles/10g/Flashback10g.php
            • 3. Re: CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE
              Kamran Agayev A.
              You can test it by yourself easily :) You will not be able to open your database
              After getting the error, just rename that datafile and flashback again. Then open your database
              C:\Documents and Settings\Administrator>sqlplus "/as sysdba"
              
              SQL*Plus: Release 10.2.0.1.0 - Production on Sat Aug 1 14:20:34 2009
              
              Copyright (c) 1982, 2005, Oracle.  All rights reserved.
              
              
              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
              With the Partitioning, OLAP and Data Mining options
              
              SQL> shutdown immediate
              Database closed.
              Database dismounted.
              ORACLE instance shut down.
              SQL> startup mount
              ORACLE instance started.
              
              Total System Global Area  293601280 bytes
              Fixed Size                  1248624 bytes
              Variable Size              96469648 bytes
              Database Buffers          192937984 bytes
              Redo Buffers                2945024 bytes
              Database mounted.
              
              SQL> alter database archivelog;
              
              Database altered.
              
              SQL> alter database flashback on;
              
              Database altered.
              
              SQL> alter database open;
              
              Database altered.
              
              SQL> create tablespace tb datafile 'c:\tb.df' size 1m;
              
              Tablespace created.
              
              SQL> create user tb identified by tb;
              
              User created.
              
              SQL> grant dba to tb;
              
              Grant succeeded.
              
              SQL> alter user tb default tablespace tb;
              
              User altered.
              
              SQL> create table tb (id number);
              
              Table created.
              
              SQL> select current_scn from v$database;
              
              CURRENT_SCN
              -----------
                   547292
              
              SQL> drop tablespace tb including contents and datafiles;
              
              Tablespace dropped.
              
              SQL> shutdown immediate
              Database closed.
              Database dismounted.
              ORACLE instance shut down.
              SQL> startup mount
              ORACLE instance started.
              
              Total System Global Area  293601280 bytes
              Fixed Size                  1248624 bytes
              Variable Size              96469648 bytes
              Database Buffers          192937984 bytes
              Redo Buffers                2945024 bytes
              Database mounted.
              
              SQL> flashback database to scn 547292;
              flashback database to scn 547292
              *
              ERROR at line 1:
              ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
              below
              ORA-01245: offline file 5 will be lost if RESETLOGS is done
              ORA-01111: name for data file 5 is unknown - rename to correct file
              ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
              
              
              SQL> alter database open resetlogs;
              alter database open resetlogs
              *
              ERROR at line 1:
              ORA-01245: offline file 5 will be lost if RESETLOGS is done
              ORA-01111: name for data file 5 is unknown - rename to correct file
              ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'
              
              
              SQL> select name from v$datafile;
              
              NAME
              --------------------------------------------------------------------------------
              
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSTEM01.DBF
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\UNDOTBS01.DBF
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSAUX01.DBF
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\USERS01.DBF
              C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005
              
              SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'c:\tb.dbf';
              
              Database altered.
              
              SQL> flashback database to scn 547292;
              
              Flashback complete.
              
              SQL> alter database open resetlogs;
              
              Database altered.
              
              SQL>
              
              
              SQL> select * from tb;
              
              no rows selected
              
              SQL> select name from v$datafile;
              
              NAME
              --------------------------------------------------------------------------------
              
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSTEM01.DBF
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\UNDOTBS01.DBF
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSAUX01.DBF
              C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\USERS01.DBF
              C:\TB.DBF
              
              SQL> select name from v$tablespace;
              
              NAME
              ------------------------------
              SYSTEM
              UNDOTBS1
              SYSAUX
              USERS
              TEMP
              TB
              
              6 rows selected.
              
              SQL>
              - - - - - - - - - - - - - - - - - - - - -
              Kamran Agayev A. (10g OCP)
              http://kamranagayev.wordpress.com
              [Step by Step install Oracle on Linux and Automate the installation using Shell Script |http://kamranagayev.wordpress.com/2009/05/01/step-by-step-installing-oracle-database-10g-release-2-on-linux-centos-and-automate-the-installation-using-linux-shell-script/]

              Edited by: Kamran Agayev A. on Jul 27, 2009 2:38 PM
              • 4. Re: CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE
                694626
                forget about delete datafile/tablespace, even shrinking datafile is restricted with flashback database.

                hth,
                http://borndba.com
                • 5. Re: CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE
                  Muhammad.Usman
                  Wow man its great ......i have just tested and its write ......in this when you flashback after re creating datafile you even get your data back because archiving is enabled.....


                  well greatttttttt thank you very muck


                  thanks alotttttttt
                  • 6. Re: CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE
                    Kamran Agayev A.
                    Muhammad.Usman wrote:
                    Wow man its great ......i have just tested and its write ......in this when you flashback after re creating datafile you even get your data back because archiving is enabled.....


                    well greatttttttt thank you very muck


                    thanks alotttttttt
                    You're welcome! :)

                    - - - - - - - - - - - - - - - - - - - - -
                    Kamran Agayev A. (10g OCP)
                    http://kamranagayev.wordpress.com
                    [Step by Step install Oracle on Linux and Automate the installation using Shell Script |http://kamranagayev.wordpress.com/2009/05/01/step-by-step-installing-oracle-database-10g-release-2-on-linux-centos-and-automate-the-installation-using-linux-shell-script/]
                    • 7. Re: CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE
                      gk
                      hi man,
                      i tried this one lot of time,but i getting error after changing the datafile unnamed to original name

                      onnected to:
                      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
                      With the Partitioning, OLAP and Data Mining options

                      SQL> shutdown immediate
                      Database closed.
                      Database dismounted.
                      ORACLE instance shut down.
                      SQL> startup mount
                      ORACLE instance started.

                      Total System Global Area 293601280 bytes
                      Fixed Size 1248624 bytes
                      Variable Size 96469648 bytes
                      Database Buffers 192937984 bytes
                      Redo Buffers 2945024 bytes
                      Database mounted.

                      SQL> alter database archivelog;

                      Database altered.

                      SQL> alter database flashback on;

                      Database altered.

                      SQL> alter database open;

                      Database altered.

                      SQL> create tablespace tb datafile 'c:\tb.df' size 1m;

                      Tablespace created.

                      SQL> create user tb identified by tb;

                      User created.

                      SQL> grant dba to tb;

                      Grant succeeded.

                      SQL> alter user tb default tablespace tb;

                      User altered.

                      SQL> create table tb (id number);

                      Table created.

                      SQL> select current_scn from v$database;

                      CURRENT_SCN
                      -----------
                      547292

                      SQL> drop tablespace tb including contents and datafiles;

                      Tablespace dropped.

                      SQL> shutdown immediate
                      Database closed.
                      Database dismounted.
                      ORACLE instance shut down.
                      SQL> startup mount
                      ORACLE instance started.

                      Total System Global Area 293601280 bytes
                      Fixed Size 1248624 bytes
                      Variable Size 96469648 bytes
                      Database Buffers 192937984 bytes
                      Redo Buffers 2945024 bytes
                      Database mounted.

                      SQL> flashback database to scn 547292;
                      flashback database to scn 547292
                      *
                      ERROR at line 1:
                      ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
                      below
                      ORA-01245: offline file 5 will be lost if RESETLOGS is done
                      ORA-01111: name for data file 5 is unknown - rename to correct file
                      ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'


                      SQL> alter database open resetlogs;
                      alter database open resetlogs
                      *
                      ERROR at line 1:
                      ORA-01245: offline file 5 will be lost if RESETLOGS is done
                      ORA-01111: name for data file 5 is unknown - rename to correct file
                      ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005'


                      SQL> select name from v$datafile;

                      NAME
                      --------------------------------------------------------------------------------

                      C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSTEM01.DBF
                      C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\UNDOTBS01.DBF
                      C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\SYSAUX01.DBF
                      C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1\USERS01.DBF
                      C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005

                      SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' as 'c:\tb.dbf';

                      Database altered.


                      after that if i tried to flashback iam getting same error

                      flashback database to scn 547292;
                      flashback database to scn 547292
                      *
                      ERROR at line 1:
                      ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
                      below
                      ORA-01245: offline file 5 will be lost if RESETLOGS is done
                      ORA-01110: data file 5: 'C:\TB.DBF';


                      CAN U PL HELP ME OUT....FROM THIS PROBLEM