Using Google Chrome?

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

Recent Posts

Thursday, January 26, 2012

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

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

This is where V$SYSAUX_OCCUPANTS comes in.

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

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


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

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

Tuesday, January 17, 2012

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

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

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

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

Friday, January 13, 2012

Populate User Groups into SU01 Authorization Group

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

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

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

set serveroutput on size 1000;

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

FOR ug IN c_ug LOOP

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

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

END LOOP;

END;

Monday, January 09, 2012

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

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

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

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

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

UPDATE: This is a bug.  See here.

Thursday, January 05, 2012

SAP PI Channel Stopped by Administrative Task

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

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

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

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

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

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

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