Post on 10-Sep-2018
Administration pour développeurs
Oracle 10g
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 2
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 3
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 4
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 5
Utilisateurs et rôles
Inst
ance
Mémoire
Processus
Paramètres d’initialisation
Base
de
donn
ées
---- --- --- ---
--- --- ---
Data
Contrôle
Journalisation
2
2 Architecture Oracle
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 6
Utilisateurs et rôles
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
Paramètres d’initialisation
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 7
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 8
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 9
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 10
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 11
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 12
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 13
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 14
Utilisateurs et rôles
L’instance
Inst
ance
Mémoire
Processus
SGA PGA
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 15
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 16
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 17
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 18
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 19
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 20
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 21
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 22
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 23
Utilisateurs et rôles
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 …
LGWR CKPT SMON ARCn CJQn
Processus d’arrière plan
DBWn PMON
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 24
Utilisateurs et rôles
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
LGWR CKPT SMON ARCn CJQn
Processus d’arrière plan
DBWn PMON
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 25
Utilisateurs et rôles
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
LGWR CKPT SMON ARCn CJQn
Processus d’arrière plan
DBWn PMON
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 26
Utilisateurs et rôles
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
LGWR CKPT SMON ARCn CJQn
Processus d’arrière plan
DBWn PMON
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 27
Utilisateurs et rôles
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
LGWR CKPT SMON ARCn CJQn
Processus d’arrière plan
DBWn PMON
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 28
Utilisateurs et rôles
Processus CJQ
n Job Queue
n Lance les travaux programmés en démarrant un processus esclave (Jnnn)
LGWR CKPT SMON ARCn CJQn
Processus d’arrière plan
DBWn PMON
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 29
Utilisateurs et rôles
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
LGWR CKPT SMON ARCn CJQn
Processus d’arrière plan
DBWn PMON
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 30
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 31
Utilisateurs et rôles
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)
Paramètres d’initialisation
# 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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 32
Utilisateurs et rôles
Résumé des composants
Inst
ance
Paramètres d’initialisation
Base
de
donn
ées
Contrôle
---- --- --- ---
--- --- ---
Journalisation Data
Processus
LGWR CKPT SMON ARCn CJQn DBWn PMON
Mémoire
PGA SGA
2
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 33
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 34
Utilisateurs et rôles
Stockage physique et logique
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 35
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 36
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 37
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 38
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 39
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 40
Utilisateurs et rôles
DC : Espace disque logique
3
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 41
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 42
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 43
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 44
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 45
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 46
Utilisateurs et rôles
Utilisateurs et rôles
Authentification
Définition
Profil
Privilèges
Schéma
Rôles
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 47
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 48
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 49
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 50
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 51
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 52
Utilisateurs et rôles
Créer / modifier un profil
[CREATE|ALTER] PROFILE nom LIMIT [SESSIONS_PER_USER x|UNLIMITED|DEFAULT]
[CPU_PER_SESSION x|UNLIMITED|DEFAULT]
[CPU_PER_CALL x|UNLIMITED|DEFAULT]
[CONNECT_TIME x|UNLIMITED|DEFAULT]
[IDLE_TIME x|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_SESSION x|UNLIMITED|DEFAULT]
[LOGICAL_READS_PER_CALL x|UNLIMITED|DEFAULT]
[COMPOSITE_LIMIT x|UNLIMITED|DEFAULT]
[PRIVATE_SGA x [K|M]|UNLIMITED|DEFAULT]
[FAILED_LOGIN_ATTEMPTS] x|UNLIMITED|DEFAULT]
[PASSWORD_LIFE_TIME] x|UNLIMITED|DEFAULT]
[PASSWORD_REUSE_TIME] x|UNLIMITED|DEFAULT]
[PASSWORD_REUSE_MAX] x|UNLIMITED|DEFAULT]
[PASSWORD_LOCK_TIME] x|UNLIMITED|DEFAULT]
[PASSWORD_GRACE_TIME] x|UNLIMITED|DEFAULT]
[PASSWORD_VERIFY_FUNCTION function|NULL|DEFAULT]
4
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 53
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 54
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 55
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 56
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 57
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 58
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 59
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 60
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 61
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 62
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 63
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 64
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 65
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 66
Utilisateurs et rôles
La concurrence d’accès
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 67
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 68
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 69
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 70
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 71
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 72
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 73
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 74
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 75
Utilisateurs et rôles
Les outils
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 76
Utilisateurs et rôles
Outils graphiques
n Database control
n Enterprise manager
n Différents assistants
Introduction
Architecture Oracle
Stockage physique et logique
Concurrence d’accès
Les outils
Page 77
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 78
Utilisateurs et rôles
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
Stockage physique et logique
Concurrence d’accès
Les outils
Page 79
Utilisateurs et rôles
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