Pense-bête du DBA Oracle

Bonjour, cet article s'adresse à un public averti mais pas forcément expert. Il a pour but de rappeler des acquis, c'est pourquoi je ne suis pas entré dans le détail. Vous pouvez bien entendu me faire part de vos remarques et attentes sur les sujets traités.

I. La théorie

Il est ici question de rappeler quelques paramètres de la base de données (choisis complètement arbitrairement :-D) ainsi que les paramètres d'arrêt/démarrage de la base. J'ai voulu rappeler aussi les différentes commandes possibles sur les redo logs et enfin la gestion des tablespaces.

I-A. Le fichier init.ora

Le fichier init[SID].ora est le fichier de paramétrage de la base de données. Sans ce fichier la base ne pourra pas démarrer. L'emplacement par défaut est ORACLE_HOME\dbs. Voici quelques exemples de paramètres :

  • BACK_GROUND_DUMP_DEST Emplacement où les fichiers traces des processus en arrière plan sont enregistrés.
  • USER_DUMP_DEST Emplacement où les fichiers traces sont créés.
  • COMPATIBLE Version du serveur avec lequel l'instance est compatible.
  • CONTROL_FILES Noms des fichiers de contrôle.
  • DB_BLOCK_BUFFERS Nombre de blocs mis en cache dans la SGA. La valeur par défaut et minimum est de 50 buffers.
  • DB_NAME Identifiant de la base de données de 5 caractères ou moins. (seul paramètre nécessaire à la création d'une base).
  • SHARED_POOL_SIZE Taille en octets de la zone de partage. (Default : 3 500 000).
  • IFILE Permet de référencer un autre fichier de paramètre à imbriquer dans la définition.
  • LOG_BUFFER Nombre d'octets alloués au buffer redolog dans la SGA.
  • MAX_DUMP_FILE_SIZE Taille maximum des fichiers trace, spécifiée en nombre de blocs de l'OS.
  • PROCESSES Nombre de processus de l'OS pouvant se connecter simultanément à cette instance.
  • SQL_TRACE Active on non l'outil de suivi SQL pour chaque session utilisateur (cf TKPROF pour exploiter les sorties).
  • TIMED_STATISTICS Active ou non le minutage dans les fichiers trace et sur les écrans.

I-B. Démarrage de la base

 
Sélectionnez
STARTUP [paramètres];
  • NOMOUNT Créé la SGA et démarre les processus en arrière plan mais ne permet pas l'accès à la base (reste un OPEN à faire).
  • MOUNT Monte la base pour certaines activités DBA mais ne permet aucun accès à la base.
  • OPEN Permet aux utilisateurs d'accéder à la base.
  • EXCLUSIVE Autorise l'instance courant seulement à accéder à la base.
  • PFILE Spécifie le fichier d'initialisation à prendre en compte.
  • FORCE Annule l'instance courante avant d'effectuer un démarrage normal.
  • RESTRICT Autorise seulement l'accès aux utilisateurs avec le privilège RESTRICTED SESSION.
  • PARALLEL Serveur parallèle Oracle.
  • SHARED Autre terme pour parallèle.
  • RECOVER Démarre la restauration media quand la base démarre.

Sous NT, la base de données ORACLE s'exécute comme étant un service. Pour modifier le mode démarrage d'une base, il suffit de modifier le script strt[SID].cmd dans %ORACLE_HOME%\DATABASE

I-C. Arrêt de la base

 
Sélectionnez
SHUTDOWN  [paramètre];
  • NORMAL Les nouvelles connexions ne sont pas permises, le serveur oracle attend la fin de toutes les connexion.
  • IMMEDIATE Les utilisateurs sont déconnectés, les opérations en cours annulées (rollback).
  • ABORT L'instance se termine sans fermer les fichiers, une restauration d'instance est souvent nécessaire (recover) au prochain démarrage.
  • TRANSACTIONNAL Plus de connexion possible, les ordres SQL en cours s'exécute jusqu'à leur terme et aucun nouveau n'est accepté.

I-D. Vues dynamiques à connaitre

Il existe toute une série de vue dynamique dont v$fixed_table qui contient le nom de toutes les vues disponibles. Les principales sont :

  • v$parameter Contient des informations sur les paramètres d'initialisation.
    N.B. : la commande SHOW PARAMETER CONTROL
    équivaut à : SELECT name,type,value FROM v$parameter WHERE name LIKE '%control%';
  • v$system_parameter Contient des informations sur les paramètres d'initialisation et leurs modifications éventuelles.
  • v$sga Contient des infos sur la SGA.
  • v$option Contient la liste des options installées sur le serveur Oracle.
  • v$process Contient des informations sur les process actifs courant.
  • v$session Liste des information sur la session courante.
  • v$version Liste le numéro de version et les composants.
  • v$instance Affiche l'état de l'instance courante.
  • v$thread Contient des informations thread , par exemple sur les groupes redo log.
  • v$controlfile Liste le nom des fichiers de contrôle (Ne renvoie aucune valeur à l'état NOMOUNT).
  • v$database Contient des informations sur la base.
  • v$databafile Contient des informations sur les fichiers de données et de contrôle.
  • v$datafile_header Affiche des informations fichiers de données des fichiers de contrôle.
  • v$logfile Contient des informations sur les fichiers redo_log.

Certains paramètres sont modifiables grâce aux commandes ALTER SESSION ou ALTER SYSTEM. Par exemple :

 
Sélectionnez
ALTER SESSION SET SQL_TRACE=true; (Session courante)
ALTER SYSTEM SET TIMED_STATISTICS=true; (jusqu'à l'arrêt de la base)
ALTER SYSTEM SET SORT_AREA_SIZE=131072 DEFERRED; (modifie la valeur de toutes les nouvelles connexions)

Pour trouver les paramètres modifiés:

 
Sélectionnez
SELECT name, isses_modifiable, issys_modifiable, ismodified 
     FROM v$system_parameter 
     WHERE ismodified!='false';

La vue dynamique v$system_parameter a les spécifications suivantes :

  • NAME Nom du paramètre.
  • TYPE Type de données de la valeur :
    • 1: Booléen
    • 2: Chaîne de caractères
    • 3: Entier
    • 4: Fichier
    • 5: Reservé
    • 6: Entier long
  • VALUE Valeur du paramètre.
  • ISDEFAULT Indique si la valeur du paramètre est celle par défaut ou non.
  • ISSES_MODIFIABLE Indique si le paramètre est modifiable ou non avec un ALTER SESSION.
  • ISSYS_MODIFIABLE Indique si le paramètre est modifiable ou non avec un ALTER SYSTEM.
  • DEFERRED Indique que le paramètre ne sera modifié qu'à partir de la prochaine session.
  • ISMODIFIED Indique comment le paramètre a été modifié. Si c'est par un ALTER SYSTEM, la valeur sera MODIFIED.
  • ISADJUSTED Indique si la valeur a été ajusté (par exemple, la valeur doit être un entier et on a saisi 9.6, alors Oracle ajuste la valeur à 10).
  • UPDATE_COMMENT Commentaire associé à la modification la plus récente.

I-E. Les redo logs

Les fichiers redo logs permettent à la base de garder une trace de toutes les altérations de données, ainsi en cas de crash de la base, ils permettent de rejouer les modifications apportées à la base. Ces fichiers doivent être au moins au nombre de deux et nécessitent une attention toute particulière tant au niveau de la sauvegarde que de l'optimisation des accès.
En mode ARCHIVELOG, les redo logs sont archivés afin de garder une trace de toutes les modifications apportées et non pas seulement dans la limite de la taille des fichiers de redo log.

Les fichiers de redo log écrivent sur le disque le contenu de la mémoire lorsque le redo log buffer est plein. Il parait alors évident que la taille des fichiers de redo log doivent être au moins égale à celle du redo log buffer (paramètre log_buffer). Il n'y a pas vraiment de règles pour déterminer la taille adéquate des fichiers de redo.
Néanmoins il convient de garder en tête que plus le fichier est gros, moins vite il sera archivé et inversement, plus il est petit plus vite il sera archivé. Le nombre d'archives générées doit être environ de deux par heure (du moins, c'est cette règle que j'applique ), effectivement il faut limiter le nombre d'archives écrites puisque les accés disques sont couteux en performance. De plus, les fichiers doivent être, dans la mesure du possible, partagés entre plusieurs disques afin d'améliorer les temps d'accès et augmenter la sécurité, et surtout, sur un disque différent de la base de données.
Ainsi, si les disques de la base de données sont altérés et que les redo logs sont dessus, ils seront eux aussi altérés, rendant impossible la restauration de la base.

Voici quelques requêtes relatives aux redo logs:

Pour voir l'état des archives logs :

 
Sélectionnez
ARCHIVE LOG LIST;
SELECT name, log_mode FROM v$database;
SELECT archiver FROM v$instance;

Pour voir les informations sur les groupes de redo log :

 
Sélectionnez
SELECT groups, current_group#, sequence# FROM v$thread;

Pour voir des informations sur les groupes et les membres :

 
Sélectionnez
SELECT group#, sequence#, bytes, members, status FROM v$log;

Le status redo logs peut-être : UNUSED : jamais écrit CURRENT : en ligne et en cours d'écriture ACTIVE : en ligne et en cours d'archivage INACTIVE : en ligne, archivé et non utilisé Pour voir des informations sur les fichiers de redo :

 
Sélectionnez
SELECT * FROM v$logfile;

Pour forcer le switch de groupe de redo log :

 
Sélectionnez
ALTER SYSTEM SWITCH LOGFILE;

Cette commande permet d'archiver le redo log courant et d'activer le redo log suivant.

 
Sélectionnez
ALTER SYSTEM CHECKPOINT;

Cette commande permet d'archiver le redo log courant.

Pour ajouter un groupe de fichier de redo log :

 
Sélectionnez
ALTER DATABASE ADD LOGFILE GROUP 2 'g:\oracle\oradata\orafrance\REDO03.LOG' size 10M;
ALTER DATABASE ADD LOGFILE GROUP 2 'g:\oracle\oradata\orafrance\REDO04.LOG' size 10M;

Pour supprimer un groupe de fichier de redo log :

 
Sélectionnez
ALTER DATABASE DROP LOGFILE GROUP 2;

Pour supprimer un membre d'un fichier redo log ONLINE :

 
Sélectionnez
ALTER DATABASE DROP LOGFILE MEMBER 'g:\oracle\oradata\orafrance\REDO02.LOG';

Pour déplacer le fichier de redo log ONLINE :

Ici nous allons illustrer comment utiliser les commandes précédentes. Imaginons le scénario suivant : nous avons deux groupes de fichiers (le groupe 1 et le 2) avec chacun deux fichiers de redo logs.

 
Sélectionnez
SELECT group#, status FROM v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         1 ACTIVE
         2 CURRENT
         2 INACTIVE
  1. Créer un fichier de redo temporaire de la taille des redo logs :
     
    Sélectionnez
    ALTER DATABASE ADD LOGFILE GROUP 3 'g:\oracle\oradata\orafrance\REDO05.LOG' size 10M;
    
  2. Positionner le redo courrant sur ce nouveau fichier :
     
    Sélectionnez
    ALTER SYSTEM SWITCH LOGFILE;
    ALTER SYSTEM SWITCH LOGFILE;
    
    SELECT group#, status FROM v$log;
    
        GROUP# STATUS
    ---------- ----------------
             1 INACTIVE
             1 INACTIVE
             2 INACTIVE
             2 ACTIVE
             3 CURRENT
    
  3. Supprimer les fichiers de redo logs et les recréer dans le nouveau répertoire :
     
    Sélectionnez
    ALTER DATABASE DROP LOGFILE MEMBER 'g:\oracle\oradata\orafrance\REDO01.LOG';
    ALTER DATABASE ADD LOGFILE GROUP 1 'g:\oracle\oradata\orafrance\redo\REDO01.LOG' size 10M;
    ALTER DATABASE DROP LOGFILE MEMBER 'g:\oracle\oradata\orafrance\REDO02.LOG';
    ALTER DATABASE ADD LOGFILE GROUP 1 'g:\oracle\oradata\orafrance\redo\REDO02.LOG' size 10M;
    ALTER DATABASE DROP LOGFILE MEMBER 'g:\oracle\oradata\orafrance\REDO01.LOG';
    ALTER DATABASE ADD LOGFILE GROUP 2 'g:\oracle\oradata\orafrance\redo\REDO03.LOG' size 10M;
    ALTER DATABASE DROP LOGFILE MEMBER 'g:\oracle\oradata\orafrance\REDO02.LOG';
    ALTER DATABASE ADD LOGFILE GROUP 2 'g:\oracle\oradata\orafrance\redo\REDO04.LOG' size 10M;
    
    SELECT group#, status FROM v$log;
    
        GROUP# STATUS
    ---------- ----------------
             1 UNUSED
             1 UNUSED
             2 UNUSED
             2 UNUSED
             3 CURRENT
    
  4. Penser à supprimer les fichiers g:\oracle\oradata\orafrance\REDOxx.LOG rendus inutiles.
  5. Activer tous les groupes et supprimer le redo log temporaire :
 
Sélectionnez
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;

SELECT group#, status FROM v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         1 INACTIVE
         2 INACTIVE
         2 CURRENT
         3 INACTIVE

ALTER DATABASE DROP LOGFILE GROUP 3;

SELECT group#, status FROM v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         1 INACTIVE
         2 INACTIVE
         2 CURRENT

Penser à supprimer le fichier g:\oracle\oradata\orafrance\REDO05.LOG rendu inutile.

I-F. Les Tablespaces

Les tablespaces sont des espaces disques réservés au stockage des données. Chaque tablespace est constitué d'au moins un datafile : fichier de données, créé sur un disque.

Pour créer un tablespace :

 
Sélectionnez
CREATE TABLESPACE ora_data
DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA01.dbf' size 100M,
         'g:\oracle\oradata\orafrance\ORA_DATA02.dbf' size 100M
MINIMUM EXTENT 500K (uniquement V8)
DEFAULT STORAGE (initial 500K next 500K MAXEXTENTS 500 PCTINCREASE 0);

Paramètres de création :

  • DATAFILE Liste des fichiers de données.
  • MINIMUM EXTENT Permet de s'assurer que chaque taille d'extent utilisé dans le tablespace est
  • un multiple de l'entier.
  • ONLINE Rend le tablespace disponible à l'utilisation immédiatement après sa création.
  • OFFLINE Crée le tablespace mais le laisse indisponible.
  • PERMANENT Spécifie que le tablespace contient des objets permanents.
  • TEMPORARY Permet de spécifier que les objets sont de types temporaire.
  • DEFAULT STORAGE Paramètres de stockage de tous les objets dans le tablespace.

Paramètres de stockage (DEFAULT STORAGE) :

  • INITIAL Définit la taille du premier extent (Par défaut : 5*DB_BLOCK_SIZE).
  • NEXT Se rapporte à la taille de l'extent suivant.
  • MINEXTENTS Est le nombre d'extents alloués lors de la création du segment (Par défault 1).
  • PCTINCREASE Pourcentage de croissance de la taille de l'extent, le n-iéme next est alors égale à next*(1+(pctincrease/100))e(n-2).
    Par exemple : si le initial est à 16k et le pctincrease à 10, le deuxiéme extent sera à 16k,
    le suivant de 18k (=16+10%),le suivant à 20k (=18+10%), etc…

Pour ajouter un fichier à un tablespace :

 
Sélectionnez
ALTER TABLESPACE ora_data ADD DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA03.dbf' SIZE 200M;

Pour activer l'extension automatique de fichiers de données :

 
Sélectionnez
ALTER TABLESPACE ora_data ADD FILE 'g:\oracle\oradata\orafrance\ORA_DATA04.dbf' SIZE 200M 
AUTOEXTEND ON NEXT 10M MAX SIZE 500M;

Pour modifier la taille d'un fichier de données :

 
Sélectionnez
ALTER DATABASE DATAFILE 'g:\oracle\oradata\orafrance\ORA_DATA02.dbf' RESIZE 50M;

Lorsqu'un tablespace a alloué de nouveaux extents ceux-ci même si des lignes ont été supprimées, restent alloués de telle sorte que si le tablespace a été rempli à hauteur de 80 %, sa taille ne pourra pas être réduite de plus de 20 %.
Seul un TRUNCATE TABLE ou un ALTER INDEX REBUILD libère les extents. Ainsi, pour retailler un tablespace il peut être nécessaire de le vider complétement en déplaçant les objets dans un autre tablespace.

Pour déplacer un tablespace :

  1. Mettre le tablespace OFFLINE :
     
    Sélectionnez
    ALTER TABLESPACE ora_data OFFLINE;
    
  2. Copier le fichier dans le nouveau répertoire
  3. Renommer le fichier :
     
    Sélectionnez
    ALTER DATABASE RENAME FILE 'g:\oracle\oradata\orafrance\ORA_DATA01.dbf' 
    TO 'g:\oracle\oradata\orafrance\data\ORA_DATA1.dbf;
    
  4. Mettre le tablespace ONLINE :
     
    Sélectionnez
    ALTER TABLESPACE ora_data ONLINE
    
  5. Supprimer le fichier g:\oracle\oradata\orafrance\ORA_DATA01.dbf.

Pour mettre un tablespace en lecture seule :

 
Sélectionnez
ALTER TABLESPACE app_data READ ONLY;

Pour mettre un tablespace en lecture/écriture :

 
Sélectionnez
ALTER TABLESPACE app_data READ WRITE;

Pour supprimer un tablespace :

 
Sélectionnez
DROP TABLESPACE app_data INCLUDING CONTENTS;

Cette commande ne supprime pas le datafile, il convient donc de supprimer le fichier haddock si nécessaire.

II. Copie de la base de données

Dans cet exemple, nous allons copier la base « orafrance » dans la base de test « oratest » qui aura été créée au préalable.
La copie consiste à arrêter la base « oratest » et remplacer ses fichiers par ceux de la base « orafrance » et faire en sorte que ceux-ci soient bien pris en compte.

II-A. Création des fichiers de contrôle

Les fichiers de contrôle indique à la base où sont situés les fichiers constituant la base de données.
Nous allons générer le script de création des control files de la base orafrance :

 
Sélectionnez
SQL> alter database backup controlfile to trace;

Cette commande génère un fichier trace dans le répertoire des traces utilisateurs indiqué par le paramètre « user_dump_dest ». Le fichier ressemble alors à ceci : La partie qui nous intéresse est la suivante :

 
Sélectionnez
STARTUP NOMOUNT 
CREATE CONTROLFILE REUSE DATABASE "orafrance" NORESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 908
LOGFILE
GROUP 1 'G:\ORACLE\ORADATA\orafrance\REDO01.LOG' SIZE 10M,
GROUP 1 'G:\ORACLE\ORADATA\orafrance\REDO02.LOG' SIZE 10M,
GROUP 2 'F:\ORACLE\ORADATA\orafrance\REDO03.LOG' SIZE 10M,
GROUP 2 'F:\ORACLE\ORADATA\orafrance\REDO04.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'F:\ORACLE\ORADATA\orafrance\SYSTEM01.DBF',
'F:\ORACLE\ORADATA\orafrance\CWMLITE01.DBF',
'F:\ORACLE\ORADATA\orafrance\DRSYS01.DBF',
'F:\ORACLE\ORADATA\orafrance\EXAMPLE01.DBF',
'F:\ORACLE\ORADATA\orafrance\INDX01.DBF',
'F:\ORACLE\ORADATA\orafrance\ODM01.DBF',
'F:\ORACLE\ORADATA\orafrance\TOOLS01.DBF',
'F:\ORACLE\ORADATA\orafrance\USERS01.DBF',
'F:\ORACLE\ORADATA\orafrance\XDB01.DBF',
'F:\ORACLE\ORADATA\orafrance\INDEX\INDPRD.DBF',
'F:\ORACLE\ORADATA\orafrance\DATA\DATPRD.DBF',
'F:\ORACLE\ORADATA\orafrance\DATA\DATDEM.DBF',
'F:\ORACLE\ORADATA\orafrance\DATA\DATDIS.DBF',
'F:\ORACLE\ORADATA\orafrance\DATA\DATHIS.DBF',
'F:\ORACLE\ORADATA\orafrance\DATA\DATREF.DBF',
'G:\ORACLE\ORADATA\orafrance\INDEX\INDDEM.DBF',
'G:\ORACLE\ORADATA\orafrance\INDEX\INDDIS.DBF',
'G:\ORACLE\ORADATA\orafrance\INDEX\INDHIS.DBF',
'G:\ORACLE\ORADATA\orafrance\INDEX\INDREF.DBF',
'G:\ORACLE\ORADATA\orafrance\INDEX\INDDIS2.DBF',
'G:\ORACLE\ORADATA\orafrance\INDEX\INDDIS3.DBF',
'G:\ORACLE\ORADATA\orafrance\INDEX\INDDIS4.DBF',
'G:\ORACLE\ORADATA\orafrance\INDEX\INDDIS5.DBF'
CHARACTER SET WE8MSWIN1252
;
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
# Database can now be opened normally.
ALTER DATABASE OPEN;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'G:\ORACLE\ORADATA\orafrance\TEMP02.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'G:\ORACLE\ORADATA\orafrance\TEMP01.DBF'
SIZE 2000M REUSE AUTOEXTEND OFF;

II-B. Modification du fichier généré

La base à copier doit être arrêtée afin que tous les fichiers soient bien synchronisés.

Une fois que les fichiers de la base « orafrance » sont copiés sur « oratest », il convient de modifier le script de création des fichiers de contrôle pour l'adapter aux nouveaux chemins. Par exemple, nous allons copier les fichiers de F:\ORACLE\ORADATA\orafrance et G:\ORACLE\ORADATA\orafrance sur un autre serveur dans D:\ORACLE\ORADATA\oratest.
Il faut donc remplacer les chaînes de caractères F:\ORACLE\ORADATA\orafrance et G:\ORACLE\ORADATA\orafrance par D:\ORACLE\ORADATA\oratest. Le nom de la base change aussi de « orafrance » à « oratest », il faut donc spécifier ce changement en remplaçant l'entête :

 
Sélectionnez
CREATE CONTROLFILE REUSE DATABASE "orafrance" NORESETLOGS ARCHIVELOG

devient :

 
Sélectionnez
CREATE CONTROLFILE SET DATABASE "oratest" RESETLOGS ARCHIVELOG

Nous allons commenter la ligne : RECOVER DATABASE; la base « orafrance » ayant été arrêtée une récupération n'est pas nécessaire. Et nous démarrons la base en supprimant les logs : Nous allons commenter la ligne : ALTER DATABASE OPEN; devient ALTER DATABASE OPEN RESETLOGS;.

Après avoir supprimé les lignes inutiles le fichier doit ressemblé à ceci :

 
Sélectionnez
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "oratest" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 908
LOGFILE
  GROUP 1 'D:\ORACLE\ORADATA\oratest\REDO01.LOG'  SIZE 10M,
  GROUP 1 'D:\ORACLE\ORADATA\oratest\REDO02.LOG'  SIZE 10M,
  GROUP 2 'D:\ORACLE\ORADATA\oratest\REDO03.LOG'  SIZE 10M,
  GROUP 2 'D:\ORACLE\ORADATA\oratest\REDO04.LOG'  SIZE 10M
DATAFILE
  'D:\ORACLE\ORADATA\oratest\SYSTEM01.DBF',
  'D:\ORACLE\ORADATA\oratest\CWMLITE01.DBF',
  'D:\ORACLE\ORADATA\oratest\DRSYS01.DBF',
  'D:\ORACLE\ORADATA\oratest\EXAMPLE01.DBF',
  'D:\ORACLE\ORADATA\oratest\INDX01.DBF',
  'D:\ORACLE\ORADATA\oratest\ODM01.DBF',
  'D:\ORACLE\ORADATA\oratest\TOOLS01.DBF',
  'D:\ORACLE\ORADATA\oratest\USERS01.DBF',
  'D:\ORACLE\ORADATA\oratest\XDB01.DBF',
  'D:\ORACLE\ORADATA\oratest\INDEX\INDPRD.DBF',
  'D:\ORACLE\ORADATA\oratest\DATA\DATPRD.DBF',
  'D:\ORACLE\ORADATA\oratest\DATA\DATDEM.DBF',
  'D:\ORACLE\ORADATA\oratest\DATA\DATDIS.DBF',
  'D:\ORACLE\ORADATA\oratest\DATA\DATHIS.DBF',
  'D:\ORACLE\ORADATA\oratest\DATA\DATREF.DBF',
  'D:\ORACLE\ORADATA\oratest\INDEX\INDDEM.DBF',
  'D:\ORACLE\ORADATA\oratest\INDEX\INDDIS.DBF',
  'D:\ORACLE\ORADATA\oratest\INDEX\INDHIS.DBF',
  'D:\ORACLE\ORADATA\oratest\INDEX\INDREF.DBF',
  'D:\ORACLE\ORADATA\oratest\INDEX\INDDIS2.DBF',
  'D:\ORACLE\ORADATA\oratest\INDEX\INDDIS3.DBF',
  'D:\ORACLE\ORADATA\oratest\INDEX\INDDIS4.DBF',
  'D:\ORACLE\ORADATA\oratest\INDEX\INDDIS5.DBF'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\oratest\TEMP02.DBF'
     SIZE 2000M REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\oratest\TEMP01.DBF'
     SIZE 2000M REUSE AUTOEXTEND OFF;

Enfin, il ne faut pas oublier de modifier le fichier d'initialisation de la base (initORAFRANCE.ora) si celui-ci a aussi été copié.
Il faut le renommer en initORATEST.ora et modifier les paramètres suivant : db_name control_file user_dump_dest, background_dump_dest, core_dump_dest utl_file_dir
Pensez également à réduire la taille de la base et supprimer le mode ARCHIVELOG si nécessaire.

II-C. Redémarrage de la base

Il suffit maintenant de se connecter en internal à la base et exécuter le script précédent :
Sous UNIX :

 
Sélectionnez
sqlplus "/ as sysdba"
SQL>@control_file_oratest.txt
SQL>shutdown immediate
SQL>startup

Sous NT (dans une console) :

 
Sélectionnez
net start OracleServiceoratest
sqlplus "/ as sysdba"
SQL>@control_file_oratest.txt
SQL>shutdown immediate
SQL>startup

Cela aura pris 30 minutes environ (sans compter la copie de la base) et un petit tour dans le fichier alertoratest.log vous assurera que tout est opérationnel et que tous les paramètres sont bons :-)

III. Tracer une session

III-A. Oradebug

Il est possible de tracer une session sous Oracle. Cette trace permet de voir les requêtes exécutées ainsi que les temps d'exécution de chacune des étapes (parse, execute et fetch).
Pour lancer une trace vous pouvez utiliser la commande SQL*Plus : oradebug.
D'abord, recherchons le spid de la session en question :

 
Sélectionnez
SELECT spid 
FROM v$process a, v$session b
WHERE a.addr = b.paddr 
AND sid = &sid;

Le spid permet alors de positionner oradebug et lancer la trace proprement dite. Connectez-vous sous l'utilisateur SYS et lancez (en mentionnant votre spid) :

 
Sélectionnez
SQL> oradebug setospid 2961508
Oracle pid: 48, Unix process pid: 2961508, image: oracle@canebiere (TNS V1-V3)
SQL> oradebug event 10046 trace name context forever, level 8
Statement processed.
SQL>

La trace de niveau 8 (le niveau permet de tracer plus ou moins de détail) est en cours. Un fichier trace doit être créé dans le répertoire indiqué par le paramètre user_dump_dest.

 
Sélectionnez
SQL> show parameter user_dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /oracle/admin/orafrance/udump

Pour arrêter la trace vous devez lancer la commande suivante :

 
Sélectionnez
SQL> oradebug event 10046 trace name context off
Statement processed.

III-B. Analyse de la trace

L'opération précédente a généré un fichier de cette forme :

Fichier trace
Sélectionnez
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /oracle
System name:    AIX
Node name:      oraserver
Release:        2
Version:        5
Machine:        0026510C4C00
Instance name: orafrance
Redo thread mounted by this instance: 1
Oracle process number: 48
Unix process pid: 2961508, image: oracle@oraserver (TNS V1-V3)

*** 2005-06-07 11:00:33.056
*** SESSION ID:(51.13) 2005-06-07 11:00:32.994
WAIT #1: nam='pipe get' ela= 2517030 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 1000024 p1=504403158592102776 p2=4096 p3=20
APPNAME mod='01@</appl/glb/4.0.0/sql/GLBSUBMIT.sql' mh=1035094561 act
='' ah=4029777240
=====================
PARSING IN CURSOR #25 len=715 dep=1 uid=173 oct=3 lid=173 tim=1091928548919953 h
v=2807191513 ad='1379eed0'
SELECT IR1.GROUP_REQUEST_ID,MIN(IR1.GROUP_ORDER_NUM) ORDRE FROM JEFB_CONC_INST_R
EQUESTS IR1 WHERE IR1.PHASE_CODE = 'I' AND ( (IR1.FORCE_RUN = 'N' AND NOT EXISTS
 (SELECT 'requete precedente non terminee ou en erreur' FROM JEFB_CONC_INST_REQU
ESTS IR2 WHERE IR2.GROUP_REQUEST_ID = IR1.GROUP_REQUEST_ID AND IR2.GROUP_ORDER_N
UM < IR1.GROUP_ORDER_NUM AND ( IR2.PHASE_CODE <> 'C' OR (IR2.PHASE_CODE = 'C' AN
D IR2.STATUS_CODE NOT IN ('C','I','R')) ))) OR (IR1.FORCE_RUN = 'Y' AND NOT EXIS
TS (SELECT 'requete precedente en cours' FROM JEFB_CONC_INST_REQUESTS IR2 WHERE
IR2.GROUP_REQUEST_ID = IR1.GROUP_REQUEST_ID AND IR2.GROUP_ORDER_NUM < IR1.GROUP_
ORDER_NUM AND IR2.PHASE_CODE IN ('P','R'))) ) GROUP BY IR1.GROUP_REQUEST_ID
END OF STMT
EXEC #25:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928548919919
FETCH #25:c=0,e=57,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928548922291
XCTEND rlbk=0, rd_only=1
=====================
PARSING IN CURSOR #27 len=6 dep=1 uid=173 oct=44 lid=173 tim=1091928548922410 hv
=1053795750 ad='125e3f68'
COMMIT
END OF STMT
EXEC #27:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928548922408
WAIT #1: nam='pipe get' ela= 2000021 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 3000027 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 4000026 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 5000029 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 5000021 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 1000017 p1=504403158592102776 p2=4096 p3=20
EXEC #25:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928568923249
FETCH #25:c=0,e=30,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928568923330
XCTEND rlbk=0, rd_only=1
EXEC #27:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928788936635
WAIT #1: nam='pipe get' ela= 2000021 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 3000021 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 4000020 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 5000023 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 5000021 p1=504403158592102776 p2=4096 p3=20
WAIT #1: nam='pipe get' ela= 1000019 p1=504403158592102776 p2=4096 p3=20
EXEC #25:c=0,e=88,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928808937449
FETCH #25:c=0,e=39,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928808937541
XCTEND rlbk=0, rd_only=1
EXEC #27:c=0,e=45,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928808937641
WAIT #1: nam='pipe get' ela= 2000021 p1=504403158592102776 p2=4096 p3=20

Je ne rentrerais pas trop dans les détails puisque nous verrons dans l'article suivant comment lire plus facilement ce résultat. Néanmoins, il n'est pas inintéressant de savoir interpréter un minimum cette trace.
L'entête récapitule les données du système : OS, nom de l'instance, nom du serveur, etc…

Ligne du fichier Description
*** SESSION ID:(51.13) 2005-06-07 11:00:32.994 Indique le SID et SERIAL# de la session tracée et l'heure de début de la trace
APPNAME mod='01@</appl/glb/4.0.0/sql/GLBSUBMIT.sql' mh=1035094561 act ='' ah=4029777240 C'est le script /appl/glb/4.0.0/sql/GLBSUBMIT.sql qui est en cours
=====================
PARSING IN CURSOR #25 len=715 dep=1 uid=173 oct=3 lid=173 tim=1091928548919953 h v=2807191513 ad='1379eed0'
Le curseur 25 (id interne) est en cours de parsing (phase d'analyse de la requête). Ce curseur traite la requête SQL qui suit ces lignes jusqu'à la ligne END OF STMT
EXEC #25:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928548919919
FETCH #25:c=0,e=57,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928548922291 XCTEND rlbk=0, rd_only=1
Phase execute et fetch (exécution de la requête et restitution du résultat).
WAIT #1: nam='pipe get' ela= 2000021 p1=504403158592102776 p2=4096 p3=20 Événement d'attente pipe get sur le cursor 1.

Je n'ai pas parlé de la signification des variables c ou e dont on peut lire les valeurs.
Voila une liste de ce qu'elles indiquent :

  • Liste des indicateurs
  1. c =>temps cpu en centième de seconde
  2. e =>temps total en centième de seconde
  3. p => nombre de lectures sur disque
  4. (cr + cu) => nombre buffers gets
  5. r => nombre de lignes retournées
  6. og => mode de l'optimiseur : 4 => CHOOSE

Ainsi la ligne :
EXEC #25:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1091928548919919
Indique une exécution qui n'a pas consommé de temps processeur mais qui a duré 108cs (1s).

Ici l'extrait de trace est suffisamment peu fournit pour admettre une lecture. Mais il est très difficile de lire un fichier trace exhaustif. Heureusement, Oracle fournit un outil pour formater ce fichier pour faciliter la lecture, cette outil s'appelle tkprof.

III-C. Tkprof

tkprof est un outil aussi simple que pratique, vous découvrirez tous les paramètres d'utilisation dans la documentation Oracle
Voici une commande à exécuter pour convenir à la majorité des cas :

 
Sélectionnez
tkprof unx1_ora_2961508 orafrance explain=apps/apps sys=no sort=execnt,fchcnt
Ligne du fichier Description
tkprof orafrance_ora_2961508 orafrance Lance tkprof et analyse la trace orafrance_ora_2961508.trc et génère le fichier orafrance.prf.
explain=apps/apps Exécute le plan d'exécution en se connectant apps/apps.
sys=no Exclus les requêtes systèmes.
sort=execnt,fchcnt Tri les requêtes par nombre d'exécutions et nombre de lignes ramenées.

Le résultat de la commande précédente est le suivant :

 
Sélectionnez
TKPROF: Release 9.2.0.5.0 - Production on Tue Jun 7 12:02:13 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: orafrance_ora_2961508.trc
Sort options: execnt  fchcnt
********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SELECT IR1.GROUP_REQUEST_ID,MIN(IR1.GROUP_ORDER_NUM) ORDRE
FROM
 JEFB_CONC_INST_REQUESTS IR1 WHERE IR1.PHASE_CODE = 'I' AND ( (IR1.FORCE_RUN =
   'N' AND NOT EXISTS (SELECT 'requete precedente non terminee ou en erreur'
  FROM JEFB_CONC_INST_REQUESTS IR2 WHERE IR2.GROUP_REQUEST_ID =
  IR1.GROUP_REQUEST_ID AND IR2.GROUP_ORDER_NUM < IR1.GROUP_ORDER_NUM AND (
  IR2.PHASE_CODE <> 'C' OR (IR2.PHASE_CODE = 'C' AND IR2.STATUS_CODE NOT IN
  ('C','I','R')) ))) OR (IR1.FORCE_RUN = 'Y' AND NOT EXISTS (SELECT 'requete
  precedente en cours' FROM JEFB_CONC_INST_REQUESTS IR2 WHERE
  IR2.GROUP_REQUEST_ID = IR1.GROUP_REQUEST_ID AND IR2.GROUP_ORDER_NUM <
  IR1.GROUP_ORDER_NUM AND IR2.PHASE_CODE IN ('P','R'))) ) GROUP BY
  IR1.GROUP_REQUEST_ID


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute     14      0.01       0.00          0          0          0           0
Fetch       14      0.00       0.00          0         14          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       28      0.01       0.00          0         14          0           0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 173  (APPS)   (recursive depth: 1)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (GROUP BY NOSORT)
      0    FILTER
      0     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                'JEFB_CONC_INST_REQUESTS'
      0      INDEX   GOAL: ANALYZED (FULL SCAN) OF
                 'JEFB_CONC_INST_REQUESTS_N1' (NON-UNIQUE)
      0     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                'JEFB_CONC_INST_REQUESTS'
      0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                 'JEFB_CONC_INST_REQUESTS_N1' (NON-UNIQUE)
      0     TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                'JEFB_CONC_INST_REQUESTS'
      0      INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                 'JEFB_CONC_INST_REQUESTS_N1' (NON-UNIQUE)

********************************************************************************

COMMIT


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute     14      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       14      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 173  (APPS)   (recursive depth: 1)



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  pipe get                                       81        5.00        265.52


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute     28      0.01       0.00          0          0          0           0
Fetch       14      0.00       0.00          0         14          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       42      0.01       0.00          0         14          0           0

Misses in library cache during parse: 0

    2  user  SQL statements in session.
    0  internal SQL statements in session.
    2  SQL statements in session.
    1  statement EXPLAINed in this session.
********************************************************************************
Trace file: orafrance_ora_2961508.trc
Trace file compatibility: 9.02.00
Sort options: execnt  fchcnt
       1  session in tracefile.
       2  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       2  SQL statements in trace file.
       2  unique SQL statements in trace file.
       1  SQL statements EXPLAINed using schema:
           APPS.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
     163  lines in trace file.

Vous voyez que le résultat est bien plus explicite et trivial. Vous noterez toutefois qu'avec tkprof nous perdons l'information des événements d'attente. Pensez donc à bien relire la trace originale pour essayer d'expliquer par exemple les écarts entre temps CPU et temps réellement écoulé.

Remerciements

Je tenais à remercier toutes les personnes qui m'ont aidées et encouragées dans la rédaction de cette article.

Merci à Developpez.com et ses membres (Fadace, Helyos, Pomalaix et Sheikyerbouti) pour leur accueil et leur aide.

Enfin, je vous remercie, vous, lecteur attentif de cet article puisque c'est bien vous qui m'encouragez à partager mon maigre savoir ;-)