Risk analysis batch error - unique constraint violated (ORA-00001)

[Former Member]

Dear All,

We are running GRC AC 5.3 SP7 (Oracle 10 DB) and currently encounter the following issue:

- the daily background job performing the batch risk analysis (users, roles, profiles, critical actions analysis) gets systematically in error

- the message is the following: "Failed Error while executing the Job for Object(s) :XXXXXX:Batch rolled back. Caused by java.sql.BatchUpdateException: ORA-00001: unique constraint (SAPSR3DB.SYS_C005915) violated ... (see log for details)"

- a single error occurs for users, then for roles, then for profiles.

Whenever we run a risk analysis on the erroneous object (user, role or profile), the status of the individual batch risk analysis is COMPLETE (and not ERROR). So it seems that online risk analysis is working fine in this case.

We have gone through all support messages.

Our issue is NOT related with:

- the failed generation of rules,

- the failed scheduling of a background job for batch risk analysis,

- or the missing JOBID field in the VIRSA_CC_GSEQ table,

for which we have identified similar issues in the forum (ORA-00001 - unique constraint violated).

In paralell, this might help: we have created a BITMAPINDEX on table VIRSA_CC_PRMVL, in order to fix some performance issues.

Once again, our issue is really a failed batch risk analysis via daily background job (full synch).

Has anyone some advice regarding the resolution of this issue?

Thanks in advance

Best regards

Nicolas

Edited by: Nicolas PIOT on Sep 30, 2010 6:05 PM

[Former Member]

Hello Surpeet

Thanks for your message.

Can you please be a little more specific on the reason why we should no longer user JCO conections but direct (rfc?), if I understand you well.

Thanks

Best regards

Nicolas

[Former Member]

Dear Ankan

Thanks for your message.

1 - We had already implemented the SAP Note (JOBID and sequence is not the problem, as it was already OK when we checked this note).

2 - we are effectively in SP7 but then it might be a bug. We have opened an OSS message at SAP. I will give the feedback also.

So the issue is still open.

Best regards

Nicolas

[Former Member]

Hi Nicolas,

We have the same error and it gets resolved by the note Sunny has mentioned. In this case the foreground ah-hoc risk analysis can be executed but the background risk analysis is the problem , for me it was cancellation at around 81%. Remember to keep taking an archive log backup of the GRC server and dont allow the archival jobs to get stuck which will result in the job hanging.

Also this error occurs repeatedly due to some application bug. However we are currently on patch 12 while i see you are on patch 7.

Regards

Ankan

[Former Member]

Hello Surpreet

I created a new dummy user, assigned SAP_ALL and SAP_NEW profiles.

Then, in Informer, I did a RIsk Analysis - user level on the abovementioned user (offline analysis = NO, unique rule set, report type = permission level and without any critical roles or profiles being excluded).

The foreground risk analysis result are OK, as all the risks of the rule set are shown for this single user (having all authorisations).

Any idea then for the way forward?

Thanks

Best regards

Nicolas

[Former Member]

Nicolas,

this confirms that there is no issue with rules.

hence issue is with background job. please do not use JCO connections.

use direct method to put details of the server in RAR itself -> configuration tab

this will solve the issue.

also scheudle one job per activity, say one job for user sync, one for role sync, and where ever possible per system

regards,

Surpreet

[Former Member]

Nicolas,

Create one use with SAP_ALL & SAP_NEW.

Now run analysis on that user in foreground.

Please let me know if it shows correct output or gives error.

Regards,

Surpreet

[Former Member]

Hello Sunny

Thanks for your question and time.

the user, role and profile in error are not always the same.

But it seems that it is almost always the last record to be analysed during the risk analysis batch.

Best regards

Nicolas

[Former Member]

Hello

Thanks for your message but this SAP Note does not solve this issue.

As stated in my initial message, we do have the correct set-up for the table VIRSA_CC_GSEQ with the correct number for JOBID.

To make it clear:

We CAN schedule a background job but whenever it is running, there is systematically an error with 1 user, 1 role and 1 profile in the batch risk analysis (all are 99% completed all the time)

Any other idea?

Thanks again

Nicolas

[sunny_pahuja2]

Hi,

Is this always from same role, user and profile or just the last one ?

Thanks

Sunny

[sunny_pahuja2]

Hi,

Your problem is documented in SAP Note 1463294 - Failed to schedule background job ORA-00001.

Thanks

Sunny