oracle11g突然连不上了，ora-12514 和 ora-27101

最新推荐文章于 2025-01-07 15:52:38 发布

idarewho

最新推荐文章于 2025-01-07 15:52:38 发布

阅读量1.8k

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/idarewho/article/details/38487997

本文记录了解决因LISTENER.ORA配置不当导致的ORA-12514错误，以及由于SPFILE中LOCAL_LISTENER设置不正确引起的ORA-27101错误的过程。通过修改LISTENER.ORA文件和SPFILE中的相关设置，最终成功连接Oracle数据库。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

放假前还能正常连，回来后突然应用程序启动出错，用PL/SQL一连，发现出错，ORA-12514，查baidu后说是需要修改LISTENER.ORA，该文件位于D:\app\zk\product\11.2.0\dbhome_2\NETWORK\ADMIN下，增加了几行，变成

# listener.ora Network Configuration File: D:\app\zk\product\11.2.0\dbhome_2\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = D:\app\zk\product\11.2.0\dbhome_2)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:D:\app\zk\product\11.2.0\dbhome_2\bin\oraclr11.dll")
    )

这块是新加的

    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\app\zk\product\11.2.0\dbhome_2)
      (SID_NAME = ORCL)
    )

    这块是新加的
)

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
)

ADR_BASE_LISTENER = D:\app\zk

重启一堆服务后，再连，出错，ORA-27101，再BAIDU后，说是SPFILE有问题，LOCAL_LISTENER不对，这回操作有点麻烦了

1. SQLPLUS，用sys as sysdba连上

2. create pfile from spfile; 生成的pfile叫INITorcl.ora，位于D:\app\zk\product\11.2.0\dbhome_2\database下

3. 修改pfile里面的local_listener, 最后变成下面

*.audit_file_dest='D:\app\zk\admin\orcl\adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='D:\app\zk\oradata\orcl\control01.ctl','D:\app\zk\fast_recovery_area\orcl\control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='D:\app\zk\fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='D:\app\zk'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.local_listener='(ADDRESS_LIST=(Address=(Protocol=tcp) (Host=127.0.0.1)(Port=1521)))' 这是修改的地方
*.memory_target=1659895808
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

4. create spfile from pfile="INITorcl.ora";

5. startup

终于他妈的搞定了。。。