How to do a point in time restore of one pluggable database with multiples

By | In Oracle | February 08th, 2017

This post will show how to do a Point in Time Restore of one pluggable database in a container with multiple pluggable databases.

In this scenario we are fixing the following mistake: You dropped a table in your pluggable databases PDB1 and PDB2. But oops! You only meant to drop the table in PDB1. So now you need to do a point in time restore on PDB2 to right before you dropped the table.

Requirements: In order do to a point in time restore, you need to have a backup.  You also need to have an auxiliary dest configured in RMAN.

Process

1. Login and show the list of pluggable databases

2. Create a table in three different pluggable databases

3. Backup database

4. Drop the table in PDB1 and PDB2

5. Restore pluggable database PDB2 to point in time before the tables are dropped

6. Show the table has been restored on PDB2, but is still missing from PDB1, which is is the goal of this test scenario.

7. Conclusion

 

1. Login and show the list of pluggable databases

Set your environment on your server – I use a shell script.

List names of all all containers:

s

2. Create a table in three different pluggable databases

Switch a few log files – this must be done from the root container

Check that the tables exist in each pluggable database:

3. Create an RMAN backup

4. Drop the table in PDB1 and PDB2

Check the time

Look for your tables.  Oops you only meant to drop one of them, and now it is missing from two pluggable databases!

6. Show the table has been restored on PDB2, but is still missing from PDB1, which is is the goal of this test scenario.

 

Voila.  Restore to a point in time is successful and your table has reappeared.

7. Conclusion

With an RMAN backup you can restore one pluggable database while leaving others as they are. This would be very handy in situations where you have DEV, TEST and QA as pluggable databases on the same server. Each can be restored or refreshed independently. The caveat is that if you are using flashback database, you will not be able to flashback any pluggable to a point in time before your restore.

Make sure you have an auxiliary destination setup in RMAN. This will probably not be an issue in a real life situation, but you may run across it testing this in a lab. The auxiliary destination allows RMAN to create some temporary files (Control file and sysaux table space) that are used for the restore and then deleted.

Go forth and test, and then be ready to use this process when you need it.

 

Datavail Script: Terms & Conditions

By using this software script (“Script”), you are agreeing to the following terms and condition, as a legally enforceable contract, with Datavail Corporation (“Datavail”). If you do not agree with these terms, do not download or otherwise use the Script. You (which includes any entity whom you represent or for whom you use the Script) and Datavail agree as follows:

1. CONSIDERATION. As you are aware, you did not pay a fee to Datavail for the license to the Script. Consequently, your consideration for use of the Script is your agreement to these terms, including the various waivers, releases and limitations of your rights and Datavail’s liabilities, as setforth herein.

2. LICENSE. Subject to the terms herein, the Script is provided to you as a non-exclusive, revocable license to use internally and not to transfer, sub-license, copy, or create derivative works from the Script, not to use the Script in a service bureau and not to disclose the Script to any third parties. No title or other ownership of the Script (or intellectual property rights therein) is assigned to you.

3. USE AT YOUR OWN RISK; DISCLAIMER OF WARRANTIES. You agree that your use of the Script and any impacts on your software, databases, systems, networks or other property or services are solely and exclusively at your own risk. Datavail does not make any warranties, and hereby expressly disclaims any and all warranties, implied or express, including without limitation, the following: (1) performance of or results from the Script, (2) compatibility with any other software or hardware, (3) non-infringement or violation of third party’s intellectual property or other property rights, (4) fitness for a particular purpose, or (5) merchantability.

4. LIMITATION ON LIABILITY; RELEASE. DATAVAIL SHALL HAVE NO, AND YOU WAIVE ANY, LIABILITY OR DAMAGES UNDER THIS AGREEMENT.

You hereby release Datavail from any claims, causes of action, losses, damages, costs and expenses resulting from your downloading or other use of the Script.

5. AGREEMENT. These terms and conditions constitute your complete and exclusive legal agreement between you and Datavail.

 

 

Contact Us
Megan Elphingstone
Senior Oracle Database Administrator
Megan has been working with Oracle databases for over 15 years, including 10 years as an Oracle Database Administrator. Megan has supported over 200 production databases - performing upgrades, infrastructure moves to the cloud, tuning, performance testing, implementing high availability solutions, including moves to ASM, Data Guard, RAC, and Golden Gate. Megan’s favorite part of being a DBA is helping customers and users solve problems and implementing solutions that are automated, stable, and reliable. When she isn’t working, Megan is biking or snowboarding with her family.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).