Networking And Gateways :: Database Link From 11g To 10g?
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
ADVERTISEMENT
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
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 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
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
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 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
Nov 2, 2011
I installed Oracle Express on my Windows 2003 Enterprise server. And right off the bat I cannot connect to it via command line. I have googled and read many sites, but am still getting the same error, no matter what I do. I am newbie again (was a DBA in 1990...when I used to have the same error and fix it in seconds...not sure why this problem still lingers in Oracle) so most likely I am overlooking something. Below are my tnsnames.ora and liserner.ora files
tnsnames.ora
XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myservername.domainname.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)
[code]....
# 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)
View 19 Replies
View Related
May 30, 2010
How many listener we can have in one database? How many users can be support by one listener?
View 3 Replies
View Related
Nov 22, 2010
i want to restrict particular ips ,for that i specified
TCP.VALIDNODE_CHECKING= YES
AND
TCP.INVITED_NODES=(IPADDRESS)
in sqlnet.ora file but whenever i connect to database from the particular specified IPADDRESS , the database allows the connection to happen ( doesnot restrict it),
View 1 Replies
View Related
Jul 14, 2010
i have a inventory system and trying to connect remote database one by one throught two static ips. If first ip is not ping then go to 2nd ip only when open the login.fmx page
following examples are my tnsnames.ora
IP1.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 117.65.48.12)(PORT = 1521))
(CONNECT_DATA = (SID = ORCL))
IP2.WORLD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 117.65.48.13)(PORT = 1521))
(CONNECT_DATA = (SID = ORCL))
i am not understand what i write the code on-logon trigger
View 8 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
Feb 1, 2012
I am getting beloww error very frequently in database log and not able to connect to the database.
************************
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
Time: 01-FEB-2012 18:00:34
Tracing not turned on.
Tns error struct:
ns main err 12535
TNS-12535: TNS:operation timed out
ns secondary err 12606
nt main err 0
nt secondary err 0
nt OS err 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.179.1.24)(PORT=65503))
WARNING: inbound connection timed out (ORA-3136)
Wed Feb 01 18:00:44 2012
*****************
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
Jun 27, 2011
am getting this message when am connecting to a database :
ora-12519:Message 12519 not found;product=RDBMS80 facility = ORA
am not getting this message all the time, sometimes am connected without any problem sometimes i get this message and i have to try again several times until am connected
i searched the net and my database provider guided me to this log URL.....
how to set my ORACLE_HOME environment variable.
View 9 Replies
View Related
Apr 5, 2010
i need to connect to a remote database located on a different server using sqlplus without using database name in connect string.
e.g. if mydb1 (located on another database server on network)
sqlplus username/password
i am able to do this on local database but not on the remote database
View 7 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
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
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
Jul 21, 2011
how to find out what are all the objects referring to particular database links in database?
View 6 Replies
View Related
Jul 2, 2011
There is a user account called 'BALA' in my database.I want to find out the database links that uses this 'BALA' account.
View 2 Replies
View Related
Oct 22, 2011
Our Team is planning to find a new architecture for our new project. In which we have to fire query to multiple database and then we have to collect all responses from them.(Suppose there are 10databases on which we have to fire query)
I searched a lot,the only thing I got is...It could be possible only through Database link(DbLink),Is there any other way to fire query on distributed databases...?
View 20 Replies
View Related
Sep 6, 2009
I'm trying to connect from Oracle 11g (11.2.0.1.0) to Sybase Adaptive Server Anywhere (9.0.2.3527). My Oracle environment is running on Linux Centos 5.3, the sybase database runs on a Windows Server.
All my attempts have failed so far - both through Oracle Database Gateway for Sybase and via Oracle Database Gateway for ODBC in combination with freeTDS. Using either way I'm ending up with apparently the same error:
ASA Error -611: Transact-SQL feature not supported
When I use db link created via dg4sybs (Oracle Database Gateway for Sybase) I get this:
SQL> SELECT * FROM aaa@hvx;
SELECT * FROM aaa@hvx
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[code]....
When I use db link created via dg4odbc (Oracle Database Gateway for ODBC) and freeTDS driver I get this:
SQL> SELECT * FROM aaa@hvx1;
SELECT * FROM aaa@hvx1
*
ERROR at line 1:
ORA-28511: lost RPC connection to heterogeneous remote agent using
SID=ORA-28511: lost RPC connection to heterogeneous remote agent using
[code]....
It seems both drivers use Transact-SQL instructions which are then denied by ASA. I'm not familiar with Sybase products at all, but as far as what my googling revealed the Transact-SQL is only supported in Adaptive Server Enterprise (enterprise-class version of Sybase's database). I couldn't figure out if there's a way how to disable Transact-SQL in the driver configuration. how to connect from Oracle (10g or 11g) to Sybase Adaptive Server Anywhere?
View 3 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
May 12, 2011
when i am connecting to my database in command prompt,sql * plus editor is it successfully connecting to the database but not connecting with the oracle client OEM.
but after connecting the LAN it is not connecting and also in the case when i am connecting to the internet through the USB datacard it is not connecting with the oracle client OEM.
how can i connect to database when the LAN,USB datacard connection.
View 1 Replies
View Related
Mar 12, 2010
We are using Oracle 10g on linux 64 bit, client is on XP, using Oracle sql developer to connect to database through vpn, we have only allowed access to 1521 port of database
Client is frequently disconnecting
Initially client connected, then start disconnecting after 20 minutes.
Client side sqlnet.ora has following entry
SQLNET.AUTHENTICATION_SERVICES = (NTS)
Client side Tnsnames.ora has following entry
dd12 =
(DESCRIPTION =
(ADDRESS_LIST =
[Code]....
Following entry in Listener.ora
# listener.ora Network Configuration File: /home/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
[Code].....
No error in listener.log in database.
Receive following error in sqlnet.log in database
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 10.2.0.3.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.3.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.3.0 - Production
Time: 11-MAR-2010 16:25:16
Tracing not turned on.
Tns error struct:
ns main err 12535
TNS-12535: TNS:operation timed out
ns secondary err 12560
nt main err 505
TNS-00505: Operation timed out
nt secondary err 110
nt OS err 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=172.14.0.31)(PORT=3934))
View 9 Replies
View Related
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
