- 16 Jun, 2022
- 0 Comments
- 5 Mins Read
ORA-12514
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/[email protected] 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;