We value your privacy

We use cookies to enhance your browsing experience, serve personalised content, and analyse our traffic. By clicking "Accept All", you consent to our use of cookies.

Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

  Master in PostgreSQL DBA |Join Free Demo Session on 28 April 2025 at 7.30 PM IST| Register for Free Demo

Know More

ORA-12514

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarKiran Dalvi
  • 16 Jun, 2022
  • 0 Comments
  • 5 Mins Read

ORA-12514

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[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 : 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
[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 :
1
2
3
4
5
6
7
8
9
10
11
12
13
[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
1
2
3
4
5
6
7
8
9
10
11
12
13
[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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
[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
1
2
3
4
[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 :
1
2
[oracle@prim admin]$ hostname
prim.oracle.com
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
[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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
[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
1
2
3
4
5
6
7
8
9
[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!

Undo Management
Enable Archive Log Mode In Oracle RAC