administration pour développeursmyuuu.fr/cours/mbds/dba/dbapourdeveloppeur-oracle10g.pdf ·...

Administration pour développeurs

Oracle 10g

Introduction

Architecture Oracle

Stockage physique et logique

Concurrence d’accès

Les outils

Utilisateurs et rôles

1 Introduction

n  Pré requis à l’administration:

n  Langage SQL et PL/SQL

n  Avoir « pratiqué » Oracle

1

Introduction

Architecture Oracle



Les outils


Produits et gamme

n  Produits n  Oracle Database n  Oracle Application Server n  Oracle Developer Suite n  Oracle Enterprise Manager Grid control

n  Gamme n  Edition Personnelle n  Edition Standard One n  Edition Standard n  Edition Entreprise

n  Source de téléchargement n  http://www.oracle.com/technology/index.html

1

Introduction

Architecture Oracle



Les outils


Principales nouveautés

n  Simplification n  Installation n  Mise à niveau n  « Easy connect » n  Conseillers n  …

n  Automatisation n  Automatic Storage

management (ASM) n  Automatic Workload

Repository n  Autodiagnostic de la base n  Réglage de la mémoire

partagée n  Collection des statistiques n  …

n  Evolution de la structure n  Tablespace SYSAUX n  Tablespace bigfile n  Groupe de tablespace

temporaires n  Compactage des lignes dans

un segment n  …

n  Evolution des outils n  Data pump n  Scheduler n  Oracle Entreprise Manager

Database Control (DC) n  …

1

Introduction

Architecture Oracle



Les outils


Inst

ance

Mémoire

Processus

Paramètres d’initialisation

Base

de

donn

ées

---- --- --- ---

--- --- ---

Data

Contrôle

Journalisation

2

2 Architecture Oracle

Introduction

Architecture Oracle



Les outils


La base de données

n  La base de donnée possède un nom défini par le paramètre DB_NAME = ma_base

n  Le nom complet est préfixé par le nom de domaine défini par le paramètre DB_DOMAIN = domaine.fr

è Ma_base.domaine.fr

---- --- --- ---

--- --- ---

Data

Contrôle

Journalisation


2

Introduction

Architecture Oracle



Les outils


Fichier de contrôle

n  Contenu binaire n  Maintenu à jour par Oracle n  Décrit la base n  Contient

n  Le nom de la base n  La date et l’heure de la création n  L’emplacement des autres fichiers de la base n  Le numéro de séquence en cours n  …

è Présence indispensable ; il est conseillé de maintenir plusieurs copies (multiplexage)

---- --- --- ---

--- --- ---

Data

Contrôle

Journalisation

2

Introduction

Architecture Oracle



Les outils


Fichiers de journalisation

n  Enregistrent les modifications n  Organisés en groupes (2 au minimum)

n  Le groupe est régulièrement réutilisé n  Un groupe peut être multiplexé (notion de membre ; 1 au minimum !)

n  Un « tour de roue » = switch n  Un groupe inactif contient les données reproduites dans la base n  Utilisés lors de la « récupération » d’une instance ou d’un

média

---- --- --- ---

--- --- ---

Data

Contrôle

Journalisation

2

Introduction

Architecture Oracle



Les outils


Fichier de données

n  Contiennent les données des segments n  Regroupés en « tablespace »

n  Bigfile : jusqu’à 32 To mais n’utilise qu’1 seul fichier n  Smallfile : tablespace traditionnel de 1 ou plusieurs fichiers

n  2 tablespaces au strict minimum n  SysAux : apparu en 10g ; s’appuie sur au moins 1 fichier n  System : s’appuie sur au moins 1 fichier

¨  Ne devraient contenir aucune donnée applicative

---- --- --- ---

--- --- ---

Data

Contrôle

Journalisation

2

Introduction

Architecture Oracle



Les outils


Organisation du stockage

---- --- --- ---

--- --- ---

Data

Contrôle

Journalisation

Bloc : Allocation élémentaire d’espace Taille {4, 8, 16, 32 Ko}

Extent : Ensemble de bloc contigu d’un même segment

Segment : Nom générique pour un objet ‘conteneur’ de données Ex : Une table, un index

Segment A Extent 1

Segment A Extent 2

Segment C Extent 1

Segment B Extent 1

Segment D Extent 1

Segment B Extent 2

Espace non alloué

Tablespace Data01

Data01.dbf Data02.dbf

2

Introduction

Architecture Oracle



Les outils


