Catégorie : SGBD

Ce tutoriel peut répondre à l’erreur Oracle « ORA-39087 : nom de répertoire DATA_PUMP_DIR non valide » ou en anglais « ORA-39087: directory name DATA_PUMP_DIR is invalid » que l’on peut avoir en faisant un export de datapump avec la commande expdp ou un import avec impdp. Peut-être est-ce parce que le Datapumpdir n’a jamais été défini sur le serveur Oracle.

La solution se trouve dans la définition de la variable « Data pump dir » vers le chemin de stockage des datapumps Oracle, une information à vérifier pour résoudre l’erreur ORA39087.

Ce DATA_PUMP_DIR existe depuis Oracle 10g R2, donc également sur les version 11g R1 / R2 et 12c R1 / R2.

Voir le chemin de DATA_PUMP_DIR

Avec SQL Plus ou SQL Developer, utiliser la commande suivante pour voir le chemin actuellement renseigné du Data_pump_dir du serveur Oracle :

SELECT directory_path FROM dba_directories WHERE directory_name = ‘DATA_PUMP_DIR’;

Modifier le chemin DATA_PUMP_DIR

Pour utiliser un autre emplacement du disque dur serveur ou un lecteur réseau, il faut d’abord supprimer le raccourci Data pump dir puis le recréer et donner les droits à l’utilisateur qui va importer et exporter les bases.

DROP DIRECTORY DATA_PUMP_DIR;

CREATE DIRECTORY DATA_PUMP_DIR as ‘D:\oracle\dbdump’;

GRANT read,write ON DIRECTORY DATA_PUMP_DIR TO utilisateur;

L’import et l’export de BDD doit à nouveau être possible, du moins sans erreur ORA-39087 à cause du Data_pump_dir.

Le SGBD Oracle gère les bases de données de manière différente par rapport à SQL Server. Au lieu d’avoir un fichier par base, Oracle range les bases dans des tablespaces qui sont un espace de stockage dans lequel on enregistre une ou plusieurs bases. Mais sans interface graphique Oracle Enterprise Manager (OEM), comment voir ce qui compose un tablespace sur un serveur de BDD ?

Ce tutoriel donne la commande SQL Plus ou SQL Developer pour lister les bases de données montées sur un serveur Oracle. Toutes les versions de Oracle sont compatibles avec cette commande : 8i, 9i, 10g, 11g, 12c. Il suffira d’avoir accès au serveur Oracle, en direct sur la machine, par un accès distant (Bureau à distance Windows, VNC…) ou une connexion distante par commande ou avec l’outil Oracle SQL Developper.

Obtenir une liste des bases de données d’un serveur Oracle

1. Ouvrir une connexion SQL Developer ou SQL Plus sur le serveur Oracle avec un utilisateur disposant des droits suffisants sur le SGBD.

2. Taper la commande suivante et exécuter la requête :

SELECT sum(bytes) SUM, OWNER
FROM dba_segments
group by OWNER ;

3. Après quelques secondes de recherche, le résultat renvoyé liste les bases de données dans la colonne OWNER. Il s’agit donc des BDD actuellement montées sur le serveur Oracle.

L’installation d’un serveur Oracle définit un nombre maximal de processus à accepter pour ne pas saturer le SGBD et on peut arriver à cette limite lorsqu’un nombre important d’utilisateurs se connecte simultanément aux BDD. Pour connaitre quelle base est concernée, quel logiciel métier ouvre trop de sessions ou quel développeur n’a pas fermé ses connexions ou a mal codé son script, voici comment lister toutes les sessions ouvertes sur les bases de données hébergées sur un serveur Oracle au moyen d’une requête à exécuter en SQLPlus ou via SQL Developer. Cette information peut aussi être disponible via la console web Oracle Enterprise Manager (OEM ou EM).

Ces requêtes sont compatibles avec les différentes versions du SGBDR Oracle : 9i, 10g, 11g, 12c (à chaque fois dans les mises à jour R1 et R2).

