Home » SQL & PL/SQL » Client Tools » ORA-12514 (Oracle 10g / Windows Xp Prof.)
ORA-12514 [message #440267] Thu, 21 January 2010 22:51 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi,

I have recently installed Oracle 10g in my machine. I have started the following services.

OracleDBConsoleorcl
OracleOraDb10g_home1TNSListener
OracleSerivceORCL

when I try to connect with the database name mentioned, it says
ORA-12514. But without which its able to connect.

Whats wrong in my machine. Please help.

C:\>sqlplus fieluser/o@orcl

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jan 22 12:41:31 2010

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

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


Enter user-name: ^C
C:\>sqlplus fieluser/o

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Jan 22 12:41:35 2010

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


Connected to:
Oracle Database 10g Release 10.1.0.2.0 - Production

SQL> set linesize 10000;
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION           STARTUP_T
 STATUS    PAR    THREAD# ARCHIVE LOG_SWITCH_ LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST
--------------- ---------------- ---------------------------------------------------------------- ----------------- ---------
 ------------ --- ---------- ------- ----------- ---------- --- ----------------- ------------------ ---------
              1 orcl             XXXXXXXXX                                                      10.1.0.2.0        14-JAN-10
 OPEN      NO           1 STOPPED             ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL

SQL>

C:\>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 22-JAN-2010 12:46:32

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=iflexsg2022.i-flex.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
Start Date                15-JAN-2010 12:46:59
Uptime                    6 days 23 hr. 59 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File         D:\oracle\product\10.1.0\db_1\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx.xxx.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
The command completed successfully


Regards
Re: ORA-12514 [message #440268 is a reply to message #440267] Thu, 21 January 2010 22:56 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
using CUT & PASTE post results from following commands

lsnrctl status
set
lsnrctl service

HOST=iflexsg2022.i-flex.com)
(HOST=xxxxx.xxx.com)

post tail end of listener.log file after reproducing error again
Re: ORA-12514 [message #440269 is a reply to message #440268] Thu, 21 January 2010 23:05 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

C:\>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 22-JAN-2010 12:55:18

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx.xxx.xxx)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
Start Date                15-JAN-2010 12:46:59
Uptime                    7 days 0 hr. 8 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   D:\oracle\product\10.1.0\db_1\network\admin\listener.ora
Listener Log File         D:\oracle\product\10.1.0\db_1\network\log\listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx.xxx.xxx)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
The command completed successfully

C:\>set
ALLUSERSPROFILE=C:\Documents and Settings\All Users
APPDATA=C:\Documents and Settings\srivathsang\Application Data
classpath=C:\Program Files\Java\jdk1.6.0\lib
CommonProgramFiles=C:\Program Files\Common Files
COMPUTERNAME=xxxxxx
ComSpec=C:\WINDOWS\system32\cmd.exe
DEFLOGDIR=C:\Documents and Settings\All Users\Application Data\McAfee\DesktopProtection
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Documents and Settings\srivathsang
JAVA_HOME=C:\Program Files\Java\jdk1.6.0
NUMBER_OF_PROCESSORS=2
OS=Windows_NT
Path=C:\Program Files\Business Objects\common\3.5\bin\NOTES\;C:\Program Files\Business Objects\common\3.5\bin\NOTES\DATA\;D:\
oracle\product\10.1.0\db_1\bin;D:\oracle\product\10.1.0\db_1\jre\1.4.2\bin\client;D:\oracle\product\10.1.0\db_1\jre\1.4.2\bin
;D:\Oracle\product\10.1.0\Client_1\bin;D:\Oracle\product\10.1.0\Client_1\jre\1.4.2\bin\client;D:\Oracle\product\10.1.0\Client
_1\jre\1.4.2\bin;D:\oracle\ora92\bin;C:\Program Files\Oracle\jre\1.3.1\bin;C:\Program Files\Oracle\jre\1.1.8\bin;C:\WINDOWS\s
ystem32;C:\WINDOWS;C:\WINDOWS\System32\Wbem;C:\Program Files\Windows Imaging\;.;C:\Program Files\Java\jdk1.6.0\bin
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH
PERL5LIB=D:\oracle\product\10.1.0\db_1\perl\lib\5.6.1\MSWin32-x86;D:\oracle\product\10.1.0\db_1\perl\lib\5.6.1;D:\oracle\prod
uct\10.1.0\db_1\perl\5.6.1\lib\MSWin32-x86;D:\oracle\product\10.1.0\db_1\perl\site\5.6.1;D:\oracle\product\10.1.0\db_1\perl\s
ite\5.6.1\lib;D:\oracle\product\10.1.0\db_1\sysman\admin\scripts
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_IDENTIFIER=x86 Family 6 Model 15 Stepping 2, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=0f02
ProgramFiles=C:\Program Files
PROMPT=$P$G
SESSIONNAME=Console
SonicCentral=C:\Program Files\Common Files\Sonic Shared\Sonic Central\
SystemDrive=C:
SystemRoot=C:\WINDOWS
TEMP=C:\DOCUME~1\SRIVAT~1\LOCALS~1\Temp
TMP=C:\DOCUME~1\SRIVAT~1\LOCALS~1\Temp
UATDATA=C:\WINDOWS\system32\CCM\UATData\D9F8C395-CAB8-491d-B8AC-179A1FE1BE77
USERDNSDOMAIN=X-XXXX.COM
USERDOMAIN=X-XXXX
USERNAME=xxxxxxxx
USERPROFILE=C:\Documents and Settings\xxxxxxxx
VSEDEFLOGDIR=C:\Documents and Settings\All Users\Application Data\McAfee\DesktopProtection
WF_RESOURCES=D:\oracle\ora92\WF\RES\WFus.RES
windir=C:\WINDOWS

