Thursday, December 23, 2021

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

Wednesday, December 15, 2021

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


Monday, November 29, 2021

OPERATIONSQL_REDOSQL_UNDOINFO
DDLcreate table t (id number(8,0), c clob); USER DDL (PlSql=0 RecDep=0)
INSERTinsert into "ADMIN"."T"("ID","C") values ('1',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '1'; 
UPDATEupdate "ADMIN"."T" set "C" = 'a' where "ID" = '1';update "ADMIN"."T" set "C" = NULL where "ID" = '1'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('2',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '2'; 
UPDATEupdate "ADMIN"."T" set "C" = 'b' where "ID" = '2';update "ADMIN"."T" set "C" = NULL where "ID" = '2'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('3',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '3'; 
UPDATEupdate "ADMIN"."T" set "C" = 'c' where "ID" = '3';update "ADMIN"."T" set "C" = NULL where "ID" = '3'; 
UPDATEupdate "ADMIN"."T" set "C" = 'c1' where "ID" = '3';update "ADMIN"."T" set "C" = NULL where "ID" = '3'; 
UPDATEupdate "ADMIN"."T" set "C" = 'b1' where "ID" = '2';update "ADMIN"."T" set "C" = NULL where "ID" = '2'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('4',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '4'; 
UPDATEupdate "ADMIN"."T" set "C" = NULL where "ID" = '4';update "ADMIN"."T" set "C" = NULL where "ID" = '4'; 
INSERTinsert into "ADMIN"."T"("ID","C") values ('5',EMPTY_CLOB());delete from "ADMIN"."T" where "ID" = '5'; 
UPDATEupdate "ADMIN"."T" set "C" = NULL where "ID" = '5';update "ADMIN"."T" set "C" = NULL where "ID" = '5'; 
UPDATEupdate "ADMIN"."T" set "C" = EMPTY_CLOB() where "ID" = '4';update "ADMIN"."T" set "C" = NULL where "ID" = '4'; 
SEL_LOB_LOCATORDECLARE 
 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   
INTERNALEND;

  
UPDATEupdate "ADMIN"."T" set "C" = EMPTY_CLOB() where "ID" = '5';update "ADMIN"."T" set "C" = NULL where "ID" = '5'; 
SEL_LOB_LOCATORDECLARE 
 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   
INTERNALEND;

  


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';