Cours Complet - Administration de Bases de Données Oracle - Janvier2023 [PDF]

Zitiervorschau

Administration de bases de données oracle

Prérequis et Objectifs • Prérequis • Modèle relationnel (structure, contraintes) • Connaitre le langage SQL • Commande de base sur linux

• Objectifs • Connaître les tâches d’un DBA • Connaître les concepts et points clés de l’architecture Oracle • Savoir effectuer les principales tâches sous Oracle

2

Les métiers autour des bases de données • Administrateur System • Responsable sécurité • Administrateur réseaux • Développeurs d’application • Administrateurs d’application • Data scientiste et analyste • Utilisateurs : modifier les données, créer des rapports • Cloud Manager et administrateur 3

Métier de DBA • Installer les logiciels Oracle • • • • • • • • • •

un serveur, des applications clientes, En fonction de l’OS et des paramètres systèmes Si fonctionnement en réseau : composants réseaux d’Oracle Planifier et créer des bases de données Gérer les utilisateurs et leurs droits  Gérer l’espace et implanter les schémas des données Assurer la sécurité, l’intégrité et la pérennité des données Effectuer des réglages pour optimiser les performances Géré les disaster recovery (savegarde et restauration) Conseiller les équipes applicatives 4

Les differents types de licences • Full use • NUP Named User Plus

5

Les Différents produits et gammes d’Oracle Oracle commercialise 3 types d'Edition de produit SGBD. • Edition Personnelle (uniquement sur Windows et Linux) • Elle a les même fonctionnalités que EE mais est en monolicence. • Elle est plus destinée aux développeurs

• Edition Standard (SE2) • Elle a toute les fonctionnalités de base pour faire fonctionner un application en client/serveur • Elle est limitée à 2 processeurs maxi et 16 threads • Elle est moins onéreuse que l’Entreprise Edition 6

Les Différents produits et gammes d’Oracle • Edition Entreprise (EE) • Comporte toutes les fonctionnalités et destinée aux applications critiques • Elle est la seule édition qui comporte des options • • • • • •

RAC Partitionnement Advanced Security Diagonstics / Tuning pack Multitenant (CDB/PDB) à plus de 4 PDB Etc ..

• Elle est relativement couteuse. 7

Architecture de Haute disponibilitée • SEHA remplace RAC en sur la version 19C en SE2 • RAC disponible seulement en Entreprise Edition

8

Notion de Client / Serveur • Un processus utilisateur est crée quand un utilisateur lance une application cliente • Une connexion va être créée avec l’instance Oracle, l’utilisateur va ouvrir une session

• Un processus serveur va analyser et exécuter les requetés, retourner les données • Mode dédié : un processus serveur pour un processus client • Mode partagé : les clients partagent un groupe de processus serveurs évite les processus serveurs inactifs

9

Architecture OFA (Optimal Flexible Architecture)

10

Architecture OFA

(Optimal Flexible Architecture)

• OFA est un ensemble de recommandations sur l’arborescence et le nommage des fichiers du serveur, destinées à faciliter l’administration des produits Oracle • Un des points les plus intéressants du standard OFA est de clairement séparer le produit Oracle, les fichiers relatifs à l’administration et les fichiers des bases de données, en tenant compte de la possibilité d’avoir plusieurs versions d’Oracle et/ou plusieurs bases sur le serveur. • Les recommandations varient légèrement selon la plate-forme (voir la documentation "Oracle® Database Installation Guide" de votre plate-forme). • Dans le standard OFA, deux répertoires jouent un rôle particulier : • les répertoires Oracle Base et Oracle Home • exemple: oracle_base = /u01/app/oracle • Oracle_home=oracle_base/product/v.oracle/type_product (/u01/app/oracle/product/PROD/19.0.0/dbhome_1) • (/u01/app/oracle/product/QUALIF/19.0.0/dbhome_1) 11

Architecture OFA (Optimal Flexible Architecture) En dehors du répertoire Oracle Home, le répertoire Oracle Base est destiné à contenir sept autres répertoires • admin pour les fichiers d’administration des bases de données ; • audit pour les fichiers d’audit  • cfgtoollogs pour les fichiers journaux des assistants de configuration  • checkpoints utilisé pendant l’installation pour enregistrer des étapes intermédiaires (vidé une fois l’installation terminée) ; • diag pour le Référentiel de diagnostic automatique (ADR - Automatic Diagnostic Repository)  • fast_recovery_area pour la zone de récupération rapide (fast recovery area)  • oradata pour les fichiers des bases de données. • /u02/app/oradata/CAMPUS1 • /u02/app/oradata/CAMPUS2

12

13

Installation du serveur oracle

14

