Skip to content
  • There are no suggestions because the search field is empty.

Error message ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion from Oracle ODBC collection in the NetWitness Platform

Issue

When configuring NetWitness ODBC collection from 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)

Cause

The error means the SQL query is selecting data from a field that is defined as an Oracle LOB (Large OBjects) data type.

Where CLOB is Character Large OBject.
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter (8 TB to 128 TB)
DB_BLOCK_SIZE specifies (in bytes) the size of Oracle database blocks. Typical values are 4096 and 8192 (range 2048 to 16384 for 32-bit OS & 32768 for 64-bit OS).

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.

The SQL query will work for returned data less than equal to 4,000 characters.  In the above example the returned data is 25,318 characters, hence the error.

Workaround

To resolve the issue, download the XML files attached to this article and follow the steps below for each Log Collector appliance.
 
1. SSH to the Log Collector appliance as the root user, and stop the nwlogcollector service.
systemctl stop nwlogcollector
2. On the Log Collector, navigate to the /etc/netwitness/ng/logcollection/content/collection/odbc directory, and create a backup of the original 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.bkup
rm -f /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.bkup
rm -f /etc/netwitness/ng/logcollection/content/collection/odbc/oracle_11g_auditing.xml
3. Replace these files with those attached to this article.
 
Warning: Do not 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.
systemctl 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.

Warning: The above workaround will be reverted if a new "Oracle Log Collector Configuration" is deployed from RSA Live to the Log Collector appliances.
 
Addendum:

A. After making the above change, there may occur an error relating to :basic_string::_S_create
User-added

This error is due to the Max Cell Size default setting is 2048 characters, and now the above change truncates the CLOB fields at 4,000 characters.

To increase the Max Cell Size in the NetWitness UI, Administration > Services > {Log Collector} > Config
In the Event Sources tab.
  1. Choose ODBC and Config in the dropdowns.
  2. Edit each oracle_10g_auditing and oracle_11g_auditing Source.
  3. Open Advanced.
  4. Change the "Max Cell Size" value to 4096 (default setting is 2048).
    User-added
     
  5. Click OK.
  6. Restart the nwlogcollector service.
    systemctl restart nwlogcollector

B. If the new oracle_10g_auditing.xml and oracle_11g_auditing.xml files fail with the error ORA-00904: : invalid identifier then see the following RSA Knowledgebase article, Running revised NetWitness oracle auditing xml files fails with error ORA-00904: : invalid identifier

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.

Product Details

RSA Product Set: NetWitness, Security Analytics
RSA Product/Service Type: Log Collector
RSA Version/Condition: 11.x
Platform: CentOS
Platform (Other): Oracle 10g, 11g

Summary

NetWitness ODBC Oracle collection failure on Oracle 11g with error [ORA-22835].


Approval Reviewer Queue

Technical approval queue