How to fix issues with Sentinel 8.0.0.1 custom DataSync RDD policies configured with Oracle 11g Database to utilize the newly changed Event Data limit from 4k to 8k.

Sentinel older version’s support max of 4k event data size. After upgrading to Sentinel 8.0 or above with 8k event data size support, the DataSync policies fail to synchronize events beyond 4k event data size for custom RDD’s for Oracle 11g.

A script is provided, which can be executed on the Oracle 11g table to fix the datatype for message field. We should follow the below steps for fixing the issue:

1. Create script 1021125_fix_script.sql file using the content below. 

2. Open a SQLPLUS terminal/putty session on Oracle 11g machine. 

3. Login to sqlplus as the schema owner.

4 .Run the sql file( ex: @1021125_fix_script.sql ).

5. It will prompt for the rdd_table_name.

6. Enter the correct table name & press enter. 

7. Follow step 1-6 for all the RRD’s that are in problem state.

By following the above steps, it will update the RDD table column to the appropriate type as required.  Validate from the UI that the msg column is mapped correctly to the msg field. Re-sync from the date you upgraded to Sentinel 8 to get the lost data back to the database

Create script with the following content

spool  ./log_1021125.txt

set serveroutput on

set linesize 1000

set heading off

DECLARE

        v_table_name varchar2(100);

BEGIN

        v_table_name := '&RDD_table_name' ;

        execute immediate 'create table ' || v_table_name ||TO_CHAR(SYSDATE, 'YYYYMMDD') || ' as select * from '|| v_table_name;

        dbms_output.put_line('Table ' || v_table_name ||TO_CHAR(SYSDATE, 'YYYYMMDD') || ' created.');

        execute immediate 'alter table ' || v_table_name || ' add (msg_clob nclob)';

        dbms_output.put_line('Table ' || v_table_name || ' altered added column msg_clob of type nclob');

        execute immediate 'update '|| v_table_name || ' set msg_clob = msg ';

        dbms_output.put_line('Table '|| v_table_name || ' updated');

        execute immediate 'alter table '|| v_table_name || ' drop (msg)';

        dbms_output.put_line('Table '|| v_table_name || ' altered column msg dropped');

        execute immediate 'alter table '|| v_table_name || ' add (msg nclob)';

        dbms_output.put_line('Table '|| v_table_name || ' altered, added column (msg nclob)');

        execute immediate 'update '|| v_table_name ||' set msg = msg_clob';

        dbms_output.put_line('Table '|| v_table_name ||' updated');

        execute immediate 'alter table '||v_table_name|| ' drop (msg_clob)';

        dbms_output.put_line('Table '|| v_table_name ||' altered, column msg_clob dropped');

END;

/

spool off

set serveroutput off

exit 0;

In the Oracle 11g tables, the data type for message field was varchar2 which has a restriction of max 4k limit. The issue came up after we started supporting up to 8k character limit and varchar2 doesn’t support it.