Liste simple des connexions à un serveur Oracle

Cette commande va simplement lister les bases ouvertes par session :

SELECT username FROM v$session 
WHERE username IS NOT NULL 
ORDER BY username ASC;

Résultat par SQL Plus :

Résultat par Oracle SQL Developer :

Liste détaillée des connexions à un serveur Oracle

Pour avoir plus d’informations, comme connaitre le programme qui est connecté ou quel serveur / poste de travail travaille actuellement sur une base, ce script va donner des détails sur les sessions en cours sur le SGBD Oracle.

select
 substr(a.spid,1,9) pid,
 substr(b.sid,1,5) sid,
 substr(b.serial#,1,5) ser#,
 substr(b.machine,1,15) box,
 substr(b.username,1,10) username,
-- b.server,
 substr(b.osuser,1,15) os_user,
 substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by username;

Résultat par SQL Plus :

Résultat par Oracle SQL Developer :

Ce résultat est beaucoup plus détaillé, plus facile à lire avec SQL Developper et indique les informations suivantes :

• BOX : nom de la machine (hostname) qui a établi la session
• USERNAME : nom de l’utilisateur connecté (user Oracle)
• OS_USER : profil du système (login Windows par exemple)
• PROGRAM : logiciel ou outil utilisé par la connexion

Dans les programmes, on peut retrouver sqlplus.exe (SQL Plus), Oracle SQL Developer, JDBC Thin Client (application Java)…

Pour gérer une base de données montée sur un serveur Oracle, par exemple pour la supprimer, il est nécessaire que plus aucun utilisateur ou service ne soit connecté à cette base. Les applications métier qui travaillent avec une base Oracle ouvrent des sessions qui peuvent rester ouvertes, mêmes quand le service du logiciel est arrêté. S’il n’est pas possible de modifier la base, peut-être qu’une session est encore ouverte et il sera nécessaire de la « kill » pour manipuler la base.

Ce tutoriel explique comment fermer une session utilisateur sur une base Oracle 11g. Ce guide indique la procédure par la console web de gestion Enterprise Manager.

Fermer une session utilisateur sur Oracle 11g

1. Se connecter à la console Oracle Enterprise Manager 11g avec un identifiant qui possède des droits DBA.

2. Aller dans l’onglet Performances et cliquer en bas, dans Liens de surveillance supplémentaires, sur « Rechercher des sessions » :

3. Faire une recherche en utilisant le filtre :

• SID
• Utilisateur dB : le nom de la base
• Programme : SQL Developer, OMS, JDBC Thin Client (application Java)
• Service : l’instance concernée
• Module : SQL Developer, OMS, JDBC Thin Client, Admin Connection
• Action : indiqué sur une connexion OMS (Oracle Management Server)
• Client : peut indiquer une adresse
• Ordinateur : le nom du poste ou serveur connecté
• Utilisateur OS : l’utilisateur connecté à l’ordinateur (session Windows ou Linux)

4. Sélectionner les lignes dont il faudra terminer la session restée ouverte.

Malgré le paramètre AUTOEXTEND du tablespace Oracle, votre base de données ne peut s’agrandir et provoque des erreurs ? Cela peut aussi être un problème d’import qui plante, affichant un message comme celui-ci :

ORA-39171 : Le travail se heurte à une attente avec possibilité de reprise
ORA-01653 : Impossible d’étendre la table XXX de YYY dans le tablespace ZZZ

En cause, le tablespace en question (USERS ou ici ZZZ) est plein et ne peut s’étendre malgré l’autoextend activé. L’extension automatique a peut-être fonctionné jusqu’à présent mais c’est le fichier système qui est arrivé à sa valeur maximale, environ 32Go.

Agrandir un tablepace Oracle 11g par Enterprise Manager

1. Se connecter à la console web Oracle Enterprise Manager 11g avec un utilisateur type sys.

2. Aller sur l’onglet « Serveur » et cliquer sur « Tablespaces » dans la partie Stockage.

3. Cliquer sur le tablespace qu’il faut agrandir. On peut trier par « Espace alloué utilisé (%) » pour y voir plus clair.

4. Cliquer sur le bouton « Modifier » (en haut à droite).

5. Dans la partie Fichiers de données, sélectionner le bon Fichier de données (s’il y en a plusieurs) et cliquer sur « Modifier » :

6. Indiquer la nouvelle taille du fichier tablespace. Activer ou non l’extension automatique avec une valeur d’incrément. La taille maximale d’un fichier tablespace sur Oracle 11g NTFS Windows est de 32767 Mo.

7. Ne pas oublier d’appliquer les modifications pour que le changement soit pris en compte.

Ajouter un fichier à un tablespace Oracle 11g

Le tablespace pèse déjà plus que 32Go ? Suivre ce guide pour ajouter un nouveau fichier au tablespace et ainsi dépasser la limite.

1. Retourner dans l’onglet Serveur, Tablespaces et cliquer sur le tablespace en question.

2. Cliquer sur le bouton « Ajouter » (à droite).

3. Indiquer un nom pour le nouveau fichier, par exemple USERS02.DBF pour agrandir le tablespace USERS. Préciser aussi son emplacement de stockage (Répertoire du fichier), par défaut dans \APP\ADMINISTRATEUR\ORADATA\ORCL11\. Renseigner une taille de base pour ce nouveau fichier et cocher l’Extension automatique avec un Incrément, pour lui permettre d’évoluer tout seul.

4. Après avoir renseigné ces informations, cliquer sur le bouton « Continuer » et ne pas oublier d’ « Appliquer » sur l’écran suivant, rappelé par le message d’information « La modification du fichier de données ne prendra pas effet tant que vous n’aurez pas cliqué sur le bouton Appliquer. »

Si la console Enterprise Manager de Oracle 11g est plutôt explicite à ce sujet, l’EM version Oracle 12c n’affiche pas les mêmes informations visuelles de l’utilisation des tablespaces. Ces espaces alloués peuvent être remplis ou vides, selon les bases montées à l’intérieur. Il est toujours intéressant de savoir où en sont les espaces de stockage, pour savoir s’il faut en agrandir un tablespace, mieux gérer son espace alloué ou en créer un nouveau.

Oracle 11g ou 12c, voire 10g, on peut voir l’utilisation des tablespaces grâce à une requête SQL Plus pour éviter l’utilisation de la console graphique Enterprise Manager, plus visuelle mais pas présente sur toutes les versions du SGBD Oracle.

Voir l’utilisation des tablespaces sous Oracle 11g (EM)

1. Ouvrir la console Enterprise Manager du serveur Oracle 11g, par exemple https://srvoracle11g:1158/em

2. Se connecter en compte sys ou équivalent.

3. Aller à l’onglet Serveur, Tablespaces (dans le groupe Stockage) :

4. Un tableau résume les tablespaces avec la taille pré-allouée, l’espace utilisé et l’espace encore libre dans le fichier alloué. Dans cet exemple, le tablespace USERS est plein à 92,2% mais il reste presque 18Go de libre sur les 228Go alloués.

Requête pour voir l’utilisation des tablespaces sous Oracle

1. La console SQL Plus permet de voir la même information, peu importe la version du serveur Oracle (10g, 11g, 12c). Se connecter avec un compte sys ou équivalent (connect).

2. Copier / coller la requête suivante :

select T1.TABLESPACE_NAME,
T1.BYTES / 1024 / 1024 as "bytes_used (Mb)",
T2.BYTES / 1024 / 1024 as "bytes_free (Mb)",
T2.largest /1024 /1024 as "largest (Mb)",
round(((T1.BYTES-T2.BYTES)/T1.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
T1,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
T2
where T1.TABLESPACE_NAME=T2.TABLESPACE_NAME
order by ((T1.BYTES-T2.BYTES)/T1.BYTES) desc ;

Cette longue requête est aussi disponible dans ce fichier texte pour éviter les problèmes de copier/coller.

Télécharger “Oracle - Utilisation des tablespaces” oracle-tablespace-used.txt – Téléchargé 1034 fois – 519 o

3. Le résultat s’affiche sous forme d’un tableau. On retrouve les quasi 18Go de libre sur 228Go de notre exemple précédent en console Enterprise Manager.

Selon le résultat, on pourra optimiser et réduire la taille des tablespaces Oracle.

ORA-56935: les travaux Data Pump existants utilisent une version différente du fichier de données de fuseau horaire. En voilà une drôle d’erreur renvoyée par la console SQL Plus d’un serveur Oracle.

Dans un exemple de migration d’une base de données entre un serveur Oracle 11g et un 12c, l’erreur ORA56935 est apparue, empêchant ainsi l’import de la base au format datapump. Cela peut aussi arriver entre Oracle 10g et 11g, de 10 à 12, etc.

Cela parle d’un problème de fuseau horaire alors que les deux serveurs sont dans le même réseau et avec le même serveur de temps NTP. Il s’agit d’une erreur connue des DBA Oracle et voici la solution pour réussir à importer la base de données récalcitrante. L’opération s’applique au serveur complet et pas juste au tablespace en question.

Erreur lors d’un import Oracle

ORA-39006 : erreur interne
ORA-39065 : exception de processus maître inattendue dans DISPATCH
ORA-56935 : les travaux Data Pump existants utilisent une version différente du fichier de données de fuseau horaire
ORA-39097 : Le travail Data Pump a détecté une erreur inattendue -56935

Solution officielle Oracle

Sur le site du support Oracle référençant les Database Error Messages (lien) :

ORA-56935 : existing datapump jobs are using a different version of time zone data file
Cause: An attempt was made from a datapump job to request a different version of time zone data file from the one used by existing datapump jobs.
Action: Wait until existing datapump jobs unload the secondary time zone data file.

Solution efficace

La manipulation se fait avec une simple ligne de commande, au niveau du serveur SGBDR, pas d’un tablespace.

1. Ouvrir une console SQLPlus sur le serveur Oracle 12c (celui qui reçoit l’import de BDD).

2. Se connecter avec un compte sys ou équivalent.

3. Entrer et valider les commandes suivantes :

ALTER SESSION SET EVENTS ‘30090 TRACE NAME CONTEXT FOREVER, LEVEL 32’;

exec dbms_dst.unload_secondary;

4. On peut vérifier la nouvelle valeur du fuseau horaire / timezone par cette commande (via Oracle SQL Developer par exemple) :

SELECT PROPERTY_NAME, SUBSTR (property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

5. Le résultat doit être celui-ci :

DST_PRIMARY_TT_VERSION
26

DST_SECONDARY_TT_VERSION
0

DST_UPGRADE_STATE
NONE

Si le résultat est différent, il faudra peut-être appliquer une seconde fois les deux commandes de l’étape 3.

5. Le serveur peut maintenant accepter un import de datapump sans provoquer d’erreur de fuseau horaire ORA-56935.

Console Enterprise Manager inaccessible, vue rapide ou en prévision d’un export en texte, il est possible d’accéder aux utilisateurs Oracle en dehors de l’interface web de gestion EM. Ce tutoriel explique comment lister les utilisateurs Oracle à partir d’une simple ligne de commande. Cette commande se lance via la console sqlplus livrée avec le serveur Oracle.

Cette commande fonctionne avec Oracle 11g et Oracle 12c.

Afficher la liste des utilisateurs Oracle en une commande SQLPlus

1. Ouvrir un Invite de commandes (cmd.exe) sur le serveur Oracle.

2. Ouvrir une session SQLPlus : sqlplus /nolog

3. Se connecter à l’instance Oracle : connect
Saisir les identifiants d’un compte Oracle (pas forcément sysdba mais qui pourra lister les comptes)

4. Taper la commande suivante : select * from all_users ;

5. S’affiche ainsi la liste de tous les utilisateurs du serveur Oracle (USERNAME) avec leur USER_ID et la date de création du compte.

Erreur SP2-0640 : Non connecté

Si l’erreur « SP2-0640 : Non connecté » s’affiche, cela signifie que l’étape de connexion « connect » n’as pas été effectuée.

Supprimer des utilisateurs ainsi que leurs propriétés associées est utile pour faire du ménage dans son serveur SGBD Oracle, pour supprimer des bases ou pour récupérer de l’espace disque. Il s’agit d’une opération classique de maintenance et qui ne nécessite pas de connaissances pointues en administration de bases de données.

Il existe deux manières d’annuler un user : on peut simplement supprimer le compte utilisateur ou supprimer le compte ainsi que ses objets (tablespace, etc). Dans ce cas, on ajoute l’attribut CASCADE.

Attention à ne pas supprimer un compte sys ou system au risque de planter le serveur Oracle.

La procédure est à réaliser avec un compte sys ou qui dispose au moins du privilège DROP USER.

Supprimer un utilisateur Oracle 12c avec la console Enterprise Manager

1. Se connecter à la console EM (avec un login autorisé à Drop user) : https://serveuroracle:5500/em

2. Aller à l’onglet Sécurité, Utilisateurs.

3. Sélectionner l’utilisateur à supprimer.

4. Cliquer sur le bouton « Supprimer un utilisateur » :

5. Laisser cochée la ligne « Cascade » pour supprimer les objets de cet utilisateur et valider par OK.

Supprimer un utilisateur en ligne de commande

1. Ouvrir la console SQL Plus (ou un cmd avec sqlplus)

2. Se connecter au serveur avec un compte qui dispose du droit Drop user : connect

3. Taper la commande : DROP USER nom CASCADE ;

4. La réponse doit être « Utilisateur supprimé. »

En savoir plus sur DROP USER

Erreur lors de la suppression ?

ORA-01940 : cannot drop a user that is currently connected
ORA-06512

Si des connexions fantômes sont encore actives sur le serveur ou qu’une autre erreur empêche la suppression de l’utilisateur, suivre cette méthode plus radicale. Concrètement, on arrête le serveur pour le relancer en mode restrictif (seuls les DBA pourront s’y connecter) avant de le repasser en mode normal. Prévoir quelques minutes pour cette opération.

connect /as sysdba

SHUTDOWN IMMEDIATE ;

STARTUP RESTRICT ;

DROP USER nom CASCADE ;

SHUTDOWN IMMEDIATE ;

STARTUP ;

En cas d’un nombre trop élevé de tentatives échouées de connexions, ou pour d’autres raisons, un compte utilisateur Oracle peut être bloqué. Celui-ci est locked et rend donc son utilisation impossible, que ce soit par une application qui utilise le système SGBD ou par la console de gestion Enterprise Manager.

L’erreur ORA-28000 affichée par Oracle 12c EM lors d’une tentative de connexion avec l’utilisateur locké :

Echec de la connexion avec une erreur inattendue

ORA-28000 : the account is locked

Ce tutoriel explique comment réactiver un compte utilisateur Oracle verrouillé (locked).

Réactiver un compte utilisateur verrouillé dans Oracle 12c

1. Se connecter à Oracle Enterprise Manager avec un compte sys (system, sys, sysman ou un autre compte admin).

2. Aller sur l’onglet Securité, Utilisateurs (Security, Users en anglais).

3. Cliquer sur la ligne du compte utilisateur verrouillé : s’aider du tri par la colonne « Statut du compte » ou par le champ de recherche. Un icône de cadenas confirme ce verrou.

4. Aller dans le menu Actions, Modifier le compte (ou Actions, Alter Account).

5. Décocher la ligne « Compte verrouillé » (Account Locked).

Commande équivalente en SQL :

alter user "OUTLN" account unlock container=ALL;

6. Le compte utilisateur est déverrouillé et sera à nouveau accessible par l’application, par la console web Enterprise Manager ou via un logiciel comme l’environnement de développement intégré (EDI) Oracle SQL Developer.