SAP is Accessable but Users are not able to perform transaction

[Former Member]

Dear All,

We have found in SAP as System is Accessable but Users are not able to perform transaction .

please check the below error log:

16.02.2012 10:58:57 pssapbw2_BP3_02 ALEREMOTE 590 C MESSAGE_TYPE_X

16.02.2012 10:51:43 pssapbw1_BP3_00 ALEREMOTE 590 C DBIF_RSQL_SQL_ERROR CX_SY_OPEN_SQL_DB

16.02.2012 10:43:44 pssapbw3_BP3_03 MEHRA1U 590 C UNCAUGHT_EXCEPTION CX_SDB_ORA_PROGRAM_ERROR

16.02.2012 10:32:31 pssapbw3_BP3_03 S10034198 590 C MESSAGE_TYPE_X

16.02.2012 10:30:51 pssapbw3_BP3_03 S10009432 590 C MESSAGE_TYPE_X

16.02.2012 10:17:24 pssapbw2_BP3_02 S5002705 590 C MESSAGE_TYPE_X

16.02.2012 10:14:48 pssapbw1_BP3_00 YADAM1N 590 C DBIF_RSQL_SQL_ERROR CX_SY_OPEN_SQL_DB

16.02.2012 10:07:27 pssapbw2_BP3_02 S10039196 590 C MESSAGE_TYPE_X

16.02.2012 10:07:27 pssapbw3_BP3_03 DDIC 000 C DBIF_RSQL_SQL_ERROR CX_SY_OPEN_SQL_DB

16.02.2012 10:05:28 pssapbw3_BP3_03 S10052097 590 C MESSAGE_TYPE_X

16.02.2012 10:03:36 pssapbw1_BP3_00 MATHU2N 590 C OBJECTS_OBJREF_NOT_ASSIGNED

16.02.2012 10:03:04 pssapbw2_BP3_02 S10052097 590 C MESSAGE_TYPE_X

16.02.2012 10:01:59 pssapbw2_BP3_02 S10039196 590 C MESSAGE_TYPE_X

16.02.2012 10:00:37 pssapbw2_BP3_02 S10052097 590 C MESSAGE_TYPE_X

16.02.2012 09:59:41 pssapbw3_BP3_03 S10039196 590 C MESSAGE_TYPE_X

16.02.2012 09:51:44 pssapbw2_BP3_02 S10046076 590 C MESSAGE_TYPE_X

16.02.2012 09:50:05 pssapbw2_BP3_02 S10009432 590 C MESSAGE_TYPE_X

16.02.2012 09:33:20 pssapbw3_BP3_03 S10005042 590 C MESSAGE_TYPE_X

16.02.2012 09:30:28 pssapbw3_BP3_03 YADAM1N 590 C DBIF_RSQL_SQL_ERROR CX_SY_OPEN_SQL_DB

16.02.2012 09:28:42 pssapbw2_BP3_02 ALEREMOTE 590 C DBIF_RSQL_SQL_ERROR CX_SY_OPEN_SQL_DB

16.02.2012 09:28:21 pssapbw2_BP3_02 S10039196 590 C MESSAGE_TYPE_X

16.02.2012 09:25:15 pssapbw2_BP3_02 S10049858 590 C MESSAGE_TYPE_X

16.02.2012 09:21:12 pssapbw3_BP3_03 S10039196 590 C MESSAGE_TYPE_X

16.02.2012 09:18:28 pssapbw3_BP3_03 S0429977 590 C MESSAGE_TYPE_X

16.02.2012 09:18:17 pssapbw3_BP3_03 S0429977 590 C MESSAGE_TYPE_X

Sm21 error log :

Database error 4031 at EXE

Database error 4031

Run-time error "MESSAGE_TYPE_X" occurred

> Short dump "120216 103051 pssapbw3 BP303 " generated

Database error 4031 at EXE

Database error 4031

Run-time error "MESSAGE_TYPE_X" occurred

