Home » RDBMS Server » Networking and Gateways » dblinks (Oracle 10g / Oracle 7.3.4/ Solaris)
dblinks [message #540821] Wed, 25 January 2012 21:31 Go to next message
reym21
Messages: 238
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 #540822 is a reply to message #540821] Wed, 25 January 2012 22:17 Go to previous messageGo to next message
BlackSwan
Messages: 26747
Registered: January 2009
Location: SoCal
Senior Member
>Please help me to connect Oracle 10g (connecting dB) to Oracle 7.3.4 (remote dB) via dblink (both in DEV environment).
V7 can not & does not inter-operate with V10; no way & no how!
At best SQL*Net works between versions not greater than TWO major versions.
Re: dblinks [message #540823 is a reply to message #540822] Wed, 25 January 2012 22:30 Go to previous messageGo to next message
reym21
Messages: 238
Registered: February 2010
Location: Philippines
Senior Member

Thanks for the info.

But how come that the ORA error messages doesn't indicate incompatibilities?

How can we resolve this? Can you suggests any workaround?

Thanks again.
Re: dblinks [message #540824 is a reply to message #540823] Wed, 25 January 2012 22:39 Go to previous messageGo to next message
BlackSwan
Messages: 26747
Registered: January 2009
Location: SoCal
Senior Member
>But how come that the ORA error messages doesn't indicate incompatibilities?
Silly question!
When V7 was tested & released, who could have predicted it could not talk to V10?
When V10 was released, V7 was obsoleted & unsupported, so why test against V7?

>How can we resolve this? Can you suggests any workaround?
Install V9.2.0.7; which is obsoleted & unsupported but can talk to both V7 & V10
Re: dblinks [message #540825 is a reply to message #540824] Wed, 25 January 2012 23:03 Go to previous messageGo to next message
reym21
Messages: 238
Registered: February 2010
Location: Philippines
Senior Member

Thanks for the suggestion. But you didn't answer well my 1st question. I am expecting the message below as I tried connecting the dblinks. I read from some forums re:

http://www.dbasupport.com/forums/showthread.php?t=50652

that they experienced it and I want to validate this to an expert like you:

"ORA-03115: unsupported network datatype or representation
Cause: A user bind or define, or an Oracle function, is not
supported by this heterogeneous Net8 connection.
Action: Upgrade the older version of Oracle and try again."





Re: dblinks [message #540830 is a reply to message #540825] Thu, 26 January 2012 00:52 Go to previous messageGo to next message
Littlefoot
Messages: 21612
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 Go to previous messageGo to next message
reym21
Messages: 238
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 Go to previous messageGo to next message
BlackSwan
Messages: 26747
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 Go to previous messageGo to next message
reym21
Messages: 238
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 Go to previous messageGo to next message
Michel Cadot
Messages: 67456
Registered: March 2007
Location: Nanterre, France, http://...
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:
ping bir-dev
tnsping ltd 

Regards
Michel

[Updated on: Mon, 06 February 2012 00:36]

Report message to a moderator

Re: dblinks [message #542233 is a reply to message #542230] Mon, 06 February 2012 00:39 Go to previous messageGo to next message
BlackSwan
Messages: 26747
Registered: January 2009
Location: SoCal
Senior Member
>ORA-12545: Connect failed because target host or object does not exist
results from basic mis-configuration & NOTHING concerning version incompatibility

[Updated on: Mon, 06 February 2012 00:39]

Report message to a moderator

Re: dblinks [message #542234 is a reply to message #542232] Mon, 06 February 2012 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 67456
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now that I have aligned the columns in your post, I see:
Hostname: dir-dev
and HOST = bir-dev

Regards
Michel

[Updated on: Mon, 06 February 2012 00:40]

Report message to a moderator

Re: dblinks [message #542235 is a reply to message #542232] Mon, 06 February 2012 00:40 Go to previous messageGo to next message
reym21
Messages: 238
Registered: February 2010
Location: Philippines
Senior Member

$ hostname
smodevdb
$ ping bir-dev
ping: unknown host bir-dev
$ tnsping ltd

TNS Ping Utility for Solaris: Version 9.2.0.1.0 - Production on 06-FEB-2012 14:58:23

Copyright (c) 1997 Oracle Corporation. All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = bir-dev)(PORT = 1526))) (CONNECT_DATA = (SERVICE_NAME = ltd) (SID = ltd)))
TNS-12545: Connect failed because target host or object does not exist
$
Re: dblinks [message #542236 is a reply to message #542235] Mon, 06 February 2012 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67456
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 06 February 2012 07:39
Now that I have aligned the columns in your post, I see:
Hostname: dir-dev
and HOST = bir-dev

Regards
Michel


Re: dblinks [message #542241 is a reply to message #542236] Mon, 06 February 2012 00:59 Go to previous messageGo to next message
reym21
Messages: 238
Registered: February 2010
Location: Philippines
Senior Member

Gurus,

Thanks for all your help... it has something to do with our network firewall.

Keep up the good work!

Regards,
Re: dblinks [message #542264 is a reply to message #542241] Mon, 06 February 2012 03:23 Go to previous message
John Watson
Messages: 8394
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.
Previous Topic: Not able to connect to database
Next Topic: Shared Server OR Dedicated Server
Goto Forum:
  


Current Time: Sat Oct 24 10:40:59 CDT 2020