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

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

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

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

有网友遇到登录时,oracle12560错误

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

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

问:操作系统windowsoracle9i在服务端,以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:
协议适配器错误

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

BTW:自己为什么没有拿到论坛上来问问大家,BS一下自己

经过测试发现,

1.确认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=TEST1
OS=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

已连接到空闲例程。

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

 

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

原文地址:https://www.cnblogs.com/liuzhuqing/p/7481068.html