Program RSUSR003 reports security violation

I got this alarm on a system recently. And it really annoyed me, because I'd not seen this one before.

So I looked at the source of the alarm. It was a syslog message, and it was pointing to a user and a report. So presumably, running the report would generate the error. I easily replicated this in another environment, by simply running the report.

So I contacted the user and asked what was this all about, and apparently, during an audit someone suggested running this report on a regular basis. And the report is nice. It shows the status of default users, the settings of the passwordlength and complexity etc.

The only problem is that if our setup varies from how the report thinks the world should look, apparently I will get this error in my syslog. And as I cant really go around enforcing a different password policy on the SAP system than on all the other systems, I had to make the error go away.

So setting out to do this, I looked again in the syslog. The error code was E03, meaning in area E0 subid 3. I opened the table TSL1T and looked at area E0.

So it would appear that this particular error only occurs "when expected", as it is called from within reports. So my solution was to simply remove the E03 from the syslog filter in my solution manager alerting... However, there's no "where used" function for syslog messages, so I might be wrong about the severity, but I doubt it.

Automating windows tasks for SAP systems

It always bothered me that SAP systems are so horribly bad at cleaning up after themselves. Especially anything that runs on Java, windows or is a thridparty plugin/adaptor/app/whatever.And given that I in many cases dont even have an SAP/ABAP backend to program a cleanup in, I had to make due with regular windows expressions and commands.

So the other day, when I had a javabased plugin, running on a windows system that was creating about 500 logfiles/day, I figured, enough is enough, I dont WANT anymore alerts on semifull disks, bad performance or requests for defrag etc. etc. caused by millions of small files taking up all the space on the system. 

So I decided to create a small script that would clean up all of the subfolders that contained the offending files.

I created a file called "cleanup.cmd" and placed in on the servers desktop. 

In the file, I put a lot of lines that would uniquely identify the files that I was looking for, lines like this:

forfiles /P "<absolute_path_without_wildcards>" /S /M <mask> /D -<age> /C "cmd /c echo @path"

Once each line selected the stuff I wanted from the directories (and with the /S also subdirectories) I wanted, I replaced the  "cmd /c echo @path" with "cmd /c del @path" and saved the file.

Then it's just a matter of scheduling it in the windows scheduler to run on a monthly basis.

At some point in time, I'll take the time to create a script that'll be able to accept a parameter or two so that it'll clean up each of the different types of systems I manage, that does not clean up all of its own work- and logfiles (XI, gw connector, charm, TREX, livereorg, redwood, BPC, SBOP,  sapconsole etc....). It would be so nice if I could just maintain ONE script.... Oh, the hazzards of automation :D

Dependent module libclntsh.a(shr.o) could not be loaded

I've found this error "Dependent module libclntsh.a(shr.o) could not be loaded" on many occasions, but usually only when the Oracle client or version in general had been changed. And often in conjuction with a faling "magic number". Magic being somewhat non-descriptive, when they only mean 32 or 64 bit.... But nevermind teasing the developers ;)

Usually, it's caused by the incorrect setting of the LIBPATH (for AIX), SHLIB_PATH (for HP-UX) or similar library path.
On occasion I've even had to relink my Oracle to get the libraries running correctly again.