Vérification des prérequis logiciel • Pour chaque distribution, un certain nombre de packages doivent être installés (dans leur dernière version • • • •

Installation des packages en fonction de l’OS Installer un environnement X Configuration du noyau Créer le compte oracle et les diffèrent groupe (oinstall/dba/sysoper/sysbackup)

• groupadd oinstall  • groupadd dba  • useradd -g oinstall -G dba oracle  •Rebooter le serveur pour valider les modifications ou (sysctl -p [fichier] ) • Si vous êtes enregistrés auprès du support ULN • vous pouvez très facilement installer ces packages à l’aide d’une commande yum • yum install oracle-database-preinstall-19c

15

Installation du serveur oracle

L’installation d’Oracle sur un serveur nécessite une bonne compréhension de l’architecture Oracle et des compétences minimales sur le système d’exploitation • Principales étapes de l’installation • Pré-installation : préparer le système d’exploitation (unix) • • • •

vérifier les prérequis logiciels et matériels  configurer le noyau (sémaphores, mémoire partagée...)  créer les répertoires nécessaires  créer un groupe et un compte appartenant à ce groupe

• installation : • installer les produits Oracle  • Choix de type de produit SE2/ EE et la version

• post-installation : terminer l’installation et configurer certains composants Oracle • télécharger et appliquer d’éventuels patches • configurer le démarrage et l’arrêt automatiques des différents composants • configurer certains composants Oracle (Oracle Net, etc.) 16

Configuration supplémentaire • dnf install -y oracle-database-preinstall-19c • https://yum.oracle.com/repo/OracleLinux/OL8/baseos/latest/x86_64/getPackag e/oracle-database-preinstall-19c-1.0-1.el8.x86_64.rpm • /sbin/sysctl -p /etc/sysctl.d/98-oracle.conf • Créer les répertoire oracle_base et oracle_home • mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1 

•Dezipper le fichier d’installation dans oracle_home •unzip /tmp/LINUX.X64_193000_db_home.zip 

17

Vérification des prérequis matériels Les exigences matérielles sont les suivantes : • 1 Go de mémoire physique minimum (2 Go ou plus recommandé). • Espace swap : 1,5 fois la mémoire physique si cette dernière fait moins de 2 Go ou égal à la mémoire physique si cette dernière est comprise entre 2 Go et 16 Go, 16 Go au-delà. • Au moins 1 Go d’espace temporaire (/tmp). • Environ 7,5 Go d’espace disque pour les produits Oracle, mais Oracle recommande de prévoir de l’espace supplémentaire (jusqu’à 100 Go !) pour l’application de futurs patches. • Environ 4,5 Go d’espace disque supplémentaire si vous souhaitez créer une base de données de départ lors de l’installation. • Résolution 1024 x 768 au minimum requis pour Oracle Universal Installer. 18

Installation des binaires oracle L’installation des binaires se fait en mode graphique Oracle Universal Installer (OUI)

19

Installation • Lancer le runinstaller

20

• Dans notre exemple nous allons en premier installer les binaire seul ensuite la base.

21

• Choix du type d’instance (Single/RAC)

22

• Choix du produit en fonction de la licence acheté

23

24

25

• Vérification des près requis. Si tous est OK on a la fenêtre cidessous • Dans le cas contraire il faut corriger et relancer la vérification jusqu’à ce que tous soient OK

26

27

28

29

Fin d’installation des binaires Une fois les 2 scripts exécuter un on valide le OK

Télécharger et appliquer des patches Oracle 30

Architectures d’une base de données

31

Les différentes catégories de base de données • On distingue deux types de bases de données • transactionnelles" (ou OLTP pour OnLine Transaction Processing) • les bases de données "décisionnelles" (ou DSS pour Decision Support Systems).

• Une base de données transactionnelle se caractérise par : • une forte activité de mise à jour (INSERT/UPDATE), généralement sous la forme de petites transactions ; • un nombre plus ou moins important d’utilisateurs concurrents • une exigence de temps de réponse court.

• Une base de données décisionnelle se caractérise par : • une forte activité d’interrogation (SELECT) généralement sur des gros volumes de données (cette activité peut être interactive et/ou batch)  • une mise à jour périodique sous forme de batch avec des gros traitements de mise à jour  32

Architectures d’une base de données • Deux architectures possibles:

• client/serveur : des applications clientes envoient les requetés SQL et PL/SQL à un serveur. • Multiplexer : des serveurs d’application allègent la charge du serveur en réalisant certains accès pour les clients.

• Un serveur de bases de données est compose : • d’une instance = plusieurs processus et une zone de mémoire • d’une base de données = ensemble de fichiers physiques • de plusieurs schémas, assimilés à des utilisateurs

• Dans le cas de clusters de machines, Oracle peut associer plusieurs instance à une même base de données. 33

Architectures Notions d’instance et de base de données

34

La base de données • Une base de données est constituée de : • un ou plusieurs fichiers de données contenant les données proprement dites ; • au minimum un fichier de contrôle contenant des informations de contrôle sur la base de données ; • au minimum deux groupes de fichiers de journalisation enregistrant toutes les modifications apportées à la base.

35

•Chaque base de données porte un nom défini lors de sa création • ce nom est défini par le paramètre d’initialisation DB_NAME du fichier de paramètres (CAMPUS par exemple). •l’emplacement de la base de données sur le réseau peut être défini grâce au paramètre DB_DOMAIN (campus-lome.tg par exemple). •La base de données peut alors être aussi identifiée par son nom global défini par DB_NAME.DB_DOMAIN (CAMPUS.campus-lome.tg  par exemple)

36

L’instance • Une instance est constituée : • d’une zone de mémoire partagée appelée System Global Area (SGA) ; • d’un ensemble de processus d’arrière-plan (background process) ayant chacun un rôle bien précis ; • d’un ensemble de processus serveur (server process) chargés de traiter les requêtes des utilisateurs.

37

Les Mémoires • La SGA (Système Global Area) • Zone partagée par tous les utilisateurs de la base de données • Allouée au démarrage de l’instance en mémoire principale : doit-être la plus grosse possible. • Son but est d’´économiser les E/S. Elle contient : • le cache de données (database buffer cache) : • le cache de reprise (redo log buffer) : log des changements récents • le cache d’exécution partagé (shared pool) pour les requetés SQL et PL/SQl. • Contient le dictionnaire de données en cache.

• La PGA (Program Global Area) • Zone d’exécution des processus du serveur • Allouée au lancement de chaque processus 38

La SGA (System Global Area) composition • Elle est allouée au démarrage de l’instance et libérée lors de l’arrêt de

l’instance. Elle est dimensionnée par un ensemble de paramètres définis dans le fichier de paramètres • Elle est composé de: • Database Buffer Cache  (contient les blocs de données les plus récemment utilisés ) • Redo Log Buffer (mémoire tampon pour l’enregistrement des modifications apportées à la base de données) • Shared Pool (zone de partage des requêtes ) • Java Pool  (mémoire utilisée par la machine virtuelle Java intégrée) • Large Pool (zone de mémoire optionnelle utilisée pour exécution de requêtes en parallèle) • Streams Pool  (zone de mémoire qui permet de faire circuler des informations entre processus) • Flashback Buffer  (zone de mémoire optionnelle utilisée lorsque la fonctionnalité Flashback Database est activée) • Database Smart Flash Cache (permet d’étendre le Database Buffer Cache à l’aide de disques flash (s/s UNIX) • Shared I/O Pool (SecureFiles)  (utilisée pour les opérations sur les SecureFile Large Objects (LOB)) • In-Memory Area (qui permet de stocker les tables en mémoire) • Memoptimize Pool (optionnelle permet d’effectuer des recherches rapides ) 39

La PGA (Program Global Area) La PGA est la mémoire privée des différents processus • Pour un processus serveur, la PGA contient : • une zone de travail SQL (SQL work area) allouée dynamiquement pour certaines opérations (tri par exemple) ; • des informations sur la session ; • des informations sur le traitement des requêtes de la session ; • les variables de session.

• La PGA totale, allouée à tous les processus serveur, est appelée PGA agrégée (aggregated PGA) ou PGA de l’instance (instance PGA). • Dans une configuration serveur partagé, une partie de la PGA est en fait stockée dans la SGA, dans la Large Pool, ou, à défaut, dans la Shared Pool.

40

Gestion automatique de la mémoire partagée • Lorsque la gestion automatique de la mémoire partagée (ASMM - Automatic Shared Memory Management) est activée, les composantes suivantes de la SGA sont dimensionnées automatiquement en: • Fonction de leurs besoins respectifs • Et adaptées dynamiquement en fonction de la charge du système 

• Certaines zones de mémoire ne sont pas gérée automatiquement • Redo Log Buffer

•Si SGA_TARGET est égal à zéro (réglage automatique désactivé), les paramètres DB_CACHE_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE  et  STREAMS_POOL_ SIZE doivent être définis manuellement  41

Gestion manuelle de la mémoire • Pour une gestion manuelle et une répartition de la mémoire entre la SGA et la PGA, Oracle donne les indications suivantes. • Pour les systèmes transactionnels (OLTP) : • SGA : environ 80% de la mémoire disponible pour l’instance • PGA : environ 20% de la mémoire disponible pour l’instance

• Pour les systèmes décisionnels (DSS) : • SGA : entre 30% et 50% de la mémoire disponible pour l’instance • PGA : entre 50% et 70% de la mémoire disponible pour l’instance

• Pour un serveur qui a 4Go • Réserver 20 % de la mémoire OS (soit 819M ~ 1Go) • Attribuer 80 % pour SGA ( soit SGA_MAX_SIZE= 2464M) • 20% pour PGA (PGA_AGGREGATE_TARGET = 608M)

42

Les Processus serveur de la base • DBWn et BWnn • chargés d’écrire les blocs modifiés du Database Buffer Cache dans les fichiers de données

• CKPT (Chekpoint) • Pour assurer la synchronisation et la cohérence des données

• SMON (System Monitor) • Effectue la restauration lors de reprise après panne • Nettoie les segments temporaires • Fusionne certains extents libres contiguë

• PMON (Process Monitor) • Pour gérer les pannes des processus clients

• RECO (Recover) • Pour les reprises après panne de transactions distribuées

• ARCn (Archiver) • Pour l’archivage, lorsqu’il est active la base pourra être restauré dans le passée

• Il y a d’autre processus • Non décrit 43

Organisation logique du stockage

44

Organisation logique du stockage • Les fichiers de données sont découpés en blocs d’une taille donnée (4 Ko, 8 Ko...). • L’espace occupé par un objet dans un tablespace est désigné par le terme générique de segment. Il y a quatre types principaux de segments : • les segments de table : espace occupé par les tables ; • les segments d’index : espace occupé par les index ; • les segments d’annulation : espace temporaire utilisé pour stocker les informations permettant d’annuler une transaction ; • les segments temporaires : espace temporaire utilisé notamment lors d’un tri.

• Un segment appartient à un tablespace et est constitué d’extensions (extents). • Une extension est un ensemble de blocs contigus dans un fichier de données 45

Organisation logique du stockage • La notion de "bloc Oracle" est fondamentale • Un bloc Oracle est la plus petite unité d’entrée/sortie utilisée par Oracle. • Tous les fichiers de données sont organisés en blocs Oracle et ont donc une taille multiple de la taille du bloc

46

Notion de Schema • Le terme schéma désigne l’ensemble des objets qui appartiennent à un utilisateur. • Les principaux types d’objets sont les suivants : • • • • • •

Table Vue Synonyme Index Séquence Programme PL/SQL (procédure, fonction, package, trigger)

47

Le dictionnaire de Données • Ensemble de tables et de vues qui donnent des informations sur le contenu d’une base de données  • • • •

les structures de stockage ; les utilisateurs et les droits ; les objets (tables, vues, index, procédures, fonctions, etc.). etc.

• Appartient à SYS et est stocké dans le tablespace SYSTEM • Chargé en mémoire dans la partie Dictionary Cache de la Shared Pool et est utilisé en interne

• Il y a deux grands groupes de tables/vues dans le dictionnaire de données • les vues statiques  • les vues dynamiques de performance

• Elles sont mis à jour automatiquement lors de l’exécution des ordres SQL DDL 48

Structure du dictionnaire • Les vues statiques • Se sont des vues mises à la disposition des utilisateurs (selon leurs droits) • USER_% : informations sur les objets qui appartiennent à l’utilisateur • ALL_% : informations sur les objets auxquels l’utilisateur a accès (les siens et ceux sur lesquels il a reçu des droits)  • DBA_% : informations sur tous les objets de la base de données

• Vues dynamiques sur l’activité de la base : V $Views. • sont préfixées par V$ • les vues DICTIONARY et DICT_COLUMNS donnent la description de toutes les tables et vues du dictionnaire de données • les vues V$FIXED_TABLE et V$FIXED_VIEW_ DEFINITION donnent des informations sur la définition interne des vues dynamiques. 49

Création d’une base de donnée • Deux possibilités • Utiliser l’assistant Oracle : graphique (DBCA) • Créer manuellement à l’aide de scripts

• Prérequis : • Avoir installer le binaire d’oracle • Avoir les droits suffisants au niveau du system d’exploitation • La mémoire principale et espace disque doit être suffisante

50

Les Etapes de création • Le processus complet de création d’une nouvelle base de données pour une application comporte les grandes étapes suivantes : • Conception du modèle physique • Définir tous les objets (Oracle) de l’application : tables, contraintes d’intégrité (clés primaires/uniques/étrangères) • Définir la volumétrie

• Création de la base proprement dite (Voir ce chapitre) • Création des structures de stockage adaptées • Création du compte • Oracle qui va contenir les objets • Des comptes utilisateurs ou applicatifs et leurs droits

51

• Création des utilisateurs finaux de l’application • Leur donner des droits adaptés sur les objets de l’application

• Configurer l’accès à la base • Création des objets de l’application dans ce compte Oracle • Les tables, indexes, package ….

• Sauvegarde de la base • Mettre en place une politique de sauvegarde en fonction de la criticité

52

Les principaux paramètres d’initialisation Il y a plus de 400 paramètres documentés • DB_NAME: Nom de la base (8 caractères). Généralement DB_NAME est égal au nom de l’instance (ORACLE_SID) • DB_DOMAIN: Localisation logique de la base sur le réseau (jusqu’à 128 caractères) • DB_UNIQUE_NAME: Nom unique de base de données (jusqu’à 30 caractères) dans la plus part des des cas, DB_NAME= DB_UNIQUE_NAME • COMPATIBLE: Indique un numéro de version d’Oracle avec laquelle la base de données doit être compatible • CONTROL_FILES: Emplacement et le nom des fichiers de contrôle de la base de données 53

• DB_BLOCK_SIZE: Taille de bloc "standard" en octets, utilisée par défaut pour les fichiers de données des tablespaces et pour l’organisation du cache de données (buffer cache) valeur par defaut 8K autres valeurs (2K,4K 16K,32K) • MEMORY_MAX_TARGET: Taille maximum de la mémoire utilisable par l’instance, il est égale MERORY_TARGET s’il n’est pas spécifié • MEMORY_TARGET: Taille de la mémoire allouée à l’instance.

• Si ce paramètre a une valeur différente de zéro, la gestion automatique de la mémoire (Automatic Memory Management - AMM) est activée. • Dans ce cas, Oracle dimensionne automatiquement la SGA et la PGA en fonction de leurs besoins respectifs et de la charge du système

• SGA_MAX_SIZE: Taille maximale de la SGA . • Il a la valeur de MEMORY_MAX_TARGET s’il est défini ou la taille de la SGA au démarrage de l’instance

• SGA_TARGET: Taille souhaitée pour la SGA • Si la gestion automatique de la mémoire est activée (MEMORY_TARGET est différent de zéro) • s’il n’est pas spécifié, la valeur 0 lui est attribuée et la taille de la SGA est ajustée en interne

• SHARED_POOL_SIZE: Taille en octets de la Shared Pool • La taille de la Shared Pool est plutôt liée au nombre total de requêtes différé 54

• JAVA_POOL_SIZE: Taille en octets de la Java Pool • Si le réglage automatique de la mémoire partagée est activé (SGA_TARGET ou MEMORY_TARGET différent de zéro) • LARGE_POOL_SIZE: Taille en octets de la Large Pool • LOG_BUFFER:Taille en octets du Redo Log Buffer • Valeur par défaut : 2 Mo par groupe de 16 CPU avec un maximum de 4 Mo. La valeur par défaut est généralement suffisant

• PGA_AGGREGATE_LIMIT: Taille maximale de la PGA • = MEMORY_MAX_TARGET si MEMORY_TARGET est défini  • = 2 * PGA_AGGREGATE_TARGET si MEMORY_TARGET n’est pas défini  • 90 % de la mémoire physique du serveur moins la taille totale de la SGA si MEMORY_TARGET

• PGA_AGGREGATE_TARGET: Quantité de mémoire totale allouée à la PGA agrégée de tous les processus serveur • Valeur par défaut : 10 Mo, ou 20 % de la taille de la SGA si cette valeur est plus grande.

55

• OPEN_CURSORS: Détermine le nombre maximum de curseurs qui peuvent être ouverts simultanément par une session. Valeur par défaut : 50 • PROCESSES: Nombre maximum de processus qui peuvent se connecter simultanément à l’instance. Nombre de session= 1,5*nb_process + 22 • SHARED_SERVERS: Spécifie le nombre de processus serveur partagés qui sont créés lorsque l’instance démarre • NLS_LANGUAGE: Langage par défaut de l’instance • utilisé pour les messages, • les noms de jour et de mois et le tri. • Détermine aussi la valeur des paramètres NLS_DATE_LANGUAGE et NLS_SORT. • NLS_TERRITORY • :NLS_TERRITORY:Territoire par défaut de l’instance • utilisé pour la numérotation des jours et des semaines. • Détermine aussi la valeur par défaut des formats de date • Des séparateurs numériques et des symboles monétaires.

56

• UNDO_MANAGEMENT: Mode de gestion souhaité pour les segments d’annulation • UNDO_TABLESPACE: Nom du tablespace d’annulation à utiliser par défaut lors du démarrage de l’instance • DB_RECOVERY_FILE_DEST: Emplacement de la zone de récupération rapide (fast recovery area) • DB_RECOVERY_FILE_DEST_SIZE: Taille maximale autorisée pour l’ensemble des fichiers stockés dans la zone de récupération rapide • LOG_ARCHIVE_DEST: Spécifie une ou plusieurs destinations pour l’archivage des fichiers de journalisation • En SE: LOG_ARCHIVE_DEST et LOG_ARCHIVE_DUPLEX_DES entre 1 à 2 fichiers • En EE: LOG_ARCHIVE_DEST_n entre 1 à 31 fichiers

• LOG_ARCHIVE_FORMAT: Définit le format du nom des archives des fichiers de journalisation • Doit inclure les variables %T, %S et %R  • donnant respectivement le numéro de l’instance (thread), le numéro de séquence du fichier de journalisation et un identifiant d’incarnation

• REMOTE_LOGIN_PASSWORDFILE: 3 valeurs possible: NONE / SHARE /EXCLUSIVE(defaut) • LOCAL_LISTENER:Adresse réseau du processus d’écoute auprès duquel l’instance doit s’enregistrer automatiquement

57

Exemple de fichier PFILE(pfile.ora) audit_file_dest=‘/dba/traces/CAMPUS/adump‘  audit_trail='db'  compatible='19.0.0'  control_files=‘/dba/sys/CAMPUS/control01.ctl', ‘/dba/data/CAMPUS/control02.ctl‘ db_block_size=8192  db_name='CAMPUS'  diagnostic_dest=‘/dba/traces'  dispatchers='(PROTOCOL=TCP)(SERVICE=CAMPUSXDB)‘ local_listener='LISTENER_CAMPUS‘  memory_target=818m  nls_language='FRENCH'  nls_territory='FRANCE‘ open_cursors=300  processes=300  remote_login_passwordfile='EXCLUSIVE‘  undo_tablespace='UNDOTBS1'

58

Prérequis pour créer un base manuellement • Configurer le fichier pfile.ora avec les paramétrés choisie au préalable • exporter SID de la base • export $ORACLE_BASE • Export ORACLE_SID=CAMPUS • Export ORACLE_HOME

• Se connecter en • sqlplus / as sysdba • Startup nomount pfile.ora

• Exemple de fichier de création d’une base copier le contenue du fichier cre_db coller dans sqlplus 59

CREATE DATABASE CAMPUS USER SYS IDENTIFIED BY iamdba01 USER SYSTEM IDENTIFIED BY dba01iam LOGFILE GROUP 1 (’/u01/oracle/CAMPUS/log/CAMPUS_G1_L1.log’, ’/u01/oracle/CAMPUS/log/CAMPUS_G1_L2.log’) ) SIZE 100M, GROUP 2 (’/u01/oracle/CAMPUS/log/CAMPUS_G2_L1.log’, ’/u01/oracle/CAMPUS/log/CAMPUS_G2_L2.log’) SIZE 100M, GROUP 3 (’/u01/oracle/CAMPUS/log/CAMPUS_G3_L1.log’, ’/u01/oracle/CAMPUS/log/CAMPUS_G3_L2.log’) SIZE 100M MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1 MAXDATAFILES 100 MAXINSTANCES 1 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 DATAFILE ’/u01/oracle/CAMPUS/data/system01_CAMPUS.dbf’ SIZE 325M REUSE EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE ’/u01/oracle/CAMPUS/data/sysaux01_CAMPUS.dbf’ SIZE 325M REUSE DEFAULT TABLESPACE tbs_1 DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE ’/u01/oracle/CAMPUS/data/temp01_CAMPUS.dbf’ SIZE 20M REUSE 60 UNDO TABLESPACE undotbs

Création du dictionnaire de la base • La création du catalogue de la base se fait en exécutant un certain nombre de package pl/sql • @/$ORACLE_HOME/rdbms/admin/catalog.sql; • @/$ORACLE_HOME/rdbms/admin/catblock.sql; • @/$ORACLE_HOME/rdbms/admin/catproc.sql; • @/$ORACLE_HOME/rdbms/admin/utlrp.sql; • connect system/« xxxxxx" • @/$ORACLE_HOME/sqlplus/admin/pupbld.sql; • connect / as sysdba • @/outils/oracle/product/DEV/19.3SE2/sqlplus/admin/help/hlpbld.sql; • @/outils/oracle/product/DEV/19.3SE2/sqlplus/admin/help/helpus.sql;

61

Création du dictionnaire de la base • Créer le fichier spfile à partir de pfile • Créer de nouveaux tablespaces utilisateur: • Un DATA pour les utilisateurs • un INDX pour les index • Autres tablespaces applicative Synthaxe: Create tablespace nom_tbs datafile ‘/chemin/nom_datafile.dbf’ size xM reuse autoextend off Extent management local segment space management auto;

62

Création de la base avec DBCA • Pour lancer dbca il faut utiliser l’interface graphique • /outils/oracle/product/19C/prod/bin/dbca

63

Création de la base avec DBCA

64

Création de la base avec DBCA

• Permet d’utiliser une Template déjà existant pour configurer la nouvelle base ou récupérer les paramètres d’une base existante. • De configurer la base en mode datawarehouse • Genaral Purpose ou en Transaction Processing

65

Création de la base avec DBCA

66

GESTION DE L’INSTANCE

67

GESTION DE L’INSTANCE

• Les paramètres peuvent être classés en deux catégories : • les paramètres dynamiques ; • les paramètres statiques

• Les paramètres dynamiques peuvent être modifiés par un ordre SQL alors que l’instance est en cours de fonctionnement. Selon les cas, le paramètre est modifiable au niveau de la session et/ou du système (pour toutes les sessions). Au niveau système, la modification peut être immédiate (s’applique aux sessions actuelles) ou différée (s’applique aux sessions futures uniquement). • Les paramètres statiques ne peuvent pas être modifiés dynamiquement alors que l’instance est en cours de fonctionnement ; il faut modifier la valeur du paramètre dans le fichier de paramètres et redémarrer l’instance. 68

GESTION DE L’INSTANCE • ALTER SESSION SET paramètre = valeur [...] ; • ALTER SYSTEM SET paramètre = valeur [...] [ COMMENT = 'texte' ] • [ DEFERRED ] [ SCOPE = MEMORY | SPFILE | BOTH ] • Les paramètres dans le dictionnaire de données • V$SYSTEM_PARAMETER/ V$SYSTEM_PARAMETER2  : valeur actuelle des paramètres dans l’instance. • Show parameters

69

Outils d’Administration

• Oracle propose plusieurs outils d’administration • SQL*Plus : outil de base permettant d’éditer et d’exécuter des requêtes SQL. • Oracle Enterprise Manager Database Express : application Web, permettant d’administrer graphiquement une seule base de données. • Oracle Enterprise Manager Grid/Cloud Control : application Web, permettant d’administrer de manière centralisée plusieurs bases de données. • Oracle SQL Developer : application graphique permettant d’exécuter des requêtes ou des scripts SQL, de gérer les objets d’une base de données (tables, vues, etc.), de développer et mettre au point des programmes PL/SQL et d’effectuer des tâches d’administration. • Oracle SQL Developer Command Line (SQLcl) : outil en ligne de commande Java basé sur le moteur d’Oracle SQL Developer ; plus convivial et plus complet que SQL*Plus, tout en étant compatible avec ce dernier.

70

Installation du client

71

Installation du client

Les procédures d’installation d’un client Oracle ressemblent beaucoup, en plus simples, aux procédures d’installation du serveur. • Les similitudes d’installation entre un serveur et un client portent notamment sur : • Les différentes étapes de l’installation (pré-installation, installation avec Oracle Universal Installer, post-installation) ; • Le standard OFA, avec notamment un répertoire Oracle Home (plusieurs clients peuvent être installés sur la même machine) ; • Les spécificités de chaque plate-forme (variables d’environnement, base de registre, etc.) ; • La possibilité d’effectuer une installation non interactive, en utilisant un fichier de réponse

• Depuis la version 19c, l’installation et la configuration du client peuvent s’effectuer à partir d’une image (archive zip)

72

73

Un client Oracle comporte généralement au minimum le composant Oracle Net qui permet d’accéder à une base Oracle du réseau • Lors de l’installation plusieurs types installations sont proposées • Administrateur : installe la quasi-totalité des composants y compris les outils d’administration et les produits de développement. • Exécution : installe un client simple comportant principalement Oracle Net, SQL*Plus et les librairies de développement (drivers JDBC, Provider pour OLE DB ou .NET, etc.). • InstantClient : installe uniquement les librairies nécessaires aux applications qui utilisent les OCI avec la fonctionnalité de "client instantané" (instant client). Nécessite peu d’espace • Personnalisée : permet de sélectionner précisément les composants et d’installer un client parfaitement adapté à un besoin précis (développeur, déploiement) 74

Gestion des fichiers de journalisation

75

Gestion des fichiers de journalisation • Les fichiers de journalisation (redo log) enregistrent toutes les modifications apportées à la base de données. Ils sont au minimum au nombre de 2 groupes et chaque groupe est composé de 1 ou plusieurs fichiers membre. Les membres d’un groupe sont écrits en miroir et ont exactement le même contenu. Les groupes sont organisés et écrits de manière circulaire. Les informations écrite dans les groupes sont par défaut périodiquement écrasées.

76

Gestion des fichiers de journalisation

77

Gestion des fichiers de journalisation

• Les fichiers de journalisation (redo log) enregistrent toutes les modifications apportées à la base de données. Ils sont organisés en groupes écrits de manière circulaire ; les informations sauvegardées sont donc, par défaut, périodiquement écrasées après avoir été écrites sur disk. • Les fichiers de journalisation sont organisés en groupes (au minimum deux) composés d’un ou de plusieurs membres (minimum un) • À l’intérieur d’un groupe, les membres sont écrits simultanément en miroir et contiennent les même informations • Lorsqu’un groupe est plein (c’est-à-dire lorsque les membres sont pleins), l’instance Oracle passe au groupe suivant et ainsi de suite jusqu’au dernier  • Lorsque l’instance Oracle revient dans le premier groupe, elle écrase les informations qui y sont stockées après les avoir écrite sur disk. 

• Les vues du dictionnaire permettent d’obtenir des informations sur les fichiers de journalisation • • • •

V$LOG : informations sur les groupes. V$LOGFILE : informations sur les membres. V$LOG_HISTORY : informations sur l’historique des fichiers de journalisation V$LOG_HISTORY  : peut être utilisée pour analyser la vitesse de basculement des fichiers de journalisation.   78

Gestion des fichiers de journalisation • Différentes opérations d’administration peuvent être effectuées sur les fichiers de journalisation  • Ajouter un nouveau membre dans un groupe permet d’améliorer la sécurité des fichiers de journalisation (multiplexage)

ALTER DATABASE ADD LOGFILE MEMBER 'nom_fichier' [,...] TO GROUP numéro; • Ajouter un nouveau groupe permet d’améliorer la disponibilité des fichiers de journalisation pour le processus LGWR, en augmentant la durée d’un cycle complet de rotation. ALTER DATABASE ADD LOGFILE [GROUP numéro] ('nom_fichier' [,...]) [ SIZE valeur [K|M|G] ] [REUSE]; • Déplacer un membre permet d’améliorer la répartition des entrées/sorties par exemple. ALTER DATABASE ADD LOGFILE GROUP 4 ('chemin/campus/redo04a.log', 'chemin/campus/redo04b.log') SIZE 200M; • Supprimer un groupe peut être utilisé dans une opération d’augmentation de la taille des groupes (ajout d’un nouveau groupe plus gros puis suppression d’un ancien). ALTER DATABASE DROP LOGFILE GROUP numéro ; ALTER DATABASE DROP LOGFILE MEMBER 'nom_fichier' [,...] (suppression d’un membre) • Forcer le basculement du groupe courant au suivant peut être utilisé dans l’opération d’augmentation de taille.

79

Gestion des fichiers de contrôle • Le fichier de contrôle contient des informations de contrôle sur la base de données : • le nom de la base de données ; • la date et l’heure de création de la base de données ; • l’emplacement des autres fichiers de la base de données (fichiers de données et fichiers de journalisation) ; • le numéro de séquence actuel des fichiers de journalisation ; • des informations sur les points de reprise (checkpoint), • etc. • Le fichier de contrôle est automatiquement mis à jour par Oracle lors de chaque modification de la structure de la base de données (ajout ou déplacement d’un fichier par exemple). La taille du fichier de contrôle est déterminée par Oracle • Si le fichier de contrôle est inexploitable la base restera à l’état

80

Sécurisation du fichier de controle • Pour ne pas être en mesure de démarrer la base il est fortement conseiller de faire fonctionner la base de données avec au moins deux fichiers de contrôle et si possible sur des disques différents (dans l’idéal, trois ou quatre sur des disques différents) • Ajout de nouveau fichier de contrôle SQL> ALTER SYSTEM SET CONTROL_FILES = (‘$ORACLE_HOME/oratada/campus/control01.ctl‘, ‘$ORACLE_HOME/oratada/campus/control02.ctl‘, ‘$ORACLE_HOME/oratada/campus/control03.ctl‘)  SCOPE = SPFILE; • SQL> SHUTDOWN IMMEDIATE • SQL> startup

• La vues v$controle donne les information sur les fichier de contrôle •  la vue V$CONTROLFILE_RECORD_SECTION (informations sur le contenu des différentes sections du fichier)

81

Démarrage et Arrêt • Pour rendre une base accessible à tous les utilisateurs, il faut démarrer une instance et ouvrir la base de données avec cette instance. • Il y • • •

a trois grandes phases dans le processus de démarrage  démarrage de l’instance  (état NOMOUNT) montage de la base de données   (état MOUNT)  ouverture de la base de données (état OPEN)

• Lors du démarrage de l’instance, le fichier de paramètres est lu, la SGA est allouée et les processus d’arrière-plan sont démarrés • À ce stade, seule l’instance est lancée ; • il n’y a pas de base de données associée • Les vues dynamiques relatives à l’instance (V$INSTANCE, V$SGA, V$OPTION, V$PARAMETER, V$VERSION, etc.) • Lors du montage de la base de données (état mount), l’instance utilise le paramètre CONTROL_FILES du fichier initSID.ora pour localiser les fichiers de contrôle et les ouvrir. Dans le fichier de contrôle, l’instance extrait le nom et le statut des fichiers de données et des fichiers de journalisation mais ne les ouvre pas et ne vérifie pas non plus leur présence

82

• Lors de l’ouverture de la base de données, • • • •

l’instance ouvre les fichiers de journalisation les fichiers de données qui étaient en ligne au moment de l’arrêt vérifie la cohérence de la base de données Signale une erreur si un fichier de données est manquante ou endommagée • S’il y a un arrêt brutal et aucun fichiers d’endommagé le processus SMON effectue la réparation

•Quand est ouverte, elle est accessible pour une utilisation "normale" : les utilisateurs peuvent se connecter. Le dictionnaire de données est totalement disponible.

83

Arrêt • De même, il y a trois grandes phases dans le processus : • fermeture de la base de données ; • démontage de la base de données ; • arrêt de l’instance.

84

Automatisation et scripts

• Sous Unix les bases de données peuvent être démarrées ou arrêtées automatiquement grâce au script de démarrage (dbstart et dbshut) • Les scripts dbstart et dbshut utilisent le fichier /etc/oratab pour déterminer quelles sont les bases de données à démarrer ou arrêter automatiquement. • Exemple du contenue d’oratable: CAMPUS:/u01/app/oracle/product/12.1.0/dbhome_1:Y • Le script restart ORACLE_SID en paramètre permet de redémarrer une base de données. • Il appelle le script oraenv pour modifier l’environnement • puis SQL*Plus pour faire un SHUTDOWN et un STARTUP

85

Gestion des utilisateurs et de leurs droits • Pour gérer la sécurité de la base, oracle permet: • de définir les utilisateurs qui peuvent se connecter à la base de données (avec une identification par le système d’exploitation ou par la base de données) ; • de définir dans quel(s) tablespace(s) un utilisateur peut créer des objets (éventuellement aucun) ; • de limiter l’utilisation des ressources système (quota) ; • d’imposer une politique de gestion de mots de passe (expiration périodique, non- réutilisation avant un certain temps, etc.) ; • de définir les droits de chaque utilisateur à l’intérieur de la base de données. •Dans une base de données Oracle, les droits des utilisateurs sont gérés avec la notion de privilège qui peut être accordé ou révoqué. •Les privilèges peuvent être attribués directement aux utilisateurs ou par l’intermédiaire de rôles

86

• Les différents mode d’identification de l’utilisateur. • Identification par Oracle (exemple: CONNECT oheu/rx239$) • Identification par le système d’exploitation (exemple: CONNECT /) Oracle ne vérifie pas le mot de passe mais contrôle simplement que le nom de l’utilisateur, au niveau du système d’exploitation, correspond à un nom d’utilisateur dans la base de données. Oracle utilise un préfixe défini par le paramètre OS_AUTHENT_PREFIX (par défaut égal à OPS$). Par exemple, l’utilisateur ayant pour nom vdep au niveau du système d’exploitation pourra se connecter à la base par un CONNECT / uniquement s’il existe un compte Oracle ops$vdep

• Utilisateur schéma seul Depuis la version 18c, il est possible de créer des utilisateurs schéma seul, qui n’ont pas de mot de passe et qui ne peuvent donc pas se connecter à la base de données

87

Creation d’un utilisateur • Pour qu’un utilisateur se connecte le dba ou une personne qui a les droits adéquats doit créer le compte et lui donnée les droits en fonction des actions à réaliser sur la base • Synthaxe CREATE USER nom   NO AUTHENTICATION | { IDENTIFIED { BY mot_de_passe | EXTERNALLY } }  [ DEFAULT TABLESPACE nom_tablespace ]  [ TEMPORARY TABLESPACE nom_tablespace ]  [ QUOTA { valeur [K|M] | UNLIMITED } ON nom_tablespace [,...] ]  [ PROFILE nom_profil ]  [ PASSWORD EXPIRE ]  [ ACCOUNT { LOCK | UNLOCK } ] ; • Exemple d’un utilisateur identifié par l’OS avec uniquement les clauses obligatoires : CREATE USER "OPS$SRVWINORA\VDEP" IDENTIFIED EXTERNALLY;

88

• Modification d’un utilisateur • ALTER USER nom  [ NO AUTHENTICATION | { IDENTIFIED { BY mot_de_passe | EXTERNALLY } } ]  [ DEFAULT TABLESPACE nom_tablespace ]  [ TEMPORARY TABLESPACE nom_tablespace ]  [ QUOTA { valeur [K|M] | UNLIMITED } ON nom_tablespace [,...] ]  [ PROFILE nom_profil ]  [ PASSWORD EXPIRE ]  [ ACCOUNT { LOCK | UNLOCK } ] ;

• Suppression d’un utilisateur •

DROP USER nom [ CASCADE ] ;

• Trouver des informations sur les utilisateurs •

Plusieurs vues du dictionnaire de données permettent d’obtenir des informations sur les utilisateurs • •

DBA_USERS informations sur les utilisateurs  DBA_TS_QUOTAS : informations sur les quotas des utilisateurs

89

les profils Utilisateur Un profil est un ensemble nommé de limitations de ressources qui peut être attribué à un utilisateur • Les ressources et suivantes peuvent être mises en œuvre • temps CPU par appel et/ou par session ; • nombre de lectures logiques par appel et/ou par session ; • nombre de sessions ouvertes simultanément par un utilisateur ; • temps d’inactivité par session ; • durée totale de la session ; • quantité de mémoire privée dans la SGA (configuration serveurs partagés uniquement)

90

les profils Utilisateur • verrouillage de compte (et durée de verrouillage) au-delà d’un certain nombre d’échecs de tentative de connexion ; • verrouillage de compte au-delà d’un certain nombre de jours d’inactivité (c’est-à-dire sans connexion - apparu en version 12.2) ; • durée de vie des mots de passe (avec éventuellement une période de grâce) ; • non-réutilisation d’un mot de passe avant un certain temps ou avant un certain nombre de changements ; • complexité du mot de passe. • Le profil nommé DEFAULT est automatiquement créé lors de la création de la base de données. Ce profil est attribué par défaut aux utilisateurs. Par défaut, ce profil DEFAULT n’impose aucune limite pour les ressources ; par contre, depuis la version 11, ce profil comporte des limites pour les mots de passe 91

Les actions sur un profil •Création d’un profile Syntaxe CREATE PROFILE nom LIMIT [ SESSIONS_PER_USER { valeur | UNLIMITED | DEFAULT } ] [ CPU_PER_SESSION { valeur | UNLIMITED | DEFAULT } ] [ CPU_PER_CALL { valeur | UNLIMITED | DEFAULT } ] [ CONNECT_TIME { valeur | UNLIMITED | DEFAULT } ] [ IDLE_TIME { valeur | UNLIMITED | DEFAULT } ] [ LOGICAL_READS_PER_SESSION { valeur | UNLIMITED | DEFAULT } ] [ LOGICAL_READS_PER_CALL { valeur | UNLIMITED | DEFAULT } ] [ COMPOSITE_LIMIT { valeur | UNLIMITED | DEFAULT } ] [ PRIVATE_SGA { valeur [K|M] | UNLIMITED | DEFAULT } ] [ FAILED_LOGIN_ATTEMPTS { valeur | UNLIMITED | DEFAULT } ] [ PASSWORD_LIFE_TIME { valeur | UNLIMITED | DEFAULT } ] [ PASSWORD_REUSE_TIME { valeur | UNLIMITED | DEFAULT } ] [ PASSWORD_REUSE_MAX { valeur | UNLIMITED | DEFAULT } ] [ PASSWORD_LOCK_TIME { valeur | UNLIMITED | DEFAULT } ] [ PASSWORD_GRACE_TIME { valeur | UNLIMITED | DEFAULT } ] [ PASSWORD_VERIFY_FUNCTION { nom_fonction | NULL | DEFAULT } ] [ INACTIVE_ACCOUNT_TIME { valeur | UNLIMITED | DEFAULT } ];

92

Les actions sur un profil • Modification d’un profil • Alter profile (Même structure que create profil )

• Affectation d’un profil à un utilisateur •  CREATE USER xgeo IDENTIFIED BY tempo  TEMPORARY TABLESPACE temp  PROFILE exploitation PASSWORD EXPIRE; • ALTER USER oheu PROFILE exploitation;

• Activation de la limitation des ressources • ALTER SYSTEM SET RESOURCE_LIMIT = TRUE [ clause_SCOPE ]; • La vérification effective de la limitation des ressources est contrôlée par le paramètre RESOURCE_LIMIT. Si le paramètre vaut TRUE, la vérification de la limitation des ressources est active ; s’il vaut FALSE, la vérification de la limitation des ressources n’est pas active • Par défaut, le paramètre est à FALSE en version 11g et à TRUE depuis la version 12c.

•Suppression d’un profil •Plusieurs vues du dictionnaire de données permettent d’obtenir des informations sur les profils (DBA_USERS / DBA_PROFILES) 93

Gérer les droits

94

Gérer les droits • Les droits sont des privilèges accorder ou retirer à un utilisateur ou rôle pour exécuter un ordre SQL sur la base • Attribution d’un privilège system a un utilisateur se fait avec l’ordre SQL " GRANT«  • Syntaxe GRANT nom_privilège [,...] TO { nom_utilisateur | PUBLIC } [,...]  Exemple: grant create session,create table to philippe • Un utilisateur peut recevoir le privilège de pouvoir donner le droit à quelqu’un d’autre avec la clause WITH ADMIN OPTION  • Plusieurs privilèges peuvent être attribués à plusieurs utilisateurs en un seul ordre • Tous les privilèges système peuvent être attribués d’un seul coup avec le mot-clé ALL PRIVILEGES (GRANT ALL PRIVILEGES)

• Révocation d’un privilège système à un utilisateur • • • •

Syntaxe REVOKE nom_privilège [,...] FROM { nom_utilisateur | PUBLIC } [,...] ; Exemple REVOKE CREATE TABLE FROM francois; Pour pouvoir révoquer un privilège système, il faut avoir reçu le privilège avec la clause WITH ADMIN OPTION  ou le privilège système GRANT ALL PRIVILEGE 95

Gérer les droits • Les privilèges système SYSDBA et SYSOPER • Ces privilèges peuvent être contrôlés, soit par l’appartenance à un groupe particulier du système d’exploitation, soit par un fichier de mots de passe • Dans le cas de l’utilisation d’un fichier de mots de passe, par défaut seul SYS a reçu les privilèges SYSDBA et SYSOPER • La vue V$PWFILE_USERS permet de lister les utilisateurs qui ont reçu les privilèges SYSDBA ou SYSOPER ; • cette vue est toujours vide si REMOTE_LOGIN_PASSWORDFILE=NONE.

• Privilège objet • Un privilège objet est le droit d’accéder à un objet d’un autre utilisateur : de mettre à jour les données de la table CLIENT • Par défaut, seul le propriétaire d’un objet a le droit d’y accéder. Pour qu’un autre utilisateur puisse y accéder, le propriétaire de l’objet doit lui donner un privilège objet • Avoir un droit sur un objet ne dispense pas de devoir qualifier l’objet par le nom du propriétaire pour y accéder (sinon, Oracle pense que vous cherchez à accéder à un objet dans votre schéma). • l’existence d’un synonyme, même public, ne donne aucun droit sur l’objet sous-jacent • Pour les privilèges INSERT et UPDATE, une liste de colonnes peut être spécifiée afin de limiter le privilège aux colonnes indiquée • Synthaxe: GRANT { nom_privilège[(liste_colonnes)] [,...] | ALL [PRIVILEGES] } ON [nom_schéma.]nom_objet TO { nom_utilisateur | PUBLIC } [,...] [ WITH GRANT OPTION ] ; •Exemple : GRANT SELECT, INSERT, UPDATE(nom,prenom) ON adherent TO oheu; 96

Gérer les droits • La clause WITH GRANT OPTION donne au bénéficiaire le droit de transmettre le privilège objet . • Le mot-clé ALL permet d’attribuer tous les privilèges

•Révocation d’un privilège objet à un utilisateur

97

Gérer les droits • Privilèges sur les vues et les programmes stockés • Un utilisateur qui a un droit sur une vue ou procédure stockée n’a pas besoin d’avoir les droits sur les objets manipulés par la vue ou procédure stockée • Une procédure stockée peut s’exécute : • Avec les droits du propriétaire (definer rights). • Conçu pour s’exécuter avec les droits de l’appelant (invoker rights). (très dangereux) •Le comportement souhaité se définit lors de la création du programme stocké grâce à la clause AUTHID

• Syntaxe AUTHID { CURRENT_USER | DEFINER } •

Pour remédier à ce problème de sécurité, à partir de la version 12c, il est possible d’utiliser le privilège objet INHERIT PRIVILEGES pour autoriser explicitement les programmes stockés d’un utilisateur à être appelés avec les droits d’un autre utilisateur

• Syntaxe • GRANT INHERIT PRIVILEGES  ON USER nom_appelant  TO { nom_propriétaire | PUBLIC } [,...] ;  • REVOKE INHERIT PRIVILEGES  ON USER nom_appelant  FROM { nom_propriétaire | PUBLIC } [,...] ; • Dorénavant, lorsqu’un utilisateur appelle un programme stocké qui s’exécute avec les droits de l’appelant, Oracle vérifie que le propriétaire du programme stocké dispose du privilège INHERIT PRIVILEGES sur l’utilisateur appelant. Si ce n’est pas le cas, une erreur ORA-06598: privilège INHERIT PRIVILEGES insuffisant est retournée. 98

Gestion de Role • Un rôle est un regroupement nommé de privilèges (système et objet) qui peut être attribué à un utilisateur. • Tous les privilèges regroupés dans le rôle sont alors simultanément attribués à l’utilisateur • • • •

Un rôle peut être attribué à un autre rôle. Un utilisateur peut avoir plusieurs rôles. Un rôle n’appartient à personne Depuis la version 12, un rôle peut être attribué à un programme stocké (Partir l’accès accorder au programme basée sur le code » (Code-Based Access Control)

• La mise en œuvre s’effectue en trois étapes : • 1 - création du rôle ; Synthaxe CREATE ROLE nom [ IDENTIFIED { BY mot_de_passe | EXTERNALLY | USING nom_package} | NOT IDENTIFIED ];

• 2 - attribution des privilèges (système et objet) au rôle ; Synthaxe GRANT nom_privilège [,...] TO nom_rôle [,...] [ WITH ADMIN OPTION ] ;

• 3 - attribution du rôle aux utilisateurs (ou à d’autres rôles) Synthaxe GRANT { nom_privilège[(liste_colonnes)] [,...] | ALL [PRIVILEGES] } ON [nom_schéma.]nom_objet TO nom_rôle [,...] ;

99

Gestion de Rôle • Autre actions sur un role • Révocation d’un privilège à un rôle • Suppression d’un rôle • Activation ou désactivation d’un rôle • Un rôle attribué à un utilisateur (directement ou via un autre rôle) est par défaut automatiquement activé sauf si l’utilisateur est déjà connecté. • Parmi les rôles attribués à l’utilisateur, il est possible de définir ceux qui sont effectivement automatiquement activés lors de la connexion de l’utilisateur Syntaxe SET ROLE { nom_rôle [ IDENTIFIED BY mot_de_passe ] [,...] | ALL [ EXCEPT nom_rôle [,...] ] | NONE } ;

• Attribution d’un role à un utilisateur Syntaxe ALTER USER nom_utilisateur DEFAULT ROLE { nom_rôle [,...] | ALL [ EXCEPT nom_rôle [,...] ] | NONE } ;

• Limitation des rôles 100

Gestion de Rôle • Les rôles déjà définis

• Plusieurs vues du dictionnaire de données permettent d’obtenir des informations sur les privilèges système   • DBA_SYS_PRIVS / SESSION_PRIVS / SYSTEM_PRIVILEGE_MAP  / DBA_ROLE / DBA_CODE_ROLE_PRIVS 101

Les différents types de comptes • Une base Oracle contient en général quatre types de comptes • Administration Ces privilèges peuvent être obtenus par l’intermédiaire du rôle DBA ou d’un rôle équivalent

• Développement/hébergement du schéma applicatif • A les privilèges système nécessaires pour la création des différents types d’objets (tables, vues, procédures...) • Il possède un quota sur au moins un tablespace. • Les privilèges système requis peuvent être obtenus par l’intermédiaire des rôles CONNECT et RESOURCE ou d’un rôle équivalent

• Utilisateur final • Il a besoin de très peu de privilèges système : CREATE SESSION (obligatoire), ALTER SESSION (parfois nécessaire) et c’est généralement tout. • Par contre, il possède des privilèges objet sur les objets du schéma applicatif, généralement par l’intermédiaire d’un rôle.

• Quelques conseils pour sécuriser l’accès à votre base de données • Limitez les accès au serveur (notamment au fichier de mots de passe, au fichier de paramètres et aux fichiers de trace ou d’alerte de chaque instance Oracle). • Interdisez l’authentification par le système d’exploitation à travers le réseau (le paramètre REMOTE_OS_AUTHENT doit être à FALSE). • Modifiez les mots de passe par défaut des comptes par défaut que vous utilisez (au premier rang desquels SYS et SYSTEM). 102

Superviser les utilisateurs connectés

• La vue V$SESSION permet d’identifier les utilisateurs actuellement connectés

• Les sessions sans valeur dans la colonne USERNAME sont celles des processus d’arrièreplan

• Déconnecter un utilisateur de la base. • Syntaxe ALTER SYSTEM KILL SESSION 'sid,serial#' [IMMEDIATE]; ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' { [POST_TRANSACTION] [IMMEDIATE]; • Exemple ALTER SYSTEM KILL SESSION '10,10494'; 103

Gestion des tables et des Indexes

104

Gestion des tables • Les principaux types d’objets d’un schéma sont • Les tables et les index occupent de l’espace de stockage en dehors de leur définition dans le dictionnaire. • Leur stockage doit être planifié correctement pour éviter les erreurs liées au manque d’espace ou les problèmes de performance.

• Les tables et les index sont des segments ; • le stockage est donc organisé en extensions, piloté par la clause STORAGE et par les caractéristiques du tablespace. • l’organisation du stockage dans les blocs a de l’importance.

• Autres types d’objets qui occupent de l’espace • Vues matérialisées : structure analogue à une table et dont le contenu est périodiquement mis à jour • IOT (Index Organised Table - table organisée en index) : table dont le stockage est organisé dans l’index de la clé primaire de la table. • Clusters : structures qui permettent de stocker physiquement ensemble des tables fréquemment interrogées par jointure • Tables et index partitionnées : l’option partitionnement permet de découper le stockage physique des tables et des index en morceaux plus petits appelés partitions. • Index B-tree: index classique • Index Bitmap : index dont le stockage est organisé différemment des index B-tree et qui est plutôt destiné à l’indexation des colonnes à faible cardinalité dans un environnement décisionnel • Index basé sur des fonctions : index B-tree qui indexe non pas la valeur de la colonne mais le résultat de l’application d’une fonction SQL (UPPER, LOWER, etc.) à la valeur de la colonne 105

Gestion des tables

• Gestion des tables • Organisation du stockage dans les blocs

106

Gestion des tables • Structure du bloc • • • • • •

L’en-tête du bloc contient l’adresse du bloc, le type de segment, un répertoire des tables, un répertoire des lignes et des entrées pour les transactions. La taille de l’en-tête du bloc est variable, de l’ordre de 100 octets à 200 octets. Le reste du bloc contient les données (une à plusieurs lignes de la table) et de l’espace libre.

• Structure d’une ligne • L’en-tête d’une ligne contient quelques informations sur la ligne (nombre de colonnes, chaînage éventuel, verrou). • La taille de l’en-tête de lignes est variable (3 octets minimum). • Ensuite, chaque colonne est stockée avec un en-tête de colonne (qui donne la longueur de la colonne sur 1 à 3 octets) suivi de la valeur de la colonne • La longueur totale d’une ligne dépend: • du nombre de colonnes • la valeur stockée dans chaque colonne, 107 • la longueur de la colonne dépendant du type de données.

Gestion des tables • Types de longueur de colonne

• Une valeur NULL occupe un octet en milieu de ligne et aucun en fin de ligne

• Remarque: • Ce qu’il faut retenir, c’est que le bloc ne contient pas que des données utiles ; il y a des données de contrôle, de surcharge, utilisées en interne par Oracle. À titre d’exemple, une ligne comprenant 3 colonnes stockant 30 octets de données utiles emploiera en moyenne 35 octets d’espace dans le bloc et une ligne comprenant 15 colonnes stockant 145 octets de données utiles emploiera en moyenne 160 octets d’espace dans le bloc. 108

Gestion des tables • Gestion de l’espace dans les blocs L’espace libre à l’intérieur d’un segment peut être géré automatiquement ou manuellement • En gestion manuelle

•

• pour chaque segment, Oracle gère une liste de blocs disponibles pour l’insertion de lignes (freelist). • La disponibilité ou la non du bloc  pour l’insertion est contrôlée par deux paramètres de la définition de la table : PCTFREE/PCTUSED PCPTFREE: spécifie le pourcentage de l’espace du bloc laissé libre pour les modifications des lignes stockées dans le bloc 

109

Gestion des tables •

PCTUSED: spécifie le pourcentage d’occupation auquel le bloc doit redescendre avant de redevenir candidat à l’insertion

• En gestion automatique (Automatic Segment-Space Management - ASSM) • pour chaque segment, Oracle utilise une bitmap afin de connaître le taux de remplissage de chaque bloc alloué au segment • Oracle considère que : 0 % d’espace libre (plein), entre 0 et 25 % d’espace libre, entre 25 et 50 % d’espace libre, entre 50 et 75 % d’espace libre, entre 75 et 100 % d’espace libre • Ainsi lors de l’insertion d’une nouvelle ligne, Oracle utilisera la bitmap pour déterminer dans quel bloc il peut insérer une ligne • Le paramètre PCTUSED est sans objet. • La gestion automatique est apparue en version 9i

110

Gestion des tables • Le ROWID • Le ROWID est une colonne virtuelle présente dans chaque table qui donne : • l’adresse physique de stockage de la ligne. • Avec le ROWID Oracle a toutes les informations nécessaires à la localisation physique de la ligne dans un fichier de données (fichier, numéro de bloc, position dans le bloc) • Cette colonne virtuelle peut être interrogée comme les autres colonnes de la table • permet de localiser physiquement la ligne ; • il est utilisé en interne par Oracle dans les index • c’est le moyen le plus rapide pour accéder à une ligne • Le ROWID d’une ligne ne change jamais tant que la ligne n’est pas supprimée même si la ligne est migrée vers un autre bloc sauf dans les cas particulier • lorsqu’une ligne change de partition dans une table partitionnée ; • réorganisation du stockage de la table (ALTER TABLE MOVE) ; • compactage des lignes dans la table (ALTER TABLE SHRINK SPACE).

111

Gestion des tables • Chaînage et migration En règle générale, une ligne d’une table est stockée en totalité à l’intérieur d’un bloc de sorte qu’Oracle n’a besoin de lire qu’un seul bloc. • Chaînage : Souvent il n’est pas possible pour oracle de stocker une ligne dans un bloc dans ce cas Oracle stocke dans plusieurs bloc. Alors pour lire cette ligne, Oracle a alors besoin de lire plusieurs blocs. • Migration: une ligne grandit suite à une modification, et il ne reste plus suffisamment d’espace libre dans le bloc, alors Oracle déplace la ligne dans un autre bloc pointé par l’en-tête de la ligne resté dans le bloc d’origine. Le phénomène de migration peut (et même doit) être évité, en laissant suffisamment d’espace disponible dans les blocs pour les mises à jour. Le paramètre PCTFREE sera donc positionné avec soin sur les tables pour lesquelles la taille des lignes insérées est sensiblement inférieure à la taille des lignes après modification(s).  

• Spécifier le stockage d’une table • Syntaxe simplifiée CREATE TABLE nom_table (spécification des colonnes)  [ TABLESPACE nom_tablespace ] [ PCTFREE valeur ] [ PCTUSED valeur ]  [ clause_stockage]  [ LOGGING | NOLOGGING ] ;  -clause_stockage  STORAGE ( [ INITIAL valeur [K|M] ]  [ NEXT valeur [K|M] ]            [ MINEXTENTS valeur ]            [ MAXEXTENTS { valeur | UNLIMITED } ]           [

PCTINCREASE valeur ] )

112

Gestion des tables • Exemple CREATE TABLE adherent numero NUMBER(6),     nom         VARCHAR2(40),     prenom      VARCHAR2(30))    TABLESPACE data    PCTFREE 30    STORAGE ( INITIAL 10M ) ;

• Syntaxe simplifiée ALTER TABLE nom_table  [ PCTFREE valeur ]  [ PCTUSED valeur ]  [ LOGGING | NOLOGGING ]   [ clause_stockage_restreinte ] ;  -clause_stockage_restreinte   STORAGE ( [ NEXT valeur [K|M] ]            [ MAXEXTENTS { valeur | UNLIMITED } ]            [ PCTINCREASE valeur ] )

113

Gestion des tables • Recommandations pour la gestion du stockage des tables • stocker les tables dans un ou plusieurs tablespaces dédiés • Utiliser des tablespaces gérés localement avec une gestion automatique de l’espace dans les segments • Utiliser les valeurs de colonnes des tables définies (exemple nomber(X)) à la place de number) • Dans la mesure du possible régler, PCTFREE avec soin • PCTFREE = 100 x (1 - Ti / Tf) • Ti = taille moyenne initiale de la ligne en octets (au moment de l’insertion)  • Tf = taille moyenne finale de la ligne en octets (après les mises à jour).

