8 Replies Latest reply on Apr 19, 2012 11:37 AM by gk

    CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE

    Muhammad.Usman
    Muhammad.Usman Jul 27, 2009 8:55 AM
      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-Oracle
          Uwehesse-Oracle Jul 27, 2009 8:58 AM (in response to Muhammad.Usman)
          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
            Pavan DBA Jul 27, 2009 9:11 AM (in response to Muhammad.Usman)
            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.
              Kamran Agayev A. Jul 27, 2009 9:41 AM (in response to Muhammad.Usman)
              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
                694626 Jul 27, 2009 9:41 AM (in response to Muhammad.Usman)
                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
                  Muhammad.Usman Jul 27, 2009 10:33 AM (in response to Kamran Agayev A.)
                  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.
                    Kamran Agayev A. Jul 27, 2009 10:37 AM (in response to Muhammad.Usman)
                    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
                      gk Apr 12, 2012 7:49 AM (in response to Kamran Agayev A.)
                      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