[oracle@localhost db_1]$ sqlplus lubinsu/lubinsu
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 31 11:03:11 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux Error: 2: No such file or directory
一般情况下报这种错误都是数据库实例没有起起来的原因，用sysdba登录启动数据库实例：
Enter user-name: sys/lubinsu as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218968 bytes
Variable Size 88082024 bytes
Database Buffers 188743680 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
SQL> conn lubinsu/lubinsu
Connected.
SQL>
使用PL/SQL Dev连接数据库时，TNS提示连接超时
明显是网络连接方面的问题
先ping主机查看网络，如果正常再查看监听，使用tnsping主机
[oracle@localhost ~]$ ping 192.168.46.129
PING 192.168.46.129 (192.168.46.129) 56(84) bytes of data.
64 bytes from 192.168.46.129: icmp_seq=1 ttl=64 time=5.31 ms
64 bytes from 192.168.46.129: icmp_seq=2 ttl=64 time=0.202 ms
64 bytes from 192.168.46.129: icmp_seq=3 ttl=64 time=0.044 ms
--- 192.168.46.129 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2001ms
rtt min/avg/max/mdev = 0.044/1.852/5.312/2.447 ms
[oracle@localhost ~]$ tnsping 192.168.46.129
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 31-MAR-2013 11:07:33
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/home/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=192.168.46.129))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.46.129)(PORT=1521)))
TNS-12541: TNS:no listener
看来的确是监听没有起起来的问题：
进入监听管理，开启监听即可：
[oracle@localhost ~]$ lsnrctl
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 31-MAR-2013 11:11:20
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> help
The following operations are available
An asterisk (*) denotes a modifier or extended command:
start stop status
services version reload
save_config trace spawn
change_password quit exit
set* show*
LSNRCTL> status listener
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.46.129)(PORT=1521)))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
LSNRCTL> start listener
Starting /home/oracle/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 /home/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /home/oracle/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.46.129)(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 31-MAR-2013 11:11:47
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /home/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File /home/oracle/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.46.129)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orclsid", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>
再查看下:
[oracle@localhost ~]$ tnsping 192.168.46.129
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 31-MAR-2013 11:13:13
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/home/oracle/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=192.168.46.129))(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.46.129)(PORT=1521)))
OK (10 msec)
监听已经开启，看来连接可以正常通过了，但是很遗憾，还是报同样的错误，怀疑是防火墙搞的鬼，因为关闭防火墙之后是可以正常连接的：
查看防火墙规则表：
[root@localhost ~]# iptables -L -n
Chain INPUT (policy DROP)
target prot opt source destination
RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:21
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:20
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22
Chain FORWARD (policy DROP)
target prot opt source destination
RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
Chain RH-Firewall-1-INPUT (2 references)
target prot opt source destination
ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 icmp type 255
ACCEPT esp -- 0.0.0.0/0 0.0.0.0/0
ACCEPT ah -- 0.0.0.0/0 0.0.0.0/0
ACCEPT udp -- 0.0.0.0/0 224.0.0.251 udp dpt:5353
ACCEPT udp -- 0.0.0.0/0 0.0.0.0/0 udp dpt:631
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:631
ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
使用iptables -I INPUT -p tcp --dport 1521 -j ACCEPT配置允许通过1521端口访问数据库
[root@localhost ~]# iptables -I INPUT -p tcp --dport 1521 -j ACCEPT
--永久保存配置，因为一般用命令做的配置在重启之后都会失效，需要保存到文件中。
[root@localhost ~]# service iptables save
Saving firewall rules to /etc/sysconfig/iptables: [ OK ]
[root@localhost ~]# service iptables restart
Flushing firewall rules: [ OK ]
Setting chains to policy ACCEPT: filter [ OK ]
Unloading iptables modules: [ OK ]
Applying iptables firewall rules: [ OK ]
Loading additional iptables modules: ip_conntrack_netbios_n[ OK ]
[root@localhost ~]# iptables -L -n
Chain INPUT (policy DROP)
target prot opt source destination
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:1521
RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:21
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:20
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:22
Chain FORWARD (policy DROP)
target prot opt source destination
RH-Firewall-1-INPUT all -- 0.0.0.0/0 0.0.0.0/0
Chain OUTPUT (policy ACCEPT)
target prot opt source destination
Chain RH-Firewall-1-INPUT (2 references)
target prot opt source destination
ACCEPT all -- 0.0.0.0/0 0.0.0.0/0
ACCEPT icmp -- 0.0.0.0/0 0.0.0.0/0 icmp type 255
ACCEPT esp -- 0.0.0.0/0 0.0.0.0/0
ACCEPT ah -- 0.0.0.0/0 0.0.0.0/0
ACCEPT udp -- 0.0.0.0/0 224.0.0.251 udp dpt:5353
ACCEPT udp -- 0.0.0.0/0 0.0.0.0/0 udp dpt:631
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 tcp dpt:631
ACCEPT all -- 0.0.0.0/0 0.0.0.0/0 state RELATED,ESTABLISHED
ACCEPT tcp -- 0.0.0.0/0 0.0.0.0/0 state NEW tcp dpt:22
REJECT all -- 0.0.0.0/0 0.0.0.0/0 reject-with icmp-host-prohibited
OK，现在已经正常了。