Blog

ORA-12514 : TNS:listener does not currently know of service requested in connect

[oracle@prim admin]$ sqlplus pdbuser/pdbpassword@pdbprim

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 5 18:14:00 2022

Version 19.3.0.0.0

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

ERROR:

ORA-12514: TNS:listener does not currently know of service requested in connect

descriptor

Enter user-name: ^C

Check Listener status : 

[oracle@prim admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 05-MAY-2022 18:13:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prim)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                05-MAY-2022 18:13:11

Uptime                    0 days 0 hr. 0 min. 45 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /data/app/oracle/product/19C/dbhome_3/network/admin/listener.ora

Listener Log File         /data/app/oracle/diag/tnslsnr/prim/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prim)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Services Summary...

Service "86b637b62fdf7a65e053f706e80a27ca.oracle.com" has 1 instance(s).

Instance "prim", status READY, has 1 handler(s) for this service...

Service "ddb75c59941569f4e0535000a8c09962.oracle.com" has 1 instance(s).

Instance "prim", status READY, has 2 handler(s) for this service...

Service "de085f23955e6312e0535000a8c03cc4.oracle.com" has 1 instance(s).

Instance "prim", status READY, has 1 handler(s) for this service...

Service "de09c232cdb91bb0e0535000a8c0cee3.oracle.com" has 1 instance(s).

Instance "prim", status READY, has 1 handler(s) for this service...

Service "pdbprim.oracle.com" has 1 instance(s).

Instance "prim", status READY, has 2 handler(s) for this service...

Service "pdbprim2.oracle.com" has 1 instance(s).

Instance "prim", status READY, has 1 handler(s) for this service...

Service "pdbprim3.oracle.com" has 1 instance(s).

Instance "prim", status READY, has 1 handler(s) for this service...

Service "prim.oracle.com" has 1 instance(s).

Instance "prim", status READY, has 1 handler(s) for this service...

Service "primXDB.oracle.com" has 1 instance(s).

Instance "prim", status READY, has 1 handler(s) for this service...

The command completed successfully

AS there is pdbprim.oracle.com is the name, we will try to connect with the same name :

[oracle@prim admin]$ sqlplus pdbuser/pdbpassword@pdbprim.oracle.com

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 5 18:14:06 2022

Version 19.3.0.0.0

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

ERROR:

ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name: ^C

 [oracle@prim ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 5 18:08:52 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter system register;
System altered.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

lets stop & start listener one more time

 [oracle@prim admin]$ lsnrctl stop
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 05-MAY-2022 18:13:07
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prim)(PORT=1521)))
The command completed successfully

[oracle@prim admin]$ lsnrctl start
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 05-MAY-2022 18:13:11
Copyright (c) 1991, 2019, Oracle.  All rights reserved.
Starting /data/app/oracle/product/19C/dbhome_3/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /data/app/oracle/product/19C/dbhome_3/network/admin/listener.ora
Log messages written to /data/app/oracle/diag/tnslsnr/prim/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prim)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prim)(PORT=1521)))
STATUS of the LISTENER
------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production

Start Date                05-MAY-2022 18:13:11

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /data/app/oracle/product/19C/dbhome_3/network/admin/listener.ora

Listener Log File         /data/app/oracle/diag/tnslsnr/prim/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prim)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

The listener supports no services

The command completed successfully

Check the /etc/hosts file

 [oracle@prim admin]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.0.80    prim    prim.oracle.com 

Check Host Name :

[oracle@prim admin]$ hostname
prim.oracle.com 
[oracle@prim admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 5 18:16:02 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter pluggable database all open;
Pluggable database altered.

SQL> show pdbs;
CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED                       READ ONLY  NO
3 PDBPRIM                        READ WRITE NO
4 PDBPRIM2                       READ WRITE NO
5 PDBPRIM3                       READ WRITE NO

SQL>
SQL> show parameter domain;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string oracle.com
SQL> alter system reset db_domain scope=spfile;
System altered.
SQL> alter system register;
System altered.

ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOl=TCP)(HOST=192.168.0.80)(PORT=1521))' SCOPE=BOTH;
 SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1543500832 bytes

Fixed Size                  9135136 bytes

Variable Size             973078528 bytes

Database Buffers          553648128 bytes

Redo Buffers                7639040 bytes

Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@prim admin]$ tnsping pdbprim
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 05-MAY-2022 18:18:09
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:
/data/app/oracle/product/19C/dbhome_3/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.80)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbprim)))
OK (10 msec)
[oracle@prim admin]$ nslookup prim
Server:         192.168.0.2
Address:        192.168.0.2#53
Name:   prim.localdomain
Address: 192.168.0.128
[oracle@prim admin]$
[oracle@prim admin]$ ping prim
PING prim (192.168.0.80) 56(84) bytes of data.
64 bytes from prim (192.168.0.80): icmp_seq=1 ttl=64 time=0.091 ms
64 bytes from prim (192.168.0.80): icmp_seq=2 ttl=64 time=0.174 ms
^C
--- prim ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1005ms
rtt min/avg/max/mdev = 0.091/0.132/0.174/0.043 ms
[oracle@prim admin]$
[oracle@prim admin]$
[oracle@prim admin]$ ping prim
PING prim (192.168.0.80) 56(84) bytes of data.
64 bytes from prim (192.168.0.80): icmp_seq=1 ttl=64 time=0.086 ms
64 bytes from prim (192.168.0.80): icmp_seq=2 ttl=64 time=0.085 ms
^C
--- prim ping statistics ---
2 packets transmitted, 2 received, 0% packet loss, time 1031ms
rtt min/avg/max/mdev = 0.085/0.085/0.086/0.009 ms

 [oracle@prim admin]$ sqlplus pdbuser/pdbpassword@pdbprim
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 5 18:28:44 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Last Successful login time: Thu May 05 2022 18:24:53 +05:30
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user;

Hope It Helps!

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.