| dblinks [message #540821] |
Wed, 25 January 2012 21:31  |
reym21
Messages: 241
Registered: February 2010
Location: Philippines
|
Senior Member |
|
|
Good day!
Please help me to connect Oracle 10g (connecting dB) to Oracle 7.3.4 (remote dB) via dblink (both in DEV environment). I encounter these errors everytime I attempted to, re:
ORA-12545: Connect failed because target host or object does not exist
ORA-12154: TNS:could not resolve the connect identifier specified
ORA-02019: connection description for remote database not found
The user exists in both servers (with same privileges) with the same SQLPlus password and using a private DBLINK with the same username and password also. The user will only view 2 tables from the remote_db. Parameters like TNSNAMES.ora, SQLNET.ora, LISTENER.ora, GLOBAL_NAME are already configured.
I uploaded the connecting_db file for reference.
Thanks in advance.
PS.
this is for the remote_db details:
=============================================================
LTD-DEV (as remote dB)
=============================================================
BIR-dev:/appl1/home/oracle734>. oraenv
ORACLE_SID = [itsdev01] ? ltd
BIR-dev:/appl1/home/oracle734>. oraenv
ORACLE_SID = [ltd] ?
BIR-dev:/appl1/home/oracle734>sqlplus /
SQL*Plus: Release 3.3.4.0.1 - Production on Thu Jan 26 09:02:09 2012
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Connected to:
Oracle7 Server Release 7.3.4.5.0 - Production
With the distributed and parallel query options
PL/SQL Release 2.3.4.5.0 - Production
oracle@LTD
SQL:-) show user
user is "ORACLE"
oracle@LTD
SQL:-) select name from v$database;
NAME
---------
LTD
oracle@LTD
SQL:-) select global_name from global_name;
GLOBAL_NAME
-------------------------------------------------------------
LTD.WORLD
oracle@LTD
SQL:-) select table_name from dba_tables
2 where table_name like 'REG_TAXPAYERS%'
3 and owner = 'ITS';
TABLE_NAME
------------------------------
REG_TAXPAYERS
REG_TAXPAYERS_TEMP
oracle@LTD
SQL:-) exit
BIR-dev:/appl1/home/oracle734/network/admin>
Disconnected from Oracle7 Server Release 7.3.4.5.0
With the distributed and parallel query options
PL/SQL Release 2.3.4.5.0 - Production
BIR-dev:/appl1/home/oracle734/network/admin> exit
$ pwd
/u01/appl/its
$ . oraenv
ORACLE_SID = [ltd] ?
$ sqlplus ITS (<== apps schema)
SQL*Plus: Release 3.3.4.0.1 - Production on Thu Jan 26 09:50:55 2012
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Enter password:
Connected to:
Oracle7 Server Release 7.3.4.5.0 - Production
With the distributed and parallel query options
PL/SQL Release 2.3.4.5.0 - Production
its@LTD
SQL:-)@creausr_ncbcarre.sql
CREATE USER NCBCARRE
IDENTIFIED BY pass1234
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT;
GRANT CONNECT TO NCBCARRE;
GRANT ITSROLE TO NCBCARRE;
GRANT READONLY TO NCBCARRE;
GRANT RESOURCE TO NCBCARRE;
ALTER USER NCBCARRE DEFAULT ROLE ALL;
GRANT CREATE DATABASE LINK TO NCBCARRE;
GRANT SELECT ON ITS.REG_TAXPAYERS TO NCBCARRE;
GRANT SELECT ON ITS.REG_TAXPAYER_LOCATIONS TO NCBCARRE;
its@LTD
SQL:-) select username from all_users
2 where username = 'NCBCARRE';
USERNAME
------------------------------
NCBCARRE
its@LTD
SQL:-) select privilege, table_name
2 from user_tab_privs
3 where grantee = 'NCBCARRE';
PRIVILEGE TABLE_NAME
----------------------------------- ------------------------------
SELECT REG_TAXPAYERS
SELECT REG_TAXPAYER_LOCATIONS
its@LTD
SQL:-) exit
Disconnected from Oracle7 Server Release 7.3.4.5.0 - Production
With the distributed and parallel query options
PL/SQL Release 2.3.4.5.0 - Production
$ sqlplus NCBCARRE
SQL*Plus: Release 3.3.4.0.1 - Production on Thu Jan 26 10:26:55 2012
Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
Enter password: pass1234
Connected to:
Oracle7 Server Release 7.3.4.5.0 - Production
With the distributed and parallel query options
PL/SQL Release 2.3.4.5.0 - Production
ncbcarre@LTD
SQL:-) select count(*) from REG_TAXPAYERS;
COUNT(*)
----------
23983
ncbcarre@LTD
SQL:-) select count(*) from REG_TAXPAYER_LOCATIONS;
COUNT(*)
----------
23720
ncbcarre@LTD
SQL:-) exit
Disconnected from Oracle7 Server Release 7.3.4.5.0
With the distributed and parallel query options
PL/SQL Release 2.3.4.5.0 - Production
$ ps -ef|grep smon
oracle 14129 26890 0 Jan 19 ? 0:02 ora_smon_ltd
$
------------
TNSNAMES.ora
------------
ltd=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=bir-dev)
(PORT=1526)
)
(CONNECT_DATA=(SID=ltd))
)
ltd.world =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=tcp)
(HOST=bir-dev)
(PORT=1526)
)
(CONNECT_DATA=(SID=ltd))
)
------------
LISTENER.ora
------------
LISTENER=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL = tcp)
(HOST = bir-dev)
(PORT = 1526)
)
)
TRACE_LEVEL_LISTENER=USER
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=ltd)
(ORACLE_HOME=/appl1/home/oracle734)
)
----------
SQLNET.ora
----------
NAMES.DIRECTORY_PATH = (TNSNAMES,HOSTNAME)
|
|
|
|
|
|
|
|
|
|
|
|
| Re: dblinks [message #540830 is a reply to message #540825] |
Thu, 26 January 2012 00:52  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
That's what BlackSwan told you - install a database that can communicate with both Oracle 7 and Oracle 10 (which is - according to BlackSwan - 9.2.0.7. I don't know whether it is the *only* version you could use. I remember we have had 9i between 7.1 and 10g, but which 9i exactly ... don't remember). So, you'd actually need two database links:
Oracle 7 <-- DB link --> Oracle 9i <-- DB link --> Oracle 10g
[Updated on: Thu, 26 January 2012 00:53] Report message to a moderator |
|
|
|
| Re: dblinks [message #542200 is a reply to message #540830] |
Sun, 05 February 2012 21:46 |
reym21
Messages: 241
Registered: February 2010
Location: Philippines
|
Senior Member |
|
|
Sir,
Good day!
Thanks for your reply. But to our disappointment after we installed the 9i (9.2.0.1) database we can't still 'bridge' 734 to 10g via dblinks. We dropped all the dblinks, synonyms and
views we've created to start again from the scratch.
Do you have any procedures that we can follow? It's our first time to create a setup like this. For now we have this:
Version SID Global_Name Hostname
------- ------ -------------------------- --------
7.3.4** aybusy ABUSY.WORLD abc-dev
9iR2* xferd XFERD.US.ORACLE.COM smdevdb
10gR2* queyz QUEYZ.REGRESS.RDBMS.DEV.US.ORACLE.COM smdevdb
** remote dB server
* in the same dB server
Thanks in advance.
[Updated on: Sun, 05 February 2012 21:49] Report message to a moderator |
|
|
|
| Re: dblinks [message #542201 is a reply to message #542200] |
Sun, 05 February 2012 22:13 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>Do you have any procedures that we can follow?
Not I, since I have no idea what problem you are trying to solve.
From V9 create 2 DBLINKs; V7 & V10 to the 2 other DBs.
SELECT COUNT(*) USER_OBJECTS@V7;
SELECT COUNT(*) USER_OBJECTS@V10;
post all SQL showing; including CREATE DATABASE LINK & results from 2 SQL above
[Updated on: Sun, 05 February 2012 22:33] Report message to a moderator |
|
|
|
| Re: dblinks [message #542230 is a reply to message #542201] |
Mon, 06 February 2012 00:32 |
reym21
Messages: 241
Registered: February 2010
Location: Philippines
|
Senior Member |
|
|
This is the actual setup:
Oracle 10g <-- DB link --> Oracle 9i <-- DB link --> Oracle 7.3.4
Version SID Grantor Global_Name Hostname User
------- -------- -------- ---------------- -------- --------
7.3.4** ltd ITS LTD.WORLD dir-dev NCBCARRE
9iR2* transfer n/a TRANSFER.US.ORACLE.COM smodevdb n/a
10gR2* laeiss LAMS LAEISS.REGRESS.RDBMS.DEV.US.ORACLE.COM smodevdb NCBCARRE
** remote dB server
* both in the same dB server
Note: user NCBCARRE was both existing in 7.3.4 & 10g with current privileges
of CREATE PUBLIC/DATABASE LINKS, CREATE SYNONYMS & CREATE VIEWS and
same SQLPlus password
TNSNAMES ...
Oracle 10.2.0.1.0
-----------------
laeiss =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = smodevdb)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = laeiss)
)
)
Oracle 9.2.0.1.0
----------------
TRANSFER =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = smodevdb)(PORT = 1522))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = transfer)
)
)
Oracle 7.3.4
------------
ltd =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = tcp)(HOST = bir-dev)(PORT = 1526))
(CONNECT_DATA = (SID = ltd)
)
)
ISSUE:
User NCBCARRE to "SELECT from ITS.REG_TAXPAYERS & ITS.REG_TAXAPYER_LOCATIONS@ltd;"
=======================================
As per your instruction
=======================================
$ hostname
smodevdb
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Feb 6 14:27:58 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> select name from v$database;
NAME
---------
TRANSFER
SQL> conn ncbcarre
Enter password:
Connected.
SQL> show user
USER is "NCBCARRE"
SQL> create database link ltd_dev <-- Oracle 7.34
2 using 'ltd';
Database link created.
SQL> create database link elams <-- Oracle 10g
2 using 'laeiss';
Database link created.
SQL> select count(*) from user_objects@elams;
COUNT(*)
----------
2
SQL> select count(*) from user_objects@ltd_dev;
select count(*) from user_objects@ltd_dev
*
ERROR at line 1:
ORA-12545: Connect failed because target host or object does not exist
[Updated on: Mon, 06 February 2012 00:37] by Moderator Report message to a moderator |
|
|
|
| Re: dblinks [message #542232 is a reply to message #542230] |
Mon, 06 February 2012 00:36 |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
ORA-12545: Connect failed because target host or object does not exist
*Cause: The address specified is not valid, or the program being
connected to does not exist.
*Action: Ensure the ADDRESS parameters have been entered correctly; the
most likely incorrect parameter is the node name. Ensure that the
executable for the server exists (perhaps "oracle" is missing.)
If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
host name to a numeric IP address and try again.
From 9i dataabse server, execute:
Regards
Michel
[Updated on: Mon, 06 February 2012 00:36] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: dblinks [message #542264 is a reply to message #542241] |
Mon, 06 February 2012 03:23 |
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Your tnsnames.ora file in the 9.2 home needs an entry for LTD. Without this, you can create the link LTD_DEV but you will get an
ORA-02019 when you try to use it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
