administration de bases de données avec oracle...

1

Administration de bases de données avec Oracle 11G Sommaire

PARTIE 1 : Administration logique1) Généralités2) Les privilèges3) Les rôles4) Les profiles5) Les utilisateurs

PARTIE2 : Administration physique1) Généralités2) La base de données3) Les instances4) Le dictionnaire de données5) Connexion d'un utilisateur6) Sauvegarde et restauration

E.Porcq : Cours BDD - IUT : DUT Informatique M3106CAnnée 2019-2020Sources :Administration Oracle 10G

Partie I G. Mopolo-Moké prof. MBDS / UNSA NICE

http://orafrance.developpez.com/dbahelp/Architecture d'Oracle de R.Chbeir

Oracle 11g Exploitation de C.Latouche (Tellora)

Oracle 11g Administration de R.Bizoï

Oracle 11g Administration de O.Heurtel

http://orafrance.developpez.com/dbahelp/

2

Administration de bases de données avec Oracle 11G Partie 1 : Administration logique 1) Généralités 1-1) Rôle de l'Administrateur de sécurité et des ressourcesDéfinir une politique de sécurité

Faire les choix du type de sécurité : au niveau système, au niveau Oracle, au niveau Global

Gérer les utilisateurs

Gérer les ressources (profiles)

Assurer l’affectation et le retrait des droits

Affiner la politique de sécurité par l'utilisation des rôles

Effectuer les audits

3

Administration de bases de données avec Oracle 11G Partie 1 : Administration logique 1) Généralités 1-2) Moyens pour la Gestion de la sécurité

Utilisateurs Audit

Profiles

Privilèges et Rôles

Droits d'accès aux tablespace(quotas, ...)

Les vues

4

Administration de bases de données avec Oracle 11G Partie 1 : Administration logique 2) Les privilègesUn privilège donne le droit d'exécuter certaines commandes SQL ou le droit d'accéder à certaines ressources

Oracle possède deux types de privilèges :les privilèges systèmesles privilèges objets.

Un privilège peut être affecté (retiré) à un Utilisateur,un Rôle tous les utilisateurs (PUBLIC)

5

Administration de bases de données avec Oracle 11G Partie 1 : Administration logique 2) Les privilèges 2-2) Les privilèges systèmesOracle 11 possède plus de 200 privilèges Systèmes

Les privilèges donnent le droit de réaliser des opérations systèmes

Ces privilèges sont classés par catégories d'objets

Exemple de privilèges systèmes de la catégorie TABLE:CREATE TABLE CREATE ANY TABLEALTER ANY TABLE BACKUP ANY TABLEDROP ANY TABLE LOCK ANY TABLELOCK ANY TABLE SELECT ANY TABLEINSERT ANY TABLE UPDATE ANY TABLEDELETE ANY TABLE COMMENT ANY TABLEUNDER ANY TABLE FLASHBACK ANY TABLE

UNDER : création de sous tableBACKUP : Autorise l'utilisation de l'outil d'export de tablesFLASHBACK : Restaurer des tables suppriméesLOCK : Verrouillage des tables

6

Administration de bases de données avec Oracle 11G Partie 1 : Administration logique 2) Les privilèges 2-2) Les privilèges systèmes

Affectation d’un privilège SystèmeGRANT { system_priv | role } TO { user | role | PUBLIC } [ WITH ADMIN OPTION ]user, role ou PUBLIC : droit affecté à un utilisateur, un rôle ou public (à tous) With Admin Option : le bénéficiaire* pourra redistribuer le privilège ( * pas un rôle)

Ex :GRANT alter tablespace to ETU1_45; GRANT create session, create user to ETU1_45 with admin option; GRANT alter any table to public;

Révocation d’un privilège SystèmeREVOKE { <system_priv> | <rôle> } FROM { <utilisateur> | <rôle> | PUBLIC }Ex : REVOKE ALTER ANY TABLE FROM PUBLIC ; REVOKE CREATE SESSION FROM DIMITRI ;

Les vues du dictionnaireSELECT * FROM DBA_SYS_PRIVS ORDER BY grantee, privilege ;GRANTEE PRIVILEGE ADMCONNECT ALTER SESSION NOCONNECT CREATE CLUSTER NO

7

Administration de bases de données avec Oracle 11G Partie 1 : Administration logique 2) Les privilèges 2-3) Les privilèges objets

Ces privilèges contrôlent l'accès aux objets des tables, vues ...

Classification selon les types d'objets :Privilèges objets Libellé Objets concernésALTER droit de modifier table, séquenceDELETE droit de supprimer table , vue, VMEXECUTE droit d’exécuter procédure, fonction,package,type user,

opérateur, indextype,libraryINDEX droit de créer un index table (ne peut être affecté à un rôle)INSERT droit d’insérer table, vue,VM...

