今天碰到一个错，很典型。

1. 错误 ORA-12514

C:\Documents and Settings\Administrator>sqlplus icmadmin/fvt6eip@rmdb

SQL*Plus: Release 10.2.0.3.0 - Production on Fri May 23 01:53:46 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:

Read the rest of this entry »

http://www.orafaq.com/node/5

这种方式使用local naming service 连接数据库。也就是读取tnsnames.ora来连接。

ICMNLSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = icmnlsdb.cn.ibm.com)
)
)

这个时候，

sqlplus icmadmin/fvt6eip@ICMNLSDB

sqlplus icmadmin/fvt6eip@icmnlsdb

都能连上数据库，也就是说 dbname 本身对大小写不敏感，并且这个值可以随便改。

SERVICE_NAME = icmnlsdb.cn.ibm.com 与 listener.org 里面 的GLOBAL_DBNAME必须一致。

(SID_DESC =
(GLOBAL_DBNAME = icmnlsdb.cn.ibm.com)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = icmnlsdb)
)

使用ezconnect佐证以上内容，

1.sqlplus icmadmin/fvt6eip@//localhost:1521/icmnlsdb.cn.ibm.com 连接成功。

2.sqlplus icmadmin/fvt6eip@//localhost:1521/icmnlsdb 连接失败。

oracle@linux:/u01/app/oracle/product/10.2.0/db_1/network/admin> sqlplus icmadmin/fvt6eip@//localhost:1521/icmnlsdb

SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 4 09:30:33 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

oracle@linux:~> sqlplus icmadmin/fvt6eip@icmnlsdb

SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 4 09:07:05 2008

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

ERROR:
ORA-12560: TNS:protocol adapter error

Enter user-name:

mentalink解释是：

ORA-12560: TNS:protocol adapter error

Cause: A generic protocol adapter error occurred.
Action: Check addresses used for proper protocol specification. Before reporting this error, look at the error stack and check for lower level transport errors. For further details, turn on tracing and reexecute the operation. Turn off tracing when the operation is complete.

于是我ping

oracle@linux:~> hostname
linux
oracle@linux:~> ping linux.cn.ibm.com
PING linux.cn.ibm.com (192.168.150.10) 56(84) bytes of data.
From 192.168.150.129: icmp_seq=2 Destination Host Unreachable
From 192.168.150.129 icmp_seq=2 Destination Host Unreachable
From 192.168.150.129 icmp_seq=3 Destination Host Unreachable
From 192.168.150.129 icmp_seq=4 Destination Host Unreachable

— linux.cn.ibm.com ping statistics —
4 packets transmitted, 0 received, +4 errors, 100% packet loss, time 2999ms

 

果然不通。

oracle@linux:~> tnsping icmnlsdb

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 04-MAY-2008 09:10:48

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

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = linux.cn.ibm.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = icmnlsdb)))
TNS-12560: TNS:protocol adapter error

 

同样的错。