Issue | When 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) |
Workaround | To 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
 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)
 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. |