Skip to Content

Purpose:

The primary purpose of this blog is to demonstrate how a skilled person can solve problems quickly and efficiently than by several groups. I don’t suggest one person could solve all problems in all situations. The point I’m trying to make is that a Basis Administrator should explore all opportunities to gain experience in non-SAP layers. Opportunities to gain knowledge in other layers used to be easy in the past – 15+ years ago. Now for more than one reason, I see more and more Basis Administrators focusing on just SAP-ABAP and Java layers. They don’t even consider the importance of ABAP/programming knowledge to solve performance issues. With skills just in SAP layer, Basis Administrators’s scalability-to be able to support large and complex landscapes- would become an issue. 

Background:

Recently we converted Oracle/Solaris based BW system to Unicode. Our testing in non-P systems didn’t reveal any serious issues.So after working more than 48 hours non-stop(well almost), we delivered the system ahead of time to BW team. They started testing. Two hours into testing, we experienced a serious error. See the screenshot below. Oracle started showing ORA-00600 errors. Anyone working in Oracle would know this error is normally a serious error. Since we didn’t run into this error in non-P systems, we suspected a very serious situation. We were on a conference call while this issue was discovered. Here is one of many alerts we received from Remedy system:

Action:

Immediately I logged into Solaris server to see if there were any other useful messages in Oracle’s alert.log. I was really panicked at this point for more than one reason:

  • Already worked 48+ hours (almost) non-stop. 
  • ORA-00600 error is normally very serious – anyone who has worked in Oracle would know.
  • 3 other system were converted, didn’t run into this issue.

Oracle log showed additional entries indicating there were unusable indexes with name SAPSR3.”/BIC/B0003420000″ etc. Immediately after seeing this error, I felt a little bit better, told myself – no need for panic. Any table in BW with names such as “/BIC/B000*” are either PSA or Change Log Tables. These tables are normally temporary tables(PSA is definitely temporary tables, change logs-if used as designed-are also temporary tables holding records for slightly longer periods depending on business needs. Since the purpose of this blog is not about CL or PSA, I don’t want to get into the details). They’re not accessed directly by the users. I also knew weekly PSA data deletion jobs were running when I was monitoring the system after Unicode conversion. I also knew these tables are partitioned. And there is a bug with ~900 index of F table of an aggregate. Somehow I thought the error I see now is related to ~900 bug. What is that bug? Read Lars’s blog: Question to SAP Support “Why is partition pruning not working in my BW on Oracle?”.

As I explained, PSA/Change log tables are partitioned. Underlying indexes could either be global index or local to the partition. For those who don’t understand partitioning, I’m planning to write blogs on partitioning. The indexes should be created LOCAL for a partitioned table if you’re planning to drop one/more partitions. Otherwise if a partition is dropped, the global index would become unusable unless the index is rebuilt after dropping the partition. I believed this was exactly what was happening. Temporarily I guessed the error could be fixed by rebuilding the index using SE14. However one issue with SE14 is that the primary index can’t be dropped. So I performed two things:

  1. Checked whether the table was PSA or change log. You can check this in either RSTSODS table or in the description of the table in SE11 for the table shown in alert.log. PSA’s table description would be something like “Transfer Structure for…”. SE11 showed “Transfer Structure for..”.  This is a great news because change log tables are huge in this customer’s site. Rebuilding index for CL would take time whereas PSA index would take no time because PSA deletion jobs run regularly every week.
  2. In SQLPLUS, I dropped the index and rebuilt it in SE14.

Analysis:

What happened? Why we didn’t run into this issue in other non-P systems? Did you guess it?  Well, this functionality was not tested. We ran into this error in Development system two weeks after going live.(Nothing new I guess). Since BW system uses partitioning feature more than non-BW system(almost zero in non-BW systems unless performed by the customer), we should use SMIGR_CREATE_DDL to generate Data Definition Language(DDL) statements for the objects in the database. I suspected a bug in  SMIGR_CREATE_DDL. I checked the output of *.SQL files generated by that script. I couldn’t figure out. I generated those files with older version SMIGR_CREATE_DDL. I ran diff command between old and new SQL files. I saw LOCAL was missing in newer files used by Unicode conversion job. I checked marketplace for any note corrections for SMIGR_CREATE_DDL. Yes, I found one. That correction should’ve been applied before running the script/Unicode Conversion. Since it was not tested in non-P systems, we didn’t know about that bug. Now I had two options: 1) Open SAP message or 2) Write my own script to generate Drop/Recreate statements for all indexes of PSA/Change log tables using AWK. Since writing script is easier than contacting SAP support I decided to write awk script.Currently we’re testing in non-P systems. One more thing: Since these tables are customer based, we can’t use older version’s SMIGR_CREATE_DDL’s output because the names would be different in different systems/days.

New SMIGR_CREATE_DDL’s output showing missing “LOCAL”  keyword:

Old SMIGR_CREATE_DDL’s output showing LOCAL keyword:

AWK script which parses new APPL1.SQL, retrieves “CREATE UNIQUE INDEX” Statement and adds DROP INDEX command before CREATE UNIQUE INDEX statement. 

Conclusion:

