Networking And Gateways :: ORA-28500 - While Selecting SQL Server Data In Oracle (HSODBC)
Sep 7, 2013
I'm facing ORA-28500 error while configuring HSODBC to SQL Server.
Below is the complete information related to listener.ora, sqlnet.ora and tnsnames.ora files. Listener status is showing dg4msql instance successfully started.
I guess I'm facing some issue while creating DB link.
In Oracle 11g Release 11.2.0.1.0 we need to create DB link as below
CREATE DATABASE LINK "DBLNK_ETM_PRODUCTION"
CONNECT TO "USER_NAME" IDENTIFIED BY VALUES 'Password'
USING 'ServerIPAddress:PortNumber/SID';
How to create DB link to SQL Server.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE11.2.0.1.0Production"
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
CREATE PUBLIC DATABASE LINK DBLNK_1 CONNECT TO "manoj" IDENTIFIED BY "mypassword" USING 'SQLDB' ;
SELECT * FROM MyTable@DBLNK_1 ;
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC SQL Server Driver][libssclient24]General network error. Check your network documentation.
{08001,NativeErr = 11}[Oracle][ODBC SQL Server Driver][libssclient24]ConnectionOpen (Name or service not known()).
{01000}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
ORA-02063: preceding 2 lines from DBLNK_AVL_CLUSTER2
28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
*Cause: The cause is explained in the forwarded message.
*Action: See the non-Oracle system's documentation of the forwarded
message.
Error at Line: 5 Column: 20
We want to configure HSODBC on HP-UX 11.23 for Oracle Database 10g R2 in order to establish a DB link from Oracle to Netezza.We have installed the ODBC Drivers too..Currently we have not done any configurations and we go via an AIX box to establish a link.We want to get rid of the AIX box and instead establish directl link towards netezza from HP-UX Box.
Does Oracle support have some guidelines or steps to setup HSODBC on Oracle database 10g r2 residing on HP-UX 11.23 towards Netezza?
Attempting to create Database Link which talks to SQL Server 2008R2 system
Database: 11.1.0.7 Oracle Standard Edition Server: Windows Server Enterprise, Service Pack 2 (2008)
I do not have the Oracle Gateway product installed, as this was not configured on my working 10G system.
I tried configuring the link with both the SQL Server ODBC Driver (GMSRES) and the SQL Server Native Client 10.0 (GMSGAS). Returning errors when SQL command is executed.
Everything tests out fine (tnsping, listener configured OK, test connect in Database Link setup). When SQL statement is executed it fails.
Setup:
1. Created ODBC Driver (GMSRES). This tested successfull. Created SQL Native Driver (GMSGAS). This tested successfull.
2. Created initiGMSRES.ora file C:Oracleproduct11.1.0db_1hsadmin folder.
HS_FDS_CONNECT_INFO = GMSGAS HS_FDS_TRACE_LEVEL = OFF HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15 HS_FDS_CONNECT_INFO = GMSRES HS_FDS_TRACE_LEVEL = OFF HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P15
I want to connect ORACLE-11gR2 that is on linux with an MS sql server. DO i need to use some third party drivers like easy soft ? Can i configure the database gateway without any third party drivers ?
Secondly if my oracle is on linux, can i configure the gateway on a seperate windows machine(that will connect to linux oracle and ms sql server) as i have found the gateway installation setup only for windows version.
Currently we are loading data from oracle to sql server through oracle 11g gateway. during running scripts most of the queries takes a long time to complete. Because of this performance issue, we want to configure oracle golden gate to move data from oracle to sqlserver. whats the difference between oracle gateway & oracle golden gate? both are performing same functionality?
We are having oracle 9i as database and migrating to sql server 2008. Our oracle 9i database connects to another oracle databases and these databases are having different oracle version(9.2.0.8.0, 9.2.0.7.0,10.2.0.5.0,11.2.0.2.0,9.2.0.6.0,10.2.0.4.0,8.1.7.4.0 )
I do have two queries :
1) how to connect from Sql server 2008 R2 to different Oracle databases
2) How will other oracle databases connect to Sql server 2008 R2 database
connecting two oracle database in two different server.I installed Oracle 10g XE in both system.Both system act as Server.I need to connect both server using database link concept.
how to use the tnsnames,ora and listener.ora file for connecting both system.
Connecting Oracle to SQL Server via database link.
I am trying to connect from Oracle 10G to a SQL Server database. I have looked at the manual and i will admit that i am finding the documentation quite difficult to follow. There seems to be several options to use but none of the documentation describe how each option works.
As an example, i have been given the following information on the database i need to connect to (i.e. the SQL Server database)
- Username - Password - Database Name [lets assume the database name is data_extract]
Note: In listener.ora, i only added the last SID_DESC entry. I then went on and created the database link as shown below
create database link sqlservdb using 'sqlserver.db';
When i try to access a table i get the following error
sqlplus> select * from TESTTABLE@sqlservdb; select * from TESTTABLE@sqlservdb * 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 ORASQLSERVER
A couple of things i am not sure of:
- Where do i specify the username and password to access the sql server from the oracle db to the sql server db
- Having read around on the internet, i can see some people refering to a DSN datasource. I am told that the information that i have is all i need. Is this correct or do i need something else?
- The sid i specified in inithsodbc.ora and tnsnames.ora is actually the sql server database. Is this correct?
I have 9i RDBMS Software and RHEL 4. I want to install a Oracle Names server. I install RDBMS.But Where I run netmgr and want to configure and start Oname server it says.
$ORACLE_HOME/bin/names file not exists.
How Can I configure OracleNames Server using Oracle 9i On Linux.
I was trying to connect to SQL server from Oracle using Oracle heterogeneous services (HS).But I found that I do not have HS installed on my system. It was mentioned in Oracle website that we need to run 'caths.sql' script under the path $ORACLE_HOME/rdbms/admin.
But I do not have any such folder(rdbms) in my $ORACLE_HOME.What does it mean? And how can I install HS alone on my system(Since I already have oracle)
We got a big problem that we have a db link to connect to sql server(big 5).
In original setting, there is no Chinese characters in the data. We simply grab the data from sql server and do not have any issue. But now we have data with Big5. When we import the data into our Oracle DB, which uses UTF8, all the text becomes "?
Changing character set in DB is not possible. We need to convert the text into unicode format, or allow oracle/sql server to convert it automatically.
We've been administering a multiple instance production dB server with 3 different versions of Oracle installed.Currently, each of Oracle version had corresponding listener.Oracle 9i had 2 instances, 10g 6 instances & 11g 2 instances also.how I can integrate this 3 listeners into 1.
My client get the error Ora-12514 when he tries to connect to the db.Well;
1) we restart the services: "OracleServiceOrcl" and listener but nothing changes 2) We restart the server and nothing changes 3) We checked the dump and he was done on March,26th. No db export yesterday. Is there any link with the clock change of this week-end (location of the server: France)? 4) The lsnrctl services listener command run successfully but it doesn't mention the instance ORCL.
I have a table testing_mview on 10g which do not have any primary key. I have created MV log for it:
CREATE MATERIALIZED VIEW LOG ON testing_mview WITH ROWID; Materialized view log created.
My requirement is 1) if there is an UPDATE/DELETE/INSERT on testing_mview, it should be writen to MATERIALIZED VIEW LOG (this has been achieved) 2) Materialized view testing_mview1 of 11g on server 10.1.4.32 should pull these changes on a scheduled basis ( I have created the database link ORCL10R2 here from 11g to 10 g)
On 11g: SQL> create database link ORCL10R2 connect to omig identified by pswd using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.4.30)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ICS3) ) )';
Database link created
SQL> create materialized view testing_mview1 REFRESH FAST with rowid as select * from testing_mview@ORCL10R2; create materialized view testing_mview1 REFRESH FAST with rowid as select * from testing_mview@ORCL10R2 * ERROR at line 1: ORA-04052: error occurred when looking up remote object SYS.DBMS_SNAPSHOT@ORCL10R2 ORA-00604: error occurred at recursive SQL level 2 ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], [] ORA-06553: PLS-801: internal error [55916] ORA-02063: preceding 2 lines from ORCL10R2
I am having difficulty in making the 32 bit Oracle client connection to an existing 64 bit DB. The 64 bit SQL*Plus will connect just fine, but when trying to configure the 32 bit client I can never get a successful TEST of the connection. It always returns "No TNS Listener". I have tried several things, but may be way off base. To name a couple ...
1) Stopped the 64 bit listener so my 32 bit listener can use Port 1521 ... no success 2) Tried naming the 32 bit listener to LISTENER32 to not conflict with the original LISTENER name ... no success
If there is a documented way to make this work, I would surely like to be pointed to the "how to" of this item. I need this as a commercial application I have that connects to the DB requires the 32 bit client.
I am having 11.2.0.1.0 database with windows2k3 OS. For ORA-609 error i have started server side tracing by adding TRACE_LEVEL_SERVER=16,TRACE_LEVEL_DIRECOTRY=D:TRACE,and DIAG_ADR_ENABLED=off in sqlnet.ora file and i reload the listener.
Now i want to stop tracing so i have tried TRACE_LEVEL_SERVER=off but it wouldnot work. After that i remove TRACE_LEVEL_SERVER=16,TRACE_LEVEL_DIRECOTRY=D:TRACE from sqlnet.ora file and reload the listener but trace file generation is going on D:TRACE. stop server side tracing.
When i connect from client to server the client looks up for the host address and port number in tnsnames.ora and connect to it via listener.ora in server.Where does pga and sga come into picture here?
Also what is the role of sqlnet.ora in client?What will happen if i specify a different port for the instance in tnsnames.ora and listener.ora?
I am trying to connect to Oracle 11g using ODBC.I created a data source name from Admistrative Tools->ODBC Data Sources.I selected Oracle in OraClient11g_home1 as the driver. I tested the connection and it was succesful.I tried to connect from visual studio. I used the Data Source as Microsoft ODBC Data SOurce. Selected my DSN from drop down (LORAC). I gave the user name and password and got the followign errors:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
This is my tnsnames.ora located in C:oracleproduct11.1.0client_1 etworkadmin directory. # tnsnames.ora Network Configuration File: C:oracleproduct11.1.0client_1 etworkadmin nsnames.ora # Generated by Oracle configuration tools. LORAC = (DESCRIPTION =
[code]...
When i do tnsping LORAC, i get OK. When I do tnsping of the other service name i get Failed. However when I try to connect to that service from Visual studio I get a differnt set of errors:
ORA-12154: TNS:could not resolve the connect identifier specified
I had assumed that as the test connection from ODBC Administrator had worked I would be able to connec to my database from viusal studio. Is there something wrong in my tnsnames.ora? The content or its directory location?
I have an Access DB which I'm using to connect to an Oracle DB at work. There are several Oracle DB's that I can connect to using Access but this one I'm not able to connect to. I get the Oracle error ORA-25153 Temporary Table Space is empty.
I don't have the ability to make changes to the Oracle DB only to read and query.
We're deploying a new product release however this build uses Oracle 11G. We want to perform a side by side install for testing purposes. These are low usage systems so installing another Oracle build will not hinder the system.
We intend to test for a couple of weeks and migrate data, then uninstall the 10G. I'm in the process of installing 11G now, the 10G is up and running. I don't want to impact the 10G install. I'm installing 11G in /home/oracle/oracle11G directory. I would like to use 1 Listener.ora file. I've broken down the install bellow.
10G SID "processing" Oracle home /home/oracle/oracle10g /mnt/Oracle/Oracle10GData /mnt/Oracle/OracleRedoLog
11G SID "processing11G" Oracle home /home/oracle/oracle11g /mnt/Oracle/Oracle11GData /mnt/Oracle/Oracle11GRedoLog
Do I need to specify the databasename for Processing2? Also the front end boxes (managers) are two different servers so Processingweb1 and processingweb2.
I have a JBOSS aplication server connect to database. When I tested on local, everything was ok. But when i test on real system , have a message "connection time out" on log file of app server while Oracle db still active. I viewed OEM, the system is executing the query :
"SELECT a.path songFilePath, a.BEAT beat from vp_song a, mst_my_album b "+ "where "+ "a.BEAT = b.BEAT "+ "and b.my_album_id IN "+ "(select my_album_id from (select my_album_id, ROW_NUMBER() OVER (order by my_album_id asc) "+ "R from mst_my_album where msisdn = ?) WHERE R BETWEEN ? and ?)";
I have client and database on same machine, when I will connect to the instance , it will use IPC, suppose If I will use connection string then which process will handle this request?
I have recently installed Oracle Express 10g Server on my local PC. I can successfully connect to this while bypassing the TNSNAMES from SQLTools, I can't however connect to this from Forms 10g or while using SQLTools with TNSNAMES. How do I configure my TNS names to use this server?
