Flashback query has been a boon from Oracle since its introduction in 9i. In a PeopleSoft environment there are many areas a DBA can utilize this feature. The commands are straightforward and will make your life easier if you remember them.
Consider a scenario where you are doing a data fix in production database. You have been extremely busy today and swamped with 4 high priority problem tickets. You maintain your composure and use your multitasking skills.
The Functional Consultant gave you the below SQL to perform the data fix.
DELETE FROM PS_ABC_VCH_APPR WHERE VOUCHER_ID IN (‘7865419’,’7865438’,’7865428’) AND ENTRY_STATUS IN (‘P’,’X’);
It will delete 12 rows and you confirm the same. You issue commit, close the problem ticket and start working on the next high priority ticket. Phew, one down and 3 more to go…
You get a phone call from the functional consultant 15 mins after you closed the data fix problem ticket. He wants to re-open the problem ticket and request a restore. The fix did not work and they have identified a workaround that can be performed online. He thanks you for taking care of this so quickly and requests you to call him when the data is restored. As you hang up, you begin to feel a little bit of nervousness. Did you or did you not take a backup? Then it strikes you that you forgot to take the backup in your rush to execute the ticket and assuming that the functional team rarely request restore!! Hmm…
In these scenarios, the below SQL will be a life saver!
INSERT INTO PS_ABC_VCH_APPR SELECT * FROM PS_ABC_VCH_APPR AS OF TIMESTAMP (SYSTIMESTAMP – INTERVAL ‘20’ MINUTE) WHERE VOUCHER_ID IN (‘7865419’,’7865438’,’7865428’) AND ENTRY_STATUS IN (‘P’,’X’)
The SQL above will insert into PS_ABC_VCH_APPR all rows that existed for the above criteria 20 minutes ago.
Or
INSERT INTO PS_ABC_VCH_APPR SELECT * FROM PS_ABC_VCH_APPR AS OF TIMESTAMP TO_TIMESTAMP(‘24-aug-07 14:35:00′,’DD-MON-YY HH24: MI: SS’)WHERE VOUCHER_ID IN (‘7865419’,’7865438’,’7865428’) AND ENTRY_STATUS IN (‘P’,’X’)
The SQL above will insert into PS_ABC_VCH_APPR all rows that existed for the above criteria at the date and time mentioned.
þ You get a call at 4am from the Help Desk, an incomplete migration yesterday evening has resulted in messing up the data after the custom SQR process ran and failed, the functional analyst is requesting an emergency restore of the database. He wants the database to be restored to a point in time just prior to running the SQR process, and complete the migration. Why does this always happen when you are on-call??
You know that restoring the database is going to take 3-4 hours and keep your finger crossed (hoping the hot backup and archive logs are valid).
Instead, you call the functional analyst, check out the custom SQR process and determine that the batch job only impacts 12 tables. You tell him that it is not too late and you will use the flashback feature to get back the data in those 12 tables to a state prior to execution of the batch job!! This has saved your client 3+hrs of downtime and we all know that time is $$$.
Note 1 – It is not recommended to use the flashback feature as a backup policy but as an option in emergency scenarios where you forgot to take a backup or the backup got corrupted.
Note 2 – Do not forget to read about the limitations. You can only flashback if your UNDO segments have this information. This is driven by the parameter UNDO_RETENTION and the fact that Oracle can only honor if enough undo space is available for active transactions.
Summary – Though we still do not have the capability to flashback in our real life (oh we wish we could go back in time and correct that one mistake we made in our life) but there are many scenarios in a real world PeopleSoft environment which can utilize the flashback feature. We just have to keep our mind open to the possibilities that this feature provides.
