今天遭遇ORA-12560: TNS: 协议适配器错误的问题,经过一番努力问题已经解决,与大家共享。
造成ORA-12560: TNS: 协议适配器错误的问题的原因有三个:
1.监听服务没有起起来。windows平台个一如下操作:开始---程序---管理工具---服务,打开服务面板,
启动oraclehome92TNSlistener服务。
2.database instance没有起起来。windows平台如下操作:开始---程序---管理工具---服务,打开服务
面板,启动oracleserviceXXXX,XXXX就是你的database SID.
3.注册表问题。regedit,然后进入HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0将该环境变量ORACLE_SI
D设置为XXXX,XXXX就是你的database SID.或者右几我的电脑,属性--高级--环境变量---系统变量--新建
,变量名=oracle_sid,变量值=XXXX,XXXX就是你的database SID.或者进入sqlplus前,在command line下
输set oracle_sid=XXXX,XXXX就是你的database SID.
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=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
已连接到空闲例程。