ORA-27101: shared memory realm does not exist

[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，现在已经正常了。