Oracle Database 12c - RMAN now restores tables!

Have you ever wanted to just restore a given table, or partition of a table, from an RMAN backup using point in time recovery? While there were hack ways to do it in the past, Oracle Database 12c fully supports this table level and partition level recovery method.

First - as always - a few rules.

1. The database must be in ARCHIVELOG mode.
2. You must have a backup of the datafile(s) that contain the object.
3. That backup must have been completed before the point-in-time that you want to restore the table or table partition too.
4. You need all of the archived redo logs from the point-in-time that the backup of the datafile(s) until the point-in-time that you wish to restore too.

If you have met all of these requirements, then you can restore a table to an earlier point-in-time.

It would be really nice to be able to restore a table to a later-point-in time... I'm calling that the Quantum Entanglement Restore and I'm sure it will be coming soon! Imagine how analytics would change with such a feature!

Here is an example of recovering four tables: EMP, DEPT, BONUS and SALGRADE in the SCOTT schema:

recover table scott.emp, scott.dept, scott.bonus, scott.salgrade
until time "to_date('09/09/2013 12:44:00','mm/dd/yyyy hh24:mi:ss')"
auxiliary destination '/u01/app/oracle/aux'
remap table scott.emp:rest_emp, scott.dept:rest_dept,
scott.bonus:rest_bonus, scott.salgrade:rest_salgrade;

Note that RMAN uses an auxiliary instance to perform the restore (not surprisingly).

An error will be raised if the table you are trying to recover is present in the database (so you will need to rename old copies of the table, etc). If you want to keep the original tables in place, then you can use the remap table clause (as I did in the example) to rename the tables being recovered. You can also remap recovered tables into new tablespaces and schema's if you like.

In my mind, this functionality, along with other functionality like flashback database, pretty much eliminates any reason for using Oracle Data Pump in any kind of daily backup strategy. I'm not saying there isn't a place for Data Pump, clearly there is when doing things like transportable tablespaces. However, I fail to find much rationale for it's use as a daily backup strategy more and more as time goes on.