Upgrade phase MAIN_SHDIMP/DB_ACTION_STATSHD running long

[Former Member]

Hi Gurus,

I am busy upgrading from BW 3.5 to BI 7.0 EHP1 and my upgrade has been stuck on phase MAIN_SHPIMP/DB_ACTION_STATSHD for more than 48 hours. My DB is Oracle and OS platform AIX, the DB size is 3.7TB.

In the beginning after 12 hous of running this step I cancelled it thinking that it is hanging and only then it wrote a log showing that it has been doing something. I repeated the phase as there is no other option other that repeating, since then it has been running for over 48 hours. I also noticed that it is running an ANALYZE which is obviously analyzing a table at a time. Any indication how long this step should take or please advise if there is anything I should do. Will also appreciate to hear from someone who has done thi upgrade.

Regards

Glendah

[Former Member · Accepted solution]

Hi Sunny,

At the point the upgrade is at now, how can I possibly still go back and select "Manual Selection of Parameters" ?

Currently the shadow instance is down as it is on the MAIN import phase, are you suggesting to cancel the upgrade ?

My concern currently is the progress of the current phase it is running, moreso to be able to check how far it is with

updating the statistics on the shadow tables. It has been running for too long and I am not sure for how much more time it

will take.

It is currently running the script :

ps -ef | grep tp

bidadm 2035730 2277546 0 Jul 10 pts/0 0:00 /usr/sap/BID/SYS/exe/run/tp execdbscript BID EXSTATE.ORA EXSTATE.ORA EXSTATE.ORA

From Oracle level this is what I see:

SQL> select sql_text from v$sql where sql_text like 'ANALY%';

SQL_TEXT

-

---

ANALYZE TABLE SAPR3."TODIR" estimate STATISTICS SAMPLE 20 PERCENT

But it has been showing to be on this table since Friday, so I am even more confused.

Regards

Glendah

[sunny_pahuja2 · Accepted solution]

Hi Glendah,

Don't cancel your upgrade. These optimization step you should try in next upgrade.

Check SAP Note 892296 - Enhancements in update statistics in BRCONNECT 7.00/7.10

Thanks

Sunny

[Former Member]

Hi Sunny,

Thanks for your help, but the upgrade was just hanging on this step, I tried to manually run the stats but they also hanged. So I

ended up stopping the upgrade and rebooting the OS and DB and restarted the upgrade fromthe same phase and it went through successfully within a short space of time. So much for wasted 4 days.

Regards

Glendah

[Former Member]

Is there a logfile EXSTATE.LOG. In my case the system was busy analyzing tables to make statistics and it toke about 3 hours.

Kind regards,

Erik Traga

[Former Member]

Hi Sunny,

Thanks for your response.

The upgrade strategy I chose did not give me the opportuninty to select any parameters, it was done automatically by the upgrade, a bit different from the old way of doing an upgrade. I did not have to specify the number of R3trans.

I selected High Resource Use (minimal downtime, fast import, archiving off) which is preconfigured.

The DB_CACHE_SIZE is 20GB. The memory confgured on the system is 49GB and 4 CPUs.

Regards

Glendah

[sunny_pahuja2]

Hi,

You can define these parameters by choosing option Manual Selection of Parameters. And RSMEMORY report you can run in SE38 transaction in shadow instance and change parameters.

Thanks

Sunny

[Former Member]

Hi Sunny,

You mentioned the optimization parameters before the upgrade, please advise which ones are these as I followed the upgrade guide including the SAPNOTES that were recommended. The upgrade is still on the same step after 72 hours, I am so desperate as I cant monitor progress.

Regards

Glendah

[sunny_pahuja2]

Hi,

It depends upon various factors like number of R3trans processes, memory parameter in report RSMEMORY, db parameters like db_cache_size etc.

Thanks

Sunny

[Former Member]

Hi Sunny,

Thanks for your prompt response.

I have checked these logs and all the other logs, no logs are in error.

Among the three logs you mentioned

1. End of output log LISTSHD.LOG

NAME= WSSOAPPROP~

NAME= WWWLANGRES~

NAME= WWWPARAMS~

tp_exec_dbscript: 1 statement(s) successfully processed.

2.Log DBSTATSHD

1 ETQ201 Entering upgrade-phase "DB_ACTION_STATSHD" ("20090710063614")

2 ETQ367 Connect variables are set for standard instance access

4 ETQ399 System-nr = '00', GwService = 'sapgw00'

4 ETQ399 Environment variables:

4 ETQ399 dbs_ora_schema=SAPR3

4 ETQ399 auth_shadow_upgrade=<null>

4 ETQ380 computing toolpath for request "TP_DEFAULT"

4 ETQ382 translates to group "R3UP_TOOL_GROUP_OLD"

4 ETQ383 translates to path "/usr/sap/BID/SYS/exe/run"

4 ETQ383 translates to path "exe"

4 ETQ399 Set environment for standard connect:

4 ETQ399 ENV: dbs_ora_schema=SAPR3

4 ETQ399 ENV: auth_shadow_upgrade=0

4 ETQ399 Set RFC variables for standard connect:

4 ETQ399 System-nr = '00', GwService = 'sapgw00'

4 ETQ399 Set tool parameters for standard connect:

4 ETQ399 default TPPARAM: DEFAULT.TPP

1 ETQ200 Executing actual phase 'MAIN_SHDIMP/DB_ACTION_STATSHD'.

1 ETQ399 Phase arguments:

2 ETQ399 Arg[0] = 'UPDATE_STATISTICS'

2 ETQ399 Arg[1] = 'STATSHD.XQL)'

2 ETQ399 Arg[2] = 'STATSHD.LOG'

2 ETQ399 Arg[3] = ''

2 ETQ399 Arg[4] = 'LISTSHD.LOG'

4 ETQ389 reading file "LISTSHD.LOG" in directory "/usr/sap/BID/upg/abap/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 "2009/07/10 06:36:15"

4 ETQ399 2009/07/10 06:36:15: put_execute: (tp) forkpid:2035730

3. The 3rd log STATSHD.log does not exist

The other thing that is of concern is that even the other two logs are not being updated currently, they were updated at the beginning of this phase when it was started and ever since there is no log written. How do I track the progress of this phase

then?

Regards

Glendah

[sunny_pahuja2]

Hi,

This phase is used to update database stat for your shadow system. As your database size is large so you should wait and also it dpendes upon what upgrade optimization parameters you used before starting upgrade. You can check logs of this phase in below files:

Update statistics for shadow tables STATSHD.LOG

LISTSHD.LOG

DBSTATSHD.LOG

Check all logs if there is any error.

Thanks

Sunny