一、异常说明

因磁盘满了导致oracle服务挂掉，代码或plsql连接oracle报"ORA-12528, TNS:listener: all appropriate instances are blocking new connections..Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error"异常，详细日志如下

11356 [localhost-startStop-1] ERROR org.hibernate.util.JDBCExceptionReporter  – Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:@b@ORA-12528, TNS:listener: all appropriate instances are blocking new connections@b@ )@b@11357 [localhost-startStop-1] WARN  org.hibernate.cfg.SettingsFactory  – Could not obtain connection metadata@b@org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error:@b@ORA-12528, TNS:listener: all appropriate instances are blocking new connections@b@ )@b@        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)@b@        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)@b@        at org.springframework.orm.hibernate3.LocalDataSourceConnectionProvider.getConnection(LocalDataSourceConnectionProvider.java:81)@b@        at org.hibernate.cfg.SettingsFactory.buildSettings(SettingsFactory.java:84)@b@        at org.hibernate.cfg.Configuration.buildSettingsInternal(Configuration.java:2090)@b@        at org.hibernate.cfg.Configuration.buildSettings(Configuration.java:2086)@b@        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1311)@b@        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.newSessionFactory(LocalSessionFactoryBean.java:805)@b@        at org.springframework.orm.hibernate3.LocalSessionFactoryBean.buildSessionFactory(LocalSessionFactoryBean.java:745)@b@        at org.springframework.orm.hibernate3.AbstractSessionFactoryBean.afterPropertiesSet(AbstractSessionFactoryBean.java:134)@b@        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1203)@b@        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1172)@b@        at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:427)@b@        at org.springframework.beans.factory.support.AbstractBeanFactory$1.getObject(AbstractBeanFactory.java:249)@b@        at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:155)@b@        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:246)@b@        at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:160)@b@        at org.springframework.beans.factory.support.DefaultListableBeanFactory.preInstantiateSingletons(DefaultListableBeanFactory.java:285)@b@        at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:352)@b@        at org.springframework.web.context.ContextLoader.createWebApplicationContext(ContextLoader.java:246)@b@        at org.springframework.web.context.ContextLoader.initWebApplicationContext(ContextLoader.java:189)@b@        at org.springframework.web.context.ContextLoaderListener.contextInitialized(ContextLoaderListener.java:49)@b@        at org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:4973)@b@        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5467)@b@        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:150)@b@        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:901)@b@        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:877)@b@        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:632)@b@        at org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1247)@b@        at org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:1898)@b@        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)@b@        at java.util.concurrent.FutureTask.run(FutureTask.java:262)@b@        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)@b@        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)@b@        at java.lang.Thread.run(Thread.java:745)@b@Caused by: java.sql.SQLException: Listener refused the connection with the following error:@b@ORA-12528, TNS:listener: all appropriate instances are blocking new connections@b@ @b@        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:412)@b@        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:531)@b@        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:221)@b@        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)@b@        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:503)@b@        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)@b@        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)@b@        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)@b@        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)@b@        ... 34 more@b@Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:@b@ORA-12528, TNS:listener: all appropriate instances are blocking new connections@b@ @b@        at oracle.net.ns.NSProtocol.connect(NSProtocol.java:385)@b@        at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1042)@b@        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:301)@b@        ... 42 more

二、解决方法

重启oarcle服务，如linux可以使用下面命令进行重启（先停止再重启）

[oracle@centos6 bin]$ source  ~/.bash_profile @b@[oracle@centos6 bin]$ sqlplus  /  as  sysdba@b@@b@SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 4 00:58:41 2021@b@@b@Copyright (c) 1982, 2009, Oracle.  All rights reserved. @b@Connected to:@b@Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production@b@With the Partitioning, OLAP, Data Mining and Real Application Testing options @b@...@b@SQL> SHUTDOWN  IMMEDIATE@b@ORA-01507: database not mounted @b@ORACLE instance shut down.@b@SQL> exit@b@Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production@b@With the Partitioning, OLAP, Data Mining and Real Application Testing options@b@[oracle@centos6 bin]$ lsnrctl  status@b@@b@LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-FEB-2021 01:01:20@b@@b@Copyright (c) 1991, 2009, Oracle.  All rights reserved.@b@@b@...

[oracle@centos6 bin]$ lsnrctl  start@b@@b@LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-FEB-2021 01:01:29@b@@b@Copyright (c) 1991, 2009, Oracle.  All rights reserved.@b@@b@Starting .../product/11.2.0/dbhome_1/bin/tnslsnr: please wait...@b@@b@TNSLSNR for Linux: Version 11.2.0.1.0 - Production@b@System parameter file is .../product/11.2.0/dbhome_1/network/admin/listener.ora@b@Log messages written to .../diag/tnslsnr/centos6/listener/alert/log.xml@b@Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))@b@Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.202)(PORT=1521)))@b@@b@Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))@b@STATUS of the LISTENER@b@------------------------@b@Alias                     LISTENER@b@Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production@b@Start Date                04-FEB-2021 01:01:31@b@Uptime                    0 days 0 hr. 0 min. 1 sec@b@Trace Level               off@b@Security                  ON: Local OS Authentication@b@SNMP                      OFF@b@Listener Parameter File   ...t/11.2.0/dbhome_1/network/admin/listener.ora@b@Listener Log File         ../diag/tnslsnr/centos6/listener/alert/log.xml@b@Listening Endpoints Summary...@b@  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))@b@  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))@b@The listener supports no services@b@The command completed successfully@b@@b@[oracle@centos6 bin]$ sqlplus  / as  sysdba@b@@b@SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 4 01:02:31 2021@b@@b@Copyright (c) 1982, 2009, Oracle.  All rights reserved.@b@@b@Connected to an idle instance.@b@@b@SQL> startup@b@ORACLE instance started.@b@@b@Total System Global Area 1119043584 bytes@b@Fixed Size                  2212616 bytes@b@Variable Size             838864120 bytes@b@Database Buffers          268435456 bytes@b@Redo Buffers                9531392 bytes@b@Database mounted.@b@Database opened.