How to Resolve Common Errors Encountered while using Database Gateways (DG4IFMX, Dg4MSQL, DG4SYBS), DG4ODBC or GenericConnectivity (文档 ID 234517.1)
APPLIES TO:
Oracle Database - Enterprise Edition -Version 11.1.0.6 to 11.2.0.2 [Release 11.1 to 11.2]
Oracle Database Gateway for SQL Server - Version 11.1.0.6 to 11.2.0.1 [Release11.1 to 11.2]
Oracle Database Gateway for Sybase - Version 11.1.0.6 to 11.2.0.1 [Release 11.1to 11.2]
Oracle Database Gateway for Informix - Version 11.1.0.6 to 11.2.0.1 [Release11.1 to 11.2]
Oracle Database Gateway for DRDA - Version 10.2.0.1 to 11.2.0.1 [Release 10.2to 11.2]
Information in this document applies to any platform.
PURPOSE
This note explains how to resolve thefollowing error messages when using Database Gateways or Generic Connectivityusing DG4ODBC:
ORA-3113; ORA-2019; ORA-2085; ORA-12154;ORA-28545; ORA-28546; ORA-28509; ORA-942; ORA-904; ORA-28500; ORA-28528; Ora-12500;ORA-28509; ORA-28507;
TROUBLESHOOTINGSTEPS
Common errors with gateways or genericconnectivity
In this note we're using the English descriptions of common error messagescaused by an Oracle Database Gateway. To compare your message with the messagebelow, you can alter your current SQL*Plus session and compare your messagewith the messages listed in this note:
alter session set nls_language=american;
ORA-3113:
Problem:
SQL> connectsystem/manager@tg4msql;
ERROR:
ORA-03113: end-of-file on communication channel
Comment:
The log file written by the hs agent contains:
Remote HO Agent received unexpected RPC disconnect
status 2: ncrorpi_recv_procid, called from
Resolution:
You can't connectwith SQL*Plus directly to the foreign data store. The Gateways are onlydesigned to work with a database link from an Oracle database to the foreigndata store
ORA-2019:
Problem:
SQL> select *from all_catalog@demo;
select * from all_catalog@demo
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
Resolution:
Create a databaselink to connect to the foreign database
create[public] database link
connect to <username> identified by <password> using '<tnsalias>';
For case sensitiveusernames/passwords of foreign databases,
surround the user and password with double quotes.
create[public] database link
connect to "<UserName>"identified by"<PassWord>" using '<tns alias>';
ORA-2085:
Problem:
SQL> select *from all_catalog@dg4msql;
select * from all_catalog@dg4msq
*
ERROR at line 1:
ORA-02085: database link
connects to
Resolution:
This problem is related to GLOBAL NAMES.
The simplest way to disable global naming is to alter the current session:
alter session setglobal_names=false;
The other solution isto add a global_name to the gateway using these 2 case sensitive parameters:
HS_DB_NAME=<name of the database link>
HS_DB_DOMAIN =<name of the Oracle database domain>
(the domain valuemight be gathered running: select * from global_name;)
But the HS_DB_NAME must not exceed 8 characters and must not contain any extracharacters.
In the example from above the gateway database link is DG4MSQL and the , so the HS_DB_DOMAIN needs to be set to andHS_DB_NAME to DG4MSQL - both using capital letters.
If this is not possible, the global naming needs to be disabled within theOracle database by setting in the Oracle database GLOBAL_NAMES = FALSE.
ORA-12154:
Problem:
SQL> select *from all_catalog@demo;
select * from all_catalog@demo
*
ERROR at line 1:
ORA-12154: TNS:could not resolve service name
Resolution:
The database linkuses a SQL*Net alias not specified in the of the database server.
Query the data dictionary to figure out the 'HOST' specified for the databaselink:
selectdb_link, host from user_db_links;
or
selectdb_link, host from dba_db_links;
The 'HOST'value is the alias which will be used by SQL*Net and that alias needs to existsin the file present at the Oracle database server.
ORA-12504:
Problem:
ORA-12504:TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
Resolution:
Please check out the entryyou made, there might be a missing bracket after (ADDRESS=(PROTOCOL.... or anexra bracket at the end.
ORA-28513
Problem:
SQL> select *from all_catalog@demo;
select * from all_catalog@demo
*
ERROR at line 1:
ORA-28513: internal error in heterogeneous remote agent
Solution:
This error can happen when the gatewayinit file is not found in the gateway/admin directory or if it has an error inthe file. When there's a config error in the file it commonly reports the linein which the error is.
ORA-28544:
Problem:
SQL> select *from all_catalog@demo; select * from all_catalog@demo * ERROR at line 1:
ORA-28544 connect to agent failed, probable Net8 administration error
ORA-28546: connection initialization failed, probable Net8 admin error
ORA-28511: lost RPC connection to heterogeneous remote agent
using SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTO =TCP)(HOST=localhost)
...
Resolution:
Sometimes the errorORA-28544 might be visible only in the file of the database with thedb link calling the gateway. On Windows platforms this error could be related to the NTS authentication. Please set
sqlnet.authentication_services=(NONE)
in file
ORA-28545:
Problem:
SQL> select *from all_catalog@demo;
select * from all_catalog@demo
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
NCRO: Failed to make RSLV connection
ORA-02063: preceding 2 lines from DEMO
Resolution:
There's somethingwrong with the SQL*Net connection. The first step here is to verify thelistener.
1. Check out thelistener status using
lsnrctlstatus <listener name of the gateway>
it shoulddisplay at least one service handler for the gateway SID you configured and thestatus for this SID is 'unknown'. If you don't see your newly configuredGateway SID, please make sure that the listener was stopped and started afterthe file was edited. Still no service? Check out the brackets orif you have extrac characters in the listener service - finally a listenertrace set to SUPPORT during startup might help to identify the root cause whyther's no service.
The next step is the connectivity from the Oracle database home to the gatewaylistener
2. check out theSQL*Net configuration with TNSPING:
tnsping<gateway alias>
TNS-12545: Connectfailed because target host or object does not exist
The HOSTNAMEspecified in the is invalid Try with the OS ping to resolve thehostname / IP adddress
TNS-12541: TNS:nolistener
The hostnamespecified in the points to a machine without an Oracle listener,the listener on that machine is not running or the port number is wrong.
3. check out SID configuration in the listener and
Please make sure,that the SID in the listener reflects the SID specified in the sure you don't mix SID syntax with SERVICE_NAME syntax.
4. Programexecutable
Another very commonerror on Windows is the PROGRAM=<executable> specification. Specifying(PROGRAM = ) the executable with the .exe file type is causing anORA-28545 error.
You can specify just the executable dg4msql or when you want to have the .exefile type, then you have to specify the whole path to the executable: (PROGRAM= C:\product\11.2.0\tg_2\bin\)
5. Other rootcauses
Still no error found?Then please enable listener tracing (level support) and have a look at file after retrying the same select statement again. In the fileyou should see the connect from the Oracle database to the listener. After afew lines you should see that the listener tries to open 2 pipes. Doesthis work or does it fail and again a few lines later you see a hex/ascii blockthat contains an error stack like:
Example 1:
ERR=12500,CODE=12500, EMFI=4, CODE=12560, EMFI=4
Solution 1:
Then please checkagain the file; is the ORACLE_HOME directory specified within file correct? Or if you are using environment variables in please replace them with absolute paths.
Example 2:
EMFI=4,CODE=12547, EMFI=4, CODE=12560, EMFI=4, CODE=517, EMFI=4, BUF=':32: Broken pipe'
or similar
TNS-12500:TNS:listener failed to start a dedicated server process
TNS-12547: TNS:lost contact
TNS-12560: TNS:proto adapter error
TNS-00517: Lost contact
Solaris Error: 32: Broken pipe
Solution 2:
check out theexecutable of the gateway mentioned in the gateway listener
PROGRAM=<gatewayexecutable>
it must be located inthe $ORACLE_HOME/bin (where $ORACLE_HOME is the Oracle_Home of the gateway andalso correctly specified in the file). Calling it from the DOScommand line / Unix shell should not report any error like file not found...
Make sure theLD_LIBRARY_PATH, LIBPATH, SHLIB_PATH contains the correct word size librarylocation - 32bit libraries commonly located in $ORACLE_HOME/lib32 for 32bitgateways and $ORACLE_HOME/lib for 64bit gateways.
Make sure the SID specified in the matches the SID specified inthe file. After modifying the please make sure you'vestopped and started the listener again. Check the listener status afterwardsusing lsnrctl utility. The status report must show the gateway SID withat least 1 handler in status unknown.
SIDE NOTE: tnspingdoes NOT check for a valid SID - it only checks if a listener islistening on this particular host on this port number. The SID check needs tobe performed manually.
6. Versionconflict
Another commonissue that causes this error is that the gateway was installed into an existingOracle home of a different version. The gateway installation manual Oracle®Database Gateway Installation and Configuration Guide
11g Release 2 (11.2) for AIX 5L Based Systems (64-Bit), HP-UX Itanium,Solaris Operating System (SPARC 64-Bit), Linux x86, and Linux x86-64 PartNumber E12013-07 states in chapter 1:
1.3.3.1 Installingthe Software on a System with an Existing Oracle Installation
You must install this product in a new Oracle homedirectory. You cannot install
products from one release of Oracle Database Gateways intoan Oracle home
directory of a different release. For example, you cannotinstall release
11.2 software into an existing Oracle 10gR2 Oracle homedirectory. If you attempt
to install this release in an Oracle home directory thatcontains software from
an earlier Oracle release, then the installation will can install
this release more than once on the same system if eachinstallation is installed
in a separate Oracle home directory.
So please make sure that you do not install a certain gateway versioninto an existing Oracle_home where the software versions do not match.
7. foreigndatabase issue
It has been reportedthat when the foreign datbase was running out of disk space the error was alsoraised.
ORA-28546:
Problem 1:
SQL> select *from all_catalog@demo;
ERROR at line 1: ORA-28546: connection initialization failed, probable Net8admin error
ORA-02068: following severe error from DEMO
ORA-03113: end-of-file on communication channel
Resolution 1:
This error might becaused by a misplaced 'HS=' or 'HS=OK' key word in the . Make sureHS= or HS=OK is present and that the SID Connect String looks like:
(CONNECT_DATA= (SID = tg4msql)) <= there are 2 closing brackets!
The HS (HS=OK)key word is OUTSIDE of the connect_data section
Problem 2:
SQL> select *from all_catalog@test;
ora-28546: connection initialization failed, probable net8 admin error
ora-28511 lost rpc connection to heterogeneous remote agent SID ...
ora-02063 preceding 2 lines from test
Resolution 2:
Another issue couldbe a spelling mismatch in the : For example PROGARM= instead ofPROGRAM= will cause this error as well.
Problem 3:
SQL> select *from all_catalog@test;
ora-28546: connection initialization failed, probable net8 admin error
ora-28511 lost rpc connection to heterogeneous remote agent SID ...
ora-02063 preceding 2 lines from test
Resolution 3:
Another situationwhere this error can be found is a version mismatch. For example the Oracledatabase is release 9.2.0.4 and the gateway itself 9.2.0.1 => The fix is toapply the same database patchset (gateway patches are part of the databasepatchsets) to the gateway home as well; or if the gateway 9.2.0.1 was installedinto a 9.2.0.4 database directory to reapply the database patchset.
ORA-28509:
Problem 1:
Older gateways reportthis error instead of giving a detailed error description.
Resolution 1:
This error indicatesthe same as the errors described above. They can be solved by checking the SQL*Netconfiguration for the gateway / generic connectivity setup.
Problem 2:
SQL> selectuser from dual@test
ERROR at line 1:
ORA-02068: following severe error from TEST
ORA-28511: lost RPC connection to heterogeneous remote agent using SID=%s
ORA-28509: unable to establish a connection to non-Oracle system
Resolution 2:
This error might becaused by partly patched libraries. On some platforms patches apply theGateway/HSODBC patches only to $ORACLE_HOME/lib directories and the originallibraries still remain in the $ORACLE_HOME/lib32 directory. Please make surethe GATEWAY/HSODBC libraries in the $ORACLE_HOME/lib32 match the libraries in$ORACLE_HOME/lib directory. If you are NOT sure which libraries belong to whichgateway, please call Oracle Support.
ORA-942:
Problem:
SQL> selectirgendwas from demo@demo;
select irgendwas from demo@demo
*
ERROR at line 1:
ORA-00942: table or view does not exist
...(the text here depends on the library/odbc...
ORA-02063: preceding 2 lines from DEMO
Resolution:
make sure the table exists in the foreigndata store. Pay attention that some foreign data stores are case sensitive andthus the table_name must be surrounded by double quotes.
ORA-904:
Problem:
SQL> selectirgendwas from "demo"@demo;
select irgendwas from demo@demo
*
ERROR at line 1:
ORA-00904: "IRGENDWAS": invalid identifier
Resolution:
Make sure the columnname specified exists at the foreign table and if it is case sensitive,surround it by double quotes.
ORA-972:
Problem:
SQL> select *from "VERYLONGTABLENAMEMOREThan30characters"@demo;
select * from "VERYLONGTABLENAMEMOREThan30characters"@demo
*
ERROR at line 1:
ORA-00972: identifier is too long
Resolution:
An Oracle databasedoes not allow object names with more then 30 characters. A gateway let aforeign database behave like an Oracle database; thus long object names liketable or column names are not allowed. A simple workaround is to create a viewon the remote database with less then 30 characters and work with the view.
ORA-28500:
Problem 1:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][H006] The init parameter is not set. Pleaseset it in file.
ORA-02063: preceding 2 lines from HSODBC
Resolution 1:
The connectinformation to the remote database or the ODBC DSN is not found. Add the properconnect string required by the gateway or generic connectivity agent.
Problem 2:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-28541: Error in HS init file on line 11.
ORA-02063: preceding 2 lines from HSODBC
Resolution 2:
The of thegateway / generic connectivity contains an invalid parameter in line 11
Problem 3:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC]DRV_InitTdp: (2005): ;
[Microsoft][ODBC SQL Server Driver][SQL Server]Error authenticating user
'sa'. (SQL State: 00000; SQL Code: 18456)
ORA-02063: preceding 2 lines from HSODBC
Resolution 3:
The username passwordfor the database link is wrong or no connect information was specified. Makesure that for case sensitive usernames/passwords the create database linkstatement contains the double quotes for the username/passwords.
Further make sure you specified a username and password. OS Authentication iscurrently not supported by most of the gateways of by generic connectivity.
Problem 4:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned
this message:
[Generic Connectivity Using ODBC]DRV_InitTdp: (2005): ;
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does
not exist or access denied.[Microsoft][ODBC SQL Server Driver]
[TCP/IP Sockets]ConnectionOpen (Connect()).
(SQL State: 00000; SQL Code: 10061)
ORA-02063: preceding 2 lines from HSODBC
Resolution 4:
Make sure the foreigndatabase is up and running on the configured machine and no Firewall isblocking the connections. Test can for example performed by using Telnet toconnect to the target database host and port.
Problem 5:
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Wire Protocol driver]General
gethostbyname_r {HY000}
ORA-02063: preceding 2 lines from DG4MSQL
Resolution 5:
TheHS_FDS_CONNECT_INFO parameter has an incorrect value. For example whenincluding the port within the brackets: HS_FDS_CONNECT_INFO=[:1433]//Northwindthe gateway will use [:1433] as hostname which will thenlead to the error MapHostNameToAddress gethostbyname.
The correct parameter format is HS_FDS_CONNECT_INFO=[<Hostname>]:<portnumber>//<Database> or instead of specifying a port you can also use anames instance: HS_FDS_CONNECT_INFO=[<Hostname>]/<named SQL Serverinstance>/<Database>
ORA-28528:
Problem:
SQL> select *from DecimalType@hsodbc;
ERROR:
ORA-28528: Heterogeneous Services datatype conversion error
Resolution:
The language of the foreign data store (remote database) does not match withyour Oracle database settings. Adapt in the Gateway/Generic Connectivityinitialisation file the parameter HS_LANGUAGE.
HS_LANGUAGE must be set to the LANGUAGE used in the FOREIGN database. Detailscan be found in a separate note: NLS Guide for Generic Connectivity andGateways (Doc ID 230239.1)
ORA-24274 / ORA-28522 :
SQL> select *from all_catalog@;
select * from all_catalog@
*
ERROR at line 1:
ORA-28522: error initializing heterogeneous capabilities
ORA-28522: error initializing heterogeneous capabilities
ORA-28559: FDS_CLASS_NAME is 9.2.0.5.0_128, FDS_INST_NAME is
ORA-02063: preceding 3 lines from %s
ORA-00604: error occurred at recursive SQL level 1
ORA-24274: no row exists in the HS$_BASE_CAPS table for these parameters
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_HS_UTL", line 431
ORA-06512: at "SYS.DBMS_HS_CHK", line 51
ORA-06512: at "SYS.DBMS_HS_UTL", line 48
ORA-06512: at "SYS.DBMS_HS", line 38
ORA-06512: at line 1
or
SQL> selectcount(*) from task_status@idwcdw_t02;
ORA-28522: error initializing heterogeneous capabilities
ORA-28522: error initializing heterogeneous capabilities
ORA-28559: FDS_CLASS_NAME is RDB9.2.0.5.0_128, FDS_INST_NAME is IDWCDW
ORA-2063: preceding 3 lines from IDWCDW_T02
ORA-604: error occurred at recursive SQL level 1
ORA-24274: no row exists in the HS$_FDS_CLASS table for these parameters
ORA-6512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-6512: at "SYS.DBMS_HS_UTL", line 431
ORA-6512: at "SYS.DBMS_HS_CHK", line 147
ORA-6512: at "SYS.DBMS_HS_UTL", line 46
ORA-6512: at "SYS.DBMS_HS", line 38
ORA-6512: at line 1
or when you seeerrors in your file similar to:
HS: Unableto update ORACLE DD to upload Foreign Data Store info
HS: FDS class = 'DG4DB2_DB2ZOS11.2.0.1.0_0008', FDS instance = 'dg4db2'
HS: Failure occurred on create_fds_class() or create_fds_inst()
Problem 1:
HS_AUTOREGISTER isset to FALSE in the Oracle database and the gateway can't upload istcapabilities into the Oracle database.
Resolution:
connect to the Oracledatabase using SQL*Plus as DBA user and execute:
showparameter HS_AUTOREGISTER
If it is set to FALSEthen please change it to TRUE by executing:
altersystem set HS_AUTOREGISTER=true scope=spfile;
Problem 2:
Problems with the HSdictionary
Resolution 2:
There are severalcombinations where of Oracle database release and Gateway version where thisproblem is reported (For example Oracle database release 9.2.0.5). To fixthe issue connect to the Oracle database with the db link as SYSDBA user andrecreate the HS catalog.
@$ORACLE_HOME/rdbms/admin/
commit;
@$ORACLE_HOME/rdbms/admin/
commit;
Now therepository is up to date and the problem should be gone. (It might happen thatcatnohs/caths will report ORA-00054: resource busy and acquire with NOWAITspecified error; then please start up the database in restricted mode and rerunthe scripts catnohs/caths).
ATTENTION:
Commonly the impactof dropping and recreating the gateway dictionary is low as the gateway agentwill determine the next time it is loaded automatically if its capabilitiesexist in the dictonary and upload them if needed. The only impact is when youmanually updated the gateway capabilities (for example as suggested bySupport/Development), then you need to change the cpability AFTER the firstconnection of the gateway again.
A better way to fix the issue is to explicite update the HS data dictionarywith the changed capabilities rather then running catnohs/caths script:
On Unix you can use the "diff" command to figure out the differencesbetween the caths script shipped with the different different patch set versions.
For example:
diff$ORACLE_9204/rdbms/admin/ $ORACLE_9205/rdbms/admin/
..
> dbms_hs.replace_base_caps(531, 531, 'First/Last function');
..
The diff commandshows the mismatches, for example:dbms_hs.replace_base_caps(531, 531, 'First/Lastfunction');
To solve the problem, the entries needed in the "older" databaseshould be updated by running the SQL statements shown by the "DIFF"command:
SQL>exec dbms_hs.replace_base_caps(531, 531, 'First/Last function');
ORA-28509/28507
Problem:
SQL> selectuser from dual@hsodbc;
ERROR at line 1:
ORA-28509: unable to establish a connection to non-Oracle system
ORA-28507: error in data dictionary view $_class_init
ORA-02063: preceding 2 lines from
ORA-00604: error occurred at recursive SQL level 1
ORA-00942: table or view does not exist
Resolution
Run scriptlocated in the $ORACLE_HOME/rdbms/admin directory as SYSDBA user to create theviews and tables required by the gateway:
connect / as sysdba
commit;
ORA-600: internal error code, arguments:[opidsa-2], [24338]
Problem:
Running an"internal table" query like select * from all_catalog,select * fromuser_tables ...
fails with error:
SQL> selectto_char(sysdate, 'dd-mm-yyyy hh24:mi:ss') from dual@hsmsql;
ERROR at line 1:
ORA-600: internal error code, arguments: [opidsa-2], [24338],
[ORA-24338:statement handle not executed], [], [], [], [], []
ERROR at line 1:
Other queries fail immediately or after some time with error
ORA-28509: unableto establish a connection to non-Oracle system
ERROR at line 1:
ORA-2068: following severe error from HSMSQL
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_D
ATA=(SID=hsmsql)))
Resolution:
IfINBOUND_CONNECT_TIMEOUT is set gateway connections might be dropped due todefect 6242391.
Set
INBOUND_CONNECT_TIMEOUT_=0
Set
SQLNET.INBOUND_CONNECT_TIMEOUT=0
in
Failed to load ODBC library symbol:/<path>/(SQLAllocHandle)
Problem:
Another very commonissue related to all ODBC based gateways is: Failed to load ODBC librarysymbol: /<path>/(SQLAllocHandle)
Resolution:
When the error israised using DG4MSQL, DG4SYBS, DG4IFMX or DG4ODBC please make sure the listenercontains the ENV parameter with the correct LD_LIBRARY_PATH/LIBPATH setting.
If the parameter is already added when using DG4ODBC please check the word sizeof the ODBC Driver Manager library:
file/<path>/
It must matchthe word size of the DG4ODBC executable - on platforms where DG4ODBC is 64bit(you can check that also using the Unix "file" command
file$ORACLE_HOME/bin/dg4odbc
the ODBCdriver Manager has to be 64bit and where the DG4ODBC executable is 32bit youhave to use a 32bit ODBC Driver Manager.
Driver's SQLAllocHandle on SQL_HANDLE_HENVfailed
Problem:
select * from GTW_EMP@dg4odbc;
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_HENV failed
{IM004}
Solution:
check the word size of the ODBC DriverManager. For 64bit DG4ODBC the ODBC Driver Manager and the ODBC Driver has tobe 64bit for 32bit DG4ODBC both need to be 32bit.
Ask Questions, Get Help, And Share YourExperiences With This Article
Would you like to explore this topicfurther with other Oracle Customers, Oracle Employees, and Industry Experts?
Click here to jointhe discussion where you can ask questions, get help from others, and shareyour experiences with this specific article.
Click here to join thediscussion where you can ask questions, get help from others, and shareyour experiences with this specific article. Discover discussions aboutother articles and helpful subjects by clicking here to access the main MyOracle Support Community page for Database Gateways.
Sharon
2014.06.12
------------------------------------------------------
转自MOS