Using Google Chrome?

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

Recent Posts

Monday, April 30, 2012

Oracle AUD$ TIMESTAMP# from 9i to 10gR2

You select TIMESTAMP# from AUD$ in an Oracle 10gR2 or 11g database, but you seem to be missing the most recent audit information.
Your DB was upgraded at some point from 9i or earlier.

SQL> select min(TIMESTAMP#) from aud$;
MIN(TIMES
---------
04-MAR-04

SQL> select max(TIMESTAMP#) from aud$;
MAX(TIMES
---------
01-JUN-10


When you select from the DBA_AUDIT_TRAIL view, you see all the records:

SQL> select max(timestamp) from dba_audit_trail;
MAX(TIMES
---------
26-APR-12 07.48.13.242725 AM


The reason is that the TIMESTAMP# column in AUD$ was made obsolete in 10.1.0.5, but it still contains the old data.
The old data was migrated into the NTIMESTAMP# column but it will be missing the additional sub-second timings and the timezone:

SQL> select min(NTIMESTAMP#) from aud$;
MIN(NTIMESTAMP#)
---------------------------------------------------------------------------
04-MAR-04 03.43.28.000000 PM


You should use the NTIMESTAMP# column in AUD$ to see the later audit records post 10g upgrade.
See MYOS note 427296.1 for more detail on this.

The DBA_AUDIT_TRAIL view uses the new NTIMESTAMP# column:

SQL> select max(timestamp) from dba_audit_trail ;
MAX(TIMES
---------
26-APR-12 07.48.13.242725 AM


SQL> select min(timestamp) from dba_audit_trail;
MIN(TIMES
---------
04-MAR-04 03.43.28.000000 PM

Thursday, April 26, 2012

Windows Batch File to Install Windows Task


@cls
@rem ######################################################################
@rem # Script:  install_task.bat
@rem # Params:  none.
@rem # Desc:    This script installs the task that will run the
@rem #           start.bat on the required schedule.  WinXP or Win2k3
@rem # Author:  D.Griffiths
@rem # Version: 1.0
@rem # History:
@rem #          09-June-2008 - DMG - Created.
@rem #
@rem ######################################################################

@set TN=My_TaskName
@set DAYS=MON,TUE,WED,THU,FRI,SAT
@set TR=C:\start.bat
@set ST=05:50:00

@set /P conf=Press return to install task "%TN%":

@echo Tasks currently scheduled...
@schtasks /Query /FO TABLE

@echo.
@echo Removing existing task if already present...
@schtasks /Delete /TN %TN% /F

@echo.
@echo Installing new task "%TN%"...
@schtasks /Create /RU SYSTEM /SC WEEKLY /D %DAYS% /TN %TN% /TR %TR% /ST %ST%
@IF ERRORLEVEL 1 goto invalid_task

@echo.
@echo Task "%TN%" now scheduled.
@echo.
@echo Tasks currently scheduled...
@schtasks /Query /FO TABLE
@echo.
@echo.
@set /P conf=Press return to exit.
@exit

:invalid_task
@echo.
@echo.
@echo ERROR: There was a problem installing the task.
@echo ERROR: Please try again or check the command syntax.
@set /P conf=Press return to exit.

Monday, April 23, 2012

SAP PI RSXMB_DELETE_MESSAGES - Copy job failed

Whilst implementing a process for the “switch” deletion of XML messages from the SAP PI persistence layer you may end up, at some point, in a situation where the deletion job simply fails to run.

The contents of the job log state: “Repeat terminated copy job first”. No further information is displayed and there are no errors in the system log (SM21).

By executing the ABAP program RSXMB_DELETE_MESSAGES (same as the delete job step) in SE38, you can then double click the error at the bottom of the screen:



Finally you will get an explanation and a process for resolution:

Call transaction SXMB_MONI and choose Job Overview. Repeat the incomplete job. If the job cannot be completed due to database errors, first correct the database errors, and then repeat the job again.”.

It seems the jobs displayed using the "Job Overview" in SXMB_MONI is not simply a view of SM37 jobs, but a view of the underlying job control and enqueue function specifically for the messaging framework.

Tuesday, April 17, 2012

SAP PI - Persistence Layer Deletion - Oracle Stats

When configuring persistence layer deletion in SAP PI 7.0, you should be aware that the very first time you enable the “switch” procedure and execute the delete jobs, the new tables will be created in the database.

These tables (SXMS*2) will NOT have any database statistics on them.
Therefore, once the switch procedure is completed, until your stats gathering job is executed in DB13, your PI system may run very slow indeed!

Either schedule a one-off stats gathering after the very first “switch”, or schedule the stats gathering frequently (e.g. daily).

Sunday, April 15, 2012

Oracle 10.2 Data Types (oacdty)

Oracle 10.2 data types (oacdty) for use when you're (bravely) exploring an Oracle trace:

1     VARCHAR2 or NVARCHAR2
2     NUMBER
8     LONG
9     NCHAR VARYING, VARCHAR
12   DATE
23   RAW
24   LONG RAW
25   LONG UB2
26   LONG SB4
58   ANYDATA
69   ROWID
96   CHAR or NCHAR
100 BINARY FLOAT
101 BINARY DOUBLE
102 REF CURSOR
104 UROWID
105 MLSLABEL
106 MLSLABEL
111 XMLTYPE (TABLE or REF)
112 CLOB or NCLOB
113 BLOB
114 BFILE
121 TYPE (USER-DEFINED)
122 TYPE (TABLE OF RECORD)
123 TYPE (VARRAY)
178 TIME
179 TIME WITH TIME ZONE
180 TIMESTAMP
181 TIMESTAMP WITH TIME ZONE
182 INTERVAL YEAR TO MONTH
183 INTERVAL DAY TO SECOND
208 UROWID
231 TIMESTAMP WITH LOCAL TIME ZONE

Thursday, April 12, 2012

Negative Values in V$TEMP_SPACE_HEADER

Whilst trying to figure out a reliable method of monitoring temporary tablespace usage I came across this problem.
The v$temp_space_header bytes_used column was displaying negative values.

Metalink has document 467435.1 which states quite simply that in 10.2.0.3 and later, the temp file header(s) may have been corrupted.

The solution, drop and re-create the temp file.