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]
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?
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 RelatedCan 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.
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 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 RelatedI 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.
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.
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 RelatedWe'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 RelatedI 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 RelatedI 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
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.
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)
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.
Do I need to install the Oracle client in order to use putty to connect to the database?
View 11 Replies View RelatedWe 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?
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.
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.
can i start multiple listeners in one oracle database ?
View 7 Replies View RelatedBut 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.
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 RelatedWe 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
where can i download the oracle gateway for sql server? i can't find the download web site .
View 4 Replies View RelatedI 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 are having major differences in performance when accessing an Oracle 11gR2 database from two different environments.
I won't sadden you with the gory details of the full application but I will use an indicative example of our problem.
We have SQL Developer in both environments. In each of these SQL Developer instances is a defined a connection to the same database on another machine. So there is the PC looking at a database and a virtual instance looking at the same database.
Selecting the '+' sign next to 'tables' for that connection, on the PC the list of four tables comes back in 4 seconds but on the virtual instance the list of the same four tables comes back in 1 minute and 50 seconds.
This sort of timing issue is repeatable for all accesses to the Oracle database on the third machine.
how can i run dml statement on the oracle transparent gateway for sql server ,such as insert ,update,delete.
View 4 Replies View RelatedWe 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.
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
---------------------------------------------------------------------------------------------------------------------
$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/sqlnet.ora
[Code]...
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