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


42 comments:

  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"

    ReplyDelete
  2. 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
  3. 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
  4. 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
  5. 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    data science course in patna

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

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

    ReplyDelete