ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 连接数据库报错ORA-12514

连接数据库报错ORA-12514

原创 Linux操作系统 作者:beret595 时间:2011-09-16 01:31:52 0 删除 编辑

在本机安装VMWARE虚机上,无法通过服务名连接数据库

 

 

连接时报错:

[oracle@yangtk2 ~]$ lsnrctl status

LSNRCTL for Linux: Version11.1.0.6.0 - Production on 01-SEP-2008 17:36:03

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
&[5bIsWn&N13332800STATUS of the LISTENER
lLSe*r13332800------------------------ITPUB个人空间9?yV!x;z Z$d&Rg
Alias                     LISTENERITPUB个人空间+~a.A n b
Version                   TNSLSNR for Linux: Version11.1.0.6.0 - Production
v,h m*vxH13332800Start Date                01-SEP-2008 17:01:31
2rj7g$^'t7MR uw13332800Uptime                    0 days 0 hr. 34 min. 32 sec
[as iw*T#i(qr13332800Trace Level               off
9U#YA4BTw13332800Security                  ON: Local OS AuthenticationITPUB个人空间:U1V(YBK
SNMP                      OFFITPUB个人空间v@dzUW
Listener Parameter File   /data/oracle/product/11.1/network/admin/listener.oraITPUB个人空间;ydc"I v5U G
Listener Log File         /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xmlITPUB个人空间D\pl)| ^q
Listening Endpoints Summary...
Ea1?)Z#~-X)SX+Fo13332800  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
n N%e^x2?$r X13332800  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.25.1.104)(PORT=1521)))ITPUB个人空间t t'ifqf
The listener supports no services
KNY1H*h `)}/]z13332800The command completed successfully

检查listener.oratnsnames.ora都未发现异常:

[oracle@yangtk2 admin]$ more listener.ora
6Cb]-G4s$D6\ss({13332800# listener.ora Network Configuration File: /data/oracle/product/11.1/network/admin/listener.oraITPUB个人空间!S,RD6A?,c1N n5E
# Generated by Oracle configuration tools.

 

LISTENER =
TO7_9_A'fqqA Z13332800  (DESCRIPTION_LIST =
w}P;Mw3V;nV13332800    (DESCRIPTION =ITPUB个人空间L8]2G$Hv!d {
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
"f2X(B6G6[Ks13332800      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.25.1.104)(PORT = 1521))ITPUB个人空间+@g~[}.h0niY
    )
2M8i/L C-L5Du13332800  )
P5k)r/?T13332800[oracle@yangtk2 admin]$ more tnsnames.ora
4B-m&F-p3ZD6V gK13332800# tnsnames.ora Network Configuration File: /data/oracle/product/11.1/network/admin/tnsnames.oraITPUB个人空间X HEaz
# Generated by Oracle configuration tools.

ORA11G_S =
O i7Z w$N;N u k13332800  (DESCRIPTION =ITPUB个人空间%d Inm6sF
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.25.1.104)(PORT = 1521))ITPUB个人空间-F+YQ7krm X.N
    (CONNECT_DATA =
,b;b;@ L)KH!D C13332800      (SERVER = DEDICATED)ITPUB个人空间![wM Xfs,G0S
      (SERVICE_NAME = ora11g_s.ytk-thinkpad)ITPUB个人空间 nb Qjd2zH
    )
w3`j'{:z&c13332800  )

ORA11G_P =ITPUB个人空间-Y!Z8yN*}[3d]
  (DESCRIPTION =
qsfu5KeL'JX13332800    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.25.1.102)(PORT = 1521))
)M O#a6H} qc13332800    (CONNECT_DATA =ITPUB个人空间:w Mm'~q2z-CA
      (SERVER = DEDICATED)
zj&s7Gro1m13332800      (SERVICE_NAME = ora11g_p.ytk-thinkpad)
1n#JK K.gQ13332800    )ITPUB个人空间(ye;^Wph-Ha%mr"{
  )

 

而且以前这个配置没有任何问题,就是这次启动虚机后才出现的问题。

查询了metalink,虽然上面记载了很多ORA-12514错误,但是没有发现什么有帮助的线索。

由于这个错误以前并没有出现,所以怀疑是最近修改了什么东西导致了这个问题。而启动之后做了唯一的修改就是修改了主机的IP地址。

通过ifconfig修改了主机IP地址,莫非是修改错误导致了问题,不过现在通过IP地址可以正常的登陆主机,而且tnsping也没有任何的问题:

[oracle@yangtk2 admin]$ tnsping ora11g_s

TNSPingUtility for Linux: Version11.1.0.6.0 - Production on 01-SEP-2008 17:54:31

Copyright (c) 1997, 2007, Oracle.  All rights reserved.

Used parameter files:ITPUB个人空间4v$A,A!m _j
/data/oracle/product/11.1/network/admin/sqlnet.ora

ITPUB个人空间$UN b.Z ~X1g O"cp
Used TNSNAMES adapter to resolve the alias
8VhN(q*x:qO \l13332800Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.25.1.104)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora11g_s.ytk-thinkpad)))
U)Z7?A+W7?13332800OK (0 msec)

最后检查listner的日志:

