Ora 28545 error diagnosed by net8 when connecting to an agent ora 02063

Dear all,

This question has been asked many times in this forum, and I have gone to many threads in this forum, but unfortunately i failed.

I want to connect to MySQL database from oracle. I have the following configurations.

1. Oracle database enterprise edition 12c  (12.1.0.2.0) 64bit on Windows server 2012 64bit
2. i have MySQL 5.6.11 on windows 7 64bit
3. i have downloaded ODBC driver from oracle delivery site (MySQL Connector/ODBC 5.3.4 MSI for Windows x86 (64bit))
4. i install the ODBC on my oracle server, a full installation which install the Unicode and Ansi versions both.
5. i create ODBC connection for Unicode by going to Control Panel—>Administrator Tools—>ODBC Data Sources (64-bit)—>System DSN. i test it and the test was successful.
6. i configure my Listner.ora, Tnsname.ora and initmysql.ora files as below;

Listner.ora

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
   (SID_NAME = CLRExtProc)
   (ORACLE_HOME = c:appAdministratorproduct12.1.0dbhome_1)
   (PROGRAM = extproc)
   (ENVS = "EXTPROC_DLLS=ONLY:c:appAdministratorproduct12.1.0dbhome_1binoraclr12.dll")
  )
  (SID_DESC=
   (SID_NAME = mysql)
   (ORACLE_HOME = C:appAdministratorproduct12.1.0dbhome_1)
   (PROGRAM = dg4odbc)
   (ENVS=LD_LIBRARY_PATH = C:appAdministratorproduct12.1.0dbhome_1LIB)
   )
 )
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV.hct.org)(PORT = 1521))
      )

Tnsnames.ora

PDBORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV.hct.org)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = pdborcl.hct.org)



  


                       
  




    )
  )
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORADEV.hct.org)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.hct.org)
    )
  )
mysql =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = N410FS02)(PORT = 1521))
    (CONNECT_DATA =
    (SID = mysql)
    )
    (HS = OK)
   )

initmysql.ora ( C:appAdministratorproduct12.1.0dbhome_1hsadmin)

HS_FDS_CONNECT_INFO = mysql
HS_FDS_TRACE_LEVEL = OFF

then i stop and start the listener, the listener status is below

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 23-APR-2015 09:16
:52
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORADEV.hct.org)(PORT=152
1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Produ
ction
Start Date                23-APR-2015 09:13:56
Uptime                    0 days 0 hr. 2 min. 59 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   c:appAdministratorproduct12.1.0dbhome_1networka
dminlistener.ora
Listener Log File         c:appAdministratordiagtnslsnrORADEVlisteneraler
tlog.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ORADEV.hct.org)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "mysql" has 1 instance(s).
  Instance "mysql", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl.hct.org" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.hct.org" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdborcl.hct.org" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully



  


                       
  

tnsping MySQL

TNS Ping Utility for 64-bit Windows: Version 12.1.0.2.0 - Production on 23-APR-2
015 09:17:54
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
Used parameter files:
c:appAdministratorproduct12.1.0dbhome_1networkadminsqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = N410FS02
)(PORT = 1521)) (CONNECT_DATA = (SID = mysql)) (HS = OK))
OK (40 msec)

then i open sqlplus and connect to my pluggable database as sys user and create a public database link for the test schema which i already created in MySQL database.

create public database link mysql connect to test identified by test using 'mysql';

then i try the table1 which is in test schema in MySQL from sqlplus as below

SQL> select * from table1@mysql;
select * from table1@mysql



  


                       
  




                     *
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from MYSQL

i have tried both the Ansi and Unicode versions of ODBC but the same issue.

where is the problem in my configuration?

Thank you.

ORA-28545: error diagnosed by Net8 when connecting to an agent

Dear all,

This question has been asked many times in this forum, and I have gone to many threads in this forum, but unfortunately i failed.

I want to connect to MySQL database from oracle. I have the following configurations.

