我装的是Oracle10G企业版。数据库也在本机。已将DMP成功导入数据库,但是当用PL\SQL登录时,出现ORA-12154错误。我也查了一些资料,但是好像都没什么效果。下面把相关设置和问题描述一下,希望你们帮我解决下。1.环境变量。
查看了注册表,是正确的
ORACLE_HOME c:\oracle\product\10.1.0\Db_12.SQLPLUS
输入connect,然后按照提示输入用户名和密码后,显示Connected
但是输入connect user/password@orcl后,显示的也是ORA-12154错误,并出现Warning: You are no longer connected to ORACLE.3.确定监听服务已经起来。
4.用户名和密码确定正确
5.我的tnsnames.ora文件内容如下:# tnsnames.ora Network Configuration File: c:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XP-200907172259)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
6.sqlnet.ora文件内容如下
# sqlnet.ora Network Configuration File: c:\oracle\product\10.1.0\Db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.SQLNET.AUTHENTICATION_SERVICES= (NTS)NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)7.listener.ora文件如下:# listener.ora Network Configuration File: c:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XP-200907172259)(PORT = 1521))
)
)
)
请帮我找出原因,这个问题搞的我快要吐了。。
谢谢!
查看了注册表,是正确的
ORACLE_HOME c:\oracle\product\10.1.0\Db_12.SQLPLUS
输入connect,然后按照提示输入用户名和密码后,显示Connected
但是输入connect user/password@orcl后,显示的也是ORA-12154错误,并出现Warning: You are no longer connected to ORACLE.3.确定监听服务已经起来。
4.用户名和密码确定正确
5.我的tnsnames.ora文件内容如下:# tnsnames.ora Network Configuration File: c:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XP-200907172259)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
6.sqlnet.ora文件内容如下
# sqlnet.ora Network Configuration File: c:\oracle\product\10.1.0\Db_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.SQLNET.AUTHENTICATION_SERVICES= (NTS)NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)7.listener.ora文件如下:# listener.ora Network Configuration File: c:\oracle\product\10.1.0\Db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XP-200907172259)(PORT = 1521))
)
)
)
请帮我找出原因,这个问题搞的我快要吐了。。
谢谢!
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = c:\oracle\product\10.1.0\Db_1)
(PROGRAM = extproc)
)
)
修改为:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
#(SID_NAME = PLSExtProc)
(SID_NAME = orcl)
(ORACLE_HOME = c:\oracle\product\10.1.0\Db_1)
#(PROGRAM = extproc)
)
)
又如何,再不行的话,使用netca重建
C:\Documents and Settings\Administrator>tnsping orcl
TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 18-7月 -
2009 14:48:53
Copyright (c) 1997, 2003, Oracle. All rights reserved.
Used parameter files:
c:\oracle\product\10.1.0\Db_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION =
TNS-12533: TNS:illegal ADDRESS parametersshow parameter ins的结果如下:SQL> show parameter insNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_groups string
instance_name string orcl
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
SQL>
# tnsnames.ora Network Configuration File: c:\oracle\product\10.1.0\Db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XP-200907172259)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
其它不要
运行lsnrctl status看看 实例注册的状态。服务名不解析的错误,多是listener启动,但是没有根据你的tnsname找到实例。
21)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Produ
ction
Start Date 18-7月 -2009 15:34:11
Uptime 0 days 0 hr. 2 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File c:\oracle\product\10.1.0\Db_1\network\admin\listener.o
ra
Listener Log File c:\oracle\product\10.1.0\Db_1\network\log\listener.logListening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XP-200907172259)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XP-200907172259)(PORT=8080))(Present
ation=HTTP)(Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=XP-200907172259)(PORT=2100))(Present
ation=FTP)(Session=RAW))
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 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfullyC:\Documents and Settings\Administrator>
刚才没有看到你上面的回复,这里你的lsnrctl status的反馈是实例已经注册上去了,是orcl.看你后来的回复,好像是tnsname文件配置问题。把tnsname.ora文件备份一下,然后删除掉,重新用net manager建一个新的tnsname试试。
Listener 和 Tnsname中也许有其他隐性字符,删除后用netca或netmgr重配,其中的Host尽量用本机IP地址配置.
改成:SQLNET.AUTHENTICATION_SERVICES= (NONE)