SQL error 1452 on TPALOG

[former_member182034]

Dear All,

I am 3rd time getting following error during system Import after successfully exported while i execute SMIGR_CREATE_DDL and past the output of these file in <EXPORT_DIR>/ABAP/DB/ORA .

(DB) INFO: TPALOG created #20120202220412

(IMP) INFO: import of TPALOG completed (117599 rows) #20120202220415

DbSl Trace: Error 1452 in exec_immediate() from oci_execute_stmt(), orpc=0

DbSl Trace: ORA-1452 occurred when executing SQL stmt (parse error offset=34)

(DB) ERROR: DDL statement failed
 (CREATE UNIQUE INDEX "TPALOG~0" ON "TPALOG"
 ( "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP" )
TABLESPACE PSAPSR3 STORAGE (INITIAL 2071552 NEXT 0000000080K MINEXTENTS 0000000001
 MAXEXTENTS 2147483645 PCTINCREASE 0 ) NOLOGGING COMPUTE STATISTICS )
 DbSlExecute: rc = 99
  (SQL error 1452)
  error message returned by DbSl:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
(DB) INFO: disconnected from DB

SQL> desc SAPSR3.TPALOG;
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------

 TRTIME                                    NOT NULL VARCHAR2(42)
 TRKORR                                    NOT NULL VARCHAR2(60)
 TARSYSTEM                                 NOT NULL VARCHAR2(30)
 TRCLI                                     NOT NULL VARCHAR2(9)
 TRSTEP                                    NOT NULL VARCHAR2(3)
 ALLCLI                                    NOT NULL VARCHAR2(3)
 PROJECT                                   NOT NULL VARCHAR2(60)
 TRUSER                                    NOT NULL VARCHAR2(36)
 RETCODE                                   NOT NULL VARCHAR2(12)
 HOST                                      NOT NULL VARCHAR2(96)
 ADMIN                                     NOT NULL VARCHAR2(36)
 TPSTAT_KEY                                NOT NULL VARCHAR2(60)
 LINESEQUENCE                              NOT NULL VARCHAR2(51)

SQL> select alldata.rowid, alldata.* from SAPSR3.TPALOG alldata,
  2  (SELECT TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP, ALLCLI, PROJECT, TRUSER,
RETCODE, HOST, ADMIN, TPSTAT_KEY, LINESEQUENCE FROM SAPSR3.TPALOG GROUP BY TRTIM
E, TRKORR, TARSYSTEM, TRCLI, TRSTEP, ALLCLI, PROJECT, TRUSER, RETCODE, HOST, ADM
IN, TPSTAT_KEY, LINESEQUENCE HAVING COUNT(*) > 1) keydata where
  3  alldata.TRTIME=keydata.TRTIME
  4  alldata.TRKORR=keydata.TRKORR
  5  alldata.TARSYSTEM=keydata.TARSYSTEM
  6  alldata.TRCLI=keydata.TRCLI
  7  alldata.TRSTEP=keydata.TRSTEP
  8  alldata.ALLCLI=keydata.ALLCLI
  9  alldata.PROJECT=keydata.PROJECT
 10  alldata.TRUSER=keydata.TRUSER
 11  alldata.RETCODE=keydata.RETCODE
 12  alldata.HOST=keydata.HOST
 13  alldata.ADMIN=keydata.ADMIN
 14  alldata.TPSTAT_KEY=keydata.TPSTAT_KEY
 15  alldata.LINESEQUENCE=LINESEQUENCE.HOST;
alldata.TRKORR=keydata.TRKORR
*
ERROR at line 4:
ORA-00933: SQL command not properly ended

what's issue with above command.

and

how can i resolve this issue? please guide me.

Regards,

[former_member184473 · Accepted solution]

Hello,

