000031961 - Error message "ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion" is reported when attempting to configure Oracle ODBC collection in RSA Security Analytics

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

Article Content

Article Number000031961
Applies ToRSA Product Set: NetWitness Logs and Packets (Security Analytics)
RSA Product/Service Type: Log Collector
RSA Version/Condition: 10.5.x, 10.6.x
Platform: CentOS
Platform (Other): Oracle 10g, 11g
O/S Version: EL6
IssueWhen configuring ODBC connection for Oracle 10g or 11g databases using the default template with extended logging enabled, an error similar to the example below is observed.
 
[OdbcCollection] [failure] [oracle_11g_auditing.dbanfs] [processing] [dbanfs] [processing] Data query failed; dataQuery: SELECT "SYS"."V_$INSTANCE"."VERSION", '-', "SYS"."V_$INSTANCE"."HOST_NAME", '-', "SYS"."V_$INSTANCE"."INSTANCE_NAME", '-', '-', "SYS"."AUD$"."SESSIONID", "SYS"."AUD$"."ENTRYID", "SYS"."AUD$"."STATEMENT", CASE "SYS"."AUD$"."ACTION#" WHEN 101 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') WHEN 102 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') ELSE TO_CHAR(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date),'yyyy-mm-dd hh24:mi:ss') END "timestamp", "SYS"."AUD$"."USERID", "SYS"."AUD$"."USERHOST", "SYS"."AUD$"."TERMINAL", "SYS"."AUD$"."ACTION#", "SYS"."AUD$"."RETURNCODE", "SYS"."AUD$"."OBJ$CREATOR", "SYS"."AUD$"."OBJ$NAME", "SYS"."AUD$"."AUTH$PRIVILEGES", "SYS"."AUD$"."AUTH$GRANTEE", "SYS"."AUD$"."NEW$OWNER", "SYS"."AUD$"."NEW$NAME", "SYS"."AUD$"."SES$ACTIONS", "SYS"."AUD$"."SES$TID", "SYS"."AUD$"."LOGOFF$LREAD", "SYS"."AUD$"."LOGOFF$PREAD", "SYS"."AUD$"."LOGOFF$LWRITE", "SYS"."AUD$"."LOGOFF$DEAD", COALESCE(TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss'),' ') as LOGOFF$TIME, "SYS"."AUD$"."COMMENT$TEXT", "SYS"."AUD$"."CLIENTID", "SYS"."AUD$"."SPARE1", "SYS"."AUD$"."SPARE2", "SYS"."AUD$"."OBJ$LABEL", "SYS"."AUD$"."SES$LABEL", "SYS"."AUD$"."PRIV$USED", "SYS"."AUD$"."SESSIONCPU" , cast("SYS"."AUD$"."SQLBIND" as varchar(2000)) as SQL_BIND, cast("SYS"."AUD$"."SQLTEXT" as varchar(2000)) as SQL_TEXT FROM "SYS"."AUD$", "SYS"."V_$INSTANCE" WHERE (cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) > to_date('2015-11-09 14:41:12','yyyy-mm-dd hh24:mi:ss') AND SYSDATE-(2/(3600*24)g] Error finding any new events.  Reason: SQLFetch failed: Statement: "SELECT "SYS"."V_$INSTANCE"."VERSION", '-', "SYS"."V_$INSTANCE"."HOST_NAME", '-', "SYS"."V_$INSTANCE"."INSTANCE_NAME", '-', '-', "SYS"."AUD$"."SESSIONID", "SYS"."AUD$"."ENTRYID", "SYS"."AUD$"."STATEMENT", CASE "SYS"."AUD$"."ACTION#" WHEN 101 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') WHEN 102 THEN TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss') ELSE TO_CHAR(cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date),'yyyy-mm-dd hh24:mi:ss') END "timestamp", "SYS"."AUD$"."USERID", "SYS"."AUD$"."USERHOST", "SYS"."AUD$"."TERMINAL", "SYS"."AUD$"."ACTION#", "SYS"."AUD$"."RETURNCODE", "SYS"."AUD$"."OBJ$CREATOR", "SYS"."AUD$"."OBJ$NAME", "SYS"."AUD$"."AUTH$PRIVILEGES", "SYS"."AUD$"."AUTH$GRANTEE", "SYS"."AUD$"."NEW$OWNER", "SYS"."AUD$"."NEW$NAME", "SYS"."AUD$"."SES$ACTIONS", "SYS"."AUD$"."SES$TID", "SYS"."AUD$"."LOGOFF$LREAD", "SYS"."AUD$"."LOGOFF$PREAD", "SYS"."AUD$"."LOGOFF$LWRITE", "SYS"."AUD$"."LOGOFF$DEAD", COALESCE(TO_CHAR("SYS"."AUD$"."LOGOFF$TIME",'yyyy-mm-dd hh24:mi:ss'),' ') as LOGOFF$TIME, "SYS"."AUD$"."COMMENT$TEXT", "SYS"."AUD$"."CLIENTID", "SYS"."AUD$"."SPARE1", "SYS"."AUD$"."SPARE2", "SYS"."AUD$"."OBJ$LABEL", "SYS"."AUD$"."SES$LABEL", "SYS"."AUD$"."PRIV$USED", "SYS"."AUD$"."SESSIONCPU" , cast("SYS"."AUD$"."SQLBIND" as varchar(2000)) as SQL_BIND, cast("SYS"."AUD$"."SQLTEXT" as varchar(2000)) as SQL_TEXT FROM "SYS"."AUD$", "SYS"."V_$INSTANCE" WHERE (cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) > to_date('2015-11-09 14:41:12','yyyy-mm-dd hh24:mi:ss') AND SYSDATE-(2/(3600*24)) > cast((from_tz("SYS"."AUD$"."NTIMESTAMP#" ,'00:00') at local) as date) AND "SYS"."AUD$"."ACTION#" NOT IN (101,102)) OR ("SYS"."AUD$"."LOGOFF$TIME" > to_date('2015-11-09 14:41:12','yyyy-mm-dd hh24:mi:ss') AND "SYS"."AUD$"."ACTION#" IN (101,102) AND SYSDATE-(2/(3600*24)) > "SYS"."AUD$"."LOGOFF$TIME" ) ORDER BY 11 ASC"; Reason: state: S1000; error-code: 22835; description: [RSA][ODBC Oracle Wire Protocol driver][Oracle]ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 25318, maximum: 4000)
CauseThe error means the SQL query is selecting from an Oracle LOB (Large OBjects) data type (CLOB which has a maximum size of 4GB-1), and the data returned is too long to fit into the target data type, VARCHAR2 (maximum size 4,000 bytes).
CAST does not directly support any of the LOB datatypes. When using CAST to convert a CLOB value into a character datatype, the database implicitly converts the LOB value to characters, and then explicitly casts the resulting value into the target datatype. If the resulting value is larger than the target type, then the database returns an error.
WorkaroundTo resolve the issue, download the XML files attached to this article, and follow the steps below.
 
