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.
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.
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.
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. :(
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
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?
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.
September 25, 2020 - 3:09 am UTC
Fair point.