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

CAN I RECOVER DELETED DATAFILE AND ITS TABLESPACE USING FLASHBACK DATABASE

Muhammad.Usman Jul 27, 2009 3: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 Jul 27, 2009 3: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
Like Show 0 Likes (0)

Actions
2. Re: CAN I RECOVER DELETED DATAFILE AND ITS TABLESPACE USING FLASHBACK DATABASE

Pavan DBA Jul 27, 2009 4: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
Like Show 0 Likes (0)

Actions

3. Re: CAN I RECOVER DELETED DATAFILE AND ITS TABLESPACE USING FLASHBACK DATABASE

Kamran Agayev A. Jul 27, 2009 4: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 Jul 27, 2009 4:41 AM (in response to Muhammad.Usman)

forget about delete datafile/tablespace, even shrinking datafile is restricted with flashback database.

hth,
http://borndba.com
Like Show 0 Likes (0)

Actions
5. Re: CAN I RECOVER DELETED DATAFILE AND ITS TABLESPACE USING FLASHBACK DATABASE

Muhammad.Usman Jul 27, 2009 5: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
Like Show 0 Likes (0)

Actions
6. Re: CAN I RECOVER DELETED DATAFILE AND ITS TABLESPACE USING FLASHBACK DATABASE

Kamran Agayev A. Jul 27, 2009 5: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/]
Like Show 0 Likes (0)

Actions
7. Re: CAN I RECOVER DELETED DATAFILE AND ITS TABLESPACE USING FLASHBACK DATABASE

gk Apr 12, 2012 2: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
Like Show 0 Likes (0)

Actions
8. Re: CAN I RECOVER DELETED DATAFILE AND ITS TABLESPACE USING FLASHBACK DATABASE

gk Apr 19, 2012 6:37 AM (in response to gk)

can any help me quickly
Like Show 0 Likes (0)

Actions

Go to original post