【故障处理】ORA-12545: Connect failed because target host or object does not exist
1 BLOG文档结构图
2 前言部分
2.1 导读和注意事项
各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~:
① 错误ORA-12545: Connect failed because target host or object does not exist的一种问题解决
Tips:
① 本文在ITpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和微信公众号(xiaomaimiaolhr)有同步更新
② 文章中用到的所有代码,相关软件,相关资料请前往小麦苗的云盘下载(http://blog.itpub.net/26736162/viewspace-1624453/)
③ 若文章代码格式有错乱,推荐使用搜狗、360或QQ浏览器,也可以下载pdf格式的文档来查看,pdf文档下载地址:http://blog.itpub.net/26736162/viewspace-1624453/,另外itpub格式显示有问题,可以去博客园地址阅读
④ 本篇BLOG中命令的输出部分需要特别关注的地方我都用灰色背景和粉红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33,thread 2的最大归档日志号为43是需要特别关注的地方;而命令一般使用黄色背景和红色字体标注;对代码或代码输出部分的注释一般采用蓝色字体表示。
List of Archived Logs in backup set 11
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48
1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58
2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49
2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53
[ZHLHRDB1:root]:/>lsvg -o
T_XDESK_APP1_vg
rootvg
[ZHLHRDB1:root]:/>
00:27:22 SQL> alter tablespace idxtbs read write;
====》2097152*512/1024/1024/1024=1G
本文如有错误或不完善的地方请大家多多指正,ITPUB留言或QQ皆可,您的批评指正是我写作的最大动力。
3 故障分析及解决过程
3.1 故障环境介绍
项目 |
source db |
db 类型 |
RAC |
db version |
11.2.0.3 |
db 存储 |
ASM |
OS版本及kernel版本 |
AIX 64位 6.1.0.0 |
3.2 故障发生现象及报错信息
同事过来找我说数据库不能连接,如下情况:
cssp@ZFZHLHRDB1:/cssp$ sqlplus tacc/tacc
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 12 16:35:42 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
ERROR:
ORA-12545: Connect failed because target host or object does not exist
Enter user-name:
ERROR:
ORA-12545: Connect failed because target host or object does not exist
注意:以上的连接方式采用了TWO_TASK这个环境变量,在Unix和Linux环境下,可以设置TWO_TASK环境变量,当用户连接数据库且没有指定服务名时,会自动利用TWO_TASK的设置作为环境变量连接数据库。有关这个环境变量的更多内容可以参考:http://blog.itpub.net/26736162/viewspace-2112274/ 和 http://blog.itpub.net/26736162/viewspace-2112277/
3.3 故障分析及解决过程
我采用了tnsping、用tnsnames连接和检查3大网络配置文件,都没有问题,如下:
cssp@ZFZHLHRDB1:/cssp$ tnsping oratacc1
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 12-AUG-2016 16:36:14
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/11.2.0/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ZFtaccDB-scan)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.173.15)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 22.188.173.16)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oratacc1)))
OK (80 msec)
cssp@ZFZHLHRDB1:/cssp$ sqlplus tacc/tacc@oratacc1
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 12 16:36:25 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
cssp@ZFZHLHRDB1:/cssp$ oerr ora 12545
12545, 00000, "Connect failed because target host or object does not exist"
// *Cause: The address specified is not valid, or the program being
// connected to does not exist.
// *Action: Ensure the ADDRESS parameters have been entered correctly; the
// most likely incorrect parameter is the node name. Ensure that the
// executable for the server exists (perhaps "oracle" is missing.)
// If the protocol is TCP/IP, edit the TNSNAMES.ORA file to change the
// host name to a numeric IP address and try again.
查看环境变量的设置:
cssp@ZFZHLHRDB1:/cssp$ more .profile
#For CICS
export CICS=/usr/lpp/cics
#export LANG=zh_CN.UTF-8
export LANG=en_US
#For ORACLE
ORACLE_BASE=/oracle/app/oracle
ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db
TWO_TASK=oratacc1
ORACLE_SID=oratacc1
ORACLE_TERM=vt100
ORACLE_OWNER=oracle
export ORACLE_BASE ORACLE_HOME ORACLE_SID ORACLE_TERM ORACLE_OWNER
LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0/db/lib:/oracle/app/oracle/product/11.2.0/db/lib32
#LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0/db/lib32
#export LIBPATH=$LIBPATH:/oracle/app/oracle/product/11.2.0/db/lib
NLS_LANG=American_America.ZHS16GBK
ORA_NLS33=/oracle/app/oracle/product/11.2.0/db/nls/data
export PATH=/usr/java5/bin:/cssp/jscs
export PATH=$PATH:/oracle/app/oracle/product/11.2.0/db/OPatch:/oracle/app/oracle/product/11.2.0/db/bin:/bin:/usr/ccs/bin:/usr/bin/X11:/sharebkup/data/peij
OPATCH_PLATFORM_ID=212
export LD_LIBRARY_PATH NLS_LANG ORA_NLS33 PATH TMP TMPDIR OPATCH_PLATFORM_ID
export AIXTHREAD_SCOPE=S
umask 022
export DBCA_RAW_CONFIG=/oracle/dbname_raw.conf
export PS1='LOGNAME@′‘hostname‘:′LOGNAME@′‘hostname‘:′PWD''$ '
export ORA_NL10=$ORACLE_HOME/nls/data
set -o vi
export EDITOR=vi
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_DATE_FORMAT=YYYY-MM-DD
export PATH=$PATH:/usr/vac/bin:/var/ifor:/usr/opt/ifor/ls/conf:/usr/lib/cobol/bin:/usr/lpp/cics/bin:/usr/lpp/cics/etc:/usr/lpp/dce/bin:/usr/lpp/mqm/bin:/usr/lpp/mqm/sa
mp/bin:/usr/mqm/samp/bin:HOME/bin:HOME/bin:ORACLE_HOME/bin:/csspd05u/test/bin:.:/usr/mqm/samp/bin
export LIBPATH=/usr/mqm/lib:ORACLEHOME/lib32:ORACLEHOME/lib32:COBDIR/coblib:/usr/lib:/lib:/usr/lpp/cics/lib:/usr/lpp/encina/lib:/usr/lpp/dce/lib:.
export NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lpp/cics/msg/%L/%N:/usr/lpp/nls/msg/en_US/%N:/usr/lpp/encina/msg/%L/%N:/usr/lpp/dce/lib/nls/msg/%L/%N
#For cssp
export JAVA_HOME=/usr/java5_64
export cssp_HOME=$HOME
export CLASSPATH=/csspd05u/CF
# PS1='[`hostname`:LOGNAME:LOGNAME:PWD]'
alias rm='rm -i'
alias lf='ls -aF'
alias l='ls -al'
set -o vi
可以看到上边的环境变量很乱,尤其PATH的配置有问题,所以我把这个部分的内容整理了一下,最后为:
umask 022
#For CICS
export CICS=/usr/lpp/cics
#export LANG=zh_CN.UTF-8
export LANG=en_US
#For ORACLE
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/oracle/app/oracle/product/11.2.0/db
export LD_LIBRARY_PATH=/oracle/app/oracle/product/11.2.0/db/lib:/oracle/app/oracle/product/11.2.0/db/lib32
export TWO_TASK=oratacc1
export ORACLE_SID=oratacc1
export ORACLE_TERM=vt100
export ORACLE_OWNER=oracle
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export ORA_NLS33=/oracle/app/oracle/product/11.2.0/db/nls/data
export OPATCH_PLATFORM_ID=212
export AIXTHREAD_SCOPE=S
export DBCA_RAW_CONFIG=/oracle/dbname_raw.conf
export PS1='LOGNAME@′‘hostname‘:′LOGNAME@′‘hostname‘:′PWD''$ '
export ORA_NL10=$ORACLE_HOME/nls/data
export EDITOR=vi
export PATH=$PATH:/usr/java5/bin:/cssp/jscs
export PATH=$PATH:/oracle/app/oracle/product/11.2.0/db/OPatch:/oracle/app/oracle/product/11.2.0/db/bin:/bin:/usr/ccs/bin:/usr/bin/X11:/sharebkup/data/peij
export PATH=PATH:/usr/vac/bin:/var/ifor:/usr/opt/ifor/ls/conf:/usr/lib/cobol/bin:/usr/lpp/cics/bin:/usr/lpp/cics/etc:/usr/lpp/dce/bin:/usr/lpp/mqm/bin:/usr/lpp/mqm/samp/bin:/usr/mqm/samp/bin:PATH:/usr/vac/bin:/var/ifor:/usr/opt/ifor/ls/conf:/usr/lib/cobol/bin:/usr/lpp/cics/bin:/usr/lpp/cics/etc:/usr/lpp/dce/bin:/usr/lpp/mqm/bin:/usr/lpp/mqm/samp/bin:/usr/mqm/samp/bin:HOME/bin:$ORACLE_HOME/bin:/csspd05u/test/bin:.:/usr/mqm/samp/bin
export LIBPATH=/usr/mqm/lib:ORACLEHOME/lib32:ORACLEHOME/lib32:COBDIR/coblib:/usr/lib:/lib:/usr/lpp/cics/lib:/usr/lpp/encina/lib:/usr/lpp/dce/lib:.
export NLSPATH=/usr/lib/nls/msg/%L/%N:/usr/lpp/cics/msg/%L/%N:/usr/lpp/nls/msg/en_US/%N:/usr/lpp/encina/msg/%L/%N:/usr/lpp/dce/lib/nls/msg/%L/%N
#For cssp
export JAVA_HOME=/usr/java5_64
export cssp_HOME=$HOME
export CLASSPATH=/csspd05u/CF
# PS1='[`hostname`:LOGNAME:LOGNAME:PWD]'
alias rm='rm -i'
alias lf='ls -aF'
alias l='ls -al'
set -o vi
修改.profile文件后,重新登录试试:
cssp@ZFZHLHRDB1:/cssp$ . .profile
cssp@ZFZHLHRDB1:/cssp$ sqlplus tacc/tacc
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 12 16:43:26 2016
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
可以正常登陆数据库了,可见环境变量的配置对sqlplus的登录有至关重要的作用。