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

ORA-12528

Table of Contents

• 1. 错误信息
• 2. 原因
• 3. 解决方法
  • 3.1. 实例未打开
  • 3.2. 监听相关
    • 3.2.1. 动态监听改为静态监听
    • 3.2.2. 配置local_listener

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)
  

Author: halberd.lee

Created: 2019-12-22 Sun 13:19

Validate