• allouer un espace initial à la table, adapté à la volumétrie estimée à une échéance donnée, si possible lointaine (6 mois, 1 an, 2 ans ou plus) Rem: Oracle trace le nombre approximatif d’ordres SQL INSERT, UPDATE et DELETE exécutés sur la table afin de pouvoir calculer les statistiques (vue DBA_TAB_MODIFICATIONS) 114

Gestion des tables

Réorganiser le stockage d’une table • Les besoins de réorganisation d’une table sont variés • libérer de l’espace libre au-dessus de la HWM  • Pour chaque table (et plus généralement chaque segment), Oracle connaît le dernier bloc utilisé par la table : c’est la high water mark:HWM • La HWM augmente lors des insertions mais ne diminue pas lors des suppressions 

• améliorer le taux de remplissage des blocs  • corriger un problème de migration et chaînage • réorganiser plus globalement le stockage de la table : changement de tablespace, réduction du nombre d’extensions, changement de PCTFREE, etc

• Plusieurs techniques sont disponible pour réorganiser le stockage d’une table • ordre SQL ALTER TABLE ... DEALLOCATE UNUSED ; • recréer la table / des lignes de la table ; • export/import  • ordre SQL ALTER TABLE ... SHRINK SPACE ; • ordre SQL ALTER TABLE ... MOVE. 115

