解决ORA-12528

最新推荐文章于 2021-09-23 03:09:35 发布

原创最新推荐文章于 2021-09-23 03:09:35 发布 · 2.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #list #oracle #database #tcp #command

oracle概念专栏收录该内容

1 篇文章

订阅专栏

本文介绍了当遇到ORA-12528错误时，如何通过修改listener.ora和tnsnames.ora文件，将动态参数设置为静态，以及使用SQL命令或Oradim工具来启动、关闭数据库，从而解决问题。

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

lsnrctl status
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=lypch)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=//./pipe/EXTPROCipc)))
Services Summary...
Service " LSExtProc" has 1 instance(s).
  Instance " LSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ammicly" has 1 instance(s).
  Instance "ammicly", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

这个问题说明数据库没有Mount
连接数据库,发现:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
用sqlplus /nolog
sql>connect [email=sys/ammic@amicly]sys/ammic@amicly [/email] as sysdba
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

连接的问题可以通过修改listener.ora的参数,把动态的参数设置为静态的参数,红色标注部分,然后从新启动监听,就OK
SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
   (SID_NAME = PLSExtProc)
   (ORACLE_HOME = C:/oracle/product/10.1.0/db_1)
   (PROGRAM = extproc)
)
(SID_DESC =
   (GLOBAL_DBNAME = ammicly)
   (ORACLE_HOME = c:/oracle/product/10.1.0/db_1)
   (SID_NAME = ammicly)
)
  )
LISTENER =
  (DESCRIPTION_LIST =
(DESCRIPTION =
   (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lypch )(PORT = 1521))
   )
   (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
   )
)
  )

通过修改tnsnames.ora的参数,红色表注部分
# tnsnames.ora Network Configuration File: C:/oracle/product/10.1.0/db_1
/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
AMMICLY =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = lypch )(PORT = 1521))
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = ammicly)
   (UR=A)
)
  )
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
(ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(CONNECT_DATA =
   (SID = PLSExtProc)
   (PRESENTATION = RO)
)
  )

然后 sqlplus /nolog
SQL>alter database mount;
SQL>alter database open;

这样就可以启动数据库了;

或者用Oradim -shutdown -sid ammicly -shuttype srvc,inst -shutmode immediate -syspwd ammic;关闭数据库
在用oradim -startup -sid ammicly -starttype srvc,inst -syspwd ammic;启动数据库解决;也可以在截面上从启动数据库服务来解决;

总结 RA- 12528问题是因为监听中的服务使用了动态服务,系统启动后,数据库没有正常的MOUNT,因此在动态模式下,就会出现这个问题，用静态的就不会有这个问题,因此上面的方法就是把监听设置为静态,或者在tnsnames.ora中追加(UR=A)或者从启动服务,三种方法解决问题;