Affectation de privilèges objetsGRANT { object_priv | ALL [ PRIVILEGES ] } [( column [,column ] ...) ] [, { object_priv | ALL [ PRIVILEGES ] } [ ( column [,column] ...) ] ] ON [ schema.] object TO { user | role | PUBLIC } [ WITH GRANT OPTION ]

EX : GRANT INSERT ON coureur TO BIL with grant option; GRANT UPDATE (moyenne), DELETE ON temps TO role_etu1; GRANT SELECT ON tdf_temps_cfi to ETU1_02;

8


Révocation de privilèges objetsREVOKE { object_priv | ALL [ PRIVILEGES ] } ON [ <schéma>. ] object FROM { user | role | PUBLIC } [CASCADE CONSTRAINTS ]

CASCADE CONSTRAINTS : s’emploie avec le privilège REFERENCES,supprime les contraintes d'intégrité mises.

WITH GRANT OPTION: Si un utilisateur U1 a affecté un privilège P1 à U2 et U2 l'a affecté à U3, le retrait à U2 entraîne le retrait à U3 : le retrait se fait en cascade.

REVOKE DELETE ON coureur FROM Kenzo;REVOKE UPDATE ON participation FROM public;REVOKE REFERENCES ON temps FROM role_etu1;

9


Tables des privilèges objetsDBA_TAB_PRIVS DBA_COL_PRIVS ALL_TAB_PRIVS ALL_COL_PRIVS USER_TAB_PRIVS USER_COL_PRIVSAll_TAB_PRIVS_MADE DBA_COL_PRIVS USER_TAB_PRIVS_MADEALL_COL_PRIVS_MADE USER_TAB_PRIVS_MADE USER_COL_PRIVS_MADEALL_TAB_PRIVS_RECD ALL_COL_PRIVS_RECD USER_TAB_PRIVS_RECDALL_COL_PRIVS_RECD TABLE_PRIVILEGES COLUMN_PRIVILEGES

Visualisation de tous les droits sur les objets de la baseSELECT * FROM sys.dba_tab_privs WHERE table_name = ’coureur’ OR table_name = ’temps’;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGEIUT022 SCOTT COUREUR ERIC ALTERIUT022 SCOTT TEMPS PATRICE DELETEIUT022 SCOTT COUREUR ERIC INDEXIUT022 SCOTT TEMPS PATRICE INSERTIUT025 SCOTT COUREUR ERIC SELECTIUT025 SCOTT TEMPS PATRICE UPDATEIUT025 SCOTT COUREUR ERIC REFERENCES

10

Administration de bases de données avec Oracle 11G Partie 1 : Administration logique 3) Les rôles

Un rôle est un concept Oracle qui permet de regrouper plusieurs privilèges et /ou rôles afin de les affecter ou retirer en bloc à un utilisateur et /ou un rôle.

Un rôle facilite la gestion des privilèges

L'affectation d'un rôle à un utilisateur peut se faire sous Oracle ou à travers l'OS

Pour des raisons de sécurité, un mot de passe peut être assigné à un rôle

Oracle fournit un certain nombre de rôles par défaut (connect, resource, dba, exp_full_database, imp_full_data_base, select_catalog_role, delete_catalog_role / execute_catalog_role, …)

Pour créer un rôle, il faut avoir le privilège "CREATE ROLE"

11


Assignation de privilèges aux utilisateurs : SANS ROLES

Assignation de privilèges aux utilisateurs : VIA UN ROLE

Privilège 1

Privilège 2

Privilège 3

Privilège 4

Utilisateur 1

Utilisateur 2

Privilège 1

Privilège 2

Privilège 3

Privilège 4

Utilisateur 1

Utilisateur 2

Rôle 1

12


Création d'un rôleCREATE ROLE rôle [ { NOT IDENTIFIED | IDENTIFIED { BY password | EXTERNALLY | GLOBALLY | USING package} ]Mots clés et paramètresNOT IDENTIFIED : permet de créer un rôle sans mot de passeEXTERNALLY : mot de passe est contrôlé au niveau de l'OSGLOBALLY : Rôle autorisé au niveau de l’annuaireUSING package : rôle applicatif

ExempleCREATE ROLE role_etu2;CREATE ROLE rl_admin_secu IDENTIFIED BY secu_pass ;

Suppression d'un rôleLe privilège DROP ANY ROLE et le fait d'avoir acquis un rôle avec WITH ADMIN OPTION permettent de le supprimerSyntaxe : DROP ROLE role;

Exemple : DROP ROLE rl_admin_secu ;

13


Création d'un rôleCREATE ROLE rôle [ { NOT IDENTIFIED | IDENTIFIED { BY password | EXTERNALLY | GLOBALLY | USING package} ]Mots clés et paramètresNOT IDENTIFIED : permet de créer un rôle sans mot de passeEXTERNALLY : mot de passe est contrôlé au niveau de l'OSGLOBALLY : Rôle autorisé au niveau de l’annuaireUSING package : rôle applicatif

ExempleCREATE ROLE role_etu2;CREATE ROLE rl_admin_secu IDENTIFIED BY secu_pass ;

