ORA-12516, ORA-17800 et Saturation de Process Oracle : Comprendre, Diagnostiquer et Corriger

La gestion des processus Oracle est un sujet qui revient fréquemment en production. Beaucoup d’administrateurs et développeurs rencontrent des erreurs comme :

  • ORA-12516: TNS:listener could not find available handler with matching protocol stack
  • ORA-17800: Got minus one from a read call

Ces erreurs surviennent généralement lorsque la base n’a plus de processus disponibles pour accepter de nouvelles connexions.

🔎 C’est quoi un « process » Oracle ?

Un process est tout simplement un morceau de programme en mémoire qui exécute une tâche.

Dans Oracle, il y a :

  • des processus de fond (DBWR, LGWR, CKPT…) qui assurent le bon fonctionnement interne,
  • des processus serveurs qui gèrent vos connexions (1 connexion = 1 process en mode dedicated),
  • des processus partagés (shared servers, dispatchers),
  • des esclaves parallèles (PX slaves pour les requêtes en parallèle).

Le paramètre PROCESSES définit le nombre maximum de process que l’instance Oracle peut lancer. S’il est atteint → plus de nouvelles connexions possibles.

🚨 Symptômes courants

  • Applications qui plantent avec ORA-12516.
  • Messages réseau comme ORA-17800 (connexion brutalement fermée).
  • Saturation du CPU par de nombreux process Oracle.
  • Alert log montrant des refus de connexion.

🛠 Se connecter directement au serveur Oracle

Lorsque vous êtes connecté sur le serveur qui héberge Oracle, vous pouvez contourner le listener et vous connecter directement à l’instance. Cela est très utile en cas de saturation.

Connexion en OS Authentication (compte oracle sur le serveur)

# Se connecter en tant qu'utilisateur oracle sur l'OS
sqlplus / as sysdba

Connexion classique avec mot de passe

sqlplus sys@ORCL as sysdba
# ou
sqlplus sys/password@ORCL as sysdba

Ici ORCL peut être remplacé par l’SID ou un SERVICE_NAME valide (ex: cdbprod).

En cas de doute :

export ORACLE_SID=CDBPROD
sqlplus / as sysdba

🛠 Diagnostic pas à pas

1. Vérifier l’utilisation des limites

SELECT resource_name, current_utilization, max_utilization, limit_value
FROM   v$resource_limit
WHERE  resource_name IN ('processes','sessions','transactions');

2. Voir combien de process sont actifs

SELECT COUNT(*) AS current_processes FROM v$process;

3. Identifier l’appli/service qui consomme

SELECT s.service_name,
       COALESCE(NULLIF(s.module,''), s.program) AS module,
       s.machine,
       COUNT(*) AS sessions
FROM   gv$session s
WHERE  s.type='USER'
GROUP  BY s.service_name, COALESCE(NULLIF(s.module,''), s.program), s.machine
ORDER  BY sessions DESC
FETCH FIRST 20 ROWS ONLY;

4. Détecter les sessions inactives (potentielles « fuites » de connexions)

SELECT s.service_name, s.program, s.machine,
       COUNT(*) AS idle_cnt, MAX(s.last_call_et) AS max_idle_sec
FROM   gv$session s
WHERE  s.type='USER'
  AND  s.status='INACTIVE'
  AND  s.last_call_et > 30*60
GROUP  BY s.service_name, s.program, s.machine
ORDER  BY idle_cnt DESC;

🗑 Nettoyer les sessions inutiles

Voici un script tout-en-un qui tue automatiquement les sessions inactives depuis plus de 30 minutes, sans transaction en cours, et hors comptes SYS/SYSTEM :

SET SERVEROUTPUT ON SIZE UNLIMITED
DECLARE
  v_killed NUMBER := 0;
