---
title: "ORA-12528问题解决 与 静态注册"
author: "winzenghua"
date: "2010-08-28T22:56:00Z"
tags: []
slug: "ora-12528"
draft: false
lastmod: "2010-08-28T22:56:00Z"
id: 987937
url: "https://java4gis.iteye.com/blog/987937"
---

原文：[http://blog.chinaunix.net/u/9800/showart\_396710.html](http://blog.chinaunix.net/u/9800/showart_396710.html)

startup nomount 之后

用sqlplus [sys/orcl@orcl](mailto:sys/orcl@orcl) as sysdba 连接的时候报 ORA-12528 的错误

但是用 sqlplus "/as sysdba", conn sys/orcl as sysdba 就可以连接上。

搜了下 ORA-12528 这个错误。

将listener.ora的动态参数设置为静态参数之后，在 nomount 时，也可以用 sqlplus [sys/orcl@orcl](mailto:sys/orcl@orcl) as sysdba登录了。

原文：[http://blog.chinaunix.net/u/9800/showart\_396710.html](http://blog.chinaunix.net/u/9800/showart_396710.html)

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&gt;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&gt;alter database mount;  
SQL&gt;alter database open;

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

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

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

用静态注册的风险是，如果在instance运行中，lisener重新启动，就找不到instance了。换言之，静态注册需要先启动lisener，再启动instance。而且静态模式下，lisener status显示的是unknown

而且，用动态的话，instance nomount状态下，一般是dba需要进行操作（比如恢复），这个时候一般直接登陆到host进行，不使用tns远程连接。

\--------------------------------------------------------  
\--------------------------------------------------------

listner.ora 修改之后：

SID\_LIST\_LISTENER =  
(SID\_LIST =  
(SID\_DESC =  
(SID\_NAME = PLSExtProc)  
(ORACLE\_HOME = /oracle/app/oracle/product/10.2.0.1/db\_1)  
(PROGRAM = extproc)  
)  
(SID\_DESC =  
(GLOBAL\_DBNAME = orcl)  
(ORACLE\_HOME = /oracle/app/oracle/product/10.2.0.1/db\_1)  
(SID\_NAME = orcl)  
)  
)  
LISTENER =  
(DESCRIPTION\_LIST =  
(DESCRIPTION =  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))  
)  
(ADDRESS\_LIST =  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))  
)  
)  
)

修改之前：

SID\_LIST\_LISTENER =  
(SID\_LIST =  
(SID\_DESC =  
(SID\_NAME = PLSExtProc)  
(ORACLE\_HOME = /oracle/app/oracle/product/10.2.0.1/db\_1)  
(PROGRAM = extproc)  
)  
)

LISTENER =  
(DESCRIPTION\_LIST =  
(DESCRIPTION =  
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))  
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))  
)  
)  
注意： 一个  
LISTENER  
(  
......  
)  
对应一个  
SID\_LIST\_LISTENER  
(  
....  
)  
如果修改有误，报错：  
Oracle Error :: TNS-01155 Incorrectly specified SID\_LIST\_string parameter in LISTENER.ORA