1. SSH to the Log Collector appliance as the root user, and stop the nwlogcollector service.

stop nwlogcollector

2. On the Log Collector, navigate to the /etc/netwitness/ng/logcollection/content/collection/odbc directory, and create a backup of the oracle_10g_auditing.xml and oracle_11g_auditing.xml files.

cp -p /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_10g_auditing.xml /root/oracle_10g_auditing.xml.bkp
rm -rf /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_10g_auditing.xml
cp -p /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_11g_auditing.xml /root/oracle_11g_auditing.xml.bkp
rm -rf /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_11g_auditing.xml

3. Replace these files with those attached to this article.
 

Warning: Don't keep a backup of the .xml files in the /etc/netwitness/ng/logcollection/content/collection/odbc/ directory, SA will use the first oracle_10g_auditing*, and the first oracle_11g_auditing* file it finds in this directory.
 

4. Start the nwlogcollector service again.

start nwlogcollector

5. Check whether or not the ODBC collection has started.
 

6. Check whether or not ODBC logs are being collected for the given event sources.
 

Addendum:
After making the above change there may occur an error relating to :basic_string::_S_create
User-added image
This error is due to the Max Cell Size default setting is 2048 characters, and now the above change truncates a couple of CLOB columns at 4000 characters.
To increase the Max Cell Size in the SA UI, Administration > Services > {Log Collector} > Config
In the Event Sources tab
Choose ODBC and Config in the dropdowns.
Edit each oracle_10g_auditing and oracle_11g_auditing Source
Open Advanced
See Max Cell Size, change the value to 4096 (default setting is 2048)
User-added image
Click OK
Restart the nwlogcollector service.
If you are unsure of any of the steps above or experience any issues, contact RSA Support and quote this article number for further assistance.

Outcomes