|Applies To||RSA Product Set: SecurID|
RSA Product/Service Type: SecurID Appliance
RSA Version/Condition: 3.0.4 AM 7.1 SP4
Platform: EMC CAP
Platform (Other): null
O/S Version: 2.2/rPath Linux
Product Name: RSA-0010015
Product Description: RSA SecurID Appliance SW License
|Issue||Customer having OOB errors for 4 tokens when trying to run sync tokens CLU. Tokens also cannot be deleted due to same error.|
*** Aborted by error. org.springframework.jdbc.UncategorizedSQLException: Hibernate operation: Could not execute JDBC batch update; uncategorized SQLException for SQL [update AM_TOKEN_OOB set LAST_LOGIN_DATE=?, SB_6=?, SB_2=?, IS_FIRST_LOGIN=?, EVENT_REMAIN_COUNTER=? where AM_TOKEN_ID=?]; SQL state ; error code ; ORA-08102: index key not found, obj# 545505, file 6, block 5990 (2)
|Tasks||determine which tokens are corrupt, either by trying to delete from Security Console, or by running a synctokens list on subsets of all tokens until you see the failure;|
# Authenticator Bulk Synchronization Utility
000120596691 0 false Mon Nov 03 16:23:00 MST 2014 Unlocked
The corrupt token is the one right after the last one before the error; most likely next sequence #
|Resolution||AM 7.1 – which uses Oracle. |
Delete the corrupt Tokens; 000120596332, 000120596693, 000126334256, 000120578213,
Get DB password
ON APPLIANCE or Linux: cd / RSA Security/RSAAuthenticationManager/utils
./rsautil manage-secrets -a get com.rsa.db.root.password
After entering your master password you should have a result which will be used to connect to sqlplus in the next step.
. ./rsaenv (dot space dot/rsaenv) rsaenv.cmd in Windows
sqlplus sys/<value from step1> as sysdba
sqlplus sys/HIKyB0Eobm as sysdba
-bash-3.00$ sqlplus sys/HIKyB0Eobm as sysdba
At the SQL prompt, run the following Select statements, and grab the results:
Select id, serial_number from RSA_REP.AM_TOKEN where serial_number='000120596332';
Copy the ID from your output (not 1c73fddf1483a8c0149638cf7899ef5a which is mine) and run the following queries;
select AM_TOKEN_ID, SB_2, SB_6 from RSA_BATCHREP.AM_TOKEN_OOB where am_token_id='1c73fddf1483a8c0149638cf7899ef5a';
select ID, NAME from RSA_REP.AM_TOKEN_ATTRIBUTE where id='1c73fddf1483a8c0149638cf7899ef5a';
Note if you get something back or nothing ‘no rows selected’ meaning that table is empty for that token ID
Depending on what we see, we will change those last couple of select statements to delete, in reverse order
delete from RSA_REP.AM_TOKEN_ATTRIBUTE where id='1c73fddf1483a8c0149638cf7899ef5a';
delete from RSA_BATCHREP.AM_TOKEN_OOB where am_token_id='1c73fddf1483a8c0149638cf7899ef5a';
delete from RSA_REP.AM_TOKEN where serial_number='000120596332';
Repeat to delete the rest of the corrupt Tokens; 000120596693, 000126334256, 000120578213,
|Notes||If this does not work, you will need to rebuild the Oracle indexes, easy way is to backup and restore the database. Worst case scenario below, be careful...|
Here's something to rebuild the database indexes, but we may want to test it before doing anything on your Production Server.
1. Backup your system Database
2. Import that backup into a Test System, either yours or mine. There is a chance this is all we have to do.
3. Rebuild the index – details below
a. Find all indexes for the table in SQL, details attached and below
b. Analyze the indexes
c. Rebuild the index online
I tested it here, but that’s not a thorough test. At a minimum we need a backup of your database before we do anything in case rebuilding these indexes impacts performance or breaks something, we can revert to the backup. It could be considered a security risk, but if you send your database backup to me, I can do the testing here. If you want to cut to the chase, we just backup your system then run this procedure on your production, and revert if any problems.
CE recommends the best approach is to take a backup of the system and restore on to a lab system and check if this resolves the issue.
If backup and restore resolves the issue, we can do the same on their prod server.
Please try backup and restore approach before rebuilding of indexes solution.
<screen shot - access SQL>
SQL statements to validate and Rebuilding of indexes.
1. Find all indexes for the table
Select table_name, index_name FROM dba_ind_columns where table_name like '%AM_TOKEN_OOB%';
2. Analyze the index
ANALYZE INDEX RSA_BATCHREP.IDX_AM_TOKEN_OOB_UTC VALIDATE STRUCTURE;
SELECT * FROM INDEX_STATS;
ANALYZE INDEX RSA_BATCHREP.IDX_AM_TOKEN_OOB_PK VALIDATE STRUCTURE;
SELECT * FROM INDEX_STATS;
3. Rebuild the index online
alter index RSA_BATCHREP.IDX_AM_TOKEN_OOB_PK rebuild ONLINE;
alter index RSA_BATCHREP.IDX_AM_TOKEN_OOB_UTC rebuild ONLINE;
Theoretically rebuilding indexes should not impact AM, it will have performance overhead while building but since our tables are small, it will be minimal.
Testing the impact of rebuilding of indexes on AM. Basic functional validation, also validate rebuilding of indexes does not impact token/replication/authentication behavior in your local setup.