Home » RDBMS Server » Backup & Recovery » RMAN backup tablepspace then drop table (Oracle 11g Linux RHEL 5)  () 1 Vote
RMAN backup tablepspace then drop table [message #303984] Mon, 03 March 2008 10:40 Go to next message
dariakon
Messages: 6
Registered: March 2008
Junior Member
I backuped a tablespace, dropped a table within in, restore & and recover tablespace, but could not get the table back.

Why ?


SQL> create tablespace test4 datafile '+DB_DATA' size 20m;

Tablespace created.

SQL> create table t4 ( dt date) tablespace test4;

Table created.

SQL> insert into t4 values ( sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t4;

DT
-------------------
2008-03-03 08:04:07


-------------------------------------------------------

RMAN> backup tablespace TEST4 tag='BACKUP_TEST4';

Starting backup at 03-MAR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00026 name=+DB_DATA/grdb/datafile/test4.394.648374623
channel ORA_DISK_1: starting piece 1 at 03-MAR-08
channel ORA_DISK_1: finished piece 1 at 03-MAR-08
piece handle=/opt/oracle/product/11.1.0/grdb/temp/17jaar0r_1_1 tag=BACKUP_TEST4 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 03-MAR-08

Starting Control File and SPFILE Autobackup at 03-MAR-08
piece handle=+DB_DATA/grdb/autobackup/2008_03_03/s_648375326.392.648375329 comment=NONE
Finished Control File and SPFILE Autobackup at 03-MAR-08
-------------------------------------------------------

SQL> drop table t4;

Table dropped.

-------------------------------------------------------

RMAN> sql 'alter tablespace test4 offline';

sql statement: alter tablespace test4 offline

RMAN> restore tablespace test4;

Starting restore at 03-MAR-08
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00026 to +DB_DATA/grdb/datafile/test4.394.648374623
channel ORA_DISK_1: reading from backup piece /opt/oracle/product/11.1.0/dw1/temp/17jaar0r_1_1
channel ORA_DISK_1: piece handle=/opt/oracle/product/11.1.0/dw1/temp/17jaar0r_1_1 tag=BACKUP_TEST4
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 03-MAR-08

RMAN> recover tablespace test4;

Starting recover at 03-MAR-08
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 03-MAR-08

RMAN> sql 'alter tablespace test4 online';

sql statement: alter tablespace test4 online

-------------------------------------------------------

1* select * from t4
SQL> /
select * from t4
*
ERROR at line 1:
ORA-00942: table or view does not exist

-------------------------------------------------------
Re: RMAN backup tablepspace then drop table [message #303988 is a reply to message #303984] Mon, 03 March 2008 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You recover the tablespace to the current time and and its time the table no more exists.

If you want to get back your table you have to recover until time just before drop.

Regards
Michel
Re: RMAN backup tablepspace then drop table [message #303993 is a reply to message #303984] Mon, 03 March 2008 11:08 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
what if we don't know the time?
Re: RMAN backup tablepspace then drop table [message #303994 is a reply to message #303993] Mon, 03 March 2008 11:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use LogMiner to find it.

Regards
Michel
Re: RMAN backup tablepspace then drop table [message #304035 is a reply to message #303984] Mon, 03 March 2008 16:31 Go to previous messageGo to next message
dariakon
Messages: 6
Registered: March 2008
Junior Member
Thanks for the tips, Michel.

Also Flashback is useful.
Re: RMAN backup tablepspace then drop table [message #304105 is a reply to message #304035] Tue, 04 March 2008 01:13 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Of course but this is not the same exercise.

Regards
Michel
Previous Topic: Oracle data block corrupation
Next Topic: Which type of backup shall I use?
Goto Forum:
  


Current Time: Sat Dec 03 20:09:35 CST 2016

Total time taken to generate the page: 0.12900 seconds