search cancel

Could not get audit next sequence id, ODBC code: [AA][ODBC Oracle Wire Protocol driver][Oracle]ORA-01426: numeric overflow in Risk Authentication logs

book

Article ID: 202808

calendar_today

Updated On:

Products

CA Advanced Authentication - Strong Authentication (AuthMinder / WebFort) CA Strong Authentication CA Rapid App Security CA Risk Authentication CA Advanced Authentication

Issue/Introduction

We are observing the below error message in the Risk server log file, what is the reason for this and how to address this?

backup/arcotriskfort_31Oct20_23_03_26.log:Sat Oct 31 23:01:25.196 2020 LOW:     pid 14546 tid 15479: 2: 9:20790000: ArDBM::Executing Query[UDSUserAuditNextSeqID]

backup/arcotriskfort_31Oct20_23_03_26.log:Sat Oct 31 23:01:25.197 2020 INFO:    pid 14546 tid 15479: 2: 9:20790000: Dbm::SQL State:HY000, Native Code: 7F64FFFFFA6E, ODBC code: [AA][ODBC Oracle Wire Protocol driver][Oracle]ORA-01426: numeric overflow

backup/arcotriskfort_31Oct20_23_03_26.log:Sat Oct 31 23:01:25.197 2020 LOW:     pid 14546 tid 15479: 2: 9:20790000: ArDBPoolManagerImpl::reportQueryFailure: Query has failed. Figure out the implications

backup/arcotriskfort_31Oct20_23_03_26.log:Sat Oct 31 23:01:25.197 2020 LOW:     pid 14546 tid 15479: 2: 9:20790000: Entering ArDBPoolManagerImpl::isKnownFailure with this [0xf8a9c0]

backup/arcotriskfort_31Oct20_23_03_26.log:Sat Oct 31 23:01:25.197 2020 LOW:     pid 14546 tid 15479: 2: 9:20790000: Exiting ArDBPoolManagerImpl::isKnownFailure with this [0xf8a9c0]

backup/arcotriskfort_31Oct20_23_03_26.log:Sat Oct 31 23:01:25.197 2020 LOW:     pid 14546 tid 15479: 2: 9:20790000: DBTransactionProtector::rolling back transaction on connection [0xfc9e80]

backup/arcotriskfort_31Oct20_23_03_26.log:Sat Oct 31 23:01:25.197 2020 INFO:    pid 14546 tid 15479: 2: 9:20790000: ArDBM::Execution with error (not ICV) for Query[UDSUserAuditNextSeqID]

backup/arcotriskfort_31Oct20_23_03_26.log:Sat Oct 31 23:01:25.197 2020 LOW:     pid 14546 tid 15479: 2: 9:20790000: Releasing in primary, ctxtIndex=1

backup/arcotriskfort_31Oct20_23_03_26.log:Sat Oct 31 23:01:25.197 2020 WARNING: pid 14546 tid 15479: 2: 9:20790000: Could not get audit next sequence id: [SQL State:HY000, Native Code: 7F64FFFFFA6E, ODBC code: [AA][ODBC Oracle Wire Protocol driver][Oracle]ORA-01426: numeric overflow]

CA UDS manages its own Sequence IDs. The ARUDSUSERAUDITREFIDSEQ sequence contains all information for user activities. We need to split ARUDSUSERAUDITREFIDSEQ StartingValue and MaximumValue accordingly to cater replication environment needs. The distributed/split values are shared across the StrongAuth and RiskAuth instances using the respective UDS. The Transaction Sequence ID is continually incremented with the operation of the User related activities (Retrieve User, List User, Authorize...). When this Transaction Sequence ID limit is reached, additional user related operations are not allowed (which in turn affects CA Strong Authentication, CA Risk Authentication, and CA Adapter transactions) until the Transaction Sequence IDs are reset and appropriate database tables purged.

Environment

Release : 9.0

Component : AuthMinder(Arcot WebFort)

RiskMinder(Arcot RiskFort)

Cause

When the UDS sequence limit is reached, CA Strong Authentication transactions, CA Risk Authentication transactions are not allowed as user related operations failed.

Resolution

One of the sequences ARUDSUSERAUDITREFIDSEQ sequence contains the information related to UDS Audit sequences. Use a health check monitor to verify the current value with respect to the maximum possible value.
 
You can know the current sequence value and whether it is near maximum limit (as per the sequence distribution/divided value) with the help of the following query:
 
For Oracle:
 
select LAST_NUMBER from dba_sequences where sequence_name ='ARUDSUSERAUDITREFIDSEQ' and sequence_owner = '<databaseusername>';
For example:
 
select LAST_NUMBER from dba_sequences where sequence_name ='ARUDSUSERAUDITREFIDSEQ' and sequence_owner = 'AMRM8X';
 
For MSSQL :
 
select curr_val from ARSEQUENCETABLE where sequence_id like 'ARUDSUSERAUDITREFIDSEQ';
 
Resolution:
 
Perform the following steps where UDS is deployed and is being used by CA Strong Authentication Server, CA Risk Authentication Server, and CA Adapter.
 
Perform the following steps in Oracle:
Archive audit log table ARUDSUSERAUDITLOG (currentday – 1) data.
Truncate the audit log table ARUDSUSERAUDITLOG till (currentday – 1) data.
Enable the cycle flag which automatically resets the value to the minimum value configured.
alter sequence ARUDSUSERAUDITREFIDSEQ CYCLE; commit;
If you regularly archive and truncate the ARUDSUSERAUDITLOG data, the UDS sequence reset happens seamlessly without any downtime.
 
Perform the following steps in MSSQL:
Archive audit log table ARUDSUSERAUDITLOG till (currentday – 1) data.
Truncate the audit log table ARUDSUSERAUDITLOG till (currentday – 1) data.
Stop the Strong Authentication and Risk Authentication services. It requires downtime till the sequence reset occurs.
Stop the Application Server where UDS is deployed.
Stop the Application Server where CA Adapter is deployed.
update ARSEQUENCETABLE set curr_val = <min_value> where sequence_id like 'ARUDSUSERAUDITREFIDSEQ'; <min_value> refers to the starting value taken while distributing/splitting the sequences in the replication environment.
Restart the Application Servers where UDS and Adapter are deployed.
Restart the Strong Authentication service and Risk Authentication server in all the nodes.