Suppression d'un rôleLe privilège DROP ANY ROLE et le fait d'avoir acquis un rôle avec WITH ADMIN OPTION permettent de le supprimerSyntaxe : DROP ROLE role;

Exemple : DROP ROLE rl_admin_secu ;

14


Affectation de privilèges ou de rôles à un rôleEx: CREATE ROLE rl_connect; CREATE ROLE rl_admin_secu identified by aliceCooper; GRANT create session, alter session, Restricted session TO rl_connect; GRANT create role, create user, create profile TO rl_admin_secu; GRANT rl_connect TO rl_admin_secu ;

Affectation d'un rôle à un UtilisateurEx : GRANT role_iut01 to IUT04 WITH ADMIN OPTION;

Un rôle affecté à un utilisateur par la commande grant devient automatiquement un « rôle par défaut » (voir gestion des utilisateurs)

Rôles (principaux) prédéfinisCONNECT ALTER SESSION, CREATE CLUSTER,CREATE DATABASE LINK,

CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM,CREATE TABLE, CREATE VIEW

RESOURCE CREATE CLUSTER, CREATE PROCEDURE,CREATE SEQUENCE,CREATE TABLE, CREATE TRIGGER

DBA tous les privilèges WITH ADMIN OPTION

15


Activation/Désactivation d'un rôleSET ROLE Permet d'activer un rôle parmi les rôles attribués mais pas ceux « par défaut »Ex: SET ROLE rl_connect ; SET ROLE rl_admin_secu identified by aliceCooper; SET ROLE all ; –- * SET ROLE all except rl_connect; –- * SET ROLE none ; –- attention aux problèmes

Roles listed in the EXCEPT clause must be roles granted directly to you. They cannot be roles granted to you through other roles. If you list a role in the EXCEPT clause that has been granted to you both directly and through another role, then the role remains enabled by virtue of the role to which it has been granted. Restriction on the ALL Clause You cannot use this clause to enable roles with

passwords that have been granted directly to you.

Specify NONE to disable all roles for the current session, including the DEFAULT role.

La désactivation d'un rôle prive l'utilisateur des privilèges et rôles associés sauf si ce rôle est attribué par défaut (le mot de passe n'est de plus pas nécessaire)

16


Informations sur les rôlesLes vues suivantes contiennent des informations sur les rôles : dba_roles, user_role_privs, dba_role_privs,role_role_privs, role_sys_privs, role_tab_privs,session_roles

Ex : liste des rôles affectés à un role ou un utilisateur.SELECT * FROM sys.dba_role_privs WHERE grantee = 'RL_ADMIN_SECU' ;GRANTEE GRANTED_ROLE ADM DEF DefaultRL_ADMIN_SECU RL_CONNECT NO YES

Ex : liste des rôles actifs pour la sessionSELECT * FROM session_roles;ROLEMONITORERDBAEXP_FULL_DATABASEIMP_FULL_DATABASERL_ADMIN_SECU

17

Administration de bases de données avec Oracle 11G Partie 1 : Administration logique 4) Les profiles

Un profile est un concept Oracle qui permet à l'administrateur d'une base de contrôler la consommation des ressources systèmes et des mots de passes

Il existe un profile par défaut appelé DEFAULT. Il est par défaut affecté à un utilisateur lors de sa création

Les limites du profile DEFAULT sont positionnées à UNLIMITED

Le profile DEFAULT ne peut être supprimé. Les limites de ce profile peuvent par contre être modifiées

Activation et contrôle des limites :Dans le fichier init.ora positionner :RESOURCE_LIMIT = TRUEDynamiquement : ALTER SYSTEM SET resource_limit= true;

18


Création d'un profile (Privilège requis CREATE PROFILE)Syntaxe sans la partie passwordCREATE PROFILE profile LIMIT[ SESSIONS_PER_USER { integer | UNLIMITED | DEFAULT} ][ CPU_PER_SESSION { integer | UNLIMITED | DEFAULT } ][ CPU_PER_CALL { integer | UNLIMITED | DEFAULT } ][ CONNECT_TIME { integer | UNLIMITED | DEFAULT } ][ IDLE_TIME { integer | UNLIMITED | DEFAULT } ][LOGICAL_READS_PER_SESSION {integer | UNLIMITED|DEFAULT}][LOGICAL_READS_PER_CALL {integer | UNLIMITED|DEFAULT}][ COMPOSITE_LIMIT { integer | UNLIMITED | DEFAULT } ][PRIVATE_SGA {integer [K | M] | UNLIMITED | DEFAULT}];

Session_per_user : Nombre maximum de sessions par utilisateurLogical_read_per_session : Nbre de blocs de données à lire pour une sessioncpu_per_session : temps CPU max par session en % de sécondescpu_per_call : temps CPU pour un appel (en cas de parse, execute ou fetch) en % de secondesconnect_time : temps écoulé maximum (en minutes)idle_time : temps maximum d'inactivité.private_sga : taille privée de la SGA allouée à un utilisateurunlimited : limite de la ressource illimitéedefault : prend la limite par défaut de la ressource