1. Oracle database enterprise edition 12c (12.1.0.2.0) 64bit on Windows server 2012 64bit
2. i have MySQL 5.6.11 on windows 7 64bit
3. i have downloaded ODBC driver from oracle delivery site (MySQL Connector/ODBC 5.3.4 MSI for Windows x86 (64bit))
4. i install the ODBC on my oracle server, a full installation which install the Unicode and Ansi versions both.
5. i create ODBC connection for Unicode by going to Control Panel—>Administrator Tools—>ODBC Data Sources (64-bit)—>System DSN. i test it and the test was successful.
6. i configure my Listner.ora, Tnsname.ora and initmysql.ora files as below;

Listner.ora

Tnsnames.ora

initmysql.ora ( C:appAdministratorproduct12.1.0dbhome_1hsadmin)

then i stop and start the listener, the listener status is below

then i open sqlplus and connect to my pluggable database as sys user and create a public database link for the test schema which i already created in MySQL database.

then i try the table1 which is in test schema in MySQL from sqlplus as below

i have tried both the Ansi and Unicode versions of ODBC but the same issue.

Источник

ORA-28545: error diagnosed by Net8 when connecting to an agent

I really hate to make another topic about a problem that many people have posted about already, but now matter how hard I try, I can’t resolve the following error:

ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message 65535 ORA-02063: preceding 2 lines from DBLSS.WORLD

I’ve followed this tutorial with the exception of step 7, where I substituted hsodbc for dg4odbc: Database link from Oracle to SQL Server

Also, the database link worked before the databasename of the SQL Server had to change, so I know the right programs/drivers are installed.

MSSQL Server: MS10, database: SXSS

Oracle Server: OR39, database: SXSO

3. Test the ODBC drivers to ensure that connectivity is made to the SQL Server database.

On the oracle database server OR39:

Start -> Data Sources (ODBC) -> System DSN -> Name=SXSS, Server = MS10, change default database=SXSS

Test Data source results:

Microsoft SQL Server ODBC Driver Version 06.01.7601

Running connectivity tests.

Verifying option settings

Disconnecting from server

TESTS COMPLETED SUCCESSFULLY!

4. Ensure that your global_names parameter is set to False.

SQL> select name from v$database;

SQL> show parameter global_names

5. Configure the Oracle Heterogeneous services by creating an initodbc.ora file within the Oracle database.

In %Oracle_Home%/hs/admin I have the file initSXSS.ora:

also initdg4odbc.ora which I didn’t modify, and an extproc.ora which only says

7. Modify the Listener.ora file.

this is part of my listener

(ADDRESS = (PROTOCOL = TCP)(HOST = OR39)(PORT = 1521))

(SID_DESC = (SID_NAME = SXSS) (ORACLE_HOME = F:oracleora112) (PROGRAM = dg4odbc)

8. Modify the Tnsnames.ora file.

(ADDRESS= (PROTOCOL=TCP) (HOST = OR39 )(PORT=1521))

(CONNECT_DATA= (SID = SXSS))(HS=OK))

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION=[..])

9. Reload the listener on local Oracle database

10. Create a database link on the local Oracle installation that accesses the heterogeneous connection, which, in turn, connect to SQL Server.

CREATE PUBLIC DATABASE LINK «MICRO.WORLD» CONNECT TO «ORUSER» IDENTIFIED by «******» USING ‘SXSS’

Then I go to management studio, to my SXSO database, to Database Links, click on MICRO.WORLD, click on test and then I get the ORA-28545.

It had worked before following the same manual, but now it doesn’t seem to work anymore. Am I doing anything wrong? Or am I missing a step?

Answers

If you followed the doco then the only avenue left is creating an SR.

You may need to post your config files in their entirety* for us to better assist .

Message was edited by: RobbR * Masking sensitive data

The error ORA-28545 error you get is related to a configuration issue but the files you posted look good.

You have stated that you reloaded the Oracle listener — did you use the reload or the stop/start command?

