SOLVED

ORA-12514

Asked by OracleDba••13 views•oracle

#oracle#error

Solutions(1)

Accepted Solution
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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
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;

Hope It Helps!
OracleDba•

Post Your Solution