ITPub博客

首页 > 数据库 > Oracle > Oracle数据库连接常见问题诊断

Oracle数据库连接常见问题诊断

原创 Oracle 作者:moonsoft 时间:2019-01-09 14:27:06 0 删除 编辑

Oracle数据库常见问题诊断-SQL*NET

1 TNS-12154 Error ORA-12154

  2NL-00462 Error ORA-00462

  3NL-00405 Error ORA-00405

  4TNS-01155 Error ORA-01155

 5TNS-12537 TNS-12560TNS-00507 Error

  6TNS-12203 Error

  7TNS-12533 Error

[@more@]

Oracle数据库常见问题诊断-SQL*NET

1 TNS-12154 Error ORA-12154

  特征:SQL*NET没有找到连接串或别名

  原因1:(1)没有找到TNSNAMES.ORA文件,该文件的缺省路径为:

  Windows 95/98 client

  SQL*Net 2.x - ORAWIN95NETWORKADMIN

  Net8 - ORAWIN95NET80admin

  Net8i - ORACLEORA81NETWORKADMIN

  Windows NT client

  SQL*Net 2.x - ORANTNETWORKADMIN

  Net8 - ORANTNET80ADMIN

  Net8i - ORACLEORA81NETWORKADMIN

  UNIX Client

  $ORACLE_HOME/NETWORK/ADMIN

  or /etc

  or /var/opt/oracle

  . 客户端设置TNS_ADMIN

  在注册表 HKEY_LOCAL_MACHINESoftwareOracle 设置TNS_ADMIN. TNS_ADMIN 指向TNSNAMES.ORA文件的位置

  措施:按上述说明检查文件位置

  原因2:(2)TNSNAMES.ORA文件中内容格式不对

  措施:检查文件格式,标准格式如下:

  DEV1.WORLD =

  (DESCRIPTION = (ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP) (Host = 145.45.78.56) (Port = 1521) ) )

  (CONNECT_DATA = (SID = ORCL) ) )

  原因3: (3) TNSNAMES.ORASQLNET.ORA不一致

  措施: SQLNET.ORA格式如下:

  TRACE_LEVEL_CLIENT = OFF

  SQLNET.AUTHENTICATION_SERVICES = (NONE)

  NAMES.DIRECTORY_PATH = (TNSNAMES)

  AUTOMATIC_IPC = OFF

  因为没有NAMES.DEFAULT_DOMAIN=world,所以DEV1.WORLD连接串不能连接,可以在TNSNAMES.ORA中增加

  DEV1 =

  (DESCRIPTION = (ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP) (Host = 145.45.78.56) (Port = 1521) ) )

  (CONNECT_DATA = (SID = ORCL) ) )

  此时可连接。

  2NL-00462 Error ORA-00462

  特征:监听器无法启动

  原因:LISTENER.ORA文件内容错误,例如,括号不匹配、参数名错误等、多余的空格等。

  措施:重新编辑文件

  3NL-00405 Error ORA-00405

  特征:LISTENER.ORA文件不可读或找不到

  原因:LISTENER.ORA文件内容错误,例如,括号不匹配、参数名错误等、多余的空格等。

  措施:确保文件位置在缺省目录下$ORACLE_HOME/network/admin,或设置环境变量TNS_ADMIN指向这个文件所在位置。

  4TNS-01155 Error ORA-01155

  特征:LISTENER.ORA文件中SID_LIST_LISTENER变量赋值不对

  原因:LISTENER.ORA文件内容错误,SID_LIST_LISTENER变量赋值不对。

  措施:确保文件中赋值正确,$ORACLE_HOME/network/admin/samples目录下有样例文件,可供参考。

  5TNS-12537 TNS-12560TNS-00507 Error

  特征:LISTENER.ORA文件中引用了一个无效的协议适配程序

  原因:LISTENER.ORA文件中PROCOTOL变量赋值不对。

  措施:确保文件中赋值正确,一般用TCP协议,$ORACLE_HOME/network/admin/samples目录下有样例文件,可供参考。用adapters tnslsnr检查协议程序

  6TNS-12203 Error

  特征:不能与目标连接

  原因:监听器是否启动;别名是否配置

  措施:检查监听器是否启动,例如lsnrctl status;检查TNSNAMES.ORA的别名配置

  7TNS-12533 Error

  特征:不能与目标连接

  原因:非法的address参数,

  措施:检查TNSNAMES.ORAADDRESS参数

ORA-12154: TNS:could not resolve service name First open the tnsnames.ora file and check the settings eg-

CHECK1 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =CHECK2)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = CHECK3) ) )

IN THE ABOVE GIVEN SCRIPT

CHECK1---> Data Source Name u r Connecting from the front end

CHECK2---> Server name to which u r connecting

CHECK3---> Service name/SID of the Data base u r connecting

* - c:> ping CHECK2 (Host ip or name) if you cannot ping that means the client server network problem. Else

* - c:> telnet CHECK2:1521 if you cannot connect to port ,network problem . Else

* - c:> TNSPING CHECK1 if still the same error check SQLNET.ORA Commect other settings and keep only following line and TNSPING. SQLNET.AUTHENTICATION_SERVICES= (NTS) If not NTS change to NONE and TNSPING. If Still not working check

whether a Spyware running on your PC. Run spyware detection tool. Do you have multiple Oracle installations on your machines like Oracle client and Developer ?

If so you'll have to set up the tnsnames.ora on both HOMES. Or else setup the registry. Add string value TNS_ADMIN to HKEY_LOCAL_MACHINESOFTWAREORACLE Set the values of TNS_ADMIN to the folder where the tnsnames.ora you set up resides. Eg. C:oracleora92admin

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/226700/viewspace-851256/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-20

  • 博文量
    412
  • 访问量
    332284