Gestion des tables • Le tableau suivant résume les techniques envisageables (√) et indique lesquelles sont les mieux adaptées () à tel ou tel besoin :

• Syntaxe • ALTER TABLE nom_table DEALLOCATE UNUSED [ KEEP valeur [K|M] ] ; L’option KEEP indique l’espace à conserver au-dessus de la HWM 116

Gestion des Indexes Un index est une structure définie sur une ou plusieurs colonnes d’une table ; la (les) colonne(s) constitue(nt) la clé de l’index. • L’index permet un accès rapide aux lignes de la table lors d’une recherche basée sur la clé de l’index. • Un index est physiquement et logiquement dépendant de la table. • Il peut être créé/supprimé sans affecter la table de base (sauf impact sur les performances lorsque l’index est supprimé). • Un index nécessite son propre espace de stockage • actualisés à chaque mise à jour (INSERT, UPDATE, DELETE). • Un index peut être unique ou non unique  • Les données sont stockées dans des blocs Oracle d’index comprenant les données proprement dites et des informations de contrôle (en-tête de bloc, en-tête de ligne, etc.). • Pour un index unique, il existe un seul ROWID par valeur de clé ; pour un index non unique, plusieurs ROWID sont possibles pour chaque valeur de clé • Le remplissage du bloc, à la création de l’index uniquement, peut être contrôlé par • le paramètre PCTFREE. Le paramètre PCTFREE est donc important lors de 117 la

Gestion des Indexes

• Le stockage de l’index unique Le stockage d’un index peut être spécifié lors de la création de l’index, dans l’ordre SQL CREATE INDEX • Syntaxe simplifiée CREATE [UNIQUE] INDEX nom_index ON nom_table(liste_colonnes)  [ TABLESPACE nom_tablespace ]  [ PCTFREE valeur ]  [ clause_stockage ]  [ ONLINE ]  [ NOCOMPRESS | COMPRESS [n] ]  [ LOGGING | NOLOGGING ] ;  - clause_stockage  STORAGE ( [ INITIAL valeur [K|M] ]            [ NEXT valeur [K|M] ]           [ MINEXTENTS valeur ]            [ MAXEXTENTS { valeur | UNLIMITED } ]           [ PCTINCREASE valeur ] )

• la clause STORAGE est traitée différemment selon que le tablespace est géré par le dictionnaire ou localement. Dans un tablespace géré localement, seule l’option INITIAL est utile. 118

Gestion des Indexes • Index d’une contrainte de clé primaire ou unique • Le stockage de l’index d’une clé primaire ou unique peut être spécifié lors de la définition de la contrainte grâce à l’option USING INDEX de la clause CONSTRAINT. • Exemple de synthaxes: • Définition des clauses de stockage de l’index ALTER TABLE adherent ADD CONSTRAINT adherent$pk PRIMARY KEY(numero) USING INDEX   TABLESPACE indx   PCTFREE 0   STORAGE (INITIAL 2M) ;

• Spécification d’un index déjà existant : ALTER TABLE adherent ADD CONSTRAINT adherent$uk01 UNIQUE (nom,prenom,telephone) USING INDEX adherent$ix01;

• Création complète de l’index • ALTER TABLE adherent ADD CONSTRAINT adherent$uk01   UNIQUE (nom,prenom,telephone)   USING INDEX ; •  CREATE INDEX adherent$ix01 ON adherent(nom,prenom,telephone)        TABLESPACE indx PCTFREE 25  STORAGE (INITIAL 10M)     ) ;

119

Gestion des Indexes

• Les recommandations: • • • •

S’assurer que les requêtes sont bien écrites L’index n’est pas utilisé dans les cas suivants écriture du type like ‘%XX’ n’utilise pas l’index Lorsqu’une fonction est appliquée à la colonne, ou que la colonne est utilisée dans une expression. • Les recommandations concernant le storage de la table demeure identique.

• Réorganiser le stockage d’un index • • • •

Les besoins de réorganisation d’un index sont variés : libérer de l’espace libre au-dessus de la HWM ; réorganiser un index dont la structure s’est dégradée ; réorganiser plus globalement le stockage de l’index : changement de tablespace, réduction du nombre d’extensions, changement de PCTFREE, etc.

120

Gestion des Indexes • Plusieurs techniques sont à notre disposition pour réorganiser le stockage d’un index : • Ordre SQL ALTER INDEX ... DEALLOCATE UNUSED ; • Ordre SQL ALTER INDEX ... COALESCE ; • Ordre SQL ALTER INDEX ... SHRINK SPACE ; • Ordre SQL ALTER INDEX ... REBUILD. Le tableau suivant résume les techniques envisageables (√) et celles qui sont les mieux SHRIN DEALLOCA REBUIL COALESC adaptées () à tel ou tel besoin : K TE D E

121

Gestion des Indexes Depuis la version 12.2, l’accès au index est automatiquement surveillé par Oracle, ce qui peut permettre de déterminer si les index sont utilisés ou non. Un index non utilisé peut être supprimé pour libérer de l’espace et améliorer les performances des mises à jour • La vue DBA_INDEX_USAGE/V$INDEX_USAGE_INFO affiche des statistiques cumulées sur l’utilisation des index • Les statistiques sont collectées en mémoire et écrites dans le dictionnaire de données toutes les 15 minutes. Si besoin, vous pouvez interroger la colonne LAST_FLUSH_TIME de la vue V$INDEX_USAGE_INFO pour connaître la date et l’heure de la dernière écriture dans le dictionnaire de données (cette vue en elle-même donne des statistiques générales sur la surveillance des index). •Les statistiques et l’optimiseur Oracle • Réorganiser le stockage d’un index est moins compliqué que réorganiser le stockage d’une table car les données ne sont pas affectées et la table est toujours accessible et pleinement opérationnelle. • L’optimiseur Oracle est chargé de déterminer le plan d’exécution des requêtes, c’est-à-dire la manière dont Oracle va exécuter la requête. • Depuis maintenant plusieurs versions, Oracle recommande de faire fonctionner l’optimiseur dans le mode CBO (Cost Based Optimizer - Optimiseur basé sur les coûts). Depuis la version 10, seul le mode CBO est supporté ; le mode RBO (Rule Based Optimizer - Optimiseur basé sur les règles) n’est plus supporté.

122

Tablespaces et fichiers de données • Un tablespace est une unité logique de stockage composée d’un ou plusieurs fichiers physiques (fichiers de données). • La majorité des opérations d’administration relatives au stockage s’effectue au niveau du tablespace, et non au niveau des fichiers de données. • À l’intérieur d’un tablespace, le stockage est organisé en segments, composés d’une ou de plusieurs extensions (extent). • Ces extensions peuvent être gérées "par le dictionnaire" ou "localement". • Dans le premier cas (tablespace géré par le dictionnaire), les informations sur les extensions libres et allouées sont stockées dans des tables du dictionnaire de données ; dans le second cas (tablespace géré localement), les informations sur les extensions libres et allouées sont stockées dans l’en-tête des fichiers de données du tablespace. • En version 10, Oracle a introduit la notion de tablespace BIGFILE : un tablespace BIGFILE est un tablespace composé d’un seul fichier de données qui peut être particulièrement volumineux (jusqu’à 2^32 - 3 blocs Oracle soit plus de 4 milliards de blocs). A contrario, un tablespace traditionnel, dorénavant appelé tablespace SMALLFILE • Une base de données possède toujours au minimum deux tablespaces nommés SYSTEM et SYSAUX,USERS,  • Un tablespace peut être READ WRITE (en lecture/écriture) ou READ ONLY (en lecture seule). Rendre un tablespace READ ONLY est un moyen simple de garantir que les données qu’il contient ne seront jamais modifiées • Le tablespace est l’unité de base de nombreuses tâches d’administration. La règle fondamentale est donc d’utiliser plusieurs tablespaces pour séparer au maximum les différents types d’éléments et garantir une plus grande souplesse dans les opérations d’administration. 123

Tablespaces et fichiers de données Les principales directives sur l’organisation des tablespaces sont les suivantes : ne pas mettre de données utilisateur dans les tablespaces SYSTEM et SYSAUX ; en plus des tablespaces SYSTEM et SYSAUX, créer au minimum : un tablespace pour les segments d’annulation (tablespace d’annulation) ; un tablespace pour les segments temporaires (tablespace temporaire) ; un tablespace pour les tables ; un tablespace pour les indexes. si possible, répartir les fichiers de données de ces tablespaces sur des disques différents. Toutes les opérations relatives aux tablespaces et aux fichiers de données sont enregistrées dans le fichier d’alerte de l’instance. 124

Tablespaces et fichiers de données • Syntaxe simplifiée CREATE [ BIGFILE | SMALLFILE ] TABLESPACE nom  DATAFILE spécification_fichier [,...]  [ clause_gestion_extension ]  [ clause_gestion_segment ]  [ BLOCKSIZE valeur [K] ]  [ LOGGING | NOLOGGING ]  [ FORCE LOGGING ] [ FLASHBACK { ON | OFF } ]  [ ONLINE | OFFLINE ] ;        spécification_fichier 'nom_fichier' [ SIZE valeur [K|M|G|T] ] [REUSE] [ clause_auto_extension ]         - clause_auto_extension AUTOEXTEND OFF  AUTOEXTEND ON [ NEXT valeur [K|M|G|T] ]  [ MAXSIZE UNLIMITED | valeur [K|M|G|T] ]         - clause_gestion_extent EXTENT MANAGEMENT DICTIONARY | EXTENT MANAGEMENT LOCAL   { AUTOALLOCATE | UNIFORM [ SIZE valeur [K|M|G|T] ] }         • - clause_gestion_segment SEGMENT SPACE MANAGEMENT { MANUAL | AUTO }

125

Tablespaces et fichiers de données

• Exemple • Tablespace pour les tables, avec une gestion locale uniforme des extensions : CREATE TABLESPACE data  DATAFILE ‘/dba/data/Campus/campus_data01.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 800M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M SEGMENT SPACE MANAGEMENT AUTO;

• Tablespace pour les index, avec une gestion locale automatique des extensions : CREATE TABLESPACE indx  DATAFILE 'e:\app\oracle\oradata\hermes\indx01.dbf' SIZE 500M  AUTOEXTEND ON NEXT 100M MAXSIZE 800M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO; 126

Tablespaces et fichiers de données • Modification sur un tablespace tablespace • Après création, il est possible de modifier un tablespace, notamment pour : • le renommer ; • lui allouer de l’espace supplémentaire ; • ALTER TABLESPACE nom ADD DATAFILE spécification_fichier [,...];

• • • •

déplacer le(s) fichier(s) de données ; le passer OFFLINE / ONLINE ; le passer READ ONLY / READ WRITE ; Renommer un tablespace s’effectue avec l’ordre SQL ALTER TABLESPACE. • ALTER TABLESPACE ancien_nom RENAME TO nouveau_nom;

• modifier ses autres caractéristiques (LOGGING / NOLOGGING, FORCE LOGGING, FLASHBACK ON / OFF, etc.). •Ces opérations s’effectuent selon les cas avec les ordres SQL ALTER TABLESPACE ou ALTER DATABASE.

•

127

Tablespaces et fichiers de données Organisation du stockage à l’intérieur d’un tablespace L’organisation du stockage à l’intérieur d’un tablespace peut être résumée par le schéma ci-après.

128

Tablespaces et fichiers de données • À l’intérieur d’un tablespace, le stockage est organisé en segments contenant une ou plusieurs extensions (extents), une extension étant un ensemble de blocs Oracle contigus. • Lorsqu’un segment est créé dans un tablespace, Oracle lui alloue une ou plusieurs extensions dans un des fichiers de données du tablespace. Lorsque l’espace initialement alloué est plein (suite à l’insertion de données par exemple), Oracle alloue une nouvelle extension au segment, et ainsi de suite • Toutes les extensions allouées à un segment sont dans le tablespace de création du segment, mais pas obligatoirement côte à côte, ni forcément dans le même fichier de données (si le tablespace est composé de plusieurs fichiers de données). Lorsqu’un segment est supprimé, les extensions qu’il occupe sont libérées et rendues disponibles pour d’autres segments. Des créations/suppressions fréquentes de segments dans un tablespace peuvent donc conduire à une fragmentation de l’espace disponible dans ce tablespace. • il existe quatre types principaux de segments : • les segments de table : espace occupé par les tables ; • les segments d’index : espace occupé par les index ; • les segments d’annulation : espace temporaire utilisé pour stocker les informations permettant d’annuler une transaction ; • les segments temporaires : espace temporaire utilisé notamment lors d’un tri.

129

Tablespaces et fichiers de données • La première extension d’un segment contient au minimum deux blocs, le premier étant réservé à l’en-tête du segment (ne contient pas de données utiles mais la carte des extensions allouées au segment). Il en est de même pour chaque fichier de données du tablespace ; le premier bloc est un bloc d’en-tête (nous verrons bientôt que l’en-tête du fichier peut contenir davantage de blocs). • Un tablespace peut être "géré par le dictionnaire" ou "géré localement". • Dans un tablespace "géré par le dictionnaire", les informations relatives à la gestion de l’espace (extensions libres/allouées) sont enregistrées dans le dictionnaire de données. • Dans un tablespace "géré localement", les informations relatives à la gestion de l’espace (extensions libres/allouées) sont enregistrées dans une bitmap, dans l’en-tête de chaque fichier de données du tablespace. Chaque bit de la bitmap correspond à une extension et vaut 0 ou 1 selon que l’extension est libre ou allouée. • Par défaut, les tablespaces sont gérés localement. • Une gestion dite "automatique" : la taille des extensions est déterminée automatiquement par Oracle. • Une gestion dite "uniforme" : la taille des extensions est uniforme, égale à une valeur définie lors de la création du tablespace. • Par défaut, un tablespace permanent géré localement est en gestion automatique des extensions ; la gestion uniforme doit être spécifiée.

• Un tablespace temporaire géré localement est obligatoirement en gestion uniforme des extensions (détaillé ultérieurement dans la section Tablespace temporaire de ce chapitre). 130

Tablespaces et fichiers de données • La clause EXTENT MANAGEMENT de l’ordre SQL CREATE TABLESPACE permet de spécifier le mode de gestion d’un tablespace. • Syntaxe EXTENT MANAGEMENT   DICTIONARY   | LOCAL [ AUTOALLOCATE | UNIFORM [ SIZE valeur [K|M] ] ] • exemples • Tablespace géré localement avec des extensions uniformes :

•   

CREATE TABLESPACE tbs_local_uniform     DATAFILE ‘/dba/data/CAMPUS/tbs_local_uniform.dbf‘ SIZE 10M     EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; • Tablespace géré localement avec des extensions gérées par Oracle : CREATE TABLESPACE tbs_local_auto  DATAFILE ' /dba/data/CAMPUS/tbs_local_uniform.dbf’ SIZE 10M  EXTENT MANAGEMENT LOCAL AUTOALLOCATE;    -- équivalent à :    • CREATE TABLESPACE tbs_local_auto DATAFILE ' ‘/dba/data/CAMPUS/tbs_local_uniform.dbf ' SIZE 10M ;

131

Tablespaces et fichiers de données • Les clauses TABLESPACE et STORAGE peuvent être utilisées dans les ordres de création des segments pour spécifier le stockage du segment. • Syntaxe de la clause STORAGE STORAGE ( [ INITIAL valeur [K|M|G] ]  [ NEXT valeur [K|M|G] ]  [ MINEXTENTS valeur ] [ MAXEXTENTS { valeur | UNLIMITED } ]  [ PCTINCREASE valeur ] )

• Dans le cas d’un tablespace géré localement, la clause STORAGE a beaucoup moins d’importance qu’avec un tablespace géré par le dictionnaire. • De par sa définition, le tablespace impose des contraintes sur la taille des extensions (taille choisie par Oracle ou taille uniforme). • Dans la pratique, seule l’option INITIAL a réellement de l’importance puisqu’elle indique à Oracle la taille initiale souhaitée pour le segment. Plus généralement, Oracle tiendra aussi compte de la présence éventuelle des clauses NEXT, MINEXTENTS et PCTINCREASE pour déterminer la taille initiale du segment et allouer des extensions en conséquence.

132

Tablespaces et fichiers de données • Gestion des extensions à l’intérieur d’un tablespace géré localement • Dans le cas d’une gestion automatique des extensions, Oracle utilise un petit nombre de tailles d’extension différentes (64 Ko, 1 Mo, 8 Mo, 64 Mo) et tente d’allouer côte à côte des extensions de même taille, en nombre suffisant pour occuper de l’espace potentiellement utilisable pour une extension de taille supérieure (16 extensions de 64 Ko = une extension potentielle de 1 Mo). Cette technique permet de limiter les risques de fragmentation de l’espace disponible : si un segment contenant de nombreuses extensions est supprimé, l’espace libéré peut être réutilisé de différentes manières. La taille d’extension initialement choisie pour un segment dépend de la taille initiale du segment (pour une taille de bloc de 8 Ko) • Dans le cas d’une gestion uniforme des extensions toutes les extensions ont la même taille définie par l’option SIZE de la clause EXTENT MANAGEMENT (1 Mo par défaut). • Remarque En Enterprise Edition, par défaut, lors de la création d’une table dans un tablespace géré localement, Oracle diffère la création du segment associé jusqu’au moment où la première ligne est insérée dans la table. Dans ce cas, la table apparaît bien dans le dictionnaire de données (vue *_TABLES) mais pas le segment ni les extensions (vues *_SEGMENTS et *_EXTENTS). Cette fonctionnalité permet d’économiser de l’espace et d’accélérer la création des tables pour une application qui comporte plusieurs milliers de tables dont un grand nombre pourraient ne jamais être alimentées. Voir le chapitre Gestion des tables et des index. 133

Tablespaces et fichiers de données • Tablespace temporaire • Lorsqu’une requête nécessite un tri (clause ORDER BY par exemple), Oracle tente de faire le tri en mémoire, dans la PGA du processus serveur qui exécute la requête. • Si le tri ne tient pas en mémoire, Oracle le découpe en morceaux et trie chaque morceau individuellement en stockant des résultats intermédiaires sur disque dans des segments temporaires. • Un segment temporaire peut être créé dans n’importe quel tablespace mais ce n’est pas souhaitable pour les performances. • Oracle recommande donc de créer un tablespace dédié, de type TEMPORARY, pour stocker les segments temporaires, et de préférence un tablespace temporaire géré localement.  • Il est possible de créer un tablespace temporaire géré par le dictionnaire mais les performances sont alors limitées et ce choix est déprécié par Oracle  • Syntaxe CREATE [ BIGFILE | SMALLFILE ] TEMPORARY TABLESPACE nom TEMPFILE spécification_fichier [,...]  [ EXTENT MANAGEMENT LOCAL ] [ UNIFORM [ SIZE valeur [K|M] ] ] [ TABLESPACE GROUP nom_groupe ] ;   - spécification_fichier 'nom_fichier' [ SIZE valeur [K|M|G|T] ] [REUSE] [ clause_auto_extend ]        clause_auto_extend AUTOEXTEND { OFF | ON [ NEXT valeur [K|M|G|T] ]  [ MAXSIZE { UNLIMITED | valeur [K|M|G|T] } ] }      134

Gestion des informations d’annulation • Lorsque des modifications de données sont en cours, Oracle génère des informations d’annulation qui seront utilisées, si nécessaire, pour annuler les modifications. Ces informations d’annulation contiennent essentiellement la valeur précédente des données qui sont modifiées par la transaction ("image avant", "before image") et l’identification des blocs concernés. • • • • • •

Les informations d’annulation sont stockées dans des segments d’annulation. Elles sont principalement utilisées pour : l’annulation de la transaction (ROLLBACK) ; la lecture cohérente ; certaines fonctionnalités de flashback ; la récupération de la base de données (RECOVER).

• Le segment d’annulation est une structure utilisée par Oracle pour stocker temporairement la version précédente des données en cours de modification dans une transaction. • Si la transaction est validée (COMMIT), l’espace occupé est libéré ; si la transaction est annulée (ROLLBACK), la version précédente des données est remise à la place de la nouvelle. • Les segments d’annulation sont aussi utilisés par certaines fonctionnalités de flashback qui permettent de lire (et récupérer) les données telles qu’elles étaient à un instant donné dans le passé (voir chapitre Sauvegarde et récupération). 135