The tnsping shows that there is a listener service running and serving the 1521 port. Unfortunately tnsping does not check, if there is a SID service. So could you please post the listener status output of your listener («lsnrctl status» executed in the command line window).

Also please provide the output of «F:oracleora112bindg4odbc» executed in the same command line window.

So, the weirdest thing just happened. On thursday I made the database link and when it still didn’t work after 1.5 workday, I wrote the opening post in this topic and then went home, frustrated. Monday morning I go to my work again, check this topic (Thanks for the replies!!) and before I was going do the any of the actions proposed here I decided to check if the database link was working, just in case. Guess what: It’s working now! The only thing that happened between Friday and Monday was that the server was rebooted (which happens once a month) . So I guess that is what fixed my problem. While that seems like a rigorous solution for such a «simple» problem, especially when you have an SLA which state the server may only be down once a month, I’m happy that it’s working now.

Well, you stated the server was rebooted and earlier you stated you reloaded the listener. I now assume that you have used the listener command reload rather then stop and start which is done explicit or when rebooting the server.

Источник

ORA-28545 ORA-02063

I’m installing DG4MSQL to connect to SQL server database. I installed the Oracle Gateway 11.2 on a server(server name — aris) where SQL Server 2005 is also running. Below are the config files for the gateway
Created DBLINK from Oracle database:

create public database link to_sqldb connect to «arisuser1» identified by «user1pwd»
using ‘dg4msql’;

SQL> select * from [email protected]_sqldb;
select * from [email protected]_sqldb
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from TO_SQLDB

Any help please.Thanks

Edited by: 817622 on Dec 16, 2010 2:58 PM

Answers

root cause of the 28545 is your listener — it does not support any service see the message: The listener supports no services

A listener configured for the SID dg4msq commonly states:
Service «dg4msql» has 1 instance(s).
Instance «dg4msql», status UNKNOWN, has 1 handler(s) for this service.

=> Are you sure the listener file you’ve posted is located in the directory: E:product11.2.0tg_1networkadminlistener.ora? For me it looks like you’ve edited the sample listener.ora file from E:product11.2.0tg_1dg4msqladmin.

By default the SQL+Net listener is reading the listener file from E:product11.2.0tg_1networkadmin, so please make sure you’ve edited this file. Then STOP and START the listener again.

You are right. I moved the listener.ora to Oracle Gateway home/network/admin and it worked. Thanks so much. But I get a different error now when I execute the select..

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[aris]/aris/sqldb
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=arisuser1
HS_FDS_RECOVERY_PWD=user1pwd

create public database link to_sqldb connect to «arisuser1» identified by «user1pwd»
using ‘dg4msql’;

ERROR at line 1:
ORA-01017: invalid username/password; logon denied
[Oracle][ODBC SQL Server Driver][SQL Server]Login failed for user ‘arisuser1’.
<28000,NativeErr = 18456>[Oracle][ODBC SQL Server Driver]Invalid connection
string attribute <01s00>
ORA-02063: preceding 2 lines from TO_SQLDB

arisuser1 is the administrative account we use and the server name is aris.. Please help..

Thanks.. Our applications use windows authentication to connect to SQL Server databases.. Do we need to start using SQL Server authentication to use DG4MSQL? Or just create a sql server user for DG4MSQL and we can keep our windows authentication for the applications.. sorry I’m confused..

Thanks so much. You have been really helpful..

I’m using sql server authentication and get this error..

initdg4msql.ora in E:product11.2.0tg_1dg4msqladmin folder:

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[aris]/aris/sqldb
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=sqluser
HS_FDS_RECOVERY_PWD=sqluser
create public database link to_sqldb connect to «sqluser» identified by «sqluser»
using ‘dg4msql’;

ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access
denied. <08001,NativeErr = 17>[Oracle][ODBC SQL Server
Driver][DBMSLPCN]ConnectionOpen (Connect()). <01000,NativeErr =
53>[Oracle][ODBC SQL Server Driver]Invalid connection string attribute <01s00>
ORA-02063: preceding 2 lines from TO_SQLDB

