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

[kng]

Hello Team,

Thanks for reaching Qlik community forum!!

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

It seems to be an Oracle issue and not Replicate one. Some times Oracle bulk copy could lead to ignored constraints with unusable indexes. Here are posts regarding the possibility.

http://www.dba-oracle.com/t_ora_01502_index_string_string_or_partition_of_such_index_is_in_usable_st...

https://dba.stackexchange.com/questions/7287/what-specifically-does-oraclebulkcopy-do-and-how-can-i-...

>>> Also you also try unchecking the Internal parameters for parameter named bulkUseParallel, save and resume the task to see if that helps!!

Regards,
shivananda

[MoeyE]

Hi Shivananda,

Thank you. I will look into this.

Regards,

Mohammed

[MoeyE]

Hi Shivananda,

Sorry just a follow up, if an Oracle Bulk Copy was done on the target database, what can be done to get the task to work again? Do we have to do a full reload of the task or go back by timestamp? We already tried doing a metadata refresh but the issues still persist.

Also, what does bulkUseParallel do and how does it help in this scenario? 

Regards,

Mohammed

[DesmondWOO]

Hi @MoeyE ,

Could you query SCHEMANAME.attrep_txn_state table and check for duplicate records based on the SERVER_NAME and TASK_NAME columns?

Regards,
Desmond

[john_wang]

Hello Mohammed, @MoeyE 

We discussed this error here. We need to drop this table and then let Replicate to recreate it again. We will do more tests and get back to you later.

Regards,

John.

[john_wang]

Hello Mohammed, @MoeyE 

Based on our analysis, it appears that duplicate rows were loaded into the SCHEMANAME.attrep_txn_state table. Since this table has a unique index, these duplicates have caused the index status to become UNUSABLE. We recommend the following steps to address the issue:

1. Verify Index Status:

    First, confirm the status of the index on the table by running the following query:

SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name = 'attrep_status';

    The status is likely to be UNUSABLE.

2. Check for Duplicate Rows:

    Next, query the table to identify if any duplicate rows are present. The table's index definition is:

CONSTRAINT "attrep_txn_state22135010" UNIQUE ("server_name", "task_name")

3. Resolve Duplicates and Rebuild Index:

    If duplicate rows are found, delete them, and then rebuild the index using the following SQL statement:

alter index "attrep_txn_state22135010" REBUILD;

Alternatively, you can drop the table attrep_txn_state and restart the task to recreate it:

drop table "attrep_txn_state";

Please note that other factors could also lead to the UNUSABLE status of the index. We should troubleshoot this issue step by step to ensure all possible causes are addressed.

Hope this helps.

John.

[MoeyE]

Hi John,

Thanks a lot. I looked into this and realised something odd which is that the attrep_status table isn't even enabled for the task, however the task is still trying to use it.

I'm not sure if this is a bug but I am going to try your solutions to see if it fixes it. 

Regards,

Mohammed

[DesmondWOO]

Hi @MoeyE ,

attrep_txn_state table is created when you enable "Store task recovery data in target database", while attrep_status is enabled in the "Control Tables" section. The attrep_status table should not affect attrep_txn_state table.

As mentioned earlier, could you identify the cause of the ORA-01502 error? Have you found any duplicate records?

Regards,
Desmond

[MoeyE]

Hi Desmond,

No duplicate records were found in the attrep_txn_state table.

Regards,

Mohammed