C:\>lsnrctl service

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 22-JAN-2010 12:55:30

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx.xxx.xxx)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0
         LOCAL SERVER
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 2 handler(s) for this service...
    Handler(s):
      "D000" established:6716 refused:0 current:66 max:1002 state:ready
         DISPATCHER <machine: IFLEXSG2022, pid: 2072>
         (ADDRESS=(PROTOCOL=tcp)(HOST=xxxxx.xxx.xxx)(PORT=1322))
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully

C:\>




22-JAN-2010 12:44:36 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=iflexsg2022)(PORT=1521)))(VERSION=168821248)) * status * 0
22-JAN-2010 12:45:21 * service_update * orcl * 0
22-JAN-2010 12:46:35 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SrivathsanG))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=168821248)) * status * 0
22-JAN-2010 12:47:00 * service_update * orcl * 0
22-JAN-2010 12:47:05 * ping * 0
22-JAN-2010 12:47:05 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=iflexsg2022)(PORT=1521)))(VERSION=168821248)) * status * 0
22-JAN-2010 12:47:11 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86\perl.exe)(HOST=IFLEXSG2022)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=163.37.102.165)(PORT=3952)) * establish * orcl * 0
22-JAN-2010 12:51:03 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVICE_NAME=orcl)) * (ADDRESS=(PROTOCOL=tcp)(HOST=163.37.102.165)(PORT=4007)) * establish * orcl * 0
22-JAN-2010 12:52:05 * ping * 0
22-JAN-2010 12:52:05 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=iflexsg2022)(PORT=1521)))(VERSION=168821248)) * status * 0
22-JAN-2010 12:52:11 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86\perl.exe)(HOST=IFLEXSG2022)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=163.37.102.165)(PORT=4028)) * establish * orcl * 0
22-JAN-2010 12:53:24 * service_update * orcl * 0
22-JAN-2010 12:53:54 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86\perl.exe)(HOST=IFLEXSG2022)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=163.37.102.165)(PORT=4059)) * establish * orcl * 0
22-JAN-2010 12:55:18 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SrivathsanG))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=168821248)) * status * 0
22-JAN-2010 12:55:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SrivathsanG))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=168821248)) * services * 0
22-JAN-2010 12:57:05 * ping * 0
22-JAN-2010 12:57:05 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=SYSTEM))(COMMAND=status)(ARGUMENTS=64)(SERVICE=(ADDRESS=(PROTOCOL=TCP)(HOST=iflexsg2022)(PORT=1521)))(VERSION=168821248)) * status * 0
22-JAN-2010 12:57:11 * (CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=D:\oracle\product\10.1.0\db_1\perl\5.6.1\bin\MSWin32-x86\perl.exe)(HOST=IFLEXSG2022)(USER=SYSTEM))) * (ADDRESS=(PROTOCOL=tcp)(HOST=163.37.102.165)(PORT=4092)) * establish * orcl * 0
22-JAN-2010 12:57:27 * service_update * orcl * 0
22-JAN-2010 12:58:11 * (CONNECT_DATA=(SERVICE_NAME=orcl.iflexsg2022.com)(CID=(PROGRAM=D:\oracle\product\10.1.0\db_1\bin\sqlplus.exe)(HOST=IFLEXSG2022)(USER=SrivathsanG))) * (ADDRESS=(PROTOCOL=tcp)(HOST=163.37.102.165)(PORT=4100)) * establish * orcl.iflexsg2022.com * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