Gestion des informations d’annulation • Durée de rétention des informations d’annulation • Lorsqu’une transaction se termine, les informations d’annulation la concernant ne sont plus nécessaires pour effectuer une annulation de la transaction (ROLLBACK). Par contre, ces informations peuvent encore être utiles pour une lecture cohérente. • En effet, une lecture cohérente longue qui a commencé avant ou pendant une transaction peut encore avoir besoin de l’image avant, après la fin de la transaction. Si cette image avant ancienne n’est plus disponible (l’espace a été réutilisé par une autre transaction), une erreur ORA-01555 (la "fameuse" erreur snapshot too old) est retournée. • Avec la gestion automatique de l’annulation, il existe une durée de conservation (rétention) des informations d’annulation (undo retention period)  • c’est la durée minimale pendant laquelle Oracle tente de conserver les informations d’annulation des transactions validées, avant de réutiliser l’espace. • Les informations d’annulation des transactions validées sont dites expirées (expired) si elles sont plus anciennes que la période de conservation actuelle • l’espace qu’elles occupent peut être réutilisé par de nouvelles transactions.

• À l’inverse, les informations d’annulation des transactions validées sont dites non expirées (unexpired) si elles sont plus récentes que la période de conservation actuelle et Oracle tente de préserver l’espace qu’elles occupent pour satisfaire les lectures cohérentes et les opérations flashback. 136

Gestion des informations d’annulation • Si le tablespace d’annulation est autoextensible, Oracle règle la durée de conservation à une valeur légèrement supérieure à la durée de la requête la plus longue. Dans ce cas, si le paramètre UNDO_RETENTION est défini, il impose une valeur minimum • Si le tablespace d’annulation est de taille fixe, • Oracle règle la durée de conservation à la plus grande valeur possible, compte tenu de la taille du tablespace et de l’activité de la base de données. Dans ce cas, si le paramètre UNDO_RETENTION est défini, il est ignoré (sauf si le tablespace d’annulation est défini avec la garantie de rétention). • Il faut noter que pour le calcul, Oracle utilise le seuil d’alerte du remplissage du tablespace d’annulation (85 % par défaut) et non 100 % de la taille du tablespace. • Modifier la taille du tablespace d’annulation ou son seuil d’alerte a donc, un impact direct sur la durée de conservation.

137

Sauvegarde et récupération logique

138

Sauvegarde et restauration logique • Data Pump Export : permet d’exporter dans un fichier binaire propriétaire tout ou une partie des objets (structure et/ou données) d’une base de données. • Data Pump Import : permet d’importer dans une base de données tout ou une partie des objets (structure et/ou données) préalablement exportés par l’outil Data Pump Export. • Caractéristique • plus rapides ; • possibilité d’arrêter un travail Data Pump, puis de le redémarrer ; • possibilité de détacher sa session d’un travail Data Pump puis de la rattacher ultérieurement ; • possibilité de faire des transferts directs, à travers le réseau, entre deux bases de données ; • plus de finesse dans la sélection des objets à exporter ou importer ; • possibilité d’avoir une estimation de l’espace nécessaire lors d’un export ; • possibilité de paralléliser une opération ou de compresser le fichier d’export (Enterprise Edition uniquement). •Nécessite l’existance d’un Directory et utilise des package PL/SQL (DBMS_DATAPUMP/DBMS_METADATA) •CREATE[ OR REPLACE ] DIRECTORY nom_directory AS 'chemin' ; • GRANT privilège [,...] ON DIRECTORY nom_directory TO utilisateur [,...] ; 139

Sauvegarde et récupération Une des tâches principales de l’administrateur est d’assurer sécurité est assurée des données : • la mise en œuvre d’une protection des fichiers sensibles de la base notament: • • • •

fichiers de contrôle ; fichiers de journalisation. Par la mise en place d’une stratégie de sauvegarde/restauration adaptée aux contraintes de l’entreprise testée et documentée.

• La protection des fichiers de contrôle et des fichiers de journalisation s’effectue par multiplexage

• La principale question à se poser est: • • • •

• 

Quelle politique de sauvegarde dois je mettre en place Est-il acceptable de perdre des données ? Est-il possible d’arrêter périodiquement la base ? Est-il possible de réaliser une sauvegarde complète de la base pendant l’arrêt ?

140

Sauvegarde et récupération physique

141

Sauvegarde et récupération • L’archivage des fichiers de journalisation • Le fichiers de journalisation peuvent être réappliqués à une sauvegarde de fichier de données, pour rejouer toutes les modifications survenues entre la sauvegarde et un incident ayant endommagé le fichier (restauration de média), • à condition d’avoir conservé tous les fichiers de journalisation • ceci est possible en faisant fonctionner la base de données en mode ARCHIVELOG. • Ce mode de fonctionnement permet de garantir zéro perte de données en cas d’incident sur un fichier de données.

142

Sauvegarde et récupération • Pour effectuer des sauvegardes et des récupérations, il y a deux possibilités • Sauvegarder régulièrement les fichiers de données base arrêtée. • RMAN (Recovery Manager) •Il est important de définir la stratégie de sauvegarde qui peut être cohérente ou incohérente: •Une sauvegarde cohérente est une sauvegarde de la totalité de la base de données après un arrêt propre de la base de données (pas après un SHUTDOWN ABORT ou un arrêt anormal de l’instance) ; ce type de sauvegarde est aussi souvent appelé "sauvegarde base fermée" ou "sauvegarde à froid". •Une sauvegarde incohérente est une sauvegarde effectuée alors que la base de données est ouverte et que l’activité de mise à jour se poursuit pendant la sauvegarde ; ce type de sauvegarde est aussi souvent appelé "sauvegarde base ouverte" ou "sauvegarde à chaud". Les fichiers sauvegardés ne sont pas synchrones du point de vue des modifications enregistrées. Lorsqu’une base de données est restaurée à partir d’une sauvegarde incohérente, il faut appliquer les fichiers de journalisation pour rendre les fichiers cohérents. Les sauvegardes incohérentes ne sont possibles que lorsque la base de données fonctionne en mode ARCHIVELOG. •Une sauvegarde Incohérente peut être  complète, partielle ou incrémentale •complète est une sauvegarde de la totalité de la base de données. •partielle est une sauvegarde incluant uniquement une partie de la base de données. •incrémentale est une sauvegarde qui ne contient que les blocs modifiés depuis la dernière sauvegarde  •une sauvegarde incrémentale peut être complète ou partielle 143

Sauvegarde et récupération • stratégie en fonction du mode de fonctionnement de la base  

• réaliser des sauvegardes fréquentes (au minimum tous les jours) et de conserver plusieurs cycles de sauvegarde (en cas de problème avec une sauvegarde) • Si la base de données fonctionne en mode ARCHIVELOG, vous pouvez réaliser des sauvegardes bases ouvertes  • Dans le cas de sauvegardes partielles il faut être simplement très rigoureux dans le suivi et veiller de à faire une sauvegarder sur un cycle complet de sauvegardes partielles. 144

Sauvegarde et récupération • Archivage des fichiers de journalisation • La base doit être en mode archivelogue • Si la base est créée en mode sans archivage on peut la repasser en archive. • Définir l’emplacement et nom des fichiers d’arch • ALTER SYSTEM SET log_archive_format='redo_%S_%R_%T.arc‘ SCOPE=SPFILE;  •  ALTER SYSTEM SET  log_archive_dest_1='LOCATION=/dba/arch/CAMPUS/arch1‘ SCOPE=SPFILE; • Shutdown immediate • Startup mount • Alter database archivelog • Alter database open; • Le mode ARCHIVELOG/NOARCHIVELOG est une propriété de la base qui se modifie par l’ordre SQL ALTER DATABASE. • Ce mode de fonctionnement est mémorisé dans le fichier de contrôle de la base de données il n’est pas nécessaire de le repréciser à chaque démarrage.

•Le format doit inclure les variables suivantes : •%s ou %S Numéro de séquence du fichier de journalisation. •%t ou %T Numéro d’instance (thread). •%r ou %R Identifiant de remise à zéro des fichiers de journalisation (voir la section Récupération). • Si ces trois variables ne sont pas présentes dans le format, l’erreur suivante se produit lors du démarrage de l’instance : ORA-19905: log_archive_format doit contenir %s, %t et %r

145

Sauvegarde et récupération • Le paramètre LOG_ARCHIVE_DEST définit une première destination de l’archivage et le paramètre LOG_ARCHIVE_DUPLEX_DEST une deuxième destination d’archivage (dupliquée). (uniquement en Standard Edition). • En Enterprise Edition, il faut utiliser les paramètres LOG_ARCHIVE_DEST_n ; • pour des raisons de compatibilité ascendante, ils peuvent néanmoins encore être utilisés si aucun paramètre LOG_ARCHIVE_DEST_n n’est défini • les paramètres LOG_ARCHIVE_[DUPLEX_]DEST et LOG_ARCHIVE_DEST_n sont incompatibles et ne peuvent pas être définis simultanément. • Si aucune destination d’archivage n’est définie, mais qu’une zone de récupération rapide  est configurée (paramètre DB_RECOVERY_FILE_DEST), la zone de récupération rapide est utilisée comme destination d’archivage • ARCHIVE_LAG_TARGET: permet de définir une durée maximale en secondes entre deux archivages Les valeurs autorisées sont comprises entre 1mn(60s) et 2h(7200s) • S’il n’y a rien à archiver, Oracle ne génère pas d’archive c’est-à-dire pas de ARCHIVE_LAG_TARGET de définie ou à 0

• Trouver des informations sur l’archivage • ARCHIVE LOG LIST • Les vues V$DATABASE  / V$LOG  / V$ARCHIVED_LOG  / V$ARCHIVE_DEST    • L’archivage peut être bloqué lorsqu’il y a un problème avec la destination d’archivage : • •

plus d’espace disponible ; destination inaccessible.

• Cela peut conduire à un blocage de LGWR si tous les fichiers de journalisation en ligne ont besoin d’être archivés.

146

RMAN (Recovery Manager)

147

RMAN (Recovery Manager) / Backup • RMAN est un outil en ligne de commande qui permet de réaliser des sauvegardes et des récupérations d’une base de données appelée base de données cible 

• Il utilise un référentiel (repository) pour stocker des informations sur: sa configuration, les sauvegardes réalisées, la structure de la base cible, les fichiers de journalisation archivés, etc. • Ce référentiel est toujours stocké dans le fichier de contrôle de la base cible. La durée de conservation des informations dans le fichier de contrôle est déterminée par le paramètre d’initialisation CONTROL_FILE_RECORD_KEEP_TIME (7 jours par défaut).  • Le référentiel peut aussi être stocké dans un catalogue de récupération (recovery catalog) qui se matérialise par un schéma dans une autre base de données. Un seul catalogue de récupération peut être utilisé pour centraliser les référentiels RMAN de plusieurs bases de données cibles • Lorsque RMAN n’est pas utiliser en mode catalogue , l’existence du fichier de contrôle est important, en cas de perte du fichier de contrôle, RMAN n’aura plus d’information sur les sauvegarde de la base. • Si fast recovery area est configuré avec les paramètres DB_RECOVERY_FILE_DEST et DB_RECOVERY_FILE_DEST_SIZE alors RMAN permet une sauvegarde / restauration automatiques sur disque • Pour chaque sauvegarde restauration il s’établie un canal entre le RMAN et le client (channel) • Une sauvegarde RMAN peut se faire sous la forme d’une copie image (image copy) ou d’un jeu de sauvegarde (backup set). • •

Une copie image est une copie à l’identique du fichier (analogue à une copie par une commande du système d’exploitation) 148 Un jeu de sauvegarde contient un ou plusieurs fichiers sauvegardés

RMAN (Recovery Manager) / Backup • RMAN offre un très grand nombre de fonctionnalités et d’options et peut être utilisé de différentes manières. • Syntaxe rman [liste_options] • TARGET [=] Chaîne de connexion à la base de données cible. • CATALOG [=] Chaîne de connexion à la base de données du catalogue de récupération. • CMDFILE [=] Chemin vers un fichier contenant des commandes RMAN à exécuter. • LOG [=] Chemin vers un fichier journal ou log de l’activité RMAN. • APPEND Indique que le fichier journal ou log doit être ouvert en mode ajout. • USING valeur [...] Définit une ou plusieurs valeurs pour des variables de substitution qui peuvent être utilisées dans un fichier de commandes RMAN. Dans le fichier de commande RMAN, les variables de substitution sont définies par la syntaxe &n (éventuellement suivie d’un point), n étant un entier.

• Exemple: rman target / rman target sys/wX#12@campus • Les variables d’environnement comme NLS_DATE_FORMAT et NLS_LANG influent aussi sur le format des dates et la langue des messages affichés par RMAN. 149

RMAN (Recovery Manager) / Backup • Les commandes suivantes peuvent être utilisées dans RMAN : • @fichier: Exécute un fichier de commande. • @@fichier: Exécute un fichier de commande dans le même répertoire que le fichier de commande actuel. • SET ECHO ON | OFF Active ou désactive l’écho des commandes. • SPOOL LOG TO fichier [APPEND]: Écrit la sortie RMAN dans un fichier. • SPOOL LOG OFF

• Les commande de sqlplus d’arrêt / redémarrage d’instance sont également disponible sous RMAN • il est possible de grouper des commandes RMAN dans un bloc délimité par des accolades et d’exécuter ce bloc avec la commande RUN : • RUN { ... } • Show all: permet d’afficher la configuration d’RMAN 150

RMAN (Recovery Manager) / Backup Exemple RMAN> SHOW ALL ; les paramètres de configuration RMAN de la base de données ayant le db_unique_name HERMES sont les suivants : CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/oracle/prouct/19.0.0/PROD /database/snapCAMPUS.ora #defaultCONFIGURE

151

RMAN (Recovery Manager) / Backup • Exemple de configuration: • La commande « configure" permet de modifier les paramètres de configuration permanant CONFIGURE CHANNEL DEVICE TYPE DISK options ; • • •

La clause options peut prendre une ou plusieurs valeurs dont : FORMAT 'format' Chemin et format de nom de fichier pour la sauvegarde. MAXPIECESIZE taille [K|M|G] Taille maximale des éléments de sauvegarde. Aucune limite par défaut

• Exemple CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/dba/backup/%U‘ MAXPIECESIZE 2G ;

152

RMAN (Recovery Manager) / Backup %d Nom de la base de données. %I Identifiant de la base de données (DBID). %a Numéro d’activation de la base de données. %N Nom du tablespace. %f Numéro de fichier de données. %e Numéro de séquence du fichier de journalisation archivé. %h Numéro d’instance (thread) du fichier de journalisation archivé. %s Numéro du jeu de sauvegarde (backup set). %p Numéro de l’élément de sauvegarde (backup piece) à l’intérieur d’un jeu de sauvegarde. %c Numéro de copie de l’élément de sauvegarde (cas d’une sauvegarde multiplexée). 1 si la sauvegarde n’est pas multiplexée. %u Chaîne unique de 8 caractères basée sur le numéro du jeu de sauvegarde ou de la copie image incluant date/heure de la sauvegarde/copie. %D Numéro du jour dans le mois sur deux chiffres. %M Numéro du mois sur deux chiffres. %Y Année sur quatre chiffres. %T Date au format YYYYMMDD (équivalent à %Y%M%D). 153

RMAN (Recovery Manager) / Backup • Définir une politique de conservation • La politique de conservation peut être définie en termes de fenêtre de restauration ou de redondance. •

Fenêtre de restauration = nombre de jours auxquels on souhaite revenir en arrière. CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF n DAYS ; •

Redondance = nombre de jeux du même fichier de sauvegarde qu’on souhaite garder. CONFIGURE RETENTION POLICY TO REDUNDANCY n ; •

Réinitialisation des paramètres de configuration par défaut: CONFIGURE RETENTION POLICY CLEAR ;

• Les règles de nommage des fichiers gérés par Oracle sont les suivantes :

154

RMAN (Recovery Manager) / Backup Avec: nom du tablespace.

chaîne de 8 caractères qui garantit l’unicité.

numéro de groupe pour les fichiers de journalisation.

numéro d’instance (thread) pour les fichiers de journalisation archivés. numéro de séquence pour les fichiers de journalisation archivés.

nom donné à la sauvegarde (option TAG de la commande BACKUP).

chaîne de 5 caractères correspondant au contenu du jeu de sauvegarde.

horodatage de la sauvegarde automatique (nombre de secondes écoulées depuis une date interne fixe).  Rman effectue des sauvegardes des fichiers de données dans un jeu de sauvegarde mais ne n’effectue jamais de sauvegarde par blocs

155

RMAN (Recovery Manager) / Backup • Les commandes • Validate: est utilisée dans différentes situations (à titre préventif, avant une sauvegarde, avant une restauration) Database / tablespace / datafile / current controlfile / spfile / archivelog all / backupset liste_clés / recovery area • Exemples • VALIDATE DATABASE ;  • VALIDATE DATAFILE 1,‘/dba/data/CAMPUS/campus_data_01' ; 

•Sauvegarde / BACKUP • Exemple RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE TAG='DBF'; RMAN> BACKUP BACKUP BACKUP BACKUP

 

backup database; backup validate database; TABLESPACE data,indx INCLUDE CURRENT CONTROLFILE ;  CURRENT CONTROLFILE ;  AS COPY CURRENT CONTROLFILE ;  SPFILE ;

•Le fichier de contrôle et le fichier de paramètre serveur sont sauvegardés dans un jeu de sauvegarde séparé.

156

RMAN (Recovery Manager) / Backup • Exemple • • • • •

BACKUP BACKUP BACKUP BACKUP BACKUP

ARCHIVELOG ALL ;  ARCHIVELOG FROM TIME 'SYSDATE-1' DELETE ALL INPUT ;  AS COMPRESSED BACKUPSET ARCHIVELOG FROM TIME 'SYSDATE-7‘NOT BACKED UP 2 TIMES ;  DATABASE PLUS ARCHIVELOG ;  DATABASE PLUS ARCHIVELOG DELETE ALL INPUT ;

• Sauvegarde incrémentale • Pour réaliser une sauvegarde incrémentale, il suffit d’inclure l’option INCREMENTAL LEVEL n [CUMULATIVE] dans la commande BACKUP.

• Exemple • BACKUP INCREMENTAL LEVEL 0 DATABASE TAG='dbinc0' ;  • BACKUP INCREMENTAL LEVEL 1 DATABASE TAG='dbinc1' ; • Une sauvegarde incrémentale de niveau 0 sauvegarde toujours tous les blocs utilisés des fichiers de données • Une sauvegarde incrémentale différentielle de niveau 1 sauvegarde tous les blocs modifiés depuis la dernière sauvegarde incrémentale de niveau 0 ou 1

157

RMAN (Recovery Manager) / Backup

• Une sauvegarde incrémentale cumulative de niveau 1 sauvegarde tous les blocs modifiés depuis la dernière sauvegarde incrémentale de niveau 0.

• Les sauvegardes incrémentales cumulatives sont plus intéressantes pour la rapidité de récupération (moins de sauvegardes intermédiaires à appliquer) mais nécessitent plus d’espace disque. • Une sauvegarde incrémentale de niveau 0 peut être effectuée sous la forme d’une copie image (BACKUP AS COPY INCREMENTAL LEVEL 0) alors qu’une sauvegarde incrémentale de niveau 1 est forcément réalisée sous la forme d’un jeu de sauvegardes. 158

RMAN (Recovery Manager) / Backup • Sauvegarde complète base fermée (cohérente) • BACKUP DATABASE;

• Sauvegarde complète base ouverte (incohérente) • BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;

• Sauvegarde partielle base ouverte • BACKUP DATAFILE 1,2 PLUS ARCHIVELOG DELETE ALL INPUT; Sauvegarde les fichiers de données 1 et 2 • BACKUP DATAFILE 3,4 PLUS ARCHIVELOG DELETE ALL INPUT; Sauvegarde les fichiers de données 3 et 4 • BACKUP DATABASE NOT BACKED UP SINCE TIME='SYSDATE-3' PLUS ARCHIVELOG DELETE ALL INPUT; Sauvegarde tous les fichiers qui n’ont pas été sauvegarder depuis 3 jours y compris tous nouveaux fichiers de données

159

RMAN (Recovery Manager) / Backup • Sauvegardes incrémentales cumulatives sont réalisées sur un cycle d’une semaine • Dimanche : sauvegarde incrémentale de niveau 0 • BACKUP INCREMENTAL LEVEL 0 DATABASE ;

• Lundi au samedi : sauvegarde incrémentale cumulative de niveau 1 • BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE ;

