Instance "mydb", status UNKNOWN

[big]

Hi,

On 19c on AIX

My listener is always in UNKNOWN status:
 Instance "mydb", status UNKNOWN, has 1 handler(s) for this service...

From dbserver we cannot connect:

sqlplus system@mydb

ora-12154 tns could not resolve.

But from client (with tnsnames.ora exactly the same) we can connect:

sqlplus system@mydb
connected

sql>

Is there any relation between UNKNOWN status and ora-12154?

Thanks for help.

[ErmanArslansOracleBlog]

ORA-12154: TNS: could not resolve service name.

If the tnsnames.ora files are exactly the same, this means your db is reading another tnsnames.ora file.. I mean it reads/uses another tnsnames.ora file which is present in your db server.. Check your configuration.. ORACLE_HOME, TNS_ADMIN etc..
Also if there is an ifile in tnsnames.ora which is pointing to another additional tns file that have a wrong entry for that services, then this may also cause issues..
Check the global_name as well..

that UNKNOWN status in lsnrctl status means that; the instance is registered statically (with a SID_LIST) in the listener.ora file rather than dynamically with service registration.  Therefore, the database status is "not known" to the listener.
I can't see a connection between ORA-12514 and status unknown..

Here is an example:

[oracle@ebstestdb ~]$ lsnrctl status PROD

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 02-FEB-2021 08:48:44

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ebstestdb)(PORT=1530)))
STATUS of the LISTENER
------------------------
Alias                     PROD
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                02-JAN-2021 11:33:56
Uptime                    30 days 21 hr. 14 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/dbebs/PROD/db/tech_st/11.2.0/network/admin/PROD_ebstestdb/listener.ora
Listener Log File         /u01/dbebs/PROD/db/tech_st/11.2.0/admin/PROD_ebstestdb/diag/tnslsnr/ebstestdb/prod/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ebstestdb)(PORT=1530)))
Services Summary...
Service "PROD" has 1 instance(s).
  Instance "PROD", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

[oracle@ebstestdb PROD_ebstestdb]$ cat tnsnames.ora

PROD=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=ebstestdb)(PORT=1530))
            (CONNECT_DATA=
                (SERVICE_NAME=PROD)
            )
        )

[oracle@ebstestdb PROD_ebstestdb]$ tnsping PROD

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 02-FEB-2021 08:54:31

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/dbebs/PROD/db/tech_st/11.2.0/network/admin/PROD_ebstestdb/sqlnet_ifile.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=ebstestdb)(PORT=1530)) (CONNECT_DATA= (SERVICE_NAME=PROD)))
OK (0 msec)
[oracle@ebstestdb PROD_ebstestdb]$ sqlplus apps/*********@PROD

SQL*Plus: Release 11.2.0.4.0 Production on Tue Feb 2 08:54:33 2021
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

SQL>

[ErmanArslansOracleBlog]

Check NAMES.DEFAULT_DOMAIN as well.. You may have this setting in your DB's sqlnet.ora.. Check if there a need to a set in your sqlnet.ora
global names may cause this as well...

[big]

Hi,
Thanks.
You are true:
" this means your db is reading another tnsnames.ora".

TNS_ADMIN was pointing to a wrong tnsnames.ora file. I corrected and now connection is possible.

One thing:

It seems that in 19c TNS_ADMIN should directly point to tnsnames.ora file and does not like pointing to symbolic links.
 for example if TNS_ADMIN=/etc and in /etc we have
tnsnames.ora --> /u01/cfg/tnsnames.ora

we have ora-12154 tns could not resolve.

Best regards.

[ErmanArslansOracleBlog]

Hi,

I tested it on a EBS 19C database and it works..

[oracle@ebsclone tns_link]$ echo $TNS_ADMIN
/home/oracle/tns_link

[oracle@ebsclone tns_link]$ ls -al
total 4
drwxr-xr-x   2 oracle oinstall   26 Feb  3 17:15 .
drwx------. 24 oracle oinstall 4096 Feb  3 17:15 ..
lrwxrwxrwx   1 oracle oinstall   52 Feb  3 17:15 tnsnames.ora -> /u01/dbebs/product/19.0.0/network/admin/tnsnames.ora

[oracle@ebsclone tns_link]$ echo $TNS_ADMIN
/home/oracle/tns_link
[oracle@ebsclone tns_link]$ ls -al
lrwxrwxrwx   1 oracle oinstall   52 Feb  3 17:15 tnsnames.ora -> /u01/dbebs/product/19.0.0/network/admin/tnsnames.ora

[oracle@ebsclone tns_link]$ sqlplus apps/xxxxxxxxx@TEST

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Feb 3 17:17:33 2021
Version 19.9.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Last Successful login time: Wed Feb 03 2021 17:16:17 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.0

SQL>

[ErmanArslansOracleBlog]

ensure you have permission to read that symbolic link.. that may be a cause..