一、故障说明
通过PLSQL无法连接oracle数据库服务器，界面提示"ORA-12528: TNS:listener: all appropriate instances are blocking new connection"错误，如下图所示
代码同样报连接错误“org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Listener refused the connection with the following error: Caused by: java.sql.SQLException: Listener refused the connection with the following error:ORA-12528, TNS:listener: all appropriate instances are blocking new connections”，详情日志如下
)@b@6908 [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@ 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:511)@b@ at java.util.concurrent.FutureTask.run(FutureTask.java:266)@b@ at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)@b@ at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)@b@ at java.lang.Thread.run(Thread.java:748)@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
二、解决方法
[root@centos6 tomcat7]# su oracle@b@@b@[oracle@centos6 tomcat7]$ source ~/.bash_profile@b@@b@[oracle@centos6 tomcat7]$ sqlplus /nolog@b@@b@SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 25 04:49:28 2019@b@@b@Copyright (c) 1982, 2009, Oracle. All rights reserved.@b@@b@SQL> connect /as sysdba;@b@Connected.@b@@b@SQL> alter database mount;@b@@b@Database altered.@b@@b@SQL> alter database open;@b@@b@Database altered.
注：source ~/.bash_profile命令需当前oracle用户的环境执行生效oracle环境配置参数，具体参见”linux下安装oracle的具体配置步骤“文章）