联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
客户的数据库通过监听访问报ORA-01034 ORA-27101错误无法正常使用：
SQL> conn app/xxxxx@192.168.129.145/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> conn app/xxxxx Connected.
检查数据库状态和tnsping都正常
SQL> select open_mode from v$database; OPEN_MODE -------------------- READ WRITE SQL> !tnsping orcl TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 18-OCT-2020 22:11:49 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.129.1) (PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl))) OK (0 msec)
检查主机之间ping操作也正常：
[XIFENFEI@DB1 trace]$ [XIFENFEI@DB1 trace]$ ping 192.168.129.1 PING 192.168.129.1 (192.168.129.1) 56(84) bytes of data. 64 bytes from 192.168.129.1: icmp_seq=1 ttl=64 time=0.025 ms 64 bytes from 192.168.129.1: icmp_seq=2 ttl=64 time=0.032 ms 64 bytes from 192.168.129.1: icmp_seq=3 ttl=64 time=0.034 ms ^C --- 192.168.129.1 ping statistics --- 3 packets transmitted, 3 received, 0% packet loss, time 2318ms rtt min/avg/max/mdev = 0.025/0.030/0.034/0.006 ms
检查数据库和监听配置：
[XIFENFEI@DB1 trace]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 22:13:50 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> show parameter name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string oracle
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
SQL>
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[XIFENFEI@DB1 trace]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 18-OCT-2020 22:15:16
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 18-OCT-2020 22:05:04
Uptime 0 days 0 hr. 10 min. 12 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File /usr/local/oracle/diag/tnslsnr/DB1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.129.1)(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 "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[XIFENFEI@DB1 trace]$ cat /usr/local/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1)
(GLOBAL_DBNAME= orcl)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /usr/local/oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.129.1) (PORT = 1521))
)
)
ADR_BASE_LISTENER = /usr/local/oracle
[XIFENFEI@DB1 trace]$
[XIFENFEI@DB1 trace]$
这里看到数据库配置和监听配置不匹配，因此需要调整。
[XIFENFEI@DB1 admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 18 22:21:16 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> conn app/xxxxx@192.168.129.145/orcl Connected. SQL> SQL> SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options [XIFENFEI@DB1 admin]$