19


Exemple de création d'un profileCREATE PROFILE pf_manager LIMITsessions_per_user 2cpu_per_session unlimitedcpu_per_call 1000logical_reads_per_session unlimitedlogical_reads_per_call 100idle_time 30connect_time 480;

Assignation d'un profile à un utilisateurA la création : CREATE USER frank IDENTIFIED BY zappa PROFILE PF_MANAGER;A la modification d'un utilisateur : ALTER USER frank PROFILE pf_guitariste;

Suppression d'un profileEn cas de suppression d'un profile existant affecté à un utilisateur, ce dernier se verra automatiquement attribué le profile DEFAULT. Le profile DEFAULT ne peut être supprimé.Privilège requis : DROP PROFILESyntaxe : DROP PROFILE nom_profile [CASCADE]CASCADE : retire le profile aux utilisateurs l'ayant puis, suppression du profile

Exemple : DROP PROFILE pf_secretaire CASCADE ;

20


Visualisation des informations des profilesExemple 1 : Liste de tous les profilesSELECT profile, resource_name, limit FROM dba_profiles ORDER BY profile;PROFILE RESOURCE_NAME LIMITDEFAULT COMPOSITE_LIMIT UNLIMITED...DEFAULT CPU_PER_SESSION 600PF_AGENT COMPOSITE_LIMIT 20000...PF_AGENT SESSIONS_PER_USER 2PF_SECRETAIRE LOGICAL_READS_PER_CALL 100

Exemple 2 : Liste des coûts (poids) des ressources pour la session couranteSELECT resource_name, limit FROM resource_costRESOURCE_NAME UNIT_COSTCPU_PER_SESSION 100LOGICAL_READS_PER_SESSION 2CONNECT_TIME 0

Exemple 3 : Liste des limites des ressources de l'utilisateur courantSELECT resource_name, limit FROM user_resource_limitsRESOURCE_NAME LIMITSESSIONS_PER_USER UNLIMITEDCPU_PER_SESSION 600

21

Administration de bases de données avec Oracle 11G Partie 1 : Administration logique 5) Les utilisateurs

Création d'un utilisateur : Lors de la création d'un utilisateur, il est possible de lui affecter : un mot de passe, un tablespace par défaut, un tablespace temporaire, un profile (explicite ou implicite), des quotas sur les tablespaces.

CREATE USER user IDENTIFIED { BY password | EXTERNALLY| GLOBALLY AS‘nom_externe’ } [ DEFAULT TABLESPACE tablespace ] [ TEMPORARY TABLESPACEtablespace ] [ QU0TA { integer [ K | M ] | UNLIMITED } ON tablespace ] ...[ PROFILE profile ] [PASSWORD EXPIRE] [ACCOUNT {LOCK | UNLOCK}]

Note : Externally : utilisateur authentifié par l'OS globally as : accès autorisé par l’annuaire LDAP

Ex : CREATE USER IUT042 IDENTIFIED BY alicecooper DEFAULT TABLESPACE users

QUOTA 2 M ON system QUOTA 2 M ON users;

le tablespace temporaire par défaut est SYSTEM, le tablespace par défaut est SYSTEM Les quotas sur les tablespaces sont obligatoires (ou UNLIMITED TABLESPACE ) le privilège CREATE USER est requis.

22


Modification d'un utilisateur : Lors de la modification d'un utilisateur, il est possible de lui affecter : un mot de passe, un tablespace par défaut, un tablespace temporaire, un profile (explicite ou implicite), des quotas sur les tablespaces et un rôle par défaut (parmi les rôles attribués par la commande grant). Exemples :

ALTER USER etu1_21 IDENTIFIED BY md2000p DEFAULT TABLESPACE usersQUOTA UNLIMITED ON users QUOTA 1M ON system; ALTER USER etu1_21 DEFAULT ROLE role_etu1;

Seule la commande alter user permet de décider quels rôles ne seront pas des rôles par défaut. Ici, seul le rôle role_etu1 est par défaut parmi les rôles affectés par la commande grant.

En définissant un rôle par défaut, on rend les autres rôle affectés par la commande grant en rôle classique (soumis aux éventuelles désactivations et aux demandes de mot de passe). On peut utiliser aussi faire le contraire. Exemple : ALTER USER etu1_21 DEFAULT ROLE all except role_etu8;

23


Suppression d'un utilisateur : La suppression d'un utilisateur entraîne la suppression des objets de son schéma (tables, vues, séquences, synonymes, indexes ... ) . Le privilège drop user est requis.

Exemples : DROP USER ETU1_33; -- suppression d'un utilisateur lié à un schéma videDROP USER ETU1_33 CASCADE;CASCADE : supprime les objets du schéma de l'utilisateur et les contraintes d'intégrité de référence (et vide la corbeille).

