ORA-12505,TNS:listener does not currently know of SID given in connect descriptor

最新推荐文章于 2024-02-19 16:16:35 发布

ifairstyle

最新推荐文章于 2024-02-19 16:16:35 发布

阅读量1k

点赞数

分类专栏： oracle-11g 文章标签： oracle11g

本文链接：https://blog.csdn.net/iFairStyle/article/details/69056166

版权

oracle-11g 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

这里写图片描述

这是tnsnames.ora和listener.ora的目录文件
D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
记住，每次更改这些文件都需要重启服务
计算机-右击管理-服务和应用程序，在找OracleOraDb11g_home1TNSListener 重新启动就好了

这是我的tnsnames.ora文件内容
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)

这是我的listener.ora文件内容
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = “EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll”)
)
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = WQ-20160623TKCJ)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

ADR_BASE_LISTENER = D:\app\Administrator
三个要注意的地方，
1、tnsnames.ora文件ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)
2、listener.ora文件中添加这一行
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
(SID_NAME = orcl)
)
3、 (ADDRESS = (PROTOCOL = TCP)(HOST = WQ-20160623TKCJ)(PORT = 1521)这个host为计算机名。
改完后又遇到了第二个bug，好伤心
ORA-01034: ORACLE not available ORA-27101
1启动oracle监听：cmd命令行窗口下，输入LsnrctL start，回车即启动监听。
2查看oracle的sid,一般是“orcl”，那么先手工设置一下oracle的sid，cmd命令窗口中，set ORACLE_SID=orcl
3再输入sqlplus /nolog,回车再输入 conn / as sysdba;回车
4再输入startup，回车.这步是启动oracle服务。

接下来又会报错
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name ‘LISTENER_ORCL’
解决办法:
1、修改tnsnames.ora文件，添加下面的语句：
LISTENER_ORCL =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
2、将本机的tnsnames.ora中的network name修改为LISTENER_ORCL，重启解决。

vi $ORACLE_HOME/network/admin/tnsnames.ora

LISTENER =

BJDG01 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Pro-BJDG01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LISTENER_ORCL)
)
)
最后成功的文件内容如下，讲真三天我被这东西折磨死了，痛不欲生，希望有遇到相同的人我可以帮助他们，或者私下问我1178890956

这里写代码片
listener.ora文件
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
    (GLOBAL_DBNAME = orcl)
    (ORACLE_HOME = D:\app\Administrator\product\11.2.0\dbhome_1)
    (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = WQ-20160623TKCJ)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = D:\app\Administrator


tnsnames.ora文件内容
LISTENER_ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = LISTENER_ORCL)
    )
    (LISTENER_ORCL =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
![一个测试用的官方网页链接https://localhost:1158/em/](https://img-blog.csdn.net/20170404112555530?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvaUZhaXJTdHlsZQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)