• Il faut une sauvegarde des archivelogue • Les commandes LIST BACKUP, LIST COPY et LIST BACKUPSET listent tous les éléments enregistrés dans le référentiel RMAN. • Exemple • LIST BACKUP OF DATABASE ; # n'importe quel fichier de la base  • LIST BACKUP OF DATAFILE 1,‘/u01/backup/CAMPUS/DATA01.DBF' ; •  LIST BACKUP OF TABLESPACE system,sysaux ;  • LIST BACKUP OF CONTROLFILE SPFILE ; LIST BACKUP OF ARCHIVELOG ALL ;  • LIST BACKUP OF ARCHIVELOG UNTIL TIME 'SYSDATE-1' ;

• La commande REPORT permet: • lister les éléments qui nécessitent une sauvegarde ; • lister les sauvegardes obsolètes ; • afficher la liste des fichiers de données de la base de données.

160

RMAN (Recovery Manager) / Backup • Syntaxe REPORT NEED BACKUP [condition] [objets];

- condition DAYS [=] n INCREMENTAL [=] n RECOVERY WINDOW OF n DAYS REDUNDANCY [=] n

- objets DATABASE DATAFILE liste_numéros_ou_noms TABLESPACE liste_noms • La commande REPORT NEED BACKUP affiche la liste des fichiers qui nécessitent une sauvegarde par défaut, par rapport à la politique de sauvegarde configurée (CONFIGURE RETENTION POLICY). • La commande REPORT OBSOLETE affiche liste des sauvegardes obsolètes en tenant compte de la politique de conservation configurée (CONFIGURE RETENTION POLICY).

• Condition: permet de spécifier le critère à prendre en compte dans la commande REPORT afin de déterminer si un fichier doit d’être sauvegardé.

• REPORT SCHEMA: Liste les fichiers de données de la base de données

161

RMAN (Recovery Manager) / Backup • La commande CROSSCHECK:  Permet de vérifier que les informations enregistrer dans RMAN correspond bien à ceux qui existent physiquement

Dans le cas ou le fichier n’existe pas physiquement sur disque, elle met à jour le statu de l’élément dans le catalogue ou référentiel RMAN. Les statuts de crosscheck: EXPIRED: L’objet n’a pas été trouvé au niveau du système d’exploitation. AVAILABLE: L’objet est disponible et peut être utilisé par RMAN. UNAVAILABLE: L’objet n’est pas disponible et ne peut pas être utilisé par RMAN

• La commande DELETE: permet de supprimer des sauvegardes. Elle supprime les fichiers physiques et l’enregistrement dans le référentiel RMAN.

deux possibilités • •

supprimer des sauvegardes ou des fichiers de journalisation spécifiques ; supprimer les sauvegardes obsolètes.

• La commande CATALOG: permet d'indiquer à RMAN l'emplacement d'un fichier ou l'existence ou non de celui-ci CATALOG { ARCHIVELOG | BACKUPPIECE } liste_fichiers ;

• Permet de cataloguer des fichiers précis. Si le fichier est déjà catalogué alors RMAN supprime l’ancienne référence avant de créer la nouvelle. CATALOG { RECOVERY AREA | DB_RECOVERY_FILE_DEST } [NOPROMPT] ;

•

Permet de cataloguer tous les fichiers stockés dans la zone de récupération rapide

CATALOG START WITH 'chemin' [NOPROMPT] ;

•

Permet de cataloguer tous les fichiers dont le nom complet commence par une certaine chaîne de caractères

162

RMAN (Recovery Manager) / Restauration

• La stratégie de récupération dépend de plusieurs facteurs : • • • • • • •

De la nature du(des) fichier(s) endommagé(s) ou perdu(s) : fichier de données ; fichier de contrôle ; fichier de paramètres serveur ; fichier de journalisation. Du mode de fonctionnement de la base ARCHIVELOG / NOARCHIVELOG. Des sauvegardes disponibles.

• Comment réagir en cas de restauration suite à problème • identifier la nature du problème  • définir le mode opératoire en tenant compte du mode de fonctionnement de la base et des sauvegardes disponibles

• il existe en fait deux étapes bien précises et distinctes  dans la restauration • L’étape de restauration (restore) consiste à extraire dans un jeu sauvegarde les fichiers nécessaires pour la restauration • L’étape de récupération (recover) consiste à appliquer les fichiers de journalisation aux 163 fichiers récupérés de la sauvegarde

RMAN (Recovery Manager) / Restauration • En mode NOARCHIVELOG • Restauration des dernière sauvegarde complète de la base  • Redémarrage de la base

• En mode ARCHIVELOG • restaurer la dernière sauvegarde de chaque fichier perdu ; • appliquer les fichiers de journalisation (archives puis ceux en ligne) ; • redémarrer la base (si la récupération n’a pas été faite base ouverte). • Toutes les modifications apportées depuis les sauvegardes utilisées sont récupérées. La récupération est dite complète • Dans ce cas il faut au moins un fichier de contrôle, un membre par groupe de fichier de journalisation et que toutes les archives de fichiers de journalisation soient disponibles

• Sur la base de ce scénario, différentes situations peuvent conduire à une récupération incomplète (point-in-time recovery) • volontairement, pour s’arrêter avant un ordre SQL malencontreux ; • involontairement, si des fichiers de journalisation sont perdus (une archive ou tout un groupe de fichiers de journalisation en ligne). 164

RMAN (Recovery Manager) / Restauration Syntaxe de la commande RESTORE: • RESTORE cibles [options] - cibles DATABASE DATAFILE liste_numéros_ou_noms TABLESPACE liste_noms CONTROLFILE [TO 'destination'] [FROM AUTOBACKUP | 'sauvegarde'] SPFILE [TO 'destination'] [FROM AUTOBACKUP | 'sauvegarde'] ARCHIVELOG { ALL | filtre_archive } - filtre_archive FROM TIME 'date' UNTIL TIME 'date' TIME BETWEEN 'date1' AND 'date2' - options PREVIEW [SUMMARY] VALIDATE L’option cibles permet d’indiquer ce qu’il faut restaurer. L’option PREVIEW est intéressante pour lister les sauvegardes dont RMAN a besoin pour réaliser l’opération de restauration correspondante. L’option VALIDATE permet de tester si la restauration peut avoir lieu s’il y a pas de fichiers ou blocs corrompus. 165

RMAN (Recovery Manager) / Restauration • La commande RECOVER Syntaxe de la commande RECOVER: RECOVER cible [options] - cible DATABASE DATAFILE liste_numéros_ou_noms TABLESPACE liste_noms • RMAN recherche les fichiers de journalisation archivés dont il a besoin pour faire le recover / récupération sur disque à défaut les restaures depuis la sauvegarde vers LOG_ARCHIVE_DEST_1

• La commande REPAIR Syntaxe de la commande REPAIR: REPAIR cible [options] - cible DATABASE DATAFILE liste_numéros_ou_noms TABLESPACE liste_noms • •

La commande REPAIR enchaîne les commandes RESTORE puis RECOVER Dans le cas d’une réparation de fichier de données ou de tablespace actuellement en ligne, les fichiers de données concernés sont automatiquement passés OFFLINE au début de l’opération puis repassés ONLINE à la fin 166

Architecture Multitenant (CDB/PDB)

167

Architecture Multitenant (CDB/PDB)

• Architecture NoCDB/CDB Avec la version 12C d’Oracle est apparue l’architecture Multitenant (CDB Container Database). Un CDB peut contenir plusieurs PDB (Pluggable Database) • Architecture d’un base actuelle dite NoCDB

168

Architecture Multitenant (CDB/PDB) • Avant la 19C une seule PDB était disponible en version sans option aussi bien en EE et SE2. • Avec 19C oracle met à disposition en EE et SE2 3PDB + PDB(SEED) sans option • A partir de la 21C l’architecture NoCDB sera dépréciée donc il ne sera plus possible de créer un base de données conventionnelle. • Une base de données conteneur est composée obligatoirement d’un conteneur racine (Root ou CDB Root) et d’une base de données enfichable d’amorçage ou modelé appelée PDB Seed et de une ou plusieurs bases de données enfichables "utilisateurs" (PDB). • Il est possible d’avoir le CDB(root ) et d’autre CDB applicative avec leur propre PDB • La base CDB porte un nom défini lors de sa création par le paramètre d’initialisation DB_NAME du fichier d’initialisation, comme pour une base de données traditionnelle. Cette base de données conteneur est ouverte de façon habituelle par une instance désignée par la variable d’environnement ORACLE_SID. • Dans l’architecture CDB, les différentes PDB partagent la même instance (SGA et processus).  • Dans le cas ou il y a une instance avec CDB$root, lorsqu’on arrête l’instance alors la CDB est fermée, toutes les PDB qui sont également attachées fermées • . Par contre, lorsque il y a un CDB$root et d’autre CDB applicative, on peut arrêter une CDB spécifique sans arrêter les autres. • Les fichiers de contrôle et les fichiers de journalisation jouent le même rôle que dans une base de données traditionnelle et ils sont eux aussi partagés par l’ensemble des conteneurs (CDB Root, PDB Seed et PDB utilisateur). • Les PDB partagent tous les fichiers de données, les metadonnées, package …. de la CDB$root • Une PDB a aussi ces propres fichiers de données et metadonnées • Depuis la version 12.2 oracle donne le choix pour la configuration du tablespace d’annulation (UNDO). Il peut être locale au PDB ou partage au niveau de la CDB$Root 169

• A chaque PDB utilisateur est associé un nom de service qui porte le même nom que la PDB  • Le nom de service sera utilisé pour se connecter à la PDB comme une base de données NoCDB, soit en utilisant le nom de service réseau défini dans le fichier tnsnames.ora, soit dans une connexion de type (serveur(:port]/service) • Le nom du PDB doit être unique au sein d’une CDB, mais aussi au sein de l’ensemble des CDB qui ont le même processus d’écoute, afin d’éviter des conflits sur les noms de services enregistrés auprès du processus d’écoute 170

171

Architecture Multitenant (CDB/PDB)

• Architecture Multitenant avec CDB$ROOT et CDB(Application)

• Une CDB-Application joue le même rôle qu’un CDB$ROOT. L’application est installé au niveau de CDB-A et est partagée par toutes les PDBs qui sont connectées à cette applis.

172

Architecture Multitenant (CDB/PDB)

• Les différentes types de PDBs dans l’architecture multitenant • PDB proxy

est une PDB dans une CDB qui fait référence à une PDB dans une autre CDB (sur le même serveur ou sur un autre serveur). Les utilisateurs connectés à cette PDB proxy accèdent en fait de façon transparente à la PDB distante et voient les objets de la PDB distante comme s’ils étaient locaux.

• PDB copie snapshot

est une copie d’une PDB qui utilise des mécanismes de snapshot au niveau du stockage afin ne pas dupliquer l’intégralité des fichiers de données de la PDB d’origine. De ce fait, la création de la PDB est très rapide et l’espace qu’elle utilise initialement très faible. Il faut vérifier que votre système support le mécanisme de snapshot.

173

Administration d’un instance CDB

• Connexion

Dans une base de données CDB, la connexion peut s’effectuer par l’intermédiaire de n’importe quel conteneur CDB$Root ou PDB elle peut être: • Connexion locale au PDB en positionnant la variable d’environnement ORACLE_SID.  • Connexion à l’aide d’un nom de service réseau défini dans le fichier tnsnames.ora par l’intermediaire de SID ou service_name • Connexion à l’aide la méthode easy connect ([//]hôte[:port][/service]). Pour se connecter à un CDB$ROOT, il faut avoir le privilège nécessaire "create session" ou des droit du type sys ou system Exemple de connexion sqlplus / as sysdba Show con_name  affiche le nom du container de connexion. Show PDBS  affiche les noms des PDBs Pour se connecter à une PDB exemple P_CAMPUS sqlplus system@p_campus

174

Administration d’une instance CDB • Pour pouvoir changer de container l’utilisateur doit avoir le droits globale ou local sur le centenaire et avoir le "privilège set container" ALTER SESSION SET CONTAINER = nom_conteneur [SERVICE = nom_service];  permet de se connecter à un autre PDB. • Les vues actuelles du dictionnaire peuvent être interrogé mais ces vues retournent des informations sur le conteneur courant uniquement (CDB Root ou PDB). Par exemple, la vue DBA_TABLESPACES donne la liste des tablespaces de la PDB courante, pas la liste de tous les tablespaces de la base de données dans son ensemble c’est-à-dire la CDB . (DBA_%, ALL_% et USER_%)

• Oracle introduit une notion "d’objet de données de conteneur" (container data objects) qui désigne une table ou une vue qui contient des données relatives à plusieurs conteneurs. • Les vues V$% donnent les informations sur toutes les données du conteneur. • Il y a de nouvelle catégorie de vues dont le nom est préfixé par CDB_. Toutes ces vues comportent une colonne CON_ID qui identifie le conteneur de la ligne retournée. Les valeurs possibles de cette colonne sont les suivantes  

175

• Remarque dans une base de données non CDB, la colonne CON_ID vaut toujours 0. • Pour chaque vue de type DBA_% il y a une vue de type CDB_% équivalante • Pour une connexion à CDB$Root avec un utilisateur comme SYS ou SYSTEM, l’interrogation des vues CDB_ % retourne des informations sur toutes PDB ouvertes, à l’exception de la PDB Seed et des conteneurs ouverts dans le mode RESTRICTED. • L’interrogation des vues V$% retourne des informations sur tous les conteneurs sans exception. Si on est connecté à une PDB, l’interrogation de ces vues retourne des informations de la PDB courante uniquement. L’interroger d’une vue CDB_% est équivalent dans ce cas à interroger une vue DBA_%. • Le fichier d’alerte de l’instance est associé à l’instance et est donc unique pour une base de données conteneur : il n’y a pas un fichier d’alerte par PDB. Le fichier d’alerte d’une instance qui ouvre une base de données conteneur contient donc des messages relatifs à la totalité de la base de données et à l’ensemble des conteneurs (CDB$Root et PDB). • Les messages spécifiques à une PDB sont clairement identifiés avec le nom et l’identifiant de la PDB en début de ligne

176

Administration d’un instance CDB L’administration d’une base CDB est identique à celui d’une base NoCDB La création d’une base de données Container peut se faire manuellement en exécutant les packages nécessaire ou par l’interface graphique: le DBCA. Cette étape va créer le CDB$ROOT et PDB$SEED. Pour créer une PDB une fois la PDBseed créé, il y a plusieurs possibilité (voir schema ci-dessous)

177

Administration d’un instance CDB

ifférentes opérations de création ou de manipulations des objets de PDB / CDB peuvent se faire ne de commande avec sqlpl / sqlcl / sqlDeveloper / EM Express / DBCA

178

Administration d’une instance CDB

• Prérequis à la création d’une PDB. • Pour un utilisateur non (sys, system et n’ayant pas le rôle dba), il doit avoir le privilège "CREATE PLUGGABLE DATABASE«  • La CDB$ROOT doit être ouverte en lecture/ écriture • La vue PDB_PLUG_IN_VIOLATIONS permet d’afficher les information sur les problème de conflit éventuel.

• Création d’une PDB à partir de zéro. Syntaxe • CREATE PLUGGABLE DATABASE NOM_PDB • ALTER PLUGGABLE DATABASE NOM_PDB (action) CREATE PLUGGABLE DATABASE nom_pdb ADMIN USER nom_utilisateur IDENTIFIED BY mot_de_passe [ ROLES = (nom_role[,...]) ] [ FILE_NAME_CONVERT = ('chaine1','chaîne2'[, …]) | NONE] [ CREATE_FILE_DEST = 'chemin' | NONE];

179

Administration d’un instance CDB • Exemples exemple CREATE PLUGGABLE DATABASE P_CAMPUS01 ADMIN USER admin_pdb IDENTIFIED BY JesuisOraclePDB; Exemple 2 CREATE PLUGGABLE DATABASE P_CAMPUS01 ADMIN USER admin_pdb IDENTIFIED BY JesuisOraclePDB FILE_NAME_CONVERT = ('/u01/CAMPUS01/data/campus/pdbseed', '/u01/CAMPUS01/data/P_CAMPUS01/p_campus01') Exemple 3 ; CREATE PLUGGABLE DATABASE P_CAMPUS01 ADMIN USER admin_pdb IDENTIFIED BY JesuisOraclePDB FILE_NAME_CONVERT = ('/pdbseed/','/P_CAMPUS01/'); Exemple 4 CREATE PLUGGABLE DATABASE P_CAMPUS01 ADMIN USER admin_pdb IDENTIFIED BY JesuisOraclePDB CREATE_FILE_DEST = '/u01/CAMPUS01/data/P_CAMPUS01/p_campus01'); Si aucune clause n’est présente et si aucun paramètre d’initialisation n’est défini, vous obtiendrez une erreur lors de la création de la PDB.

180

Administration d’un instance CDB • Pour finaliser la création de la PDB, elle doit être ouvert en lecture / écriture • La clause obligatoire ADMIN USER permet de spécifier le nom et le mot de passe d’un utilisateur local de la nouvelle PDB qui aura potentiellement le droit d’administrer la PDB. • Cet utilisateur reçoit le rôle PDB_DBA qui comporte initialement et uniquement les privilèges système CREATE SESSION et CREATE PLUGGABLE DATABASE. • Les droits de cet utilisateur sont donc restreints au départ, mais on peut lui attribuer d’autres droits ultérieurement si nécessaire. PLUGGABLE DATABASE P_CAMPUS01 • CREATE On peut ajouter la clause ROLES lors de la création de la PDB pour attribuer localement à l’utilisateur le rôle PDB_DBA. ADMIN USER admin_pdb IDENTIFIED BY JesuisOraclePDB roles=(DBA);

• Le rôle DBA au rôle PDB_DBA permet à l’utilisateur admin_pdb d’avoir les droits suffisants pour administrer la PDB • Tableau des clauses

181

Administration d’un instance CDB • La clause FILE_NAME_CONVERT permet de définir des règles pour la conversion des noms de fichiers sous la forme de couples de chaînes : la première chaîne est recherchée dans le nom du fichier d’origine et remplacée par la deuxième chaîne pour générer le fichier de destination. •

La clause FILE_NAME_CONVERT = NONE est équivalente à omettre la clause.

• La clause CREATE_FILE_DEST active l’utilisation des fichiers gérés par Oracle pour la nouvelle PDB et spécifie le répertoire de base dans lequel les fichiers seront créés •

•

La valeur (autre que NONE) de cette clause sera stockée dans le paramètre DB_CREATE_FILE_DEST de la PDB ceci permet de stocker les fichiers de données d’Oracle créés ultérieurement dans la même arborescence que les fichiers de données créés lors de la création de la PDB (sauf si le paramètre DB_CREATE_FILE_DEST est modifié entre-temps dans la PDB). •

•

La clause CREATE_FILE_DEST = NONE désactive l’utilisation des fichiers gérés par Oracle (OMF) pour la PDB.

Par défaut, la PDB hérite de la valeur du paramètre DB_CREATE_FILE_DEST de la CDB Root

Dans la clause CREATE_FILE_DEST, le chemin au niveau OS peut être remplacé par le nom d’un objet DIRECTORY défini dans la CDB Root

CREATE OR REPLACE DIRECTORY PDB_DEST AS '/u01/CAMPUS01/data/P_CAMPUS01/p_campus01';

CREATE PLUGGABLE DATABASE p_campus01 ADMIN USER admin_pdb IDENTIFIED BY JesuisPlusFort CREATE_FILE_DEST = 'PDB_DEST';

•

Si les clauses FILE_NAME_CONVERT et CREATE_FILE_DEST sont présentes simultanément dans l’ordre SQL de création de la PDB, alors c’est la première clause qui sera utilisée pour définir l’emplacement des fichiers de données de la nouvelle PDB (clause prioritaire). Dans ce cas, la deuxième clause est juste utilisée pour définir la valeur du paramètre DB_CREATE_FILE_DEST dans la nouvelle PDB. 182

Administration d’un instance CDB • Lors de création d’une PDB on peut aussi ajouté les clauses suivantes: • DEFAULT TABLESPACE nom_tablespace • DATAFILE spécification_fichier [,...] [ clause_gestion_extension ] • STORAGE ([MAXSIZE taille | UNLIMITED] [MAX_DIAG_SIZE taille | UNLIMITED])

183

Administration d’un instance CDB • Création d’un PDB Locale par CLONAGE • Une nouvelle PDB peut être créée par clonage d’une PDB existante sur la même CDB. Les fichiers de données de la PDB source seront copiés vers un nouvel emplacement et associés à la nouvelle PDB. • S la CDB est en mode noarchivelog, il faut mettre en lecture seule la PDB source avant de cloné. • Si la CDB est en mode archivelog et en gestion locale d’annulation alors le PDB source peut être cloné en étant ouverte en lecture / écriture C’est le clonage a chaud.

Syntaxe

CREATE PLUGGABLE DATABASE nom_pdb_cible FROM nom_pdb_source [NO DATA] [ DEFAULT TABLESPACE nom_tablespace ] [ USER_TABLESPACES = (liste) | ALL [EXCEPT (liste)] | NONE [NO DATA] ] [ FILE_NAME_CONVERT = ('chaine1','chaîne2'[,…]) ] [ CREATE_FILE_DEST = 'chemin' | NONE] [ STORAGE ([MAXSIZE taille | UNLIMITED] [MAX_DIAG_SIZE taille | UNLIMITED]) ] [ SERVICE_NAME_CONVERT = ('ancien_nom','nouveau_nom'[,...]) ] ; Exemple CREATE PLUGGABLE DATABASE p_campus01 FROM p_campus02; Exemple CREATE PLUGGABLE DATABASE p_campus01 FROM p_campus02; FILE_NAME_CONVERT = ('/u01/CAMPUS01/data/P_CAMPUS01/', '/u01/CAMPUS01/data/P_CAMPUS02/') CREATE PLUGGABLE DATABASE p_campus02 FROM p_rhcampus CREATE_FILE_DEST = '/u01/CAMPUS01/data/P_CAMPUS01/p_rhcampus/');

