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