Rq : les rôles créés par l'utilisateur ne sont pas supprimés.

Suppression d'une session : En cas de problèmes avec une session (interblocage, consommation excessive de ressources, ...), sa suppression peut être décidée. cette suppression entraîne :

l'annulation de la transaction concernée, la libération des verrous et des ressources consommées la commande Alter System Kill Session ... supprime une session.select sid, serial#, username From v$session ;sid Serial# Username13 8 etu1_3014 11 etu1_35

ALTER SYSTEM KILL SESSION '13, 8';

24


Affectation de droits à un UtilisateurExemple 1 : Affectation de droits systèmesGRANT create tablespace, create user TO ETU_030;Exemple 2 : Affectation d'un rôle à un utilisateurGRANT role_iut01 TO ETU_110;Exemple 3 : Affectation d'un privilège objet à un utilisateurGRANT SELECT, UPDATE (nom,prenom) ON coureur TO IUT024;Exemple 4 : Affectation de privilèges à tous les utilisateursGRANT drop any table TO PUBLIC ; -- NOTE !!! Attention danger

Informations sur les utilisateursQuelques vues sur les utilisateursuser_users, all_users, dba_users,user_ts_quotas, dba_ts_quotas

Exemple 1: informations concernant l'utilisateur actuelSELECT username,user_id,default_tablespace,created FROM user_users;username user_id default_tablespace createdETU1_023 23 USER_DATA TEMPORARY_DATA 07/05/96

25


Informations sur les utilisateursExemple 2 : informations sur tous les utilisateursSELECT * FROM all_users;USERNAME USER_ID CREATEDSYSTEM 5 13/04/05ETU2_8 1518 07/09/12ERIC 64 05/12/08...

select USERNAME,USER_ID, ACCOUNT_STATUS, lock_date, EXPIRY_DATE from dba_users;USERNAME USER_ID ACCOUNT_STATUS LOCK_DATE EXPIRY_DATESYS 0 OPEN 10/10/19 SYSTEM 5 OPEN 10/10/19 ETU000 52 OPEN 14/11/19 COPIE_TDF 53 OPEN 02/01/20 ERIC 50 OPEN 10/10/19 APEX_PUBLIC_USER 45 LOCKED 29/05/14 25/11/14 FLOWS_FILES 44 LOCKED 29/05/14 25/11/14 OUTLN 9 EXPIRED & LOCKED 13/04/19 13/04/19...

Pour débloquer un compte verrouillé on tapeAlter user <nom compte> account unlock;

Pour débloquer un compte expiré on tapeAlter user <nom compte> identified by <nouveau mot de passe>;

26

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 1) Généralités

Comme pour les versions précédentes, on trouve les versionsEnterprise Edition - La gamme pour les grosses applications critiques de l’entreprise, intégrant des options supplémentaires telles que le partitionnement des tables.Standard Edition - La gamme destinée à des serveurs possédant 4 processeurs et ne proposant que l’option RAC/ASM.Standard Edition ONE - la gamme limitée aux serveurs biprocesseurs, sans option.Personal Edition - La gamme pour l’utilisateur indépendant (développeur, consultant, …), elle utilise un noyau Enterprise Edition.Express Edition : version d'entrée de gamme simple à installer et gratuite

Trois outils sont présents pour administrer une base de données OracleSQL*Plus (sqlplus), interface d’accès à la base de données en mode commandeiSQL*Plus, outil Internet d’accès à une base de données Oracle, permettant d’écrire des requêtes SQL (d’une façon plus ou moins graphique)Oracle Enterprise Manager (OEM), appelé Grid Control ou Database Control.➢ Database control est créé à la création d’une base oracle et ne permet d’administrer

graphiquement que cette base de données.➢ Grid control est un outil qui permet d’administrer une ferme de bases de données

(oracle ou non oracle).

27

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 1) Généralités

La nouvelle fonctionnalité Automatic Storage Management (ASM) permet à la base de données de gérer directement les disques bruts, elle élimine le besoin pour un gestionnaire de fichiers de gérer à la fois des fichiers de données et des fichiers de journaux.

L’ASM répartit automatiquement toutes les données de bases de données entre tous les disques, délivrant le débit le plus élevé sans aucun coût de gestion.

Au fur et à mesure de l’ajout et de l’abandon de disques, l’ASM actualise automatiquement la répartition des données.

Un serveur Oracle comporte 2 éléments, l'instance et la base de données

28

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 2) la base de données 2-1) Structure physique

La base de données est l’ensemble des fichiers qui permettent de gérer les données de la base.

Une base de données possède une structure physique constituée de : fichiers binaires de contrôle, contenant les informations sur tous les autres fichiers de la base (nom,emplacement, taille). Fichiers journaux (Redo Log), contenant l’activité des sessions connectées à la base. Ce sont des journaux de transactions de la base. Ils sont organisés en groupe possédant le même nombre de membres. D’un ou plusieurs fichiers de données qui contiennent les données des tables de la base (non lisible avec un éditeur de textes). Ils sont logiquement divisés en tablespace (espace de disque logique).

