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

  2. 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

  3. In this page, all essential information on latest and upcoming railway recruitment 2020 recruitment is presented in the tabular form which is very user-friendly and easy to understand...

  4. I'm not one of those perusers that remarks on articles frequently, yet yours truly constrained me. There's a ton of fascinating substance with regards to this article is intriguing and intense.

    SEO services in kolkata
    Best SEO services in kolkata
    SEO company in kolkata
    Best SEO company in kolkata
    Top SEO company in kolkata
    Top SEO services in kolkata
    SEO services in India
    SEO copmany in India

  5. Since this is much scientific than spiritual, let's speak in terms of science. I will try not to put a lot of scientific terminology so that a common man or woman could understand the content easily. artificial intelligence training in hyderabad

  6. I realize that, the skills that the companies wants now are different from the skills that I have learnt during my degree course, for the reason that all the companies are digital orientated. digital marketing course in hyderabad

  7. Unlike data science, machine learning is a set of techniques that allow computers to make decisions based on the given data. And these techniques derive results that can perform much better without the need of programming rules. best AI course

  8. Great tips and very easy to understand. This will definitely be very useful for me when I get a chance to start my blog.
    Data Science Training in Hyderabad

  9. Instagram stories allow businesses to interact with their customers and prospects by making a series of images in order to tell a story. Each story you create should enhance your brand and make your value proposition clear. fitur instagram

  10. Fantastic Site with relevant information and content Shared was knowledgeable thank you.
    Data Science Courses Hyderabad

  11. Thanks for posting the best information and the blog is very helpful.data science interview questions and answers

  12. Thanks for posting the best information and the blog is very helpful artificial intelligence course in hyderabad

  13. Our training is a 100% real time Salesforce training program in Bangalore by profound instructors who has real experience will give you more practical knowledge rather than academic, so CRS Info Solutions is the best institute for practical based Salesforce online training. Experience our quality, attend to our free live demo class salesforce training in bangalore

  14. This is also a very good post which I really enjoyed reading. It is not every day that I have the possibility to see something like this..
    data science training in pune

  15. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    iot training in hyderabad

  16. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors.
    business analytics courses

  17. I’ve read some good stuff here. Definitely worth bookmarking for revisiting. I surprise how much effort you put to create such a great informative website.
    data scientist training and placement

  18. Glad to chat your blog, I seem to be forward to more reliable articles and I think we all wish to thank so many good articles, blog to share with us. cyber security course in delhi

  19. We are tied directly into the sate’s renewal database which allows us to process your request almost instantly. buy essays data science training in noida

  20. Superbly written article, if only all bloggers offered the same content as you, the internet would be a far better place.. cyber security course in delhi

  21. Extremely overall quite fascinating post. I was searching for this sort of data and delighted in perusing this one. Continue posting. A debt of gratitude is in order for sharing.data scientist course in warangal

  22. I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read !! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site. data science course in kanpur

  23. Amazingly by and large very interesting post. I was looking for such an information and thoroughly enjoyed examining this one. Keep posting. An obligation of appreciation is all together for sharing.data science training in gwalior

  24. I think it could be more general if you get a football sports activity data science course in surat

  25. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
    data science training in trivandrum

  26. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
    data analytics course in faridabad

  27. This Is An Excellent Post I Seen Thanks To Share It. It Is Really What I Wanted To See Hope In Future You Will Continue For Sharing Such A Excellent Post. Data Science Course In Chennai

  28. Admiring the time and effort you put into your blog and detailed information you offer!.. business analytics course in surat

  29. I would like to say that this blog really convinced me to do it! Thanks, very good post. data analytics course in mysore

  30. It is perfect time to make some plans for the future and it is time to be happy. I’ve read this post and if I could I desire to suggest you few interesting things or tips. Perhaps you could write next articles referring to this article. I want to read more things about it! data scientist course in kanpur

  31. I want to leave a little comment to support and wish you the best of luck.we wish you the best of luck in all your blogging enedevors
    data science course in trivandrum

  32. I can set up my new thought from this post. It gives inside and out data. A debt of gratitude is in order for this significant data for all, data scientist course in surat

  33. The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my choice to read, but I actually thought you have something interesting to say. All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention. data analytics course in mysore

  34. Thank you quite much for discussing this type of helpful informative article. Will certainly stored and reevaluate your Website.
    Data Analytics Course in Bangalore

  35. I am impressed by the information that you have on this blog. It shows how well you understand this subject.data science course in trivendrum

  36. Data Science is a booming field with ample job opportunities. Start your preparation today with 360DigiTMG and become a Data
    Scientist in the right way.business analytics course in pondicherry

  37. Viably, the article is actually the best point on this library related issue. I fit in with your choices and will enthusiastically foresee your next updates. data science course in pune

  38. Advance your technical skills required to crack huge datasets to bring out new possibilities from data. Join the Data Science institutes in Bangalore and get access to top industry trainers, LMS, live projects, assignments, and mock interviews to skyrocket your career in the ever- evolving field of Data Science.

    Data Science Course in Bangalore with Placement

  39. Learn to build powerful models to solve business problems by generating useful insights and discover the various scientific processes and methods used to transform the information available in huge datasets into meaningful results. master all the tools and techniques in Data Science and gain domain-specific knowledge which will help you to add more value to your profile. Sign up for the Data Science course in Bangalore with Placements and multiple your chances of working across all industries and job functions.

    Data Science Training in Delhi

  40. 360DigiTMG provides exceptional training in the Data Science course with placements. Learn the strategies and techniques from the best industry experts and kick start your career.data analytics course in jalandhar

  41. https://www.oracleracexpert.com/2019/05/recovering-tables-and-table-partitions.html

    data science course in patna

  42. Get the best Data Science online course at the comfort of your home. Flexible timings, Best industry trainers, and meticulously crafted curriculum. Avail now!!!!business analytics course in varanasi

  43. Healthcare has reached a new end, and in the past, there was less usage of data science tools in the medical field, but now all the hospitals are using modern products to make the field safe and secure.

  44. I'm glad to hear that you found the information helpful! It seems like you're expressing your appreciation for a piece of content that highlighted the benefits of taking a data science course with 360DigiTMG. According to the information you provided, pursuing such a course can make a significant impact on one's career prospects, especially in today's highly competitive job market.
    best data science course institute in hyderabad