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▲
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 courante 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▲
SHUTDOWN [paramètre];- NORMAL Les nouvelles connexions ne sont pas permises, le serveur oracle attend la fin de toutes les connexions.
- 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écutent 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 courants ;
- v$session Liste des informations 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 :
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 :
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ée (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 :
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 :
SELECT groups, current_group#, sequence# FROM v$thread;Pour voir des informations sur les groupes et les membres :
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 :
SELECT * FROM v$logfile;Pour forcer le switch de groupe de redo log :
ALTER SYSTEM SWITCH LOGFILE;Cette commande permet d'archiver le redo log courant et d'activer le redo log suivant.
ALTER SYSTEM CHECKPOINT;Cette commande permet d'archiver le redo log courant.
Pour ajouter un groupe de fichier de redo log :
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 :
ALTER DATABASE DROP LOGFILE GROUP 2;Pour supprimer un membre d'un fichier redo log ONLINE :
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.
SELECT group#, status FROM v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
1 ACTIVE
2 CURRENT
2 INACTIVE-
Créer un fichier de redo temporaire de la taille des redo logs :
SélectionnezALTERDATABASEADDLOGFILEGROUP3'g:\oracle\oradata\orafrance\REDO05.LOG'size 10M; -
Positionner le redo courant sur ce nouveau fichier :
SélectionnezALTERSYSTEM SWITCHLOGFILE;ALTERSYSTEM SWITCHLOGFILE;SELECTgroup#, status FROM v$log;GROUP# STATUS---------- ----------------1INACTIVE1INACTIVE2INACTIVE2ACTIVE3CURRENT -
Supprimer les fichiers de redo logs et les recréer dans le nouveau répertoire :
SélectionnezALTERDATABASEDROPLOGFILEMEMBER'g:\oracle\oradata\orafrance\REDO01.LOG';ALTERDATABASEADDLOGFILEGROUP1'g:\oracle\oradata\orafrance\redo\REDO01.LOG'size 10M;ALTERDATABASEDROPLOGFILEMEMBER'g:\oracle\oradata\orafrance\REDO02.LOG';ALTERDATABASEADDLOGFILEGROUP1'g:\oracle\oradata\orafrance\redo\REDO02.LOG'size 10M;ALTERDATABASEDROPLOGFILEMEMBER'g:\oracle\oradata\orafrance\REDO01.LOG';ALTERDATABASEADDLOGFILEGROUP2'g:\oracle\oradata\orafrance\redo\REDO03.LOG'size 10M;ALTERDATABASEDROPLOGFILEMEMBER'g:\oracle\oradata\orafrance\REDO02.LOG';ALTERDATABASEADDLOGFILEGROUP2'g:\oracle\oradata\orafrance\redo\REDO04.LOG'size 10M;SELECTgroup#, status FROM v$log;GROUP# STATUS---------- ----------------1UNUSED1UNUSED2UNUSED2UNUSED3CURRENT -
Penser à supprimer les fichiers g:\oracle\oradata\orafrance\REDOxx.LOG rendus inutiles.
- Activer tous les groupes et supprimer le redo log temporaire :
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 CURRENTPenser à 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 :
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 type « 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 l’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 :
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 :
ALTER TABLESPACE ora_data ADD DATAFILE '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 :
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 :
-
Mettre le tablespace OFFLINE :
SélectionnezALTERTABLESPACEora_dataOFFLINE; -
Copier le fichier dans le nouveau répertoire ;
-
Renommer le fichier :
SélectionnezALTERDATABASERENAMEFILE'g:\oracle\oradata\orafrance\ORA_DATA01.dbf'TO'g:\oracle\oradata\orafrance\data\ORA_DATA1.dbf; -
Mettre le tablespace ONLINE :
SélectionnezALTERTABLESPACEora_dataONLINE - Supprimer le fichier g:\oracle\oradata\orafrance\ORA_DATA01.dbf.
Pour mettre un tablespace en lecture seule :
ALTER TABLESPACE app_data READ ONLY;Pour mettre un tablespace en lecture/écriture :
ALTER TABLESPACE app_data READ WRITE;Pour supprimer un tablespace :
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 indiquent à 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 :
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 :
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 :
CREATE CONTROLFILE REUSE DATABASE "orafrance" NORESETLOGS ARCHIVELOGdevient :
CREATE CONTROLFILE SET DATABASE "oratest" RESETLOGS ARCHIVELOGNous 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 ressembler à ceci :
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 suivants : 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 :
sqlplus "/ as sysdba"
SQL>@control_file_oratest.txt
SQL>shutdown immediate
SQL>startupSous NT (dans une console) :
net start OracleServiceoratest
sqlplus "/ as sysdba"
SQL>@control_file_oratest.txt
SQL>shutdown immediate
SQL>startupCela 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 :
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) :
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.
SQL> show parameter user_dump
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string /oracle/admin/orafrance/udumpPour arrêter la trace vous devez lancer la commande suivante :
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 :
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=20Je ne rentrerai 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 |
|
===================== |
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 |
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.
Voilà une liste de ce qu'elles indiquent.
- Liste des indicateurs
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 fourni 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, cet 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 :
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 :
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é et encouragé dans la rédaction de cet 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 ;-)