A reader, February 13, 2019 - 2:54 pm UTC
To let you better understand the situation, i'll give you my configuration.
I'll use LNXDB to identify the db on Linux server (Oracle 12c) and WINDB for Windows DB (Oralce 11g R2).
The TNSNAMES on linux server is:
LNXDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LNXDB)
)
)
WINDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =WINHOST)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = WINDB)
)
)
the dblink on linux is:
CREATE DATABASE LINK winlink
CONNECT TO user IDENTIFIED BY abcd1234
USING 'WINHOST:1521/WINDB';
the tnsping work, connecting via sqlplus on WINDB work, query a table@WINDB don't work.
i want to say that i've also tried to create a dblink from the Oracle 11 windows db to the Oracle 12c Linux db and it is working.
Another test is to create dblink between oracle 12 and 11 all on the same OS (windows) and don't work, so i think that is a problem between Oracle 12c and Oracle 11g.
There are some know issues and workaround?
Best Regards
Vincenzo
February 13, 2019 - 4:36 pm UTC
Connections between 12c and 11.2 are supported*. See MOS note 207303.1.
So I doubt this is the issue.
*12.2 only supports 11.2.0.3 & 11.2.0.4. So check your versions.
The TNS error comes from the listener. Triple check the configuration is correct.
MOS note 2340617.1 has the following solution for dealing with ORA-12514 over DB links:
A) Make certain that your DBLink is created with the correct LISTENER address (the target).
1. Alias (Tnsnames) used -->
CREATE DATABASE LINK ROYTEST.ORACLE.COM
...
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = oracle_test.oracle.com) (PORT = 5000)) (CONNECT_DATA = (SERVICE_NAME = ROY_TEST.ORACLE.COM )))';
2. Listener -->
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle_test.oracle.com)(PORT=5000)))
Services Summary...
...
Service "ROY_TEST.oracle.com" has 1 instance(s).
...
B) Make sure to set GLOBAL_NAMES = FALSE to allow the DBLink to be a different value than the GLOBAL_NAME.
A reader, February 14, 2019 - 7:05 am UTC
Thanks for the response.
We have installed the 12.1.0.2 version and try to create a DB link to 11.2.0.2 version, so it might be the problem? Isn't the db link between 12.1.0.2 and 11.2.0.2 supported?
I don't understand your suggestion about using the same service name, because I already did. In TNSNAME i use the same host and service name, write into the listener for destination database and if i run lsnrctl status i see the entire hostname of source DB (including .domani.it) and all instances ready...
Do you need some screen of my configuration?
Thanks in advance
February 15, 2019 - 1:56 am UTC
On the *target* node (where the db link is trying to connect *to*) run:
lsnrctl status
This will show the list of services that your listener can respond to. The db link definition must resolve (either directly) or via tnsnames.ora (on the server where the db link is being defined) to one of those services.
A reader, February 14, 2019 - 7:15 am UTC
I've recreated the dblink to the 11.2.0.2 severe as:
CREATE DATABASE LINK link_windb
CONNECT TO user IDENTIFIED BY abc123
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = winhost.domain.it) (PORT = 2016)) (CONNECT_DATA = (SERVICE_NAME = WINDB)))';
and I have also changed the entries for tnsnames and listener as well:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = LNXDB)
(ORACLE_HOME = /opt/oracle/product/12.1.0.2/db_1)
(SID_NAME = LNXDB)
)
(SID_DESC =
(SID_NAME = WINDB) --- instead of GLOBAL_NAME
(ORACLE_HOME = /opt/oracle/product/12.1.0.2/db_1)
(SID_NAME = WINDB)
)
)
and tnsnames:
LNXDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LNXDB)
)
)
WINDB=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = WINHOST.domain.it)(PORT = 2016))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = WINDB)
)
)
but it isn't working
Maybe the version ?
J. Laurindo Chiappa, February 15, 2019 - 11:31 am UTC
As Chris said, the Support doc clearly says that 12.2 only supports 11.2.0.3 & 11.2.0.4 : if OP´s version really is 11.2.0.2, problems CAN (probably WILL) arise ....
Personally I had similar problems with 11.2.0.1 (technet´s version) , so really check your versions....
Best regards,
Chiappa
February 15, 2019 - 12:01 pm UTC
Yes, but they've said they're on 12.1.0.2. Which supports all 11.2 versions.
To Chris
J. Laurindo Chiappa, February 15, 2019 - 4:24 pm UTC
You are speaking about the MOS note "Client / Server Interoperability Support Matrix for Different Oracle Versions" (Doc ID 207303.1), Chris, right ?? Yes, the note says that the older release 12.1.0.x supports any 11gr2 release while the newer 12.2.0.x release does not - and really, when I tested in my environment and had problems I was using 12.2.0.x, really... Well, if possíble what I would suggest to Vincenzo is to create a dblink connecting in some 11.2.0.4 database, just to be sure...
And another point : if he is trying to use a SERVICE in the dblink :
CREATE DATABASE LINK link_windb
CONNECT TO user IDENTIFIED BY abc123
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (Host = winhost.domain.it) (PORT = 2016)) (CONNECT_DATA = (SERVICE_NAME = WINDB)))';
WHY the SID in the LISTENER.ORA and in TNSNAMES.ORA :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = LNXDB)
(ORACLE_HOME = /opt/oracle/product/12.1.0.2/db_1)
(SID_NAME = LNXDB)
)
(SID_DESC =
(SID_NAME = WINDB) --- instead of GLOBAL_NAME
(ORACLE_HOME = /opt/oracle/product/12.1.0.2/db_1)
(SID_NAME = WINDB)
)
)
SERVICEs are the preferred way to connect in 12c, no ? Could be something related with this ?
Regards,
Chiappa