重庆思庄Oracle、Redhat认证学习论坛

标题: ORA-12528:TNS:listener: all appropriate instances are blocking new connections [打印本页]

作者: 王亮 时间: 2020-12-29 18:33
标题: ORA-12528:TNS:listener: all appropriate instances are blocking new connections
ORA-12528:TNS:listener: all appropriate instances are blocking new connections

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

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

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

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
(DESCRIPTION =
   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
   (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
)
  )
SID_LIST_LISTENER =
  (SID_LIST =
(SID_DESC =
   (GLOBAL_DBNAME = orcl)
   (ORACLE_HOME =/u01/app/oracle/product/11.2.0/dbhome_1)
   (SID_NAME = orcl)
)
  )
静态注册的风险:如果在instance运行中，lisener重新启动，就找不到instance了。静态注册需要先启动lisener，再启动instance。且静态模式下，lisener status显示的是unknown

方法2、启动到nomount状态,通过修改tnsnames.ora的参数
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
SYK =
  (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
(CONNECT_DATA =
   (SERVER = DEDICATED)
   (SERVICE_NAME = orcl)
   (UR=A)
)
  )
然后连接上数据库
SQL>alter database mount;
SQL>alter database open;

方法3、重启ORACLE或者重启ORACLE服务
在oracle帐户下依序执行如下命令：
sqlplus / as sysdba;//在其它帐户(如root)下执行可能会报错（ORA-01031）因为这些帐户没有在dba组中

shutdown immediate;
startup;

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)