ORA-01502: index 'SCHEMANAME.attrep_txn_state22135010'

[MoeyE]

Hi,

We are recently seeing this message in the task logs. Then the task fails. It looks like something to do with a Replicate internal table? I'm thinking of doing a start from timestamp to overcome this message or doing a metadata refresh. What exactly is this table? It's different from the the attrep_status table right?

ORA-01502: index 'SCHEMANAME.attrep_txn_state22135010' or partition of such index is in unusable state.

We are also seeing, and I found this article to solve it https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-and-Oracle-target-endpoint-OR....:

ORA-14452: attempt to create, alter or drop an index on temporary table already in use

Edit: furthermore, I am seeing this message a lot. This comes after an "Error in bulk" message for a certain table. Does anyone know what this means? I think I may need to create a support ticket for this as I am unsure as to whether this is caused by Replicate or Oracle.

ORA-06535: statement string in EXECUTE IMMEDIATE is NULL or 0 length ORA-06512: at line 1

Any help or pointers to what the first message indicates will be helpful. Thank you.

Regards,

Mohammed

[john_wang]

Hello Mohammed, @MoeyE 

Some other operations may lead the Unique Index status to UNUSUAL eg the table was moved to another tablespace etc.

Thanks,

John.

[MoeyE]

Hi John,

The table wasn't moved to another tablespace and there are no duplicates in it. I found in the logs this error after increasing target_apply logging

ORA-01400: cannot insert NULL into ("BYNXAU"."attrep_txn_state"."STATE_TIME") ORA-06512: at line 1 ORA-06512: at line 1

So it looks like it's trying to do a null insert into the attrep_txn_state table, I'm not sure why. Do you think it is worth dropping the table then stopping and resuming the task to have it recreated?

I'm not sure if this is worth mentioning but this attrep_txn_table has another task writing to it with the same name but from a different server. 

Regards,

Mohammed

[DesmondWOO]

Hi @MoeyE ,

I would recommend submitting support tickets for your issues:

1) ORA-01502: index 'SCHEMANAME.attrep_txn_state22135010' or partition of such index is in unusable state.

Please provide following information in the support ticket:

- Diagnostic package
- DDL of the SCHEMANAME.attrep_txn_state table, including all indexes.
- Export all records to the CSV file.
- If possible, please check with DBA if they have any idea why the index is in unable state.

Workaround: 
I think you can drop the table and let Replicate create it again.

2) ORA-01400: cannot insert NULL into ("BYNXAU"."attrep_txn_state"."STATE_TIME") ORA-06512: at line 1 ORA-06512: at line 1

For attrep_txn_state table, all columns are not nullable.  Please provide:
- Diagnostic package
- Task log
- recreate steps if possible

Thanks,
Desmond 

[MoeyE]

Hi Desmond,

Thanks. I've created a ticket.

Regards,

Mohammed