A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 Administration des bases de données relationnelles (suite) 3 – Sécurité et confidentialité : la gestion des utilisateurs Le SGBDR permet de distinguer les administrateurs (super utilisateurs) des autres utilisateurs. L’administrateur a le droit de créer et de supprimer des utilisateurs. C’est également lui qui leur affecte des droits d’accès aux différentes structures logiques de la base. La norme SQL La norme stipule qu’un utilisateur ne peut exécuter une opération sur la base de données qu’à la condition de détenir le privilège correspondant. Un utilisateur possède tous les droits sur les objets dont il est propriétaire. La norme définit les privilèges suivants : -SELECT : privilège d’accès à toutes les colonnes d’une table -INSERT : privilège autorisant l’insertion de tuple dans une table ; peut être limité à certaines colonnes de la table -UPDATE : privilège autorisant la mise à jour des tuples d’une table ; peut être limité à certaines colonnes de la table -DELETE : privilège autorisant la suppression de tuples dans une table -REFERENCES : privilège autorisant l’utilisation des colonnes d’une table dans une contrainte d’intégrité Les SGBDR du marché propose généralement des privilèges spécifiques tels que : ALTER (pour autoriser la modification de structure d’une table), INDEX (pour autoriser la gestion des index), DBA (pour obtenir tous les privilèges de l’administrateur), ALL (pour obtenir tous les privilèges sur une table),…. La norme définit également deux instructions : -une instruction pour allouer des privilèges à un(plusieurs) utilisateur(s) : GRANT privilèges ON objet TO liste d’utilisateurs [with grant option] ; L’option permet à l’utilisateur qui reçoit le privilège de le redistribuer à d’autres utilisateurs L’objet peut être une table, une vue, un index, une séquence Exemple : Autoriser la lecture et la mise à jour de la table AVION à l’utilisateur univ_ag : GRANT UPDATE, SELECT ON avion TO univ_ag ; Autoriser la lecture de la table VOL à tous les utilisateurs (PUBLIC) : GRANT SELECT ON vol TO PUBLIC ; Autoriser la valorisation de la clé étrangère NUM_AV dans la table VOL qui référence la clé NUM_AV de la table AVION à l’utilisateur univ_ag : GRANT REFERENCES(num_av) ON TABLE vol TO univ_ag ; -une instruction pour enlever des privilèges à un(plusieurs) utilisateur(s) : REVOKE [GRANT OPTION FOR] privilèges ON objet FROM liste d’utilisateurs [Restrict | Cascade] L’option permet de retirer à l’utilisateur le droit qui lui avait été préalablement accordé par l’option du GRANT Exemple : Enlever à l’utilisateur univ_ag l’accès en mise à jour à la table avion REVOKE UPDATE ON avion FROM univ_ag ; P.1 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 La gestion des utilisateurs sous Oracle A sa création (ordre sql « Create user »), un utilisateur ne possède aucun privilège, il faut les lui affecter explicitement (Grant). Oracle dispose de deux types de privilèges ainsi que des rôles pour gérer les droits d’accès des utilisateurs aux objets. Un « privilège système », objectivement destiné aux administrateurs, autorise des opérations système sur la base de données. Exemple de privilèges système : « alter database » : privilège de modifier la base de données « create role » : privilège de créer des rôles « create tablespace » : privilège de créer des tablespaces « create any table » : privilège de créer des tables dans n’importe quel schéma Un « privilège objet » permet de contrôler les accès à un objet particulier. Ce sont les privilèges définis par la norme sql, complétés de privilèges spécifiques à Oracle. Exemple de privilèges objet spécifique à Oracle : « alter » : donne le privilège de modifier la structure de l’objet référencé (table, vue) « index » : donne le privilège de créer des index pour la table référencée « execute » : donne le privilège d’exécuter la fonction/procédure référencée Un « rôle » est un regroupement de plusieurs privilèges et/ou autres rôles qui facilite l’attribution de privilèges multiples aux utilisateurs. Exemple de rôles que l’on peut créer : rôle pour chaque tâche d’application, rôle pour chaque type d’utilisateur. Plusieurs rôles sont prédéfinis par Oracle comme par exemple « dba » : tous les privilèges système « connect » : le privilège de se connecter à la base de données, de créer une session A sa création (ordre sql « Create role »), un rôle ne possède aucun privilège, ces derniers doivent être explicitement attribués au rôle en utilisant un ordre GRANT. A la création d’une base de données, Oracle crée les administrateurs SYS et SYSTEM qui possède tous les privilèges système et tous les rôles prédéfinis. Lors de la création d’un utilisateur, on lui attribue en général trois tablespaces : un tablespace Données qui est le tablespace par défaut de l'utilisateur ; un tablespace pour le tri qui est le tablespace temporaire de l’instance en général ; un tablespace pour les index qui est également le tablespace dédié aux index en général dans l’instance. Il est aussi possible d’affecter un PROFIL à un utilisateur. Un profil (ordre sql « Create profile »)est un ensemble nommé de limites de ressources (temps de connexion, temps d’inactivité, SGA privée, durée de vie du mot de passe, nombre de tentatives infructueuse de login, …). Etapes de création d’un utilisateur -choisir le nom et le mode d’identification (Oracle ou Système d’Exploitation) -identifier les tablespaces requis (données, tris, index) -arrêter le quota pour chaque tablespace -créer l’utilisateur (create user) -allouer les rôle et/ou privilèges Gestion d’un compte utilisateur Modification L’ordre de modification est ALTER USER, pour modifier des paramètres de l’utilisateur tels que son tablespace par défaut, son quota dans son tablespace, son mot de passe, son droit à se connecter à la base, … Suppression L’ordre de suppression est DROP USER, à utiliser avec l’option « CASCADE » si l’utilisateur possède des objets Information Plusieurs vues du Dictionnaire fournissent des informations sur les utilisateurs, parmi elles : DBA_USERS, et DBA_TS_QUOTAS P.2 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 P.3 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 4 – Etapes de la mise en place d’une base de données Oracle Etape 1 : Evaluation des ressources matérielles Déterminer le nombre de disques disponibles pour Oracle, les media de sauvegardes utilisables, la taille mémoire disponible pour l’instance Oracle. Etape 2 : Plannifier la mise en place de la base de données Prévoir les structures logiques et physiques de stockage, concevoir l’organisation globale de la base, penser la stratégie de sauvegarde . Avoir une idée précise du type des données ainsi que de l’organisation des données, au sens sémantique/applicatif, que vont contenir les tablespaces ; ceci afin entre autres de mieux déterminer la taille des blocs, les paramètre de gestion de l’espace libre Avoir connaissance du nombre de fichier de données qui vont constituer les tablespaces et de la localisation physique de ces fichiers (cas plusieurs disques) Prendre des options en matière de gestion des annulations (tablespace dédié ou non) Estimer la taille des tables et des index associés … Les choix faits à ce stade vont influencer les performances de la machine exécutant Oracle, les performances du SGBD pendant les opérations sur la base, l’efficacité des procédures de sauvegarde/restauration. Etape 3 : Installer Oracle Suivre les étapes spécifiées dans le guide d’installation : vérification des pré-requis hardware et software, création des utilisateurs et groupe Unix/Linux requis par Oracle, création des répertoires requis, configuration des paramètres du noyau Unix/linux, configuration des paramètre d’environnement Oracle. Au terme de la procédure d’installation une instance est créée. Etape 4 : effectuer les vérifications post-installation Un fichier trace a été produit par la procédure d’installation, il est recommandé d’en vérifier le contenu. Il est recommandé de vérifier les variables d’environnement telles que : ORACLE_HOME, PATH, et LD_LIBRARY_PATH. Les paramètres d’initialisation de la base doivent être vérifiés et éventuellement modifiés dans le fichier « init.ora ». Une base de données Oracle peut être dans un des états suivants : « Fermée », « Non montée » (instance montée, base en création ou fichier de contrôle en création), « Montée » (instance démarrée et fichiers de contrôle ouverts), « Ouverte » (instance démarrée, fichiers de contrôle et de données ouverts). Il est recommandé, après installation, de procéder au démarrage (startup) puis à la fermeture (shutdown) de l’instance . oracle$ sqlplus /nolog SQL> connect / as sysdba SQL> startup oracle$ sqlplus /nolog SQL> connect / as sysdba SQL> shutdown Où “/” (slash) permet de se connecter au schéma de l’administrateur “SYS”, et le privilège “sysdba” permet entre autre de créer des bases de données Etape 5 : Créer la base de données Concevoir l’ordre Create Database qui correspond à la base P.4 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 Exemple de création complète CREATE DATABASE mynewdb USER SYS IDENTIFIED BY pz6r58 USER SYSTEM IDENTIFIED BY y1tz5p LOGFILE GROUP 1 ('/u01/oracle/oradata/mynewdb/redo01.log') SIZE 100M, GROUP 2 ('/u01/oracle/oradata/mynewdb/redo02.log') SIZE 100M, GROUP 3 ('/u01/oracle/oradata/mynewdb/redo03.log') SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 DATAFILE '/u01/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE '/u01/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE tbs_1 DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; Exemple de création où c’est Oracle qui assure la gestion des fichiers CREATE DATABASE rbdb1 USER SYS IDENTIFIED BY pz6r58 USER SYSTEM IDENTIFIED BY y1tz5p UNDO TABLESPACE undotbs DEFAULT TEMPORARY TABLESPACE tempts1; Etape 6 : Sauvegarder la base Sauvegarder la structure de la base. Prévoir la programmation des sauvegardes à venir Déterminer les fichiers de reprises Définir une politique de sauvegarde -sauvegarde à chaud en mode « archivage automatique », sans arrêt de la base -sauvegarde à froid : les fichiers concernés(data, control, reprise)sont sauvegardés après arrêt de la base 5 – Optimiser les performances d’une base de données 5.1 - Compétences et fonctions d’un administrateur de base de données L’administrateur doit posséder des compétences couvrant plusieurs domaines: - l’entreprise et la conception du système d’informations il doit maîtriser la signification des informations manipulées par le SGBD et les règles de gestion associées. - le SGBDR il doit maîtriser techniquement le SGBD, i.e maîtriser ses fonctionnalités, ses contraintes et ses limites, ses outils de surveillance et d’administration, ses paramètres de réglage, les langages de définition et de manipulation supportés - l’architecture technique il doit connaître le fonctionnement du matériel et du système d’exploitation supportant le SGBD Deux types de fonctions pour l’administrateur : - fonctions de coordination il doit savoir faire appel au bon interlocuteur au bon moment : chef de projet, ingénieur système, maintenance du SGBD, développeur, ... - fonctions techniques le DBA doit administrer à plusieurs niveaux : P.5 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 - au niveau logique de la base de données : droits utilisateurs, création de vues, surveillance du fonctionnement, mesures et analyses de performances, ... - au niveau physique de la base de données : volumes d’information, répartition physique des données, paramètrage du stockage disque, dimensionnement des structures mémoire, ..... - au niveau optimisation : choix index, optimisation requêtes, clusterisation, .... 5.2 – Optimisation de l’espace logique de stockage Optimiser l’exploitation de la SGA L’une des vues du dictionnaire la plus utile à cette fin est v$sysstat. Performances du Buffer Cache : il faut que le ratio (R) « nombre de lectures sur disque », « nombre total de lectures sur disque et en mémoire » soit grand. R = 1 – (Physical read / (db block gets + consistent gets) Optimiser l’exploitation des Data Blocks On peut agir sur PCTFREE et PCTUSED pour - augmenter les performances des recherches et écritures de données - diminuer la quantité d’espace inutilisée dans les data blocks - diminuer le nombre de chaînage entre les blocs Un petit PCTFREE a pour conséquences : - moins d’espace disponible pour la mise à jour des lignes existantes - ce sont les insertions qui contribuent principalement à remplir le bloc - moins d’espace au total pour stocker les tables Un petit PCTFREE sera mieux adapté aux segments subissant peu de modifications, ou des modifications qui n’augmentent pas la taille de la rangée. Un grand PCTFREE a pour conséquences : - beaucoup d’espace disponible pour la mise à jour des lignes existantes - gain de performance dans les mises à jour dans la mesure où Oracle devrait avoir moins de chaînage à gérer - plus d’espace requis pour un même nombre d’insertions - plus d’espace au total pour stocker les tables Un grand PCTFREE sera mieux adapté aux segments fréquemment modifiés, ou pour lesquels les modifications contribuent fréquemment à augmenter la taille d’une rangée. Un petit PCTUSED a pour conséquence : - beaucoup d’espace inutilisé dans la base - moins de ressources consacrées au processus de passage en « freelist » lors des opérations de DELETE et d’UPDATE Un grand PCTUSED a pour conséquence : - utilisation plus optimisée de l’espace de stockage - plus de ressources consacré au processus de passage en liste des bloc libres lors des opérations de DELETE et d’UPDATE Règle : PCTFREE + PCTUSED < 100% Exemples de choix de PCTFREE et PCTUSED au niveau des tables : - Exemple1: l’exploitation de la table implique de fréquentes mises à jour qui augmente la taille des lignes. PCTFREE=20; pour permettre l’augmentation de taille des lignes. PCTUSED=40; pour diminuer la fréquence du processus de changement de file pendant les mises à jour - Exemple2: l’exploitation de la table consiste principalement en des INSERT, des DELETE et des UPDATE qui n’augmentent pas la taille des lignes. PCTFREE=5; puisqu’on n’a pas besoin d’espace pour les mises à jour. PCTUSED=60; pour pouvoir exploiter rapidement l’espace libéré par les DELETE - Exemple3: la table est très grande et la grande majorité des accès est en lecture seule. Le problème de stockage est donc essentiel. PCTFREE=5; pour remplir au maximum chaque bloc, compte tenu de la taille de la table. PCTUSED=90; pour la même raison. P.6 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 Positionner de façon optimale les paramètres de stockage Les paramètres de stockage peuvent être définis au niveau des objets (tables, index,...), ou au niveau du tablespace, ou pas du tout (utilisation des valeurs par défaut). INITIAL Taille en octets de la première extension allouée. Au mimimum : 2 blocs de données. Dans tous les cas c’est un nombre entier de blocs qui est alloué. NEXT Taille de base en octets des extensions allouées après la 1ère, avec un incrément à chaque nouvelle allocation, à partir de la 3ème. NEXT est la taille de la seconde extension NEXT est ensuite incrémenté : NEXT=NEXT*(1+PCTINCREASE/100) MAXEXTENTS Nombre maximum d’extension y compris l’extension initiale. Minimum=1 MINEXTENTS Nombre d’extensions à allouer à la création du segment. Minimum=1. Si la valeur est supérieure à 1, les extensions de taille incrémentée sont allouées dès la création selon les valeurs de INITIAL, NEXT et PCTINCREASE Pour un segment de rollback, la valeur de MINEXTENT est toujours 2. Une grande valeur de MINEXTENTS est conseillée pour charger une grande table intégralement avec succès PCTINCREASE Pourcentage d’augmentation de la taille de la prochaine extension à allouer; appliqué à partir de la 3ème extension. Adapter la stratégie de création des tablespaces Intérêts de plusieurs tablespaces : - séparer les données utilisateur des données du dictionnaire - séparer les données d’application différentes - diminuer le risque de contention dans les Entrées/Sorties disque en créant des tablespaces sur des disques distincts - séparer les données des segments de rollback des données utilisateur - mettre offline un tablespace alors que les autres demeurent online - réserver un tablespace à un usage particulier, par exemple le stockage des segments temporaires - effectuer la sauvegarde des différents tablespace séparément La commande CREATE TABLESPACE permet créer le tablespace avec les options adéquates. Pour modifier les valeurs des paramètres de stockage par la suite, il faudra utiliser la commande ALTER TABLESPACE ; les nouvelles valeurs n’affecteront que les nouvelles extensions à venir. L’ordre ALTER DATABASE permet également de modifier un tablespace. Gérer la disponibilité et le mode d’utilisation des tablespaces Un tablespace peut être - online, et dans ce cas les utilisateurs peuvent accéder aux objets qui y sont stockés - offline, et dans ce cas aucun des objets qui s’y trouvent n’est accessible par les utilisateurs NOTE : le tablespace SYSTEM doit toujours être online! ALTER TABLESPACE permet de mettre online un tablespace offline. Un tablespace peut également être accédé en lecture seulement (sauf SYSTEM bien sûr) : Un certain nombre de vues du dictionnaire permettent d’obtenir des informations sur les tablespaces. Nécessité d’avoir au moins deux segments de rollback Quand une base de données est créée, un seul segment de rollback, nommé SYSTEM, est créé dans le tablespace SYSTEM. Pour pouvoir exploiter (y créer des objets) d’autres tablespaces, il faut obligatoirement créer au moins un segment supplémentaire de rollback dans le tablespace SYSTEM. P.7 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 Deux paramètres permettent de contrôler l’exécution de transactions multiples par une instance. TRANSACTIONS = nombre de transactions s’exécutant concurremment. TRANSACTIONS_PER_ROLLBACK_SEGMENT = nombre de transactions prises en charge simultanément par chaque segment de rollback. il convient de ce fait de créer N segments de rollback avec N = TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT Segment de rollback SYSTEM Oracle l’utilise uniquement pour les transactions système. Il est également utilisé quand les autres segments de rollback ne suffisent plus pour le nombre de transactions s’exécutant concurremment. Stockage des segments de rollback Deux avantages à créer un tablespace spécialement pour stocker les segments de rollback créés en plus des 2 placés dans le tablespace SYSTEM : 1) on ne peut pas mettre offline un tablespace avec un segment de rollback actif; en séparant données utilisateurs et rollback on s’affranchit de cette contrainte 2) la liste des extensions libres d’un tablespace risque moins un haut degré de fragmentation sans segments de rollback; les segments de rollback effectuant fréquemment des allocations/désallocations d‘extensions. Assigner explicitement une transaction à un segment de rollback En utilisant le paramètre USE ROLLBACK SEGMENT dans la commande SET TRANSACTION qui, si utilisée, doit être la première instruction de la transaction. Possibilité utile en particulier dans le cas de très petites transactions ou de très longues transactions. En effet la taille des segments de roolback est choisie pour traiter au mieux les transactions les plus courantes. Le cas extrême de très petites transactions requiert des segments de petite taille (pour toujours avoir l’intégralité de la transaction en mémoire), alors que celui des très longues transactions requiert des segments de grande taille (pour éviter l’écrasement éventuel du début de la transaction). On peut donc prévoir quelques segments dédiés au très petites transactions et quelques autres dédiés aux très longues transactions. Visualiser des informations sur les rollback le dictionnaire contient des vues relatives aux segments de roolback, dont DBA_ROLLBACK_SEGS qui contient des informations telles que : nom du segment, tablespace de stockage, status du segment (online,offline). USER_SEGMENTS et DBA_SEGMENTS donne également des informations sur les segments de rollback. Administration des objets logiques de la base Estimer la taille des tables, pour plusieurs raisons : - cette estimation combinée aux estimations d’index , de rollback et de redo log permet un meilleur dimensionnement physique de la base de donnée - cette estimation permet une gestion optimisée de la table; les paramètres de stockage étant mieux adaptés Règles à retenir : stocker les données dans un petit nombre de grandes extensions vaut mieux que stocker les données dans un grand nombre de petites extensions Le cas des grandes tables : Pour n’avoir pas à se préoccuper des extensions futures, utiliser le mot clé ‘UNLIMITED’ pour le paramètre MAXEXTENTS. Noter cependant que les extensions ne pourront aller au-delà du nombre maximum de blocs potentiels du tablespace. P.8 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 Séparer les index des données, en les plaçant dans un tablespace distinct, si possible sur un disque physique différent. Les performances des entrées/sorties s’en trouvent améliorées. Penser à allouer suffisamment d’espace temporaire pour les tris. Maitrîser la conception des tables Parce que c’est le meilleur moyen d’en prévoir le mode d’exploitation, et donc de bien dimensionner PCTFREE et PCTUSED pour le paramétrage du stockage dans les blocs de données. 5.3 – Surveillance de la base de données Utiliser les Alertes Via les seuils spécifiés pour : temps de réponse, nombre d’accès disque, … Utiliser les fichiers trace Le DBA peut en déterminer l’emplacement, la taille, et la teneur. Il existe en général un fichier trace par processus serveur Utiliser l’outil d’audit Oracle Le DBA peut l’activer via le paramètre AUDIT_TRAIL pour lui permettre d’avoir des inofrmations sur : les tentatives de login, les accès aux objets de la base, les actions sur la bases de données, … Utiliser les statistiques Oracle Consultables via des vues du dictionnaires et paramétrables via le fichier init.ora 5.4 – Les moyens de l’optimisation 5.4.1 – Edition du fichier de paramètres Le fichier « init.ora » contient de nombreux paramètres impactant le fonctionnement du SGBDR (nom de fichier, taille de bloc, nombre de processus, …) 5.4.2 – Choix des index L’objectif d’un index est d’accélérer l’accès aux données. La recherche de performance implique : - indexation des colonnes intervenant régulièrement dans des jointures - indexation des colonnes intervenant régulièrement dans des clauses Order By - indexation des colonnes intervenant régulièrement dans des clauses Group By ou utilisées dans des fonctions de calcul (min, max, …) - éviter indexation des colonnes contenant très peu de valeurs distinctes - éviter indexation des colonnes très souvent modifiées et très peu utilisées en consultation 5.4.3 – Création de clusters Le cluster optimise les accès disques principalement en cas de jointure. Il est donc préférable de limiter son usage aux seuls cas où les tables clusterisées sont très fréquemment jointes. Le choix de la clé (des clés) de cluster doit être fait à bon escient pour permettre un usage optimal des blocs. P.9 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 ANNEXE Cluster Oracle (source : Oracle 10g Administrator Guide) P.10 A d mi n i s t r a t i o n B a s e d e D o n n é e s 2011-2012 ANNEXE Fichier « init.ora » Fichier texte contenant les paramètres d’initialisation d’une instance Exemples de paramètres # Cache et E/S db_block_size=4096 db_cache_size=33554432 # Configuration du fichier control_files=("C:\oracle\oradata\DBTEST\CONTROL01.CTL", "C:\oracle\oradata\DBTEST\CONTROL02.CTL", "C:\oracle\oradata\DBTEST\CONTROL03.CTL") # Curseurs et cache de bibliothèque open_cursors=300 # Diagnostics et statistiques background_dump_dest=C:\oracle\admin\DBTEST\bdump core_dump_dest=C:\oracle\admin\DBTEST\cdump timed_statistics=TRUE user_dump_dest=C:\oracle\admin\DBTEST\udump # Pools java_pool_size=33554432 large_pool_size=1048576 shared_pool_size=33554432 # Processus et sessions processes=150 # Segments d’annulation (Undo et Rollback) gérés par le système undo_management=AUTO undo_tablespace=UNDOTBS # Tri, jointures par hachage, index bitmap sort_area_size=524288 P.11