Reorganize the CDHDR table caused SAP hang - twice !

[Former Member]

Hello,

Last week, we did data archive on a couples of SAP table, one of them was : CDHDR (Change document header, it belongs to object : SD_VBRK). After that, we used BRTools to do table reorgan., but our users complained SAP system hang, I broke the table reorgan., around 2.5 hours, SAP resumed normal suddenly.

Last night, I did CDHDR table reorgan. again, every things repeated : our users complained SAP system hang, I broke the table reorgan. around 2.5 hours, SAP resumed normal.

Here is a portion of the alert_PRD.log :

Tue Jul 19 23:11:17 2011

DEBUG: Replaying xcb 0xc00000027db570d8, pmd 0xc00000027ed92db8 for failed op 8

Reconstructing Uhdr 0x800091 for xcb 0xc00000027db570d8, pmd 0xc00000027ed92db8

Doing block recovery for file 2 block 145

Tue Jul 19 23:26:15 2011

Incremental checkpoint up to RBA [0x423a.6f70.0], current log tail at RBA [0x423a.7e8f.0]

Wed Jul 20 02:10:33 2011

Block recovery from logseq 16954, block 31630 to scn 26731015084

Wed Jul 20 02:10:33 2011

Recovery of Online Redo Log: Thread 1 Group 2 Seq 16954 Reading mem 0

Mem# 0: /oracle/PRD/origlogB/log_g12m1.dbf

Mem# 1: /oracle/PRD/mirrlogB/log_g12m2.dbf

Block recovery completed at rba 16954.32399.16, scn 6.961211311

DEBUG: Restoring block headers for xcb 0xc00000027db570d8, pmd 0xc00000027ed92db8

DEBUG: Finished replay for xcb 0xc00000027db570d8, pmd 0xc00000027ed92db8 for failed op 8

Wed Jul 20 02:10:34 2011

Questions

(1)What is wrong on the CDHDR table ?

(2)Is it a hardware prolbem or software problem ?

(3)How should I fix this error permantently ?

(4)Will this error also occur on other tables when I do table reorgan next time ?

[Former Member]

How can I upload my alert log files to you ?

[stefan_koehler]

Hello Ming,

well you provided too less information.

What oracle version?

What kind of operating system?

There is a similiar known issue on SUN Solaris for your alert.log entry "DEBUG: Finished replay for xcb 0xc00000027db570d8, pmd 0xc00000027ed92db8 for failed op 8". Similiar messages are also written in case of "replaying" a transaction.

We need more information and the current wait event of the user sessions and the reorg session when this issue occurs.

Regards

Stefan

[Former Member]

Hello Ming,

I believe this is a case for SAP Support, if you haven't opened a support call yet then please do so.

Maybe the entries from the alertlog have nothing to do with your CDHDR reorganization problem.

There is a corrupt block in data file #2, which might be tablespace SYSAUX.

Anyway, SAP Support will need and ask for additional information to find the root cause.

Regards,

Mark

[Former Member]

Without the alert log entries i would have assumed this is a locking issue. The online reorg needs a full table lock at the end of the reorg. It is possible if you have a high load on your system, you cannot get the lock and cause a deadlock like situation. CDHDR is a notorious suspect for that, i experienced situations alike too. In my case there was a changepointer reorg job running for hours among other activity. I had to wait until the activity went down to do the reorg. You will have to watch ST04 -> Oracle session and the lock overview (DB01) to check for lock issues next time.

But with the entries in your alert log i also suggest you a consistency check on you database.

[23345 Consistency check of ORACLE database|https://service.sap.com/sap/support/notes/23345]

Cheers Michael