RMAN table recovery (12c new feature)


Notice: get_currentuserinfo is deprecated since version 4.5.0! Use wp_get_current_user() instead. in /home/dbascrip/public_html/wp-includes/functions.php on line 4435

Cyrille Modiano

OCP Oracle DBA in Montreal at Desjardins

Latest posts by Cyrille Modiano (see all)

Before oracle 12c, recover a table from loss or corruption was a difficult affair.
Most of the time you notice that the table is lost or corrupted too late to use the flashback technology and then the only way to go is to duplicate your database to a time the table was still valid, export this table and reimport it in your production database. That was a long drawn out affair.
Oracle 12c still procede the same way, it creates an auxiliary database with just the needed tablespace(s) containing the table to restore, export the table using datapump and reimport it to the database. All these steps are now automated. This feature is only available in Enterprise Edition as for the Tablespace Point in time recovery.

Recover a table with RMAN

As for any RMAN recovery you can restore your table by specifying :

  • UNTIL SCN
  • UNTIL SEQUENCE
  • UNTIL TIME


For the exemple we will simulate the corruption of a table and try to restore it with RMAN.
First, lets create our test table:

Now we will query the current scn to be able to restore our table before the corruption:

I will Now just alter some rows to simulate the corruption:

First step before trying to restore our table is to take a backup of our database.

Here we go, lets try to recover our table, here is the script I will use:

Basically what will this script do ? it will create a clone of my database to the scn 1756563 (the scn before I made the corruption). The files of the cloned database will be placed in ‘/u01/app/oracle/oradata_aux’ and the table will be reimported in my database using the new name TEST_BEFORE.

DBA Oracle MontrΓ©al

At this point my table is reimported to my database with the new name TEST_BEFORE:

Now lets check if the table restored contains the correct data:

We can now replace our corrupted table with the one restored:

You can choose to just export the table with datapump without reimporting it in the database. This option allow you to be ready to reimport if needed, here is the script you can use:

Thats it.

22 thoughts on “RMAN table recovery (12c new feature)

  1. Great job…. I am starting the 12c migration approach and best features to test out prior to full upgrade in Development.

  2. Great!
    I tried to do something similar with tablespace pint in time recovery in Oracle 10 and this was hell!! πŸ™‚

    Regards.

  3. Great job, I need some information. How I am gonna get the changes recorded in which SCN ? for I example in live scenario.. user updated the table, how would I restore at that point ? from where I get the SCN of that time ?

    1. Hi Rakesh,

      As I mentioned at the begining of the post, the procedure to restore is identical to any RMAN restoration, you can choose to restore from a date, a scn or an archivelog sequence, if you don’t know the scn you can precise a date (set until time) or get the scn with that function :

      SQL> SELECT TIMESTAMP_TO_SCN(‘2014-03-12 08:25:00’) FROM dual;

      TIMESTAMP_TO_SCN(‘2014-03-1208:25:00’)
      ————————————–
      6170263709

      Hope this helps.

    1. There is a small variation of the RESTORE COMMAND, here is an exemple form oracle documentation :

      RECOVER TABLE HR.PDB_EMP OF PLUGGABLE DATABASE HR_PDB
      UNTIL TIME ‘SYSDATE-4’
      AUXILIARY DESTINATION ‘/tmp/backups’
      REMAP TABLE ‘HR’.’PDB_EMP’:’EMP_RECVR’;

  4. great work cyrille…it will be help full to all…keep posting….gaining begins with sharing…!! πŸ™‚

    1. In 11g it is TSPITR, not table level recovery, table recovery still uses TSPITR but now all operations are automated, from the auxiliary database creation to the table import back to your database.

  5. Great example, thanks for the explanation, especially for newbies like me.

    One question, do we have to gather stats afterwards? What about indexes?

    1. Hi Atiq,

      Thank you for reading my blog, to answer your questions, no you don’t have to gather statistics afterwards, statistics are imported back with the table.
      The same is true for indexes and constraints if you don’t remap the table like explained in the oracle documentation :

      “When you use the REMAP option, any named constraints and indexes are not imported. This is to avoid name conflicts with existing tables.”

      Cyrille

  6. This is nice, but what about single table restoration if table is indexed and index is located in separate tablespace/datafile? ORA-31693, ORA-00376, ORA-01110 πŸ™

Leave a Reply

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