184

Administration d’un instance CDB • Il est possible de cloner une PDB sans les données en utilisant la clause "NO DATA"  • Il est aussi possible de définir un storage par défaut de type tablespace mais le tablespace doit exister dans la PDB source. Syntaxe USER_TABLESPACES = (liste) | ALL [EXCEPT (liste)] | NONE [NO DATA]

exemple CREATE PLUGGABLE DATABASE p_campus02 FROM p_rhcampus NO DATA

User_TABLESPACES= data, data_RH, data_Prod FILE_NAME_CONVERT = ('/u01/CAMPUS01/data/P_CAMPUS01/', '/u01/CAMPUS01/data/P_CAMPUS02/')

• Dans la nouvelle PDB, les tablespaces exclus sont créés mais sont en OFFLINE, sans fichiers de données. Ils apparaissent dans la vue V$TABLESPACE mais pas dans la vue DBA_TABLESPACES. Les tablespaces non reprisent c’est-à-dire offline peuvent être supprimés définitivement dans la nouvelle PDB • La clause SERVICE_NAME_CONVERT permet de renommer les services de la PDB d’origine dans la nouvelle PDB. Syntaxe SERVICE_NAME_CONVERT = ('ancien_nom','nouveau_nom'[,...])

CREATE PLUGGABLE DATABASE p_campus01 FROM p_rh FILE_NAME_CONVERT = ('/data/CAMPUS01/campus01','/data/CAMPUS01/RH/') SERVICE_NAME_CONVERT = ('campus','p_rh'); 185

Administration d’un instance CDB • Création d’un PDB par CLONAGE distante • Prérequis: • • • •

Créer un utilisateur commun dans la CDB$ROOT source (un utilisateur commun est du type C##mawer) L’utilisateur du dblink doit avoir le privilège "CREATE SESSION, CREATE PLUGGABLE DATABASE et SYSOPER" Créer un dblink entre la CDB cible et la CDB ou PDB source. Les options installées dans la CDB source doivent être les mêmes que les options installées dans la CDB cible, ou un sous-ensemble. • Si le jeu de caractères de la CDB cible n’est pas AL32UTF8, alors la PDB source doit avoir le même jeu de caractères que la CDB cible (ou un jeu de caractères compatible, c’est-à-dire un jeu de caractères qui est un sousensemble du jeu de caractères de la CDB cible). Cette contrainte n’existe pas si le jeu de caractères de la CDB cible est AL32UTF8 • Si la CDB est en mode noarchivelog, il faut mettre en lecture seule la PDB source avant de cloné. • Si la CDB est en mode archivelog et en gestion locale d’annulation alors le PDB source peut être cloné en étant ouverte en lecture / écriture C’est le clonage a chaud. Syntaxe Exemple CREATE PLUGGABLE DATABASE nom_pdb_cible FROM nom_pdb_source@dblink - CREATE USER c##mawer IDENTIFIED BY JeSuisOracle; (source) ## cible ## - Create database link to_campus01 connect to c##mawer identified by JeSuisOracle using CAMPUS02; CREATE PLUGGABLE DATABASE p_campus02 FROM p_campus01@to_campus01 FILE_NAME_CONVERT = ('/u01/CAMPUS01/data/P_CAMPUS01', '/u01/CAMPUS01/data/P_CAMPUS02/');

186

Administration d’un instance CDB • Création d’un PDB par CLONAGE d’un NoCDB • Prérequis: • • • • •

Même contrainte qu’un clonage distante (options installées, jeu de caractères etc ..) Les version de la CDB et NoCDB doit être 12.1.0.2 ou ultérieure. La base de données CDB et NoCDB doivent utiliser la même taille de bloc CDB en mode archivelog ou noarchivelog (même contrainte clonage PDB distant) Créer un dblink dans la CDB cible avec un user qui a les privilèges "CREATE SESSION, CREATE PLUGGABLE DATABASE et SYSOPER"

Exemple: - CREATE USER mawer IDENTIFIED BY JeSuisOracle;  (source NoCDB) - GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE, SYSOPER TO mawer; - ## Cible Base CDB - Create database link to_dbnoCDB connect to mawer identified by JeSuisOracle using CAMPUS02; CREATE PLUGGABLE DATABASE pdb_CAMPUS01 FROM CAMPUS@to_dbnoCDB    FILE_NAME_CONVERT = ('\CAMPUS\','\pdb_CAMPUS\'); ALTER SESSION SET CONTAINER = pdb_CAMPUS01;  pour se connecter à la PDB

• Post Clonage: - Exécuter le script de conversion de la base NoCDB en CDB @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

187

Administration d’un instance CDB • Création d’une PDB par " rafraîchissable "  • C’est identique à un clonage de PDB distante mais la PDB demeure en mode lecture seule. Toute modification faite sur la source est répercutée à une fréquence régulière sur la cible. Syntaxe REFRESH MODE { NONE | MANUAL | EVERY n {MINUTES | HOURS} }

CREATE PLUGGABLE DATABASE p_epoer FROM p_rh@to_CAMPUS01   FILE_NAME_CONVERT = ('/u01/CAMPUS01/data/P_CAMPUS01','/u01/CAMPUS01/data/P_CAMPUS02')    REFRESH MODE MANUAL;  •ALTER PourPLUGGABLE une refresh DATABASE manuel la p_epoer PDB doitOPEN être fermer au préalable avant de faire le refresh READ ONLY;

Close immédiate  refresh  open read only.

188

Administration d’un instance CDB • Création d’une PDB par "BRANCHEMENT"  • On peut débrancher un PDB (unplug) d’une CDB1 et le brancher sur une autre CDB2. • Prérequis: • Les options installées dans la CDB source doivent être les mêmes que les options installées dans la CDB cible, ou un sousensemble • Si le jeu de caractères de la CDB cible n’est pas AL32UTF8, alors la PDB source doit avoir le même jeu de caractères que la CDB cible (ou un jeu de caractères compatible, c’est-à-dire un jeu de caractères qui est un sous-ensemble du jeu de caractères de la CDB cible). Cette contrainte n’existe pas si le jeu de caractères de la CDB cible est AL32UTF8 • Se connecté à la CDB$Root avec une connexion AS SYSDBA, la PDB source à débrancher doit être fermée • Avant de débrancher/brancher une PDB vérifier qu’elle est bien compatible avec la CDB cible (options, jeu de caractères, etc.). Avec la procédure DBMS_PDB.DESCRIBE (source) ou DBMS_PDB.CHECK_PLUG_COMPATIBILITY (cible). La vue PDB_PLUG_IN_VIOLATIONS pour en savoir plus d’information sur l’incompatibilité des de la PDB Syntaxe • ALTER PLUGGABLE DATABASE nom_pdb UNPLUG INTO 'nom_fichier'

Exemple ALTER PLUGGABLE DATABASE p_rh CLOSE; Fichier XML : (contient uniquement les information sur les metadonnées ) ALTER PLUGGABLE DATABASE p_rh UNPLUG INTO ‘/data/CAMPUS01/p_rh.xml'; Fichier PDB : (fichier archive plus long et volumineux mais contient le fichier xml et tous les fichiers de données)

ALTER PLUGGABLE DATABASE p_rh UNPLUG INTO ‘/data/CAMPUS02/p_rh.pdb'; • Une fois que la PDB a été débranchée, elle apparaît toujours MOUNTED dans la vue V$PDBS mais elle a le statut UNPLUGGED dans la vue CDB_PDBS, et 189 elle n’est plus disponible dans la CDB

Administration d’un instance CDB • Brancher la PDB • Le branchement d’une PDB dans une CDB diffère légèrement selon que la PDB a été débranchée dans un fichier XML ou dans une archive de PDB • •

Dans l’archive de PDB, les fichiers de données de la PDB sont extraits de l’archive par conséquent il n’est pas nécessaire d’utiliser les clauses qui spécifient l’emplacement des fichiers de données. Dans le cas de fichier XML il faut préciser l’emplacement des fichiers de données avec la clauses SOURCE_NAME_CONVERT ou SOURCE_FILE_DIRECTORY 

Syntaxe CREATE PLUGGABLE DATABASE nom_pdb [AS CLONE] USING nom_fichier [ SOURCE_FILE_NAME_CONVERT = ('chaine1','chaîne2'[,…]) ] [ SOURCE_FILE_DIRECTORY = 'chemin' ] [ NOCOPY | COPY | MOVE ] [ FILE_NAME_CONVERT = ('chaine1','chaîne2'[,…]) ] [ TEMPFILE REUSE ] [ DEFAULT TABLESPACE nom_tablespace ] [ USER_TABLESPACES = (liste) | ALL [EXCEPT (liste)] | NONE [COPY | MOVE] ] [ CREATE_FILE_DEST = 'chemin' | NONE] [ STORAGE ([MAXSIZE taille | UNLIMITED] [MAX_DIAG_SIZE taille | UNLIMITED]) ] [ SERVICE_NAME_CONVERT = ('ancien_nom','nouveau_nom'[,...]) ] ;

CREATE PLUGGABLE DATABASE p_rh USING ‘/u01/data/Campus/p_rh.xml' NOCOPY TEMPFILE REUSE; CREATE PLUGGABLE DATABASE p_rh USING ‘/u01/data/Campus/p_rh.xml'  SOURCE_FILE_DIRECTORY = ‘/u01/data/Campus02/p_rh' NOCOPY TEMPFILE REUSE; Ou SOURCE_FILE_NAME_CONVERT = ’ /u01/data/Campus02/p_rh' NOCOPY CREATE PLUGGABLE DATABASE p_rh USING ‘/temp/pdbrh.pdb'   TEMPFILE REUSE; • Pour déplacer = FILE_NAME_CONVERT les(‘/temp/','/u01/data/Campus02/p_rh'); datafiles utiliser la clause MOVE (par defaut c’est copy)

190

Administration

d’un instance CDB

• Déplacement d’une PDB • Il est possible de déplacer une PDB d’une CDB1 sur un CDB2. les 2 CDB peuvent être sur le même serveur ou sur différents. • Dans le cas de deux serveurs différent il faut configurer REMOTE_LISTENER en plus du paramètre LOCAL_LISTENER. • Les contraintes sont les même que lorsqu’on clone une PDB distante • Le déplacement peut être fait à chaud. Une fois le déplacement effectué et que la PDB déplacée est ouverte elle est supprimer de la source. Syntaxe RELOCATE [ AVAILABILITY { NORMAL | MAX } ] L’option AVAILABILITY permet de gérer la durée de l’indisponibilité lors de la bascule entre l’ancienne PDB et la nouvelle Une fois le déplacement terminé les actions suivantes sont réalisées par Oracle • Une dernière mise à jour de la PDB déplacée # source • Fermeture de la PDB d’origine • Ouverture de la PDB déplacée CREATE USER mawer IDENTIFIED BY jeSuisOracle  GRANT CREATE SESSION, CREATE DATABASE, SYSOPER TO c##clone   • suppression de la PDB PLUGGABLE d’origine   CONTAINER = ALL; # Cible CREATE PLUGGABLE DATABASE P_rh FROM p_rh@to_Campus02    CREATE_FILE_DEST = ‘/data/CAMPUS02/'    RELOCATE AVAILABILITY MAX;  ALTER PLUGGABLE DATABASE p_rh OPEN;

191

Administration d’un instance CDB

• Toutes les actions sur une CDB/PDB peuvent être réalisées par l’intermédiaire des outils d’administration graphique. • DBCA • • • •

Créer une PDB à partir de zéro Clonage d’un PDB local Débranchement / Branchement Supprimer un PDB

Remarque en mode silencieux de DBCA il y a plus de possibilité qu’en mode graphique.

• EM Express / SqlDeveloper • • • • • •

Créer une PDB à partir de zéro Clonage d’un PDB local / distante Débranchement / Branchement Rafraîchissement Supprimer un PDB Ouvrir/fermer la base de données pluggable

192

Gestion de la CDB et des PDB

• Lorsqu’une CDB est ouverte, les PDBs attenante peuvent être ouverte également. • La commande "startup"  Monter / ouvre la CDB par contre les PDBs ne sont pas ouverte automatiquement. • Commande pour démarrer plusieurs PDBs Syntaxe: ALTER PLUGGABLE DATABASE {liste | ALL | ALL EXCEPT liste} action; - liste nom_pdb[, ...] - action OPEN [READ WRITE | READ ONLY] [RESTRICTED] [FORCE] CLOSE [IMMEDIATE | ABORT]

Pour mémoriser l’action précédente de "alter pluggable…. " il faut ajouter le "save state / discard state"  (vue DBA_PDB_SAVE_STATES)

• il est aussi possible d’activer et de désactiver le mode RESTRICTED SESSION sur les PDB individuellement • La commande "shudtown" ferme toute les PDBs attacher à la CDB Il existe plusieurs commandes pour ouvrir/fermer une PDB selon qu’on est connecté à la CDB$Root ou de la PDB 

193

Gestion de la CDB et des PDB • Actions de modification de la PDB

Lorsqu’on se connecte à une PDB seule les actions alter system ci-dessous agissent au niveau de la PDB . • • • • • • •

ALTER ALTER ALTER ALTER ALTER ALTER ALTER

SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM SYSTEM

CHECKPOINT  FLUSH {SHARED_POOL | BUFFER_CACHE}  REGISTER  { ENABLE | DISABLE } RESTRICTED SESSION  { KILL | DISCONNECT } SESSION  CANCEL SQL  SET

194

Gestion de la CDB et des PDB • Gestion des paramètres

Les paramètres d’initialisation sont gérés au niveau de la CDB et les valeurs définies s’appliquent à toutes les PDBs. Cependant certains paramètres d’initialisation peuvent être modifiés au niveau de la PDB ainsi chaque PDB pouvant avoir des valeurs différentes. Dès lors, les paramètres qui ne sont pas explicitement définis dans la PDB héritent des valeurs définies au niveau de la CDB. Les paramètres pouvant être modifier dans la PDB sont consultable dans la vue V$PARAMETER  (paramètres qui ont la valeur TRUE dans la colonne ISPDB_MODIFIABLE)

Dans la CDB ALTER SYSTEM SET ... [CONTAINER = CURRENT | ALL] [SCOPE = ...]

Remarque dans une PDB seul l’option "CURRENT"  est autorisé

195

Gestion de la CDB et des PDB

• Gestion de la mémoire et contrôle des ressources • Gestion de la mémoire

La mémoire allouée à l’instance est définie par les paramètres habituels au niveau de la CDB et elle est répartie entre les différentes PDB en fonction des besoins de chaque PDB. Or une PDB très active peut consommer une grande partie de la mémoire au détriment des autres PDBs. Pour contrôler plus précisément la répartition de la mémoire entre les PDB, il est possible de définir plusieurs paramètres dans les PDBs. Pour que les paramètres soient prise en compte au niveau de la PDB il faut positionner au niveau de la CDB le paramètre NONCDB_COMPATIBLE à FALSE et désactiver la gestion automatique de l’instance MEMORY_TARGET = 0 • DB_CACHE_SIZE (Taille minimale garantie du Buffer Cache dans la PDB) • SHARED_POOL_SIZE (Taille minimale garantie de la Shared Pool dans la PDB avec [DB_CACHE_SIZE + SHARED_POOL_SIZE] =< 50% de SGA_TARGET de la PDB et de la CDB.)

à la valeur

• SGA_MIN_SIZE (Taille minimale garantie de la SGA dans la PDB sa valeur doit être =< 50% de la valeur du paramètre SGA_TARGET de la PDB et de la CDB)

• SGA_TARGET (La valeur de ce paramètre dans la PDB doit être =< 50% à la valeur du paramètre SGA_TARGET de la CDB )  • PGA_AGGREGATE_LIMIT (Taille maximum de la PGA dans la PDB  valeur =< de PGA_AGGREGATE_LIMIT de la CDB et >= 2* PGA_AGGREGATE_TARGET de la PDB)

• PGA_AGGREGATE_TARGET

(Taille cible de la PGA dans la PDB =< à la valeur de PGA_AGGREGATE_TARGET de la CDB et =< 50% la valeur du paramètre PGA_AGGREGATE_LIMIT de la PDB et de la CDB)

les contraintes d’inferieur ou égale à 50 % permettent de garantir qu’Oracle aura de la réserve nécessaire pour allouer dynamiquement un minimum de mémoire en fonction des besoins. La vue V$RSRC_PDB permet de voir la quantité de mémoire actuellement utilisée dans les différents conteneurs D’autre informations sont disponibles dans les vues V$RSRCPDBMETRIC (dernière minute d’activité)196 et V$RSRCPDBMETRIC_HISTORY (dernière heure d’activité avec une ligne par minute)

Gestion de la CDB et des PDB

• Gestion de la mémoire et contrôle des ressources • Gestion des entrées/sorties Comme pour les autres paramètres d’initialisation, la PDB hérite des valeurs affectées à la CDB Root par défaut, mais ces paramètres peuvent être redéfinis dans chaque PDB en fonction des besoins. Par défaut, la valeur de ces paramètres est égale à zéro, ce qui signifie qu’il n’y a aucune limite Cependant il est possible de définir les valeurs des paramètres d’entrèes/sorties suivantes pour chaque PDB. • •

MAX_IOPS (Nombre maximum d’opérations d’entrée/sortie par seconde) MAX_MBPS (Nombre maximum de Mo d’entrée/sortie par seconde )

Les vues V$RSRCPDBMETRIC (dernière minute d’activité) et V$RSRCPDBMETRIC_HISTORY (dernière heure d’activité avec une ligne par minute) peuvent être interrogées pour avoir une idée de l’activité des différentes PDB en matière d’entrées/sorties et éventuellement déterminer des limites adaptées

197

Gestion de la CDB et des PDB

• Gestion de la mémoire et contrôle des ressources • Gestion de la CPU Le paramètre d’initialisation CPU_COUNT définit le nombre de CPU disponibles pour la base de données. Par défaut, la valeur de ce paramètre est égale au nombre de CPU de la machine. Par conséquent la notion de "nombre de CPU" mentionnée ici correspond à un nombre de threads (files d’exécution) en tenant compte de l’architecture des processeurs (plusieurs cœurs avec ou sans fonctionnalité de multithreading). Ce paramètre peut être défini explicitement dans une instance (CDB ou non CDB) pour limiter le nombre de CPU utilisées par l’instance. Dans une base de données conteneurs, ce paramètre peut aussi être défini dans les PDB pour limiter le nombre de CPU utilisées par la PDB. De cette manière, il est possible de faire en sorte qu’une PDB ne consomme pas trop de CPU au détriment des autres PDB. La valeur définie dans une PDB doit être inférieure à la valeur définie dans la CDB (nombre maximum de CPU utilisées pour la totalité de la CDB) Depuis la version 19.4, il est aussi possible de définir le paramètre d’initialisation CPU_MIN_COUNT  ( spécifie le nombre minimum de CPU (threads) requises pour la PDB)

Deux annotations sont possibles: •

Le paramètre peut être un nombre entier supérieur ou égal à 1  Définit directement un nombre minimum de CPU au niveau de la PDB

•

Le paramètre peut être un nombre décimal compris entre 0,1 et 0,95, multiple de 0,05  Définit un pourcentage minimum de temps pendant lequel la PDB requiert une CPU. 

Les paramètres CPU_COUNT  et CPU_MIN_COUNT permettent de contrôler les consommations CPU de PDB Les vues V$RSRCPDBMETRIC (dernière minute d’activité) et V$RSRCPDBMETRIC_HISTORY (dernière heure d’activité avec une ligne par minute) donnent des informations sur l’activité des différentes PDB en matière de consommation CPU et d’activité des sessions et éventuellement déterminer des limites adaptées. 198

Gestion de la CDB et des PDB

• Modification de la base de données

L’ordre SQL "ALTER DATABASE" peut être utilisé pour modifier la base de données conteneur dans son ensemble, comme c’est le cas pour une base de données non CDB. Cependant certaines clauses de cet ordre "ALTER DATABASE" ne vont pas modifier la totalité de la CDB, mais juste la CDB Root en tant que conteneur, et éventuellement définir une valeur par défaut pour les PDBs. • Exemple des ordres SQL qui vont agir sur les fichiers de données de la CDB$ROOT •

ALTER DATABASE RENAME FILE

•

ALTER DATABASE DATAFILE

•

ALTER DATABASE TEMPFILE

