OraFAQ Forum: Backup & Recovery » Can Flashback function recover tablespace that I dropped?

Home » RDBMS Server » Backup & Recovery » Can Flashback function recover tablespace that I dropped? (Oracle 10gR2 RedHat AS 4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Can Flashback function recover tablespace that I dropped? [message #316708]

Sun, 27 April 2008 22:16

wxfjordan
Messages: 92
Registered: December 2006

Member

If I dropped a tablespace, can I use flashback function to recover this tablespace?

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #316710 is a reply to message #316708]

Sun, 27 April 2008 23:18

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

No.

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #316711 is a reply to message #316708]

Sun, 27 April 2008 23:43

wxfjordan
Messages: 92
Registered: December 2006

Member

Can you tell me flashback function detail? what situation can use the flashback function?

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #316716 is a reply to message #316708]

Mon, 28 April 2008 00:43

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

You can have a look at Flashback Technology

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #316725 is a reply to message #316708]

Mon, 28 April 2008 01:22

wxfjordan
Messages: 92
Registered: December 2006

Member

"get back whole database to a previous point in time."

Flashback function can get back database to previous status. Why can not recover a tablespace that been dropped?

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #316728 is a reply to message #316708]

Mon, 28 April 2008 01:50

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

This is the limitations of flashback database.

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #316729 is a reply to message #316708]

Mon, 28 April 2008 01:54

gopu_g
Messages: 54
Registered: March 2008
Location: mumbai

Member

Hi,
you can restore the table that you droped, from the flash back.
this is the command for that

flash back table <tabename> to before drop;

but is is not possible to restore a tablespace from the flash back.

Regards
Gopu

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #316734 is a reply to message #316708]

Mon, 28 April 2008 02:19

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Follow the links that I posted. The things you mention to get back dropped table is not flashback database feature. It is flashback drop feature. It is only based on recycle bin. And it has nothing to do either with flashback logs or with retention target.

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #316830 is a reply to message #316725]

Mon, 28 April 2008 08:49

ebrian
Messages: 2794
Registered: April 2006

Senior Member

wxfjordan wrote on Mon, 28 April 2008 02:22

"get back whole database to a previous point in time."

Flashback function can get back database to previous status. Why can not recover a tablespace that been dropped?

wxfjordan you are most certainly correct. Flashback database CAN recover a dropped tablespace.

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #316937 is a reply to message #316708]

Mon, 28 April 2008 21:23

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Brain can you please give an example. I tried every time and I failed , the datafiles name become unnamed and it remained in offline state.

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #317037 is a reply to message #316937]

Tue, 29 April 2008 05:07

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Correct, the file(s) associated with the tablespace will be unknown, therefore a ALTER DATABASE RENAME '<directory_path/UNNAMED0005>' to '<direcotry_path/original_name>' must be performed.

Once this is performed the flashback can occur.

SQL> create tablespace test_restore datafile '/oracle/oradata/ora10g/test_restore01.dbf' size 5m;

Tablespace created.

SQL> create table test tablespace test_restore as select level a1 from dual connect by level < 99;

Table created.

SQL> select current_scn from v$database;

    CURRENT_SCN
---------------
    27034883091

SQL> drop tablespace test_restore including contents;

Tablespace dropped.

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

Total System Global Area	167772160 bytes
Fixed Size			  1260672 bytes
Variable Size			125830016 bytes
Database Buffers		 33554432 bytes
Redo Buffers			  7127040 bytes
Database mounted.

SQL> flashback database to scn 27034883091;
flashback database to scn 27034883091
*
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: '/oracle/product/10.2/dbs/UNNAMED00005'

SQL> alter database rename file '/oracle/product/10.2/dbs/UNNAMED00005' to
  2    '/oracle/oradata/ora10g/test_restore01.dbf';

Database altered.

SQL> flashback database to scn 27034883091;

Flashback complete.

SQL> alter database open read only;

Database altered.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME		STATUS
----------------------- ------------
SYSTEM			ONLINE
UNDOTBS1		ONLINE
SYSAUX			ONLINE
TEMP			ONLINE
USERS			ONLINE
TEST_RESTORE		ONLINE

6 rows selected.

SQL> select count(*) from test;

    COUNT(*)
------------
          98

At this point, you could export everything you wanted and then shutdown the database and startup mount then issue RECOVER DATABASE and be right back to where you were after the tablespace drop. Then, if needed, you could recreate the tablespace and import the data.

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #317048 is a reply to message #316708]

Tue, 29 April 2008 05:38

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Brain, I tried this step in past many times and I failed. The step where I fail is , whenever to open database.

SQL> flashback database to scn 896583
*
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:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'


SQL> alter database rename file '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'
 to '/oradata2/test_restore01.dbf';

Database altered.
SQL> flashback database to scn 896583
  2  ;
flashback database to scn 896583
*
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: '/oradata2/test_restore01.dbf'


SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01110: data file 5: '/oradata2/test_restore01.dbf'

Any comment from you.

[Edit: Linesize]

[Updated on: Tue, 29 April 2008 05:42]

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #317062 is a reply to message #317048]

Tue, 29 April 2008 06:42

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Can't really determine from just that output. Is your SCN set correctly? Can you produce a full example?

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #317194 is a reply to message #316708]

Tue, 29 April 2008 21:06

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Just a replica of your example and I failed. I failed it more than 10 times.

SQL> create tablespace test_restore datafile '/oradata2/test_restore01.dbf' size 5m;

Tablespace created.

SQL> create table test tablespace test_restore as select level a1 from dual connect by level < 99;

Table created.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
897265

SQL> drop tablespace test_restore including contents;

Tablespace dropped.

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

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 113246248 bytes
Database Buffers 46137344 bytes
Redo Buffers 6369280 bytes
Database mounted.
SQL> flashback database to scn 897265;
flashback database to scn 897265
*
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:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'

SQL> alter database rename file '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to
2 '/oradata2/test_restore01.dbf';

Database altered.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01110: data file 5: '/oradata2/test_restore01.dbf'

SQL> flashback database to scn 897265;

Flashback complete.

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01565: error in identifying file
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #317213 is a reply to message #317194]

Wed, 30 April 2008 01:02

Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Sorry to get into your discussion but Arju I saw:
SQL> flashback database to scn 897265;
flashback database to scn 897265
*
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:
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005'

and

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-01565: error in identifying file
'/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

So don't you have 2 undefined files (due to your previous test)? Actually your test environment seems corrupted.

Regards
Michel

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #317223 is a reply to message #316708]

Wed, 30 April 2008 01:24

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Thank you Michel so much. So, it was my fault in the experiment. The time where I started is there was an offline datafile. So subsequent experiment error remains. I mistakenly interpret later UNNAMED00006 as UNNAMED00005.

Thank you again.

However if I drop tablespace with include datafiles option then it is not possible to get tablespace back with flashback database.

Report message to a moderator

Re: Can Flashback function recover tablespace that I dropped? [message #317256 is a reply to message #317223]

Wed, 30 April 2008 04:55

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Arju wrote on Wed, 30 April 2008 02:24

However if I drop tablespace with include datafiles option then it is not possible to get tablespace back with flashback database.

Yes it is !

Report message to a moderator

Previous Topic:	Defining Second destination of Archive log list (merged)
Next Topic:	control file corrupted

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 09 22:11:59 CDT 2024