BEGIN
  FOR r IN (
    SELECT s.inst_id, s.sid, s.serial#
    FROM   gv$session s
    LEFT JOIN gv$transaction t
           ON t.addr = s.taddr AND t.inst_id = s.inst_id
    WHERE  s.type='USER'
      AND  s.username NOT IN ('SYS','SYSTEM')
      AND  s.status='INACTIVE'
      AND  s.last_call_et > 30*60
      AND  t.addr IS NULL
      AND  s.blocking_session IS NULL
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'ALTER SYSTEM KILL SESSION '''||r.sid||','||r.serial#||',@'||r.inst_id||''' IMMEDIATE';
      v_killed := v_killed + 1;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('FAILED: '||SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Killed '||v_killed||' session(s).');
END;
/

⚠ À utiliser avec précaution. Filtrez si possible par SERVICE_NAME, MODULE ou MACHINE pour ne viser que l’application fautive.

🔧 Solutions durables

  1. Augmenter la capacité (redémarrage nécessaire) :
ALTER SYSTEM SET processes = 1500 SCOPE=SPFILE;
ALTER SYSTEM SET sessions  = 2250 SCOPE=SPFILE; -- ~1.5x processes

Puis :

SHUTDOWN IMMEDIATE;
STARTUP;
  1. Configurer le pool de connexions (UCP, Hikari, etc.) :
    • Max pool size raisonnable
    • Idle timeout
    • Validation query (SELECT 1 FROM DUAL)
  2. Paramétrer SQLNET.EXPIRE_TIME pour nettoyer les connexions zombies.
  3. Limiter par profil :
ALTER PROFILE app_profile LIMIT IDLE_TIME 30; -- en minutes
  1. Resource Manager : fixer un plafond de sessions par service ou par utilisateur.

🔐 Connexions locales depuis le serveur (sans et avec listener)

1) Connexion locale sans listener (bequeath) — administration rapide

À exécuter en SSH sur l’hôte de la base ; l’utilisateur OS doit appartenir au groupe OSDBA (dba).

# Définir l'environnement Oracle (adapter ORACLE_HOME/ORACLE_SID)
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=CDBPROD

# Connexion SYS locale (OS authentication)
sqlplus / as sysdba

# (Optionnel) Basculer vers une PDB
SQL> SHOW con_name;
SQL> ALTER SESSION SET CONTAINER=pdbsid;   -- remplacer par votre PDB
SQL> SHOW con_name;

Avantages : pas de dépendance au listener, idéal en incident ou maintenance.

2) Connexion locale via listener TCP (utilise le service)

# Vérifier le listener et les services
lsnrctl status

# Test de résolution / reachability
tnsping //oracle.test.local:1526/pdbsid

# Connexions SQL*Plus
sqlplus user/pass@//oracle.test.local:1526/pdbsid
sqlplus sys@//oracle.test.local:1526/pdbsid as sysdba

TNSNAMES (facultatif, plus lisible)

PDBSID =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=oracle.test.local)(PORT=1526))
    (CONNECT_DATA=(SERVICE_NAME=pdbsid)(SERVER=DEDICATED))
  )
sqlplus user/pass@PDBSID
sqlplus sys@PDBSID as sysdba

3) Outils alternatifs

# SQLcl (Oracle) : similaire à SQL*Plus avec améliorations
sql / as sysdba
sql sys@//oracle.test.local:1526/pdbsid as sysdba

4) Conseils & dépannage

  • PDB non ouverte :
    -- en CDB$ROOT
    ALTER PLUGGABLE DATABASE ALL OPEN;
    ALTER PLUGGABLE DATABASE ALL SAVE STATE;
  • Service manquant :
    SHOW PARAMETER service_names;
    ALTER SYSTEM REGISTER; -- forcer l'enregistrement auprès du listener lsnrctl services
  • Erreur ORA-12516 : vérifier la capacité processes/sessions et la saturation des handlers (lsnrctl services).
  • Erreur ORA-17800 : vérifier timeouts réseau, keepalive (SQLNET.EXPIRE_TIME=10), et versions de driver côté client.

✅ Conclusion

La saturation de PROCESSES est l’une des causes les plus fréquentes des erreurs ORA-12516 et ORA-17800. Elle est souvent liée à des applications qui ouvrent trop de connexions sans les réutiliser.

En résumé :

  • Court terme : tuer les sessions inactives.
  • Moyen terme : augmenter PROCESSES/SESSIONS.
  • Long terme : corriger le pool applicatif et mettre en place une supervision proactive.

En adoptant ces bonnes pratiques, vous éviterez les interruptions de service et améliorerez la stabilité de vos bases Oracle.

Étiquettes

Publications similaires