Using FLASHBACK DATABASE without losing current data.

Hello Oracle freaks. Welcome back to my blog. I had this thought in my mind where DBA’s generally configure flashback in their databases but then they never really use it in any recovery scenarios. Flashback is a real gift of Oracle to save us from those human mistakes and if we know how to cleverly use it in our real time environments it will be an added advantage. I know most of us think why should I use flashback to restore user data  when I know that it will restore back the entire database to previous point in time loosing all current data. For example , suppose your application team has truncated some of its tables mistakenly and asks you to restore it back. You are in a scenario where application team has not taken the backup of the tables and there would be no other option but to restore an auxiliary instance and do tablespace point in time recovery. This is an immense time taking exercise for the DBA’s and also on top your head you need to estimate additional storage, time and knowledge  that will be required to accomplish this task. But with the method that I will show, you can easily bring back the data with FLASHBACK and what more do you need when you know that the database will be intact and will not require it to be opened with RESETLOGS option to a previous flashback time. So without wasting any more time lets go ahead.

Please Note : To implement the below steps you should have the database flashback feature on (which we generally do for Production/DR databases). If you know that you have a restore point created before that would also suffice.

In the below database I have the flashback feature on in the database .

In the above database I have the flashback retention set to 1440 minutes. This means that the database will hold the flashback logs for 24 hours by default. So logically we can rollback the database to any time or SCN that falls within the last 24 hours.

Now lets create a test table in “pulakesh”  schema of our database. Since this is 12c I will be creating the objects in the pluggable database “TESTPL1”.

Cool we got the table created . Now lets see what is the current SCN of the database. Later if we rollback the database to this SCN we should get back the table rows.

.

Got it..! Now lets assume that something went wrong and the table is truncated.

Aahh..!! never want to be in this situation without a backup. Now think in a scenario where you do not have additional storage to create an auxiliary and do tablespace point in time restore to export the table and import it back to original. Also you have time constraints and the table is the most important table of the application. Well at this point you have an option that is pretty easy and does not really require the expertise to do other stuff. Simply as usual next step would be to flashback the database to a point before the truncate. It could be time before truncate or scn before truncate. In our case lets take the SCN we recorded before the truncate.

Now the trick is at this point. We generally do a open RESETLOGS at this point to open the database. However do not do RESETLOGS here. Instead lets do a open readonly here. Remember readonly does not require the online logs and hence no resetlogs here. Cool, lets see how it works.

Perfect..! Now lets verify if we have got the table rows back.

Good as expected. For the example I have taken a small table. Now let’s assume that the rows were of GB’s and we need to get the rows exported out of this table from the READ ONLY database. But hold on the database is READ ONLY so can we do an export here ? Definitely NO. Because the moment you initiate datapump it will try to create the datapump related internal JOB table in the database and since it is in read only you will get an error. So we have passed all the hurdles except the datapump internal table creation in the database. Do not worry we have the famous database link option.  Yes it can read data of another database from any other local database. So in our case we need to get a database that is in read/write mode and the TESCON database can be reached via database link. So for this purpose lets create a database link to TESTCON from another database (EXADEV).

Here you can create the database link connecting to system user of testcon. The connect description has to use the pluggable “TESTPL1” database service name where our table was created. Prior to 12c it would be straight forward database service name.

Now since the database link is working,  we can now export the table out of the database using NETWORK_LINK option in Oracle :). Remember the role of local database “EXADEV” is only to allow datapump master table to be created on it. Lets go ahead..

Cool..! Table is exported successfully and we will need to now roll forward the original “TESTCON” database. Remember we did not do RESETLOGS, so we can recover the database upto the latest online log available before the shutdown. This would mean that all sequences are still in place and database will be as it is without losing any data (But we know how much we played with it :D).

Lets do a roll forward and see..

Remember since we are doing a complete recovery it is just “recover database” to roll forward

Bang on..! Lets see if it is in the poor state, the way we left it before flashback 🙂

Just as expected. Now is the time to import the dump back here and we are done..! Create the import directory in the database pointing to the dump location and do as below. In my case I will need to import into the pluggable database “TESTPL1”.

There you go. The rows are back and at the cost of zero data loss while flashback was still in use. This method is very simple and really does not require the expertise to do a tablespace point in time recovery on an auxiliary instance spending hours. It saves both time and storage. So until next post bye bye..!!. Happy Learning.

Either win or guide..!!