When querying a table in Tibero using DBLink from Oracle, an ORA-28500 error occurs.

This document explains the cause and solution for the ORA-28500 error that may occur when querying tables using DBLink (Oracle to Tibero) from Oracle. 

Note

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

Environment Where Issue Occurs

• Oracle 19c (19.0.0 / Linux)
• Tibero 7.2.4 (build seq 303667 / Linux)

[oracle@localhost]$ sqlplus tibero/tibero
SQL> select * from t1@tblink;
select * from t1@tblink
                 *
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from TBLINK

Cause

The ORA-28500 error occurs because the Oracle to Tibero DBLink configuration is not set up correctly.

Solution

1. Check Configuration Files 

• tnsnames.ora 
  1. Verify that the Alias in tnsnames.ora matches the 'Alias' used in the DBLink creation with using 'Alias'.
  2. Check that the IP, PORT, and other configuration details and spellings are correct.
  3. Ensure the number and placement of parentheses are correct.

[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ cat tnsnames.ora
tibero_test =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = IP of the server where Oracle Listener runs)(PORT = 1621))
   (CONNECT_DATA =(SID = tibero))
   (HS = OK)
 )

[oracle@localhost admin]$ sqlplus tibero/tibero
SQL> create database link tblink connect to tibero identified by "tmax" using 'tibero_test';
Database link created.

• listener.ora 
  1. Confirm that the SID_NAME in listener.ora matches the SID in tnsnames.ora.
  2. Check that the IP, PORT, and other configuration details and spellings are correct.
  3. Ensure the number and placement of parentheses are correct.
  4. After verification, restart the listener.

[oracle@localhost ~]$ cd $ORACLE_HOME/network/admin
[oracle@localhost admin]$ cat listener.ora
LORACLE =
(ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = tcp)(HOST = IP of the server where Oracle Listener runs)(PORT = 1621))
)
SID_LIST_LORACLE =
       (SID_LIST=
               (SID_DESC=
                       (SID_NAME=tibero)
                       (ORACLE_HOME=/home/oracle/oracle_19c)
                       (PROGRAM=dg4odbc)
               )
       )

• init<SID>.ora 
  1. Verify that the <SID> part of the init<SID>.ora filename matches the SID_NAME in listener.ora.
  2. Check that HS_FDS_CONNECT_INFO in init<SID>.ora matches the Alias in tbdsn.tbr.
  3. Confirm the path and permissions of HS_FDS_SHAREABLE_NAME in init<SID>.ora.

[oracle@localhost ~]$ cd $ORACLE_HOME/hs/admin
[oracle@localhost admin]$ cat inittibero.ora
# HS init parameters
HS_FDS_CONNECT_INFO=tibero
HS_FDS_TRACE_LEVEL=DEBUG
HS_FDS_SHAREABLE_NAME=/home/oracle/tibero7/client/lib/libtbodbc.so
HS_NLS_NCHAR=UCS2

# Environment variables required for the non-Oracle system
set TBCLI_WCHAR_TYPE=ucs2

• tbdsn.tbr
  1. Check that the IP, PORT, and other configuration details and spellings are correct.
  2. Ensure the number and placement of parentheses are correct.
  3. Verify that you can connect to Tibero using tbsql.

[oracle@localhost ~]$ cd $TB_HOME/client/config
[oracle@localhost config]$ cat tbdsn.tbr
tibero=(
   (INSTANCE=(HOST= Tibero server IP)
             (PORT=18629)
             (DB_NAME=tibero_tar)
   )
)
Confirmed Tibero connection via tbsql from Oracle server
[oracle@localhost ~]$ tbsql tibero/tmax@tibero
tbSQL 7
TmaxTibero Corporation Copyright (c) 2020-. All rights reserved.
Connected to Tibero using tibero.
SQL>

2. Add Parameters to init<SID>.ora

• Set this if the database character sets in the Oracle to Tibero environment are AL32UTF8 (Oracle) / UTF8 (Tibero), respectively.
• TBCLI_WCHAR_TYPE: Specifies the default wide char type used by the database client.
  Setting this to UCS2 fixes the wide char type to 2 bytes.

HS_NLS_NCHAR=UCS2
set TBCLI_WCHAR_TYPE=UCS2

3. Replace libtbodbc.so and dg4odbc

• Check the permissions of $TB_HOME/client/lib/libtbodbc.so and try replacing it with another version of libtbodbc.so.
• Check the permissions of $ORACLE_HOME/bin/dg4odbc and try replacing it by separately downloading another version of dg4odbc.

[oracle@localhost ~]$ ls -al $TB_HOME/client/lib/libtbodbc.so
-rwxr-xr-x 1 oracle oracle 45689072 Nov  1  2024 /home/oracle/tibero7/client/lib/libtbodbc.so
[oracle@localhost ~]$ ls -al $ORACLE_HOME/bin/dg4odbc
-rwxr-x--x 1 oracle oracle 738168  Jun 22  2023 /home/oracle/oracle_19c/bin/dg4odbc