Oracle登录错误12560,登录错误-----ORA-12560: TNS: 协议适配器错误

问：操作系统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: 协议适配器错误

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

平台：windows+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的运行状态，该服务的启动与关闭也可通过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=

?/P>

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.

?/P>

SQL> $net stop oracleserviceTEST2

The OracleServiceTEST2 service is stopping...

The OracleServiceTEST2 service was stopped successfully.

?/P>

SQL> $net stop oracleserviceTEST1

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

The OracleServiceTEST1 service could not be stopped.

?/P>

SQL> $net start oracleserviceTEST1

The OracleServiceTEST1 service is starting.

The OracleServiceTEST1 service was started successfully.

?/P>

SQL> conn / as sysdba

已连接到空闲例程。

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

SQL> $net stop oracleserviceTEST1

The OracleServiceTEST1 service is stopping...

The OracleServiceTEST1 service was stopped successfully.

?/P>

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.

?/P>

SQL> conn / as sysdba

已连接到空闲例程。

?/P>

SQL> exit

已断开连接

?/P>

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.

?/P>

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.

?/P>

SQL> CONN / AS SYSDBA

已连接到空闲例程。

另外今天我也遇到这个错误提示

查看了一下listener  是启动的，并且注册表里是有oracle_sid=oratest的，只是今天我们网络不通，因为数据库在我本地机器上，所以开始并不认为是tnsnames.ora里的host=192.168.5.195的问题，但是又有所怀疑，所以把 ip 地址改成了 我机器的名字，保存以后 sqlplus aaa/aa233@oratest 就没问题了：)所以当网络不通的时候还是用自己机器名字的好