WildFly 9关闭后致Oracle 11g数据库异常重启故障排查问询

Alright, let's break down your problem step by step. From the symptoms you described—WildFly runs fine initially, but after shutting it down properly, Oracle becomes unresponsive (requiring a full PC reboot instead of just restarting the DB/TNS services)—and the logs you shared, this is almost certainly a connection leak issue where WildFly isn't properly releasing Oracle database connections when it shuts down, leading to exhausted database resources.

Root Cause Analysis #

The ORA-12519 (no appropriate service handler found) and ORA-12505 (listener doesn't recognize the SID) errors point to Oracle hitting its maximum connection/process limit. When you shut down WildFly, some JDBC connections or background threads are stuck holding onto database resources, and even restarting the Oracle/TNS services doesn't clear these stuck processes (hence why only a PC reboot fixes it).

Looking at your standalone.xml concurrent configuration, the managed-executor-service has a max-threads=25 setting. If these threads are holding database connections and aren't properly terminated during WildFly shutdown, they'll leave orphaned sessions in Oracle that consume resources until they're forcefully cleared.

Troubleshooting Steps #

First, let's confirm the resource exhaustion and identify stuck connections:

• Check Oracle's current connection/process count: When the DB is working (right after a PC reboot, before starting WildFly), run these SQL commands:

  -- Count active sessions
  SELECT COUNT(*) FROM v$session;
  -- Count active processes
  SELECT COUNT(*) FROM v$process;
  -- Check the maximum allowed processes
  SHOW PARAMETER processes;
  

  If the current count is close to or matches the processes limit, that's proof your DB is hitting resource caps.

• Verify stuck connections after WildFly shutdown: Start WildFly, let it run, then shut it down using the official command. Immediately run this query to check for orphaned JDBC sessions:

  SELECT s.sid, s.serial#, p.spid, s.program, s.status
  FROM v$session s
  JOIN v$process p ON s.paddr = p.addr
  WHERE s.program LIKE '%JDBC Thin Client%';
  

  If these sessions still exist with status=INACTIVE, WildFly isn't releasing connections properly.

• Confirm you're using the correct WildFly shutdown command: Never kill the WildFly process directly. Use the official CLI command on Windows:

  %WILDFLY_HOME%\bin\jboss-cli.bat --connect command=:shutdown
  

  Directly terminating the process skips connection cleanup logic.

Fixes & Solutions #

Let's address this from both WildFly configuration and Oracle side:

1. Tighten WildFly's connection pool settings to prevent leaks
   Update your datasource configuration in standalone.xml to add connection validation and auto-recovery rules:

   <datasource ...>
     <pool>
       <max-pool-size>20</max-pool-size> <!-- Keep this below Oracle's processes limit -->
       <min-pool-size>5</min-pool-size>
       <idle-timeout-minutes>5</idle-timeout-minutes> <!-- Recycle idle connections after 5 mins -->
       <remove-abandoned>true</remove-abandoned>
       <remove-abandoned-timeout>60</remove-abandoned-timeout> <!-- Force close connections unused for 60s -->
     </pool>
     <validation>
       <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
       <validate-on-match>true</validate-on-match>
       <background-validation>true</background-validation>
       <background-validation-millis>60000</background-validation-millis> <!-- Validate connections every minute -->
     </validation>
   </datasource>
   

   This ensures stale or abandoned connections are automatically cleaned up.

2. Adjust WildFly's concurrent thread termination settings
   Modify your <concurrent> configuration to add termination timeouts, so WildFly waits for threads to clean up before shutting down:

   <concurrent>
     <context-services>
       <context-service name="default" jndi-name="java:jboss/ee/concurrency/context/default" use-transaction-setup-provider="true"/>
     </context-services>
     <managed-thread-factories>
       <managed-thread-factory name="default" jndi-name="java:jboss/ee/concurrency/factory/default" context-service="default"/>
     </managed-thread-factories>
     <managed-executor-services>
       <managed-executor-service name="default" jndi-name="java:jboss/ee/concurrency/executor/default" 
                                 context-service="default" hung-task-threshold="60000" 
                                 core-threads="5" max-threads="25" keepalive-time="5000"
                                 termination-timeout="30000"/> <!-- Wait 30s for threads to terminate -->
     </managed-executor-services>
     <managed-scheduled-executor-services>
       <managed-scheduled-executor-service name="default" jndi-name="java:jboss/ee/concurrency/scheduler/default" 
                                          context-service="default" hung-task-threshold="60000" 
                                          core-threads="2" keepalive-time="3000"
                                          termination-timeout="30000"/>
     </managed-scheduled-executor-services>
   </concurrent>
   

3. Temporarily increase Oracle's process limit (quick relief)
   If your Oracle processes parameter is set too low, increase it to give more headroom:

   ALTER SYSTEM SET processes=100 SCOPE=SPFILE;
   

   Restart the Oracle service after running this. Note: This is a temporary fix—you still need to address the connection leak.

4. Manually clear stuck Oracle sessions (no PC reboot needed)
   When the DB becomes unresponsive, you don't have to reboot your PC. First, identify stuck sessions:

   SELECT s.sid, s.serial#, p.spid
   FROM v$session s
   JOIN v$process p ON s.paddr = p.addr
   WHERE s.program LIKE '%JDBC Thin Client%' AND s.status = 'INACTIVE';
   

   Then kill each stuck session:

   ALTER SYSTEM KILL SESSION '<sid>,<serial#>';
   

   On Windows, you can also open Task Manager, find the oracle.exe process matching the spid from the query, and end it.

5. Ensure your application cleans up connections
   Check your application code for any custom components (like ServletContextListener or beans with @PreDestroy annotations) that hold database connections. Make sure they explicitly close connections on shutdown:

   @PreDestroy
   public void cleanupResources() {
       if (dbConnection != null) {
           try {
               dbConnection.close();
           } catch (SQLException e) {
               // Log the error instead of ignoring it
               logger.error("Failed to close database connection", e);
           }
       }
   }
   

Final Notes #

This issue is all about ensuring connections are properly released at every stage—from application code, to WildFly's connection pool, to thread termination during shutdown. Implementing these fixes should eliminate the need for PC reboots and keep your Oracle DB responsive after WildFly shutdowns.

内容的提问来源于stack exchange，提问作者WendyG