Re: Instance "mydb", status UNKNOWN

Posted by ErmanArslansOracleBlog on Feb 02, 2021; 5:56am
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Instance-mydb-status-UNKNOWN-tp9305p9306.html

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>