Data growth and Sequence increment in CA Risk Authentication database
Article ID: 263743
Updated On:
Products
Issue/Introduction
CA Risk Authentication is a security application used by many organizations to provide secure access to their network and systems. In this application, device IDs are created using a sequence that starts from 1 and increments with each new device ID. However, in some cases, this sequence can be disrupted due to various reasons, such as database errors or system failures. In such situations, it becomes necessary to reset the device ID sequence and revert it back to 1. This article will guide you through the process of reverting the sequence creation to 1 in CA Risk Authentication application.
Environment
Release : CA Risk Authentication 9.1
Cause
You can observed below errors in Advanced Auth and the calls are periodically returning errors in Production. The log files are showing:
Mon Mar 13 09:02:27.156 2023 WARNING: pid 4155 tid 28196: 2: 9:51343837: ArDBM::Caught ArDBMSQLException in _DbOp!. err : [ArDBM::fetch : SQL State:22003, Native Code: 0, ODBC code: [CA Advanced Auth][ODBC Oracle Wire Protocol driver]Numeric value out of range. Error in column 1.]
Mon Mar 13 01:15:27.255 2023 INFO: pid 4155 tid 5160: 2: 9:51337101: Dbm::ArDBM::fetch : SQL State:22003, Native Code: 0, ODBC code: [CA Advanced Auth][ODBC Oracle Wire Protocol driver]Numeric value out of range. Error in column 1.
Mon Mar 13 01:15:27.255 2023 WARNING: pid 4155 tid 5160: 2: 9:51337101: ArDBM::Caught ArDBMSQLException in _DbOp!. err : [ArDBM::fetch : SQL State:22003, Native Code: 0, ODBC code: [CA Advanced Auth][ODBC Oracle Wire Protocol driver]Numeric value out of range. Error in column 1.]
Mon Mar 13 01:15:27.255 2023 INFO: pid 4155 tid 5160: 8: 9:51337101: Device ID generation failed.
Mon Mar 13 01:15:27.269 2023 WARNING: pid 4155 tid 5160: 8: 9:51337101: SQLException in ArRFMSSQLDBOp::insertDeviceInfo : [[DB: Sql Error]]
Mon Mar 13 01:15:27.269 2023 INFO: pid 4155 tid 5160: 8: 9:51337101: Error: inserting deviceId in DB
Resolution
The Risk Authentication Server creates a user to device binding in the database during risk authentication. Periodically, you may want to remove older device information from your database using below documentation:
However, by resetting the sequence, you can ensure that the application is functioning as intended and that device IDs are created in the correct order. Resetting the device ID sequence in CA Risk Authentication is a simple process that can be done by following the below steps:
- Step 1: Stop all the Risk Authentication instances. Before proceeding with the reset process, it is necessary to stop all the Risk Authentication instances.
- Step 2: Once all the instances are stopped, you need to reset the INCREMENT_BY and SEQUENCEBLOCKSIZE to 1. This can be achieved by executing the following SQL queries:
- ALTER SEQUENCE ARRFDEVICEIDSEQUENCE INCREMENT BY 1;
- UPDATE ARRFCONFIGURATION SET VALUE=1 WHERE CATEGORY='SEQUENCEBLOCKSIZE' AND NAME='ARRFDEVICEIDSEQUENCE';
- Step 3: After resetting the INCREMENT_BY and SEQUENCEBLOCKSIZE, you need to reset the deviceIdSequenceIds by updating the LAST_NUMBER in ARRFDEVICEIDSEQUENCE. This can be done by executing the following SQL query:
- ALTER SEQUENCE ARRFDEVICEIDSEQUENCE RESTART START WITH 1;
- Step 4: Once the sequence is reset, you can start all the Risk Authentication instances.
Note: It is important to note that any changes made to the application's database should be done with caution and only by authorized personnel.
Feedback
