ORA-01034和ORA-27101的解决方法，plsql登录提示标识符无效

最新推荐文章于 2023-09-05 22:04:26 发布

gototryVay

最新推荐文章于 2023-09-05 22:04:26 发布

阅读量5.6k

点赞数 2

文章标签：服务器数据库 oracle

本文链接：https://blog.csdn.net/gototryVay/article/details/126251071

版权

问题追踪思路

1. sqlplus进入操作页面，提示需要输入账号密码

报错如下：

ERROR:
	ORA-01034: ORACLE not available
	ORA-27101: shared memory realm does not exist

原因：
1. 首先检查内核参数设置以及alert.log 文件
2. 查看sqlnet.log日志
3. 检查listener是否配置正确命令： tnsping orcl 以上操作都是正常

2. 查看监听状态

lsnrctl status

简要日志信息

监听程序参数文件          C:\Users\Administrator\Desktop\PLSQL_Developer_8.0.3.1510\instantclient_11_2\listener.ora
监听程序日志文件          c:\app\administrator\diag\tnslsnr\WIN-WINS2K8-158\listener\alert\log.xml
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=WIN-WINS2K8-158)(PORT=1521)))
监听程序不支持服务
命令执行成功

3. 排查监听程序参数文件路径出现原因，跟踪到net manager

处理方案

1. 查看net manager里面路径配置

服务器位置：C:\Users\Administrator\Desktop\PLSQL_Developer_8.0.3.1510\instantclient_11_2
进入操作：开始–>Net Manager 进入

以下所有配置路径：C:\Users\Administrator\Desktop\PLSQL_Developer_8.0.3.1510\instantclient_11_2

2. 配置 tnsnames.ora文件

#tnsnames.ora Network Configuration File: C:\Users\Administrator\Desktop\PLSQL_Developer_8.0.3.1510\instantclient_11_2\tnsnames.ora
#Generated by Oracle configuration tools.

LISTENER_ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = WIN-WINS2K8-158)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER=DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.158)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID=CLRExtProc)
      (SERVICE_NAME = ORCL)
    )
  )

3. 配置listener.ora文件

# listener.ora Network Configuration File: C:\Users\Administrator\Desktop\PLSQL_Developer_8.0.3.1510\instantclient_11_2\listener.ora
# Generated by Oracle configuration tools.


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
    (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.158)(PORT = 1521))
    )
  )

DIAG_ADR_ENABLED_LISTENER = OFF

4. 执行命令

lsnrctl reload									####### 说明：重新加载监听文件

5. 重启服务

OracleOraDb11g_home1TNSListener					####### 说明：监听服务
OracleServiceORCL								####### 说明：oracle服务

6. 执行命令

lsnrctl status									####### 说明：查看监听状态

监听程序参数文件          C:\Users\Administrator\Desktop\PLSQL_Developer_8.0.3.1510\instantclient_11_2\listener.ora
监听程序日志文件          C:\app\Administrator\product\11.2.0\dbhome_1\network\log\listener.log
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.158)(PORT=1521)))
服务摘要..
服务 "orcl" 包含 1 个实例。
  实例 "orcl", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
命令执行成功