ORA-12528
Table of Contents
1 错误信息
ERROR:ORA-12528: TNS:listener: all appropriate instances are blocking new connections
2 原因
- 实例未打开(搭建standby时常见)
- 使用动态监听未配置local_listener引起
3 解决方法
3.1 实例未打开
举个粟子，使用duplicate 复制数据库时，目标实例只启动到nomount状态。此时，监听 中对应实例的状态就是“BLOCKED”. 解决方法是在TSN配置添加特殊标记(UR = A),示例 如下：
TEST_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10 )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = halberd)
(UR=A)
)
)
3.2 监听相关
3.2.1 动态监听改为静态监听
静态监听配置示例如下:
SID_LIST_LISTENER_FOREIGN =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = dbm012)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)
(GLOBAL_DBNAME=dbm01)
)
)
LISTENER_FOREIGN =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.24.69.47)(PORT = 1521))
)
)
3.2.2 配置local_listener
使用动态监听，但是没有配置local_listener参数。案例解决如下:
-
修改local_listener参数
-- 修改local_listener SYS@halberddg1>show parameter list NAME TYPE VALUE -------------------- ------ ------------------------------ listener_networks string local_listener string remote_listener string SYS@halberddg1>alter system set local_listener=‘(ADDRESS=(PROTOCAL=TCP)(HOST=10.1.10.131)(PORT=1521))‘; System altered. SYS@halberddg1> SYS@halberddg1> SYS@halberddg1> show parameter list NAME TYPE VALUE -------------------- ------ ------------------------------ listener_networks string local_listener string (ADDRESS=(PROTOCAL=TCP)(HOST=1 0.1.61.131)(PORT=1521)) remote_listener string SYS@halberddg1> exit # 修改监听配置文件（listener.ora）,将global_dbname(halberd 改为halberddg1) 部分内容如下： (GLOBAL_DBNAME = halberddg1) (ORACLE_HOME=/tpsys/app/oracle/product/12.1.0.2/dbhome_1) (SID_NAME=halberddg1)
Created: 2019-12-22 Sun 13:19
ORA-12528: TNS:listener: all appropriate instances are blocking new connections