Une base de donnée porte un nom défini lors de la création (DB_NAME)

29

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 2) la base de données 2-1) Structure physique : Les fichiers de contrôle

Un fichier de contrôle contient des informations de contrôle d'une base de données Oracle

Le contenu de ce fichier est : nom de la base date (‘timestamp’) de création de la base noms et localisation des fichiers de données et redo log numéro de séquence du fichier redo log courant informations sur les CHECKPOINT

Ces fichiers sont lus au démarrage de la base de données

Une base de données contient au moins un fichier de contrôle

Oracle recommande de mettre les fichiers de contrôle en miroir et de les localiser sur des disques différents. Attention aux performances !

30

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 2) la base de données 2-1) Structure physique : 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.

En mode ARCHIVELOG, les redo logs sont archivés afin de garder une trace de toutes les modifications apportées.

Les fichiers de redo logs é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).

Plus le fichier est gros, moins vite il sera archivé et inversement. Il faut limiter le nombre d'archives écrites puisque les accès disques sont coûteux en performance.

Pour voir l'état des archives logs :ARCHIVE LOG LIST;SELECT name, log_mode FROM v$database;SELECT archiver FROM v$instance;

31

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 2) la base de données 2-1) Structure physique : Les fichiers de données

Ils contiennent toutes les données relatives à une base de données (dictionnaire Oracle,Tables, index, clusters, rollback segments, segment temporaires)

L'unité de découpage est le bloc (2K, 4K, 8K, 16K, 32K) selon l'OS

Ces fichiers sont de taille fixe et optionnellement variables depuis la 7.2 Ils appartiennent à une et une seule base

Une base contient au moins un fichier de données

Ils sont souvent volumineux

32

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 2) la base de données 2-2) Structure logique

Une base de données possède une structure logique constituée de tablespaces, de segments, d'extents et de blocs de données.

33

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 2) la base de données 2-2) Structure logique : les tablespaces

Ce sont des espaces disques réservés au stockage des données. Chaque tablespace est constitué d'au moins un fichier de données.

Une base peut être décomposée en tablespaces : partitions logiques contenant un ou plusieurs fichiers. Un fichier appartient à 1 et 1 seul tablespace. Un tablespace n'apaprtient qu'à une seule BDD. Il peut s'étendre soit par ajout (on-line) d'un fichier, soit par auto-extension du fichier du tablespace. Chaque utilisateur possède

Un tablespace par défaut qui stockera ses objets (sauf autre choix délibéré) Un tablespace temporaire

Un tablespace permet de gérer efficacement La répartition des E/S Les quotas utilisateurs De fermer l'accès à certaines ressources (OFFLINE)

Les relations entre les tablespaces, les fichiers de données, les segments et les extents utilisés et libres peuvent être vus avec les dictionnaires de données.

34


Une base de données Oracle 11G possède au minimum 4 tablespaces. SYSTEM (dictionnaire de données - Tablespace SYSTEM) SYSAUX (system auxiliaire composants oracle - Tablespace SYSAUX) UNDOTBS (segments d’annulations - Tablespace UNDO) TEMP (segments temporaires - Tablespace temporaire)

L'espace occupé par un objet dans le tablespace est appelé segment (segment de table, d'index, d'annulation pour les transactions, temporaire pour les tris)

Un segment est composé d'extents. Un extent est composé de blocs contigus dont la taille dépend de l'OS.

Un bloc de données (bloc oracle) est la plus petite unité logique. Elle est proportionnelle aux blocs définis au niveau du système d'exploitation. Un bloc correspond donc à un nombre spécifique d'octets.

Quand un tablespace est créé avec plus d’un fichier, une ligne est ajoutée à DBA_TABLESPACES.

35


Pour chaque fichier dans une base de donnée, une ligne est ajoutée dans DBA_DATA_FILES.

Chaque segment est visible dans DBA_SEGMENTS et ses extents dans DBA_EXTENTS.

36


Pour créer un tablespace :CREATE TABLESPACE montbs4DATAFILE 'd:\ORA_DATA04.dbf' size 1MDEFAULT STORAGE (initial 32K next 32K MAXEXTENTS 20 PCTINCREASE 0);

Paramètres de création :DATAFILE Liste des fichiers de donnéesMINIMUM 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) pour les TBS gérés dans le dictionnaire: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 minimum d'extents alloués lors de la création du segment.MAXEXTENTS Est le nombre maximum d'extents alloués lors de la vie de l'objet. PCTINCREASE Pourcentage de croissance de la taille de l'extent,

37

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 2) la base de données 2-3) Les objets

