ORA-12528: TNS:listener: all appropriate instances are blocking new connections

ORA-12528问题是因为监听中的服务使用了动态服务,实例虽然启动，但没有注册到监听。实例是通过PMON进程注册到监听上的，而PMON进程需要在MOUNT状态下才会启动。所以造成了上面的错误。

解决这个问题,有三种方法：1、把监听设置为静态,2、在tnsnames.ora中追加(UR=A),3、重新启动服务。

一、通过修改listener.ora的参数,把listener.ora动态注册设置为静态注册,然后重新启动监听

# listener.ora Network Configuration File: $ORACLE_HOME\network\admin\listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = [IP])(PORT = 1521))

)

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = [DBNAME])

(ORACLE_HOME = [$ORACLE_HOME])

(SID_NAME = [SID])

)

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

二、启动到nomount状态,通过修改tnsnames.ora的参数# tnsnames.ora Network Configuration File: $ORACLE_HOME\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

SYK =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = [IP])(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = SYK)

(UR=A)    )

)

然后连接上数据库

SQL>alter database mount;

SQL>alter database open;

三、重启ORACLE或者重启ORACLE服务