DB_ACTION_STATNEW - Error - How to increase shared memory in shadow instanc

[Former Member]

Hi all,

We are facing the below error in the DB_ACTION_STATNEW phase:

-

---

TRACE-INFO: 82: 13 4.822960

TRACE-INFO: 83: [developertra,00000] WHERE TABNAME = :A0; 8 4.822968

TRACE-INFO: 84: [dbntab ,06694] ***LOG BY4=>sql error 604 performing SEL on table DDNTT

TRACE-INFO: 85: 222 4.823190

TRACE-INFO: 86: [dbntab ,06694] ***LOG BY0=>ORA-00604: error occurred at recursive SQL level 1

TRACE-INFO: 87: ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","Typecheck","seg:kggfaAllocSeg")

TRACE-INFO: 88: 21 4.823211

TRACE-INFO: 89: [dbeh.c ,00000] *** ERROR => missing return code handler 13 4.823224

TRACE-INFO: 90: caller does not handle code 1 from dbntab.c[3718]

TRACE-INFO: 91: ==> calling sap_dext to abort transaction

HALT 20080410111303

-

---

We have a physical RAM of 64 GB.

dcecqv1:root > dmesg | more

Apr 10 11:24

...

$Revision: vmunix: B11.23_LR FLAVOR=perf Fri Aug 29 22:35:38 PDT 2003 $

Memory Information:

physical page size = 4096 bytes, logical page size = 4096 bytes

Physical: 66399504 Kbytes, lockable: 51408244 Kbytes, available: 59648572 Kbytes

From the error message, I feel that I need to increase the shared memory, can you help me how to increase the shared memory now, bcos system is now running in the shadow instance.

Thanks & Regards

Senthil

[Former Member · Accepted solution]

Give the details in these logs

STATNEW.LOG

LISTNEW.LOG

DBSTATNEW.LOG

Did you run Update Statistics before upgrade ?

Make sure that the shared pool is 400MB or higher.

If the problem stil occurs, expand the shared pool to an even higher value.

sqlplus “/as sysdba”

show parameter shared_pool_size

ALTER SYSTEM SET SHARED_POOL_SIZE = 500M;

Edited by: Shaji Jacob on Apr 10, 2008 8:01 PM

[Former Member · Accepted solution]

Thank you for your mail Shaji.

SQL> show parameter shared_pool_size

ORA-00604: error occurred at recursive SQL level 2

