| ORA-12514 [message #440267] |
Thu, 21 January 2010 22:51  |
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 #440269 is a reply to message #440268] |
Thu, 21 January 2010 23:05  |
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 #440336 is a reply to message #440282] |
Fri, 22 January 2010 09:16 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
