Musings of an IT Implementor

Proving new Oracle Index in SAP Without a Transport

2012-03-01T08:38:00.000Z

Let’s suppose you have created a new Oracle level index on a SAP table using SE11.
You’ve created it in DEV and proven that it changes the EXPLAIN PLAN using SQL Trace or Oracle level tools.

Unfortunately you don’t have a significant amount of data in your DEV instance, but you do have data in your UAT or integration test system.
You could release your workbench request that you will have created to create the new index in SE11, but this means a flurry of potential additional transports to remove it again if it doesn’t work.
You just want a proof of concept that can be removed again.

Since it’s entirely possible for you to create an index at the Oracle level below SAP, you just need to ensure that you create the index the same as SAP would.
You can choose a couple of methods to ensure this, but we will discuss the SAP biased method.

Using SE11, find the table on which the new index exists in DEV.

NOTE: We’re showing R/3 Enterprise, but it should be similar for Netweaver based systems.

Click the “Indexes...” button to list the indexes:

Double click the new index you created earlier.
Now on the menu, select “Utilities -> Activation Log”:

Click the magnifying glass icon next to the “Technical log for mass activation” option on the main screen.

Expand all the possible expansion icons:

Look for the text “CREATE” to find the create statement:

Copy the text and paste into SQL*Plus in any other SAP systems Oracle database to create the index.
You’re done.

Just remember that this index does not exist in the SAP data dictionary, so you must remove it once you have proven the EXPLAIN PLAN is working for the larger amount of data.

Running Oracle Production Database on VMware

2012-02-27T08:36:00.000Z

Are you considering running Oracle production databases on VMware?
Obviously you've considered the Oracle support policy on this.
How likely is it that you will get asked to "make it physical"? or How will Oracle support deal with your call if you tell them you're on VMware?

Well, there are some harsh responses from Oracle towards VMware customers if you have access to Metalink (sorry, My Oracle Support).

First you should read this doc: 1071005.1 (HOTSPOT ERROR DURING 32-BIT 11GR2 CLIENT INSTALL ON 64-BIT (X86_64) SUSE (VMWARE)).

Then read this document: 1075717.1 (Installing 32-bit RDBMS Client software on x86_64 Linux.)

I don’t think the first problem’s resolution is justified. Do you?

I have found Oracle notes that state the following:

"(1) Make sure you are logged into the Server Console directly, and that you are NOT trying to install the patch over a remote connection (such as Terminal Services, Remote Desktop, Timbuk2, PCAnywhere, VNC, VMWare, etc.) "

"As for installing over a remote connection, we do not support this, because Oracle cannot control the way the permissions are setup, over the remote connection."

So VMware console is a remote connection...

Then there is this document:

http://www.oracle.com/us/solutions/sap/wp-o10g-rac-config-win-303805.pdf

This doc is an Oracle whitepaper which details SAP Netweaver / Oracle Databse 10gR2 RAC on Windows 2003.
In the doc it shows an example hosts file which is clearly from a VMware hosted server. Double standards I feel!

This is just not a nice place to be if you're trying to convince a company that VMware is a solid, supported tool to run production databases, and that Oracle even support RAC on it now. Maybe some of the notes are old, before the acceptance by Oracle that VMware is becoming big in many companies. Or maybe Oracle's RAC support is an illusion of good will, whilst they quietly (or not so) improve the Oracle VM product.
The best way to tell, would be the acceptance of Oracle that VMware's vCPU allocation is acceptable as a form of hard partitioning, so that you can bring the Oracle DB license cost down by runnining on VMware, in the same way you can on Oracle VM.

_fix_control bug

2012-02-26T08:19:00.000Z

It turns out that the issue with the Oracle init param: "_FIX_CONTROL" preventing the database from starting (I blogged about this before), is bug 7509689: "_FIX_CONTROL" IS CAUSING TROUBLE DURING STARTUP.

It's fixed in 10.2.0.5.

Use Oracle 10g Segment Advisor Usage

2012-02-17T11:38:00.000Z

Generally, the Oracle 10g Segment Advisor collection job runs automatically out-of-the-box in an Oracle 10g install.
It's useful to run the advisor sometimes on large indexes as it should be able to report whether the index is efficiently storing index records, or if it could be re-built.
Oracle Enterprise Manager is already capable of pulling Space Advisor information from Oracle database.

Here are some links to the Oracle docs:

Oracle Doc: 10g Advisors
Oracle Doc: 10g Segment Advisor

Oracle Doc: Manually Running the Segment Advisor to Reclaim Wasted Space

An excellent whitepaper on pro-actively managing space in the Oracle 10g database.

The collector job (Automatic Segment Advisor) analyses segments in the database and compares to AWR reports to produce recommendations which can be reported on.
You can check the last run of the job using the SQL below:

-- Check the DBA SEGMENT ADVISOR job is collecting data.
COL actual_start_date FORMAT A20
COL run_duration FORMAT A15
COL job_name FORMAT A30
SELECT TO_CHAR(actual_start_date,'DD-MM-YY HH24:MI:SS') start_date,
run_duration,
job_name
FROM dba_scheduler_job_run_details
WHERE owner='SYS'
AND job_name = 'AUTO_SPACE_ADVISOR_JOB'
ORDER BY actual_start_date;

Once the collector is running, you can query the database using the DBMS_ADVISOR package to create analysis tasks that will query the recommendations and produce a recommendation report.

NOTE: The collector job does not analyse every object.

Below is the process I used to create a simple task to analyse a specific table and an index:

-- Create some variables to hold our task details.
VARIABLE TASK_ID NUMBER;
VARIABLE TASK_NAME VARCHAR2(100);
VARIABLE OBJECT_ID NUMBER;

-- Create a new empty task, which will populate the variables just defined.
EXEC DBMS_ADVISOR.CREATE_TASK('Segment Advisor', :TASK_ID, :TASK_NAME);

-- Assign a table and index check to the new task (replace <TABLE NAME> and <INDEX NAME>).
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, 'TABLE', '<SCHEMA>', '<TABLE NAME>', NULL, NULL, :OBJECT_ID);
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, 'INDEX', '<SCHEMA>', '<INDEX NAME>', NULL, NULL, :OBJECT_ID);

-- Execute the task.
EXEC DBMS_ADVISOR.EXECUTE_TASK(:TASK_NAME);

NOTE: On a 40GB table this took approximately 10 minutes.

-- Check the results in two ways:
-- Query the tables DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_ACTIONS.

-- or use DBMS_SPACE package (recommended).

SELECT
RECOMMENDATIONS RECOMMENDATION,
C1 ACTION1,
C2 ACTION2,
C3 ACTION3
FROM
TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS)
WHERE
TASK_ID = :TASK_ID;

-- Delete the task at the end.
EXEC DBMS_ADVISOR.DELETE_TASK(:TASK_NAME);

Disabling Change or Deactivation of SAP Audit Logging SM19 - SAL

2012-02-14T18:31:00.000Z