Une base de données peut contenir desTABLES (données de la BDD)INDEX (adresse d'une physique d'une table ou d'un cluster permettant l'accès direct aux informations) VIEW (une requête) SYNONYM (un autre nom pour un objet) SEQUENCES (générateur de nombres entiers) CLUSTER (groupement de tables ayant des colonnes communes) PROCEDURE (programme stocké dans la BDD) FUNCTION (procédure retournant une valeur) PACKAGE (ensemble de fonctions et procédures) TRIGGER (procédure associée à un événement)

38

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 3) les instances

Une instance est l'ensemble des processus d'arrière plan et des zones mémoires allouées pour permettre l'exploitation de la base de données.

L'instance est en fait la composition de 2 sous ensembles :Une zone mémoire : La SGA (System Global Area). Elle va servir à stocker les données issues des fichiers de données sur le disque dur. Des processus d'arrière plan et serveurs. Les premiers vont servir à gérer les transferts de données entre la mémoire et le disque dur. Les seconds sont chargés de traiter les requêtes des utilisateurs.

Une instance ne peut ouvrir qu'une base à la fois. Avec l'option RAC (Real Application Cluster), une BDD peut être ouverte par plusieurs instances ; option intéressante pour la haute disponibilité.

Une instance possède un nom appelé SID (info, XE …) attribué lors de la création de la base. En général l'instance et la BDD ont le même nom (même si on peut changer le nom de l'instance).

Exemple :

39

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 3) les instances

Un fichier de paramètres est utilisé par l'instance lors de son démarrage pour se configurer et faire le lien avec la BDD.

Création de l'instance : il faut éditer le fichier init.ora et définir la variable d'environnement ORACLE_SID avec le nom de l'instance. On peut aussi vérifier les variables d'environnements ORACLE_HOME et ORACLE_BASE

Démarrer l'instance : Seul un sysdba peut se connecter pour démarrer l'instance. Le démarrage d'une base de données complète se déroulera en plusieurs étapes

NOMOUNT : Cette étape va consister à lire le fichier init.ora, à démarrer l'instance, allouer la mémoire, et démarrer les processus d'arrière plan. MOUNT : Cette étape va consister à ouvrir le ou les fichiers CONTROL_FILES afin de mettre en mémoire les informations contenues. Durant cette étape les fichiers de données ne sont pas accessible car ils n'ont pas encore été ouverts. OPEN : Cette étape va consister à ouvrir tous les fichiers de données enregistrés dans les fichiers CONTROL_FILES. Puis une fois tous les fichiers ouverts et disponible, à ouvrir complètement la base de données aux utilisateurs.

40

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 4) le dictionnaire des données (DD)

C’est un ensemble de tables et de vues qui donne des informations sur le contenu d’une base de données. Il contient :

Les structures de stockage Les utilisateurs et leurs droits Les objets (tables, vues, index, procédures, fonctions, …) L'audit sur une base

Il appartient à l’utilisateur SYS et est stocké dans le tablespace SYSTEM

Suivant leurs droits, certains utilisateurs ont accès en lectures à quelques une de ces « tables »

Seul SYS peut modifier directement ces tables mais cela nécessite d'excellentes connaissances sur l'arborescence du DD Les vues statiques sont caractérisées par leur préfixe :

USER_* : Informations sur les objets qui appartiennent à l’utilisateur ALL_* : Information sur les objets auxquels l’utilisateur a accès (les siens et ceux sur lesquels il a reçu des droits) DBA_* : Information sur tous les objets de la base.

41


La vue DICTIONARY (synonyme DICT) donne la liste complète des tables, vues statiques et synonymes du DD.

Les colonnes des vues du dictionnaire sont visibles grâce à DICT_COLUMNS.

42


La vue DBA_CATALOG liste les objets accessibles ) chaque utilisateur

Les tables et vues dynamiques de performances sont basées sur des informations en mémoire ou extraites du fichier de contrôle. Elles donnent des informations sur le fonctionnement de la base de données dont les performances. Elles sont remises à zéro si on arrête la base de données. Elles sont Préfixées par « V$ ». Elles sont accessibles uniquement aux dba.

On peut aussi accéder à des vues pour les bases en cluster. Pour surveiller les performances d’instances placées sur des serveurs différents, il est important de disposer des vues identiques aux vues dynamiques mais permettant d’identifier l’instance surveillée. Elles sont identifiées avec le préfixe « GV$ »

43


v$fixed_table qui contient le nom de toutes les vues dynamiques 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$option Contient la liste des options installées sur le serveur Oracle. v$process Contient des informations sur les processus 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$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$logfile Contient des informations sur les fichiers redo_log.

44

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 5) Connexion d'un utilisateur

2 utilisateurs sont nécessaires au fonctionnement d'oracleSYSTEM : l'administrateur créant les différents schémas et leurs objets. Il attribue aussi les privilèges SYS : le super administrateur, réalisant les tâches d'administration lourdes (démarrage, arrêt, paramétrage, restauration. Il se connecte dans un mode SYSDBA. Il existe aussi une mode SYSOPER ne permettant pas les créations de base et restaurations partielles.

45


