Check Db hanging up

[former_member186066]

Hi Guys,

In one of DEV system, Check Db job is hanging up. (same result when run from brtools)

Here is what we see in BRTOOLS:

BR0801I BRCONNECT 7.20 (33)

BR0805I Start of BRCONNECT processing: cemdymqn.chk 2013-09-24 12.41.49

BR0484I BRCONNECT log file: /oracle/DEV/sapcheck/cemdymqn.chk

BR0477I Oracle pfile /oracle/DEV/112_64/dbs/initDEV.ora created from spfile /oracle/DEV/112_64/dbs/spfileDEV.ora

We have faced this before where the fixed happened to be a restart of Db.

However, I am looking for an alternate solution this time.

Any leads would be appreciated.

Thanks and Regards,
Ankit Mishra

[stefan_koehler]

Hi Ankit,

enable the BR*Tools Trace (environment variable BR_TRACE 15) and check the exact point (like OS command, SQL or whatever) where it is hanging.

Regards

Stefan

[former_member186066]

Hi Stefan,

Have enabled it.

here is the query where it is stuck:

'OPEN curs_84 CURSOR FOR'

'SELECT OWNER, SEGMENT_NAME, NVL(PARTITION_NAME, ' '), SEGMENT_TYPE, TABLESPACE_NAME, NVL(BYTES, 0), NVL(EXTENTS, 0), NVL(NEXT_EXTENT, 0), NVL(MAX_EXTENTS, 0), NVL(PCT_INCREASE, 0) FROM DBA_SEGMENTS WHERE OWNER LIKE '%' AND SEGMENT_NAME LIKE '%' AND SEGMENT_NAME NOT LIKE 'BIN$%' ORDER BY OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE'

Any leads?

[stefan_koehler]

Hi Ankit,

ok that is pretty obvious. You can reproduce this issue by executing this SQL manually in SQL*Plus now without using the BR*Tools every time. However it seems that this query on DBA_SEGMENTS produces an insufficient execution plan and takes very long (or never finishes at all).

Now we can go on with the exact and detailed analysis  (DBA_SEGMENTS is a well known candidate for such issues) or you can crosscheck SAPnote #871455. Troubleshooting the execution plan (on SYS objects) via SCN forum is pretty tough.

By the way .. have you ever gathered Oracle dictionary statistics (SAPnote #838725)? If not i would go on with that first .. then with SAPnote #871455 and at last we can try the detailed execution plan analysis here.

Regards

Stefan

[former_member186066]

Hi Stefan,

Thanks for the input.

Based on the SAP Note 838725:

Actions performed:

• Delete Oracle Dictionary statistics-->Done
• Delete Oracle System statistics-->Done
• Create Oracle Dictionar statistics--> Running for long.

Db size 550Gb, OS RHEL 6 on VM

Any idea how long does it take?

[stefan_koehler]

Hi Ankit,

how is "Running for long" defined? 1 hour, 1 day, etc.?

The runtime usually depends on your Oracle DDIC (e.g. amount of extents, AWR data / retention, statistic history / retention, etc.) and all of this dictionary usage depends on how you run / configured your system.

You can use V$SESSION to check what the PL/SQL procedure is doing (and maybe which objects are currently processed).

Regards

Stefan

[former_member186066]

Been at least 3 hours now.

Ok...Will check and update.

[former_member186066]

Our team worked on it and here is the cause and solutions:

Issue: Check Db hangs, SAP Jobs hangs, updates run for long time..in short SAP system is almost dead.

Observation:: Blocking sessions were found at Db level.

Cause: MMNL background process was holding up sessions.

Reason: There were DBA maintenance jobs scheduled which from SAP perpective are not required.

After disabling those jobs, there are no blocking sessions as of now.

However, we need to monitor the system for a couple of days to observe it's behaviour.

Thanks and Regards,

Ankit Mishra

[Reagan]

Hello

If this is an AIX operating system check whether the file systems are mounted with the right options.

Also do file system system consistency check.

Regards

RB

[Former Member]

Hi,

Can you please check the below note.

Note 1598594 - BR*Tools configuration for Oracle inst. under "oracle" user

And then to get more info of the issue please use the below note to increase trace..

1780001 - How to increase the trace level for the br*tools jobs which are scheduled in DB13

Thanks

Rishi abrol

[sunny_pahuja2]

Could you please check logs in alert log file?

Thanks,

Sunny

[former_member186066]

No particular logs observed in it.

[Former Member]

You have several ways to start from. First please check overall system state from OS level, free space on filesystems, process state of brconnect command and their child processes (if exists). Also attach full log file of brconnect run. What is OS you are using?