Re: ORA-12514 [message #440270 is a reply to message #440269] Thu, 21 January 2010 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
THANKS!

* establish * orcl * 0
* service_update * orcl * 0
* establish * orcl.iflexsg2022.com * 12514

The number on the right end of the line is completion code/status code.
0 status means SUCCESS!

so many clients get connected to SERVICE=orcl

Failure (12514) occurs when connecting to SERVICE=orcl.iflexsg2022.com

post contents of sqlnet.ora
Re: ORA-12514 [message #440282 is a reply to message #440270] Fri, 22 January 2010 00:19 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

I did not find, sqlnet.ora file. I did find one under sample directory.(D:\oracle\product\10.1.0\db_1\NETWORK\ADMIN\SAMPLE)

Please advice.

Thanks.
Re: ORA-12514 [message #440332 is a reply to message #440282] Fri, 22 January 2010 08:23 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
i doubt here you did something....
And you should have that file at the location.

Please execute the below
tnsping <Your connect_identifier> from cmd prompt.

IF you dont know the connect identifier execute
select '&_connect_identifier' from dual; or see it in your tnsnames.ora file
And post the result of TNSPING cmd it will give the path.

Z:\>tnsping ind

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-JAN-2010 19:51:43

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
[b]C:\oracle\product\10.2.0\db_3\network\admin\sqlnet.ora[/b]


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xx.x)(PORT = xxxx))) (CONNECT_DATA = (SERVICE_NAME =xxxxxx)))
OK (60 msec)

Z:\>


C:\oracle\product\10.2.0\db_3\network\admin\sqlnet.ora

sriram Smile
Re: ORA-12514 [message #440333 is a reply to message #440332] Fri, 22 January 2010 08:36 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
More over Have you searched before Posting here?
See these..
1)Code Report.
2)ORA-12154
3) Oracle error Manual

sriram Smile
Re: ORA-12514 [message #440336 is a reply to message #440282] Fri, 22 January 2010 09:16 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
post results from sqlplus as below

SQL> show parameter global

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size	     string
global_names			     boolean	 FALSE

[Updated on: Fri, 22 January 2010 09:17]

Report message to a moderator

Re: ORA-12514 [message #440437 is a reply to message #440336] Sun, 24 January 2010 16:55 Go to previous message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Sriram,

C:\>tnsping orcl

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 25-JAN-2010 06:40:25

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = XXXXX)(PORT = 1521))) (CONNECT_
DATA = (SERVICE_NAME = orcl.XXXXX.com)))
OK (20 msec)

SQL> select '&_connect_identifier' from dual;
old   1: select '&_connect_identifier' from dual
new   1: select 'orcl' from dual

'ORC
----
orcl

C:\>



I am not sure, why the sqlnet.ora is not present. Should I create one? Its not showing up in tnsping, as you have posted.


BlackSwan,

As requested.
SQL> show parameter global

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
global_names                         boolean     FALSE
SQL>




My tnsnames.ora entry
ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxxx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl.xxxx.com)
    )
  )


I changed the service_name parameter in tnsnames.ora to orcl. And it worked!!!. Thanks.
How does sqlplus know that it should pick SERVICE_NAME = orcl when the db name is not mentioned, while connecting.

Thanks
Previous Topic: Problems with Substitute Variable Prompts
Next Topic: ER DIAGRAM
Goto Forum:
  


Current Time: Sat Oct 01 12:38:54 CDT 2016

Total time taken to generate the page: 0.17743 seconds