Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CAN I RECOVER DELETED DATAFILE AND ITS TABLESPACE USING FLASHBACK DATABASE

Muhammad.UsmanJul 27 2009 — edited Apr 19 2012
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.............
This post has been answered by Kamran Agayev A. on Jul 27 2009
Jump to Answer

Comments

Uwehesse-Oracle
No. Flashback Database will not solve a problem with accidentally drop tablespace. Sorry.

Kind regards
Uwe

http://uhesse.wordpress.com
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
Kamran Agayev A.
Answer
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
Marked as Answer by Muhammad.Usman · Sep 27 2020
694626
forget about delete datafile/tablespace, even shrinking datafile is restricted with flashback database.

hth,
http://borndba.com
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
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/]
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
GK
can any help me quickly
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 17 2012
Added on Jul 27 2009
8 comments
6,675 views