Cannot post SD documents after upgrade to Oracle 11g

[Former Member]

Dear Gurus,

Lately I have tried to upgrade database for SAP R/3 Enterprise 4.7 from Oracle 9i (9.2.0.7) to Oracle 11g (11.2.0.2). I have tried some of its features including table and index compression.

Right now my team is testing my server and find out some weird behaviour on its database. They try to create some sales order (VA01) and this action create SD document number. But when we try to check this created document number, SAP server replied weird message. It said that this SD document number is not on database and may have been archived.

When I check on ST22, it said "ORA-01502: index 'SAPWPR.VBAP~0' or partition of such index is in unusable state".

FYI, this index has been rebuild and compressed before.

I have tried to rebuild this index successfully, but still not help. I also have checked its status from SE11 and I cannot find this index on VBPA table.

Any suggestion ?

Ardhian

http://sapbasis.wordpress.com

[Former Member · Accepted solution]

Hi,

How did you created the index? Did you try to creat the index by SE14 -> Tables -> Edit -> Indexes?

Could you paste the activation log, here?

Best regards,

Orkun Gedik

[Former Member · Accepted solution]

Hi Orkun,

This index is primary index so SAP was created for us when we installed it.

Ok, I'll try your suggestion first.

ardhian

[Former Member · Accepted solution]

Hi Orkun,

This is the log when I try from SE14 :

B-INDXVBPA0

-

---

Start background processing for object VBPA (INDX)

-

---

Request: Delete and recreate Index VBPA-0 (ADMIN/10.10.11/12:49)

Process: emaprd2ap_5

sql:

DROP INDEX "VBPA~0"

ORA-02429: cannot drop index used for enforcement of unique/primary ke

y

DDL time(___1): ...228.626 milliseconds

VBPA~0 could not be removed

Index VBPA-0 could not be deleted

Request for VBPA could not be executed

-

---

ardhian

[Former Member · Accepted solution]

Hi,

In order to be sure the index status, execute the statement;

select index_name, status from dba_indexes where index_name like 'VBPA%';

Then, execute the command, below;

alter index <DBOWNER>."VBPA~0" rebuild online;

If it not works, you may try to reorganize the table;

brspace -u / -f reorg -t VBPA

Best regards,

Orkun Gedik

[volker_borowski2]

> When I check on ST22, it said "ORA-01502: index 'SAPWPR.VBAP~0' or partition of such index is in unusable state".

Just want to make sure ...

Your error message was on VBAP, but later on you work on VBPA for verification.

Which one is now correct ?

Volker

[Former Member]

Hi Volker,

You are right. My mistake, the table is VBAP. And I have rebuild the index and now is working.

Thank you

ardhian

[volker_borowski2]

> I have tried to rebuild this index successfully, but still not help. I also have checked its status from SE11 and I cannot find this index on VBPA table. 
> 
> Any suggestion ?

Hi,

this does not sound logical. Either you created the index successfully, or it is not in place.

There is nothing in between beside may be a faulty kernel (which appears to be unlikely to me for such a major missbehavior).

Let's check:

select owner,INDEX_NAME,length(INDEX_NAME) from dba_indexes where table_name = 'VBAP';

If you created the index in sqlplus and not se14, it might be, that you got backspace chars into the name,

when you have a weird terminal emulation (This is why I am interested in the length).

and check the other way round:

select owner,TABLE_NAME from dba_indexes where index_name like 'VBAP%0';

Did you exchange the kernel to EX2 and install the instantclient?

Volker

[Former Member]

Hi Volker,

I have used SAP kernel 6.40_EX2 and oracle 11g instant client.

This index (VBPA~0) is default primary index by for table VBPA. This index is exists when I check from DB02 and ST05.

ardhian

[volker_borowski2]

ok,

please do the statements anyway, and may be include the STATUS column into the select.

If that one is 'INVALID', something with your rebuild did go wrong.

May be your compress reorg did go wrong somehow, and the Index 0 is still pointing to the interim table.

Please check Transaction DB02 in section segments for ALL VBAP objects.

so use "<star>VBAP<star>" as a qualifier. Substitute <star> with real * character. (we are in search of objects containing "#$")

Volker

[Former Member]

Hi Volker,

This is the result :

SQL> select owner,INDEX_NAME,length(INDEX_NAME) from dba_indexes where table_name = 'VBPA';

OWNER INDEX_NAME LENGTH(INDEX_NAME)

-

---

-

---

-

---

SAPWPR VBPA~Z01 8

SAPWPR VBPA~0 6

SQL> select owner,TABLE_NAME from dba_indexes where index_name like 'VBPA%0';

OWNER TABLE_NAME

-

---

-

---

SAPWPR VBPA2

SAPWPR VBPA3

SAPWPR VBPA

SQL>

Ardhian