So let me just set things straight:
On an SAP system, the user ORA<SID> needs to have the vaiable set to include <path to SYS/exe/run> and /oracle/<SID>/<version>/lib (and remember to differentiate between 32 and 64 bit libraries, if you're using both).
The user <SID>ADM needs the varibale to point to /oracle/client/<version>/instantclient (assuming you're on Oracle version 10 or newer and thus using the instantclient)

The other day, however, I did notice problems that neither spelling errors, incorreclty set variable or even a relink couldn't fix. And it took me a while to find the cause of the problem. The system had been unicode converted, oracle upgraded, had a new client installed, and even got an SAP enhancement package installed. So there were a lot of stuff that could have gone wrong. But for some reason a few (but not all) of the systems would throw the error "Dependent module libclntsh.a(shr.o) could not be loaded" when I tried running brconnect, brspace or other similar calls. Whenever the caller was the <SID>ADM it wouldnt work....

I struggled for a while, especially because the shr.o reference (rather than shr_64.o) would seem to indicate that a 32 bit module was being attempted loaded. And since I was running Oracle 11.2 64 bit, then ofcourse there'd be no 32 bit library available. But I followed the traces around, and finally I found the culprit. In the instantclient directory, there was no softlink named instantclient. There was only the instantclient_<version> directory, and the LIBPATH pointed directly to this. That meant that any test I would subject it to would pass, but for some reason there must be someting hardcoded in the SAP kernel that points to the instastantclient directory/softlink despite setting the variable LIBPATH.....

I quickly created a new softlink, and lo and behold. Everything was working like a charm again. On the upside I got to clean up all the old .dbenv files and obsolete environment variables on the failing systems ;)

Incorrect Timezone for the US after DST changed

One can wonder why SAP just doesn't put this information into a support package or something..... But it's ever a problem after The last sunday of October, when most of the world with DST changes time. Because since 2007, the US been changing on the first sunday of November... EST changes on another date than CET, GMT etc.

If you're system is running UTC, the difference drops by one hour (to normal time) whenever DST ends. It's just not the same day everywhere in the world...

When you're logged onto an SAP system, this can then give you some issues, as SAP still thinks that the US changes same as the rest of the world, because transaction STZBC hasn't been updated with the correct information

So for all lazy sysadmins getting stuck with problems about the US being one hour "off key" in november.... Tell your developers to maintan the variable DST settings in SAP... After all, the system is doing exactly what it's supposed to ;)

Resetting the OPS$ Mechanism

It always bothered me that not only is it possible to screw up the OPS$ mechanism in SAP, when running Unix/Oracle flavor. It's also quite easy. And then there's one thing wrong, then another, and the BRTOOLS just dont really cut it when it comes to fixing the errors.... It's always manual labor. And especially whenever someone is doing a system restore, homogenous system copy or similar, there's always problems afterwards.

So I've created a little script that resets all of it for me (and it also creates an ops$ mechanism for the Oracle user so that I can run backups with "-u /" istead of specifying the password, or storing the password in a plain text file).

As ora<SID> I go to sqlplus / as sysdba and issue the commands:

drop user "OPS$<old_sid>adm" cascade; #In case of homogenous system copies, remove incorrect SIDs
drop user "OPS$ora<old_sid>" cascade; #In case of homogenous system copies, remove incorrect SIDs
drop user "OPS$<new_sid>adm" cascade; #Regardless of error, remove the current SID too
drop user "OPS$ora<new_sid>" cascade; #Regardless of error, remove the current SID too
drop table SAPSR3.sapuser;   ## possibly other schemaowner, like SAP<SID>, <SID>R3, SAPR3
drop public synonym sapuser;
create user "OPS$<new_sid>adm" default tablespace <<usually psapsr3usr>> temporary tablespace <<usually psaptemp>> identified externally;
grant connect, resource to "OPS$<new_sid>adm";
create table "OPS$<new_sid>adm".sapuser ( USERID VARCHAR2(256), PASSWD VARCHAR2 (256));
insert into "OPS$<new_sid>adm".sapuser  values ('SAPSR3', 'sap'); #NOTE that the username and password ofcourse must reflect the schemaowner and current password
alter user sapsr3 identified by sap;  #and here the password must be set again
create public synonym sapuser for OPS$<new_sid>adm.sapuser;
create USER "OPS$ora<new_sid>" default tablespace system temporary tablespace <<usually PSAPTEMP>> identified externally;
grant DBA, CONNECT to "OPS$ora<new_sid>";
grant select, update on sapuser to "OPS$<new_sid>adm";

Test that this works by logging in as <sid>adm and issuing the command

R3trans -x 

Or as ora<sid>:

sqlplus / as sysdba

connect /

exit

This little snippet of code has saved me a lot of time once in a while. Sometimes I even think about making it remote executable, so I dont have to actually log on to a system whenever someone calls me with transport issues or other funny stuff following a bungled homogenous system copy.

Scripting finding hidden columns

Any good sysadmin, is a lazy sysadmin. The less work we have, the better the systems are running.
So the other day I was tasked with identifying hidden columns in an Oracle database. I decided to automate the process. It was quite simple.

I create a directory <mydir> for holding the script

I then created an sql script find_hidden.sql in the directory containing the following lines of code:

SET PAGESIZE 1000
SET LINESIZE 200
COLUMN owner FORMAT a10
COLUMN hidden FORMAT a8
spool /<mydir>/found_hidden.txt
select
  a.owner, a.table_name, a.hidden_column hidden,sum(b.bytes)/(1024*1024) size_MB
  from all_tab_cols a
       join dba_segments b on a.table_name = b.segment_name
where a.hidden_column = 'YES' and a.owner != 'SYS' and b.segment_type='TABLE'
group by a.owner,a.table_name,a.hidden_column;
spool off
exit

The astoute reader will recognize the script from my earlier writings, with the catch that I'm spooling the results to a file.

This creates a file with the data. Now for creating a script that will mail me the file, if I need it.....
So I create a shellscript mail_hidden.sh, with the following lines of code:

#!/bin/sh
sqlplus / as sysdba @/<mydir>/find_hidden.sql
myfilesize=$(ls -lart /<mydir>/found_hidden.txt | awk '{print $5}')
if [ $myfilesize > 20 ];
then
cat /<mydir>/found_hidden.txt | mailx -s "Found hidden columns in Name of system" <my_mail_adress>
fi
rm /<mydir>/found_hidden.txt

Note that the script will determine if the filesize of the output file is lager than a few bytes..."no rows selected" statement is about 19 bytes ;)
So I just run this on my database(s) every month or so.

Next step will be to automatically run an online reorg of the objects during low activity periods. Because I know I dont want to spend my weekends doing that for the forseeable future.

Does not match Component version

One of my clients have a SAP GRC Access Control application installed on their system.

But after upgrading the SAP system to EHP6, I ran into a problem with the transport system suddenly showing the status of transports from the dev system as "Does not match component version". Upon checking, I found the cause to be that the VIRSANH 530_700 component wasn't a part of the test system I'm trying to import the transports into.
Apparently, the customer only wants this component on the development system, as they only use the functionality in the development system.

So, without the option to install the component on the other systems to get the component versions identical, I had to set the parameter SP_TRANS_SYNC = OFF in the transport tool. The major downside is that this will ignore ALL component version problems, not just the ones affecting the GRC access control.