Networking And Gateways :: Connecting Oracle To SQL Server Via Database Link?
May 24, 2011
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]
To connect the above i made the following changes
$ORACLE_HOME/hs/admin/inithsodbc.ora
---------------------------------------
HS_FDS_CONNECT_INFO = data_extract
HS_FDS_TRACE_LEVEL = 0
$ORACLE_HOME/network/admin/tnsnames.ora
---------------------------------------
sqlserver.db =
(DESCRIPTION =
[code]....
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?
View 10 Replies
ADVERTISEMENT
Jul 13, 2012
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.
View 39 Replies
View Related
May 20, 2011
I'm trying to connect Oracle 11g database from Oracle 9i database, by creating dblink on Oracle 9i database. But my session got hang while i perform this. Here i'm giving the steps i followed on my both the database. Here Oracle 9i database is my source db and Oracle 11g is my target db.
Following steps/setting on Oralce 11g Database
SQL> SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE
SQL> alter user SSA identified by SSA;
User altered.
Password File recreted on Oracle 11g Database Server
[oracle1@dev11g dbs]$ orapwd file=/backup/ora11gsoft/odb/OH1/dbs/orapwwbdata
ignorecase=n password=dev entries=5
Following steps performed on Oralce 9i Database
DROP DATABASE LINK "SSA11G.SSA_DB1.WBTEA.COM";
CREATE DATABASE LINK "SSA11G.SSA_DB1.WBTEA.COM"
CONNECT TO SSA
IDENTIFIED BY SSA
USING 'DEVDATA.DEV11G';
Database link created.
SELECT * FROM SSA.SSMASTER@SSA11G;
And the hangs for ever.
View 4 Replies
View Related
Mar 18, 2010
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.
View 6 Replies
View Related
Feb 14, 2011
i want to connect to a database that is out side my network and my application is running on forms 6i and database version is 10g .
View 2 Replies
View Related
Mar 19, 2010
Can currently connect to a 9.2.0.1 database using Oracle Developer 6.0 with the following connect string:
C:orantBINifrun60.EXE GMS ops$cannonc/cannonc@GMS3B.TEST.COM[/email]
I don't believe it is using the TNSNAMES.ora entry for this, because when I take it out of there it still connects. I create a 10.1.0.5 database and copied the tables,etc to there. But I'm having problem connecting, getting "ORA-12154:TNS Could not resolve service name". Tried the following:
C:orantBINifrun60.EXE GMS ops$cannonc/cannonc@NJZTEST[/email]
C:orantBINifrun60.EXE GMS ops$cannonc/cannonc@NJZTEST.TEST.COM[/email]
I did add the entry for the 10G database to TNSNAMES.ora:
NJZTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = njzainettest.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = njztest)
)
)
I new to this, but my guess is the other entry had the full path name..and this SERVICE_NAME does not. But then again I don't believe it looks at the TNSNAMES file. This NJZTEST is the 2nd instance on the NJZAINETTEST machine.
View 6 Replies
View Related
Feb 15, 2011
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.
View 1 Replies
View Related
Jul 5, 2012
I am trying to create a database link from the 11g database to the 10g database using:
create database link ORCL10R2 connect to <username10g> identified by <password10g> using <db10g>;
It Returns
Database link created.
select sysdate from dual@ORCL10R2 returns error:
ERROR at line 1:
ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA
what changes I need to make to tnsnames and listener at both servers.
View 8 Replies
View Related
Dec 5, 2011
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?
View 2 Replies
View Related
Aug 8, 2006
I know how to create a database link between two oracle servers, but problem is that SID is same for both servers, Is there any other way to connect two servers without using only 'SID' ..here are TNSNAMES.ORA FILE i HAVE
D.w=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL = TCP)
(HOST=ff.ffc.pic.com)
(PORT = 1521)
[code].......
Is there any other way rather that this
Create database link a connect to username identified by password using 'SID';
Any alternative like using SID.A.w or how I can differentiate these two SID s.
View -1 Replies
View Related
Aug 15, 2010
Im trying to connect a benchmarking application (benchmark factory) from my windows 32bit laptop to Oracle on my linux desktop.
When connecting I am supplying the following details: (im getting the error: ORA 12170 : TNS connect timeout occurred)
User/Schema : matt
Password: ****
Host: 192.168.1.9
Port: 1521
SID: orcl
Connect as : SysDBA
Connect Using: Instant Client (selected from a drop down)
----------------------------------------------------
when i use the following command:
tail -100 /home/matt/app/matt/diag/tnslsnr/localhost/listener/alert/log.xml
i get these results:
[matt@localhost ~]$ tail -100 /home/matt/app/matt/diag/tnslsnr/localhost/listener/alert/log.xml
host_addr='127.0.0.1'>
<txt>15-AUG-2010 23:26:29 * service_update * orcl * 0
</txt>
</msg>
<msg time='2010-08-15T23:26:36.708+01:00' org_id='oracle' comp_id='tnslsnr'
[code]....
I have been told client is not getting to the listener because listener is only listening on 127.0.0.1.
unless & until DB server is configured with a routable IP#, no remote client will succeed.
View 7 Replies
View Related
Apr 22, 2010
i got to find out the users hostname who are connecting to database through remote,i tried with v$session bt it is showing null values in the terminal column.how to find out ?
View 8 Replies
View Related
Feb 15, 2010
Database version:10.2.0.3
Operating System:Microsoft Windows XP
I have two database TEST and TEST2 on same windows machine. In TEST database i have created one database link that access one schema in other TEST2 database. but when i passed select command to access object in TEST2 through Database link it is hanging indefinitely.
command that i have used to create Database link: create database link scott connect to scott identified by tiger using 'TEST2'
This is sqlnet.ora Entry:
SQLNET.AUTHENTICATION_SERVICES = (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES)
TNSNAMES.ORA entry:
TEST2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = SSIPL-LAPTP-052)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test2)
[code]....
View 3 Replies
View Related
Mar 11, 2010
I am trying to create a database link using this syntax:
CREATE PUBLIC DATABASE LINK remote
USING 'remote';
where 'remote' is the service name. It creates it successfully giving the message "External database Created"
Note: I am doing using TOAD
But when i am trying to query something like
select * from scott.employee@remote
it gives an error:
ORA-12154: TNS:could not resolve the connect identifier specified
But the 'remote' db entry exists in the tnsnames.
View 14 Replies
View Related
Mar 3, 2011
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
3. Created listener.ora entry:
LISTENERGMS =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=WALL-ALIGN-01.testsystem.com)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
[code]....
View 2 Replies
View Related
Oct 13, 2010
We are currently replacing all public database links with private db links in our prod environment. The requirement is
1) private db link name should be same as public
2) Need to create and test out private db links before we can drop public db links.
How can we test out private db links while public db links are still there with the same name?
when I execute SELECT * FROM DUAL@DBLINK from user who created this private db link, which db link it will be using- public or private?
View 9 Replies
View Related
Sep 29, 2011
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.
View 2 Replies
View Related
Aug 10, 2012
We have two schemas which earlier used to be separate databases. There were DB links created to access the objects from one schema to the other schema when they were separate databases.
Since now they are just 2 separate schema with in the same Database, we would like to remove the DB Links and create synonyms to access those objects. These DB links were used in code in many places, it is becoming tough to find a way to implement this.
how to find all the objects that are using these objects or all the places these DB likns were being used.
View 1 Replies
View Related
Mar 26, 2013
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?
View 2 Replies
View Related
Jul 15, 2010
I have one query related to database connection from server to client.I have one database in the server and I want to connect from client.I set the tnsnames in client system as follows.
BAM240T_EARTH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = 10.154.119.103)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = BAM_240T.ipdc.com)
)
)
The database name is : BAM_240T.ipdc.com
Sqlnet.ora entry is
NAMES.DIRECTORY_PATH= (TNSNAMES)
NAMES.DEFAULT_DOMAIN= ipdc.com
When I connect to server I m getting the following error.
$ tnsping BA240T_EARTH
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 15-JUL-2010 15:45:35
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/opt/oracle/product/10.2.0/db/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
View 16 Replies
View Related
Jun 28, 2006
I am facing difficulty in connecting Oracle 10g client to Oracle 10g database. Both the machines are in windows xp with service pack-2. The following observations are made in server machine
1. The loopback test of the server is successful.
2. One net service name created in the server end.
3. Database connection is successful with above service name.
4. tnsping <server ip address> is successful
The tnsnames.ora file of the server is
# tnsnames.ora Network Configuration File: C:oracleproduct10.2.0db_1
etworkadmin nsnames.ora
# Generated by Oracle configuration tools.
CONTRACT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 80.0.28.42)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cam)
[code]........
the sqlnet.ora file of the server is
sqlnet.ora Network Configuration File: C:oracleproduct10.2.0db_1NETWORKADMINsqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.INBOUND_CONNECT_TIMEOUT = 30
The listner.ora file of the server is
listener.ora Network Configuration File: C:oracleproduct10.2.0db_1NETWORKADMINlistener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 80.0.28.42)(PORT = 1521))
)
When I am connecting the 10g database with 10g client I am getting ORA-12170 error.
Attempting to connect using userid: nss
The test did not succeed.
ORA-12170: TNS:Connect timeout occurred
There may be an error in the fields entered,or the server may not be ready for a connection. How to configure this connectivity.
The client side tnsnames.ora file is as given below
# tnsnames.ora Network Configuration File: C:oracleproduct10.2.0client_1NETWORKADMIN nsnames.ora
# Generated by Oracle configuration tools.
CONTRACT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 80.0.28.42)(PORT = 1521))
)
[code]......
The sqlnet.ora file of the client side is
sqlnet.ora Network Configuration File: C:oracleproduct10.2.0client_1
etworkadminsqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
View 13 Replies
View Related
Nov 17, 2011
I have created two database one for production and another one for test.I created both database in shared_server mode(by using dbca),after than I did not change anything.My problem is when i connect to test database from outside lan through static ip its working well,but when i connect to production database which is not connected. I checked and compared two databases,then I find there are some changes in dispatcher and mts_ dispatcher parameters.And I used this query to check the shared_server status,
ie.select * from v$shared_server,
the output of this query is attached with this post.
View 8 Replies
View Related
Jul 5, 2011
Do I need to install the Oracle client in order to use putty to connect to the database?
View 11 Replies
View Related
May 23, 2011
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?
View 1 Replies
View Related
Mar 11, 2013
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.
View 3 Replies
View Related
Feb 6, 2011
setup the connection between oracle developer and database.
i found the file called tnsnames.ora however, i cant open it. i dont know if i need to choose a program to open it or download extension for it.
View 12 Replies
View Related
Dec 6, 2011
can i start multiple listeners in one oracle database ?
View 7 Replies
View Related
May 17, 2011
But i do a lot of work-arounds to make sure the installation does not make corrupt my OracleAgent,OracleService and OracleDataGatherer. If i just let the installation do its thing i have problems with my libraries, and can't start anything.
The errors are : Procedure entry point 'BlaBla' could not be located in the Dynamic Link library 'AnyName'.
I notice that i have 2 versions of the libraries in OracleHomeBIN (one version 8, one version 11). The programs that start the OracleAgent,Service and DataGatherer call the old libraries but expect to find values that can be found only in new libraries.
View 1 Replies
View Related
Mar 11, 2013
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?
View 1 Replies
View Related
Apr 2, 2013
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
View 2 Replies
View Related
