Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Tuesday, December 20, 2011

SAP note 830576 - PGA_AGGREGATE_TARGET on Oracle 10gR2

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).

Thursday, December 15, 2011

SAP Sender Address for Communication Method

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.

Tuesday, December 13, 2011

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

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.

Tuesday, December 06, 2011

Monday, December 05, 2011

Transporting SAP User Groups

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.

Saturday, December 03, 2011

Android 2.1 Calendar Sync Force Close


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...

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".

Tuesday, November 29, 2011

Logging in PL/SQL

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:

Saturday, November 26, 2011

Check if Hyperthreading is enabled in HP-UX 11iv3

UPDATED: Inaccessible links to HP.com have been removed.  I couldn't find the original PDF docs anywhere online any more, so I've just removed the links.

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.

Sunday, November 20, 2011

Oracle Tracing & Interpreting Traces - Notes

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]".

Friday, November 11, 2011

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

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”

Saturday, November 05, 2011

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

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

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.

Tuesday, November 01, 2011

Oracle Index Rows Per Leaf Block

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.

Saturday, October 29, 2011

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

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.

Sunday, October 23, 2011

Gathering Oracle Statistics - the SAP way

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 */
set linesize 400
col ADDITIONAL_INFO FORMAT A100
col LOG_DATE FORMAT a20
col JOB_NAME FORMAT A20
col RUN_DURATION FORMAT A25
col ACTUAL_START_DATE FORMAT A40
SELECT log_id,
actual_start_date,
run_duration,
job_name,
status,
log_date,
additional_info
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;

Saturday, October 22, 2011

Oracle Core: Essential Internals for Troubleshooting

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

Thursday, October 20, 2011

Connecting SAP Netweaver ABAP Stack To SAP SLD

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
Transacion: RZ70
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

Saturday, October 15, 2011

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

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

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.

Friday, October 07, 2011

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

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

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...

Wednesday, October 05, 2011

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

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

Monday, October 03, 2011

Oracle Storage Sub-system Load Stress Testing

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.

Monday, September 26, 2011

Flushing Cursor SQL Plan Out of Library Cache

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.

Friday, September 23, 2011

Enable SAP* in Netweaver 7.0

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*.

Monday, September 19, 2011

SAP on HP-UX

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

Saturday, September 10, 2011

SAP User Groups

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.

Monday, September 05, 2011

Monitoring Index Usage In Oracle 10G Using V$OBJECT_USAGE

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#;

Thursday, September 01, 2011

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

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):

SAP STAD output DB request 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:

STAD output hit list

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

STAD output hit list output

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:

STAD output hit list sorting

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

STAD output hit list call point in ABAP

STAD output hit list call point in ABAP

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:

STAD output hit list tables used

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:

STAD output hit list call hierarchy

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.

Tuesday, August 30, 2011

Lock/Unlock OEM Grid Control Accounts From Excel Macro


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