I have been so far a reader / observer in SDN blogs. I felt I should also share the little knowledge and the experience which I have with you all.
Hence I decided to write about Database flash back, do you know why? This new feature of oracle 10g gave us some relaxation from some of major incidents and database backup restoration activities. You may or may not know this; but I feel this could help you in some case or you can just brush up your knowledge.
We had a major incident couple of months back which is, “production users were deleted during planned down time instead of locking the users by mistake”. We had only two choices, from the SUIM logs, create all users and assign them to the respective roles OR restore the latest backup. Considering the situation backup restoration may be difficult as it would consume about 10+hrs. Unfortunately ECat user creation program which we tried to create users automatically / set password of all users also did not work. We took a decision to create all users manually with the help of my entire team and we had done it. Being a basis consultant, post this major incident, I was looking at options to over come this kind of situations. This incident triggered me to first list down all such major incidents which can occur inside SAP to go SAP down. Some of them are,
- All Users / roles deleted in systems.
- While applying support pack, error occurred or manual interruption occurred which is not possible to solve immediately, which are required a backup to restore.
- Client Refresh was not successful which made the sap system down.
- Some of the critical activities which are planned to be carried out in SAP with backup of database, so that if there is anything goes wrong, then backup can be restored to bring the situation back to normal.
Aforesaid problem can occur rarely, but the rare things make huge escalations, raise challenges for us. One of the best options and immediate solution to these problems is Database Flashback which I could find and comes with Oracle 10g. Here the testing of database flashback….
01. Enabled Database flash back on my test system.
02. Re-produced most of major incidents in test system
03. Brought the system back to his original status.
We can recover the database before a minute/second to the incident which occurred in system or to particular recovery point which we have marked.
01. Made a recovery point at Database level.
02. The support pack level was at SAPKB70013 and Applied two support packages SAPKB70014 to SAPKB70016.
03. Flash back the database to the recovery point.
04. Now the Support level back at SAPKB70013.
The only RISK in using Database flashback is you will not be able to get the data which are entered post incident. Consider the situation that during peak hours where all users are entering the records and a table is deleted, the table may be crucial but not impacting business operations. In this situation, recovering this table is possible thru database flashback, but data post table drop is lost and that needs to be re-entered again.
But the advantages With Database Flash back,
- We can avoid taking down time for backup and restoration for doing any SAP / DB activities.
- Human errors can be avoided
- Major incidents can be resolved within less time and effort