•

ALTER DATABASE DEFAULT [TEMPORARY] / TABLESPACE

•Exemple des ordres SQL qui vont agir sur la CDB$ROOT et définir la valeur par default de la PDB •ALTER DATABASE FLASHBACK {ON|OFF} •ALTER DATABASE SET DEFAULT {BIGILE|SMALLFILE} TABLESPACE

•Vue que certains ordres SQL "alter database .." peuvent agir à la fois sur la CDB$ROOT et PDB, il est conseillé d’utiliser l’ordre "alter pluggable database"  qui indique clairement que la modification est au niveau de la PDB. ALTER ALTER ALTER ALTER ALTER ALTER

PLUGGABLE PLUGGABLE PLUGGABLE PLUGGABLE PLUGGABLE PLUGGABLE

DATABASE DATABASE DATABASE DATABASE DATABASE DATABASE

[nom] [nom] [nom] [nom] [nom] [nom]

SET DEFAULT {BIGFILE|SMALLFILE} TABLESPACE;  DEFAULT TABLESPACE nom_tablespace;  DEFAULT TEMPORARY TABLESPACE nom_tablespace;  RENAME FILE ...;  DATAFILE ...;  STORAGE ...;

•

Certains ordres SQL de type (alter database / system) ne peuvent pas être exécuté au niveau de la PDB car la structure se situe au niveau de la CDB$ROOT (fichier de contrôle, journalisation ..) si c’est le cas vous auriez une erreur du type ORA-65040: opération non autorisée à partir d'une base de données pluggable Toutefois le paramètre statique "NONCDB_COMPATIBLE=TRUE" permet d’exécuté l’ordre au niveau de la CDB bien que la connexion est sur la PDB sans erreur. 199

Gestion du stockage • Gestion des tablespaces et des fichiers de données • Dans une base de données conteneurs, chaque PDB peut avoir ces propres tablespaces, notamment (SYSTEM, SYSAUX, data) ainsi qu’un tablespace temporaire et d’annulation selon le mode de gestion de l’annulation choisie. • Le tablespace SYSTEM dans une PDB stocke uniquement les définitions des objets "utilisateurs" stockés dans la PDB, mais les définitions communes de la CDB Root sont rendues visibles par des mécanismes internes de lien. Par conséquent le tablespace SYSTEM de PDB est beaucoup plus petit que celui de la CDB$Root • Le répertoire de destination de chaque fichiers de la PDB doit être définie • Dan le cas d’utilisation de OMF il faut définir le "DB_CREATE_FILE_DEST " et Oracle créera ces fichiers dans le répertoire indiqué. ALTER [PLUGGABLE] DATABASE [nom] SET DEFAULT {BIGFILE|SMALLFILE} TABLESPACE;  Les ordres SQL ci-dessous permet d’agir sur le storage d’un tablespace au niveau de la PDB ALTER [PLUGGABLE] DATABASE [nom] DEFAULT TABLESPACE nom_tablespace; ALTER [PLUGGABLE] DATABASE [nom] DEFAULT TEMPORARY TABLESPACE nom_tablespace;  ALTER [PLUGGABLE] DATABASE [nom] RENAME FILE ...;  ALTER [PLUGGABLE] DATABASE [nom] DATAFILE ...;  ALTER [PLUGGABLE] DATABASE [nom] STORAGE ...;

200

Gestion du stockage • Gestion de l’annulation Avant la version 12.2 seule la gestion partager de l’espace d’annulation était disponible. Avec la 12.2 est apparue la gestion locale du tablespace d’annulation. Les avantages de la gestion locale sont les suivants 

• • • •

Meilleure isolation de chaque conteneur Possibilité d’effectuer un clonage ou un déplacement d’une PDB ouverte en lecture/écriture Amélioration des performances pour certaines opérations débranchement de la PDB Meilleur gestion de redo log en récupération incomplète de la PDB

Pour des raisons de compatibilité ascendante, le mode partagé est le mode par défaut sauf lorsqu’on utilise DBCA. Il est possible de passer du mode partagé au mode local par les commandes suivantes SHUTDOWN IMMEDIATE  STARTUP UPGRADE ALTER DATABASE LOCAL UNDO ON; SHUTDOWN IMMEDIATE  STARTUP ALTER PLUGGABLE DATABASE ... OPEN;

La modification est applicable à l’ensemble des PDB y comprise la PDB$SEED. La gestion locale s’active lors de la première ouverture de la PDB avec la création d’un UNDO_01 de 20Mo. Les ordres SQL habituels de gestion des tablespaces et des datafiles sont aussi utilisable pour modifier les caractéristiques du tablespace créé. La colonne 'LOCAL_UNDO_ENABLED‘ de la vue "DATABASE_PROPERTIES"  permet d’avoir l’information sur le type de gestion. 201

Gestion des utilisateurs au niveau de la CDB/PDB • Utilisateurs dans CDB/PDB • Dans une base de données conteneur, il est possible de créer un utilisateur commun dans la CDB$Root qui seront connus implicitement dans toutes les PDB. A condition d’avoir le privilège CREATE SESSION. Un utilisateur commun peut se connecter à n’importe quelle PDB, avec le même nom et le même mot de passe. Un utilisateur commun peut avoir des privilèges et ou rôles différents dans les différentes PDB •

Par contre un utilisateur créé localement dans une PDB n’existe que dans cette PDB et ne pourra pas se connecter ni à la CDB$Root ni à une autre PDB. Il est possible de créer deux utilisateurs ayant le même nom dans deux PDB différentes avec des droits différents dans chaque PDB. Il est possible de créer des rôles communs ou locaux, ainsi que des profils communs ou locaux.

•

Les noms des utilisateurs, rôles et profils communs doivent commencer par le préfixe c## ou C##. Le paramètre du préfixe est définie par le paramètre " COMMON_USER_PREFIX " cette valeur est modifiable

•

Les ordres SQL relatifs à la gestion des utilisateurs et des droits peuvent avoir une clause "CONTAINER" qui accepte deux valeurs

• CONTAINER=ALL : l’action s’effectue dans tous les conteneurs. • CONTAINER=CURRENT : l’action concerne uniquement le conteneur courant

202

Gestion des utilisateurs au niveau de la CDB/PDB • Créer et modifier les utilisateurs • Les syntaxes SQL qui permettent de créer, modifier un utilisateur sur une base NOCDB est le même que pour les bases CDB. • Pour une base CDB il faut ajouter la clause "CONTAINER" et définir le type d’utilisateur qui sera créé (local ou commun) en respectant la règle de nommage • •

Utilisateur local: se connecte à la PDB Utilisateur commun: se connecte à la CDB$ROOT

• Lors de la création d’un utilisateur commun, les clauses DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA et PROFILE peuvent être utilisées. Si ces clauses sont omises, les valeurs par défaut seront utilisées • La seule valeur autorisée pour la clause CONTAINER est: • ALL  utilisateur commun • CURRENT  Utilisateur Local

• • • •

si les conditions de nommage ou de syntaxe ne sont pas respectées une erreur est retournées. Les clauses ALL ou CURRENT peuvent être omises Un utilisateur commun peut être modifié localement ou de façon commune.  Pour modifier un utilisateur commun localement, il faut être connecté à la PDB concernée et spécifier la clause CONTAINER=CURRENT  • Pour modifier un utilisateur commun de façon commune, il faut être connecté à la CDB Root et spécifier la clause CONTAINER=ALL  • La suppression d’un utilisateur commun s’effectue obligatoirement et uniquement en étant connecté à la CDB Root • La suppression d’un utilisateur local s’effectue obligatoirement et uniquement en étant connecté à la PDB 203 dans laquelle l’utilisateur est défini.

Gestion des utilisateurs au niveau de la CDB/PDB • Gestion des profils Utilisateur • Il est possible de créer des profils communs ou locaux, avec les mêmes règles que pour la gestion des utilisateurs • Se connecter au bon conteneur • Le préfixe C## peut être utiliser ou non et utilisation de la clause CONTAINER est optionnelle. • Un profil local peut être créé, modifié ou supprimé uniquement en étant connecté à une PDB. • Un profil commun peut être créé, modifié ou supprimé uniquement en étant connecté à la CDB Root • Il n’est pas possible d’avoir des limites différentes CDB par rapport à la PDB • Un utilisateur local peut avoir un profil local ou commun • Un utilisateur commun peut avoir un profil commun ou local. Il peut se voir attribuer aussi d’autre profile dans la CDB$ROOT et PDB • Lorsque l’utilisateur commun se connecte à la PDB, la limite qui s’applique à la session varie selon qu’il s’agit d’une limite liée aux mots de passe ou d’une limite liée aux ressources • Les limites liées aux mots de passe sont extraites du profil attribué à l’utilisateur commun dans la CDB Root. • Les limites liées aux ressources sont extraites du profil attribué à l’utilisateur commun dans la PDB.

204

Gestion des utilisateurs au niveau de la CDB/PDB • Gestion des droits • Les ordres SQL habituels GRANT et REVOKE permettent d’attribuer des droits avec la valeur de la clause CONTAINER et CURRENT par default même lorsque les ordres SQL sont exécutés en étant connecté à la CDB Root. • Il est possible de créer des rôles communs ou locaux, avec les mêmes règles que pour la gestion des utilisateurs en se connectant au bon conteneur • Il est possible d’utiliser ou non du préfixe C##. L’utilisation de la clause CONTAINER est optionnelle • Règles relatives à l’utilisation de la clause CONTAINER=CURRENT dans l’ordre SQL GRANT  • peut être exécutée par un utilisateur commun ou local (s’il est connecté à une PDB) qu’il a le droit d’attribuer des droits à d’autre utilisateur ; • peut être utiliser pour attribuer un rôle commun ou local, ou un privilège système ou objet ; • peut être utiliser pour attribuer à un utilisateur commun ou local un role ; • peut s’appliquer uniquement au conteneur courant (CDB Root ou PDB selon la connexion).

• Règles relatives à l’utilisation de la clause CONTAINER=ALL dans l’ordre SQL GRANT  • • • •

doit être exécuté par un utilisateur commun connecté à la CDB Root et qui a le droit d’attribuer des droits à d’autre utilisateur ; peut permet d’attribuer un rôle commun (uniquement), ou un privilège système ou objet ; peut être utiliser pour attribuer à un utilisateur ou un rôle commun (uniquement) ; Pour être appliquer à tous les conteneurs (CDB Root et PDB actuelles et futures).

• Attribuer un privilège objet de façon commune nécessite que l’objet en question soit lui-même commun • Les règles relatives à l’utilisation de la clause CONTAINER dans l’ordre SQL REVOKE sont les mêmes que pour l’ordre SQL GRANT • La règle supplémentaire de révocation d’un privilège ou d’un rôle dans un périmètre donné (CURRENT ou ALL) n’est possible que si le privilège a été attribué dans le même périmètre • La clause CONTAINER=CURRENT ne peut pas révoquer un privilège qui a été attribué avec CONTAINER=ALL. • La clause CONTAINER=ALL ne peut pas révoqur un privilège qui a été attribué avec CONTAINER=CURRENT.

205

Gestion des utilisateurs au niveau de la CDB/PDB • Gestion des droits • Un utilisateur commun peut avoir deux fois un privilège dans un conteneur  : • Privilège a été attribué de façon commune (CONTAINER=ALL connecté à CDB Root) • Privilège a été attribué de façon locale (CONTAINER=CURRENT connecté à une PDB Cette situation est source potentiel de confusion. Il est visible dans le dictionnaire de données avec la vue cdb_sys_privs

• Il n’est pas possible d’attribuer des privilèges pour une liste de PDB ou pour toutes les PDB sauf certaines. • Pour qu’un utilisateur commun ait le droit de se connecter à toutes les PDB mais pas à la CDB Root, il n y a pas d’autre possibilité que lui attribuer le privilège CREATE SESSION localement dans toutes les PDB mais pas dans la CDB Root. • L’attribution d’un droit à PUBLIC avec la clause CONTAINER=CURRENT, sera implicitement attribué à tous les utilisateurs du conteneur courant (CDB Root ou PDB selon la connexion). • L’attribution d’un droit à PUBLIC avec la clause CONTAINER=ALL en étant connecté à la CDB Root sera implicitement attribué à tous les utilisateurs de tous les conteneurs (CDB Root et toutes les PDB) • Pour des raisons de sécurité, il est déconseillé d’attribuer des droits à PUBLIC de façon commune • Les privilèges attribués par Oracle à PUBLIC sur les objets communs fournis par Oracle sont attribués de façon locale, ce qui laisse la possibilité de les révoquer localement dans certaines PDB si vous ne souhaitez pas que le droit puisse être exercé par tous les utilisateurs • Le principe d’attribution des droits sont identiques à ceux d’attribution de roles.

206

Sauvegarde et récupération

• Sauvegarde logique datapump • L’utilisation de Data Pump avec une PDB est identique à une base non CDB avec quelque particularités. • Data Pump peut être utilisé pour effectuer des opérations d’export/import dans différentes situations :

•

• Base de données non CDB vers PDB • PDB vers PDB • PDB vers base de données non CDB Lors de l’utilisation de Data Pump en étant connecté à la CDB Root génère un message d’avertissement.

Avertissement : les opérations Oracle Data Pump ne sont généralement pas nécessaires en cas de connexion à la racine ou à la valeur de départ d'une base de données Conteneur.

• •

Dans une base de données conteneur, le DIRECTORY (DIRECTORY DATA_PUMP_DIR ) est défini au niveau de la CDB Root Oracle impose un chemin pour la directory qui n’est pas modifiable pour chaque PDB avec un sous-répertoire par PDB dont le nom est égal au GUID (Global Unique IDentifier) de la PDB. La vue cdb_directories donne les informations sur les noms et chemins des directories.

• L’import échouera lors qu’un utilisateur commun n’existe pas dans CDB cible ou que l’utilisateur d’une base de données non CDB n’existe pas dans la CDB cible. Car impdp n’arrive pas à créer l’utilisateur. Pour y remédier il faut créer l’utilisateur dans la CDB/PDB cible et utiliser remap_schemas pour importer les objets de l’utilisateur.

207

Sauvegarde et récupération

• Sauvegarde Comme dans une base de données NOCDB, une base en conteneur peut être sauvegarder par RMAN. • La CDB dans son ensemble (CDB Root et toutes les PDB) • La CDB Root uniquement  • Une ou plusieurs PDB

Les différentes types de sauvegardes disponibles en NOCDB sont également disponible en mode CDB avec les mêmes syntaxes bien qu’il peut avoir quelque différences mineurs: (sauvegarde complète ou incrémentale, récupération complète ou incomplète, etc.) Certaines opérations ne sont pas autorisées dans RMAN en étant connecté à une PDB • • • •

configurer l’environnement RMAN (commande CONFIGURE) ; sauvegarde, suppression ou restauration des fichiers de journalisation archivés ; sauvegarde ou restauration du fichier de paramètres serveur ou du fichier de contrôle ; récupération incomplète ou flashback de la PDB en mode UNDO partagé (mais c’est possible en mode UNDO local) ; • utilisation du Data Recovery Advisor (il faut être connecté à la CDB Root).

Selon les cas, l’utilisation d’une commande interdite dans une PDB se manifestera soit par un message d’erreur soit par un message d’information, la commande étant dans ce cas ignorée. Lorsque qu’on se connecte à une CDB Root, la commande REPORT SCHEMA permet d‘afficher la liste de tous les fichiers de données de tous les conteneurs (CDB Root et toutes les PDB).  L’appartenance d’un tablespace à une PDB particulière est indiquée par la syntaxe nom_PDB:nom_tablespace  (exemple:P_RH:SYSTEM) 208

Sauvegarde et récupération • Sauvegarde La commande " BACKUP DATABASE habituelle permet de sauvegarder la base de données CDB dans son ensemble (CDB Root et toutes les PDB)" . (en étant connecter sur la CDB$ROOT) Rman target / BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT; 

Chaque élément (CDB,PDB) sera sauvegardé dans des jeux de sauvegardes différents en plus du jeu de sauvegarde des fichiers de contrôle, de paramètre et d’archivelog si la base est en archivelog. L’utilisation des jeux de sauvegardes différentes pour chaque PDB permet une récupération individuelle d’une PDB ou d’un fichier de données d’une PDB. • Lorsqu’on est connecté à une PDB la commande "BACKUP DATABASE " permet de faire la sauvegarde que de la PDB courante. Les fichiers de controles, de paramètre ne sont pas sauvegardés même si la sauvegarde automatique des fichiers est activée. • Lorsque qu’on se connecte à une CDB Root, on peut aussi sauvegarder des PDB individuellement à l’aide de la commande "BACKUP PLUGGABLE DATABASE" suivie du nom de la PDB ou d’une liste de noms de PDB

BACKUP PLUGGABLE DATABASE pdbrh;             une PDB  BACKUP PLUGGABLE DATABASE pdbrh,pdbcpt;       deux PDB  BACKUP PLUGGABLE DATABASE "cdb$root",pdbrh;  CDB Root et une PDB

• La CDB$Root peut aussi être sauvegardé individuellement avec la commande BACKUP DATABASE ROOT • Des tablespaces ou des fichiers de données peuvent être sauvegardés avec les commandes habituelles (BACKUP TABLESPACE ou BACKPUP DATAFILE) • •

Si on est connecté à une PDB alors seules les tablespaces et datafiles de la PDB seront sauvegardés. Si on est connecté à la CDB$ROOT pourront être sauvegarder ceux de la CDB et le PDB. Pour sauvegarder un tablespace d’une PDB, il faut préfixer le nom du tablespace avec le nom de la PDB sous la forme nom_pdb:nom_tablespace. Sans préfixe, c’est le tablespace du conteneur courant (CDB 209 Root) qui est pris en compte .

Sauvegarde et récupération

• Récupération • Lorsqu’on se connecte à une CDB Root, les commandes "RESTORE DATABASE" et "RECOVER DATABASE" habituelles (ainsi que "REPAIR DATABASE") permettent de récupérer la base de données CDB dans son ensemble (CDB Root et toutes les PDB) • De même lorsqu'on se connecte à une PDB; ces mêmes commandes permettent de récupérer la PDB individuellement, indépendamment des autres PDB. • lorsqu'on se connecte à une CDB Root on peut aussi récupérer individuellement des PDB à l’aide des commandes RESTORE PLUGGABLE DATABASE et RECOVER PLUGGABLE DATABASE.

RESTORE PLUGGABLE DATABASE pdbrh;             une PDB  RECOVER PLUGGABLE DATABASE pdbrh,pdbcpt;      deux PDB  REPAIR DATABASE "cdb$root";                    CDB Root

• La CDB$Root peut être récupéré individuellement par les commandes "RESTORE DATABASE ROOT" et "RECOVER DATABASE ROOT"  • Pour récupérer une PDB, celle-ci doit être fermée et la CDB peut être fermée (MOUNT) ou ouverte (OPEN). • Si la CDB ne peut pas être démarrée ou montée alors il faut d’abord la mettre en état mount avant la récupération. • Des tablespaces ou des fichiers de données peuvent être récupérés avec les commandes ({RESTORE | RECOVER | REPAIR} {TABLESPACE | DATAFILE}. • Lorsqu’on se connecte à le CDB$ROOT, pour récupérer un tablespace d’un PDB il faut préfixer sous la forme nom_pdb:nom_tablespace  sans préfixe, c’est le tablespace CDB$Root courante qui est pris en compte. • Pour une facilité de lecture on peut aussi utiliser le préfixe pour un tablespace root: ("cdb$root":system) 210

Sauvegarde et récupération • Récupération

• Récupération incomplète Il est possible d’effectuer des récupérations incomplètes, en prenant bien garde d’être connecté au bon conteneur. En effet, lorsqu'on se connecte à une CDB$Root, la récupération incomplète sera réaliser sur totalité de la base de données (CDB Root et PDB. Lorsqu’on se connecte une PDB, seule PDB courante sera restaurée c’est à dire ramenée en arrière. Il est possible de se connecter à une CDB Root pour effectuer une récupération incomplète d’une PDB en utilisant les commandes "RESTORE PLUGGABLE DATABASE" et "RECOVER PLUGGABLE DATABASE". Dans le cas de la récupération incomplète d’une PDB, le mode de gestion de l’annulation (local ou partagé) a de l’importance: • Mode Partagé • La récupération incomplète est possible uniquement en étant connecté à la CDB Root. • Oracle va devoir utiliser une instance auxiliaire pour effectuer la récupération, ce qui peut nécessiter l’utilisation de la clause AUXILIARY DESTINATION dans la commande RECOVER. • Mode local  • La récupération incomplète peut être lancée en étant connecté à la CDB Root ou à la PDB. • Oracle n’a pas besoin d’utiliser une instance auxiliaire, ce qui simplifie la syntaxe et est plus performant. Comme dans une base de données non CDB, après une récupération incomplète, il faut ouvrir la base de données dans le mode RESETLOGS. Dans le cas d’une récupération incomplète d’une PDB, le RESETLOGS s’appliquer uniquement à la PDB 211