ORA-04031: unable to allocate 4120 bytes of shared memory ("shared

pool","select x.inst_id,x.indx+1,ks...","Typecheck","seg:kggfaAllocSeg")

When I tried to see the shared_pool_size it gives the above error.

Regarding the three log files:

STATNEW.LOG

LISTNEW.LOG

DBSTATNEW.LOG

- I didnot find STATNEW.LOG file in the path /usr/sap/put/log

- supadm> cat DBSTATNEW.LOG

1 ETQ201XEntering upgrade-phase "DB_ACTION_STATNEW" ("20080410111258")

2 ETQ367 Connect variables are set for standard instance access

4 ETQ399 System-nr = '02', GwService = 'sapgw02'

4 ETQ399 Environment variables:

4 ETQ399 dbs_ora_schema=SAPR3

4 ETQ399 auth_shadow_upgrade=0

4 ETQ389 reading file "LISTNEW.LOG" in directory "/usr/sap/put/log".

4 ETQ275 Created SQL-script "EXSTATE.ORA"

4 ETQ275 Created SQL-script "EXSTATC.ORA"

4 ETQ276 Executing SQL-script "EXSTATE.ORA"

4 ETQ001 START time "2008/04/10 11:12:58"

4 ETQ399 ext. env.: DIR_LIBRARY=/usr/sap/SUP/SYS/exe/run

4 ETQ399 ext. env.: LD_LIBRARY_PATH=/usr/sap/SUP/SYS/exe/run:/opt/java1.5/jre/lib/IA64N:/opt/java1.5/jre/lib/IA64N/server:/opt/java1.5/jre/../lib/IA64N

4 ETQ399 2008/04/10 11:12:58: put_execute: (tp) forkpid:2537

4 ETQ002 END time "2008/04/10 11:13:03"

4 ETQ278 Errors during execution of SQL-script "EXSTATE.ORA"

1EETQ203 Upgrade phase "DB_ACTION_STATNEW" aborted with errors ("20080410111303")

supadm>

-

supadm> tail LISTNEW.LOG

NAME= WTY_OBJNR_INDEX

NAME= WTY_PARTNER_TAB

NAME= WTY_RCLHST

NAME= WTY_RCLOBJ

NAME= WTY_RELOB_TAB

NAME= WTY_RELOB_TAB_TX

NAME= WTY_TABLE_ALV

NAME= WYT2M

tp_exec_dbscript: 1 statement(s) successfully processed.

supadm>

Can u pl help

Thanks

Senthil

[Former Member · Accepted solution]

sqlplus '/as sysdba'

ALTER SYSTEM SET SHARED_POOL_SIZE = 500M;

Then repeat the phase

[Former Member · Accepted solution]

I just checked the shared pool size from the init<sid>.ora file

supadm> pwd

/oracle/SUP/102_64/dbs

supadm> cat initSUP.ora | grep shared_pool

1. Variable Size = shared_pool_size (approx.)

2. unit of shared_pool_size: bytes

#shared_pool_size = 796917760

#shared_pool_reserved_size = 79691776

shared_pool_size = 796917760

shared_pool_reserved_size = 402653184

It has 796 MB size for shared pool size

So can I increase now to 900 MB?

Regards

senthil

[Former Member · Accepted solution]

Shaji,

When I tried to change the shared pool size, I get the below message

SQL> alter system set SHARED_POOL_SIZE = 900M;

alter system set SHARED_POOL_SIZE = 900M

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-04033: Insufficient memory to grow pool

Can you pl help

Thanks & Regards

Senthil

[Former Member · Accepted solution]

Normally 700M is good enough. You can try these option :

Check at OS Level : sappfpar check pf=<instance profile Name>

See if any errors are listed, and correct them accordingly

or

Stop the UPGRADE and SAP system and the DB also

at os level run

check any process are running and kill them including JAVA and SAPup

run > cleanipc <instance no> -> to show

cleanipc <instance no> ->to remove

Start DB, SAP and the Shadow Instance

Start SAPup and repeat upgrade

[Former Member · Accepted solution]

Shaji,

I have checked

supadm> sappfpar check pf=SUP_DVEBMGS02_dcecqv1

Errors detected..................: 0

Warnings detected................: 0

supadm>

I have also checked the db cache size:

db_cache_size = 16777216000

Can you please help me with the procedure how to stop upgrade/sap/oracle at this stage and start the same?

Thanks & Regards

Senthil

[Former Member · Accepted solution]

Can you please help me with the procedure how to stop upgrade/sap/oracle at this stage and start the same?

To Stop Upgrade & SAP Instance

1) Exit out of your Upgrade Assistant

2) go to directory <DIR_PUT>/bin and execute the command

./SAPup stopshd

3) stopsap (to shutdown SAP and DB)

4)Kill all tp, R3load, SAPup, JAVA proceess

cleanipc <instance no> remove

cleanipc <shadow instance no> remove

To Start SAP & Upgrade

1) startsap

2) go to directory <DIR_PUT>/bin and execute the command

./SAPup startshd (to start the system)

2) start the Upgrade Assistance and Repeat the phase

[Former Member · Accepted solution]

Thank you Shaji.

I restarted Oracle and the issue got solved. Now looking for parameters to set, so that this will not repeat during our production upgrade

Regards

Senthil

[Former Member · Accepted solution]

Good.

Make sure you document all the learning of this upgrade so that it would be easy for the next upgrade

Cheers

Shaji