[转载]ORA12560: TNS: 协议适配器错误

原文地址：ORA-12560: TNS: 协议适配器错误作者：Gavin

使用Win7 HOME 在sql *plus 中连接数据库，出现 “ORA-12560: TNS: 协议适配器错误”。

参考：http://oracle114.itpub.net/post/21719/259479

******************************************************

有网友遇到登录时,oracle报12560错误

http://www.itpub.net/showthread.php?s=&postid=6774761

---------------------------------------------------------------

问：操作系统windows，oracle9i在服务端，以sys登陆不加服务名，登陆报错；如果加上服务名登陆正确。重启操作系统，重启db，现在在服务端无论用什么用户登陆只要不加服务名都错误；连接时加上服务名就正确。到底为什么呀？

C:>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 1月 31 08:46:08 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys/oracle@vassp as sysdba已连接。
SQL> exit从Oracle9i Release 9.2.0.1.0 - Production
JServer Release 9.2.0.1.0 - Production中断开

C:>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 1月 31 08:46:24 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect /as sysdba
ERROR:
ORA-12560: TNS: 协议适配器错误

---------------------------------------------------------------

由于这个问题也正困绕着我，一直在关注这个贴子的动态．今天看到PondKa的回复后，才豁然开朗

我的平台：windows2003+oracle9.2.0.1.0

SQL> select * from v$version;

BANNER
---------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

我的系统开始运行正常，系统只有一个数据库test1,为了自己做测试，又建了第两个数据库test2,这样当启动test1的服务后，当登录总是报如下错误：

ERROR:
ORA-12560: TNS: 协议适配器错误

自己尝试了几次没有搞定，发现登录时加上服务名可以正常登录，也就随它去了，但这个问题也依然在自己脑中．

ＢＴＷ：自己为什么没有拿到论坛上来问问大家，ＢＳ一下自己

经过测试发现，

１．确认sqlnet.ora文件中设置为操作系统验证

　　SQLNET.AUTHENTICATION_SERVICES= (NTS)

2．查看windows对应的服务是否开启

打开services查看，服务名oracleservice<SID>的运行状态，该服务的启动与关闭也可通过CML（命令行 COMMAND LINE）进行操作．

SQL> $net start oracleserviceTEST2The OracleServiceTEST2 service is starting.
The OracleServiceTEST2 service was started successfully.

SQL> $net stop oracleserviceTEST2The OracleServiceTEST2 service is stopping...
The OracleServiceTEST2 service was stopped successfully.

注：WINNT中，在sqlplus中运行dos命令时，命令前加＂$＂符号

3．查看本地环境变量ORACLE_SID,ORACLE_HOME是否正确设置

C:Documents and SettingsAdministrator> set

 

ALLUSERSPROFILE=C:Documents and SettingsAll Users
APPDATA=C:Documents and SettingsAdministratorApplication Data
ClusterLog=C:WINDOWSClustercluster.log
CommonProgramFiles=C:Program FilesCommon Files

COMPUTERNAME=LDY
ComSpec=C:WINDOWSsystem32cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=Documents and SettingsAdministrator
JSERV=F:oracleora92/Apache/Jserv/conf
LOGONSERVER=LDY
NUMBER_OF_PROCESSORS=1
oracle_home=F:oracleora92
oracle_sid=TEST1OS=Windows_NT．．．．

4．如果没有设置环境变量，请查看注册表中，HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE->HOME0中的ORACLE_SID,ORACLE_HOME项是否正确设置，如果oracle只有一个数据库，只需注册表中正确设置即可，无需设置环境变量．当然，如果像有多个数据库，需动态设置该环境变量

C:Documents and SettingsAdministrator>set oracle_home=

C:Documents and SettingsAdministrator>set oracle_sid=

C:Documents and SettingsAdministrator>set
ALLUSERSPROFILE=C:Documents and SettingsAll Users
APPDATA=C:Documents and SettingsAdministratorApplication Data
ClusterLog=C:WINDOWSClustercluster.log
CommonProgramFiles=C:Program FilesCommon Files
COMPUTERNAME=LDY
ComSpec=C:WINDOWSsystem32cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=Documents and SettingsAdministrator
JSERV=F:oracleora92/Apache/Jserv/conf
LOGONSERVER=LDY
NUMBER_OF_PROCESSORS=1
OS=Windows_NT．．．无此变量

C:Documents and SettingsAdministrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 2月 1 19:56:47 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> conn / as sysdba已连接。
5．如果有多个数据库，若想默认连接某个数据库，可不管当前注册表的设置，手动设置环境变量ORACLE_SID即可请看实验：

当前注册表ORACLE_SID=TEST1，

C:Documents and SettingsAdministrator>set oracle_sid=

 

C:Documents and SettingsAdministrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 2月 1 20:39:41 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

SQL> $net stop oracleserviceTEST2

The OracleServiceTEST2 service is stopping...

The OracleServiceTEST2 service was stopped successfully.

 

SQL> $net stop oracleserviceTEST1

The OracleServiceTEST1 service is stopping........

The OracleServiceTEST1 service could not be stopped.

 

SQL> $net start oracleserviceTEST1

The OracleServiceTEST1 service is starting.

The OracleServiceTEST1 service was started successfully.

 

SQL> conn / as sysdba

已连接到空闲例程。

--注：当有注册设置时，即使没有设置环境变量，此时系统可按注册表设置进行TEST1的登录

SQL> $net stop oracleserviceTEST1

The OracleServiceTEST1 service is stopping...

The OracleServiceTEST1 service was stopped successfully.

 

SQL> conn / as sysdba

ERROR:

ORA-12560: TNS: 协议适配器错误

警告: 您不再连接到 ORACLE。

--注：当相关WINNT服务ORACLESERVICETEST1没有开启，用户将无法正常连接TEST1

SQL> $net start oracleserviceTEST1

The OracleServiceTEST1 service is starting.

The OracleServiceTEST1 service was started successfully.

 

SQL> conn / as sysdba

已连接到空闲例程。

 

SQL> exit

已断开连接

 

C:Documents and SettingsAdministrator>set oracle_sid=TEST2

C:Documents and SettingsAdministrator>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 2月 1 20:45:29 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

 

SQL> conn / as sysdba

ERROR:

ORA-12560: TNS: 协议适配器错误

--注：环境变量的设置影响用户登录数据库的选择，此时设置为TEST2，而NT服务ORACLESERVICEQTEST2没有开启，所以显示登录错误

SQL> $NET START ORACLESERVICEQTEST2

The OracleServiceTEST2 service is starting.

The OracleServiceTEST2 service was started successfully.

 

SQL> CONN / AS SYSDBA

已连接到空闲例程。

*********************************************************************

 

同时，要使用管理员权限运行命令提示符。