Oracle Database 12c: RMAN recover at table level

Brett Jordan David MacdonaldOracle Database 12c comes with a new feature named “RMAN table level recovery”.

After a quick try it’s easy to understand that we are talking about Tablespace Point-in-Time Recovery (TSPITR) with some automation to have it near-transparent.

 

How to launch it

The syntax is quite trivial. Suppose you’ve dropped a table ludovico.reco and then purged it (damn!) then you can’t flashback it to before drop and don’t want to flashback the entire database.

 

You can recover the table with:

 

You identify the schema.table:partition to restore, optionally you can pass the pluggable database containing the table to recover, the time definition as usual (scn, seq# or timestamp) and an auxiliary destination.

This Auxiliary destination is well-known to be mandatory for TSPITR. You can pass other options like table renaming or tablespace remapping.

Off course, the database must be open in read-write, in archivelog mode and at least one successful backup must be taken.

How it works

Oracle prepare an auxiliary instance by restoring the SYSTEM, UNDO and SYSAUX tablespaces.

Then it opens in READ-ONLY mode the partial database.

 

It uses then the read-only dictionary to take the tablespace that was containing the table before the data loss. This tablespace (users in my example) is restored and recovered, and the database is opened.

 

At this  point, RMAN starts an export/import with datapump to move the table from the auxiliary database back to the target database:

 

Finally, the auxiliary instance is cleaned:

 

We can check if our table is ok:

 

Oh, and yes, now we can select directly from RMAN! 🙂

 

 My opinion

  • It still needs the amount of space needed to recover the auxiliary instance (system, sysaux, temp and the user tablespace containing the missing data), so it has all the defeats of the typical TSPITR, but it’s automatic so is an improvement for the real life.
  • Restoring the user tablespace separately from the system tablespaces can be an issue if you’re saving backupsets over tape: you can end up by reading twice the same backupset that could be read once instead.

Cheers

Ludovico

The following two tabs change content below.

Ludovico

Oracle ACE Director and Computing Engineer at CERN
Ludovico is an Oracle ACE Director, frequent speaker and community contributor, working as Computing Engineer at CERN, the European Organization for Nuclear Research, in Switzerland.

7 thoughts on “Oracle Database 12c: RMAN recover at table level

  1. Hi
    Did you taken full backup or only for pluggable database please ?
    Waiting for your reply please …
    regards
    Anuj

  2. Hi Anuj, thank you for your comment.
    Actually I’ve done it on a non-CDB database, but I’m planning to do some more investigations on table recover, so stay tuned!

  3. Thanks for your reply Ludovico!!!!!!!!!!!!!!!!!!!!!!!!!

    my steps

    Backup command for PLUGGABLE DATABASE . I am not taking fullbackup .

    RMAN> backup PLUGGABLE DATABASE anujv format ‘/u01/app/oracle/RmanBackup/vihaan112c_db_%U’ plus archivelog format ‘/u01/app/oracle/RmanBackup/vihaan112c_arch_%U’;

    I am getting following error . Any idea please

    error <<<<<<<<<<<<<<<< RECOVER TABLE vihaan1.XXX OF PLUGGABLE DATABASE anujv UNTIL TIME “to_date(‘2013 AUG 28 20:13′,’YYYY MON DD hh24:MI’)” AUXILIARY DESON ‘/tmp/backups’ REMAP TABLE ‘VIHAAN1′.’XXX’:’REC_XXX’;

    # switch all tempfiles
    switch clone tempfile all;
    # restore the tablespaces in the recovery set and the auxiliary set
    restore clone datafile 1, 4, 3, 21, 22;
    switch clone datafile all;
    }
    executing Memory Script

    executing command: SET until clause

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    executing command: SET NEWNAME

    renamed tempfile 1 to /tmp/VIHAAN/datafile/o1_mf_temp_%u_.tmp in control file
    renamed tempfile 5 to /tmp/VIHAAN/datafile/o1_mf_temp_%u_.tmp in control file

    Starting restore at 29-AUG-13
    using channel ORA_AUX_DISK_1

    One or more auxiliary set of datafiles could not be removed

    Removing automatic instance
    shutting down automatic instance
    Oracle instance shut down
    Automatic instance removed
    auxiliary instance file /tmp/VIHAAN/controlfile/o1_mf_91z56jcy_.ctl deleted
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of recover command at 08/29/2013 19:37:15
    RMAN-03015: error occurred in stored script Memory Script
    RMAN-20216: backup piece is missing
    RMAN-06159: error while looking up backup set

  4. Sure, you need to have a backup of your Container database also, because the undo tablespaces that are required are shared into the container database.

    I’m doing other tests with recover table but on my architecture PDBs appear quite buggy…

    Ludovico

  5. Thanks Ludovico for your reply ..

    Container database=PLUGGABLE DATABASE i.e. anujv

    is this true ?

    can you please correct my command ?
    =================
    RMAN> backup PLUGGABLE DATABASE anujv format ‘/u01/app/oracle/RmanBackup/vihaan112c_db_%U’ plus archivelog format ‘/u01/app/oracle/RmanBackup/vihaan112c_arch_%U’;
    ================

    so recovery of table is not possible if you have only pluggable database backup ???

    I know recovery will work if I take full database backup .

    regards
    Anuj

  6. Sorry for the late reply, you may have found the solution on your own but it’s just to track it here.

    Anuj> so recovery of table is not possible if you have only pluggable database backup ???

    Exact! you need to have the backup of the CDB$ROOT also, because its system and undo tablespaces are needed, moreover you have to recover them at the same point in time.
    If you want to restore your PDB at a point in time, you have to rely on PDB PITR that requires the restore on an auxiliary instance ant then a unplug/plug.

    Basically, backing up just PDBs without CDB$ROOT is useless, you will not restore them as you wouldn’t restore a single tablespace backup.
    Best regards

    Ludo

  7. Pingback: RMAN 12c Table Level Recovery | IT Remote

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.