Système de stockage

n  Utilisation du système de fichiers de l’OS

n  Utilisation de partition « raw device »

n  Utilisation de ASM : n  Nouveauté 10g n  Gestionnaire de volume dédié à Oracle n  Utilise une instance ASM n  Nécessite le patch 3555863 sous Windows

2

Introduction

Architecture Oracle



Les outils


Notion de schéma

n  Désigne l’ensemble des objets d’un utilisateur

n  Tout utilisateur est potentiellement associé à un schéma è il n’existe pas d’ordre pour créer un schéma

n  Permet une organisation logique de l’ensemble des objets créés dans Oracle

Nb : n  Les objets appartiennent à un schéma n  Les objets sont définis dans le dictionnaire n  Les segments stockent leur données dans des tablespaces

2

Introduction

Architecture Oracle



Les outils


Règles de nommage

n  30 caractères au max n  Doit commencer par une lettre n  Peut utiliser {A-Z} {a-z} {0-9} {_ $ #} n  Ne doit pas être un mot réservé

n  Définir une convention de nommage

n  Idée : commencer le nom par un code suivi de sa désignation ¨  Ex :

T001_Societes T002_Contacts V001_Meilleures_ventes

¨  Utiliser des synonymes T001_Societes -> T001 T002_Contacts -> T002 V001_Meilleures_ventes -> V001

2

Introduction

Architecture Oracle



Les outils


L’instance

Inst

ance

Mémoire

Processus

SGA PGA

2

Introduction

Architecture Oracle



Les outils


La PGA

n  Program Global Area n  Mémoire non partagée allouée aux différents processus n  Contient :

n  SQL work area (zone de travail) n  Informations de session n  Variables de session n  Informations sur le traitement des requêtes

n  Est intégrée à la SGA dans un contexte de serveur partagé n  Depuis la 9i gérée automatiquement et globalement via le

paramètre : PGA_AGGREGATE_TARGET è  Paramètres obsolètes :

è  Sort_area_size, Hash_area_size, Bitmap_merge_area …

Mémoire

SGA PGA

2

Introduction

Architecture Oracle



Les outils


La SGA

n  System Global Area n  Mémoire partagée n  Eventuellement redimensionnable à chaud (depuis la 9i) n  Eventuellement gérée automatiquement (depuis la 10g) n  Contient 6 zones principales :

n  Shared Pool ¨  Library Cache ¨  Dictionary Cache

n  Database Buffer Cache n  Redo Log Buffer n  Java Pool n  Large Pool n  Streams Pool

Mémoire

SGA PGA

2

Introduction

Architecture Oracle



Les outils


SGA – Shared Pool

n  Principalement composée de

¨  Library Cache : ¨  Le code ascii SQL et PL-SQL ¨  La version ‘parsée’ ¨  Le version exécutable (choisie par Oracle)

¨  Le Dictionary Cache ¨  Description des objects (tables, utilisateurs, …) ¨  Description des droits

n  Taille : ¨  Définie par le paramètre : Shared_pool_size ¨  Ordre de grandeur < Go

Mémoire

SGA PGA

(A)

2

Introduction

Architecture Oracle



Les outils


SGA – Database buffer Cache

n  Contient les blocs de données récemment utilisés des : n  Tables n  Index n  Annulation

n  Montés du disque grâce aux processus serveurs

n  Paramètres n  Db_cache_size : cache pour les blocs de taille standard n  Db_Keep_cache_size : cache de données pérennes n  Db_Recycle_cache_size : cache de données éphémères

Mémoire

SGA PGA

(A)

2

Introduction

Architecture Oracle



Les outils


SGA – Redo Log Buffer

n  Espace de journalisation des modifications ¨  Contient :

n  L’image avant n  L’image après

¨  Est vidé sur disque (fichier de journalisation) très fréquemment via le processus LGWR

n  Est paramétré par : LOG_BUFFER

Mémoire

SGA PGA

(M) 2

Introduction

Architecture Oracle



Les outils


SGA – Autres pools

n  JAVA_POOL_SIZE (A)

n  Zone facultative n  24 Mo par défaut n  Utilisée avec la machine virtuelle Java

n  LARGE_POOL_SIZE (A)

n  Zone facultative n  Utilisée si Oracle est en mode « serveur partagé »

n  STREAMS_POOL_SIZE (M)

n  Zone facultative

Mémoire

SGA PGA

2

Introduction

Architecture Oracle



Les outils


Granule et gestion automatique

n  Granule = allocation unitaire de RAM pour la SGA n  Taille de la granule

n  Si taille de la SGA <= 128Mo è 4 Mo n  Sinon è 8 Mo / Windows

è 16 Mo / autres systèmes

n  Utilisation d’un nb entier de granules de manière à couvrir le besoin

n  Permet depuis la 9i de modifier l’allocation Ram sans arrêt de l’instance.

n  Gestion Dynamique = ASSM n  Nouveauté 10g : réparti la RAM en fonction des structures (A) n  Paramétrée par SGA_TARGET

Mémoire

SGA PGA

2

Introduction

Architecture Oracle



Les outils


Les processus d’arrière plan

n  1 processus = 1 rôle précis n  Généralement démarrés et arrêtés avec l’instance n  Il a une trentaine de processus possibles n  Si un processus est lancé en plusieurs exemplaires son nom se

termine par son N°

LGWR CKPT SMON ARCn CJQn

Processus d’arrière plan

DBWn PMON

2

Introduction

Architecture Oracle



Les outils


Processus DBW

n  Database Writer

n  ‘Descend’ blocs ‘dirty’ dans les fichiers de donnés n  Blocs dirty = blocs qui ont été modifiés en RAM

n  Peut y avoir jusqu’à 20 processus

n  Est notamment activé : n  S’il n’y a plus de blocs libres ou non-dirty en RAM n  Lors du ‘checkpoint’ n  A la fermeture propre de l’instance n  …



DBWn PMON

2

Introduction

Architecture Oracle



Les outils


Processus LGWR

n  Log Writer

n  Descend le Redo_log_Buffer dans le « fichier de journalisation » courant

n  Activé par : n  Commit n  Saturation de 1/3 du Redo_log_Buffer n  Préalablement à DBWn n  Toutes les 3s



DBWn PMON

2

Introduction

Architecture Oracle



Les outils


Processus CKPT

n  Check point

n  Inscrit un point de contrôle dans les entêtes des fichiers de données et de contrôle

n  Activé par : n  Basculement de fichier journal = Switch n  DBWn



DBWn PMON

2

Introduction

Architecture Oracle



Les outils


Processus SMON

n  System Monitor

n  Chargé de faire la « récupération » de l’instance et, dans le cas de tablespace gérés par le dictionnaire (historique), de libérer les segments temporaires et de réassembler l’espace libre contigu.

n  Activé n  Lors du démarrage n  Périodiquement pour la gestion des TS gérés par le dictionnaire



DBWn PMON

2

Introduction

Architecture Oracle



Les outils


Processus ARC

n  Archiver

n  Recopie le fichier journal quand il devient inactif

n  N’est présent que si Oracle est en mode ARCHIVELOG



DBWn PMON

2

Introduction

Architecture Oracle



Les outils


Processus CJQ

n  Job Queue

n  Lance les travaux programmés en démarrant un processus esclave (Jnnn)



DBWn PMON

2

Introduction

Architecture Oracle



Les outils


Processus PMON

n  Process Monitor

n  Chargé du nettoyage des processus utilisateurs plantés n  Rollback des transactions en cours n  Libération des ressources



DBWn PMON

2

Introduction

Architecture Oracle



Les outils


Processus Serveur

n  Ne sont pas des processus d’arrière plan !

n  Chargés de traiter les requêtes des utilisateurs et notamment de « monter » les données en RAM

n  Oracle en serveur dédié : n  1 Processus par connexion

n  Oracle en serveur partagé : n  1 Processus pour plusieurs connexion n  1 connexion peut utiliser n’importe lequel des processus partagés

2

Introduction

Architecture Oracle



Les outils


Le fichier de paramètres

n  Lu pour pouvoir démarrer l’instance

n  Décrit notamment où se situe le fichier contrôle

n  Stocke les différents paramètres

n  Format texte (statique) à init***.ora

n  Format binaire (dynamique) à spfile***.ora

(depuis 9i)


# exemple de fichier pfile Db_name = ma_base Instance_name = domaine.fr Control_files = ("d:\control01.ctl" ,"e:\control02.ctl") … Statistics_level = typical Compatible = 10.0.0 … Sga_target = 1G Pga_aggregate_target = 10M …

2

Introduction

Architecture Oracle



Les outils


Résumé des composants

Inst

ance


Base

de

donn

ées

Contrôle

---- --- --- ---

--- --- ---

Journalisation Data

Processus

LGWR CKPT SMON ARCn CJQn DBWn PMON

Mémoire

PGA SGA

2

Introduction

Architecture Oracle



Les outils


Oracle en action

Contrôle

----

Journalisation Data

Processus

LGWR CKPT SMON ARCn CJQn DBWn PMON

Mémoire

PGA SGA

Update T1 Set col1= ‘a’;

val avant ‘ab’ ; val après ‘a’ val avant ‘ab’ ; val après ‘a’ val avant ‘ab’ ; val après ‘a’

Operation complete

Commit;

Commit;

----- -----

2

Introduction

Architecture Oracle



Les outils



Introduction

Architecture Oracle



Les outils


Objets physiques : Définitions

n  Tablespace : ¨  Espace, sur disque, de stockage des données ¨  Composé d’un ou plusieurs fichiers (os)

n  Segment : ¨  Objet défini dans la base comme étant conteneur

¨  Table ¨  Index ¨  Rollback segment ¨  …

n  Extent : ¨  Ensemble de blocs physiquement contigus d’un segment donné

n  Blocs : ¨  Élément unitaire de gestion de l’espace (RAM ou disque) d’Oracle

3

Introduction

Architecture Oracle



Les outils


Objets logiques

n  Segments ¨  Tables

n  Relationnelles ¨  Partitionnées ¨  Organisées en cluster ¨  Organisées en Index

n  XML

¨  Index n  Btree n  Bitmap n  Partitionnés (voir : tables partitionnées)

¨  Autres segments n  Rollback segment n  Deferred segment n  LOBsegment n  …

n  Objets (non segment) ¨  Vues ¨  Synonymes ¨  Séquence ¨  Fonctions / procédures / déclencheurs / Packages ¨  …

3

Introduction

Architecture Oracle



Les outils


Organisation du stockage

Segment A Extent 1

Segment A Extent 2

Segment C Extent 1

Segment B Extent 1

Segment D Extent 1

Segment B Extent 2

Espace non alloué

Tablespace Data01

Data01.dbf Data02.dbf

A (Table)

D (Table)

C (Table) B

(Index)

3

Introduction

Architecture Oracle



Les outils


Tablespaces par défaut : SYSTEM et SYSAUX

n  Ne pas écrire dans les Tablespaces par défaut ¨  Il faut donc, soit :

n  préciser le TS lors de la création d’objet n  Imposer que l’administrateur désigne les différents types de TS

associés à chaque utilisateur

n  SYSTEM ¨  Contient le dictionnaire

n  SYSAUX ¨  Existe depuis la version 10 ¨  Est une extension de System

3

Introduction

Architecture Oracle



Les outils


Caractéristiques d’un TS

n  Type : n  Permanent : pour les objets permanents n  Temporaire : pour les objets temporaires n  Rollback : pour stocker les rollback segments (RBS) (on ne peut y créer

que des RBS)

n  Mode de gestion des extensions n  Par le dictionnaire n  Localement

n  Conseil : ¨  Créer des TS gérés localement avec gestion automatique des

extensions CREATE Tablespace nom Datafile

…

Extent management local autoallocate

…

3

Introduction

Architecture Oracle



Les outils


DC : Espace disque logique

3

Introduction

Architecture Oracle



Les outils


Création de table

n  Table permanente Create table nom

(col type [null] [, col type [null]])

[Tablespace ts]

n  Table temporaire Create global temporary table nom

(col type [null] [, col type [null]]) On commit [delete|preserve] rows

[Tablespace ts]

¨  Rq : la structure de la table est permanente, les lignes sont temporaires soit à la transaction (delete) soit à la session (preserve)

3

Introduction

Architecture Oracle



Les outils


Création de tables optimisées

Objectif Description

� Optimiser les temps de réponse en écourtant les « table scan ». � Faciliter la gestion de l’espace lors des historisations

La table est découpée en plusieurs segments physiques distincts. Les index peuvent suivre le découpage et/ou non.

Partitionnées

Optimiser les temps de réponse lorsque les 2 tables sont intimement associées

2 tables se trouvent écrites dans le même bloc. Nécessite la création d’un « cluster ».

Organisées en cluster

Optimiser les temps de réponse lors des tri.

Les lignes sont écrites dans les blocs en respectant l’ordre

Organisées en Index

3

Introduction

Architecture Oracle



Les outils


Gestion des index

n  Classification des index ¨  Index de structure

n  Sont associés à une contrainte d’unicité (index b-tree) n  Sont défini à la création de la base, ne sont pas remis en cause

¨  Index de performance n  Sont remis en cause au cours du temps

n  Type d’indexation ¨  Index b-tree

n  OLTP / OLAP n  Utiles si faible cardinalité et requêtes restrictives n  Sous types possibles :

¨  Normal ¨  À clé inversée

¨  Index bitmap n  OLAP n  Utiles si forte cardinalité

n  Portée d’un index ¨  Une colonne ¨  Plusieurs colonnes

3

Introduction

Architecture Oracle



Les outils


Index Btree

Create table alphabet (lettre char(1)); Insert into alphabet values(‘A’); Insert into alphabet values(‘Q’); …

A @ B @ C @

D @ E @ F @

G @ H @ I @

J @ K @ L @

M @ N @ O @

P @ Q @ R @

S @ T @ U @

V @ W @ X @

Y @ Z @

Create INDEX I_lettre on alphabet(lettre);

A @ D @ G @

J @ M @ P@

S @ V @ Y @

A @ J @ S @

A Q W

Z S X

E D C

R F V

T G B

Y H N

U J I

K O L

M P

Select lettre from alphabet where lettre = ‘Q’;

3

Introduction

Architecture Oracle



Les outils


Index Bitmap

13

Create bitmap INDEX I_type on factures(type); Create bitmap INDEX I_finies on factures(finies);

001,F,O 002,F,O 003,F,O … …

045,A,O 046,F,O 047,A,N … …

094,F,N 135,F,O 096,A,N

Select * from factures where type = ‘A’ or finies = ‘N’;

. 0

. 1

1 0

0 1

. 1

. 0 A 0

1 1

0 0

F 1 1

0

0

1

. 1

. 0

1 0

0 1

. 0

. 1 N 0

1 1

0 0

O 1 1

0

0

1

. 1 0 1 . 0 A 0 0 0 0 1

A ou N . 1 0 1 . 1 0 0 0 0 1

. 0 0 1 . 1 0 0 0 0 1 N

3

Introduction

Architecture Oracle



Les outils



Authentification

Définition

Profil

Privilèges

Schéma

Rôles

Introduction

Architecture Oracle



Les outils


Modes d’authentification

n  Identification Oracle ¨  SQL> connect nom/pwd

n  Identification par l’OS ¨  Le paramètre REMOTE_OS_AUTHENT doit être à TRUE

¨  SQL> connect /

¨  Mise en correspondance du nom de session OS et du nom de connexion Oracle (préfixé par OPS$)

¨  Le préfixe est géré par le paramètre OS_Authentifation_prefix

¨  Sous Windows le nom de l’annuaire fait partie du nom de l’utilisateur OPS$DOMAINE\UTILISATEUR OPS$MACHINE\UTILISATEUR

4

Introduction

Architecture Oracle



Les outils


Gestion des utilisateurs

n  Création CREATE USER nom IDENTIFIED [By pwd| externally] [DEFAULT TABLESPACE nom_ts] [TEMPORARY TABLESPACE nom_ts] [QUOTA [UNLIMITED|X [K|M] ] ON nom_ts] [PROFILE nom_p] [PASSWORD EXPIRE] [ACCOUNT [LOCK|UNLOCK];

n  Modification ALTER USER nom IDENTIFIED [By pwd| externally] [DEFAULT TABLESPACE nom_ts] [TEMPORARY TABLESPACE nom_ts] [QUOTA [UNLIMITED|X [K|M] ] ON nom_ts] [PROFILE nom_p] [PASSWORD EXPIRE] [ACCOUNT [LOCK|UNLOCK];

n  Suppression DROP USER nom [cascade];

4

Introduction

Architecture Oracle



Les outils


Définition de paramètres par défaut

n  Chaque utilisateur est (normalement) associé à un TS n  La création d’objet est implicitement faite dans ce TS n  La désignation du TS par défaut ne donne pas pour autant le droit

d’écrire

n  Le défaut, pour tout utilisateur (au cas où on prévoit d’oublier de préciser un TS par utilisateur)

Alter database default tablespace nom;

--vérification

Select property_name, property_value from database_properties Where property_name = ‘DEFAULT_PERMANENT_TABLESPACE’;

4

Introduction

Architecture Oracle



Les outils


Informations sur les utilisateurs

Dba_users

Username User_ID Password Account_status Lock_date Expiry_date Default_tablespace Temporary_tablespace Created Profile Initial_rsrc_consumer_group External_name

Dba_ts_quotas

Tablespace_name Username Bytes Max_bytes Blocks Max_blocks Dropped 4

Introduction

Architecture Oracle



Les outils


Les profils

n  Ensemble nommé de

¨  limitations de ressources n  Temps CPU n  Nb de lectures logiques n  Nb de sessions simultanées par utilisateur n  Temps d’inactivité n  Durée de la session n  Si configuration en serveur partagé : Qté de mémoire privée en SGA

¨  Paramètres de sécurité n  Seuil de verrouillage n  Délais de verrouillage n  Durée de vie des mots de passe n  Durée de vie de la période de grâce n  Historique des mots de passe n  Complexité

4

Introduction

Architecture Oracle



Les outils


Mise en place des profils

n  Activation de la limitation via les profils Alter system set ressource_limit = [True|false]

[SCOPE = [memory|spfile|both]]

n  Affectation ¨  lors de la création de l’utilisateur

CREATE USER nom

[…]

[PROFILE nom_p]

[…];

¨  Après … ALTER USER nom

[…]

[PROFILE nom_p]

[…];

n  Suppression d’un profil Drop profile nom_p [cascade];

4

Introduction

Architecture Oracle



Les outils


Informations sur les profils

Dba_users

Username User_ID Password Account_status Lock_date Expiry_date Default_tablespace Temporary_tablespace Created Profile Initial_rsrc_consumer_group External_name

Dba_profiles

Profile Resource_name Resource_type limit

4

Introduction

Architecture Oracle



Les outils


Gérer les droits

n  Ordres DCL ¨  Grant … ¨  Revoke …

n  Privilège système ¨  Droits d’exécuter un ordre SQL ¨  Si le droit porte sur un ordre indépendamment du schéma on peut

préciser ANY (ex : grant create [any] table …)

Grant ordre_sql to utilisateur [With admin option];

n  Privilège sur les objets ¨  Droits d’accéder à un objet (en dehors du propriétaire)

Grant privilège on objet to utilisateur [With grant option];

4

Introduction

Architecture Oracle



Les outils


Privilèges système

n  Select any dictionary ¨  Permet d’accéder aux vues DBA_ sans être DBA ¨  Nécessaire pour utiliser DC

n  Create session ¨  Donne le droit à un utilisateur de se connecter

n  Sysoper ¨  Droits courants de gestion (ex: startup, alter database, …)

n  Sysdba ¨  Droits de sysoper « with admin option » + create database ¨  Donnés par défaut à sys ¨  Privilège nécessaire pour certaine tâche d’administration ¨  Stocké dans le fichier de mot de passe (sinon authentification OS)

n  Informations sur l’attribution des privilèges système ¨  Dba_sys_privs ¨  V$pwfile_users

4

Introduction

Architecture Oracle



Les outils


Révocation de privilèges

n  Privilège Système Revoke [privilège|all privileges] From utilisateur;

¨  Le privilège est retiré au compte indiqué

n  Privilège Objet Revoke [privilège|all privileges] on objet From utilisateur;

¨  Le privilège est retiré au compte indiqué AINSI qu’à tous ceux pour qui le droit à été accordé via ce compte (grantor)

Nb : ¨  Un compte peut obtenir les droits via des rôles. ¨  Revoke n’est pas deny.

4

Introduction

Architecture Oracle



Les outils


Notion de base ‘Fermée’

n  Donner les privilèges sur les vues et procédures aux utilisateurs Les vues et procédures accèdent aux tables. Les utilisateurs n’ont pas de privilèges sur les tables

n  Prévoir l’usage de : ¨  Synonymes ¨  Déclencheurs ‘instead of’ (pour certaines vues) ¨  D’étudier la clause

AUTHID [Current_user|definer] pour certaines procédures

Schéma 1

Proc ------ ------

Proc ------ ------

Vue

4

Introduction

Architecture Oracle



Les outils


Accéder aux objets des autres schémas

n  Select * from schema.objet@dblink

¨  Schema : nom du schéma dans lequel est stocké l’objets

¨  Objet : nom de l’objet

¨  @dblink : nom du contexte de sécurité qui permet l’accès à une autre instance

4

Introduction

Architecture Oracle



Les outils


VPD et FGA

n  Fonctionnalités avancées dans la gestion des droits via le package DBMS_RLS

¨  VPD = virtual private database

¨  FGA = fine-grained access

4

Introduction

Architecture Oracle



Les outils


Définition de rôle

n  Ensemble nommé de privilèges

n  Un utilisateur peut adhérer à un ou plusieurs rôle.

n  Un rôle peut être attribué à un autre rôle.

n  Syntaxe : Create role nom

[identified by pwd |identified externally |using nom_package |not identified]

n  Limitations ¨  Un rôle appartient à personne ¨  Un rôle n’est pas associé à un schéma ¨  Un développeur doit avoir le droit direct (pas au travers un rôle)

d’accès aux objets cités dans ces procédures ou vues

4

Introduction

Architecture Oracle



Les outils


Attribuer/Révoquer un rôle

Grant nom_role to [utilisateur|Public|nom role] [with admin option];

n  NB : ¨  attribuer un rôle :

n  prédéterminer les rôles possibles pour un compte

¨  activer un rôle : n  indiquer le ou les rôles effectifs

¨  Attribuer n’est pas activer !

Revoke nom_role from [utilisateur|Public|nom role];

4

Introduction

Architecture Oracle



Les outils


Activation/Désactivation d’un rôle

n  On ne peut activer que des rôles attribués

n  par défaut : ¨  Alter user utilisateur

default role [nom_role[,nom_role] |all [except nom_role] |none];

n  en cours de session ¨  Set role

[nom_role[,nom_role] |all [except nom_role] |none];

4

Introduction

Architecture Oracle



Les outils


Rôles prédéfinis

n  Oracle propose un très grand nombre de rôles prédéfinis ¨  Ne pas donner plus de droits que voulu ¨  Etudier l’opportunité d’utiliser les rôles prédéfinis par rapport à

créer les siens

n  Informations sur les privilèges accordés par rôle prédéterminé : ¨  Documentation Oracle ¨  Dba_sys_privs : privilèges système

n  Session_privs n  System_privilege_map

¨  Dba_tab_privs : privilèges objets n  Dba_col_privs n  Table_privilege_map

¨  Dba_roles : liste des roles n  Dba_appliccation_roles n  Dba_role_privs n  Role_sys_privs n  Role_tab_privs n  Role_role_privs n  Session_roles

4

Introduction

Architecture Oracle



Les outils


Superviser les utilisateurs

n  Liste des utilisateurs connectés ¨  V$session

n  Liste des opérations en cours de plus de 6s ¨  V$session_longops

n  Déconnecter un utilisateur ¨  Alter system kill session ‘sid, serial#’;

¨  Alter system disconnect session ‘sid, serial#’ [immediate|post_transaction];

4

Introduction

Architecture Oracle



Les outils


La concurrence d’accès

Introduction

Architecture Oracle



Les outils


Transaction

n  Définitions : n  Ensemble d’écritures indissociables (atomiques) n  Est finie par l’ordre COMMIT ou ROLLBACK n  L’ensemble des écritures sont rendues permanentes ou sont ignorées n  Une transaction commence dès qu’une autre fini ; pas d’ordre/marque

de début de transaction

n  Fonctionnement n  Durant une transaction les informations sont écrites dans les blocs n  Les verrous empêchent l’accès aux autres transactions à ces verrous

¨  Les demandes de lecture sont dirigées vers les rollback segments (RBS) ¨  Les demandes d’écritures sont mises en attente

n  Lors de la fin de transaction ¨  COMMIT : les verrous sont levés ; les données sont disponibles pour tous ¨  ROLLBACK : les images avant sont recopiées depuis les RBS et les verrous

sont levés ; les données initiales sont disponibles 5

Début de transaction

Fin de transaction

Les écritures sont isolées par des verrous Les lectures sont possibles dans les RBS

Introduction

Architecture Oracle



Les outils


Remarques

n  COMMIT est implicite n  À chaque ordre DDL n  Avec l’ordre Truncate table n  Lors d’une déconnexion explicite

n  Rollback est implicite n  Sur perte de connexion n  À la sortie d’un programme précompilé (Pro*, …)

n  Cas particulier : SAVEPOINT n  Permet de revenir à une

marque prédéterminée de la transaction

UPDATE employees SET salary = 7000 WHERE last_name = 'Banda'; SAVEPOINT banda_sal; UPDATE employees SET salary = 120000 WHERE last_name = 'Greene'; SAVEPOINT greene_sal; SELECT SUM(salary) FROM employees; ROLLBACK TO SAVEPOINT banda_sal; UPDATE employees SET salary = 12000 WHERE last_name = 'Greene'; COMMIT;

Introduction

Architecture Oracle



Les outils


Verrous

n  Le verrouillage est implicite et automatique

n  Verrous DML n  La transaction pose :

¨  1 verrou partagé au niveau de la table ¨  1 verrou exclusif sur chaque ligne modifiée

n  La contention des verrous peut être facilement observable

n  Verrous DDL n  Ordres DDL : Create ; Alter ; Drop

¨  La transaction pose 1 verrou exclusif sur l’objet

n  Ordres DCL (et exceptions) ¨  La transaction pose 1 verrou partagé

n  La contention de ces verrous est rare du fait de la brièveté de la transaction (commit intégré)

n  Oracle ne transforme pas un verrou ligne en verrou table (pas d’escalade)

Introduction

Architecture Oracle



Les outils


Suivi des verrous

V$_lock

Addr Kaddr Sid Type Id1 Id2 Lmode Request Ctime Block

Remarque : la commande lock permet de verrouiller explicitement.

Introduction

Architecture Oracle



Les outils


Niveaux d’isolations

n  Read committed ¨  Niveau par défaut SET TRANSACTION ISOLATION LEVEL READ COMMITTED

ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;

n  Serializable ¨  Garanti que les données lues sont les mêmes tout au long de la

transaction. Aucune transaction concurrente.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE

n  Lecture seule ¨  N’autorise aucune écriture sur les données accédées. SET TRANSACTION READ ONLY

Introduction

Architecture Oracle



Les outils


Snapshot too old

n  Les images avant sont conservées dans les RBS n  Les RBS sont limités en taille n  Les informations dans les RBS sont effacées cycliquement

è  il se peut que les données à lire soient effacées des RBS (car trop anciennes !)

n  Solutions ¨  En amont :

n  limiter la durée des transactions au strict utile n  ponctuer chaque ordre DML par Commit (sauf si transaction de

plusieurs ordres) ¨  En aval :

n  Augmenter la taille disponible pour les informations d’annulations

Introduction

Architecture Oracle



Les outils


Deadlock

n  Interblocage : plusieurs transactions ayant verrouillé des ressources attendent mutuellement la libérations des ressources des autres transactions

n  Conséquences : n  Oracle tue l’ordre bloquant d’une transaction n  Une trace est écrite dans le dossier pointé par USER_DUMP_DEST n  Une exception est levée pour la transaction tuée (ORA-60) n  La transaction doit être Rollbakée et rejouée n  Les autres transactions se poursuivent

n  Conseils n  Plus les transactions sont courtes moins le deadlock est probable n  Organiser les transactions pour quelle utilisent les ressources dans le

même ordre 1.  Relationnel (parent-enfant) 2.  Alphabétique (client ; adr_facture ; adr_livraison) 3.  Dans l’ordre des lignes (order by)

n  Prévoir, dans le code, la re-tentative

Introduction

Architecture Oracle



Les outils


Exercice

n  Lancez 2 consoles clientes (connect scott/tiger@emm)

¨  Vérification de la cohérence n  Console 1 : modifiez une ligne de la table emp n  Console 2 : affichez toutes les lignes de emp n  Console 1 : Commit; n  Console 2 : affichez toutes les lignes de emp

¨  Deadlock n  Console 1 : modifiez le salaire de MARTIN n  Console 2 : modifiez la commission de SMITH n  Console 1 : modifiez le salaire de SMITH n  Console 2 : modifiez la commission de MARTIN

Introduction

Architecture Oracle



Les outils


Les outils

Introduction

Architecture Oracle



Les outils


Outils graphiques

n  Database control

n  Enterprise manager

n  Différents assistants

Introduction

Architecture Oracle



Les outils


Outils d’import / export

n  Nouveaux programmes : impdp et expdp ¨  Remplacent imp et exp ¨  Depuis et vers 9.2 et supérieur ¨  Utilisent le package DBMS_DATAPUMP ¨  Peut utiliser une « table externe »

n  Imp et Exp ¨  Existent toujours …

Introduction

Architecture Oracle



Les outils


SQL*loader

Décrit la source et la destination

Reçoit les lignes en erreur (bad)

Reçoit les lignes non retenues

Contient la source

Journalisation

Introduction

Architecture Oracle



Les outils


Dbverify : Vérification de l’intégrité physique

n  Vérification des blocs d’un fichier de données

n  Vérification d’un segment

administration pour développeursmyuuu.fr/cours/mbds/dba/dbapourdeveloppeur-oracle10g.pdf ·...

Documents