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


31 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
  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

    ReplyDelete
  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

    ReplyDelete
  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

    ReplyDelete
  12. من اجود وارخص الشركات التي توجد في منطقة مكة المكرمة والتي تعمل في مجال نقل العفش مع الفك والتركيب تلك الشركة الجيدة التي تقدم خدمات جيدة وتسمى افضل شركة نقل اثاث بجدة ايضا وهي التي تختص بنقل العفش من بيت الى بيت آخر في مدينة جدة وما جاورها من مناطق تابعة لها وقد نضطر قبل نقل العفش الى المكان الجديد الى تنظيف المنزل الجديد قبل النقل من افضل شركات منطقة مكة المكرمة التي تعمل في مجال تنظيف وتعقيم المنازل تلك الشركة الجيدة التي تقدم خدمات جيدة في تنظيف المنازل من الداخل ومن الخارج وتسمى شركة تنظيف منازل بجدة وهي التي تختص بأعمال التنظيف للمنازل الجديدة والمفروشة ومن الأفضل ان تقوم بعمل مكافحة للحشرات بالتواصل مع افضل واقوى الشركات التي تقدم خدمات مكافحة حشرات المنزل البق والصراصير والنمل والعته تلك التي تستخدم مبيدات آمنة وفعالة ومضمونة مثل افضل شركه مكافحه الصراصير بجده التي تتعامل في مكافحة الحشرات وتستخدم مبيدات آمنة ومضمونة ونحتاج ايضا الى تنظيف الخزان وذلك بالتعرف على اكبر وافضل واقوى شركات تنظيف خزانات المياه بجدة تلك التي تقوم بأعمال تنظيف وتعقيم خزانات المياه في جدة بمنتهى الدقة والاحترافية وتقدم ايضا شركه نظافة الخزانات بجده افضل الخدمات الجيدة في تنظيف وتعقيم الخزانات لكي تحافظ على الماء نظيفا ومعقما اطول فترة زمنية ممكنة لكي تكون مياهك نظيفة فان شركه نظافة منازل مستعدة لاستقبال مكالمتكم في أي وقت وطلب الخدمة

    ReplyDelete
  13. من اجود وارخص الشركات التي توجد في منطقة مكة المكرمة والتي تعمل في مجال نقل العفش مع الفك والتركيب تلك الشركة الجيدة التي تقدم خدمات جيدة وتسمى افضل شركة نقل عفش بجدة ايضا وهي التي تختص بنقل العفش من بيت الى بيت آخر في مدينة جدة وما جاورها من مناطق تابعة لها وقد نضطر قبل نقل العفش الى المكان الجديد الى تنظيف المنزل الجديد قبل النقل من افضل شركات منطقة مكة المكرمة التي تعمل في مجال تنظيف وتعقيم المنازل تلك الشركة الجيدة التي تقدم خدمات جيدة في تنظيف المنازل من الداخل ومن الخارج وتسمى شركة تنظيف شقق بجدة وهي التي تختص بأعمال التنظيف للمنازل الجديدة والمفروشة ومن الأفضل ان تقوم بعمل مكافحة للحشرات بالتواصل مع افضل واقوى الشركات التي تقدم خدمات مكافحة حشرات المنزل البق والصراصير والنمل والعته تلك التي تستخدم مبيدات آمنة وفعالة ومضمونة مثل افضل شركه مكافحه حشرات بجده التي تتعامل في مكافحة الحشرات وتستخدم مبيدات آمنة ومضمونة ونحتاج ايضا الى تنظيف الخزان وذلك بالتعرف على اكبر وافضل واقوى شركات تنظيف خزانات المياه بجدة تلك التي تقوم بأعمال تنظيف وتعقيم خزانات المياه في جدة بمنتهى الدقة والاحترافية وتقدم ايضا شركه تنظيف خزانات بجده افضل الخدمات الجيدة في تنظيف وتعقيم الخزانات لكي تحافظ على الماء نظيفا ومعقما اطول فترة زمنية

    ReplyDelete
  14. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    data science course in mumbai

    ReplyDelete
  15. I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
    data analytics courses

    ReplyDelete
  16. 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...

    ReplyDelete
  17. If you have applied for a new government job or want to get some information about an exam, then you can get the exact updates of every information with Sarkari Jobs, with the right Sarkari Exam notification and much more.

    ReplyDelete
  18. Good job by admin to share this information

    ReplyDelete
  19. This is a wonderful article, Given so much info in it, Thanks for sharing. CodeGnan offers courses in new technologies and makes sure students understand the flow of work from each and every perspective in a Real-Time environmen python training in vijayawada. , data scince training in vijayawada . , java training in vijayawada. ,

    ReplyDelete
  20. wonderful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article resolved my all queries. keep it up.
    data analytics course in Bangalore

    ReplyDelete
  21. 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

    ReplyDelete
  22. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article. This article inspired me to read more. keep it up.
    Correlation vs Covariance
    Simple linear regression
    data science interview questions

    ReplyDelete
  23. By reading this article, many things have piled up in my mind which made me to think about it.
    SAP training in Kolkata
    SAP course in kolkata
    SAP training institute in Kolkata

    ReplyDelete
  24. You have put some high quality and valuable information here that any reader would love to read. I sincerely share many of your views in this article.
    SAP training in Mumbai
    SAP course in Mumbai
    SAP training institute Mumbai

    ReplyDelete
  25. 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

    ReplyDelete