[oracle@yangtk2 admin]$ tail -20 /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xmlITPUB个人空间zA:yTuZ!d@w
 
q$A,c(dJ _+t13332800ITPUB个人空间$mmK6ZHd
ITPUB个人空间 {Lu H"QV"f.W&Z

 type='UNKNOWN' level='16' host_id='yangtk2.ytk-thinkpad'
+m~5T(Zw2c13332800 host_addr='172.25.4.70'>
K_.WGF3_13332800 01-SEP-2008 17:54:31 * ping * 0ITPUB个人空间6B+bz1`\zw6F*J
 
ITPUB个人空间N)^@ kvLkr

,HPpZ3o8S13332800ITPUB个人空间_Clmb8}Wy
 type='UNKNOWN' level='16' host_id='yangtk2.ytk-thinkpad'
n*PPb_)y13332800 host_addr='172.25.4.70'>ITPUB个人空间(F}/Ew]h!|
 WARNING: Subscription for node down event still pending
`jug1}5e*u&` P13332800 
ITPUB个人空间w0]7V{;r#W,VOGT'~

2q4H7|O/Wb V13332800ITPUB个人空间2kb,D,Q_ c
 type='UNKNOWN' level='16' host_id='yangtk2.ytk-thinkpad'ITPUB个人空间6q TX R#p^/l z.v f
 host_addr='172.25.4.70'>
*zTJoEf13332800 01-SEP-2008 17:58:24 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=yangtk2.ytk-thinkpad)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * status * 0ITPUB个人空间"`"]^hQVO h X p
 

!y*?DgT6O13332800

发现日志中的IP地址并没有改变,看来是修改IP的时候遗漏了什么位置。

检查HOSTS文件,发现里面的配置忘了进行修改:

[oracle@yangtk2 admin]$ more /etc/hosts
C-n(_'oo5_&n5t wu13332800# Do not remove the following line, or various programs
)UX2~ a$VKHUN13332800# that require network functionality will fail.
Ud%wm8O9pT%N6pT13332800172.25.4.70     yangtk2.ytk-thinkpad
?^8A#\ Uz13332800127.0.0.1       localhost.localdomain   localhost

hosts文件中的ip也修改为当前的IP后,问题解决。

[oracle@yangtk2 ~]$ lsnrctl stop

LSNRCTL for Linux: Version11.1.0.6.0 - Production on 01-SEP-2008 18:01:00

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
g)F ry-W13332800The command completed successfullyITPUB个人空间4@2~ Y$g#EErh!L9X_
[oracle@yangtk2 ~]$ lsnrctl start

LSNRCTL for Linux: Version11.1.0.6.0 - Production on 01-SEP-2008 18:01:10

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /data/oracle/product/11.1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version11.1.0.6.0 - ProductionITPUB个人空间 y"`8Uw R~7v Gz)FK
System parameter file is /data/oracle/product/11.1/network/admin/listener.oraITPUB个人空间O,J ^DR
Log messages written to /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xmlITPUB个人空间uAC4h7Z b
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
XN$h2u9L#S*Hqiv13332800Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.25.1.104)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))ITPUB个人空间 n+qa/j(U%iA
STATUS of the LISTENER
R-rI'k^-CqI13332800------------------------
6Ya;EB u13332800Alias                     LISTENER
.{'N5In@2V13332800Version                   TNSLSNR for Linux: Version11.1.0.6.0 - ProductionITPUB个人空间8v7s ^G9bT$U!D
Start Date                01-SEP-2008 18:01:10ITPUB个人空间"^;a'`q6YR
Uptime                    0 days 0 hr. 0 min. 0 sec
x7\Rm#h)gB13332800Trace Level               off
GW)zyA`%} |P13332800Security                  ON: Local OS AuthenticationITPUB个人空间k9dg!Z1jtZi
SNMP                      OFFITPUB个人空间Hn#c7lv0P ^bXp
Listener Parameter File   /data/oracle/product/11.1/network/admin/listener.oraITPUB个人空间 x0{$\_ CR6p
Listener Log File         /data/oracle/diag/tnslsnr/yangtk2/listener/alert/log.xmlITPUB个人空间;} o)Y+oWN3u
Listening Endpoints Summary...ITPUB个人空间jlB.I%y-[Q"K)f
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))ITPUB个人空间@nyOb/[?,M'n
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.25.1.104)(PORT=1521)))
gb-SIF?rkd J'pE13332800The listener supports no services
(? gI6ET9d13332800The command completed successfully

稍等一会,通过sqlplus连接数据库:

[oracle@yangtk2 ~]$ sqlplus sys/test@ora11g_s as sysdba

SQL*Plus: Release11.1.0.6.0 - Production on Mon Sep 1 18:03:16 2008

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


b } }JG }13332800Connected to:ITPUB个人空间&cR0F8X-@E3s4y M
Oracle Database11gEnterprise Edition Release11.1.0.6.0 - ProductionITPUB个人空间+p#b2DBgV@h
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

 

转自:http://space.itpub.net/?uid-4227-action-viewspace-itemid-446940

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

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

注册时间:2011-07-28

  • 博文量
    16
  • 访问量
    24814