oracle库连接问题
数据库是安装在Linux上的,我在linux下是可以操作的,可以查询库、表等等。数据库也是正常启动的。这些可以确定。
我在本地用PLSQL远程连接不上,tnsnames.ora文件的配置信息我也再三检查修改,依然连接不上。
报错如下:
远程报错 ORA-12514:tns 监听程序当前无法识别连接描述中请求的服务
求助! --------------------编程问答-------------------- 服务启动了? --------------------编程问答-------------------- 启动了。可以再linux下命令操作数据库的增删改查 --------------------编程问答-------------------- 1.#su - oracle 切换到oracle用户且切换到它的环境
2.$lsnrctl status 查看监听及数据库状态
3.$lsnrctl start 启动监听
4.$sqlplus / as sysdba 以DBA身份进入sqlplus
5.SQL>startup 启动db --------------------编程问答-------------------- tnsnames.ora
--------------------------
# tnsnames.ora Network Configuration File: /opt/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
------------------------------------
sqlnet.ora
# sqlnet.ora Network Configuration File: /opt/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
------------------------------------------------
listener.ora
# listener.ora Network Configuration File: /opt/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
)
)
--------------------编程问答-------------------- 那也就是说你在远程连接的时候出现问题,网络方面的设置看看 --------------------编程问答-------------------- The system is going down for reboot NOW!
[root@localhost ~]# Last login: Wed Jun 26 14:37:21 2013 from 192.168.1.32
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 26-JUN-2013 14:53:58
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /opt/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /opt/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /opt/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.254)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 26-JUN-2013 14:53:59
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /opt/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.254)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 26 14:54:47 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 847249408 bytes
Fixed Size 1222216 bytes
Variable Size 197134776 bytes
Database Buffers 645922816 bytes
Redo Buffers 2969600 bytes
Database mounted.
Database opened.
SQL>
--------------------编程问答-------------------- 用的是公司,局域网 --------------------编程问答-------------------- 哥来告诉你,是经验。在ORCL之前需要加一个空格,你试试瞧。。。
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.254)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
--------------------编程问答-------------------- 谢谢你,修改后重启了,试过了,不行的。 --------------------编程问答-------------------- 兄弟,可能我说错了。你贴出来的都是服务端的文件,这个可能没有问题。重点检查你的客户端的配置文件。
你要用pl sql客户端,必须要oracle的客户端的支持的。你本机装了oracle的客户端没?要用这个客户端配置服务器信息的。如下是我的客户端文件,你参考下。注意MY_DB之前是不能有空格的。
MY_DB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = xxxx)
)
) --------------------编程问答-------------------- # tnsnames.ora Network Configuration File: d:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
localHost =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = WIN-U5E1O27487R)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
Linux254 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=192.168.1.254)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
wuxi_ecard =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
我其他的都是可以连接的 --------------------编程问答-------------------- 你本地没有装oracle 的话用plsql不能连接远程数据库的 ;可以用下oracleSQLHandler这个链接数据库不用本地安装的 --------------------编程问答-------------------- 你本地装的ORACLE启监听了么?
补充:Java , Java SE