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.

RECOVER TABLE TESTUSER.TABLE1
UNTIL SCN 384840289
AUXILIARY DESTINATION '/TEMP/ORCLDB/recover'
DATAPUMP DESTINATION '/TEMP/ORCLDB/dumpfiles'
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.

RECOVER TABLE TESTUSER.TABLE1
UNTIL TIME ‘SYSDATE-2’
AUXILIARY DESTINATION '/TEMP/ORCLDB/recover'
REMAP TABLE 'TESTUSER'.'TABLE1':'TABLE1_REC';

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
http://www.oracleracexpert.com


11 comments:

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

    ReplyDelete
  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"

    ReplyDelete
  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"

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete