Error Ora-28500 and Sqlstate I or Displays a Square Bracket - [ - Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager

Symptom:

 SQL> select * from "t"@pglink;

select * from "t"@pglink

                *

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[

Log file: /u01/db/hs/log/pgdsn_agt_15012.trc:

Exiting hgocont, rc=28500 at 2021/12/23-14:57:07 with error ptr FILE:hgocont.c LINE:2842 ID:Something other than invalid authorization

Cause:

The Oracle RDBMS is using a Unicode characterset:

select * from NLS_DATABASE_PARAMETERS ; 

PARAMETER                     VALUE 
------------------------      ----------------------------------------
NLS_CHARACTERSET              AL32UTF8

Solution:

Add the following to the DG4ODBC parameter file init<dg4odbc>.ora -

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

[oracle@ip-10-2-2-192 admin]$ cat /u01/db/hs/admin/initpgdsn.ora

# This is a sample agent init file that contains the HS parameters that are

# needed for the Database Gateway for ODBC

#

# HS init parameters

#

HS_FDS_CONNECT_INFO = pgdsn

HS_FDS_TRACE_LEVEL = DEBUG

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

# Avoid "[" displayed after "ORA-28500"

HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1

#

# ODBC specific environment variables

#

set ODBCINI=/etc/odbc.ini

Set hostname in AWS EC2

  cat << EOD >> /etc/cloud/cloud.cfg.d/99_hostname.cfg

 #cloud-config

hostname: my-node2

fqdn: my-node2.mydomain.com

EOD

## in case of selinux enabled:

cd /etc/cloud/cloud.cfg.d/

chown root: 99_hostname.cfg

chmod 644 99_hostname.cfg

chcon system_u:object_r:etc_t:s0 99_hostname.cfg

# reboot

OPERATION	SQL_REDO	SQL_UNDO	INFO
DDL	create table t (id number(8,0), c clob);		USER DDL (PlSql=0 RecDep=0)
INSERT	insert into "ADMIN"."T"("ID","C") values ('1',EMPTY_CLOB());	delete from "ADMIN"."T" where "ID" = '1';
UPDATE	update "ADMIN"."T" set "C" = 'a' where "ID" = '1';	update "ADMIN"."T" set "C" = NULL where "ID" = '1';
INSERT	insert into "ADMIN"."T"("ID","C") values ('2',EMPTY_CLOB());	delete from "ADMIN"."T" where "ID" = '2';
UPDATE	update "ADMIN"."T" set "C" = 'b' where "ID" = '2';	update "ADMIN"."T" set "C" = NULL where "ID" = '2';
INSERT	insert into "ADMIN"."T"("ID","C") values ('3',EMPTY_CLOB());	delete from "ADMIN"."T" where "ID" = '3';
UPDATE	update "ADMIN"."T" set "C" = 'c' where "ID" = '3';	update "ADMIN"."T" set "C" = NULL where "ID" = '3';
UPDATE	update "ADMIN"."T" set "C" = 'c1' where "ID" = '3';	update "ADMIN"."T" set "C" = NULL where "ID" = '3';
UPDATE	update "ADMIN"."T" set "C" = 'b1' where "ID" = '2';	update "ADMIN"."T" set "C" = NULL where "ID" = '2';
INSERT	insert into "ADMIN"."T"("ID","C") values ('4',EMPTY_CLOB());	delete from "ADMIN"."T" where "ID" = '4';
UPDATE	update "ADMIN"."T" set "C" = NULL where "ID" = '4';	update "ADMIN"."T" set "C" = NULL where "ID" = '4';
INSERT	insert into "ADMIN"."T"("ID","C") values ('5',EMPTY_CLOB());	delete from "ADMIN"."T" where "ID" = '5';
UPDATE	update "ADMIN"."T" set "C" = NULL where "ID" = '5';	update "ADMIN"."T" set "C" = NULL where "ID" = '5';
UPDATE	update "ADMIN"."T" set "C" = EMPTY_CLOB() where "ID" = '4';	update "ADMIN"."T" set "C" = NULL where "ID" = '4';
SEL_LOB_LOCATOR	DECLARE   loc_c CLOB;   buf_c VARCHAR2(6156);   loc_b BLOB;   buf_b RAW(6156);   loc_nc NCLOB;   buf_nc NVARCHAR2(6156);  BEGIN  select "C" into loc_c from "ADMIN"."T" where "ID" = '4' for update;		LOB sql_redo not re-executable
INTERNAL
LOB_WRITE	buf_c := '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 1, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 1023, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 2045, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 934, 3067, buf_c);		LOB sql_redo not re-executable
INTERNAL
INTERNAL	END;
UPDATE	update "ADMIN"."T" set "C" = EMPTY_CLOB() where "ID" = '5';	update "ADMIN"."T" set "C" = NULL where "ID" = '5';
SEL_LOB_LOCATOR	DECLARE   loc_c CLOB;   buf_c VARCHAR2(6156);   loc_b BLOB;   buf_b RAW(6156);   loc_nc NCLOB;   buf_nc NVARCHAR2(6156);  BEGIN  select "C" into loc_c from "ADMIN"."T" where "ID" = '5' for update;		LOB sql_redo not re-executable
INTERNAL
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 2045, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 964, 3067, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 4031, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 5053, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 6075, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 8061, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 9083, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 10105, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 12091, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 13113, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 964, 15157, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 16121, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 17143, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 18165, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';   dbms_lob.write(loc_c, 964, 19187, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 20151, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';     dbms_lob.write(loc_c, 1022, 21173, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';     dbms_lob.write(loc_c, 1022, 22195, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 964, 23217, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 24181, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 25203, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 26225, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c := '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 964, 27247, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 28211, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 29233, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 1022, 30255, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 964, 31277, buf_c);		LOB sql_redo not re-executable
LOB_WRITE	buf_c :=  '464646464646464646464646464646464646464646464646464646464646464646446464. <modified for better reading>..46464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464646464664646464646';    dbms_lob.write(loc_c, 527, 32241, buf_c);		LOB sql_redo not re-executable
INTERNAL
INTERNAL	END;

EXEC rdsadmin.rdsadmin_util.force_logging(p_enable => true);

begin

    rdsadmin.rdsadmin_util.alter_supplemental_logging(

        p_action => 'ADD');

end;

/

begin

    rdsadmin.rdsadmin_util.alter_supplemental_logging(

        p_action => 'ADD',

        p_type   => 'PRIMARY KEY');

end;

/

begin

    rdsadmin.rdsadmin_util.set_configuration(

        name  => 'archivelog retention hours',

        value => '24');

end;

/

commit;

drop table t purge;

select current_scn from v$database; --759308

create table t (id number(8,0), c clob);

insert into t values(1,'a');

commit;

insert into t values(2,'b');

commit;

insert into t values(3,'c');

commit;

update t set c='c1' where id=3;

commit;

update t set c='b1' where to_char(c)='b';

commit;

select * from t;

insert into t values(4,'');

insert into t values(5,'');

commit;

declare

  inbuf1  raw(3964); -- 3964 still possible for in-line lob

  inbuf2  raw(20000);

begin

  inbuf1 := utl_raw.cast_to_raw(rpad('FF',3964,'FF'));

  update t set c = inbuf1 where id=4;

  commit;

  inbuf2 := utl_raw.cast_to_raw(rpad('FF',20000,'FF'));

  update t set c = inbuf2 where id=5;

  commit;

end;

/

select length(c) from t;

select current_scn from v$database; --759455

execute dbms_logmnr_d.build(options => dbms_logmnr_d.store_in_redo_logs);

alter session set nls_date_format='yyyy-mon-dd hh24:mi:ss';

select * from v$log;

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_4_jt9fqdq7_.log

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_3_jt9fqcqr_.log

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_2_jt9fqc7g_.log

/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_1_jt9fqbox_.log

select * from v$archived_log where next_change#>759308 and first_change#<759455 ;

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/arch/redolog-24-1-1089890283.arc', options => dbms_logmnr.new);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_3_jt9fqcqr_.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_1_jt9fqbox_.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_2_jt9fqc7g_.log', options => dbms_logmnr.addfile);

execute dbms_logmnr.add_logfile(logfilename => '/rdsdbdata/db/MYTEST_A/onlinelog/o1_mf_4_jt9fqdq7_.log', options => dbms_logmnr.addfile);

begin

  dbms_logmnr.start_logmnr(

   startSCN=> 759308,

   endSCN=> 759455, 

   options => dbms_logmnr.ddl_dict_tracking + dbms_logmnr.dict_from_redo_logs 

   + dbms_logmnr.no_rowid_in_stmt);

end;

/

drop table my_logmnr_contents purge;

create table my_logmnr_contents tablespace users as select * from v$logmnr_contents;

select count(*) from my_logmnr_contents;

select operation,sql_redo,sql_undo,info from my_logmnr_contents where seg_owner='ADMIN' and seg_name='T';