I guess this example demonstrates how multi-skilled person can solve a problem quickly without escalating the situation. What skills were used to identify and come up with a solution quickly and efficiently. This table would provide the details:

To report this post you need to login first.

10 Comments

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

  1. muthuraman govindasamy
    I like your heading in the Blog “COMBO SKILLS”. Combo skills always helps but Jack of all but none of king..

    I used to see  COMBO in Restaurents but our COMBO Skills also good for SAP consultants for negotiations ,day to day life etc…
    Muthu

    (0) 
    1. Bala Prabahar Post author
      Thanks for your comment. I’m glad you liked title:).
      Solving complex problem(s) gives satisfaction for more than one reason($$  is one of them but not necessarily primary goal for some).
      From Jack of all but king of none standpoint, what I follow is “80:20” rule: 80% of the time one can solve an issue by having some (20%) knowledge of something(Example: shell scripting, one doesn’t need to be very strong in scripting to write awk scripts but basic to medium skills in scripting helps me a lot in day-to-day life as you suggested). The key is to know that there is a lot more to learn than what one knows. This knowledge(that one knew only a little) would help finding a right resource(people with 80% knowledge) in solving remaining 20% of issues. 20% of the cases, I would contact SAP Support for example.

      Regards,
      Bala

      (0) 
  2. Lars Breddemann
    Hi there!
    Nice to see that you keep on with blogging.

    And a good problem solving story as well.
    Just two comments on this:

    * As much as I like the can-do attitude approach of the AWK script solution, it misses to create the necessary ABAP DDIC entries for the indexes – so although the indexes will be correctly locally partitioned on DB level, the storage information in SE14 will still lack this information.

    * The second thing I’d like to adress is the “we worked 48 hours…” part. Why didn’t you or your team avoided this situation?
    It’s known for a long time now that focus, intelligence and the ability to understand complex scenarios decreases immensely when people get tired.
    And still you put the success of the productive migration at risk by creating such a situation?
    And yes, I am aware that this is common habit with projects – still this doesn’t make it any better.

    Hope to read more from your experiences with our technology.

    regards,
    Lars

    (0) 
    1. Bala Prabahar Post author
      Lars,

      I’m glad I wrote this blog. I didn’t think about DDIC part, duh! Great point Lars. Thank you.

      Second point: I don’t know how to avoid that situation. We tried our best to avoid but our team is lean and everyone was busy with  working on a different module. Agree, we need to work on this.

      Thanks for your comments. Greatly appreciated.

      Bala

      (0) 
    2. Bala Prabahar Post author
      Hi Lars,
      On ABAP DDIC:
      You’re technically correct. However my solution will work in real world. Here is why:

      1) SE14 will still show the index is partitioned. That info comes from Oracle’s catalog. As you pointed out, storage information would be missing in DDIC.
      2) Since this is Primary Index, we can’t drop it in SE14. That means no one would be recreating the index in SE14.
      3) During system copy:
           a) Redirected restore based system copy will be fine.
           b) For export/import based system copy, two scenarios possible:
                   i) If one doesn’t use SMIGR_CREATE_DDL, all tables will be created non-partitioned so global/local indexes are not relevant.
                  ii) if one uses SMIGR_CREATE_DDL, then it creates partitioned indexes based on Oracle catalog. I tested this in ECC 6, Basis 701 SP7.
      Only one situation where this wouldn’t work is when someone drops PRIMARY KEY in SQLPLUS and then decides to recreate the index in SE14. Any other scenarios?

      Thanks,
      Bala

      (0) 
      1. Lars Breddemann
        What?
        SE14 does _not_ show the Oracle DDIC settings, but instead the current settings as they are stored in the ABAP DDIC(!).
        You can, however, change these settings and do this based on the current DB settings.

        Also, you have to use SMIGR_CREATE_DDL when doing export/import based system copy. When the formally partitioned objects are created unpartitioned, applications like BW will just not work properly (see Unusable indexes).

        Anyhow – I didn’t wanted to discuss the technical feasibility of your hands-on workaround to the problem.
        It’s surely good to be able to fix such issues quickly – but as this was clearly a bug the final solution still needs to be applied via a note or a SP. Every additional step (like fixing the DDL statements) will otherwise make system copies only more complex.

        regards,
        Lars

        (0) 
        1. Bala Prabahar Post author
          Hi Lars,

          SE14 shows Oracle’s settings. 100% sure.

          As I stated, we don’t need to fix DDL statements created by SMIGR_CREATE_DDL. This script will create  DDL statements with correct syntax. No fixing the DDL statements will be required.I don’t believe there is a note correction available currently. If there is one, I would definitely apply it.

          Thanks,
          Bala

          (0) 
          1. Lars Breddemann
            we’re talking cross each other.
            You’re referring to what you see when you open SE14 and display the storage parameters (which are indeed determined from the DB).
            This is kind of “IS” situation.

            I was referring to the settings for the new creation (technical settings) which define how the index will be created by the DB-Interface layer, when it needs to be dropped and rebuild.

            And guess what: if this is a bug and there is no fix yet, or you didn’t find it, a support message is the right way to figure this out 😉

            regards,
            Lars

            (0) 

Leave a Reply