To disable changing or deactivation of the SAP Audit Logging (SAL) settings via transaction SM19, you can remove the authorisation object S_ADMI_FCD activity AUDA.
(see http://wiki.sdn.sap.com/wiki/display/SMAUTH/S_ADMI_FCD).

This would prevent “normal” BASIS administrators from being capable of changing or disabling audit logging, but permit you to provide an emergency user with this capability (you will need to change SAL settings in SM19 at some point!).

ORA-01114 IO Error But What Is The Oracle File

2012-02-12T11:10:00.000Z

Whilst creating a new 10GB index on a large 40GB table I was experiencing the
"ORA-01114: IO error writing block to file <file#> (block # <block#>)" error.

The main vital piece of information is the file number (<file#>). This can be found in the DBA_DATA_FILES or DBA_TEMP_FILES views as the FILE_ID column.

So, you construct a query to go find the file name:

SELECT file_id, file_name
FROM dba_data_files
WHERE file_id = <the number>
UNION ALL
SELECT file_id, file_name
FROM dba_temp_files
WHERE file_id = <the number>;

NOTE: I query both just in case. I’ve worked on systems where the TEMP tablespace has not been created as “TEMPORARY”!

After running the query, you get “0 rows returned”.
What!

Well, in this case, the TEMP tablespace was a proper TEMPORARY tablespace as the file_id was returned from the DBA_TEMP_FILES view.

I knew that the file numbers were somehow linked to the number of permitted files in the database. This is controlled by the DB_FILES database parameter.
Looking at the Oracle documentation (for 10g) here: http://docs.oracle.com/cd/B19306_01/server.102/b14231/dfiles.htm#i1206149

You will see that Oracle states that:
“...the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance.”

In short this means that the DB_FILES parameter only applies to datafiles and not tempfiles.
Therefore, all other files will be outside this parameter range.

My parameter DB_FILES was set to 1024.
My reported file# in the ORA-01114 error was greater than 1024.
If you look at the file_id values for the DBA_TEMP_FILES view, you'll notice that the file_id numbers conflict with the file_id values in DBA_DATA_FILES.
Since the tempfiles exist outside the range of DB_FILES, I subtracted the value of parameter “DB_FILES” from the reported file#, to achieve the file_id for tempfiles belonging to temporary tablespaces.
Voila!

Sure enough, when I subtracted 1024 (my DB_FILES value) from my reported file id in the ORA-01114, I got 2, which equated to the second tempfile in the TEMP tablespace.
The partition that the tempfile was sitting in was 95% full, but the tempfile couldn't expand by the requested next extent size as it would be more than the partition free space. Increasing the partition size fixed my problem.

Is there another way of viewing the online file# vs the DBA_x_FILES file_id field? I don't know, but if I find one, I'll post it here.

SAP Short Dump SYSTEM_NO_TASK_STORAGE

2012-02-08T18:30:00.001Z

A background job has produced a short dump with SYSTEM_NO_TASK_STORAGE.
The “Heap Memory” section in ST02 showed a maximum use of 1.5GB, but we had allocated nearly 1.9GB according to the "abap/heap_area_nondia" and "abap/heap_area_dia" instance profile parameters.

The ST02 short dump analysis shows the source code line where the problem occurred. The line of code doesn't look specifically interesting, it is, however, requiring a slight increase in memory allocation.
If the program is running in DIALOG then it will allocated Extended Memory first, followed by HEAP (local process) memory.
If the program is running in BACKGROUND then it will be allocated HEAP memory first, followed by Extended Memory.

Extended Memory is pre-allocated at system startup according to the EM initial setting in the SAP instance profile. Extended Memory is then increased up to the maximum specified (in instance profile) per user and per application server.
HEAP memory is only allocated as it is needed within each of the SAP dw.* OS processes, up to the maximum specified (in instance profile) per user or per application server.

Since this was a background job, we can assume that we exhausted HEAP memory and should have automatically switched to Extended Memory.
However, the Kernel section of the short dump showed that we may have experienced an issue obtaining more virtual memory from the OS:


*** Error in libunwind: Out of memory. Try with a higher value >       
 > for UNWIND_RESERVE_MEM (current value = 16).                         
 (0)  0x40000000017ae480 [dw.sapXXX_DVEBMGS01]                          
 (1)  0x40000000017ae2b0 [dw.sapXXX_DVEBMGS01]                          
 (2)  0x40000000021de880 [dw.sapXXX_DVEBMGS01]                          
 (3)  0x40000000021e45d0 [dw.sapXXX_DVEBMGS01]                          
 (4)  0x400000000115e860 [dw.sapXXX_DVEBMGS01]                          
 (5)  0x400000000120b3a0 [dw.sapXXX_DVEBMGS01]                          
 (6)  0x40000000010542f0 [dw.sapXXX_DVEBMGS01]                          
 (7)  0x4000000001111940 [dw.sapXXX_DVEBMGS01]                          
 (8)  0x40000000011ae790 [dw.sapXXX_DVEBMGS01]                          
 (9)  0x40000000012fb090 [dw.sapXXX_DVEBMGS01]                          
 (10) 0x40000000012fd7d0 [dw.sapXXX_DVEBMGS01]                          
 (11) 0x400000000188cae0 [dw.sapXXX_DVEBMGS01]                          
 (12) 0x4000000001896e70 [dw.sapXXX_DVEBMGS01]                          
 (13) 0x4000000001891670 [dw.sapXXX_DVEBMGS01]                          
 (14) 0x40000000018949a0 [dw.sapXXX_DVEBMGS01]                          
 (15) 0x400000000187f1e0 [dw.sapXXX_DVEBMGS01]                          
 (16) 0x40000000014d1ce0 [dw.sapXXX_DVEBMGS01]                          
 (17) 0x400000000149e8d0 [dw.sapXXX_DVEBMGS01]                          
 (18) 0x4000000001496fc0 [dw.sapXXX_DVEBMGS01]                          
 (19) 0x4000000001364c30 [dw.sapXXX_DVEBMGS01]                          
 (20) 0x4000000000ed4af0 [dw.sapXXX_DVEBMGS01]                          
 (21) 0x4000000000ed4a90 [dw.sapXXX_DVEBMGS01]                          
 (22) 0xc000000000045880  main_opd_entry + 0x50 [/usr/lib/hpux64/dld.so]

Is this case, you could either add more OS memory (essentially you have over allocated memory somewhere), or you could resize the abap/heap_area_nondia parameter to a smaller amount, so that it will switch to EM sooner.

Documenting an SAP ABAP System Technical Configuration

2012-02-01T18:04:00.000Z

The following are my thoughts for documenting the technical configuration of an existing SAP ABAP stack:

ENVIRONMENTS

ARCHITECTURE OVERVIEW

OPERATING SYSTEM DETAILS

PATCHES

PARAMETERS

USERS

DATABASE SYSTEM DETAILS

PATCHES

PARAMETERS

SAP SCHEMAS

DB LINK INTERFACES

SAP KERNEL DETAILS

PARAMETERS

SAP COMPONENT DETAILS

ABAP STACK DETAILS

LICENSES

OPERATION MODES

CLIENTS

TMS

STANDARD SAP JOBS & SCHEDULES

NON-STANDARD JOBS & SCHEDULES

SPOOL SERVERS

OUTPUT DEVICES

SYSTEM USERS

DATABASE CHECKS AND JOBS

CCMS MONITORING

CUSTOM DEVELOPMENTS

TRANSACTIONS

PROGRAMS AND REPORTS

FUNCTION MODULES

BAPIS

INTERFACES

System Landscape Directory

Central User Administration

Solution Manager Data Collection

Integration Server

RFCs

Web Services

JDBC Connectors

SNOTES AND REPAIRS

What's the Current Size of AWR in the Oracle Database?

2012-01-26T18:38:00.000Z

If you have to size a new Oracle 10g database, or you just want to increase the AWR snapshot interval, you may want to know how much database space is being used by the current AWR setup.

This is where V$SYSAUX_OCCUPANTS comes in.


SQL> set linesize 300
SQL> col OCCUPANT_NAME format A7
SQL> SELECT OCCUPANT_NAME,
           OCCUPANT_DESC,
           SPACE_USAGE_KBYTES
           FROM v$sysaux_occupants where OCCUPANT_NAME ='SM/AWR';

OCCUPAN OCCUPANT_DESC                                         SPACE_USAGE_KBYTES
------- ----------------------------------------------------- ------------------
SM/AWR  Server Manageability - Automatic Workload Repository  107008

The above was taken from a system with a default AWR setup (retention of 7 days, snapping every hour).
This is approximately 14MB per day.

If you were thinking you were going to keep 60 days, you would expect about 900MB of data in SYSAUX.

Checking Segment High Water Mark (HWM) in Oracle 10g

2012-01-17T13:00:00.000Z

The High Water Mark is equal to “Total Blocks” - “Unused Blocks”.

Substitute “<<<A USER>>>” and “<<<A TABLE>>>” with the real values.
Thanks to this post here.

set serveroutput on size 100000;
DECLARE
alc_bks NUMBER;
alc_bts NUMBER;
unsd_bks NUMBER;
unsd_bts NUMBER;
luefi NUMBER;
luebi NUMBER;
lub NUMBER;
BEGIN
FOR c1_row IN (SELECT table_name
FROM dba_tables where table_name = '<<<A TABLE>>>') LOOP
DBMS_SPACE.UNUSED_SPACE (
segment_owner => '<<<A USER>>>' ,
segment_name => c1_row.table_name ,
segment_type => 'TABLE' ,
total_blocks => alc_bks ,
total_bytes => alc_bts ,
unused_blocks => unsd_bks ,
unused_bytes => unsd_bts ,
last_used_extent_file_id => luefi ,
last_used_extent_block_id => luebi ,
last_used_block => lub);
DBMS_OUTPUT.PUT_LINE( 'Data for '|| c1_row.table_name);
DBMS_OUTPUT.PUT_LINE( RPAD('*',LENGTH(c1_row.table_name) + 10,'*'));
DBMS_OUTPUT.PUT_LINE( 'Total Blocks.................'|| alc_bks );
DBMS_OUTPUT.PUT_LINE( 'Total Bytes..................'|| alc_bts );
DBMS_OUTPUT.PUT_LINE( 'Unused Blocks................'|| unsd_bks );
DBMS_OUTPUT.PUT_LINE( 'Unused Bytes.................'|| unsd_bts );
DBMS_OUTPUT.PUT_LINE( 'Last Used Ext File Id........'|| luefi );
DBMS_OUTPUT.PUT_LINE( 'Last Used Ext Block Id.......'|| luebi );
DBMS_OUTPUT.PUT_LINE( 'Last Used Block..............'|| lub );
DBMS_OUTPUT.NEW_LINE;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/

Populate User Groups into SU01 Authorization Group

2012-01-13T13:00:00.000Z

If you have followed my previous post on how to create SAP user groups en-mass, then you may wish to know how you can assign one of these groups as the security group (as per the security group box in SU01).

Simply setup your groups as per my previous post, then use the following SQL at the database level to perform the assignment to the user account in SU01.

NOTE: The script will apply each group it finds to the user accounts. If more than one group is assigned to a user, then only one of those groups will be used. The order will depend on the order of records returned in the inline cursor.


set serveroutput on size 1000;

DECLARE
   CURSOR c_ug IS SELECT BNAME,USERGROUP,MANDT FROM USGRP_USER;
BEGIN

FOR ug IN c_ug LOOP

UPDATE USR02 SET CLASS=ug.USERGROUP WHERE BNAME=ug.BNAME and MANDT=ug.MANDT;

DBMS_OUTPUT.PUT_LINE ('User: '||ug.BNAME || ' Group: '||ug.USERGROUP);

END LOOP;

END;

"Fix" Control and ORA-00940: invalid ALTER command

2012-01-09T09:18:00.000Z

Upon startup of an Oracle 10.2.0.4.0 database I received Oracle error:
ORA-00940: invalid ALTER command

I eventually managed to narrow this down to a “_fix_control” setting.

The database is for an SAP system and the init.ora was updated after following SAP note 830576 (Parameter recommendations for Oracle 10g).
I noticed that I had misread the note and applied a “_fix_control” parameter setting for a slightly (only by a patchset update) higher version of Oracle.

Take care when reading this note and make sure you test the startup and shutdown of the database before going home!

UPDATE: This is a bug. See here.

SAP PI Channel Stopped by Administrative Task

2012-01-05T19:10:00.000Z

During a routine message mapping using the Java stack with a custom Java class lookup that accessed an RFC communication channel (configured in the Integration Directory), the mapping was failing with multiple errors.

One of the errors stated: “com.sap.aii.af.service.administration.api.cpa.CPAChannelStoppedException: Channel stopped by administrative task”.

Whilst not completely obvious what the exact problem was, I check the CPA cache in transaction SXI_CACHE and ensured there were no errors during a full cache refresh.
No problems there.

So I moved on to check the communication channel itself. All seemed fine. Using the “check” option from the pull down menu, showed all was well.
No other errors.

Restarting the message from SXMB_MONI resulted in the same error over and over.

The resolution:
Simply editing the RFC communication channel in the Integration Directory and changing it to “Inactive”, saving and applying, then changing it back to “Active” (on the Advanced tab), saving and activating.
This seemed to fix the problem.

Looks like the RFC channel destination hostname was modified and this caused the adapter to throw a slight wobbler, making it think it was inactive.

SAP note 830576 - PGA_AGGREGATE_TARGET on Oracle 10gR2

2011-12-20T17:17:00.000Z

SAP note 830576 "Parameter Recommendations for Oracle 10g" is quite a popular one for me.
It lists all the SAP recommended Oracle 10g parameter settings for Oracle 10.2.0.4 and 10.2.0.5.
It's a good point of reference and I'd recommend you implement it as a baseline before tuning the system further.
It has a buddy note, 1289199 "Information About Oracle Parameters" which describes some of the parameters in more detail.

Unfortunately, there is a major flaw on note 830576. When setting PGA_AGGREGATE_TARGET the SAP note says 20% of available memory. It fails to mention that this should be 20% of the SGA size, not O/S memory.
The Oracle docs (see MYOS note 153367.1) say that the value should be:

Syntax PGA_AGGREGATE_TARGET = integer [K | M | G]
Default value 10 MB or 20% of the size of the SGA, whichever is greater
Modifiable ALTER SYSTEM
Range of values Minimum: 10 MB
Maximum: 4096 GB - 1

The Oracle note goes on to say that when sizing the Oracle database memory areas, you should consider the SGA size first, then assign any spare memory to PGA.
Now in an SAP landscape with a single Central Instance + Dialog Instance on the same server as the database, you may wish to use the SAP 70/30 rule (70% to SAP, 30% to Oracle).

My order of sizing would look something like this:
1, Determine number of users of SAP system.
2, Determine number of DIALOG work processes + Background work processes + Update processes (~= Oracle "processes").
3, Determine leftover memory for Oracle SGA (split between pools, SAP doesn't support automatic memory management).
4, Determine leftover memory for PGA + overheads.

If you get to step 4 and you have diddly squat RAM left (hardly any), then consider adding more RAM to your server. Remember, we don't like pageing.

SAP note 789011 "FAQ: Oracle Memory Areas", provides a range of SQL statements for checking the actual size of the PGA. Since PGA_AGGREGATE_TARGET is only telling Oracle what you would like the maximum PGA allocation to be.

When you set PGA_AGGREGATE_TARGET, you also allow Oracle to release PGA memory back to the O/S. Using the *_AREA_SIZE parameters and setting PGA_AGGREGATE_TARGET to 0, forces a specific size of PGA which does not release the memory to the O/S.


/* Actual PGA consumption */
SELECT VALUE FROM V$PGASTAT WHERE NAME = 'total PGA allocated';

/* Chronological PGA allocation (needs AWR license) */
SELECT SUBSTR(S.END_INTERVAL_TIME, 1, 40) TIME,
               P.VALUE PGA_ALLOCATION
  FROM DBA_HIST_SNAPSHOT S, DBA_HIST_PGASTAT P
WHERE P.NAME = 'total PGA allocated'
    AND S.SNAP_ID = P.SNAP_ID
ORDER BY P.SNAP_ID;

Oracle states:
Memory Area Dedicated Server Shared Server
Nature of session memory Private Shared
Location of the persistent area PGA SGA
Location of part of the runtime area for SELECT statements PGA PGA
Location of the runtime area for DML/DDL statements PGA PGA

When installing Oracle for SAP, by default it uses DEDICATED server mode (see note 70197).

SAP Sender Address for Communication Method

2011-12-15T11:28:00.000Z

Whilst configuring SAP to send email oubound via SMTP you have to configure the node in SCOT to point to an SMTP server.
Once this is done, you would expect it to just work.
Unfortunatly, you may get the following issue.

When you create a new email message in SO01, you enter the recpient and the message text, then click send, and you are prompted with an error:

The error in the log book says:

"You do not have a sender address in the chosen communication method."

This was an odd error, but a quick search on SAP notes revealed note 552616.

https://service.sap.com/sap/support/notes/552616

The note mentioned either setting my user's external email address in SU3 (or SU01), or alternatively, instead of doing this for all users that wish to send external mail, you can set the "default domain" in SCOT.

Set the default domain to something like "mydomain.com".
This means SAP will create an outbound sender address comprised of the SAP username plus the default domain (sapuser@mydomain.com).

I was then able to send mail externally.

Oracle on NetApp via NFS (yes, really, NFS!)

2011-12-13T20:06:00.000Z

I've blogged about SAP on HP-UX before, which includes a load of notes and whitepapers about Oracle on HP-UX.
This blog post is about Oracle storage on NetApp.

Here's a NetApp whitepaper specifically for Oracle on HP-UX, when using NFS mounted partitions from a NetApp device.
You should pay particular attention to ensuring that you have the correct OS patches applied, plus the kernel settings related to NFS should be set. You should also note the section on direct I/O.

This is the HP whitepaper for NFS tuneables but it's been moved into the new HP site. It looks like it's possible to get a slightly older version for HP-UX 11iv2 from ManualShark.org here.

I'm currently seeing average Oracle sequential read times of about 5-8ms running over a single gigabit ethernet card.

If at all possible ensure that you test the new architecture before hand (TEST should be representative of PRD!).
Make sure you identify and reduce Oracle full tablescans if possible. Basically reduce I/O as much as possible.
Be prepared to bump up the buffer cache a little if you have the RAM (or the slots for the RAM).

Ensure that your Oracle partitions are seperate partitions and not shared with any other apps, so that you can change the mount point options specifically for Oracle.
Set the Oracle partitions file system block sizes according to Oracle/HP-UX best practice (again, see my other SAP on HP-UX post for more on this).

Most importantly, have your DB tuning team on standby, get those AWR snapshots running more frequently and make use of the ASH reports specifically for tuning SQL statements.

Oracle X$ Tables

2011-12-06T19:49:00.000Z

I recently found this site that lists all known Oracle X$ tables and describes them:
http://yong321.freeshell.org/computer/x$table.html

Transporting SAP User Groups

2011-12-05T19:00:00.000Z

I've blogged about SAP user groups before: http://darrylgriffiths.blogspot.com/2011/09/sap-user-groups.html
Let's say you've set them up in your DEV system and now you're expecing to just transport them. Well, as with all things SAP, it's never quite that easy.

Create a new workbench transport request in SE01, SE09 or SE10 (doesn’t matter which).
Then open the request at the request level by double clicking it.
Switch to change mode by clicking the pencil button:

Now manually type in the program Id, object type and object names as follows:

R3TR TABU USGRP
R3TR TABU USGRPT

Click Yes at the prompt:

On each item, click the Function button (key symbol):

Enter * in the Table Keys field:

Save the request again.

That's it!

Release the transport and import into the next system.
This is a farily generic process that can b used to transport any table values.
NOTE: You should be aware that the "*" in the table key, means all items specific to the current client.

Android 2.1 Calendar Sync Force Close

2011-12-03T17:36:00.001Z

I have an Android 2.1 phone (an Acer Liquid A1) which recently started throwing an error during the calendar sync. It kept showing a "force close" error.

I got my laptop setup to connect to the phone using the Acer Liquid Tool (USB drivers) and the Malez recovery software, which includes the ADB debugging software.
After connecting the phone via USB and running ADB logcat, I was able to get the following error from the phone when I sync'd the calendar:

W/dalvikvm(  823): threadid=15: thread exiting with uncaught exception (group=0x2aac6160)
E/AndroidRuntime(  823): Uncaught handler: thread SyncThread exiting due to uncaught exception
E/AndroidRuntime(  823): java.lang.NullPointerException
E/AndroidRuntime(  823):        at com.android.providers.calendar.CalendarSyncAdapter.fetchCalendarsFromServer(CalendarSyncAdapter.java:1622)
E/AndroidRuntime(  823):        at com.android.providers.calendar.CalendarSyncAdapter.getServerDiffs(CalendarSyncAdapter.java:1223)
E/AndroidRuntime(  823):        at android.content.TempProviderSyncAdapter$SyncThread.runSyncLoop(TempProviderSyncAdapter.java:367)
E/AndroidRuntime(  823):        at android.content.TempProviderSyncAdapter$SyncThread.sync(TempProviderSyncAdapter.java:287)
E/AndroidRuntime(  823):        at android.content.TempProviderSyncAdapter$SyncThread.run(TempProviderSyncAdapter.java:218)

After an awful lot of Googling, I still had no answer.
It looks like the 2.1 version of Android supplied by Acer, has a flaw that prevents it working properly with some new Google calendar feature.
I don't know what the problem is.
Instead, to solve the problem, I flashed the ROM back to stock Acer Android 1.6.
The calendar then worked again properly, however, I had lost an awful lot of good functionality (Facebook integration, better Gmail & Android Market app versions etc etc).

So I tried another option. I used a link on the Modaco forums for a hacked version of the Android 2.2 OS which was for the new Acer LiquidE (same phone hardware, but with 512MB of RAM instead of 256MB).
Someone has posted a version which had been enabled to run on my 256MB Acer Liquid (genius).

I flashed the 2.2 ROM onto the phone and the calendar now works again.

Despite the problem, this highlighted an area of short sightedness. When/if the online Google apps are no longer supported or work on older Android versions, this will make the phones that use the older Android versions effectively worthless. Who wants an Android phone with no cloud capability?

What's in a SAP EHP Anyway...

2011-12-03T14:22:00.001Z

For some time I've often wondered how you can determine what functionality exists in an Enhancement Package (EHP).
I've supported SAP ERP 6.00 systems for a while, but I've never been involved in the business functionality decisions (I'm a technical guy and this is a Solution Architect's job).
So it made me wonder, if you were implementing a brand new SAP ERP 6.00 system, how would you determine what level of EHP was right for you.
Well the answer is simple, once I'd done a little reading.

All SAP EHPs are cumulative. i.e. SAP ERP 6.00 EHP 5 includes all the new features included in EHPs 1 to 4, plus some shiny new ones.
So you see, you just need to implement the latest and greatest to get "everything", then switch it on if you want it. You can use the new SAP BFP (Business Function Prediction) to see what's in each EHP.

So, why do SAP continue to dish out the older EHPs? or even the base ERP 6.0 release?
Well that is down to software release management and the way that SAP develop the code.
Each EHP is effectively a branch off the main code-set. Each of these branches will need patches, so SAP can't just kill off EHP 4 when EHP 5 is out. They must maintain the support.
For this reason, you will see why the Support Package Stack (SP-Stack) schedule always has the latest sp-stack for the base release out before the first EHP, before the second and so on.

Like this:
ERP 6.0
\-> ERP 6.0 EHP 1
\-> ERP 6.0 EHP 2

\-> ERP 6.0 EHP 3 ....

The release schedule simply follows the way the support packages are tested and released up through the code-set branches.

The time difference between the base release sp-stack and an EHP sp-stack could be a good indication of the amount of change between the base release and an EHP.

So is there a technical reason why you wouldn't implement the latest and greatest EHP (as recommended by SAP)?

My answer is YES. I don't buy a car with everything included, because I know that I can get a SATNAV cheaper elsewhere. Now I guess this depends on your software strategy.
Plus, the more tech you have, the more that could go wrong during application of support patches and upgrades. Lastly, even if you're not using the EHP functionality, you still have to apply the code during the patch process, increasing upgrade time.

I maintain, if you don't need it or want it, why apply it.
This is obviously my view, and the counter argument would be that you have the option of "just in time" enabling of new business functionality. In my experience, even the decisions take longer than the actual implementation time, even without "just in time".

Logging in PL/SQL

2011-11-29T10:00:00.000Z

Whilst tracking a PL/SQL problem with some code utilising excessive TEMP tablespace, I came upon this article for logging real-time in PL/SQL using DBMS_PIPES.

This looks perfect for real time interface monitoring:

http://dioncho.wordpress.com/2009/11/10/logging-in-plsql/

Check if Hyperthreading is enabled in HP-UX 11iv3

2011-11-26T08:40:00.000Z

There is a nice couple of documents from HP on the benefits of Hyperthreading with HP-UX, providing your CPU supports it.
http://h20338.www2.hp.com/hpux11i/downloads/MontecitohyperThre.pdf
This article tells you how to check: http://www.filibeto.org/unix/hp-ux/lib/kernel/hp-ux-11.31-logical-processors-HT-4AA0-7695ENW.pdf

You should note that HP-UX 11iv3 enables the default PSET setting for HT by default, but you may still need to enable the firmware setting.
You should also note that HP-UX 11iv2 does not support HT.

All the checks below should return 1 (for enabled).

Check HT is supported in your chipset:
$ getconf _SC_HT_CAPABLE

Check the firmware setting is enabled:
$ getconf _SC_HT_ENABLED

Check the default PSET has HT enabled:
$ kctune -v -q lcpu_attr

Enjoy some HT goodness.

Oracle Tracing & Interpreting Traces - Notes

2011-11-20T17:59:00.000Z

Oracle Trace Details; abstracted from MYOS Note ID 376442.1 and added flair.

This section details various ways of actually getting an Oracle session level trace file, interpreting the trace file and useful links to MYOS notes about the trace data:

/* Oracle 10g+ trace session using DBMS_MONITOR includes BINDS and WAITS */
/* However, this is limited to one session only */
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id =>1181,
serial_num =>7218,
waits => TRUE,
binds => TRUE);


EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 1181,

serial_num => 7218);

/* Oracle 9i Trace excludes BINDS and WAITS */
/* Again, limited to one session only */

EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(16,38779,TRUE);
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(16,38779,FALSE);

/ * Trace your own session with BINDS and WAITS */
ALTER SESSION SET SQL_TRACE=TRUE;
or
ALTER SESSION SET EVENTS ''10046 trace name context forever, level 12'';

/* Trace using OraDebug with BINDS and WAITS */

connect / as sysdba
oradebug setorapid 9834  << Insert your Oracle PID here.
oradebug unlimit
oradebug event 10046 trace name context forever,level 12

/* And switch it off again */


oradebug event 10046 trace name context off

/* Logon trigger trace for Oracle 9i+ */
/* This is the best way to trace when using Oracle Forms because it allows for multiple spontaneous sessions.
I can also confirm that this traces sessions executed on a database link */
/* First you must grant ALTER SESSION to the user */
GRANT ALTER SESSION TO <USERNAME> ;

/ * Now create the trigger */


CREATE OR REPLACE TRIGGER SYS.set_trace
AFTER LOGON ON DATABASE
WHEN (USER like '&USERNAME')
DECLARE
lcommand varchar(200);
BEGIN
EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';
EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';
EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context forever, level 12''';
END set_trace;
/

/* Simply disable the trigger when done */
ALTER TRIGGER SYS.SET_TRACE DISABLE;

/* List non-system users, very useful when attempting to trace a session */

SELECT username,sid,serial#,status,module
   FROM v$session 
 WHERE username is not null
      AND username <> 'SYS'
 ORDER BY username;

/* Enable the trace on ALL current sessions of a specific user in the system. */


set serveroutput on size 10000;
DECLARE
BEGIN
FOR c1_row IN (SELECT sid,serial#
FROM v$session where username = '<<A USER>>') LOOP
DBMS_OUTPUT.PUT_LINE('Enabling trace for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => c1_row.sid, serial_num => c1_row.serial# ,waits => TRUE, binds => TRUE);
DBMS_OUTPUT.PUT_LINE('Tracing for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/
select username,sql_trace from v$session order by username;

/* Disable the trace on ALL current sessions of a specific user in the system. */


set serveroutput on size 10000;
DECLARE
BEGIN
FOR c1_row IN (SELECT sid,serial#
FROM v$session where username = '<<A USER>>') LOOP
DBMS_OUTPUT.PUT_LINE('Disabling trace for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => c1_row.sid, serial_num => c1_row.serial#);
DBMS_OUTPUT.PUT_LINE('Tracing disabled for session: '||c1_row.sid||' serial#: '||c1_row.serial#);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM,1,250));
END;
/
select username,sql_trace from v$session order by username;

/* Run TKProf as sysdba with EXPLAIN plan included */
/* See TKProf Interpretation (9i and above) [ID 760786.1] */
tkprof <tracefile>.trc /tmp/<tracefile>.trc.tk explain=\" / as sysdba\"

/* TKProf sorting by highest elapsed time */
tkprof <tracefile>.trc /tmp/<tracefile>.trc.tk sort=fchela,exeela,prsela

Once you have your trace file and you have run it through TKProf and are still struggling, you may need to read it raw.
Check out the following MYOS articles:
"Interpreting Raw SQL_TRACE and DBMS_SUPPORT.START_TRACE output [ID 39817.1]".

The excellent but slightly unheard of "Oracle Performance Diagnostic Guide (OPDG) [ID 390374.1]".

SAP Language Pack Enabling for Login in R/3 Enterprise 4.7

2011-11-11T08:46:00.001Z

In an SAP system you can see what language packs are installed using transaction SMLT.
Sometimes however, you may find that you are unable to log into the system using one of the languages that are installed. You get prompted to select a valid language.
This is usually due to a miss-configuration of the zcsa/installed_languages profile parameter.

You can check the system language configuration using the report RSCPINST.
You should note that the zcsa/installed_languages parameter should be set in the DEFAULT profile *only*.

Some useful SAP notes on language packs:

73606 “Supported Languages and Code Pages”
352941 “Languages and Support Packages”
533888 “Example for Language Import and Errors”

IBM MQ Error - AMQ6118: An internal WebSphere MQ error has occurred (20806013)

2011-11-05T07:55:00.000Z

In the /var/mqm/errors/AMQERR*.LOG file the following error was visible:

11/01/11 16:20:23 - Process(1408.3) User(mqm) Program(amqrmppa_nd)

AMQ6118: An internal WebSphere MQ error has occurred (20806013)

EXPLANATION:

An error has been detected, and the MQ error recording routine has been called.

ACTION:

Use the standard facilities supplied with your system to record the problem

identifier, and to save the generated output files. Contact your IBM support

center. Do not discard these files until the problem has been resolved.

There was an FDC file present also in the same directory.
It contained the following information header:
+-----------------------------------------------------------------------------+

| |

| WebSphere MQ First Failure Symptom Report |

| ========================================= |

| |

| Date/Time :- Tuesday November 01 14:52:07 GMT 2011 |

| Host Name :- xxxxxxxx (HP-UX B.11.23) |

| PIDS :- 5724H7208 |

| LVLS :- 6.0.1.0 |

| Product Long Name :- WebSphere MQ for HP-UX (Itanium platform) |

| Vendor :- IBM |

| Probe Id :- XC028018 |

| Application Name :- MQM |

| Component :- xcsReleaseMutexSem |

| SCCS Info :- lib/cs/unix/generic/amqxlfmx.c, 1.147.1.1 |

| Line Number :- 3229 |

| Build Date :- Oct 21 2005 |

| CMVC level :- p600-100-051021 |

| Build Type :- IKAP - (Production) |

| UserID :- 00002440 (mqm) |

| Program Name :- amqrmppa_nd |

| Addressing mode :- 64-bit |

| Process :- 25271 |

| Thread :- 3 |

| QueueManager :- MQINTUX |

| ConnId(1) IPCC :- 26 |

| Major Errorcode :- xecL_E_NOT_OWNER |

| Minor Errorcode :- OK |

| Probe Type :- INCORROUT |

| Probe Severity :- 2 |

| Probe Description :- AMQ6125: An internal WebSphere MQ error has occurred. |

| FDCSequenceNumber :- 0 |

| |

+-----------------------------------------------------------------------------+

Check the “Component” and “Major Errorcode” sections of the header.
I was seeing “xecL_E_NOT_OWNER” for the “xcsReleaseMutexSem” component.

This prompted me to check the semaphores (HP-UX) using command "ipcs -sa".
I could see that there were some semaphores hanging around as the “mqm” user (shown in the header of the FDC file also).
So, clearing them as root and using “ipcrm -s” and the ID from the “ipcs -sa” command output, fixed the issue and prevented the need for a reboot.

The issue was caused by running MQ as the wrong UNIX user (it wasn't supposed to be running as the mqm user in our environment).

Linking Micro Focus Cobol (Server) 5.0 to Oracle

2011-11-05T07:52:00.001Z

After implementing an Oracle upgrade, you will need to re-link the Micro Focus Cobol runtime environment.

However, after the relink you may need to ensure that the link to the new shared library exists:

After seeing this dump in /var/mfcobol/es/<SERVER>/casdumpa.rec

“Load error : file 'librtsora.sl'

error code: 173, pc=0, call=1, seg=0

173 Called program file not found in drive/directory“


cd $ORACLE_HOME/lib32 
ln -s rtsora_t.sl librtsora.sl

If you're using Itanium, you will be using rtsora_t.so.

Oracle Index Rows Per Leaf Block

2011-11-01T18:07:00.003Z

The query below can be used to obtain the number of rows per index leaf block.
You will need to know the index name, the table name that the index references, the pk column in the index and the object id for the index:


SELECT object_id
from user_objects
WHERE object_name = 'MGMT_VERSIONS_PK';

SELECT rows_per_block, count(*) blocks
FROM (
      SELECT /*+ cursor_sharing_exact
                 dynamic_sampling(0)
                 no_monitoring
                 no_expand
                 index_ffs(serv_inst,ix_serv_inst)
                 noparallel_index(serv_inst,ix_serv_inst)
              */
             sys_op_lbid(349440,              -- << INDEX OBJECT ID HERE
                                 'L',rowid) block_id,
             count(*) rows_per_block
        FROM MGMT_VERSIONS                    -- << TABLE NAME HERE
       WHERE COMPONENT_NAME IS NOT NULL       -- << INDEX COL NAME HERE
      GROUP BY sys_op_lbid(349440,            -- << INDEX OBJECT ID HERE
                           'L',rowid)

     )
GROUP BY rows_per_block;

This is useful for determining sparse index blocks:
http://www.dba-oracle.com/t_sys_op_lbid_index_node_density.htm

It could be combined with this script (http://jonathanlewis.wordpress.com/segment-scans/) which helps to detect full table scans and index fast full scans:


select
       owner,

object_type,
object_name,

       obj#,
       subobject_name,

       tablespace_name,
       value scans
from
       V$segment_statistics
where
       statistic_name = 'segment scans'
  and  value != 0
order by owner, value;

(When I get time, I'll combine it and post it here).

Running the first SQL statement, then checking if the table has "high" segment scans would then give a good indication if the index is used frequently for large multi-block operations and is very sparse (block wise) and potentially up for a re-build.

DBUA "You do not have OS authentication" - Fat Fingers

2011-10-29T17:15:00.002+01:00

The other day, I was upgrading an Oracle 10.2.0.2 database to 10.2.0.5 using DBUA (it has it's benefits and provides a nice consistent approach when under pressure, providing it's used with care).

I constantly got the request to enter the password for a DB user with SYSDBA privileges.
I didn’t have the SYS or SYSTEM user passwords (really!), but the OS user I was using definitely was a member of the group that is compiled into the config.o library (see MYOS note “SYSDBA and SYSOPER Privileges in Oracle [ID 50507.1]”):

For reference, you check on UNIX by:


cd $ORACLE_HOME/rdbms/lib
cat config.[c|s]

Checking the values for SYS_DBA and SYS_OPER against the UNIX groups your user is a member of (use the "groups" command).

The solution: this was simple, the Oracle home had been manually added into the /etc/oratab file and had been misspelled. If only it didn't take 45 minutes to find it!
Correcting /etc/oratab and restarting DBUA fixed the problem.

I guess DBUA was just plain lying and it didn't have the heart to tell me that I was about to upgrade a database when it couldn't find the Oracle home. Shame on you.

Gathering Oracle Statistics - the SAP way

2011-10-23T17:04:00.000+01:00

Gathering stats on a table in an Oracle database, can significantly change the SQL excution plan.
In some cases this can be detrimental to the system performance.
I have seen this in a real production system, where the indexed column was 38 characters long. The system had just been upgraded from 9i, the original table stats carried over and left up to Oracle.
Performance was dreadful, the 9i stats had been refreshed but not re-created.

See SAP note 365480 ("CBO: Field filled with leading "0" aligned to left").
It suffered from an Oracle "bug" (773462) where only the first 32 characters are used to measure the distinct number of values for the column. In a 38 character field, left padded with zeros, using the index can cause a problem.
If we had removed all stats and re-created them, no problem, as Oracle 10g would have probably generated histograms.
However, the real issue was that the stats shouldn't have been there according to DBSTATC.

NOTE: SAP notes say to specifically disable the default Oracle 10g/11g GATHER_STATS_JOB.

As noted, if you let Oracle gather statistics it overrides the settings in the SAP DBSTATC table.
The DBSTATC table (see transaction DB20) controls where stats are gathered and how stats are gathered.
In actual fact, this is why you sometimes see "Harmful statistics" in the BR*Connect DBcheck results.

The stats are gathered using the Oracle DBMS_STATS package with Netweaver 7.0's version of BR*Tools (more specifically, BR*Connect).

Below are some SQL and PL/SQL commands useful for analysing statistics problems in a SAP Oracle 10g environment.


/* Check if gather stats auto-job is enabled */
SELECT JOB_NAME,
OWNER,
ENABLED,
PROGRAM_NAME,
TO_CHAR(last_start_date,'DD-MM-YYYY HH24:MI:SS') last_start_date
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME='GATHER_STATS_JOB';

/* Get GATHER_STATS_JOB program details */
SELECT PROGRAM_TYPE,
PROGRAM_NAME,
PROGRAM_ACTION
FROM dba_scheduler_programs
WHERE PROGRAM_NAME='GATHER_STATS_PROG';

/* Get the last time the GATHER_STATS_JOB ran and log info */
SELECT log_id,
actual_start_date,
run_duration,
job_name,
status,
additional_info,
log_date
FROM dba_scheduler_job_run_details
WHERE owner='SYS'
AND job_name='GATHER_STATS_JOB';

/* VERIFY STATS ON A COLUMN */
SELECT num_distinct,
density,
num_buckets,
sample_size,
avg_col_len,
histogram
FROM dba_tab_col_statistics
WHERE table_name = &TABNAME
AND column_name=&COLNAME;

/* VERIFY STATS ON A TABLE */
SELECT sample_size,
TO_CHAR(last_analyzed,'DD-MM-YYYY HH24:MI:SS') last_analzed,
global_stats,
user_stats,
stattype_locked,
stale_stats
FROM dba_tab_statistics
WHERE table_name = &TABNAME;

/* VERIFY HISTOGRAMS ON A TABLE */
SELECT *
FROM dba_tab_histograms
WHERE table_name=&TABNAME;

/* GATHER COLUMN STATS FOR A COLUMN ONLY */
BEGIN
       DBMS_STATS.GATHER_TABLE_STATS (
                                OWNNAME => 'SAPR3',
                                TABNAME => '< A TABLE>',
                                METHOD_OPT => 'FOR COLUMNS < A COLUMN> SIZE AUTO');
END;

/* GATHER TABLE STATS USING ESTIMATE 10% */
BEGIN
        DBMS_STATS.GATHER_TABLE_STATS (
                                 OWNNAME => 'SAPR3',
                                 TABNAME => '< A TABLE>',
                                 METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO',
                                 ESTIMATE_PERCENT => 10);
END;

/* ORACLE'S DEFAULT JOB TO GATHER STATS */
dbms_stats.gather_database_stats_job_proc;

/* DELETE ALL THE TABLE STATS CASCADE TO INDEXES and COLUMNS */
BEGIN
        DBMS_STATS.DELETE_TABLE_STATS (
                                   OWNNAME => 'SAPR3',
                                   TABNAME => '< A TABLE>',
                                   cascade_parts => TRUE,
                                   cascade_columns => TRUE,
                                   cascade_indexes => TRUE);
END;

/* DELETE COLUMN STATS ONLY */
BEGIN
         DBMS_STATS.DELETE_COLUMN_STATS (
                                   OWNNAME => 'SAPR3',
                                   TABNAME => '< A TABLE>',
                                   COLNAME => '< A COLUMN>',
                                   FORCE => TRUE);
END;

Oracle Core: Essential Internals for Troubleshooting

2011-10-22T12:26:00.000+01:00

Whilst shopping on Amazon this morning I noticed that Jonathan's book is up for pre-order.
The price gurantee from Amazon is very reasonable, so I've pre-ordered.

Should be a good read and more importantly a good weapon in any DBAs problem analysis methodology (is "weapon" the best way to describe it? Wouldn't that make Jonathan an arms dealer?).
Of course it doesn't mean I can possibly absorb everything from the book, but most of the time I find that it's not that you know how to do it, it's that you know it can be done.

http://www.amazon.co.uk/gp/product/1430239549

Connecting SAP Netweaver ABAP Stack To SAP SLD

2011-10-20T20:32:00.000+01:00

If you've ever quickly needed to know the points to check for customising settings connecting your SAP Netweaver *ABAP* system to an SLD (System Landscape Directory), then here they are:

Transaction: SLDAPICUST
Transaction: SM59 - TCP/IP connections: SAPSLDAPI (Use by ABAP API)
LCRSAPRFC (Use to read the exchange profile)

Here's the SAP Help article: http://help.sap.com/saphelp_nw04s/helpdata/en/be/6e0f41218ff023e10000000a155106/content.htm

To Delete or Not To Delete (an SAP user account)

2011-10-15T18:35:00.001+01:00

Having read around the SDN forum recently, I was surprised that no one had done any particular research into the consequences of deleting SAP user accounts.
This is probably quite a common question when you first get your shiny new SAP system implemented.
Generally, auditors prefer that you delete IT accounts. It's a nice catch-all and means that they can tick the box that says "done", knowing that what happened was the most secure option. But it might not be the best option.
Most procedures require locking the account for 1-2 months before eventually deleting it, therefore catching rogue background jobs etc.

After some heavy procrastination I have come up with the following reasons why it might always be better (safer) and actually more audit-friendly to just lock the account and not actually delete it:

- Adding a new user of the same user id as the one just deleted, will attempt to re-use the old address details.
This is really bad and could cause awful confusion. It could also cause a problem with regards to auditing.

- Customer modifications/code in programs and workflows that utilise the user id.
If the user is deleted, then re-created later on for a new employee of the same name, that new user may inherit authority, receive SAP Office emails or any other actions that were previously meant for the old owner of the user id.

- When a user creates a transport, this is recorded in the code version history for the objects transported and the transport co-file and a file in one of the the /usr/sap/trans sub-directories (off-hand I think it's called sapnames...).
Deleting the user removes the tie between the user id that created the code version, and their real name/details. In a large organisation it can be difficult to find the right "Smith" that made that change.

- When a user is deleted, you can not see what authorisations the account used to have.
Once again, how can you prove that some malicious action happened if the account has been deleted, removing the evidence that the user had access to perform the crime.
Alternatively, someone may have legitamately left the company, but recruiting didn't find a replacement for 6months. Guess who will need to create a new user id where the request form says "same roles as Joe who left 6 months ago".

- If you delete a user account after they leave, then re-create a new one for a new employee, what if you've missed an account on a system somewhere. Nobody will know if it should be for the user who currently exists. This is a major security risk.

This may lead you to thinking about a better user id naming scheme that could provide a unique name for every account created.
That really would be a worthwhile exercise.

Don't forget, locking the account does not mean that you need to pay a license cost for it, it's not usable.

HP OmniBack / DataProtector Version Check

2011-10-15T18:32:00.001+01:00

How to tell the version of HP OmniBack / DataProtector client installed on your HP-UX server:


$ /opt/omni/bin/omnicc -ver
HP Data Protector A.06.11: OMNICC, internal build PHSS_41954/PHSS_41955/DPSOL_00442/DPLNX_00148, built on Thu Mar 24 07:14:15 2011

It's possible to tell if a backup is running by using PS:


$ ps -ef | grep omni

Should show any agents running.

SAP CPI-C Error Service Unknown and /etc/services

2011-10-07T20:55:00.000+01:00

I've found that generally the Netweaver 7.0 installations seem to create all required mappings in the /etc/services (or C:\Windows\System32\Drivers\etc\services) file, plus any not specifically required.

However, on older R/3 systems, it's still possible to fall foul of this problem when the service port mappings are not present in the /etc/services file.

When using RFC connections or testing a connection in SM59, you're seeing CPI-C (CPIC) errors with "Service sapgw00 Unknown" and CM_RESOURCE_FAILURE_NO_RETRY.

It's normally visible as a connectivity error when testing connections in SM59:

You should ensure that the connection test fails when you put the exact numerical port number into the SM59 "Gateway service" connection details, as well as failing when you have the service port name e.g. sapgw00:

Oracle Documentation Is a Moving Target

2011-10-07T19:08:00.000+01:00

I appreciate that internet content is better when it's dynamic, but sometimes there's a need for static content; or should I say more accurately, static links to content.

I'm right in the middle of compiling a new tutorial on installing Oracle 11gR2 on Oracle Enterprise Linux 6.1.
The Oracle docs in HTML format have been on-line for years now, but I've never really referenced them over a period of time with such specific requirements.
I've been using the software pre-requisites section of the Oracle 11gR2 docs and have bookmarked them in my browser of choice. At least I thought I had. Until today, when I click the link I get an Oracle web page stating "404 not found".
What happened? What caused the move of the actual URL?
Here's what I was using before:
http://download.oracle.com/docs/cd/E11882_01/install.112/e16763/pre_install.htm#CIHIDJAH

Here's the same content today:
http://download.oracle.com/docs/cd/E11882_01/install.112/e24321/pre_install.htm#CIHFICFD

Looks like the e16763 has been replaced with e24321 and the internal bookmark (anchor) in the page (CIHIDJAH) has been changed to (CIHFICFD).

All links internally on the page are relative links and not absolute, and when you try and navigate up the path, you get a 404. So I guess that Oracle themselves don't want you to bookmark the links.

Lesson learned, always capture what you need elsewhere and duplicate everything in case you can't find it or it gets removed...

How to Check If You're Using the 6.40 Extension Set 2.0 SAP Kernel

2011-10-05T19:13:00.000+01:00

With SAP R/3 Enterprise 4.7 came the 6.40 Kernel.
There are two releases of this kernel, 6.40 Extension Set 1.10 and 6.40 Extension Set 2.0.

When you need to patch the Kernel, you need to know which one to download.
You can tell by executing the "disp+work" binary on UNIX or Windows:


$ id
<sid>adm

$ disp+work

--------------------

disp+work information

--------------------

kernel release 640

kernel make variant 640_EX2 <<< EXT 2.0

DBMS client library



$ disp+work

--------------------

disp+work information

--------------------

kernel release 640

kernel make variant 640_REL <<< Not EXT 2.0

DBMS client library

Oracle Storage Sub-system Load Stress Testing

2011-10-03T20:13:00.000+01:00

Whilst looking around for an Oracle equivalent stress testing freebie (like SQLIO for SQL Server), I found out about ORION (ORacle I/O Numbers).
It's been about for a while and can be used to stress test storage systems.
It's a simple single binary file that generates I/O load on a storage system using Oracle's I/O call stack.
No need to install Oracle at all!

Unfortunately for me, it only works with file systems that have Async I/O enabled.
I did not, and therefore could not use ORION.
When I finally get time to install Oracle on my new Oracle Enterprise Linux 6.1 environment, I'll give it a go.

Flushing Cursor SQL Plan Out of Library Cache

2011-09-26T18:58:00.005+01:00

I can never remember how to do this.

I wanted to flush a specific SQL execution plan out of the Oracle 11g SQL Library Cache so that I could try and compare a before and after SQL execution scenario using SQL trace and then tkprof'ing it.
Here's the link to the blog that helped me remember again:

http://prutser.wordpress.com/2009/04/19/flushing-a-cursor-out-of-the-library-cache/

Thanks.

Enable SAP* in Netweaver 7.0

2011-09-23T18:18:00.000+01:00

If you've lost the Administrator password or you want to log in as SAP* in your pure Java stack environment, this can be a little tricky as the way to set the password for the SAP* user in configtool is not as user friendly as you might think.

Log into configtool by opening an X-Windows session (on UNIX), and then execute: /usr/sap/<SID>/JC<SYS#>/j2ee/configtool/configtool.sh (.bat on Windows).

NOTE: Once you enable the SAP* user, you will not be able to log into the system as any other users e.g. Administrator or J2EE_ADMIN.

In the configtool screen, expand the “Global server configuration -> services” branches:

Click the “com.sap.security.core.ume.service” item:

On the right hand side, scroll down to the ume.superadmin.activated option:

Set the “Value:” field to “TRUE”:

Now single click the “ume.superadmin.password” item:

You can’t see the password and the “Value:” field looks like it doesn’t accept input, but it does.
Type the new password in to the “Value:” field at the bottom (even though the cursor doesn’t move):

Now click “Set” on the right:

You will be prompted to re-enter the password:

Click Save:

You should restart the J2EE stack before trying to log in as SAP*.

SAP on HP-UX

2011-09-19T07:01:00.003+01:00

There are various methods of tuning an SAP system, but sometimes the operating system vendor will permit specific tuning so that the SAP system can take better advantage of the available resources.
Usually, you can find whitpapers on the O/S or hardware vendor's web site, but also SAP will themselves develop a range of SAP notes that will offer avice and guidance when running on certain approved hardware partners.
If you're using HP-UX 11iv3 (11.31) and you are running SAP, then here are some gems direct from SAP themselves:

Note:
172747 HP-UX OS Parameter Recommendations
798194 HP-UX async IO trace files
837670 HP-UX OS Patch Requirements
918279 HP-UX SAPOSCOL Versions
1077887 HP-UX filesystem options
1329878 Using non-default pagesize on HP-UX
1351051 Support of Oracle on HPVM
1457063 HP-UX Consolidating SAP Systems
1575609 Future HP-UX support for SAP on Oracle

I would highly recommend the OS Parameters note.
If you're suffering I/O performance issues, make sure that you have set the correct block size as per 1077887.
A huge (>10%) reduction in memory usage can be obtained following 1329878, but only if you are using Oracle 11g.

Using the direct I/O option alone on a VxFS 5.0 environment decreased Data Protector backup times by over 1 quarter e.g. a 1.5 hour backup reduced to ~1 hour.

I haven't investigated the HPUX_SCHED_NOAGE O/S parameter option yet (I need the audacity to recommend it to the server team!), but according to this excellent blog post by Christian Bilien, it should help your environment significantly if you're running more than one Oracle database on a SMP system.
My reasoning is that there will be more than one CPU hungry Oracle thread and they could each be battling against each other (ageing each other out) even more so in a well tuned system with less I/O (large SGA/PGA and well tuned SQL) meaning less voluntary context switches and more forced ones. It’s possible that in this situation, without HPUX_SCHED_NOAGE, you could start to see CPU bottlenecks.

HP also produce their own performance recommendations for running Oracle.
Here's one on the HP recommended filesystem IO options.
Notice that it says if you're using VxFS v5.0, you don't need to worry about 1KB block sizes on Redo and Archive Log partitions.

WARNING: Take heed in the warning about Progress databases on file system partitions with directio enabled on the mount options. I have personally experienced issues where Progress applications have had a 20x decrease in performance! It's a fact, Progress is not very good without the file system cache.

This one is specific to tuning HP-UX TCP/IP. Very useful if you're seeing networking bandwidth problems in you environment.

Finally, the HP-UX Performance Cookbook provides an excellent source of information.
I have noticed that some of the SAP Oracle recommended parameters (830576 "Parameter Recommendations for Oracle 10g") are specific to HP-UX, I have found that a good set of descriptions for some of these are covered in this HP Oracle paper: The Oracle Database on HP Integrity servers.

It's really a trade off between what SAP say and what Oracle say.
Obviously Oracle know there own database system, so anything Oracle says can also be included.
I generally follow the top down rule, where SAP overrides anything that is contradictory from HP or Oracle, Oracle overrides anything contradictory from HP, and I only implement any parameters from HP if neither Oracle or SAP have anything to say on the matter:
- HP (OS)
- Oracle (RDBMS)
- SAP

SAP User Groups

2011-09-10T13:12:00.000+01:00

Apart from the roles, profiles and authorisation objects involved in SAP security controls, there is also an additional level. User groups.
The user groups in an SAP system can be used to control access to certain authorisation objects (i.e as a restriction in a profile), or used as a method of tagging different types of users to permit certain types of administrative delegation.
Therefore permitting a super set of users to administer passwords for a smaller sub-set of users of a certain user group.

So how do you create user groups? Use transaction SUGR to define the groups, then assign the users in SU01 or SU10.
Take a look at authorisation object S_USER_GRP.

Monitoring Index Usage In Oracle 10G Using V$OBJECT_USAGE

2011-09-05T20:48:00.000+01:00

Have you got some large indexes creeping around?
Some of my systems have 10GB indexes.
These take valuable resources away from the Oracle database:
- DML time (INSERT, UPDATE etc).
- Stats generation time.
- Segment space (hard disk).
- Structure validation time.
- Recovery time.

Removing the index may not be an option. But what if it's not actually used!!

How can we tell if it's used?
Well you could monitor all SQL in the shared pool using the AWR capture script http://www.remote-dba.net/oracle_10g_tuning/t_oracle_index_usage_tracking.htm, or you could use Jonathan's script http://jonathanlewis.wordpress.com/segment-scans/ to see if the index segment(s) has been scanned (full scan). But these don't comprehensively give you a definitive answer.
There could be holes in your monitoring.

Instead, you could use the V$OBJECT_USAGE view to monitor index usage.
Although a very basic method, if you only want to know definitively if an index has been used or not, then it gives you the answer.
It is described in great detail here: http://wiki.oracle.com/page/Monitor+Index+Usage

Turn on:

alter index <INDEX> monitoring usage;



ALTER INDEX SAPSR3."JCDS~Z01" monitoring usage;

Turn off:

alter index <INDEX> nomonitoring usage;



ALTER INDEX SAPSR3."JCDS~Z01" nomonitoring usage;

Check usage (must be as owner of monitored objects):
select count(1) from v$object_usage;

Or use query below to see all monitored objects:

SELECT 

u.name owner , 

io.name index_name , 

t.name table_name , 

DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') monitoring , 

DECODE(bitand(ou.flags, 1), 0, 'NO', 'YES') used , 

ou.start_monitoring start_monitoring , 

ou.end_monitoring END_MONITORING 

FROM 

sys.user$ u , 

sys.obj$ io , 

sys.obj$ t , 

sys.ind$ i , 

sys.object_usage ou 

WHERE 

i.obj#    = ou.obj# 

AND io.obj# = ou.obj# 

AND t.obj#  = i.bo# 

AND u.user# = io.owner#;

Basic Performance Tuning Guide - SAP NetWeaver 7.0 - Part IV - ABAP Runtime Analysis

2011-09-01T18:16:00.000+01:00

This is the final part of my SAP NetWeaver 7.0, Basic Performance Tuning Guide.
You may wish to review Part I, Part II or Part III.
In this part I will focus on the use of ABAP Runtime Analysis to check performance of ABAP programs/reports. This will help you locate areas of ABAP that are eating up valuable response time.

Execute transaction SE30.
Enter the transaction (or program or function module) that you wish to analyse:

Edit the "Measurement Restrictions" variant to either create your own, or simply adjust the DEFAULT.
This will allow us to display additional information which can be very useful when trying to determine performance problems.

Tick the "DB-level ops" option on the Statements tab:

On the Duration/Type tab, select "None" for Aggregation and tick "With memory use":

The transaction or program you entered will be run, but you should see a prompt at the bottom of the screen telling you that the measurement process has started:

Perform the actions you need in the transaction/program.
Once you have finished, simply click Back (exit from the transaction) to the SE30 main screen:

The SE30 screen will display a prompt showing the analysis has finished:

The trace data is stored in a file on the SAP instance server.
This can be seen by clicking the "File Info..." button:

You can see the file name and path:

If you need to load the trace data again for analysis, you can simply click the "Other File..." button on the main screen.
It's also possible to transfer the analysis files via RFC to another SAP system from the "Other File..." sub-screens.
This is very useful if you want to compare performance between two systems.
NOTE: The performance data files are only retained on the server for eight days. I haven't found a way of extending this.

Let's analyse the stats.
Click the Evaluate button:

You will see a basic breakdown of the transaction runtime performance analysis in histogram form.
The three different areas are shown (ABAP, Database and System) against time (as a percentage):

More details on this screen can be found here: http://help.sap.com/saphelp_nw70/helpdata/en/c6/617d27e68c11d2b2ab080009b43351/content.htm

The values shown correlate to the response times measured in the single record statistics plus the time required for analysis.
Note that DATABASE time above, also includes the time the system takes to open and close cursors etc, not just the SQL runtime.
For reference, the STAD output for the same operation (notice the slightly lower DB req time):

The most interesting buttons at the top of the SE30 screen are "Hit List", "Table Hit List" and "Call hierarchy".
Click the Hit List button:

The Hit List will be displayed in descending order (gross time):

The columns are defined as follows:

"No." - Shows the number of times a call was made.
If a "SELECT" is performed in a loop, then this will show the number of times the call was made in total in the loop.

"Gross" - Total execution time in microseconds (running bottom to top of the hit list).
"Net" - The time in microseconds for the specific call(s)
"Call" - The program component that was being executed.
"In program" - The main program of the component.
"Type" - The type of call (DB, System, Non-system).

SAP says here http://help.sap.com/saphelp_nw70/helpdata/en/c6/617d27e68c11d2b2ab080009b43351/content.htm:
"Gross time
The total time required for a call. This includes the runtime of all modularization units and ABAP statements called by the subject.

Net time
The net time is the gross time less the time required for modularization units (MODULE, PERFORM, CALL FUNCTION, CALL SCREEN, CALL TRANSACTION, CALL METHOD, CALL DIALOG, SUBMIT), and for the ABAP statements listed separately. For statements such as APPEND, the gross time is the same as the net time."

In this view, you are looking for records in the list with the larger "Net" value.
This indicates that a large amount of overall time was spent in the call/program listed.

If the Gross and Net time fields do not hold the same value for a record, then double clicking the record will display the sub-components and switch to the hierarchy view:

At any time, you can single click a record, then jump to the ABAP source by clicking the "Display Source Code" button:

NOTE: I always recommend you enable the "New" ABAP editor. It gives you visibility of line numbers and highlights the results of text "Find" operations so you can see what's been found.

Back on the main analysis screen, select the "Table Hit List" button:

The list of tables used in the execution of the transaction/program are displayed in descending order of runtime:

The #Acces column, displays the number of times the table was accessed (read/write) and corresponds to any loops you may have seen in the "Hit List" screen No. column.

The Class column shows TRANSP (Transparent Table), VIEW or POOL (cluster table).

Double click a table to display the call points, from which you can jump to the ABAP source.

Finally, from the main analysis screen, click the "Call hierarchy" button:

The Hit List that you have already seen, will now be displayed in a hierarchy with sub-calls indented under the parent call:

The only real difference between this view and the Hit List table view, is the indenting.
You can still double click to display the execution time overview for that sub-call, or use the "Display Source Code" button to jump to the ABAP source.

In my example screen shots, you will have seen that the PMSDO fetch is taking a large amount of the response time.
I was able to find that this operation was inside the GET_DATA function and check the ABAP code.

The main thing you are looking for in poor performing ABAP code, is where does the execution time get eaten up. As a side note, you may also check the amount of memory used if you are looking to resolve TSV PAGE ALLOC errors.

Try sorting the Hit List by "Net Time" to find the individual components with the highest net execution time.
Check if the operation is a database related call or not. You can then use Part III to trace this further with an SQL trace.
Check the number of loop iterations (The "No." column) in the "Hit List". Are you performing an expensive routine too many times in a loop?

Validate that the code is not doing too much work using iTabs, when some of the work could be done in the database.
The database is the ultimate place to do sorting, grouping and record elimination.
Don't pull back more records than you need, only to loop through an iTab in ABAP and remove them, it's more expensive.

That's it for now. Thanks for reading.

Lock/Unlock OEM Grid Control Accounts From Excel Macro

2011-08-30T20:14:00.000+01:00

As a complimentary post to my previous blog post on Reporting All Oracle User Accounts Through OEM Grid Control, I have decided to post the code for an Excel VBA macro which uses three buttons to call the specific OEM Grid Control URLs that will enable you Lock/UnLock or Edit an Oracle user account using the OEM Grid Control user interface, but from a link on an Excel spreadsheet.

This sounds confusing so maybe I should explain the reason behind this.
I needed to know about all Oracle user accounts throughout the Oracle landscape so that I could compare them to a dump of Active Directory accounts.
The AD accounts get locked when people leave, but the leavers process didn't always include the DBA in the 'cc list.
So, armed with the Excel spreadsheet of AD users, I needed to marry the list to the Oracle accounts and then go and lock the Oracle accounts where the AD account had been locked/expired.

Sound OK so far?
Good. So once I've got the Excel sheet with the ~200 Oracle accounts I need to lock, here's how it could be done using OEM Grid Control (you can give the spreadsheet to your 1st line support people, if they have OEM access).

Three buttons on the spreadsheet: Lock, UnLock and Edit.
Just paste the code into the sheet's VBA section, then change the text "<your_oem_server>" and add the three buttons and hook them to the sub routines.
I should mention that this was an Oracle Enterprise Manager Grid Control 10g implementation.

Enjoy.


Private Sub CommandButton1_Click() 
' Call to open IE with the URL to lock the user based on the data on the current sheet on the current selected row. 

Dim str_URLpart1 As String 
Dim str_URLpart2 As String 
Dim str_URLpart3 As String 
Dim str_URLpart4 As String 
Dim str_URLpart5 As String 
Dim str_complete_URL As String 
Dim str_sys As String 
Dim str_user As String 
Dim IE As Object 

str_URLpart1 = "http://<your_oem_server>/em/console/database/security/user?oname=" 
str_URLpart2 = "&amp;event=lockUser&amp;cancelURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart3 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;backURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart4 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;otype=USER&amp;target="
str_URLpart5 = "&amp;type=oracle_database" 
str_user = Range("D" &amp; ActiveCell.Row).Value
str_sys = Range("A" &amp; ActiveCell.Row).Value 
str_complete_URL = str_URLpart1 + str_user + str_URLpart2 + str_sys + str_URLpart3 + str_sys + str_URLpart4 + str_sys + str_URLpart5

' Create InternetExplorer Object 
Set IE = CreateObject("InternetExplorer.Application") 

' You can uncoment Next line To see form results 
IE.Visible = True 

' Send the form data To URL As POST binary request 
IE.Navigate str_complete_URL 

' Statusbar 
Application.StatusBar = "URL is loading. Please wait..." 

' Wait while IE loading... 
Do While IE.Busy 
Application.Wait DateAdd("s", 1, Now) 
Loop 

Application.StatusBar = "" 

End Sub 


Private Sub CommandButton2_Click() 

' Call to open IE with the URL to unlock the user based on the data on the current sheet on the current selected row. 

Dim str_URLpart1 As String 
Dim str_URLpart2 As String 
Dim str_URLpart3 As String 
Dim str_URLpart4 As String 
Dim str_URLpart5 As String 
Dim str_complete_URL As String 
Dim str_sys As String 
Dim str_user As String 
Dim IE As Object 

str_URLpart1 = "http://<your_oem_server>/em/console/database/security/user?oname=" 
str_URLpart2 = "&amp;event=unlockUser&amp;cancelURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart3 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;backURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart4 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;otype=USER&amp;target="
str_URLpart5 = "&amp;type=oracle_database" 
str_user = Range("D" &amp; ActiveCell.Row).Value
str_sys = Range("A" &amp; ActiveCell.Row).Value 
str_complete_URL = str_URLpart1 + str_user + str_URLpart2 + str_sys + str_URLpart3 + str_sys + str_URLpart4 + str_sys + str_URLpart5

' Create InternetExplorer Object 
Set IE = CreateObject("InternetExplorer.Application") 

' You can uncoment Next line To see form results 
IE.Visible = True 

' Send the form data To URL As POST binary request 
IE.Navigate str_complete_URL 

' Statusbar 
Application.StatusBar = "URL is loading. Please wait..." 

' Wait while IE loading... 
Do While IE.Busy 
Application.Wait DateAdd("s", 1, Now) 
Loop 

Application.StatusBar = "" 

End Sub 


Private Sub CommandButton3_Click() 

' Call to open IE with the URL to edit the user based on the data on the current sheet on the current selected row. 

Dim str_URLpart1 As String 
Dim str_URLpart2 As String 
Dim str_URLpart3 As String 
Dim str_URLpart4 As String 
Dim str_URLpart5 As String 
Dim str_complete_URL As String 
Dim str_sys As String 
Dim str_user As String 
Dim IE As Object 

str_URLpart1 = "http://<your_oem_server>/em/console/database/security/user?oname=" 
str_URLpart2 = "&amp;event=edit&amp;cancelURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart3 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;backURL=%2Fem%2Fconsole%2Fdatabase%2FdatabaseObjectsSearch%3Fevent%3Dredisplay%26target%3D"
str_URLpart4 = "%26type%3Doracle_database%26objectType%3DUSER%26otype%3DUSER&amp;otype=USER&amp;target="
str_URLpart5 = "&amp;type=oracle_database" 
str_user = Range("D" &amp; ActiveCell.Row).Value
str_sys = Range("A" &amp; ActiveCell.Row).Value 
str_complete_URL = str_URLpart1 + str_user + str_URLpart2 + str_sys + str_URLpart3 + str_sys + str_URLpart4 + str_sys + str_URLpart5

' Create InternetExplorer Object 
Set IE = CreateObject("InternetExplorer.Application") 

' You can uncoment Next line To see form results 
IE.Visible = True 

' Send the form data To URL As POST binary request
IE.Navigate str_complete_URL 

' Statusbar 
Application.StatusBar = "URL is loading. Please wait..." 

' Wait while IE loading... 
Do While IE.Busy 
Application.Wait DateAdd("s", 1, Now) 
Loop 

Application.StatusBar = "" 

End Sub

Disaster Recovery - Where To Start

2011-08-25T19:56:00.001+01:00

Perhaps IT implementors think that Disaster Recovery is a dark art, or maybe it's just another item to de-scope from the implementation project due to time restrictions or budget cuts.
But I find that it's coming up more and more as part of the yearly financial system audits (the ones we love to hate!).
Not only is it an action on the main financial systems, but the auditors are training there sights on the ancillary systems too.

So where do I normally start?
Usually it's not a case of where I would like to start, it's where I *have* to start due to missing system documentation and configuration information.
Being a DBA, I'm responsible for ensuring the continuity of business with respect to the database system and the preservation of the data.
So no matter what previous work has been completed to provide a DR capability, or a HA capability (so often confused) it's useless without any documentation or analysis to show what the tolerances are (how much data can you afford to lose - RPO, and how much time it will take to get a system back, RTO).

First things first, ensure that the business recognises that DR is not just an IT task.
You should point them at the host of web pages devoted to creating the perfect Business Continuity Plan (BCP).
It states that a BCP should include what *the business* intends to do in a disaster. This more often than not involves a slight re-work to the standard business processes. Whether this involves going to a paper based temporary system or just using a cut-down process, the business needs a plan.
It's not all about Recovery Point Objective and Recovery Time Objective!
A nice "swim lane" diagram will help a lot when picturing the business process.
You should start at the beginning of your business process (normally someone wants to buy something), then work through to the end (your company gets paid). Add all the steps in between and overlay the systems used. Imagine if those systems were not present, how could you work around them.

Secondly, you need to evaluate the risks among the systems you support. Identify points of failure.
To be able to do this effectively, you will need to know your systems inside out.
You should be aware of *all* dependencies (e.g. interfaces) especially with the more complex business systems like ERP, BPM, BI, middleware etc.
Start by creating a baseline of your existing configuration:
- What applications do you have.
- What versions are they.
- How are they linked (interfaces).
- Who uses them (business areas).
- What type of business data do they store (do they store data at all).

Third, you should make yourself aware of the hardware capabilities of important things like your storage sub-system (SAN, NAS etc). Can you leverage these devices to make yourself better protected/prepared.
An overall architecture diagram speaks a thousand words.

Once you understand what you have got, only then can you start to formulate a plan for the worst.
Decide what "the worst" actually is! I've worked for companies that are convinced an aeroplane would decimate the data centre at any moment. All the prep-work in the world couldn't prevent it from happening, but maybe you can save the data (if not yourself ;-) ).

Virtualisation Conundrum

2011-08-22T19:27:00.000+01:00

Whilst researching ideas for using my VMware ESXi test rig, I came across this blog site: http://weinshenker.net/blog/2011/07/26/oracle-redhat-vmware/
I've added it to my Fav's as a *must read* once in a while.
It's packed with some very interesting articles concerning Linux, Virtualisation and more importantly, Oracle.

It may have changed my mind about using Oracle Enterprise Linux. I'm considering shifting up the Linux tree to RedHat, or experimenting with Fedora.

See the Wikipedia tree diagram http://upload.wikimedia.org/wikipedia/commons/8/8c/Gldt.svg on the right of the Wikipedia Linux Distro page.

Clone a VMware ESXi Virtual Machine from the Command Line

2011-08-13T17:18:00.002+01:00

My version of VMware vSphere is obviously not the full "knock your socks off" version and prevents me from right clicking and cloning an existing VM.
Not to be downhearted, there is another way...

Pre-requisites:
- VMware vSpehere client.
- Access to the VMware ESXi server console & keyboard or an SSH client (like PUTTY for Windows).
- The VMware ESXi root user's password.

Process:
- Create the new VM using vSphere client.
- Give it a very small hard disk.
- Remove the hard disk from the VM once created.

- Enable local tech support mode from VMware *console* screen, press ALT+F1 and log in as "unsupported" with the root password.
or
- Enable remote tech support mode from VMware *console* screen, then use an SSH client to log into the server as root.

- Once you have a command line, locate the source virtual machine's datastore directory under /vmfs/volumes/<datastorename>:
# cd /vmfs/volumes/ds1/source_vm

- Change to the new target VM datastore directory:
# cd /vmfs/volumes/ds1/target_vm

- Now use vmkfstools to clone the disk (note the use of thin-provisioning, you don't have to):
# vmkfstools -i "/vmfs/volumes/ds1/source_vm/source_file.vmdk" "/vmfs/volumes/ds1/target_vm/target_file.vmdk" -d thin

- Back in the vSphere client, add the new disk to the target virtual machine and boot it.

- Bob is your uncle.

Use EMDIAG REPVFY to fix OEM issues

2011-08-12T20:51:00.000+01:00

EMDIAG is a diagnostics utility specifically for the Oracle Enterprise Manager system.
It is useful to diagnose issues with the repository of OEM as it doesn’t need the OMS to be running to use it.

The Oracle document 763072.1 (Oracle Enterprise Manager Grid Control Release Notes for Linux and Windows 10g Release 5) suggests upgrading the EMDIAG kit before performing a general OEM upgrade (it's in the OEM pre-upgrade tasks list).

Follow the steps in document 421499.1 to install the EMDIAG kit into the C:\ directory (you should see the old one already installed there).

Follow the process in the document for upgrading the EMDIAG kit and check the Windows environment variable for the EMDIAG_HOME value.

Once installed, use the “repvfy -version” command in the %EMDIAG_HOME%\bin directory to verify the current installed OEM software.

To use EMDIAG, set your OracleHome from the command line:

set ORACLE_HOME=C:\OracleHomes\oms10g

Change to the EMDIAG_HOME bin directory and run repvfy:

Now fix the errors where possible (generic operations that EMDIAG can perform to fix common problems):

repvfy -fix

Some of the problems cannot be fixed automatically.
You can get more details by running verify with greater detail:

repvfy verify TARGETS -level 9 -detail

NOTE: You may want to redirect to a text file in Windows (> c:\TARGET_details.txt).

The detailed view may open a can of worms for you.
Good luck!

Report All Oracle User Accounts Through OEM Grid Control

2011-08-08T17:54:00.002+01:00

You know the problem: You have Oracle Enterprise Manager Grid Control 10g installed and all your databases are configured in it.
It allows you to manage your entire landscape. Great!

All of a sudden, a "leaver form" pops on your desk from HR with a long list of people who have left the company recently.
Do they have accounts in your Oracle databases?
If you have only one or two Oracle databases to check, then you can use the power of Grid Control to sort out the list in a few minutes.
What if you've got 50+ databases?
What if you've got 100+ databases?
Don't they say: "The little jobs are the most time consuming"?

You may maintain an external spreadsheet of Oracle accounts, or you may not. If you do, or even if you don't, this article will show you how to configure a Oracle Enterprise Manager Grid Control 10g report that will show you all users accounts across the landscape at the click of a button. You could use this technique to extend it to almost anything that can be done in an SQL or PL/SQL session.

Architecture overview:

We will be creating a new OEM Grid Control job called LIST_USER_ACOUNT_DETAILS, and a new report called LIST_USER_ACCOUNT_DETAILS.
The job is executed on the monitored database targets and it stores it's output in the usual MGMT$ table in the OEM repository database.
The report simply pulls and re-connects the job output.

In OEM Grid Control create a new job called "LIST_USER_ACCOUNT_DETAILS".
Add the database targets or target group.

Set the SQL script to be:


SET HEAD OFF; 
SET PAGESIZE 9999; 
SET LINESIZE 999; 
SET TRIMOUT ON; 
SET TRIMSPOOL ON; 
SET FEEDBACK OFF; 
SET COLSEP ' '; 
COL MARKER FORMAT A2; 
COL USERNAME FORMAT A30; 
COL ACCOUNT_STATUS FORMAT A25; 
COL LOCK_DATE FORMAT A12; 
COL EXPIRY_DATE FORMAT A12; 
COL HAS_REMOVED_ROLE FORMAT A30; 
COL HAS_DBA_ROLE FORMAT A20; 
WHENEVER SQLERROR EXIT FAILURE; 
SELECT 
'@|' marker, 
username||'|' username, 
account_status||'|' account_status, 
'|'||TO_CHAR(lock_date,'DD-MM-RRRR')||'|' lock_date, 
'|'||TO_CHAR(expiry_date,'DD-MM-RRRR')||'|' expiry_date, 
'|'||DECODE((SELECT 'X' FROM dba_role_privs WHERE grantee = username AND granted_role ='REMOVED_ACCOUNTS'),'X','HAS ROLE "REMOVED_ACCOUNTS"','')||'|' has_removed_role,
'|'||DECODE((SELECT 'X' FROM dba_role_privs WHERE grantee = username AND granted_role ='DBA'),'X','HAS DBA ROLE!!','') has_dba_role
from dba_users;

Set the Parameters to be "-s" (silent):

Set the Credentials to use an account capable of querying DBA_USERS and DBA_ROLE_PRIVS (you could change this to use ALL_ views and use a lower priviledged account).
Set the schedule for the job. However frequently you wish to query the account details.

Submit the job for execution.

Once the job runs, the job output will be visible in the OEM MGMT$JOB_STEP_HISTORY table.
The actual SQLPlus output is stored in the OUTPUT column which is a CLOB field.
We have had to insert a marker in the SQL so that the output contains a '@|' at the beginning of each line. This is used to determine the start of each record in the OUTPUT CLOB.
We just need to write a report to pull out the details.

Create a new report called "LIST_USER_ACCOUNT_DETAILS".
On the "General" tab, select the "Use the specified target" option and enter the hostname of your OEM Grid Control repository server (DB server).
Select the "Run report using target privileges of the report owner (SYSMAN)" tick box.

On the "Elements" tab, add a "Table from SQL" item and then set the header, then paste in the statement below:


SELECT TARGET_NAME,JOB_TIME,ACCOUNT_DETAILS FROM ( 
SELECT '===============' TARGET_NAME,'================' JOB_TIME,'==================================================' ACCOUNT_DETAILS FROM DUAL
UNION 
SELECT 
TARGET_NAME, 
TO_CHAR(END_TIME,'DD-MON-RRRR HH24:MI:SS') JOB_TIME, 
-- non-regexp option -- TO_CHAR(REPLACE(SUBSTR(mh.OUTPUT,INSTR(mh.OUTPUT,'@| ',1,ct.x),INSTR(mh.OUTPUT,'@| ',1,ct.x+1) - INSTR(mh.OUTPUT,'@| ',1,ct.x)),'@| ','')) ACCOUNT_DETAILS
TO_CHAR(REGEXP_SUBSTR(mh.OUTPUT,'[^@]+', 1,ct.x)) ACCOUNT_DETAILS 
FROM MGMT$JOB_STEP_HISTORY mh, 
(SELECT rownum x FROM ALL_OBJECTS WHERE rownum<=5000) ct 
WHERE TARGET_TYPE='oracle_database' 
AND JOB_OWNER='SYSMAN' 
AND JOB_NAME LIKE 'LIST_USER_ACCOUNT_DETAILS.%' 
AND STEP_NAME='Command' 
) 
WHERE ACCOUNT_DETAILS IS NOT NULL 
ORDER BY TARGET_NAME, JOB_TIME;

 NOTE: In the report SQL, we have assumed a maximum number of 5000 user accounts across all database targets.
If you exceed this (if you have a lot of user accounts) then you will need to increase the number.
We have also assumed that SYSMAN is the owner of the job that created the output.  You should change this if necessary.



You can choose to schedule the report if you wish (you should take into account the source job schedule).
It's probably best to secure the report so that only administrators can see it.

Test the report.



The output format consists of the following columns:
 TARGET NAME
 JOB TIME
 ACCOUNT DETAILS {
                 USERNAME
                 ACCOUNT STATUS
                 LOCK DATE
                 EXPIRY DATE
                 HAS REMOVED ROLE
                 HAS DBA ROLE
                }

The "HAS REMOVED ROLE" column relates to a "flag" role that I use to denote that an Oracle account has been de-activated purposely.
Instead of the account being deleted, it is retained for audit purposes and the REMOVED_ACCOUNTS role granted to it.  You can choose to ignore or adapt this column.

Once you have the output, you can extract it to Excel for comparison with an Active Directory dump or any other means.

Optimal Oracle RMAN Backup Command Options

2011-08-05T09:59:00.002+01:00

Just a very short post about an optimal Oracle RMAN backup command run block.
Here's what *I* think provides an optimal backup for a generic setup.

-- Check our existing files are available before we do some obsolete deleting.

crosscheck archivelog all;
crosscheck backup;

-- Delete any obsolete files before we start so that we have max space available.
-- I have "REDUNDANCY 1" set so that I retain the previous backup until the next backup is completed successfully (you need double disk/tape space available for this).
delete noprompt obsolete;

-- Do the DB and ARCH log backups but put each datafile into it's own piece file.
-- I prefer this because should I need only a specific datafile restored and I have to go to tape, I only need to get the piece that's relevant and this piece is only the datafile needed, saving tape restore time.
-- For Oracle 9i remove the 'as compressed backupset' option.
backup as compressed backupset database filesperset 1 plus archivelog filesperset 1;

-- Now only delete archive logs that are in two separate backups. This is critical.
delete noprompt archivelog all backed up 2 times to disk;

-- Delete the previous night's backup from disk (obviously you need the space to be able to store two backups on disk).
delete noprompt obsolete;

-- Finally, always a good idea to keep a list of datafiles and temp files that existed at the time of the backup.
report schema;

Make sure that you always save the RMAN backup logs.
I've found that they can be very useful during a restore scenario.

As a tip, you can also run a daily job to list what backups are required to restore a database using the "RESTORE DATABASE PREVIEW SUMMARY;" command.

Basic Performance Tuning Guide - SAP NetWeaver 7.0 - Part III - Using SQL Trace

2011-08-02T18:37:00.000+01:00

This is Part III, you may like to see Part I and Part II.

Once you have traced the poor performing program (see Part I of this guide), and you have identified that your problem is in the database response time (see Part II of this guide), then you can start digging deeper into the potential SQL problem by running an SQL trace using transaction ST05.
In ST05, click the "Activate Trace with Filter" button:

NOTE: We are only concerned with an SQL statement that has directly accessed the database (http://help.sap.com/saphelp_nw04/helpdata/en/d1/801f89454211d189710000e8322d00/frameset.htm).

Unless you select the additional tick box "Table Buffer Trace" on the main ST05 screen, to show table buffer accesses, all the trace details in ST05 will be for direct database accesses (bypassing the table buffer).

Since direct database accesses will take longer than the buffer accesses, it is generally accepted that the longer runtimes will be associated with direct database access.

Enter your user id and the name of the report/transaction. This is so that we can try to limit the records in the trace. If you want to trace another user, simply enter their user id:

Click Execute to start the trace:

Now in a separate session (preferably) run the transaction/program with the performance problem.
Once you're happy you have experienced the problem, you can go back into ST05 and click "Disable Trace" (by default it will remember the trace details and disable the running trace, you won't need to re-enter all the details):

Still within ST05, click "Display Trace":

The system remembers the last trace recorded and should automatically populate the details, but you can always enter them for date/time, user id etc:

The SQL trace records are displayed.
The poor performing SQL has it's time (duration) in microseconds (millionths of a second) highlighted in red if it exceeds 10000 microseconds, 10 milliseconds or 0.01 seconds:

(1 second = 1000 milliseconds = 1000000 microseconds)

At the top of the window we can see the transaction name (ZW39), the work process responsbile for executing the dialog step in which our statement was contained, the type of work process (DIA or BTC), the client and user and transaction id.
The OP column shows that this session has reused an existing cursor in the DB for this query.
This is because it has a "PREPARE" operation as the first OP before the cursor "OPEN" and not a "DECLARE" (see here http://help.sap.com/saphelp_nw04/helpdata/en/d1/801fa3454211d189710000e8322d00/content.htm).
If the session re-executed this query at another point in the trace, then it is likely (depending on code and available database resources), that the query would have re-used the existing cursor and even no "PREPARE" would have been visible, just OPEN and FETCH (http://help.sap.com/saphelp_nw04/helpdata/en/d8/a61d94e4b111d194cb00a0c94260a5/content.htm)
The deep yellow colour of each line will alternate between a dark and lighter colour when the table (obj name) changes in the list.

The RECS column shows the number of records retrieved in the "FETCH" operation.
The RC column shows the Oracle database return code 1403 (ORA-1403) which you get when you call "FETCH" in a loop and reach the end of the results (see SAP note 1207927).

The STATEMENT column shows the pre-parsed SQL including bind variables (:A1, :A2 etc).
The second line of the STATEMENT column shows the statement in the "OPEN" database call where all the bind variables have been normalised.
Double clicking on the STATEMENT column delves into the SQL statement a little more:

Here you will see the SQL statement in full (above) and the bind variables, their data types (CH,3 = CHAR(3)) and respective values.
Back on the main trace screen, with the SQL statement selected (single click), you can click the "Explain" button:

Again, you will see the SQL statement with bind variables (remember you can get the values from the other screen) but more importantly, you will see the Oracle Explain Plan.
The Explain Plan shows how the Oracle optimizer *may* choose to execute the statement and the relative costs associated with performing the query (I'll explain more on this in a later chapter of this guide).

In the above example, you can see that Oracle has chosen to perform an Index Range Scan on index "IHPA~Z1" before going to the table IHPA to get the matching data rows.
Single click the "Access Predicates" on the index and you can see what will be applied to the index scan:

Again, you will need to reference the very first SQL view screen to get the bind variables values.
Clicking the name of the index will show the statistics values for the index:

You can see the two columns that make up the index (objnr and parvw).
The Analyze button can be clicked to update the index database statistics real-time or as a background job.
WARNING: Be aware that SAP collects statistics in Oracle using it's own set of predefined requirements as per SAP notes 588668, 1020260 and that you *must* disable the standard Oracle stats gathering jobs as per note 974781. Therefore, the stats may be out of date for a good reason.

The same detail can be seen when clicking the table name.

Back on the main screen, for those who have more knowledge of reading Oracle Explain Plans, you can choose the "EXPLAIN: Display as Text" button, which will display a more detailed Explain Plan that can be copied to a text editor also (I find this very useful):


 Plan hash value: 1636536768 

---------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | 
---------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |         |     2 |    82 |     2  (50)| 00:00:01 | 
|   1 |  INLIST ITERATOR             |         |       |       |            |          | 
|*  2 |   TABLE ACCESS BY INDEX ROWID| IHPA    |     2 |    82 |     1   (0)| 00:00:01 | 
|*  3 |    INDEX RANGE SCAN          | IHPA~Z1 |     2 |       |     1   (0)| 00:00:01 | 
---------------------------------------------------------------------------------------- 

Query Block Name / Object Alias (identified by operation id): 
------------------------------------------------------------- 

1 - SEL$1 
2 - SEL$1 / IHPA@SEL$1 
3 - SEL$1 / IHPA@SEL$1 

Predicate Information (identified by operation id): 
--------------------------------------------------- 

2 - filter("MANDT"=:A0) 
3 - access(("OBJNR"=:A1 OR "OBJNR"=:A2 OR "OBJNR"=:A3) AND "PARVW"=:A4) 

Column Projection Information (identified by operation id): 
----------------------------------------------------------- 

1 - "OBJNR"[VARCHAR2,66], "PARVW"[VARCHAR2,6], "PARNR"[VARCHAR2,36], 
"ERDAT"[VARCHAR2,24] 
2 - "OBJNR"[VARCHAR2,66], "PARVW"[VARCHAR2,6], "PARNR"[VARCHAR2,36], 
"ERDAT"[VARCHAR2,24] 
3 - "IHPA".ROWID[ROWID,10], "OBJNR"[VARCHAR2,66], "PARVW"[VARCHAR2,6]

Finally, it's possible to see the point in the ABAP program where the SQL statement was run.
Single click the statement column, then click the "Display Call Positions in ABAP Program" button or press F5:

The ABAP code will be displayed, and if you are using the new ABAP editor (I think you should), you will be positioned on the statement:

Looking at the ABAP statement, you can see how it has been converted from an OPEN SQL statement into an Oracle SQL statement.
The use of the "IN" keyword has been translated into multiple "OR" statements at the Oracle level.

Once at the ABAP statement level, it is sometimes required to know both Oracle SQL and ANSI SQL, since Oracle generally uses it's own dialect and SAP uses ANSI SQL.
Therefore, you may see the original statement in a somewhat different form, especially when a table join is required on two or more tables.
Lastly, you will notice that the MANDT field is not present in the original ABAP, but it gets added to the predicate list by the ABAP OPEN SQL processor unless you use the "CLIENT SPECIFIED" keyword.

Armed with the SQL performance facts, the knowledge of how to perform an SQL trace and how to link this back to the piece of ABAP code; you should be able to use your database level investigative skills to work out why the statement is causing poor performance.

Make adjustments in the test system and see how the changes affect the performance.
I like to modify the Oracle SQL captured in the SQL trace, then enter it into ST05 (Execute SQL option) with the Explain button, to see if the explain plan is any better.

Some pointers:
- How much data is in the database tables being referenced?
- Is the Explain Plan showing that the query is making best use of available indexes?
- Can you replicate the problem in the test system?
- If this is a custom table, do you need to add additional indexes?
- Could the ABAP OPEN SQL statement be re-written to be more effective (use one query instead of two)?
- Are you SELECTing more fields than you actually need?

You may wish to read the master class in reading Explain Plans from Oracle.
I have blogged before about cardinality in Explain Plans here.

Back once again (with the renegade master...)

2011-08-01T20:38:00.000+01:00

Some hefty time off spent on the beach in the south of Italy (ah che bello) with the in-laws, has revealed a slight sun-tan and a new sense of purpose.
- Part III of my SAP Basic Performance Tuning Guide is nearly ready for posting.
- A new job opportunity has come my way.
- The VMWare test rig will be getting another hammering once I have got some post holiday chores out of the way (still can't believe I got it working in 30 minutes!).

Bring on August!

SAP BI - ORA-04031 - Oracle 10.2.0.5 - Bug 9737897 - End Of.

2011-08-01T20:26:00.000+01:00

If you use SAP BI 7.0 on an Oracle 10.2.0.5 64bit database you may experience this issue.
Our BI system performs tha delta loads for FI data overnight.
These are large loads of records into the cubes.

The problem is Oracle error 04031:


----------------------------------------------------------------------------------
Mon Jul 11 00:46:26 GMT Daylight Time 2011 
Errors in file j:\oracle\bwp\saptrace\usertrace\bwp_ora_3716.trc: 
ORA-00603: ORACLE server session terminated by fatal error 
ORA-00604: error occurred at recursive SQL level 1 
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghteInit")

ORA-00604: error occurred at recursive SQL level 1 
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghteInit")

ORA-00604: error occurred at recursive SQL level 1 
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","kgghteInit")

Mon Jul 11 00:46:26 GMT Daylight Time 2011 
Errors in file j:\oracle\bwp\saptrace\background\bwp_smon_8756.trc: 
ORA-00604: error occurred at recursive SQL level 1 
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select file#, block#, ts# fr...","Typecheck","seg:kggfaAllocSeg")

Mon Jul 11 00:46:27 GMT Daylight Time 2011 
Errors in file j:\oracle\bwp\saptrace\background\bwp_smon_8756.trc: 
ORA-00604: error occurred at recursive SQL level 1 
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select file#, block#, ts# fr...","Typecheck","seg:kggfaAllocSeg")

----------------------------------------------------------------------------------

Once it hits this problem, it causes the rest of the chain to fail on the index rebuilds.
Sometimes the user queries also fail in the Bex Web front-end. The user sees a similar error as those shown above.

Using V$SGASTAT I was able to take snapshots of the Shared Pool usage via a Windows scheduled task.
I traced the problem to the memory allocation of "obj stat memo" in the Shared Pool of the database.
It's constantly growing, and although the "free memory" reduces slightly, it doesn't seem to correlate to the increase in "obj stat memo" usage.

So next step was looking on Oracle Support.
I found bug 9737897, which is valid *upto* 10.2.0.4. But it does not state that it has actually been fixed in 10.2.0.5.
The bug description says: "An SGA memory leak is possible for "obj stat memo" and "obj htab chun"
memory if a segment is dropped and recreated many times."

The bug note recommends querying x$ksolsfts (see here about this table http://yong321.freeshell.org/computer/x$table.html).
The following SQL is recommended by Oracle:

select distinct fts_objd, fts_objn from x$ksolsfts x
where not exists (select 1 from obj$ o
where o.dataobj#=x.fts_objd and o.obj#=x.fts_objn);

The note says that a high number of records returned could indicate that you are seeing the symptoms of the bug.
I was seeing over 160,000 records in a database that had been restarted 24 hours prior.

I produced a lovely Excel chart showing the snapshot times against the memory use of the "obj stat memo" component.
The chart showed significant increases (step ups) of approximately 10MB at specific points in time.
I was then able to search SM37 using the "extended" option, to show background jobs active during the memory "step ups" identified by the chart.
Generally I saw 10-20 jobs active at the time, but the correlation showed that more often than not, the "step ups" were at near enough the same time as a BI_PROCESS_INDEX job.

Looking in the background job log, I was able to identify the process chain and the type of index/load that was being performed.
The indexes in question were FI cube related. They were quite large indexes and they were bitmap indexes ("CREATE BITMAP INDEX...").
Each night these indexes got re-built after a load.
The re-build command was visible in the background job log and included "COMPUTE STATISTIC".

So I had successfully identified a segment(s) that was being dropped and re-created regularly.
This met with the Oracle bug description.

At the bottom of the bug note, it states:

"Note:
If you suffer "obj stat memo" memory leak problem in 10.2.0.5, you can try
_object_statistics = false as a workaround, or following init parameter to see
if it solves problem.
_disable_objstat_del_broadcast=false"

So what does SAP say about this.
Well the following SAP notes were/are available:
1120481 - ORA-4031 - memory allocation errors for object-level stat
1070102 - High memory allocation by 'obj stat memo'
869006 - Composite SAP note: ORA-04031

The only note of relevance is 1120481 which has a short reference to Oracle 10.2.0.4.
The other notes tend to point the read at just blindly increasing the Share Pool. I tried this, it didn't work when 900MB was used by "obj stat memo"!

So, stuck with only one possible solution (other than upgrading to 11g), I can confirm that setting "_object_statistics = false" (alter system set "_object_statistics"=false scope=spfile;) and bouncing the database solved the problem as it completely removes the use of the "obj stat memo" memory area (it's no longer visible in V$SGASTAT).

VMWare ESXi 4.1 Test Rig - UPDATE

2011-07-17T10:55:00.003+01:00

This is an update to my previous post here.

The VMware install onto the HP XW6200 hardware was a snip:
VMware ESXi 4.1 Update 1 Install Guide
VMware ESXi 4.1 Update 1 Release Notes

I decided to use a spare 8GB USB pen (check requirements in the installation guide relating to swap space) to install the VMware hypervisor on. This enables me to use the entire size of the internal SATA disks as VMware datastores for hosting guest operating systems. It also means I can create a backup copy of the USB pen when trying out a VMware upgrade.

Total time to install:
- Download boot CD ISO = 10mins.
- Burn boot CD from ISO = 5 mins.
- Install from CD to USB pen = 10 mins.
- Download vSphere Client = 5 mins.
TOTAL = 30mins.

Once installed, I then downloaded the VMware standalone converter to my laptop.
I had two VirtualBox guest machines on my laptop, so I used the VMware converter to simply pull them off (there is no pun here!) onto the VMware virtual infrastructure.
My LAN is not the fastest so total time to v2v conversion was about 10 hours to do both guest VMs.

The next problem was the Oracle Linux 6.1 install.
This seems to come in two separate parts; the boot ISO and the main DVD ISO.
Downloading the main DVD ISO took about 3 hours over my simple ADSL connection.

Once downloaded, my problems started.
I attached the Oracle Linux 6.1 boot ISO to the virtual CDROM of a new 64bit virtual machine and click "Power On".
Then, bam! No longmode enabled:

What!
Well, it turns out that the history of the Intel XEON processor is a rather tarnished story of rivalry between Intel and AMD.
Although the XEON processor I have *is* 64bit capable, and does support EM64t with Hyperthreading and all that, it just doesn't have the VT (virtualization) capability.
The spec of the XEON processors in the HP XW6200 workstations can be seen on the Intel page here http://ark.intel.com/products/27086/64-bit-Intel-Xeon-Processor-3_40E-GHz-2M-Cache-800-MHz-FSB.

Looking at the Intel page is even more confusing, since it states it doesn't have Hyperthreading, but I have just enabled it in the BIOS. Does it? Doesn't it? Arghh!

In summary, you're better off getting any one of the Core2Duo processors as these will undoubtedly support EM64t and VT.
Only problem is, most don't support 8GB of RAM due to the system board limitations (more than likely a soft limitation). Oh dear indeed.

So my story doesn't end there, I'm now in the process of downloading and installing 32bit Oracle Linux 6.1. Not quite what I wanted, but it will suffice because I have limited memory so will not be needing the use of 64bit memory addressing capabilities - yet.
My other task is to download the 32bit version of the Oracle 11g XE. But wait, Oracle only provide a Linux version of the 11g XE in 64bit mode. Scuppered! I'll have to use the 32bit version for Windows only.

As you can see, we are in this awkward transition period where software is both 32bit and 64bit but hardware is 32bit or 64bit or 64bit with flags.
We are trying our best to virtualise, but for the small guy it's an expensive project.
You either buy a server or you buy the latest and greatest desktops that support more than 4GB of RAM.
It's a very tricky thing to get right.
For me, it's a case of trying to keep skills current. I think I can afford £166 for now, then re-invest another £166 at a later stage to make the jump to a "proper" 64bit (with VT) capable system.

One other thing, once the free version of VMware ESXi expires (you get 60-days free use of all the features), you'll need to spend some time faffing with the license to register it (downgrade to free only features) so you can continue to use it. I did this up-front so I don't have a situation where I can't use the VMs because I forgot the expiry date.
See instructions here: http://planetmediocrity.com/2010/09/vmware-vsphere-evaluation-downgrade/.

VMWare ESXi 4.1 Test Rig - Up up and away!

2011-07-15T14:26:00.001+01:00

This weekend I will be focussing my attention on getting my new (old) HP XW6200 workstation transformed into a VMWare ESXi 4.1 test rig.
Bought on eBay for £166.00.

I did take a brief look at the Oracle VM 2.2 offering, but it required about twice the amount of storage space to install it compared to the ~200MB required by VMWare ESXi 4.1 and it didn't have the nice GUI for simplicity or the excellent VMWare converter for transfering my existing VirtualBox guest VMs to the virtual infrastructure.

The plan is to install VMWare onto a USB pen device, then use the 500GB SATA disk and 1TB SATA disk as virtual disk storage where I can host all manner of Virtual Servers.
I'm planning to use OpenFiler inside a virtual appliance, to provide a shared chunk of disk via iSCSI so that I can get ASM working in a cluster.
I'm hoping to get an Oracle Unbreakable Linux server running and download the beta Oracle 11gR2 XE RDBMS.
For those of you in the know, Oracle don't officially support VMWare (I wonder why...) but since I'm not running a production database, this is not a problem. I know for sure that it *will* work fine.

The XW6200 machine spec:
2 x Intel Xeon 3.4gGhz with HT and EM64t.
256MB graphics card.
4GB RAM (for the moment, but will support 8GB via 4x2GB DIMMS).
500GB SATA
1TB SATA
CDROM
Floppy (really!)
USB sockets galore.

It's a little power sucker, but it supports wake-on-lan so I can have it shutdown until required.

When Should I Pay For Oracle Diagnostics/Tuning Pack in 10G/11G

2011-07-11T18:59:00.000+01:00

If you are unsure about what action will result in you having to pay for a license for the Oracle Diagnostic Pack or Tuning Pack (or other options come to think of it), then you could use the query below to determine the check conditions:

SELECT NAME, DESCRIPTION FROM WRI$_DBU_FEATURE_METADATA;

This will output the name of the feature and the description of the check that is performed.
You could also see the check "code" if you include the USG_DET_LOGIC column in the SELECT.

In 10G it is immediately possible to see that the AWR is only classed as used if "At least one Workload Repository Report has been created by the user".
In 11G, AWR is classed as used if "At least one AWR Baseline has been created by the user" OR "At least one AWR Baseline Template has been created by the user".

The AWR is licenseable (http://www.orafaq.com/wiki/AWR).

So does this mean you can select from those views? Well Oracle do ask you to in some support situations. So maybe it is reasonable to assume so.
The Oracle 11G Diagnostics Pack doc http://www.oracle.com/technetwork/oem/grid-control/overview/diagnostics-pack-11gr2-datasheet-134502.pdf states otherwise: "These features may be accessible through Oracle Enterprise Manager , Oracle SQL Developer and APIs provided with Oracle Database software. The use of these and other features described in the product licensing documentation requires licensing of the Oracle Diagnostic Pack regardless of the access mechanism.".

The Oracle 11G Tuning Pack *does not* say the same: http://www.oracle.com/technetwork/oem/grid-control/overview/tuning-pack-11gr2-datasheet-134450.pdf
But, the tuning pack APIs use features included in the Diagnostic Pack. Chicken and egg.

I guess it's open to interpretation. Better stay safe and ensure that you don't hit any of the checks.

Analysing SAP Java Memory Usage in Windows

2011-07-09T11:37:00.000+01:00

Running SAP systems on a Windows operating system provided me with some difficulties when it came to understanding memory usage. I was used to Unix based systems and using terms like "swap file", "virtual memory" etc.
Hopefully this article will help you diagnose SAP Java system memory issues on Windows, especially “Out Of Memory” (OOM) error situations in the SAP Java stack.
It has been written based on Windows Server 2003 64bit with SAP Netweaver 7.0 (2004s) and Oracle 10g (10.2.x).

REFERENCES:

Understanding memory usage in Windows 2000.
SAP Note 723909 v33 - Java VM Settings For J2EE 6.40/7.0
SAP Note 879377 - Adjusting SDM Java Memory Usage
SAP Note 716604 v56 - Sun J2SE Recommended Options
SAP Note 313347 - Windows Editions Memory Support
Netweaver 7.0 SR2 Java Windows Oracle Installation Guide.

WINDOWS MEMORY TERMINOLOGY

Before we go into the memory details of an SAP system on Windows, it is useful to understand the memory terminology of the Windows operating system.
This is useful if you come from a UNIX background.

RAM – Random Access Memory, physical installed memory in the server.

Page File – An area of hard disk (or other storage medium) used for temporary storage of memory blocks. Generally this should be manually set according to the installation guide for the SAP system.

Virtual Memory – The logical memory accessed by user space program (calculator or any other app) this is roughly the summary of physical RAM plus page file size.

Kernel Memory – This is the area of virtual memory and RAM used by the operating system for device driver memory (non-user space programs).

Page Faults – When a process tries to access a portion of virtual memory that has been swapped to disk, a page fault occurs and the swapped portion is read back into RAM from disk potentially displacing an existing memory page).

Page – A block of memory usually around 4K in size.

DETERMINE OS MEMORY

We need to first determine how much memory is actively being used at the operating system level and how much memory is available to us.
On the Windows server, open Windows Task Manager and click on the “Performance” tab:

This will show the current memory usage of the entire server.
Ignore the line graphs and histograms as the bottom section of the pane is more important.
The following sections are described:

Physical Memory - Total Total amount of RAM installed in server.
Physical Memory - Available RAM available for CPU processes.
Physical Memory - System Cache RAM being used by the file cache.
Commit Charge - Total Size of virtual memory in use.
Commit Charge - Limit Max amount of virtual memory.
Commit Charge - Peak Virtual memory used high water mark.
Kernel Memory - Total Paged and non-paged mem used by the operating system's kernel e.g. device drivers etc.
Kernel Memory - Paged Virtual memory set aside for the kernel.
Kernel Memory - Nonpaged Physical RAM set aside for the kernel.

The most important to watch when measuring memory usage of an SAP system are the following:

Commit Charge – Peak: This is the high water mark for memory usage on the server. If this is close to the maximum available virtual memory (Commit Charge – Total) then you could be suffering performance issues caused by excessive paging to/from hard disk.

Physical Memory – System Cache: This is the file system cache that stores data accessed from the hard disk in RAM for more efficient data retrieval.
This value is maintained by the operating system but can be influenced by setting the performance options in “My Computer -> Properties -> Advanced -> Settings (Performance) -> Advanced” shown below:

Which is also related to (or rather overridden by) the setting of “Maximize data throughput for network applications” in the “File and Printer Sharing for Microsoft Networks” of the local network connections in control panel:

MANAGE OS MEMORY

Before you try and tune the memory in the SAP system, you should first explore all avenues of optimising the amount of RAM available in the operating system.
We can see that there are three factors that affect the amount of RAM available for use with programs:

1, Other programs running.
2, The file system cache.
3, Device drivers loaded in the system (kernel).

Reducing the number of running programs and services is a good way of freeing up memory.
The amount of memory used by a specific program or service can be seen in Task Manager on the “Processes” tab:

Only the needed applications for the application and its management should be running.
The svhost.exe binary signifies services that are visible in the services applet in the Administrative Tools applet in Windows Control Panel.
Disabling redundant services will improve available RAM.

You should seek to reduce the file system cache as much as possible.
Since the SAP system is not a file server, any file system level buffering of data is only wasting resources. This is especially true for the Oracle database which buffers read data in memory anyway, which is then potentially buffered again in SAP.

You can reduce the system cache by setting the “Maximize throughput for network applications” as discussed in the previous section. This should be done on all servers within the SAP landscape (apart from specific cache servers, subject to SAP recommendations).
This is generally recommended by SAP in the installation guide for the Netweaver system.

Lastly, disable any redundant devices from the device manager.
These devices require kernel memory, some of which is taken directly from RAM.
You can see the memory ranges allocated to devices in the device manager accessed from the “Computer Management -> Device Manager” option from the Administrative Tools applet in Windows Control Panel.
Simply select “View -> Resources by Connection” in the Computer Management panel:

Expand the “Memory” item from the tree on the right and the devices will be listed with their memory address ranges:

The address ranges are in hexadecimal notation and in the form [RegionLetter][HEX Values] e.g. A0000000 – AFFFFFFF. The value in hex is bytes and is calculated as follows: FFFFFFF = 4294967295 bytes in decimal. Divide the decimal by 1024 accordingly to get a value in Kilobytes, Megabytes or Gigabytes.

Devices which are not required can be disabled (right click and choose “Disable”).

DETERMINE SAP JAVA SYSTEM MEMORY

The memory allocated to an SAP system comprises of three parts:

1, The Database instance memory.
If the database is on the same server as the SAP system, then some of the operating system memory will be taken up by the database itself.

2, The ABAP system memory.
If the SAP system is a dual stack system with an ABAP instance also on the same server then some memory will also be taken up by the ABAP instance.

3, The Java stack memory.
The Java stack comprises of mainly three (sometimes four) running Java Runtime Environments (Server, Dispatcher & SDM).

The main memory of the server must be divvied up between these 3 main components of the SAP system.

Since we are concentrating on the Java stack only, we will assume that SAP recommendations were followed correctly for the setup of the database and ABAP components.

Using Windows Task Manager it is possible to determine the amount of memory in use by the SAP Java system.
The jlaunch.exe processes represent the Java Runtime Environments of the Java stack and therefore the memory usage for each process equates to the current virtual memory usage for the SAP processes:

In the Windows Task Manager you must change the view to be able to see the process ID of a process:

Ensure that you select the following columns:

PID,
Memory Usage,
Peak Memory Usage,
Page Faults
Virtual Memory Size,
Paged Pool,
Non-paged Pool

With these columns selected, we can get a far better overview of the memory usage for the SAP components:

You can now see that the actual virtual memory usage (VM Size) of the PID 5144 (what we think is the SAP Java server process) is ~1.7GB in size (1,678,188 K).

The summary of the VM Size column should roughly add up to the size of the “Commit Charge – Total” value displayed on the “Performance Tab”.

Generally you can guess that the jlaunch processes with the highest memory usage will be the SAP Java server processes (since they have the largest memory settings out of the box), followed by the dispatcher and finally the SDM (with the smallest default settings).

For confirmation, it is possible to find the exact process id using the jcmon.exe program.
Use option 30 (Shared Memory Menu), then select option 2 (Display Process Data):

The running components will be displayed and we can confirm that PID 5144 is in fact the server0 process:

DETERMINE SET MEMORY VALUES

We have determined what virtual memory the SAP Java processes are actually using but we need to see the configuration settings of the Java processes so we know what to expect at startup and maximum use.

There are a number of different areas of memory for a Java Runtime Environment.
The following is a very brief overview:

Heap Size The total amount of memory stack available to a Java Runtime Environment process (excludes the resident memory for the binary). The minimum heap memory is set using parameter “–Xms”.

New Size Also known as the “young generation”, is the amount of heap memory allocated by default for new java classes created within the JRE. The new size is set using parameter “–NewSize”.

Perm Size Also know as the “old generation” or “tenured generation” is the amount of heap memory allocated by default for java classes that have survived a number of garbage collections and are deemed “permanent” in the JRE. The perm size is set using parameter “–PermSize”.

Any java classes that are no longer referenced are cleared from memory during the garbage collection cycles which are either time-triggered or based on a percentage of free heap size.

The sections of heap memory and the heap memory total size itself can be set within bounds using the “-MaxXXXXSize” arguments or “-Xmx” (for the heap itself) to the JRE.
The JRE will not break these bounds.
If the amount of Heap allocated is not enough for the running JRE it will constantly try to garbage collect more aggressively. This will be seen in the developer trace files e.g. dev_server0, dev_dispatcher and dev_sdm.

If the JRE is still unable to reference enough memory, then a Java.Lang.OutOfMemoryError will be thrown. If this is visible in the dev_server0 trace file, then this is the JRE that threw the error and was suffering the problem (more than likely).

If no maximum heap size is set using the –Xmx parameter, then the JRE will attempt to expand the heap dynamically when required (it may not shrink back).
If the Windows virtual memory is exhausted, then the JRE will eventually throw a Java.Lang.OutOfMemoryError and Windows may throw its own error.

Now we understand roughly how the JRE memory is utilised, we can see the settings for the respective SAP JREs for the server, dispatcher and SDM.

The JRE server0 memory allocation can be seen either through ConfigTool by selecting “Cluster-Data -> Instance ID XXXXXX” -> Server” and checking the parameters on the right hand side:

Or by looking in the jlaunch process trace file:

This concludes the analysis of memory usage for the Java stack.
Using this information you should now be able to determine if this meets the recommendations by SAP.

SAP JAVA STACK MEMORY RECOMMENDATIONS

During the creation of this article a number of SAP notes became of significance.
Generally SAP try to recommend certain settings during installation of the SAP software, and then at a later date, they will release a note that details changes to the installation settings.

The base install of SAP Netweaver 7.0 Java (SR3) seems to install with the following settings:

Server:
-Xms 1024m
-NewSize 171m
-MaxNewSize 171m
-PermSize 256m
-MaxPermSize 256m

Dispatcher:
-Xms 170m
-NewSize 57m
-MaxNewSize 57m

SDM:
-Xms 256m

The installation documentation specifically states that the sizing of the Windows page file is essential for correct operation of the SAP system.
The document states in the section “3.1.2 Requirements Checklist for a Central System”:

“Minimum RAM:
1.5 GB
To check RAM, in the Windows Explorer choose Help About Windows.
Paging File Size: 1 times RAM plus 8 GB”

This would infer that on a system with 10GB of RAM, the page file should be 18GB in size. This would give a total virtual memory size of 28GB.

According to note 723909, SAP’s latest recommendations for sizing the JRE memory is as follows:

“For 32 bit platforms we recommend to start the server nodes with 1GB
whereby the initial and the maximal heap size should be equal: -Xmx1024m
-Xms1024m
Higher values may cause troubles, for example see notes 736462 and 664607
about Windows DLLs preventing big java heap.
We recommend using additional server nodes instead of huge heaps.

For 64 bit platforms (that means not only your OS but also your JDK
supports 64 bit) we recommend using 2GB: -Xmx2048m -Xms2048m
Take into account while planning your productive landscape: for NW 7.0
(04s) there is a general recommendation to use 64bit systems, see note
996600.
2.2 For dispatcher nodes it's sufficient to set the heap size to 171m on 32
bit and to 256m on 64 bit platforms. There is no need to apply the
parameters mentioned under 4-9 below.
Default size of the young generation (one third of the heap) is ok for
dispatchers.
If the instance contains more than three server nodes we recommend to add
50MB of dispatcher heap for each additional server node.
2.3 The max heap size (plus max perm size) of all Java server nodes
must fit completely into the physical memory in order to avoid heavy
paging on OS level. You must also consider the space needed for OS
and other processes.
For each JavaVM on the server, all Java memory must fit completely
into physical memory (no paging)!
Therefore the rule of thumb for number of server nodes for SUN and HP
JavaVM with 1 GB Heap each will be
#ServerNodes = (AvailableMemory / 1.5 GB)
and factor 2.5 should be used instead of 1.5 for 64 bit with 2GB heap size.”

The additional note 716604 recommends some specific settings for the SUN JVM:

“For 6.40/7.00:
For the heapsize and the size of the new generation please take care of the
recommendations of note 723909.
For Windows (ia64 and x86 (64Bit)) and Linux/ia64 (Itanium) platforms set
the stack size to at least 2MB for each serverID, the dispatcher and the
Java based tools:
-Xss2m
Additionally, increase the stack size of the JIT compiler threads at least
on the server nodes of the J2EE Engine to 4MB on these platforms with
following parameter (effective on Windows starting with 1.4.2_17 b12 resp.
1.4.2_18):
-XX:CompilerThreadStackSize=4096”

Any modifications to the Java stack should be proven in the DEV and QA environments before application to production.

Basic Performance Tuning Guide - SAP NetWeaver 7.0 - Part II - Reading Single Record Statistics

2011-07-01T09:25:00.002+01:00

This is Part II. You may like to see Part I.

In Part I, we showed you how to trace an ABAP program using STAD to show the single record statistics of the response times for a transaction/program.
In this part, we will go through and decipher the statistics to help you decide where the problem may lie in your slow performing program.

Following Part I, we saw the STAD screen output. Below is a snapshot of the right hand side of the screen showing the "standard" view fields:

The "Response time (ms)" field shows the total response time for the dialog step (or summary of steps if you have chosen not to expand the left tree).

As a reminder, SAP says (http://help.sap.com/saphelp_nw70ehp1/helpdata/en/21/2c8f38c7215428e10000009b38f8cf/content.htm
):
"Response time = wait time + execution time
where: execution time =
Generation time during the run
+ Load time for programs, screens, and graphical user interfaces
+ Roll times for rolling in the work data
+ ABAP processing time
+ Database time
+ Enqueue time for logical SAP lock processes
+ CPIC/RFC time
+ Roll wait time (excluding task types RFC/CPIC/ALE)."

So if we take a look at our initial screen of values, the example below shows that the "DB req time (ms)" is one of the larger values (4929):

The total database request time (time taken for database work to be done for this step) is more than 92% of the total response time for the step.
If the database time IS NOT a significant percentage of total response time, then you should check the other parts that make up the total response time, for clues.

At this point, it's better to change the screen columns so that you can see all the timings.
The best view is the "Time Analysis" view. Switch views by clicking the "Sel. fields" button:

Click "Time Analysis":

You will now see more timing fields

The fields highlighted are responsible for the total response time calculation.

In our example, TOTAL RESPONSE TIME = Wait Time + DB Req Time + Processing Time + Load Time + Generating Time + Roll In Time + Roll Wait Time + Enqueue Time.
=> 5330 = 0 + 4929 + 122 + 17 + 0 + 262 + 0
(5330 / 1000 = 5.3 seconds total response time)

You should check each of the fields, looking for disproportionate response times.

The "Wait time (ms)" indicates the amount of time the dialog step was waiting to be processed by a dialog work process. High values indicate that the overall system performance is degraded. Maybe a lack of memory, CPU or I/O performance at the O/S layer could be causing issues. Check the dispatcher queue.

The "Time in WPs (ms)" indicates the time that the dialog step was being actively serviced by a work process and not by any other type of process (BTC, UPD, UPD2 etc). Check "SM51 -> Goto -> Server Name -> Information -> Queue Information" and SM66 to see how busy the system is.

The "Processing time (ms)" indicates the amount of time the individual components spent processing (working) and not idle or sleeping or waiting. Check SM50 and SM66 to see how busy the system is.
Use ST06 (O/S monitor) to check CPU usage and system load.

The "Load time (ms)" indicates the amount of time taken to load the program code into memory buffers.
A problem here could indicate the program buffer is not large enough (see ST02). You should check the CPU time if the load time value is large.

Values for "Generating time (ms)" show the time spent compiling the ABAP source code. You will see a value here if the code has not already been compiled through SGEN. Any large values here for programs already generated could mean a problem in the database with the code storage tables.

Large values for "Roll (i+w) time (ms)" indicates that the Roll-In and Roll-Wait times are being affected.

Roll-In is the process of moving the users session context into the work process so that it can be used to execute the dialog step. High values could indicate that the roll buffer is too small (see ST02) or that the user has a very large set of roles and profiles and is consuming too much memory.

Values for "Enqueue time (ms)" show the time waited for a logical database lock to be performed.
A high value here could indicate excessive contention (someone else doing the same work) in the SAP system or the underlying database. Maybe check SM13.

If we look at a CPU time example, a high value here would indicate a large amount of overall processing causing the WP to use more CPU.
Maybe a rogue loop, or a large internal table operation.
The example below shows a high CPU time for a background job performing a lot of RFC calls.
Notice how the processing time (just off the screen shot, sorry) has increased too (some component has to do the work):

Could be that a lot of data/work was transfered by RFC call.

Summary:
If you see high database time in your analysis, Part III will show you how to perform an SQL trace to see what the database is doing for so long.

If you have high CPU/processing time, then in Part IV you will be shown how to find where exactly in the ABAP program is causing the most response time.

Oracle Home Space Cleanup (how random that sounds)

2011-06-25T10:08:00.000+01:00

Just a quick post to make you aware of a fantastic option I recently discovered in opatch.
I look after a large number of database systems, some having multiple Oracle homes on the same drive (I know I know, but when you're limited on drives...).
These homes consist of things like Oracle RDBMS (obvious one), Oracle 10g EM Agent and finally Oracle 10gAS (and 10g OEM if you like to think that OMS is separate, but it's a 10gAS so I didn't).

Generally the Oracle home for a 10gR2 RDBMS Enterprise Edition binary codeset seems to be around 1GB in size.
Apply a couple of opatches and you could be looking at 2GB!
All of a sudden, the partition is looking quite full (it used to be 9i on there when it was originally designed!!).

Lets bring in a new opatch command that came in as part of an opatch upgrade in 10gR2 (opatch patch 6880880).
The command is part of the "opatch util" command and is a sub-command called "cleanup".
It supposedly removes the duplicated binary files taken during an opatch patch application.

Oracle doc 550522.1 states "Starting with 10.2, Opatch does not backup only the affected modules, it also takes a backup of the complete affected libraries to $ORACLE_HOME/.patch_storage//backup//.".

The cleanup command only removes files that are not required as part of a patch rollback.

Syntax: "$ opatch util cleanup".

You will be prompted to select the oracle home and confirm the cleanup. It will also inform you of the amount of space saving.
Enjoy the space you get back, while you can!

Checking Lack of Bind Variables & Adhoc SQL in Oracle

2011-06-24T11:45:00.004+01:00

The following Oracle SQL is a crude method that can be used to check the V$SQLAREA view for SQL statements that have the same execution plan, but where the SQL is unique. e.g. SQL statements where only the predicate changes in the WHERE clause. These are otherwise known as adhoc queries (something that SQL Server 2008 is supposed to be very good at detecting).

The statement was constructed based on the "Understanding Shared Pool Memory" document by Oracle (http://www.oracle.com/technetwork/database/focus-areas/manageability/ps-s003-274003-106-1-fin-v2-128827.pdf).

The doc basically says that a count of the PLAN_HASH_VALUE column versus the number of SQL_IDs using the plan indicates how bad the SQL needs bind variables since it should have only one or two SQL statements (the lower the better) per plan.

In my opinion, systems that return >60% for ACTION "Could use bind variables." should either consider revising the application SQL code (permanent fix), or using the CURSOR_SHARING=FORCE init parameter (temporary fix!).


SELECT 'Multiple SQLs in SQLAREA using same plan:' DESCRIPTION,sum(pct_hash_use) PCT,'Could use bind variables.' ACTION FROM (
select hc.plan_hash_value, 
hc.hash_use_count, 
sc.sql_count, 
round((hc.hash_use_count/sc.sql_count*100),2) pct_hash_use 
from 
(select s.plan_hash_value, count(s.plan_hash_value) hash_use_count 
from v$sqlarea s 
group by s.plan_hash_value 
having plan_hash_value > 0 
) hc, 
(select count(1) sql_count 
from v$sqlarea t 
where plan_hash_value > 0 
) sc 
order by pct_hash_use desc 
) WHERE hash_use_count > 1 
UNION 
SELECT 'Single SQLs in SQLAREA using same plan:' Description,sum(pct_hash_use) PCT,'No action needed.' ACTION FROM ( 
select hc.plan_hash_value, 
hc.hash_use_count, 
sc.sql_count, 
round((hc.hash_use_count/sc.sql_count*100),2) pct_hash_use 
from 
(select s.plan_hash_value, count(s.plan_hash_value) hash_use_count 
from v$sqlarea s 
group by s.plan_hash_value 
having plan_hash_value > 0 
) hc, 
(select count(1) sql_count 
from v$sqlarea t 
where plan_hash_value > 0 
) sc 
order by pct_hash_use desc 
) WHERE hash_use_count <= 1;

You can also use the following query to determine roughly how much memory is wasted on single SQL statements that have been executed only once (you should use this on a well bedded in system that has been running for a while):


select count(1) num_sql, 
sum(decode(executions, 1, 1, 0)) num_1_use_sql, 
sum(sharable_mem)/1024/1024 mb_sql_mem, 
sum(decode(executions, 1, sharable_mem, 0))/1024/1024 mb_1_use_sql_mem 
from v$sqlarea 
where sharable_mem >0;

You should double check the output with the SQL text in V$SQLAREA to ensure that the SQL is truly ad-hoc.

Remember to get some hard and fast values for the "Concurrency" wait class before and after the change (bind variables introduced or init parameter changed).

You can use SQL: "select * from v$system_event where wait_class#=4 order by average_wait desc;" to determine the average wait time.

I would expect the library cache operation wait times to reduce as hard parses are reduced.

Here is an example of a system that is not using bind variables to optimise SQL:

When running the script we get:

You should note that an OLTP system such as SAP ERP should have a much lower count for adhoc SQL compared to a SAP BW system where users can write their own queries.

Oracle Explain Plans: Cardinality & Histograms

2011-06-24T11:27:00.000+01:00

This is one of the better white papers on how to read an Oracle explain plan:
http://www.oracle.com/technetwork/database/focus-areas/bi-datawarehousing/twp-explain-the-explain-plan-052011-393674.pdf
The most important point is made on cardinality on page 10 when explaining "Data Skew".
The term "Data Skew" is used to refer to the imbalance of values with respect to a normal distribution (http://en.wikipedia.org/wiki/Skewness).
The imbalance is shown as the Mode (http://en.wikipedia.org/wiki/Mode_(statistics)) of the values in a column of a table.

Example:


SELECT * FROM A_TABLE;

COL1 | COL2
-----|-----
   1    |   A   <== Mode = most frequent.
   2    |   A
   3    |   A   <== Median = middle one
   4    |   B
   5    |   B

Histogram for COL2:



Value |Number
------|------
A     |###
B     |##

Data Skew means that more of (usually it takes a lot more, like +60%) the values in COL2 have a value of "A" than of "B".
As the Oracle whitepaper points out "One of the simplest formulas is used when there is a single equality predicate in a single table query (with no histogram). In this case the Optimizer assumes a uniform distribution and calculates the cardinality for the query by dividing the total number of rows in the table by the number of distinct values in the column used in the where clause predicate.".

In example "SELECT * FROM A_TABLE WHERE COL2='A'" this would equate to: Cardinality = 5 / 2 A cardinality value of 2.5 would be rounded to 3.
Accurate enough.

The problem with this is that the larger the number of rows the more skewed the cardinality will become.
Imagine the table with values like so:



Value |Number
------|---------------
A     |###############
B     |##

Now in example "SELECT * FROM A_TABLE WHERE COL2='A'" this would equate to: Cardinality = 17 / 2
A cardinality value of 8.5 would be rounded to 9.
Definitely not 15.

To solve this problem, Oracle has the capability to collect statistics for individual columns.
You can use the DBMS_STATS.GATHER_TABLE_STATS command to collect stats on the column (http://www.dba-oracle.com/t_histograms.htm).

The below statement will collect stats on the COL2 column of A_TABLE in the OE schema with a bucket size of 20 buckets. These column stats are histograms of the distribution of values.



BEGIN
DBMS_STATS.GATHER_table_STATS (OWNNAME => 'OE', TABNAME => 'A_TABLE',
METHOD_OPT => 'FOR COLUMNS SIZE 20 COL2');

END;
/

Two types of HISTOGRAM are available in Oracle, FREQUENCY and HEIGHT.
Oracle uses HEIGHT based histograms *only* when the number of distinct values of a column is greater than the number of buckets specified.
"In a height-balanced histogram, the column values are divided into bands so that each band contains approximately the same number of rows. The useful information that the histogram provides is where in the range of values the endpoints fall.".

Oracle will use FREQUENCY based histograms by default when the number of distinct values of a column is less than or equal to the number of buckets specified.
"In a frequency histogram, each value of the column corresponds to a single bucket of the histogram.".

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm

It is possible to view the collected histogram for a column:



SELECT column_name, num_distinct, num_buckets, histogram
FROM USER_TAB_COL_STATISTICS
WHERE table_name = 'A_TABLE' AND column_name = 'COL2';

SAP RFC Gateway Error 10060

2011-06-24T11:12:00.000+01:00

Yesterday I spent the majority of the morning tracking a problem in the SAP ERP system.

It has been occurring for a while but I have had far more important issues. Finally I got round to looking at it.

In hosta SM21 system log, the gateway process (RD) had registered a network connect error (Q0I) "Operating system call connect failed (error no. 10060)":

The architecture of the SAP systems involved in the landscape looks like the following diagram, which shows an SAP ERP single ABAP stack (hosta), which is connected to an SAP Enterprise Portal system which is clustered (hostb & hostc):

Looking in the gateway error log (TX: SMGW), I saw the network interface (Ni) timeout error when looking up the hostname for IP address 192.168.1.1:

The business process involved means that an RFC connection is established from the EP server processes (hostb) to the ERP gateway process (hosta).
As you can see in the architecture diagram, the IP 192.168.1.1 is the private IP address of the hostb server in the EP cluster.
The private IP address is only used as a heartbeat address as part of the Microsoft Cluster service.
So the question was, why was the SAP ERP gateway process receiving inbound connections from hostb via the private IP address?

The method of analysis involves understanding how the IP to hostname lookups are performed on the Windows servers:

First, if you open a command prompt and use NSLOOKUP to check the hostname ("C:> nslookup hostb") it will return the hostname from the Name Service configured for the Windows Server. Usually this is in the order of hosts file first, then DNS. It performs the lookup using the appropriate network interface according to the network routings ("C:> route print").
In our example, the nslookup correctly returned the public IP address.

Second, you can issue a PING of hostb from hostb (itself) using "C:> ping hostb".
When using PING, it sends a network packet using the appropriate network interface according to the network routings ("C:> route print"). Since we are pinging ourselves, it will use the first network interface in bind order of the Windows network connections.
In our case, it was pinging the *private* IP address.
According to Microsoft, the PING command in Windows uses gethostbyname() to find the IP address for the destination to be ping'd(http://support.microsoft.com/kb/822713).

So we have established that the private IP address is seen when we ping our own servername on hostb, why does this mean that the private IP is seen as the client in a SAP RFC gateway connection?
Well, in the same way that PING uses gethostbyname(), the RFC connection that is established through jlaunch.exe (host of the JAVA stack server processes) also uses gethostbyname(). Of course, we know what IP address a gethostbyname() call returns, as this is used in the PING command .

(findstr /M gethostbyname jlaunch.exe)

So, the RFC connection performed by the JAVA stack is created from the jlanuch.exe binary, which uses gethostbyname() during the bind() call, which returns the private IP.
The RFC connection is established and part of the RFC connection process the jlanuch.exe process passes it's calling IP address. Which is the private IP address.
The ERP system gateway at the other end of the connection receives the incoming connection and notes that it is coming from 192.168.1.1. Should the ERP system on hosta wish to make an RFC callback (http://help.sap.com/saphelp_nw04/helpdata/en/22/042a77488911d189490000e829fbbd/content.htm)
(http://help.sap.com/saphelp_nw04/helpdata/en/22/042a91488911d189490000e829fbbd/content.htm), it has no chance of returning the call to the private IP address on hostb. This is where we get the error "NiHsLGetHostByName failed".

So how do we resolve the problem?
We could code the lookup into the hosts file (172.x.x.2 hostb) on hostb, but this might cause other issues.
A quick look into the SAP documentation for installing Netweaver 7.0 SR3 in a Microsoft Clustered system reveals that a simple step was missed during installation on hostb.
In the document it states "The card of the public network must be displayed before that of the private network. If necessary, change the order in which the cards are listed by using the Move Up and Move Down arrows".

So I check the binding orders on hostb in Control Panel -> Network Connections -> Advanced Settings:

They are the wrong way around. The "Cluster Heartbeat" (Private IP) should be beneath the Public IP ("Local Area Connection" in our example).
The Microsoft article here (http://support.microsoft.com/kb/894564) lists the process to change it and provides alternative solutions (I would recommend to follow the SAP document).

Changing the binding order on Windows Server 2003 does not require a reboot, but the change will not be effective until a reboot is performed.

Detecting & Reducing Space Used For SAP Business Workplace Documents

2011-06-20T19:42:00.002+01:00

Within the SAP Netweaver 7.0 system is a complete e-mail tool that allows users to send/receive electronic documents.
These documents are visible in the SAP Business Workplace (TX: SBWP) inbox/outbox and also in the SAP Connect transmission requests (TX: SOST).

As time goes by and users accumulate documents in their inbox and internal/external mails get sent through SAPConnect, space in the database will be used.

SAP Note: 966854 recommends running RSBCS_REORG to clear down these documents and free the space.
Unfortunately it doesn't tell you how much space is taken up and how much you could get back.

Using the details in SAP Note: 706478 it is possible to check some of the tables at the database level.
Once again, the note doesn't list all tables involved.
Instead, running the RSBCS_REORG report in "Test" mode reveals the full extent of the tables where records will be deleted:

So using this information, the following constructed SQL query will return the current size of the database segments (Oracle) for the selected tables:


SELECT * 
FROM ( 
select sum(s.bytes)/1024/1024 size_mb , 
s.segment_name, 
s.segment_type, 
s.segment_name table_name, 
t.ddtext descr 
from dba_segments s, 
sapsr3.dd02t t 
where s.segment_name in ('BCST_BOR', 
'BCST_CAM', 
'BCST_RE', 
'BCST_SR', 
'SOC3', 
'SOCS', 
'SOCX', 
'SOER', 
'SOES', 
'SOFM', 
'SOOD', 
'SOOS', 
'SOST') 
and s.segment_name = t.tabname 
and ddlanguage='E' 
group by s.segment_name, s.segment_type, s.segment_name, t.ddtext 
union all 
select sum(s.bytes)/1024/1024 size_mb , 
s.segment_name, 
s.segment_type, 
i.table_name, 
t.ddtext descr 
from dba_segments s, 
sapsr3.dd02t t, 
dba_indexes i 
where i.table_name in ( 
'BCST_BOR', 
'BCST_CAM', 
'BCST_RE', 
'BCST_SR', 
'SOC3', 
'SOCS', 
'SOCX', 
'SOER', 
'SOES', 
'SOFM', 
'SOOD', 
'SOOS', 
'SOST') 
and i.table_name = t.tabname 
and i.index_name = s.segment_name 
and ddlanguage='E' 
group by s.segment_name, s.segment_type, i.table_name, t.ddtext 
) t1 
ORDER BY t1.table_name ASC, t1.segment_type DESC;

You may just be able to see that the SOC3 table is using ~7GB of space with an index of 144MB and a lob object of 64KB.

Now if you run the RSBCS_REORG report using "Test" mode, with the settings to remove the documents that are >60 days old, then you can estimate what percentage of records will be removed from table SOC3 and therefore estimate the space saving.

As a rule of thumb, it may be wise to remove the documents from users who have left the company.
Generally the SAP account will be locked, so you can pull the account names using the following SQL query then add them into the RSBCS_REORG report "User" field:

SELECT DISTINCT bname FROM sapsr3.usr02 WHERE uflag =128;

NOTE: When running RSBCS_REORG, it will not remove assigned workflows from the "Workflow" folder (or sub-folders).

You may also consider running the report for the user who runs the SAPConnect background job step. As this user will have the majority of occupied space.

Once you've deleted the records that are not required, the space in the database tables will be freed.
However, this will not release the space to the rest of the database, only the tables from where the records were deleted.
Stay tuned for my up-coming post on how to free the table segment space after you've removed thousands of records from the Oracle database.

Useful SAP Reports

2011-06-20T19:34:00.000+01:00

It's always good to have the exact report handy, just incase the transaction is not in your SAP role:

BTCTRNS1 - Suspend All Jobs For an Upgrade
BTCTRNS2 - Un-Suspend All Jobs For an Upgrade
RDBMIDOC - Create IDocs from change pointers and send.
RBDMOIND - Status conversion of successfull IDoc communication.
RSALDBRG - Reorganise CCMS Alert Database
RSALERTTEST - Test alerts
RSAVGL00 - Table adjustment across clients
RSINCL00 - Extended program list
RSBDCSUB - Release batch-input sessions automaticly
RSCPINST - Check language installation config.
RSTXSCRP - Transport SAPscript files across systems
RGUGBR00 - Substitution/Validation utility
RSBCSRE03 - Clean old SOST records (send deliveries)
RSBTCDEL - Clean the old background job records
RSDBCREO - Clean batch input session log
RSEIDOCA - Active IDoc Monitoring
RSPARAM - Display all instance parameters
RSPO0041 - Removing old spooling objects
RSSNAPDL - Clean the old ABAP error dumps
RSSLG200 - Remove expired application logs (SLG1).
RSSLGK90 - Delete application logs created before the specified date.
RSSOREST - Clean old SOST records (send deliveries)
RSPO1041 - Cross-Client Spool Request Cleanup.
RSPOR_SETUP - BW / BI Configuration Consistency Check
RSTXICON - List all icons in the system
RSTRFCEG - Consistency check of outbound queues (RFC/trfc/qrfc).
RSUSR002 - Roles by complex selection (new)
RSUSR003 - Check the passwords of users SAP* and DDIC in all clients
RSUSR006 - List users last login
RSUSR020 - Profiles by complex selection
RSUSR050 - Compare users
RSUSR070 - Roles by complex selection
RSUSR200 - List users by login date.
RSWUWFML2 - Delivers work items via users e-mail address.
RSWWWIDE - Remove work items and related sub-items.
RSXMB_CANCEL_MESSAGES - Cancel messages in error.
RSXMB_CHECK_MSG_QUEUE - Check messages in queue.
SSF_ALERT_CERTEXPIRE - Certificate expiry check.

Oracle vs SQL Server - I like it!

2011-06-14T19:39:00.000+01:00

I found this today http://www.oaktable.net/sites/default/files/deathmatch.pdf
It shows a good comparison of basic syntactical differences in SQL Server and Oracle when looking to compare performance / features.
I especially like the use of blockrecover in RMAN towards the end. Nice.

Just take a look at the tags for it's hosting blog page and it's obvious which one will win without even reading the slide.

Useful SAP Transactions

2011-06-14T18:34:00.000+01:00

Here are a collection of SAP transactions that I find useful:

AISUSER - Solution Manager maintain S-user for maint-opt.
AL08 - All Users Logged On
AL13 - Display Shared Memory
ALRTCATDEF - Alert category definitions
ALRTDISP - Alert display
ALTINBOX - Alert inbox
BDLSS - Shows all items associated with the logical system name.
BDM7 - ALE Audit Statistics
BD10 - Send Material Master
BD12 - Send Customer Master
BD14 - Send Vendor Master
BD22 - Reorganise (Delete) Change Pointers
BD87 - ALE Message Monitor
BF24 - Create Product (User exits)
BF34 - Register user exit event to function module.
CG3Z - Transfer file from frontend.
CMOD - List user exits
DB02 - Database Tables/Tablespace Statistics
DB13 - Database Planning Calendar
DB14 - Database Operations Log
DB26 - Database Parameters
DBCO - External Database Connections in SAP.
F.10 - GL Accounts
F110 - BACS Payments
FBL1N - List Invoice Documents
IDX1 - Port Maintenance In IDoc Adapter
IDX2 - Meta Data Overview in IDoc Adapter
IDX5 - XI IDoc search.
LISTSCHEMA - BW Lists underlying tables for InfoCubes.
MMRV - Show current posting period
OAAD - Archivelink Administration of Stored Documents
OAER - Business Document Navigator
OAM1 - ArchiveLink Monitor
OAC0 - Define content servers
OAWD - Archivelink Document Storage.
OB29 - Define Posting Periods (See also SPRO -> Fianncial Accounting -> Financial Accounting Global Settings -> Fiscal Year)
OMJ3 - Printer Determination by Plant.
OMJR - Printer Determination by Output Type.
OS01 - Ping servers
ORA_PERF - Analyse tables, delete statistics.
OY18 - Reports for table logging.
PA30 - Maintain HR Master
PFAL - HR ALE Transfer of Master Data.
PFCG - Role Maintenance
PFTC - Workflow task maintenance.
PFTC_CHG - Change a workflow task.
PFUD - Profile consistency (mass user comparison) check (schedule job).
RMPS_SET_SUBSTITUTE - Set Workflow substitute.
PPOME - HR Mini Master (Personnel Management)
PPOMW - HR Mini Master (Organisational Management)
RRMX - Launch BEX Analyser
RSA7 - BW Delta Queue Maintenance
RSM37 - Display background jobs with parameters.
RSRDA - Stop all daemons for real-time data acquisition in BW.
RZ01 - Job Scheduling Monitor.
RZ03 - Display/ Manully Switch Operation Mode (start/stop instance).
RZ04 - Maintain Operations Modes
RZ12 - Maintain RFC groups.
RZ21 - Monitoring Properties
RZ70 - SLD Administration
SA38 - ABAP Reporting
SAPL0ARC - Archiving Customising For Object.
SCCL - Local Client Copy.
SCEM - CATT initial screen.
SCU3 - Analyse table logging logs
SDCCN - Service Data Control Centre
SECR - AIS (Audit Information System)
SE03 - System Change Option
SE11 - Data Dictionary Display
SE21 - Package Builder.
SE61 - Change display screen text.
SE93 - Transaction lookup.
SE37 - ABAP Function Modules
SE54 - Generate Table Maintenance.
SBIW - Maintain Business Information Warehouse Data Sources
SCU0 - Customizing Cross System Viewer
SLDCHECK - Check status of the SLD
SLDAPICUST - SLD API Customizing
SLG1 - Analyse application log
SLG2 - Application Logs Delete
SMLG - Logon groups.
SMGW - Gateway monitoring
SMICM - ICM Monitor
SMQ1 - qRFC Monitor (Outbound Queue)
SMQ2 - qRFC Monitor (Inbound Queue)
SMQ3 - qRFC Monitor (Saved E-Queue)
SMQE - qRFC Administration
SMSY - SOLMAN - Landscape Directory
SMSY_SETUP - Solution Directory Setup
SMX - Own Jobs
SM18 - Security Audit Log Reorganisation
SM19 - Security Audit Log Configuration
SM20 - Security Audit Log Analysis
SM35 - Batch Input Log (LSMW etc)
SM58 - Asynchornous RFC Error Log
SM63 - Maintain Operation Modes Calendar
SM64 - Background Jobs Events.
SM65 - Background processing check.
SM66 - All processes across system.
SM69 - Maintain External Commands
SO36 - Automatic System Forwarding for E-Mails
SOA0 - ArchiveLink Document Types.
SOLMAN_WORKCENTER - Solution Manager Maintenance Optimizer.
SP12 - TemSe administration
SSAA - System Administrator Assistant
ST02 - SAP Buffer Tuning
ST03 - Global Workload Monitor
ST04 - Database Statistics
ST06 - Operating System Monitor
SUCU - Table / View Authorisations.
SUGR - User Group Maintenance
SWDD - Workflow Builder.
SWDM - Workflow Builder
SWI1 - Workflow Instance Log
SWE2 - Workflow linkages
SWEL - Display Event Trace
SWELS - Switch on/off event trace
SWEQADM - Event Queue Administration
SWEQBROWSER - Event queue browser
SWO1 - Business Object Builder
SWU0 - Workflow event simulation
SWU3 - Workflow customizing
SWUS - Test Workflow
SWWL - Delete workflow
SXMB_MONI - Integration Engine Monitoring
SXMB_MONI_BPE - Process Engine Monitoring
SXMB_IFR - Call Integration Builder
SXMB_ADM - Integration Engine Admin
SXI_CACHE - XI Directory Cache
SXI_MONITOR - XI Message Monitoring
TU02 - Change parameters
USMM - User Measurement Statistics.
WE02 - IDoc Display
WE07 - IDoc Monitoring
WE05 - IDoc List.
WE09 - IDoc Search.
WE11 - Delete IDocs
WE19 - IDoc Test Tool
WE20 - IDoc Partner Profiles
WE40 - Idoc administration setup
WE41 - Outbound Process codes.
WE42 - Inbound process codes.
WE46 - IDoc Admin
WE60 - IDoc Types Documentation
XK02 - Changer Vendor Details

Useful SAP Tables

2011-06-14T18:10:00.000+01:00

During my time as a SAP BASIS administrator, I have accumulated a number of useful database tables.

ADR6 - User Address Data (cross with USR21)
AGR_1250 - Authorization data for the activity group
AGR_USERS - Assignment of roles to users
AGR_SELECT - Assignment of roles to Tcode
AGR_PROF - Profile name for role
AGR_DEFINE - Role definition
BDCPS - Change Pointers
DDLOG - Table buffer changes for sync between dialog instances.
DD02T - SAP Tables and descriptions.
PA0105 - HR Communications InfoType (includes USERID to PERNO).
SH_PRIN - Printer list
TADIR - Repository object table.
TBRG - All Authorisation Groups.
TDDAT - Tables to Authorisation Groups.
TBTCP - Background Job Steps (see field AUTHCKNAM for job user)
TOLE - OLE Link table
USR02 - User passwords.
USR01 - Users.
USR04 - User Master Authorisations.
USR10 - Authorisation Profiles.
UST04 - Users to profiles.
TDEVC - Package table.
TSP03D - Printer Output Devices
TST03 - Spool requests
TSTC - Transactions
TVARV - Variant Variables
VARI - Variants

Use of Oracle AWR / ASH leading to bad coding?

2011-06-13T18:37:00.000+01:00

I had a brief email exchange with another Oracle guru the other day.
He suggested that the quality of Oracle coding in PL*SQL and Plain Jane (www.medicaltextbooksrevealed.com/blog/2010/02/plain-jane/) SQL had gone down hill.

This could be attributed to two factors:
1, The level of coding experience has dropped. Older more experienced coders have filled into the new architect roles and the void is being filled quickly by newer in-experienced coders.
2, The rigour with which debugging, testing and tuning is performed has become somewhat lax (www.thefreedictionary.com/lax ) because there's just no emphasis on the developers to tune their code when the DBA has such great tools to do it for them.

Is it possible that the use of the additionally licensed tools such as AWR (Automatic Workload Repository) and ASH (Active Session History) introduced in Oracle 10g, have provided an easy mechanism for DBAs to seek out better performance.
I don't think these tools are just for DBAs, but the way they are marketed makes me feel they are pushed that way.

Running Oracle (Windows vs UNIX/Linux)

2011-06-12T11:08:00.000+01:00

For most of my IT career I've been using UNIX/Linux (let's call this ULix to save my fingers) to run Oracle.
Humans are creatures of habit (like cats http://www.simonscat.com/) and so why would I want to change this.
Running Oracle on ULix is a measured quantity. It works, it's reliable, you can tune it and then you can really tune it.

Whilst browsing my book library I came across a book I've had for years:
"Configuring & Tuning Databases on the Solaris Platform" by Allan N. Packer
If you have ever wondered what the possibilities are in tuning an Oracle system on ULix, then read this book. It might be old, but it's a good one, and it establishes all the basic principles of tuning.

Now if we were to consider running Oracle on Windows (http://www.dba-oracle.com/art_builder_linux_oracle.htm), then out-of-the-box (http://www.ukrapmusic.com/mixtapes/717-rootz-sparka-presents-out-of-the-box) it would work perfectly fine.

Microsoft have made vast improvements to the monitoring/tuning capabilities in newer Windows Server versions, but I don't think this can compare to the flexibility of monitoring/tuning in ULix.
I find it very difficult to profile a database on Windows.
Think about it, where can I see the individual shadow processes (http://cavyspirit.deviantart.com/art/Don-t-Mess-with-Shadow-Process-194711067) at the OS level?

On Tanel Poder's company's website, there's a great whitepaper "Understanding LGWR, Log File Sync Waits and Commit Performance".
In this whitepaper, Tanel mentions monitoring and tuning the Oracle log writer (LGWR) process to get better CPU time. I thought to myself, how would someone do this on Windows?

I really don't think you can.
To summarise: I like Windows, it can run Oracle RDBMS perfectly fine, but it's not as flexible as running on ULix.

Basic Performance Tuning Guide - SAP NetWeaver 7.0 - Part I - Finding Slow Running Programs

2011-06-10T18:06:00.003+01:00

For me, as a BASIS administrator, it's the most fun part of the job. Performance tuning!
I enjoy making things faster, more efficient. It seems German (http://www.urbandictionary.com/define.php?term=german%20efficiency) in some ways, and I guess that I must have a little OCD in me, but it's rewarding.

I can't believe how simple performance tuning can be sometimes. If I write the process down it even seems simple:
1, Find slow running programs.
2, Analyse why they are slow or poor performers.
3, Tune them.
4, Cup of tea.
5, See them fly.

For those Lean Six Sigma followers, this fits in with DMAIC (http://en.wikipedia.org/wiki/Six_Sigma) quite nicely.
I bet it would even look good on a fish bone diagram (http://www.leansixsigmasource.com/2009/04/15/how-to-construct-a-fishbone-diagram/) (sorry, I did mean "cause & effect diagram" honest).

I'm going to break these steps down.

Let's start with number 1:Find Slow Running Programs
So you have your new SAP system all installed.
The consultancy that briskly implemented it and have now rolled-off, have left it running beautifully (http://www.zdnet.com/blog/projectfailures/san-diego-fires-axon-over-erp-implementation-problems/1960).

Why wouldn't they.
As an operations specialist (http://darrylgriffiths.blogspot.com/2011/06/are-you-sure-im-not-consultant-not.html), you get called in.
Of course, you're an expert and you know that profiling a system is one of those tasks that needs to be done to help you troubleshoot performance problems. But being an expert, you also know that there are bigger fish to fry after a fresh implementation of anything.
So we assume that it is not plausible to use a comparative profile of before and after performance.

Let's assume that a report/transaction has been reported as running slowly today by a single user (one helpdesk ticket http://lukasland.com/humour/Computing-General/Help-Desk-Hell.htm).
You're on the case and you can look at the problem right now.
What's you're first call?
Wrong! Crying won't get you anywhere!
We need to see if the problem is caused by the system in general, the user (sometimes you do get them) or something else.
I always use the following methods:

1, Check SM66 (global work process overview). It's easier than checking SM50, then remembering that you're on a system with Apps servers (additional dialog instances).
What's currently running?
Look specifically at the "Time" and "Action" columns:

If there are any long running programs (large "Time" values) in the list, what "Action" are they performing? Could it affect the report that the user has identified as being slow?

2, Check SM21 (System Log) always good to keep abreast of potential system wide issues! Brownie points (http://en.wikipedia.org/wiki/Brownie_points) from the big boss for being so quick!
Any problems in here will also mention short dumps in ST22, so no need to check ST22 unless it's in the system log.

3, Check STAD (SAP Workload - Single Business Transactions). This shows specific transactions that the user has completed in the <n>hours (after this they get aggregated and shown in ST03N/ST03G depending on your collector settings).
Use the "Show Business Transaction Tots" option, it's easier to see the wood for the trees (http://www.woodfortrees.org/).
Enter the username into the filter and adjust the time period to search for (when did the problem happen?).

Great! You can see the individual transactions performed by the user (even ITS based services e.g. BSPs will be shown):

TIP: Try it without the filter on a small system to see what you can actually get. It's quite a lot of information. RFCs, background jobs, HTTP.

Check out the "Response Time", "CPU time" and "DB req time" columns.
See if any specific transaction stands out above the rest for these three values.

If you expand the "+" folder on the very left, you will see each dialog step (screen) that the user has been through:

The time gap between each screen is the previous screens response time + roll-time + front-end time + "Think Time".

So now you've found the slow program, you need to analyse it.
Part II of this guide shows how to read and decipher the single record statistics using STAD.
Part III of this guide shows how to perform an SQL Trace.

Are you sure I'm not a consultant

2011-06-10T16:30:00.002+01:00

I'd like to mention the growing (in my mind anyway) disparity between an operational support person and a consultant.
Having spent some time in contact with various recruitment agencies, I can understand how single words specified on a requirements brief from the client must be followed as accurately as possible.
The client, after all, wants the perfect candidate don't they?!

I've worked on both sides of the coin you see.
I started off as an apprentice in an IT department in the late nineties.
Customer satisfaction is key when you're in an IT department working the helpdesk. Even if you're second line (which infers a military comparison, doesn't it? http://www.google.co.uk/search?hl=en&biw=1385&bih=862&q=second+line+of+defence&aq=f&aqi=g7g-v2g-sv1&aql=f&oq=), you may be rated on your comms skills, how efficient you were and how accurate (first time fix).
So I can understand that supporting something that is already installed and working would be viewed as operational support and not consultancy.

I've also worked for a consultancy, quite a big one. I wasn't called a consultant, but if you work for a consultancy you must be? right?

But wait, doesn't an internal IT department upgrade stuff? performance tune? generate ideas?
Are we saying that IT departments just do these things without talking to the business side. I think not. I remember many meetings where new ideas had been conceived in the IT department and were conveyed to the business.
Is this not consulting? Advising what would be best for the business and the technology. I don't know any IT department today that would contemplate changing anything without consulting with the business first.

So if we're saying that IT departments do consult, the difference between a consultant and operational support must be in the *type* of consulting right?
You could argue that a consultant has more "soft" skills. Um, like a salesman perhaps?
Or would you argue that a consultant may have broader knowledge. Possibly. More of a fish eye? (http://en.wikipedia.org/wiki/Jack_of_all_trades,_master_of_none).
Hmm, I can't see how this is different either. You are either very good at these things (IT skills) or you're not.
The only difference is when you consider a company wanting change, but not having the skills internally. Wouldn't that force them to use an external consultancy? Someone *else* to consult with.

I think a "consultant" today is someone who has managed to escape the IT department. He's a free radical (http://en.wikipedia.org/wiki/Radical_(chemistry)). But without the other elements he's not got anything to bond with. He's able to offer unbounded ideas and generally gets the biggest budget (and sometimes some training), but he's not able to evolve into anything more complex. He came, he saw, he implemented, he left. There's a bigger side to this implementation lark, and it happens to be operational support!
Hurrah to the helpdesk operators, the backup tape changers and the email mailbox size limiting people (http://www.winserverkb.com/Uwe/Forum.aspx/exchange-admin/5313/Why-limit-people-s-mailbox-size).

Some of the best, most talented people I've ever worked with have been working in operational support. Fantastic, creative ideas ingeniously devised in the smallest of rooms at the eleventh hour (http://en.wikipedia.org/wiki/The_Eleventh_Hour) to stop the world ending (or the system from crashing) on a system that was implemented 20+ years ago. How can these people not be consultants? Maybe they're too honest ;-)