Skip to Content

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.  

Example:  

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
To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

  1. David Hull
    I’m not sure how you locked the users, but you don’t have to enable Flashback Database to use Flashback Query.  With Flashback Query, you can restore individual tables to an SCN prior to the accidental deletion.  Flashback Database may be more appropriate for major system changes, such as SPS installs or upgrades where you want to revert the state of the entire database, but Flashback Query would be more appropriate for minor incidents like the one you described in your blog, where only a table or a few tables are involved.

    Regarding lock the users, the way I’ve always locked users in the past is to set the UFLAG column in table USR02.  When a user gets locked, SAP sets a value in here of 32, 64, or 128, based on the reason for locking the user.  Since any value other than 0 will lock a user, I usually pick a distinct value so that I know who was locked by me.  So, for example, to lock the users:

    sqlplus > UPDATE SAPB35.USR02
              SET UFLAG = ’69’
              WHERE UFLAG = ‘0’
              AND BNAME NOT IN (‘DDIC’, ‘SAP*’, ‘OTHER_USERS_YOU_DONT_WANT_LOCKED’);
    sqlplus > commit;

    Then, to go back and unlock them later:

    sqlplus > UPDATE SAPB35.USR02
              SET UFLAG = ‘0’
              WHERE UFLAG = ’69’;
    sqlplus > commit;

    Cheers,
    David.

    (0) 
    1. Iyyappan MR Post author
      Hi,

      When i say that the users locked, used the SU10 transaction code in SAP to do mass user lock/unlock. Also its a good practice that doing any activity at SAP level instead of DB level when the functionalities available for doing that at SAP level. Hope you agree to that.. The best practice that we follow is, restricted access to DB. We do not get into sqlplus unless and until it is critical. Hence the user lock/unlock which can also be done at DB level, is best to do at SAP level.

      Also activity wise if you look at its a Level-I consultant activity with whom we can’t/are not supposed to share the db access. Here your security place a major role. By giving the DB access to level-I and carrying out this activity at DB level will be more critical.

      You will not be able to enable table level flashback as we have morethan 14K+ tables in sap which will consume huge amount of time for enable and also deciding tables which are critical and enabling db flash back for them. hence using Database flashback would be approperiate for the changes done inside sap/db whether it is major or minor.

      Regards,
      Iyyappan MR

      (0) 

Leave a Reply