‘sqluser’ has the db owner permissions in SQLDB database.. Please help

Источник

ORA-28545: error diagnosed by Net8 when connecting to an agent error URGENT

I’ve to setup and use SQL Server 2K db from Oracle10g 10.1.0 db. Basically, i’ve to
periodically PULL data from oracle to sql server 2k

Have done the necessary setup as specified in Oracle docs.

1. System DSN: MYSQLSERVERDSN

SID_LIST_LISTENERMYSQLSERVERDSN=
(SID_LIST=
(SID_DESC=
(SID_NAME=MYSQLSERVERDSN)
(ORACLE_HOME = d:oracleproduct10.1.0db_1)
(PROGRAM=hsodbc)
)
)

Did i miss something here?

I started the listener by

c:> lsnrctl start LISTENERMYSQLSERVERDSN. it started well and
I tried tnsping, that is also working. Then,

Did i miss something here? Coz when trying to connect to the tables in
SQLSERVER2K, get following error:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 — Production
With the Partitioning, OLAP and Data Mining options

SQL>create database link mysqlserverdsn connect to xx identified by xxxx using ‘MYSQLSERVERDSN’;

Database link created.

SQL> desc [email protected];
ERROR:
ORA-28545: error diagnosed by Net8 when connecting to an agent
NCRO: Failed to make RSLV connection
ORA-02063: preceding 2 lines from MYSQLSERVERDSN

I’m not able to figure out where the error lies.

Any inputs on this?? please help me, I am in urgent.

Hi I think you have to change your tns using .world after the tns name connection.

regards,
Joao Lanaro

Check the tnsnames.ora in the host you have to write the IP of oracle server.

MYSQLSERVERDSN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = IP ORACLE SERVER )(PORT = 1522))
)
(CONNECT_DATA =
(SID = MYSQLSERVERDSN)
)
(HS = OK)
)

28545 error is a configuration issue.

— please have a look at the tnsnames.ora file and make sure each line except of the tns alias starts at least with 1 space.
According to the posting all further lines start at position 1. but it might be only a
copy/paste issue.

— drop the database link and recreate it. SQL Server usernames and passwords are case sensitiver and thus need to be surrounded by double quotes like:
create database link mysqlserverdsn connect to «sa» identified by «xxxx» using ‘MYSQLSERVERDSN’;

— please provide:
lsnrctl status LISTENERMYSQLSERVERDSN
— please provide tnsping MYSQLSERVERDSN
— please provide: d:oracleproduct10.1.0db_1binhsodbc

ERROR:
ORA-28545: error diagnosed by Net8 when connecting to an agent
NCRO: Failed to make RSLV connection
ORA-02063: preceding 2 lines from MSQL2K

pour cette erreur dans mon cas, il est due au mauvias chemin de orcale_home dans le fichier listener.ora en effet j’ai mis (ORACLE_HOME = d:oracleproduct10.1.0db_1) au lieu de (ORACLE_HOME = c:oracleproduct10.1.0db_1) (oracle est installé sur partition C: et nod )

extrait de listener.ora

LISTENER_MSQL2K =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=T100266681002)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER_MSQL2K =
(SID_LIST=
(SID_DESC=
(SID_NAME=MSQL2K)
(ORACLE_HOME = c:oracleproduct10.1.0db_1)
(PROGRAM=hsodbc)
)
)

extrait de tnsnames.ora
# tnsnames.ora Network Configuration File: C:oracleproduct10.1.0db_1NETWORKADMINtnsnames.ora
# Generated by Oracle configuration tools.

PROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 128.0.0.152)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD)
)
)

ORCLMIROIR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.15.20)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

ORCLS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = Server-OscarV4)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ORCL)
)
)

ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = T100266681002)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

# HETEROGENEOUS SERVICES ###############################################
MSQL2K =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = T100266681002)(PORT = 1522 ))
)
(CONNECT_DATA =
(SID = MSQL2K)
)
(hs = ok)
)

Источник