000029666 - AM 7.1 SP4 How to delete corrupt tokens that cannot be deleted: index key not found

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

Article Content

Article Number000029666
Applies ToRSA 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
IssueCustomer 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 [72000]; error code [8102]; ORA-08102: index key not found, obj# 545505, file 6, block 5990 (2)
Tasksdetermine 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
# (c) 2005-2010 RSA Security Inc.
# THIS FILE COULD BE USED AS A SOURCE OF TOKEN SERIAL NUMBERS.
# EACH SERIAL NUMBER MUST BE 12 DIGITS IN LENGTH.
# SERIAL NUMBERS LESS THAN 12 DIGITS MUST BE PREFIXED WITH ZEROS
# IN ORDER TO MEET THIS LENGTH REQUIREMENT.
# UPDATING Token Data [Mon Jan 12 15:20:33 MST 2015]
# Token            Clock   Next Tokencode  Last Login                    Principal       Security
# Serial Number    Offset  Mode Status     Date/Time                     Lockout Status
  000120596335     0       false           Wed Dec 24 07:51:53 MST 2014  Unlocked    
. . . . .

  000120596691     0       false           Mon Nov 03 16:23:00 MST 2014  Unlocked    
                   *** 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 [72000]; error code [8102]; ORA-08102: index key not found, obj# 545505, file 6, block 5990 (2)

 

The corrupt token is the one right after the last one before the error; most likely next sequence # 
000120596692

ResolutionAM 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. 
com.rsa.db.root.password: HIKyB0Eobm
. ./rsaenv                     (dot space dot/rsaenv)                                                      rsaenv.cmd in Windows
sqlplus sys/<value from step1> as sysdba
sqlplus sys/HIKyB0Eobm as sysdba
Example:
-bash-3.00$  sqlplus sys/HIKyB0Eobm as sysdba 
sqlplus
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';
SN
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
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, 
 
NotesIf 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. 

Outline. 

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. 



Details 

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%'; 

<screen shot> 



TABLE_NAME INDEX_NAME 

------------------------------ ------------------------------ 

AM_TOKEN_OOB IDX_AM_TOKEN_OOB_PK 

AM_TOKEN_OOB IDX_AM_TOKEN_OOB_UTC 

AM_TOKEN_OOB RSA_TEMPIDX_QILGF 



2. Analyze the index 

ANALYZE INDEX RSA_BATCHREP.IDX_AM_TOKEN_OOB_UTC VALIDATE STRUCTURE; 

SELECT * FROM INDEX_STATS; 

<screen shot> 



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; 

<screen shot> 



Theoretically rebuilding indexes should not impact AM, it will have performance overhead while building but since our tables are small, it will be minimal. 

Next Steps: 

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. 

Attachments

    Outcomes