000017719 - AM 7.1 Replication breaks with ORA-02091: transaction rolled back and ORA-02292: integrity constraint

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 2Show Document
  • View in full screen mode

Article Content

Article Number000017719
Applies ToRSA Authentication Manager (AM) 7.1 SP4, 7.1 SP4 Oracle
Issue

Error.htm replication report, or Alert log:


ORA-02091: transaction rolled back
ORA-02292: integrity constraint (RSA_REP.FK_I MS_PRINCIPAL_ID_PRINADMROL) violated - child record found
RSA_REP. FK_I MS_ PRINCIPAL_ID _PRINADMROL) violated - child record found
Old: ID => 434a6af70becb78902018cea137692d8
Old: IDENTITY_SRC_KEY => cn=thielman.sl,cn=users,dc=cn,dc=pg,dc=com
Old: OWNER_ID => 8bf164110becb789020a1a858a6af3a2
Old: START_DATE => 19-07-2011 16:46:15
Old: EXUID => \86\39\af\0c\06\81\7e\4d\ae\6a\85\62\ec\bb\17\81
New: IDENTITY_SRC_KEY => cn=heidel.sl,cn=users,dc=cn,dc=pg,dc=com
New: OWNER_ID => 8bf164110becb789020a1a858a6af3a2
New: START_DATE => 19-07-2011 16:46:15
New: EXUID => \86\39\af\0c\06\81\7e\4d\ae\6a\85\62\ec\bb\17\81


Former Primary, now replica, stays attached for about a day, then needs action.
========primary_error.htm -============
remote_site_name => "problem replica won't stay attached"
local_apply_status => ABORTED
local_apply_error => ORA-26714: User error encountered while applying
local_apply_time => 01-08-2014 06:09:05 AM
local_apply_timeout => TIMEOUT
ORA-02292: integrity constraint (RSA_REP.FK_IMS_PRINCIPAL_ID_PRINGRP) violated - child record found
Command Type: DELETE
Old: ID => 44f2f89e0becb7890248d17af19fb2c3
Old: LAST_UPDATED_ON => 16-07-2012 12:25:47
Old: IDENTITY_SRC_KEY => cn=jarvis.e,cn=users,dc=eu,dc=pg,dc=com
Old: OWNER_ID => 8bf164110becb789020a1a858a6af3a2
Old: START_DATE => 20-07-2011 00:29:59
Old: EXUID => \e4\47\a5\f6\a0\ee\34\41\b6\c9\66\9f\54\e6\25\92
No CURRENT values
* End Of LCR 1 *
many others...
alert logs
Clean the instance apply error 2091 occurred in table
(1000s of times, dozens of different tables; RSA_REP.AM_TOKEN_ATTR_VALUES AM_TOKEN AM_PRINCIPAL
===alert_<SID>.log======
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.AM_TOKEN_ATTR_VALUES
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.AM_TOKEN_ATTR_VALUES
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.AM_TOKEN_ATTR_VALUES
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.AM_PRINCIPAL
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.IMS_PRINCIPAL_DATA
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.AM_TOKEN
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.AM_PRINCIPAL
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.AM_PRINCIPAL
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.IMS_PRINCIPAL_DATA
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurred in RSA_REP.AM_TOKEN
14-07-21 06:21:25 - IMS Trace - Unhandled apply error 1460 occurr?repeats
select * from rsa_rep.ims_schedule_job;
on replica, returns;
...
ID
NAME
SCHEDULE_CLASS
SCHEDULE_DATA
NODE_ID STATUS
LAST_UPDATED_BY
LAST_UPDA S
6f1ac0e30cecb7891dd845072c5afe47
ID
NAME
SCHEDULE_CLASS
SCHEDULE_DATA
NODE_ID STATUS
LAST_UPDATED_BY
LAST_UPDA S -
Unresolvable Users and User Groups Cleanup
ID
NAME
SCHEDULE_CLASS
SCHEDULE_DATA
NODE_ID STATUS
LAST_UPDATED_BY
LAST_UPDA S
com.rsa.batchjob.common.WeeklySchedule ...
==log_primary.html======
ORA-04052: error occurred when looking up remote object RSA_STREA MS_ADMIN.RSA_STREAMS_ADMIN@CDL9A8HG.IMS.RSA ORA-00604: error occu rred at recursive SQL level 3 ORA-02068: following severe error f rom CDL9A8HG ORA-01033: ORACLE initialization or shutdown in prog ress
the error occurred with remote OOB invocation.
ORA-02091: transaction rolled back ORA-02291: integrity constraint (RSA_REP.FK_I MS_PRIN_D_ID_PRIATTVAL) violated - parent key not found
CauseORA-02091: transaction rolled back
Cause: Also see error 2092. If the transaction is aborted at a remote site then you will only see 2091; if aborted at host then you will see 2092 and 2091.
Action: Add rollback segment and retry the transaction.
Above is Oracle speak - but in AM terms after promotion of replica, and re-attach of demoted Primary, a cleanup unresolvable users batch job was still running on the now replica, so the changes on the replica were sometimes already done before the changes on the Primary were replicated.
ResolutionHere is what needs to be done on the AM 7.1 SP4 primary, clean up the replica by the normal procedure:

                ./rsautil setup-replication -a remove-replica -n <name of replica to be removed>
                ./rsautil setup-replication -a remove-unreg-replicas
                ./rsautil manage-rep-error -a run-script -o cleanup_propagation.sql

Then connect to Oracle and run the following commands.  Please type in for the single quote instead of copy & paste.
1.    Update
      update rsa_rep.ims_schedule_job set node_id=?<PrimaryShortName>_server? where node_id= ?<replicaShortName>_server?;
2.    Then double check there is nothing returned there:
      select * from rsa_rep.ims_schedule_job where node_id=?<replicaShortName>_server?;
3.    Then delete the batch job:
      delete from rsa_rep.ims_batch_job where node_id=?<replicaShortName>_server?;
4.    Commit
      commit;
Then attach the replica with a manual package.
NotesData collection with SQL queries:

bash-3.00$ cd /usr/local/RSASecurity/RSAAuthenticationManager/utils
. ./rsaenv (this sets some environment variables, note this starts with dot-space-dot-slash)
./rsautil manage-secrets -a get com.rsa.db.root.password
Enter Master password: ***********
com.rsa.db.root.password: AAaaAAaaaa

sqlplus sys/AAaaAAaaaa as sysdba

SQL>
SQL> spool on
SQL> spool /tmp/changes.txt
SQL> Select name, node_id from rsa_rep.ims_batch_job;
NAME
--------------------------------------------------------------------------------
NODE_ID
--------------------------------
Unresolvable Users and User Groups Cleanup cs-appliance3-03_server ?
SQL> ?78? rows selected.
SQL> select name, node_id from rsa_rep.ims_schedule_job;
SQL> ?14? rows selected.
NAME
--------------------------------------------------------------------------------
NODE_ID
--------------------------------
weeklyadmin_admin_070513_1854PM_UTC cs-appliance3-03_server ?
SQL> select * from rsa_rep.ims_instance_node;
ID INSTANCE_ID ?
SQL> spool off
Legacy Article IDa67218

Attachments

    Outcomes