Skip to Main Content
  • Questions
  • restore single table from rman full backup

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Oracle.

Asked: January 28, 2017 - 6:24 am UTC

Last updated: September 25, 2020 - 3:09 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi

I have a rman full backup and I want to restore single table due to drop that table how can I do this.

I can not create a clone due to space and resources issue..

Thanks

and Connor said...

You need space for a "smaller" database. For a table recovery, RMAN will create just enough of a database to hold the data dictionary, and the tablespace that the table was present in.

So you'd typically end up with SYSTEM, TEMP, UNDO, SYSAUX and 'x', where 'x' is the tablespace.

In 12c, this can be done natively using "RECOVER TABLE". In 11g, you can follow the list of instructions in

"How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN (Doc ID 223543.1)"

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Alex, January 30, 2017 - 4:20 pm UTC

I would check the recycle bin first before anything. That would probably be the easiest approach if you get lucky.
Connor McDonald
January 30, 2017 - 8:07 pm UTC

good point

Take logical backups too

pmdba, January 31, 2017 - 4:13 am UTC

For future reference, it is a good idea to make regular datapump dumps of your database in addition to RMAN backups. In cases where point in time recovery is not required, it can save you a lot of time.
Connor McDonald
January 31, 2017 - 4:59 pm UTC

This is true but this gives you a copy of the table at a fixed point in time, rather than the capacity to recover it to any point in time.

But definitely useful.

Test Backup

Chuck Jolley, May 01, 2017 - 9:35 pm UTC

What do you think of the usefulness of this to proof test a backup system?
We had occasion to need to do this for a table and it was a fascinating process to watch rman pick from our multiple backup locations to find the things it wanted.
It was also pretty reassuring to see that we could restore something to our production machine, which we had never had an opportunity to physically test after it went into production.
Connor McDonald
May 02, 2017 - 2:57 am UTC

Any thing that actually *uses* a backup to do *any* kind of restoration is a good thing.

As the old saying goes...

"Backup is the *least* important thing in your database, but Restore is the *most* important"

In that sense, backups are the means to an end - and conversely, they are useless if you've never checked their viability for restoration.

Its one of the reasons I love Dataguard - its like a permanent 24x7 test of your recovery process.

Test Backups

Chuck Jolley, May 02, 2017 - 3:26 pm UTC

I guess what I was thinking is that since one can't really blow away production data just to test restores, then table recovery to a point in time with a new name might be a good way to test restoring on the actual production hardware.
For rman to do that it needs all the things it would need for a normal restore to production. But without overwriting production data.
And you are right about data guard. I tested it and it's wonderful. I just can't talk them into the licences. :(
Connor McDonald
May 03, 2017 - 1:01 am UTC

With regard to

"I just can't talk them into the licences."

on the assumption you have (say) a Production box and a Dev/Test box, then I'd suggest to them adding DG for Prod to the Dev/Test box. That is no additional license fee (just extra storage which is dirt cheap), and the applying of redo logs is a very efficient process. I've seen DG nodes with a single cpu easily accommodating the work of a production node with many cpu's.


Excellent suggestion

Chuck Jolley, May 03, 2017 - 1:49 pm UTC

Thanks! That is physically doable. Our test/dev box has plenty of room because it used to be our production server. We could even move it out to our DR site. That would be awesome.
Though, there might be a complication in that, unlike the prod server, it uses old seat licences as it isn't connected to the internet.
But I can ask. It's even possible we now have an unused processor licence set we can apply to it now. Our contract is very old.

Sachin, September 16, 2020 - 11:45 am UTC

I daily take RMAN full backup and store at external hard disk. And I not use any auxiliary RMAN feature and Clone RMAN feature.
My problem is I want to restore some of table from RMAN Backup of date 14-sep so how it is possible?

Table Name is TBL_PRODUCTION_YR_2019_FINAL ; TBL_AUDIT_YR_2019 ; TBL_PROBLEM_YR_2019
Connor McDonald
September 18, 2020 - 4:42 am UTC

You can recover the table, or recover it to a data pump file with

recover table scott.emp
  until time "to_date('14-sep-2015 00:00', 'dd-mon-yyyy hh24:mi')"
  auxiliary destination '/tmp/aux'  
  remap table 'SCOTT'.'EMP':'OLD_EMP';
  

recover table scott.emp
  until scn ...
  auxiliary destination '/tmp/aux'
  datapump destination '/data/export'
  dump file 'saved_copy.dmp'
  notableimport;
 


The concept of the auxillary database just means we temporarily create a small database in order to restore the tablespaces we need to get your table. When the recovery is done, that instance will be erased.

SCN

sachinpatel@gmail.com, September 19, 2020 - 4:20 am UTC

Thanks for helping.
But Suppose I want to restore table and I do not know SCN so how Can I restore table when SCN is not know?
Or another case is suppose SCN is store anywhere in the system or database or file. but how can i see the SCN which helps me to restore drop table?
Connor McDonald
September 21, 2020 - 1:54 am UTC

So me example commands.

You can nominate an SCN *or* a timestamp

David, September 22, 2020 - 3:30 pm UTC

Hello Connor,

Just a little comment : when you say "The concept of the auxillary database just means we temporarily create a small database in order to restore the tablespaces we need to get your table" I do not completly agree because if you have a very little table (10 MB) but in a very very big datafile (1 TB), the auxilliary database need more than 1 TB of space available.

It is a good think to check the available size before executing the RECOVER TABLE, to avoid an error message.

Best regards,

David D.
Connor McDonald
September 25, 2020 - 3:09 am UTC

Fair point.

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.