error: ORA-01034:oracle not available ORA-27101:shared memory realm does not exist

数据库 waitig 519℃ 百度已收录 0评论

原文:http://www.linuxidc.com/Linux/2014-05/101940.htm

数据库:Oracle11g for Linux
 
1。查看日志可看到
 
Thu Mar 07 09:14:31 2013
WARNING: Subscription for node down event still pending
07-MAR-2013 09:14:31 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=localhost.localdomain)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE
=LISTENER)(VERSION=186646784)) * status * 0
 
 根据以往前辈的意见,在listener.ora中增加以下内容,然后重启监听。
 
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
 
 
 
2。关于2个ORA错误
 
  重启监听之后,在数据库主机上采用主机验证方式可以登录,说明登录数据库没问题,问题还是在监听上。再次查看local_listener, 其值为空。
 
  处理过程:
 
[oracle@localhost dbhome_1]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@localhost dbhome_1]$ cd network/admin
[oracle@localhost admin]$ ls -la
total 40
drwxr-xr-x. 3 oracle oinstall 4096 Mar 8 08:58 .
drwxr-xr-x. 11 oracle oinstall 4096 Dec 30 12:31 ..
-rw-r–r–. 1 oracle oinstall 739 Mar 8 08:58 listener.ora
-rw-r–r–. 1 oracle oinstall 12288 Mar 8 08:54 .listener.ora.swp
drwxr-xr-x. 2 oracle oinstall 4096 Dec 30 12:24 samples
-rw-r–r–. 1 oracle oinstall 187 May 7 2007 shrept.lst
-rw-r–r–. 1 oracle oinstall 222 Dec 31 18:08 sqlnet.ora
-rw-r—–. 1 oracle oinstall 331 Mar 3 16:00 tnsnames.ora
[oracle@localhost admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
ORCL =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.188)(PORT = 1521))
 (CONNECT_DATA =
 (SERVER = DEDICATED)
 (SERVICE_NAME = orcl)
 )
 )
 
[oracle@localhost admin]$ tnsping orcl
 
TNS Ping Utility for Linux: Version 11.2.0.1.0 – Production on 08-MAR-2013 09:25:28
 
Copyright (c) 1997, 2009, Oracle. All rights reserved.
 
Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
 

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.0.188)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@localhost admin]$ lsnrctl
 
LSNRCTL for Linux: Version 11.2.0.1.0 – Production on 08-MAR-2013 09:25:32
 
Copyright (c) 1991, 2009, Oracle. All rights reserved.
 
Welcome to LSNRCTL, type "help" for information.
 
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.188)(PORT=1521)))
STATUS of the LISTENER
————————
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 – Production
Start Date 08-MAR-2013 09:05:43
Uptime 0 days 0 hr. 19 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
Listening Endpoints Summary…
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.188)(PORT=1521)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary…
Service "ORCL" has 1 instance(s).
 Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service…
Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
LSNRCTL> exit
[oracle@localhost admin]$ sqlplus /nolog
 
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 8 09:25:49 2013
 
Copyright (c) 1982, 2009, Oracle. All rights reserved.
 
SQL> connect sys@orcl as sysdba
Enter password: 
ERROR:
ORA-01031: insufficient privileges
 

SQL> connect / as sysdba
Connected.
SQL> select status from v$instance;
 
STATUS
————
OPEN
 
SQL> connect hwdosconplid/pokiujuwdxawsz@orcl
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0
 

SQL> exit
[oracle@localhost admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (SID_NAME = PLSExtProc)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (PROGRAM = extproc)
 )
 (SID_DESC = 
(GLOBAL_DBNAME = ORCL) 
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1) 
(SID_NAME = ORCL) 

)
LISTENER =
 (DESCRIPTION_LIST =
 (DESCRIPTION =
 (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.188)(PORT = 1521))
 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
 )
 )
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
 
 
 
同时看到监听日志中有如下记录:
 
***********************************************************************
Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=orcl)(CID=(PROGRAM=emagent)(HOST=localhost.localdomain)(USER=oracle))))
 
VERSION INFORMATION:
 TNS for Linux: Version 11.2.0.1.0 – Production
 TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 – Production
 Time: 07-MAR-2013 18:01:20
 Tracing not turned on.
 Tns error struct:
 ns main err code: 12541
 TNS-12541: TNS:no listener
 ns secondary err code: 12560
 nt main err code: 511
 TNS-00511: No listener
 nt secondary err code: 111
 
 
 
 
 
