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

oerr ora 12528
12528, 00000, "TNS:listener: all appropriate instances are blocking new connections"
// *Cause: All instances supporting the service requested by the client
// reported that they were blocking the new connections. This condition may be
// temporary, such as at instance startup.
// *Action: Attempt the connection again. If error persists, then contact the
// administrator to check the status of the instances.


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

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


一、通过修改listener.ora的参数,把listener.ora动态注册设置为静态注册,然后重新启动监听
$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)
)
)

然后连接上数据库
alter database mount;
alter database open;

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