Change (or bug) regarding parsing of tnsnames.ora in 12c
We have experienced problem with invalid database links after upgrade of one our databases to 12cR1. Certain applications which used to work properly for many years and many releases of Oracle Database begun throwing an well known error: ORA-12154: TNS:could not resolve the connect identifier specified. We were sure that neither tnsnames.ora or sqlnet.ora had been modified. Let see the typical settings of tnsnames.ora used in our case:
[oracle@lab00 ~]$ cat tnsnames.ora
ORA12CR1.test, ORA12CR1, OTHERALIAS.test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA12CR1.test)
)
)What we've got here. One (apparently valid) entry which has three aliases.
Now we'll create database links for each of aliases and we perform simple test of validity. We'll use the same file for 11gR2 and 12cR1 database.
12cR1 first:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_a CONNECT TO test IDENTIFIED BY test USING 'ora12cr1';
Database link created.
SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_b CONNECT TO test IDENTIFIED BY test USING 'ora12cr1.test';
Database link created.
SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_c CONNECT TO test IDENTIFIED BY test USING 'otheralias.test';
Database link created.
SYS@ORA12CR1:SQL> select sysdate from dual@link_a;
SYSDATE
---------
06-JUN-15
SYS@ORA12CR1:SQL> select sysdate from dual@link_b;
select sysdate from dual@link_b
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
SYS@ORA12CR1:SQL> select sysdate from dual@link_c;
SYSDATE
---------
06-JUN-15
Now 11gR2:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@ORA11GR2:SQL> CREATE DATABASE LINK link_a CONNECT TO test IDENTIFIED BY test USING 'ora12cr1';
Database link created.
SYS@ORA11GR2:SQL> CREATE DATABASE LINK link_b CONNECT TO test IDENTIFIED BY test USING 'ora12cr1.test';
Database link created.
SYS@ORA11GR2:SQL> CREATE DATABASE LINK link_c CONNECT TO test IDENTIFIED BY test USING 'otheralias.test';
Database link created.
SYS@ORA11GR2:SQL> select sysdate from dual@link_a;
SYSDATE
---------
06-JUN-15
SYS@ORA11GR2:SQL> select sysdate from dual@link_b;
SYSDATE
---------
06-JUN-15
SYS@ORA11GR2:SQL> select sysdate from dual@link_c;
SYSDATE
---------
06-JUN-15
When you compare above results all three links work on 11gR2 while link_b on 12cR1 doesn't. 12cR1 simply is unable to recognize ORA12CR1.TEST alias in tnsnames.ora entry. Ok, that was about server software, what about client software? I expected the same results but as always I prefer test before making a conclusion.
12cR1 client - same problem:
[oracle@lab00 ~]$ tnsping ora12cr1
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 06-JUN-2015 14:47:30
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (10 msec)
[oracle@lab00 ~]$ tnsping ora12cr1.test
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 06-JUN-2015 14:47:36
Copyright (c) 1997, 2014, Oracle. All rights reserved.
Used parameter files:
TNS-03505: Failed to resolve name11gR2 client - works well:
[oracle@lab00 ~]$ tnsping ora12cr1
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-JUN-2015 14:48:18
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)
[oracle@lab00 ~]$ tnsping ora12cr1.test
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 06-JUN-2015 14:48:25
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)11gR1 client - works well (skipped output from ora12cr1)
[oracle@lab00 ~]$ tnsping ora12cr1.test
TNS Ping Utility for Linux: Version 11.1.0.7.0 - Production on 06-JUN-2015 14:49:54
Copyright (c) 1997, 2008, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)10gR2 client - works well (skipped output from ora12cr1
[oracle@lab00 ~]$ tnsping ora12cr1.test
TNS Ping Utility for Linux: Version 10.2.0.5.0 - Production on 06-JUN-2015 14:50:34
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)9iR2 client - works well (skipped output from ora12cr1)
[oracle@lab00 ~]$ tnsping ora12cr1.test
TNS Ping Utility for Linux: Version 9.2.0.8.0 - Production on 06-JUN-2015 14:51:20
Copyright (c) 1997, 2006, 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 = 192.168.56.100)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORA12CR1.test)))
OK (0 msec)As I said earlier this thing used to work for many years and releases. Question is whether it is a bug in parsing or planned change since 12c. In my opinion it's a bug in probably in parsing because the OTHERALIAS.test has worked fine. Obviously you now can't have the short name alias along with alias with domain in one entry because in that case the alias with domain name is being "ignored" while short name alias works. Let's do another test as a proof of this statement. We put another alias OTHERALIAS (without domain name) for existing entry and we create another database link (named link_d) using that alias. Expected result of test is link_b and link_c will get an ORA-12154 error (note that link_c has worked before).
[oracle@lab00 ~]$ cat tnsnames.ora
ORA12CR1.test, ORA12CR1, OTHERALIAS.test, OTHERALIAS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA12CR1.test)
)
)
[oracle@lab00 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 6 15:11:10 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@ORA12CR1:SQL> CREATE DATABASE LINK link_d CONNECT TO test IDENTIFIED BY test USING 'otheralias';
Database link created.
SYS@ORA12CR1:SQL> select sysdate from dual@link_a;
SYSDATE
---------
06-JUN-15
SYS@ORA12CR1:SQL> select sysdate from dual@link_b;
select sysdate from dual@link_b
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
SYS@ORA12CR1:SQL> select sysdate from dual@link_c;
select sysdate from dual@link_c
*
ERROR at line 1:
ORA-12154: TNS:could not resolve the connect identifier specified
SYS@ORA12CR1:SQL> select sysdate from dual@link_d;
SYSDATE
---------
06-JUN-15
Solution is quite simple. You can't mix shortname (e.g. ORA12CR1) aliases with the same aliases with domain suffix (ORA12CR1.TEST). So in this case we need to create another separate entry for conflicting aliases.
[oracle@lab00 ~]$ cat tnsnames.ora
ORA12CR1.test, OTHERALIAS.test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA12CR1.test)
)
)
ORA12CR1, OTHERALIAS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORA12CR1.test)
)
)
[oracle@lab00 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 6 15:39:49 2015
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@ORA12CR1:SQL> select sysdate from dual@link_a;
SYSDATE
---------
06-JUN-15
SYS@ORA12CR1:SQL> select sysdate from dual@link_b;
SYSDATE
---------
06-JUN-15
SYS@ORA12CR1:SQL> select sysdate from dual@link_c;
SYSDATE
---------
06-JUN-15
SYS@ORA12CR1:SQL> select sysdate from dual@link_d;
SYSDATE
---------
06-JUN-15
As you can see this solution works...