> Short dump "120216 103231 pssapbw3 BP303 " generated

Database error 4031 at EXE

Database error 4031

Run-time error "UNCAUGHT_EXCEPTION" occurred

> Short dump "120216 104344 pssapbw3 BP303 " generated

Transaction Canceled 00 671 ( UNCAUGHT_EXCEPTION 20120216104344pssapbw3_BP3_03 MEHRA1U 590 )

Database error 4031 at EXE

Database error 4031

Database error 22303 at EXE

Database error 22303

Database error 4031 at SEL access to table /BI0/F0TCT

Run-time error "DBIF_RSQL_SQL_ERROR" occurred

> Short dump "120216 105143 pssapbw1 BP300 " generated

Transaction Canceled 00 671 ( DBIF_RSQL_SQL_ERROR 20120216105143pssapbw1_BP3_00 ALEREMOTE 590 )

Database error 4031 at EXE

Database error 4031

Database error 4031 at EXE

ORA-04031: unable to allocate 4104 bytes of shared memory

("shared pool","DBMS_STATS","PL/SQL MPCODE","BAMIMA: Bam

Buffer")#ORA-06508: PL/SQL: could not find program unit bei

called: "SYS.DBMS_STATS"#ORA-06512: at line 1

database error 4031

database error 22303 at EXE

ORA-22303: type "SYS"."ODCIOBJECTLIST" not found#ORA-00604:

ST04:

User calls 2.001.501.706 Recursive calls 1.756.453.104

User commits 28.255.431 Parse count 93.609.885

User rollbacks 175.023 User/recursive calls 1,1

Log.Reads/User Calls 39,9

Kindly Advise

[Former Member]

Hi,

The issue is "ORA-04031: unable to allocate 4104 bytes of shared memory"

Please check the DB related shared memory parameter shared_pool_reserved_size.

Regards,

Vijaya

[Former Member]

Update Optimiser statistics failed today and please check the below error logs :

16.02.2012 06:02:36 BR0856I Number of indexes to delete statistics: 0

16.02.2012 06:02:36 BR0857I Number of indexes to collect statistics: 0

16.02.2012 06:02:36 BR0853I Number of tables to check (and collect ifneeded) statistics: 30702

16.02.2012 06:02:36 BR0126I Unattended mode active - no operator confirmation required

16.02.2012 06:02:36 BR0280I BRCONNECT time stamp: 2012-02-16 06.02.21

16.02.2012 06:02:36 BR0301W SQL error -4031 at location BrTabModifRead-1, SQL statement:

16.02.2012 06:02:36 'BEGIN DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;END;'

16.02.2012 06:02:36 ORA-04031: unable to allocate 4128 bytes of shared memory ("shared pool","DBMS_STATS","PL/SQL MPCODE","BAMIMA: Bam Buffer")

16.02.2012 06:02:36 ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STATS"

16.02.2012 06:02:36 ORA-06512: at line 1

16.02.2012 06:02:36

16.02.2012 06:02:36 BR0280I BRCONNECT time stamp: 2012-02-16 06.02.24

16.02.2012 06:02:36 BR0877I Checking and collecting table and index statistics...

16.02.2012 06:02:36 BR0280I BRCONNECT time stamp: 2012-02-16 06.02.25

16.02.2012 06:02:36 BR0881I Collecting statistics for table SAPBP3./BI0/F0EIH_C02 with method/sample CH ...

16.02.2012 06:02:36 BR0280I BRCONNECT time stamp: 2012-02-16 06.02.25

16.02.2012 06:02:36 BR0301E SQL error -604 at location stats_tab_collect-20, SQL statement:

16.02.2012 06:02:36 'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPBP3"', TABNAME => '"/BI0/F0EIH_C02"', ESTIMATE_PERCENT => NULL, METHOD_OPT

16.02.2012 06:02:36 ORA-00604: error occurred at recursive SQL level 1

16.02.2012 06:02:36 ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select /*+ index(idl_ub2$i_...","Typecheck","kgghteIn

16.02.2012 06:02:36 ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQL"

16.02.2012 06:02:36 ORA-06512: at "SYS.DBMS_STATS", line 11984

16.02.2012 06:02:36 ORA-00604: error occurred at recursive SQL level 1

16.02.2012 06:02:36 ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select /*+ index(idl_ub2$i_...","Typecheck","kgghteIn

16.02.2012 06:02:36 ORA-06508: PL/SQL: could

16.02.2012 06:02:36 BR0886E Checking/collecting statistics failed fortable SAPBP3./BI0/F0EIH_C02

16.02.2012 06:02:36 BR0280I BRCONNECT time stamp: 2012-02-16 06.02.25

16.02.2012 06:02:36 BR0881I Collecting statistics for table SAPBP3./BI0/F0EIH_C03 with method/sample CH ...

16.02.2012 06:02:36 BR0280I BRCONNECT time stamp: 2012-02-16 06.02.25

16.02.2012 06:02:36 BR0301E SQL error -604 at location stats_tab_collect-20, SQL statement:

16.02.2012 06:02:36 'BEGIN DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => '"SAPBP3"', TABNAME => '"/BI0/F0EIH_C03"', ESTIMATE_PERCENT => NULL, METHOD_OPT

16.02.2012 06:02:36 ORA-00604: error occurred at recursive SQL level 1

16.02.2012 06:02:36 ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select /*+ index(idl_ub2$i_...","Typecheck","kgghteIn

16.02.2012 06:02:36 ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_SQL"

16.02.2012 06:02:36 ORA-06512: at "SYS.DBMS_STATS", line 11984

16.02.2012 06:02:36 ORA-00604: error occurred at recursive SQL level 1

16.02.2012 06:02:36 ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select /*+ index(idl_ub2$i_...","Typecheck","kgghteIn

16.02.2012 06:02:36 ORA-06508: PL/SQL: could

16.02.2012 06:02:36 BR0886E Checking/collecting statistics failed fortable SAPBP3./BI0/F0EIH_C03

16.02.2012 06:02:36 BR0280I BRCONNECT time stamp: 2012-02-16 06.02.26

16.02.2012 06:02:36 BR0301E SQL error -4031 at location stats_ind_collect-3, SQL statement:

16.02.2012 06:02:36 'BEGIN DBMS_STATS.GATHER_INDEX_STATS (OWNNAME => '"SAPBP3"', INDNAME => '"/BI0/F0PM_C01~070"', ESTIMATE_PERCENT => 10, DEGREE =>

16.02.2012 06:02:36 ORA-04031: unable to allocate 4128 bytes of shared memory ("shared pool","unknown object","pacdHds_kkpaco","stP_kkpacd: kkpodPac

16.02.2012 06:02:36 ORA-06512: at "SYS.DBMS_STATS", line 11801

16.02.2012 06:02:36 ORA-06512: at "SYS.DBMS_STATS", line 11825

16.02.2012 06:02:36 ORA-06512: at line 1

16.02.2012 06:02:36 BR0886E Checking/collecting statistics failed forindex SAPBP3./BI0/F0PM_C01~070

16.02.2012 06:02:36 BR0280I BRCONNECT time stamp: 2012-02-16 06.02.26

Kindly advise

[Former Member]

Hi,

Please increase shared_pool_size

Br,

Venky

[Former Member]

Any SAP notes to check the oracle parameter recommendation from SAP . i have done long back ST04 to get Oracle recommandation report in ECC , can advise on same in BI systems .

Note :

Operating system AIX Database system ORACLE

Machine type 00F63B764C Release 10.2.0.5.0

Server name pssapbw1_BP3_00 Name BP3

Platform ID 324 Host pssapbw1

Component version SAP EHP 1 for SAP NetWeaver 7.0

Kindly advise

[Former Member]

yes, SAP Provides recommendations for Oracle. And it is very simple.

Refer to Note 1171650 .. Download the script

Since, yours is a BI System, you need to replace the string '<OLAP System? [n]>' with 'y'

Have the script ready in a notepad.

Execute SE38 -> RSORADJV -> Paste the Script and Run.

Revert in case if you have further queries.

Br,

Venky

[Former Member]

This Report will take huge time (It will impact the system while executing business hour ) ???

Thanks

[Former Member]

No, Not at all... The report will just execute for a couple of seconds..

Br,

Venky

[Former Member]

Regarding this error , what could be implement to avoid this similar issue in future only , we have to increase shared_pool_size ? any other recommandation we have to check from Oracle and SAP point .

Thanks and Advise

[JPReyes]

You need to review your full Oracle config,

Check

Note 830576 - Parameter recommendations for Oracle 10g

or

Note 1431798 - Oracle 11.2.0: Database Parameter Settings

depending what version of Oracle you are on

Regards

Juan

[Former Member]

Hi All,

Currently we are checking Oracle recom as per Note 830576 - ,

Now Small query :

we have parameter_check_102 file , i have search (<OLAP System?) and find the x_os_family,

( select decode(substr(upper('<OLAP System? [n]>'),1,1),'Y','TRUE','FALSE') olap from dual ) , i have to change from ('<OLAP System? [n]>'),1,1),'Y','TRUE','FALSE') to ('<OLAP System? [Y]>'),1,1),'Y','TRUE','FALSE') for BI system right ???

Kindly Advise .

Edited by: Mattyardy on Feb 16, 2012 3:31 PM

Edited by: Mattyardy on Feb 16, 2012 3:31 PM

[Former Member]

Hi,

Since your database on 11g, pls download the script "parameter_check_112.txt" attached to the note 1171650.

you need to replace the string '<OLAP System? [n]>' with 'y'.

Br,

Venky

[Former Member]

hi Venky,

SAP is SAP EHP 1 for SAP NetWeaver 7.0 ,AIX ,10.2.0.5.0

DB is Oracle 10.2.0.5.0 , i have already downloaded parameter_check_102.txt , i have tried to search '<OLAP System? , i got below in the text file.

( select decode(substr(upper('<OLAP System? [n]>'),1,1),'Y','TRUE','FALSE') olap from dual )

Now i have make change to ( select decode(substr(upper('<OLAP System? [n]>'),1,1),'Y') olap from dual ) ???

The above is correct ? or advise me i am wrong .

Thanks

[Former Member]

Hi,

The code in the script

( select decode(substr(upper('<OLAP System? [n]>'),1,1),'Y','TRUE','FALSE') olap from dual )

In notepad --> Cntl+H

Find what '<OLAP System? [n]>'

Replace with 'y'

After replacement it becomes

( select decode(substr(upper('y'),1,1),'Y','TRUE','FALSE') olap from dual )

Br,

Venky

[Former Member]

Hi Tea,

I have found the below info from one of the forum ,please suggest is related to this issue .

Kindly advise .

One of the blog for similar issue:

Solution:

There is an error cause by ORA-4031.

Please refer to SAP Note #1120481: ORA-4031 - memory allocation errors

for object-level.

Reason and Prerequisites

This is the Oracle bug 6487875/5573238, which is corrected with Oracle

10.2.0.4. This problem may occur on databases that have a large number of objects (for example, a lot of partitions).

Up to Oracle 10.2.0.4, if the above problem occurs, please set the

following init<SID>.ora parameter as a workaround

objectstatistics=false

Also, set the parameter online, as follows:

alter system set "_object_statistics"=false scope=spfile;

Then always restart the database to activate the parameter.

Here are some additional information regarding this on SAP OSS Note,

#839187: Oracle 10.2.0: Applying patch set/patches/patch

#23345: Consistency check of ORACLE database

Thanks

[rupali_karbhari3]

Hi,

Can you please check if your database is up and running correctly.

Also check result of"R3trans -d"

Regards,