[oracle@localhost admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
 
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
 
ADR_BASE = /u01/app/oracle
 
[oracle@localhost admin]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
[oracle@localhost admin]$ cd  $ORACLE_HOME/dbs
[oracle@localhost dbs]$ ls -l
total 28
-rw-rw—-. 1 oracle oinstall 1544 Mar 8 09:05 hc_orcl.dat
-rw-r–r–. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r–r–. 1 oracle oinstall 1202 Mar 7 15:35 initorcl.ora
-rw-r—–. 1 oracle oinstall 24 Dec 30 12:51 lkORCL
-rw-r—–. 1 oracle oinstall 1536 Dec 30 13:58 orapworcl
-rw-r—–. 1 oracle oinstall 3584 Mar 7 18:03 spfileorcl_back.ora
-rw-r–r–. 1 root root 2560 Mar 7 18:06 spfileorcl.ora
[oracle@localhost dbs]$ export ORACLE_SID=orcl
[oracle@localhost dbs]$ sqlplus /nolog
 
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 8 09:41:42 2013
 
Copyright (c) 1982, 2009, Oracle. All rights reserved.
 
SQL> connect / as sysdba
Connected.
 
SQL> show parameter local;
 
NAME TYPE VALUE
———————————— ———– ——————————
local_listener string
log_archive_local_first boolean TRUE
parallel_force_local boolean FALSE
 

[oracle@localhost dbs]$ ls -l
total 28
-rw-rw—-. 1 oracle oinstall 1544 Mar 8 09:05 hc_orcl.dat
-rw-r–r–. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r–r–. 1 oracle oinstall 1202 Mar 7 15:35 initorcl.ora
-rw-r—–. 1 oracle oinstall 24 Dec 30 12:51 lkORCL
-rw-r—–. 1 oracle oinstall 1536 Dec 30 13:58 orapworcl
-rw-r—–. 1 oracle oinstall 3584 Mar 7 18:03 spfileorcl_back.ora
-rw-r–r–. 1 root root 2560 Mar 7 18:06 spfileorcl.ora
[oracle@localhost dbs]$ su
Password: 
[root@localhost dbs]# chmod 777 spfileorcl.ora
[root@localhost dbs]# ls -l
total 28
-rw-rw—-. 1 oracle oinstall 1544 Mar 8 09:05 hc_orcl.dat
-rw-r–r–. 1 oracle oinstall 2851 May 15 2009 init.ora
-rw-r–r–. 1 oracle oinstall 1202 Mar 7 15:35 initorcl.ora
-rw-r—–. 1 oracle oinstall 24 Dec 30 12:51 lkORCL
-rw-r—–. 1 oracle oinstall 1536 Dec 30 13:58 orapworcl
-rw-r—–. 1 oracle oinstall 3584 Mar 7 18:03 spfileorcl_back.ora
-rwxrwxrwx. 1 root root 2560 Mar 7 18:06 spfileorcl.ora
[root@localhost dbs]# exit
exit
[oracle@localhost dbs]$ sqlplus /nolog
 
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 8 09:49:38 2013
 
Copyright (c) 1982, 2009, Oracle. All rights reserved.
 
SQL> connect / as sysdba
Connected.
SQL> alter system set local_listener = ‘(ADDRESS = (PROTOCOL=TCP)(HOST=192.168.0.188)(PORT=1521)(SID=orcl))’ scope=spfile;
 
System altered.
 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
 
Total System Global Area 6680915968 bytes
Fixed Size 2213936 bytes
Variable Size 3892316112 bytes
Database Buffers 2751463424 bytes
Redo Buffers 34922496 bytes
Database mounted.
Database opened.
SQL> connect hwdosconplid/pokiujuwdxawsz@orcl
Connected.
SQL> OK
SP2-0042: unknown command "OK" – rest of line ignored.
SQL>
 
 
 
如果local_listener缺失,将导致自动实例注册失败,数据库实例不会识别Listener;当Listener连接数据库实例的时候,由于Listener没有注册,导致了这两个错误

更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12

本文永久更新链接地址:http://www.linuxidc.com/Linux/2014-05/101940.htm


本文由【waitig】发表在等英博客
本文固定链接:error: ORA-01034:oracle not available ORA-27101:shared memory realm does not exist
欢迎关注本站官方公众号,每日都有干货分享!
等英博客官方公众号
点赞 (0)分享 (0)