Using Google Chrome?

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

Recent Posts

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.