As mentioned in note [23237|http://service.sap.com/sap/support/notes/23237] you should use only columns TRTIME, TRKORR, TARSYSTEM, TRCLI and TRSTEP (index TPALOG~0) to identify the duplicated keys.

Regards,

Eduardo Rezende

[former_member182034 · Accepted solution]

Dear Eduardo,

the following command is running from last 10 minutes and executed thousand of line.

select alldata.rowid, alldata.* from SAPSR3.TPALOG alldata, 
(SELECT TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP FROM SAPSR3.TPALOG GROUP BY TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP HAVING COUNT(*) > 1) keydata where 
alldata.TRTIME=keydata.TRTIME
and alldata.TRKORR=keydata.TRKORR
and alldata.TARSYSTEM=keydata.TARSYSTEM
and alldata.TRCLI=keydata.TRCLI
and alldata.TRSTEP=keydata.TRSTEP;

would you share the command to find out the TPALOG~0?

Regards

[former_member184473 · Accepted solution]

Hello,

Here:

(DB) ERROR: DDL statement failed
 (CREATE UNIQUE INDEX "TPALOG~0" ON "TPALOG"
 ( "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP" )

Regards,

Eduardo

[former_member182034 · Accepted solution]

Dear ,

the output of sql command:

select alldata.rowid, alldata.* from SAPSR3.TPALOG alldata, 
(SELECT TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP FROM SAPSR3.TPALOG GROUP BY TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP HAVING COUNT(*) > 1) keydata where 
alldata.TRTIME=keydata.TRTIME
and alldata.TRKORR=keydata.TRKORR
and alldata.TARSYSTEM=keydata.TARSYSTEM
and alldata.TRCLI=keydata.TRCLI
and alldata.TRSTEP=keydata.TRSTEP;

no rows selected

when i execute following select command and it gave 64763 record.

SQL> select ROWID from "SAPSR3"."TPALOG" a where a.ROWID > (select MIN(ROWID) fr
om "SAPSR3"."TPALOG" b where b.TRTIME=a.TRTIME AND b.TRKORR=a.TRKORR);

Please guide me, how can I find this Unique index(TPALOG~0) because Indexes window is empty against TPALOG table in SE11 on source system.

Regards,

[former_member184473 · Accepted solution]

Hello,

You can use transaction SE14.

Regards,

Eduardo

[volker_borowski2]

Hi,

Check how many rows have a problem

select "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP", count(*)

from "TPALOG"

group by "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP"

having count(*) > 1

Depending of how many of these are affected different options to proceed are given.

Such errors can occur in very old systems when the export converts data.

Just recently had that with a DB that had CharNum fields padded with blanks.

Not possible to find out how they got into the source system before, but in fact

the keys where identical in SE16 allthough on the DB on value was zero padded and one was blankpadded.

Like:

"000123", ...
"   123", ...

SE16 and the DB export converted both to "000123" thus it was throwing duplicate keys on import.

Volker

Edited by: Volker Borowski on Feb 3, 2012 9:11 PM : Added code tags

[former_member182034]

hi Volker,

the result of mentioned query is:

SQL> select "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP", count(*)
  2  from SAPSR3."TPALOG"
  3  group by "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP"
  4  having count(*) > 1;

no rows selected

when i try to rebuild the TPALOG~0 then got duplicate key error

SQL> alter  index SAPSR3."TPALOG~0" rebuild online;
alter  index SAPSR3."TPALOG~0" rebuild online
*
ERROR at line 1:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

when i check this index(TPALOG~0) on source system then there is no exist this index against TPALOG table in SE11. When I try to create index (0) on TPALOG table then getting (Index ID 0 is reserved for the primary index)

please help me to sortout this issue.

Regards,

[volker_borowski2]

Uups,

so you are getting this on the SOURCE System?

I'd thought that the source system is correct and you are only having this problem on the target system....

In this case the PK Index on the source seems to be corrupted.

Your not gettig a result because the query is possibly using the incorrect index for access

which does not have the wrong keys .

Run the same query in your target system and / or run the same query in the

source system utilizing a FULL hint.

select /*+ FULL(T) */ 
"TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP", count(*)
from SAPSR3."TPALOG" T
group by "TRTIME", "TRKORR", "TARSYSTEM", "TRCLI", "TRSTEP"
having count(*) > 1;

Volker

[former_member182034]

Dear All,

would anyone(Specially Volker) guide me further because again I am getting same issue during homogenous system copy.

the result of said command is on Source and Target system:

SQL> select count(*), TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP from SAPSR3."TPALOG" group by TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP having count(*)>1;

there are showing 2535 rows,

SQL> select ROWID from "SAPSR3"."TPALOG" a where a.ROWID > (select MIN(ROWID) from "SAPSR3"."TPALOG" b

where b.TRTIME=a.TRTIME

and b.TRKORR=a.TRKORR

and b.TARSYSTEM=a.TARSYSTEM

and b.TRCLI=a.TRCLI

and b.TRSTEP=a.TRSTEP);

ROWID

------------------

AAAepgAASAAB9GGAAp

AAAepgAASAAB9GGAAu

AAAepgAASAAB9GGAA0

AAAepgAASAAB9GHAAM

AAAepgAAdAABViRAAR

69492 rows selected.

now I have to delete all above rows if yes then how can i delete duplicate rows/records please help me out because I am stuck due to this issue?

Regards,

[volker_borowski2]

Hello,

that is quit a lot of rows, and I would first assume that the Primary Key index "0" is not in place at all. Can you crosscheck the DB object in SE14 if the PK is probably missing.

How do you decide which rows to delete? Ok, if all datafields are identical, it is simple: you just eliminate the rows. But what if that is different?!? Right now I have no idea how to approach such a number of rows.

I'd first check if this are duplicate keys with same data fields. I'd try something like

(no system acces right now, so only the idea, not the real values...)

create table tpalog_check_tab as select

key_fields, datafiled1 || datafield2 || datafield 3 .... as concatenated_datafield

from tpalog;

This will create a temporary table with the same keys, but a single datafield which is a concatenation of all datafields of tpalog. You could the check this table with

select TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP,

count(distinct concatenated_datafield) from SAPSR3.tpalog_check_tab

group by TRTIME, TRKORR, TARSYSTEM, TRCLI, TRSTEP

having count(distinct concatenated_datafield) >1;

This will retrieve keys with DIFFERENT datafield content.

After this we might have a better basis to check what is defective.

BTW, I will be on vacation the next days with only limited Interent access, but I am confident, that  the other guys will help you as well.

Volker

[Former Member]

I recommend U create a backup copy of deleted rows before U actually dare to delete them:

create table TPALOG_BCK as select * from

"SAPSR3"."TPALOG";

In your query U rely on later rowid to identify newly added duplicates, but are U sure new rows are realy incorrect and not the old ones?

[Former Member]

Hi Abdul,

I recently had the same problem, this is the solution that SAP Support gave me and it worked:

1. Backup the content of table TPALOG;

2. Delete all its entries;

3. Modify the ENDTIME in TPALOGHDR table to a point in time before the

first timestamp in ALOG. This will force STMS to read the ALOG files

again and updates the table TPALOG.

They explained that the STMS looks in the table TPALOGHDR to check if the time frame for which

the import history is called is between STARTTIME and ENDTIME. If it is, it will use the entries in table TPALOG.If not, it will read

the ALOGxxxx files on DIR_TRANS/log and update table TPALOG.

You will find attatched the exact steps I followed.

Best Regards,

Luis Clemente

SAP TICKET.txt.zip

[Former Member]

Hi,

Please check line 15

Thanks,

Salman