Restoring Just a Table Using Rman 1

[Michael42]

Hello,

In Oracle 9i on Sun Solaris 8 is it possible to restore one or more tables and nothing else, i.e. I do not want to restore the entire database or a tablespace.

If possible, can you please give an example?



Thanks,

Michael42

 

[SantaMufasa]

Michael,

I am not RMAN proficient, so it may have a way to restore a single object. But if RMAN has no more functionality than the manual backups, restores, and recoveries of which I'm familiar, then this is what I have done in the past to obtain a single table from a full (non-export) backup:

1) Restore the database to a bogus instance.
2) Export the object that I want.
3) Import the object into my "real" database.
4) Drop the bodus db and instance.



Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Michael42]

SantaMufasa,

As usually you are the living definition of thinking outside the box. Thanks for posting as this option should be in EVERY DBA's toolbox.

Fortunately I do not have an emergency where I need to do such a restore but routinely take exports for the fact that I do not know how to use RMan yet to easily restore a schema object such as a table.

Perhaps if it can be shown that RMan can do this easily too I could cut down on the resources of using RMan AND database exports.

I think I am not the only one in this predicament. Can anyone please detail how to use RMan in this way?

Thanks,

Michael42

 

[SantaMufasa]

BTW, Michael, since human goofs (e.g., inadvertent table dropping, truncates, incorrect row deletes, et cetera) are tons more prevalent than hardware problems, we have a nightly cron job that does schema exports on every database. That way, if somebody goofs, we are prepared to do a restore much quicker than the method I mention above.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[stefanhei]

Michael,
Dave gave you the correct answer (as usual): The RMAN way is to restore to a bogus instance, export and import.
The benefit RMAN gives you is the possibility of a point in time recovery. So changes made to your table after the backup and before it was dropped are not lost (given you're in archive log mode).
BTW, if data in your table was destroyed by non DDL-commands (a user issued delete or update + commit) have a look at LogMiner. It's a great tool to undo unwanted changes.

Stefan

 

[Michael42]

stefanhei,

Thanks for posting. I have a few databases in the 3+ TeraByte range where the clone database technique would not be practical...take too long.

Thanks again,

Michael42

 

[stefanhei]

How about point in time restore of a single tablespace? (that was what I intended to say in my last post but didn't - point in time without rman is possible of course too).

Stefan