Oracle est une application client-seveurLe processus client (sql plus, sql developer, isqlplus) se connecte au processus serveur Le processus serveur écoute sur un port dédié (80, 1521 ..)

La PGA (Program Global Area) sur le processus serveur est une zone mémoire contenant

Une zone de tri (allouée dynamiquement lors d’un tri)Des informations sur la sessionDes informations sur le traitement des requêtes de la sessionLes variables de session

La SGA (System Global Area) est une zone mémoire partagée par les différents processus de l'instance.

Au démarrage, l'instance lit un fichier de paramètres permettant d'allouer la mémoire et de trouver le fichier de contrôle de la base On distingue les paramètres statiques et les paramètres dynamiques

modifiables « à chaud »

46


La commande de démarrage est 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 (sauvegardes, restaurations, configuration) mais ne permet aucun accès à la base.OPEN Permet aux utilisateurs d'accéder à la base.EXCLUSIVE Autorise seulement la session courante à 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.

Arrêt de l'instance : la commande est SHUTDOWN [paramètres]NORMAL Les nouvelles connexions ne sont pas permises, le serveur oracle attend la fin de toutes les connexion. C'est l'argument par défaut.TRANSACTIONNAL Plus de connexion possible, les transactions en cours s'exécutent jusqu'à leur terme et aucune nouvelle transaction n'est acceptée.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. Mode le plus brutal

47


Pour démarrer une instance, le serveur Oracle doit lire le fichier de paramètres d'initialisation SPFILE ou PFILE

Le SPFILE (Fichier de paramètres persistant) est un fichier binaire recherché automatiquement au démarrage de l'instance. Son nom par défaut est spfile<SID>.ora. Le fichier SPFILE est recommandé du fait que les paramètres d'initialisation sont gérés dynamiquement dans un fichier persistant côté serveur. il est modifié par le serveur Oracle.Le PFILE (Fichier de paramètres statique) est un fichier texte recherché automatiquement au démarrage de l'instance en l'absence du fichier SPFILE. Il est modifié manuellement.. Son nom par défaut est : init<SID>.ora (dans "$ORACLE_HOME/dbs" ). Les modifications y sont apportés manuellement et ne prennent effet qu'après démarrage suivant de l'instant.

Vous pouvez créer un fichier SPFILE à partir d'un fichier PFILE via la commande suivante (instance démarrée ou non) :

CREATE SPFILE = '$ORACLE_HOME/dbs/spfileDBA01.ora'FROM PFILE = '$ORACLE_HOME/dbs/initDBA01.ora'

Démarrage de l'instance :STARTUP [NOMOUNT | MOUNT [<nom_base>] | OPEN [<nom_base>]] [RESTRICT] [PFILE=<nom_fichier>] ;Exemple : startup pfile='D:\cours_admin10G\inittahiti02.ora'

48

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 5) Connexion d'un utilisateur : 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.

49

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 5) Connexion d'un utilisateur : init.ora

Exemple de fichier init.ora (Express Edition). .open_cursors=300db_name=XEcontrol_files=("C:\oraclexe\app\oracle\oradata\XE\control.dbf")job_queue_processes=4compatible=11.2.0.0.0diagnostic_dest=C:\oraclexe\app\oracle\.memory_target=1024Msessions=20 audit_file_dest=C:\oraclexe\app\oracle\admin\XE\adumpremote_login_passwordfile=EXCLUSIVEdispatchers="(PROTOCOL=TCP) (SERVICE=XEXDB)"shared_servers=4 undo_management=AUTOundo_tablespace=UNDOTBS1DB_RECOVERY_FILE_DEST_SIZE = 10GDB_RECOVERY_FILE_DEST=C:\oraclexe\app\oracle\fast_recovery_area

50

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 5) Connexion d'un utilisateur : les sessions

Certains paramètres sont modifiables grâce aux commandes ALTER SESSION ou ALTER SYSTEM. Par exemple : ALTER session SET nls_date_format = 'DD/MM/RRRR HH24:MI:SS';ALTER SYSTEM SET NLS_DATE_FORMAT='DD/MM/YYYY' scope=both;SELECT * FROM NLS_INSTANCE_PARAMETERS;

Pour trouver les paramètres modifiés: SELECT name, isses_modifiable, issys_modifiable, ismodified FROM v$system_parameter WHERE ismodified!='FALSE';

51

Administration de bases de données avec Oracle 11G Partie 2 : Administration physique 6) Sauvegarde et restauration

La principale responsabilité du DBA est de prendre les mesures nécessaires pour assurer la sécurité et la disponibilité des données.

Cette sécurité est assurée par : La mise en oeuvre d’une protection des fichiers sensibles de la base

Fichiers de contrôleFichiers de Redo Log

La mise en place d’une stratégie de sauvegarde/restauration Adaptée aux contraintes de l’entrepriseEt qui aura été complètement testée et documentée

Le DBA doit faire des sauvegardes régulières de la base de données physique et logique

administration de bases de données avec oracle...

Documents