Tuesday, May 14, 2019

Recovering Tables and Table Partitions using RMAN Backups

Oracle 12c offers new feature to recover tables and table partitions at a point-in-time using RMAN without affecting the other objects in the database. Use RECOVER TABLE command to recover tables or table partitions from an RMAN backup.

This feature is useful in the following scenarios:
  • Object has Logical corruption or dropped
  • There is no sufficient undo to perform Flashback table
  • DDL operation modified the structure and you want to recover the data (Flashback cannot rewind the structural changes)
  • Recover a small number of tables to a point-in-time
User should verify the pre-requisites before you prepare to recover the tables and table partitions. RMAN enables you to specify the point-in-time either using SCN, Time or sequence number.

The conditions must be met to perform table/ table Partition recovery:

Before you prepare to recover the tables and table partitions,
  • Database must be in ARCHIVELOG mode and read-write mode
  • At least one full backup is available along with archived logs
  • Enough disk space is available on the database server for auxiliary instance
  • If present, any dependent objects to include in recovery
  • At least 1 Gigabyte extra in memory for the auxiliary database
  • Tables and table partitions on standby databases cannot be recovered.
  • Tables with named NOT NULL constraints cannot be recovered with the REMAP option. · COMPATIBLE parameter must be set to 11.1.0 or higher to recover table partition

RMAN enables recovery of selected tables without affecting remaining database objects. During the recovery process RMAN creates an auxiliary database, which is used to recover the tables or table partitions to a specified point-in-time. User need to specify the auxiliary database location using AUXILIARY DESTINATION clause in the RECOVERY command or SET NEWNAME command.

Please find the steps performed by RMAN during the recovery process:

1. Determine the backup which has the tables or table partitions that needs to recover to specified point-in-time
2. Create auxiliary database and recovery the tables or table partitions until specified point-in-time
3. Take a data dump export with recovered tables or table partitions
4. Import the dump into target database
5. Rename the recovered tables or table partitions in the target database

Please find an example to recovery TABLE1 table.

UNTIL SCN 384840289
DUMP FILE 'testdump.dat';

If source table exists then user can specify NOTABLEIMPORT or REMAP TABLE. Also user can use UNTIL TIME or UNTIL SEQUENCE clause to specify point-in-time recovery.

Please find an example to recovery TABLE1 table as TABLE1_REC to the state that it was 2 days before the current date.


There are some limitations recovering tables and table partitions:
  • We cannot recover table and table partitions belongs of SYS schema
  • We cannot recover table or table partitions from SYSAUX,SYSTEM tablespace Tables
  • We cannot recover tables with named NOT NULL constraint using REMAP option.
  • We cannot recover Table/Table partitions ON STANDBY database
  • We cannot recovery table partitions if version is prior Oracle Database 11g R1
Note that there are other methods available to recover tables to a point-in-time such as Oracle Flashback and Tablespace Point-in-Time Recovery.

Refer below link for

Flashback and Database Point-in-Time Recovery
Satishbabu Gunukula, Oracle ACE


  1. Railway Recruitment Board is conducting examination for NTPC posts. RRB NTPC 2019 Result will release on the official website of Railway Recruitment Board.

  2. Al-Ghurair is similar to a small lattice with a solid structure and a furrow of heavy, coarse hair. Thirty centimeters, his face white and has a broad black line on both sides, his body is gray, his ears are very small
    And also his eyes, and in fact he hardly uses his eyes but depends primarily on his hearing and sharp sense of smell at him, Its skin is softer and softer, so that the badger can bite the dog that bites it if the dog, for example, bites it. If the badger senses fear or excitation, he will smell like musk.
    The largest Arabic entertainment site in the world

    if you want more just look here "koktale"

  3. Yet, one should also keep in mind that it is also a great opportunity for a candidate to present themselves in a very favorable light. Here is the list of the most effective techniques in case one needs help with writing a presentation for an interview

    if you want more just look here "presentations online"

  4. List of Best Carpenters Service Near me in Bangalore - Top Rated Carpenter in Bangalore, Book Carpenter Services Online in Bangalore, Book Carpentry Services Online at Home In Bangalore. Get Free Best Price Quotes Carpenters Service in Bangalore Compare Charges, Save Money And Time @ Carpenters in Bangalore

  5. List of Best House Painters Service Near me in Bangalore - Top Rated House Painters in Bangalore, Book House Painters Services Online in Bangalore, Wall Painters in Bangalore. Best Price Quotes House Painters Service in Bangalore Compare Charges, Save Money And Time @ House Painters in Bangalore

  6. List of Best Plumbers Services Near me in Bangalore - Top Rated Plumbers in Bangalore, Book Plumbing Services Online in Bangalore, Get Certified Technician at Your Doorstep for Plumbers Services. Best Price Quotes Plumbers Service in Bangalore Compare Charges, Save Money And Time @ Plumbers in Bangalore

  7. List of Best Electronic Service Near me in Bangalore - Top Rated Electrician in Bangalore, Book Electronic Services Online in Bangalore, Get Certified Technician at Your Doorstep for Electronic Services. Best Price Quotes Electronic Service in Bangalore Compare Charges, Save Money And Time @ Electricians in Bangalore

  8. We are popular as Florist Service in Gurgaon from last 10 Years, we also offers fresh flowers cakes and latest gifts across Gurgaon. Send flowers online and Offline from flower shop for your loved ones and availlable for corporate gifting with fastest delivery.

    Florist Service in Gurgaon
    Online Florist Services in Gurgaon

  9. Packers and Movers Hyderabad Give Certified and Verified Service Providers, Cheap and Best ###Office Relocation Charges, ***Home Shifting, ✔✔✔Goods Insurance worth Rs. 10,000, Assurance for Local and Domestic House Shifting. Safe and Reliable Household Shifting Services in Hyderabad with Reasonable Packers and Movers Price Quotation @ Packers And Movers Hyderabad

  10. Attend The Analytics Training Institute From ExcelR. Practical Analytics Training Institute Sessions With Assured Placement Support From Experienced Faculty. ExcelR Offers The Analytics Training Institute.
    ExcelR Analytics Training Institute

  11. Hire Best Packers And Movers Mumbai for hassle-free Household Shifting, ***Office Relocation, ###Car Transporation, Loading Unloading, packing Unpacking at affordable ✔✔✔ Price Quotation. Top Rated, Safe and Secure Service Providers who can help you with 24x7 and make sure a Untroubled Relocation Services at Cheapest/Lowest Rate
    Packers And Movers Mumbai