Home » RDBMS Server » Backup & Recovery » Can Flashback function recover tablespace that I dropped? (Oracle 10gR2 RedHat AS 4)
Can Flashback function recover tablespace that I dropped? [message #316708] Sun, 27 April 2008 22:16 Go to next message
wxfjordan
Messages: 92
Registered: December 2006
Member

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

Re: Can Flashback function recover tablespace that I dropped? [message #316710 is a reply to message #316708] Sun, 27 April 2008 23:18 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

No.
Re: Can Flashback function recover tablespace that I dropped? [message #316711 is a reply to message #316708] Sun, 27 April 2008 23:43 Go to previous messageGo to next message
wxfjordan
Messages: 92
Registered: December 2006
Member

Can you tell me flashback function detail? what situation can use the flashback function?
Re: Can Flashback function recover tablespace that I dropped? [message #316716 is a reply to message #316708] Mon, 28 April 2008 00:43 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

You can have a look at Flashback Technology
Re: Can Flashback function recover tablespace that I dropped? [message #316725 is a reply to message #316708] Mon, 28 April 2008 01:22 Go to previous messageGo to next message
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?

Re: Can Flashback function recover tablespace that I dropped? [message #316728 is a reply to message #316708] Mon, 28 April 2008 01:50 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

This is the limitations of flashback database.
Re: Can Flashback function recover tablespace that I dropped? [message #316729 is a reply to message #316708] Mon, 28 April 2008 01:54 Go to previous messageGo to next message
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
Re: Can Flashback function recover tablespace that I dropped? [message #316734 is a reply to message #316708] Mon, 28 April 2008 02:19 Go to previous messageGo to next message
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.
Re: Can Flashback function recover tablespace that I dropped? [message #316830 is a reply to message #316725] Mon, 28 April 2008 08:49 Go to previous messageGo to next message
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.
Re: Can Flashback function recover tablespace that I dropped? [message #316937 is a reply to message #316708] Mon, 28 April 2008 21:23 Go to previous messageGo to next message
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.
Re: Can Flashback function recover tablespace that I dropped? [message #317037 is a reply to message #316937] Tue, 29 April 2008 05:07 Go to previous messageGo to next message
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.
Re: Can Flashback function recover tablespace that I dropped? [message #317048 is a reply to message #316708] Tue, 29 April 2008 05:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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?
Re: Can Flashback function recover tablespace that I dropped? [message #317194 is a reply to message #316708] Tue, 29 April 2008 21:06 Go to previous messageGo to next message
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

Re: Can Flashback function recover tablespace that I dropped? [message #317213 is a reply to message #317194] Wed, 30 April 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
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
Re: Can Flashback function recover tablespace that I dropped? [message #317223 is a reply to message #316708] Wed, 30 April 2008 01:24 Go to previous messageGo to next message
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.
Re: Can Flashback function recover tablespace that I dropped? [message #317256 is a reply to message #317223] Wed, 30 April 2008 04:55 Go to previous message
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 !
Previous Topic: Defining Second destination of Archive log list (merged)
Next Topic: control file corrupted
Goto Forum:
  


Current Time: Wed Dec 07 06:52:22 CST 2016

Total time taken to generate the page: 0.06667 seconds