alphorm.com - support de la formation oracle database ... propos de ce cours objectifs décrire,...

Présentation de la formationOracle Database : 11g DBA 1

(1Z0-052)

Oracle Database : 11g DBA 1 (1Z0-052) alphorm.com™©

(1Z0-052)

Noureddine DRISSI Expert consultant bases de données

Contact : [email protected]

Site : http://www.alphorm.comBlog : http://www.alphorm.com/blogForum : http://www.alphorm.com/forum

Le formateur

� Noureddine DRISSI

� Mathématicien de formation

� Expertise dans le domaine des bases de données

� 15 années d’expérience sur tous les SGBDR du marché (Oracle, SQL Server, Postgresql, Mysql, DB2, MongoDB)


� Issue de l’environnement bancaire

[email protected]

Connaissances préalables

� Connaissances de base du système d’exploitation Linux

� Expérience professionnelle du langage SQL

� Expérience professionnelle des bases de données relationnelles

� Expérience en matière de conception de bases de données


A propos de ce cours

� Objectifs

� Décrire, comprendre l’architecture d’une instance et d’une base de données Oracle;

� installer et configurer Oracle Database 11g;

� prise en main des outils d’administration;

� configurer Oracle Net Services;

gérer les structures de stockage de la base de données;


� gérer les structures de stockage de la base de données;

� surveiller et administrer les données d'annulation UNDO;

� gérer les données et les accès concurrents;

� créer et gérer des comptes utilisateur;

� réaliser les opérations élémentaires de sauvegarde/restauration;

� gérer les performances.

Plan de cours


Plan de cours

� Module 1: Installation de Oracle 11g

� Module 2: Architecture d’une instance Oracle

� Module 3: architecture d’une base de données Oracle

� Module 4: Les outils d’administration

� Module 5: Création d’une base de données

� Module 10: Gérer la sécurité utilisateur

� Module 11: Gérer les accès concurrents

� Module 12: Gérer les données d'annulation (UNDO)

� Module 13: Implémenter l'audit de la base de données Oracle

� Module 14: Maintenance de la base de


� Module 6: Gérer une instance Oracle

� Module 7: Gérer une instance ASM

• Module 8: Configurer Oracle Net

� Module 9: Gérer les structures de stockage de base de données

� Module 14: Maintenance de la base de données Oracle

� Module 15: Gestion des performances

� Module 16: Sauvegarde/restauration

� Module 17: Transfert de données

• Module 18: Aide et support

Fin Plan de cours


Qu’est-ce qu’un SGBDR ?

� Oracle est un Système de Gestion de Base de Données Relationnelle (SGBDR)

� Oracle est chargé de :

� stocker les données,

� vérifier les contraintes d’intégrité définies,

� garantir la cohérence des données qu’il stocke, même en cas de panne (arrêt brutal) du système,


système,

� assurer les relations entre les données définies par les utilisateurs,

� respecte la norme ACID.

� Oracle est un Système de Gestion de Base de données multi-platormes (UNIX, Linux et Windows)

Certification

Cette formation prépare vous prépare à l'examen Oracle Database 11gAdministrator Certified Associate.


Environnement du LAB

192.168.1.151OS: Linux Centos 6.1Noyau Oracle: 11g R2


gescom

INSTANCE

virtuo

INSTANCE

Introduction à la base Gescom

� Dans les ateliers pratique de ce cours, nous allons travailler avec une base de données modèle que l’on nommera GESCOM.

� Cette base de données servira de fil conducteur pour l’ensemble des démonstration (sauvegarde, transfert, stockage, optimisation, etc.)

� Script « crgescom.sql » disponible sur la plateforme Alphom


Introduction à la base Gescom

Base: GESCOM

Schéma: hamid

Instance Oracle

Tables Vues


RôlesIndexes

synonyms …

Introduction au modèle de données


GO


GO

Architecture d’une instance Oracle:Le bloc Oracle





Le bloc Oracle

� Sa fonction

• Unité d’échange entre données disque et la mémoire Oracle

• SHOW PARAMETER DB_BLOCK_SIZE

• Les tailles de bloc

• 2K, 4K, 8K, 16k, 32K


• 2K, 4K, 8K, 16k, 32K

Le bloc Oracle

� Tailles non standards


Le bloc Oracle

� Tailles de bloc non standard autorisé :

• 2Ko, 4Ko, 8Ko, 16Ko et 32Ko

• On peut avoir une taille de bloc standard et 4 tailles de bloc non standard

• SHOW PARAMETER K_CACHE_SIZE


Création d’une base de données Oracle


Architecture d’une instance Oracle:Les processus





INSTANCE

SGA

Shared Pool

Library cache

Shared SQL Area

Buffer Cache Buffer redo log

Les processus d’une instance Oracle


Dictionnary cache

Data dictionnaire de données

Java pool

PMON DBWn LGWR CKPT SMON ARCn

Processus d’arrière-plan

Les processus d’une instance Oracle

� Rappel

� Notion d’instance

• Connaître l’instance courante

- SHOW PARAMETER INSTANCE_NAME

• Connaître la taille de la mémoire partagéee


• Connaître la taille de la mémoire partagéee

- SHOW PARAMETER MEMORY_TARGET

Processus DBWn (obligatoire)

INSTANCE

SGA

Shared Pool

Library cache

Dictionnary cache

Buffer Cache Buffer redo logJava pool

Large pool


Processus serveur


PGA

Fichiers de données

010101010101010101010101010101010101010010101

0101001

Fichiers Redo Log

Fichier de

contrôle

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010101010

1001

0101010101010101010101010101010

Fichiers journaux archivés

Nombre de processus : DB_WRITER_PROCESSES

Processus DBWn

� Fréquence d’écriture du DBW

� Toutes les trois secondes

� Dès que la Dirty List (liste des blocs modifiés) dépasse un seuil prédéfini

� Chaque fois qu’un processus consulte la liste LRU et ne trouve pas un emplacement libre après un nombre prédéterminé de recherche de bloc


emplacement libre après un nombre prédéterminé de recherche de bloc

� Lors de chaque Checkpoint (CKPT)

� Chaque fois qu’une base est arrêtée normalement

� Chaque fois qu’un tablespace est mis en mode OFFLINE ou READ ONLY ou s’il fait partie d’une sauvegarde en ligne

Processus LGWR (obligatoire)

INSTANCE

SGA

Shared Pool

Library cache

Dictionnary cache


Large pool


Processus serveur


PGA


010101010101010101010101010101010101010010101010

1001

Fichiers Redo Log

Fichier de

contrôle

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001


Processus LGWR (obligatoire)

� Fréquence d’écriture du LGWR

� Toutes les trois secondes

� Lors de la validation d’une transaction en cours « COMMIT »

� Si le buffer REDO LOG est rempli à 1/3

� Volume de données dans le buffer REDO LOG >= à 1M


� Volume de données dans le buffer REDO LOG >= à 1M

� Lors de chaque Checkpoint (CKPT)

� Lorsqu’il est déclenché par le processus DBWn

Processus CKPT (obligatoire)

INSTANCE

SGA

Shared Pool

Library cache

Dictionnary cache


Large pool


Processus serveur

PGA


010101010101010101010101010101010101010010101010

1001

Fichiers Redo Log

Fichier de

contrôle

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001



Processus CKPT (obligatoire)

� Fréquence d’écriture du CKPT

� Lors d’un basculement des fichiers journaux

� Manuellement : ALTER SYSTEM CHECKPOINT

� Lors d’un arrêt propre de l’instance Oracle


Processus ARCn (optionnel)

INSTANCE

SGA

Shared Pool

Library cache

Dictionnary cache


Large pool


Processus serveur


PGA


010101010101010101010101010101010101010010101010

1001Fichiers Redo Log

Fichier de

contrôle010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001


Processus SMON (obligatoire)

INSTANCE

SGA

Shared Pool

Library cache

Dictionnary cache


Large pool


Processus serveur

30


PGA


010101010101010101010101010101010101010010101010


Fichier de

contrôle010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001


PMON

Processus SMON (obligatoire)

� Rôles

� Nécessaire au démarrage de l’instance Oracle

� Nettoie les segments temporaires et inutilisés

� Efface les vieux processus

� Fusionne l’espace libre dans de plus grand blocs contigus



Processus PMON (obligatoire)

INSTANCE

SGA

Shared Pool

Library cache

Dictionnary cache


Large pool


Processus serveur


PGA


010101010101010101010101010101010101010010101010


Fichier de

contrôle010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001


PMON

Processus PMON (obligatoire)

� Rôles

� Récupération des processus utilisateurs défaillants

� Libère le cache de blocs de données

� Libère les ressources qui étaient exploitées par les utilisateurs




• Remarque : à l’instar du processus SMON, le processus PMON s’active régulièrement pour se rendre compte si on a besoin de lui.

Les infos sur les processus

� Les vues

� V$PROCESS, V$BGPROCESS

• Si champ ADDR <> ‘00’ = processus lancé

• Si champ BACKGROUND = 1 => processus démon


Le bloc Oracle


Oracle Database : 11g DBA 1 (1Z0-052) Installation avancée





Installation de Oracle 11g

� Rappel des étapes de préparation à l’installation

� Installation des librairies

� Configuration du noyau Linux

� Configuration des paramètres ulimits

� Création de l’arborescence d’installation du noyau Oracle 11g


� Création de l’utilisateur oracle et des groupes

� Installation de Oracle 11g


� Rappel des variables d’environnement Oracle

� ORACLE_BASE=/u01/app/ora11g

� ORACLE_HOME=/u01/app/ora11g/product/11.2.0/db_1



� Description de l’environnement d’installation


192.168.2.151OS: Linux Centos 6.1

192.168.2.207OS: Windowsputty + Xming

installation


� TP installation avancée de Oracle 11g R2


Oracle Database : 11g DBA 1 (1Z0-052) Installation simple






� TP installation simple de Oracle 11g R2


Oracle Database : 11g DBA 1 (1Z0-052) Installation avancée






� Description de l’environnement d’installation


192.168.2.151OS: Linux Centos 6.1

192.168.2.207OS: Windowsputty + Xming

installation

Oracle Database : 11g DBA 1 (1Z0-052) Préparation de l’installation






� Liste des pré-requis

� Configuration matérielle

• Mémoire physique de 1Gb

• Mémoire virtuelle de 4Gb

• Espace de travail temporaire de 500Mo


• Espace disque de stockage

- Noyau Oracle 11g: 3Gb

- Une base de données: 3Gb


� Description de l’environnement


• Processeur Intel Core i7 CPU @ 2.90 GHz Dual Core

• RAM: 2 Go

• Disque SSD: 32 Go

Configuration logicielle


� Configuration logicielle

• Serveur Linux Centos 6.1 64 bits (IP 192.168.2.151)

• Oracle Database 11g Release 2 Enterprise Edition


� Les étapes









� La norme OFA (Optimal Flexible Architecture)

� Noms des répertoires

• ORACLE_BASE répertoire qui accueillera toutes les installations des produits effectuées par un seul utilisateur

- /pm/h/u (pm=point de montage, h=nom de répertoire standardisé,u=propriétaire)

• ORACLE_HOME permet de définir une installation d’Oracle


• ORACLE_HOME permet de définir une installation d’Oracle

- /pm/h/u/product/version/type (version=version oracle,type=type installation)


� Utilisateur pour l’installation

� ora11g (utilisateur qui installe et administre Oracle)

� SYSDBA (groupe dba)

� SYSOPER (groupe oper)

� oinstall (groupe pour l’installation Oracle)



� TP installation simple de Oracle 11g R2


� TP test d’une installation de Oracle 11g R2


Oracle Database : 11g DBA 1 (1Z0-052) Test d’une installation Oracle 11g R2






� TP test d’un installation Oracle 11g R2


Installation de Oracle 11gOracle Database : 11g DBA 1

(1Z0-052)


(1Z0-052)





� Description de l’environnement


• Processeur Intel Core i7 CPU @ 2.90 GHz Dual Core

• RAM: 4 Go

• Disque SSD: 25 Go

Configuration logicielle


� Configuration logicielle

• Serveur Linux Centos 6.3 64 bits

• Oracle Database 11g Release 2 Enterprise Edition


� Les étapes







� Configuration des droits


module 2: Architecture d’uneinstance Oracle


instance Oracle

Création d’une base de données:Architecture d’une base de données





BASE DE DONNEES

101010101010

010010010101

Fichier de mot de passe

Fichiers journaux archivées


Fichier de contrôle

010101010101010101010101010101010101010010

1010101001

Les fichiers de base de données


Fichier paramètre

Fichiers journaux

Instance_name=db_name=gescommemory_target=1

1010101001

01

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101

010101010101010101010101010101

L’instance et la base de données

INSTANCE

SGA

Shared Pool

Library cache

Dictionnary cache


Large pool




01010101010101010101010101010101

Fichiers Redo Log

Fichier de

contrôle

010101010101010101010101010101010101010010

1010101001 010101010101010101010101010101010101010010101010

1001

0101010101010101010101010101010


01010101010101010101010101010101

Création d’une base de données avec l’assistant DBCA (Database Configuration


l’assistant DBCA (Database Configuration Assistant)

Création d’une base de données:Assistant DBCA





L’assistant DBCA (Database Configuration Assistant)

� Rôles

� Créer une base de données

� Configurer les options d’une base de données

� Gérer les modèles de création de base de données

� Supprimer une base de données


� Supprimer une base de données

L’assistant DBCA

� Etapes de création

� Créer les répertoires de stockage

� Créer l’instance et la base de données à l’aide de DBCA


Création manuelle d’une base de données


Création d’une base de données:Création manuelle






� Les étapes

� Choisir le nom de l’instance (gescom)

� Choisir le nom de la base de données (gescom)

� Préparer le système d’exploitation (création des répertoires => script etapes.txt)

� Créer le fichier des paramètres « PFILE »

� Démarrer l’instance


� Démarrer l’instance

� Créer la base de données (script crgescom.sql)

� Exécuter les scripts de création du dictionnaire de données

� Créer le fichier des paramètre à serveur « SPFILE »


� Création du dictionnaire de données

� Répertoires

• $ORACLE_HOME\rdbms

• $ORACLE_HOME\sqlplus\admin

� Scripts :


� Scripts :

• catalog.sql => obligatoire

• catproc.sql => obligatoire

• catblock.sql

• pupbld.sql

• ...

Suppression d’une base de données



� Les étapes

� Se connecter en SYSDBA

• sqlplus / as sysdba

� Arrêter l’instance

• shutdown abort

� Démarrer en mode EXCLUSIVE RESTRICT (niveau MOUNT)


� Démarrer en mode EXCLUSIVE RESTRICT (niveau MOUNT)

• startup mount exclusive restrict

� Supprimer la base de données

• drop database


� Les fichiers supprimés

� Les fichiers de données (DATAFILES)

� Les fichiers de contrôle (CONTROLFILES)

� Les fichiers redo (REDO LOG FILES)

� Le fichier de démarrage (SPFILE)


Remarque: en RAW DEVICE, les fichiers ne sont pas supprimés

Les outils d’administration: SQL*Plus


SQL*Plus

� Outil de prédilection de l’administrateur

� Permet de manipuler les données de la base de données

� Permet de réaliser les tâches d’administration


� Outil en ligne de commande

� Permet d’exécuter des scripts

Les outils d’administration

� Environnement SQL Plus

� Nécessite un nom d’utilisateur, un mot de passe et la base de données

� Exécute le script $ORACLE_HOME/sqlplus/admin/glogin.sql

� Le prompt par défaut est SQL>

� Possibilité de lancer des commandes OS


� Possibilité de lancer des commandes OS

� Possibilité de se déconecter sans sortir de SQL*Plus

� Utilisation de la commande CONNECT pour changer de contexte

� Utilisation de la commande EXIT ou QUIT pour terminer une session


� Les commandes SQL Plus

� CONNECT, DISCONNECT

� EXIT, QUIT

� RUN, START

� EDIT

� SAVE


� SAVE

� GET

� SPOOL

� HOST

� DESCRIBE

� …


� Les commandes SQL Plus

� LINESIZE (défini le nombre maximal de caractères autorisés dans chaque ligne)

� PAGESIZE (défini le nombre maximal de lignes dans chaque page)

� TERMOUT (désactive l’affichage des lignes à l’écran : SET TERMOUT OFF)

� HEADING (désactive ou active l’affichage des entêtes de colonnes : SET HEADING { ON | OFF})

� TRIMSPOOL (supprime ou non les blancs situés à la fin des lignes envoyées vers un fichier)


� TRIMSPOOL (supprime ou non les blancs situés à la fin des lignes envoyées vers un fichier)

� FEEDBACK (affiche ou non le nombre de lignes extraites)

� ECHO (affiche ou non l’instruction lorsqu’elle est exécutée)


� SQL Plus

� Utilisation des variables de substitution & et &&

• « & » pour une variable temporaire, doit être introduite à chaque utilisation

• « && » pour une variable permanente, n’est introduite que lors de la première utilisation



� SQL Plus

� Utilisation des variables de substitution

• DEFINE

• ACCEPT


Gérer une instance Oracle


Gérer une instance Oracle : utilisateurs SYS et SYSTEM





INSTANCE

SGA

Shared Pool

Library cache

Shared SQL Area


Rappel de la notion d’instance


Dictionnary cache

Data dictionnaire de données

Java pool


Processus d’arrière-plan

Les utilisateurs SYS et SYSTEM

� Les utilisateurs SYS et SYSTEM

� Créés automatiquement lors de la création de la base de données

� Administrateurs de la base de données

Le compte SYS (change_on_install) : le plus puissant


• Le compte SYS (change_on_install) : le plus puissant

• Le compte SYSTEM (manager) : sert a créer tous les objets

Les utilisateurs SYS et SYSTEM

� Les privilèges SYSDBA et SYSOPER

� SYSDBA

• Offre tous les privilèges pour administrer l’instance et la base de données

SYSOPER


� SYSOPER

• Hérite de tous les privilèges de SYSDBA sauf la possibilité de créer une base

Gérer une instance Oracle : méthode d’authentification


d’authentification

Gérer une instance Oracle : méthode d’authentification





Méthode d’authentification

� Deux méthodes

� Par système d’exploitation

• Groupe dba

Par fichier de mot de passe


� Par fichier de mot de passe

• orapwd file=orapw<ORACLE_SID> password=<mot_de_passe> entries=<nombre> force=y

Note: le fichier de mot de passe est crée dans $ORACLE_HOME/dbs

Paramètre REMOTE_LOGIN_PASSWORDFILE = [EXCLUSIVE | SHARED | NONE] du fichier d’initilisation

Gérer une instance Oracle : le fichier d’initialisation


d’initialisation

Gérer une instance Oracle :

Fichier d’initialisation


Fichier d’initialisation




Le fichier d’initialisation

� Deux types de fichier

� PFILE (init<ORACLE_SID>.ora)

• Fichier texte

• Editable

� SPFILE (spfile<ORACLE_SID>.ora)


� SPFILE (spfile<ORACLE_SID>.ora)

• Fichier binaire géré par le serveur

• Stocké sur le serveur


� Ordre de lecture des fichiers de paramètres

• spfile<ORACLE_SID>.ora

• spfile.ora

• init<ORACLE_SID>.ora

• Possibilité de spécifier un fichier de paramètres en particulier


• Possibilité de spécifier un fichier de paramètres en particulier

- STARTUP pfile=<chemin/nom_du_fichier_pfile>


� Utilisation d’un fichier de paramètre

STARTUP Fichier paramètrespfile<ORACLE_SID>.orainit<ORACLE_SID>.ora

INSTANCE

SGA


SGA

Localisation des fichiers PFILE et SPFILE:� $ORACLE_HOME/dbs� %ORACLE_HOME%/database


� Le fichier SPFILE

• Création du fichier SPFILE à partir du fichier PFILE

- CREATE SPFILE[=‘nom_fichier_spfile’] FROM PFILE [=‘nom_fichier_pfile’]

• Création du fichier PFILE à partir du fichier SPFILE

- CREATE PFILE[=‘nom_fichier_spfile’] FROM SPFILE [=‘nom_fichier_pfile’]


- CREATE PFILE[=‘nom_fichier_spfile’] FROM SPFILE [=‘nom_fichier_pfile’]

Gérer une instance Oracle : démarrage de l’instance


l’instance


Démarrage et arrêt


Démarrage et arrêt




Démarrage d’une base de données

INSTANCE

SGA + processNOMOUNTInit

SPFILE

STA

RTU

P

SHU


MOUNT

OPEN

Fichier(s) de

contrôle(s)

STA

RTU

P

UTDOWN

Fichier DATA

Fichier DATA REDO REDO

La commande STARTUP

NOMOUNT

STARTUPINSTANCE

SGA + processInit

SPFILE


MOUNT

OPEN

Fichier(s) de

contrôle(s)

Fichier DATA


cLa commande ALTER DATABASE

NOMOUNT

ALTE

R D

ATA

BA

SE

M

OU

NT;

ALTE

R D

ATA

BA

SE

InitSPFILE

INSTANCE

SGA + process

Fichier(s)


MOUNT

OPEN

ALTE

R D

ATA

BA

SE

M

OU

NT;

ALTE

R D

ATA

BA

SE

O

PE

N;

Fichier(s) de

contrôle(s)

Fichier DATA


Les différentes options de démarrage

� Les options

� STARTUP NOMOUNT

� STARTUP MOUNT

� STARTUP OPEN

� STARTUP FORCE


� STARTUP FORCE

� STARTUP RESTRICT

• Nécessite le privilège RESTRICTED SESSION

• Désactivation: ALTER SYSTEM DISABLE RESTRICTED SESSION;

Les vues dynamiques

NOMOUNT

V$PARAMETERV$SGAV$OPTIONV$SESSIONV$INSTANCE

V$CONTROLFILE

InitSPFILE

INSTANCE

SGA + process


MOUNT

OPEN

V$CONTROLFILEV$DATABASEV$DATAFILEV$LOGFILE

Vues du dictionnaire De données

Fichier(s) de

contrôle(s)

Fichier DATA


L’arrêt du serveur

Mode d’arrêt (OPTION) ABORT IMMEDIATE TRANSACTIONAL NORMAL

Autoriser de nouvelles connexions N N N N

Attend la fin des sessions en cours N N N Y

Option de la commande SHUTDOWN


Attend la fin des sessions en cours N N N Y

Attend la fin des transactions en cours

N N Y Y

Force un point de contrôle et fermeles fichiers

N Y Y Y

Gérer une instance Oracle : les fichiers de traces


traces


Les fichiers de trace


Les fichiers de trace




Les fichiers de traces

� Le fichier d’alerte (alert.log)

� Son nom: alert_<ORACLE_SID>.log

� Paramètre de configuration: diagnostic_dest = ORACLE_BASE

� Son emplacement: $ORACLE_BASE/diag/rdbms/<dbname>/<ORACLE_SID>

• diag est un repertoire commun


• diag est un repertoire commun

• rdbms est le type du produit

• <dbname> est le nom de la base de donnees

• <ORACLE_SID> est le nom de l'instance

- Visualisation du chemin: show parameter background_dump;


� Le fichier d’alerte (alert.log)

� Son utilité

• Enregistre avec horodatage

- Arrêt/Démarrage

- Les opérations de récupération automatique ou manuelle


- Les opérations de récupération automatique ou manuelle

- Toutes les erreurs au niveau instance

- Création de tablespaces et opérations DDL (ALTER)

- Possibilité de tracer les processus serveur

• ALTER SESSION SET sql_trace=TRUE;

• ALTER SYSTEM SET sql_trace=TRUE;


� Les fichiers de trace

� Chaque processus d’arrière plan a son propre fichier de trace

� Contient des informations beaucoup plus détaillées que l’alert.log

� Paramètres de configuration:

• USER_DUMP_DEST (chemin des fichiers de trace)


• USER_DUMP_DEST (chemin des fichiers de trace)

• MAX_DUMP_FILE_SIZE (taille des fichiers de trace)


� Outil ADRCI

� Permet d’identifier les problèmes

� Utilisation

• $ adrci

• adrci> help


• adrci> help

Le fichier de contrôle


BASE DE DONNEES

101010101010

010010010101





010101010101010101010101010101010101010010

1010101001

Rappel: les fichiers de la base de données


Fichier paramètre

Fichiers journaux


1010101001

01

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101

010101010101010101010101010101


� Son contenu

� Nom de la base de données et sa date de création

� La taille par défaut du bloc Oracle

� Le nom et l’emplacement des fichiers de la base base de données

� Le nom et l’emplacement des fichiers journaux

� Le nom des espaces logiques


� Le nom des espaces logiques

� Le numéro de séquence du fichier journal courant

� L’emplacement des archivelogs

� Situation sur les points de synchronisation (CHECKPOINT)

� Le SCN (system change number) courant

� Informations utilisées par RMAN (Recovery Manager)


� Caractéristiques

� Fichier binaire

� Chaque base a son propre fichier de contrôle

� Mise à jour du fichier effectuée par Oracle

� Taille du fichier


� Taille du fichier

• MAXLOGFILES

• MAXLOGMEMBERS

• MAXLOGHISTORY

• MAXLOGDATAFILES

• MAXINSTANCES


� Informations sur le fichier de contrôle

� V$CONTROLFILE

� V$PARAMETER

� SHOW PARAMETER CONTROL_FILES

� V$CONTROLFILE_RECORD_SECTION


� V$CONTROLFILE_RECORD_SECTION


CONTROL_FILES= ’CONTROL01.CTL’ , ’CONTROL02.CTL ’

� Multiplexage


ALTER SYSTEM SET CONTROL_FILES=<fichier1.ctl>, <fichier2.ctl>, … SCOPE=SPFILE




� Sauvegarde

� Commande

• ALTER DATABASE BACKUP CONTROLFILE TO ‘fichier’;

• ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

� Quand sauvegarder le fichier de contrôleALTER DATABASE ADD LOGFILE MEMBER


• ALTER DATABASE ADD LOGFILE MEMBER

• ALTER DATABASE DROP LOGFILE GROUP

• ALTER DATABASE DROP LOG MEMBER

• ALTER DATABASE RENAME FILE

• ALTER DATABASE ADD DATAFILE

• ALTER TABLESPACE RENAME DATAFILE

• CREATE TABLESPACE

• DROP TABLESPACE

Les fichiers journaux (Redo Log)


Les fichiers journaux:

Fonctionnement





Fonctionnement

BASE DE DONNEES

101010101010

010010010101





010101010101010101010101010101010101010010

1010101001

Rappel: les fichiers de la base de données


Fichier paramètre

Fichiers journaux


1010101001

01

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101

010101010101010101010101010101

Les fichiers journaux

� Pourquoi des fichiers journaux

� Enregistre toutes les modifications (INSERT, UPDATE, DELETE,...) de la base de données

� Utilisés en cas de perte de fichiers de données


SGA

Shared poolBuffer

redo log

SEGMENT UNDO


� Etapes d’enregistrement


Processus Oracleutilisateur

1

2

LGWR

54

3

REDO

PGA

REDO


� Leur contenu

� L’identification de la transaction (N° process user)

� La date et l’heure de la transaction (TIMESTAMP, SCN)

� L’adresse physique de la données modifiée (ROWID)

� Le type d’opération effectuée : UPDATE, INSERT, DELETE, COMMIT, ROLLBACK


Le type d’opération effectuée : UPDATE, INSERT, DELETE, COMMIT, ROLLBACK

� Les données AVANT modification

� Les données APRES modification

� L’état de la transaction (en cours, validée, invalidée)

� L’emplacement des archives


LGWR LGWR

Fichier redo log Fichier redo log Fichier redo log

� Fonctionnement


010101010101010101010101010101010101010010101010100101010101001010101010100101010101001010101010100101010101010010101010010101001101011111

11111

010101010101010101010101010101010101010010101010100101010101001010101010100101010101001010101010100101010101010010101010010101001101000000

000001

010101010101010101010101010101010101010010101010100101010101001010101010100101010101001010101010100101010101010010101010010101001101000000

000001

1 2 3

4

LGWR

Les groupes des fichiers journaux

01010101010101010101010101010101010101001010

LGWR LGWR

GROUPE 1

01010101010101010101010101010101010101001010

10101001

GROUPE 2

01010101010101010101010101010101010101001010

10101001

GROUPE 3

Disque 1

� Fonctionnement

Oracle Database : 11g DBA 1 (1Z0-052) alphorm.com™©LGWR

01010101010101010101010101010101010101001010

10101001

01010101010101010101010101010101010101001010

10101001

14

10101001

01010101010101010101010101010101010101001010

10101001

01010101010101010101010101010101010101001010

10101001

2

10101001

01010101010101010101010101010101010101001010

10101001

01010101010101010101010101010101010101001010

10101001

3Disque 2

Disque 3


� Fréquence d’écriture de LGW vers les fichiers journaux

� Toutes les 3 secondes

� Lors d’un COMMIT

� 1/3 du buffer des journaux de reprise est rempli

� 1MB d’information a été écrit dans le buffer des journaux de reprise


1MB d’information a été écrit dans le buffer des journaux de reprise

� A chaque point de synchronisation

� Lorsqu’il est déclenché par le processus « DBWn »


� Information sur les fichiers journaux

� V$LOGFILE

� V$LOG

� V$DATABASE

� V$INSTANCE


� V$INSTANCE

� Commande

• ARCHIVE LOG LIST


� Les opérations sur les fichiers journaux

� Création d’un groupe

ALTER DATABASE ADD LOGFILE GROUP 5 (’D:\ORACLE\ORADATA\...’ , ’E:\ORACLE\ORADATA\... ’) SIZE 10M;

� Création d’un membre

ALTER DATABASE ADD LOGFILE MEMBER ’D:\ORACLE\ORADATA\...’ TO GROUP 1;


� Suppression d’un groupe

ALTER DATABASE DROP LOGFILE GROUP 3;

� Suppression d’un membre

ALTER DATABASE DROP LOGFILE MEMBER D:\ORACLE\ORADATA\...’ TO GROUP 1 ;

� Switch de fichier journal

ALTER SYSTEM SWITCH LOGFILE;


� Le statut d’un groupe

� UNUSED => un groupe qui vient d’être créé

� CURRENT => le groupe courant (en cours d’utilisation)

� ACTIVE => n’est plus le groupe courant mais les données n’ont pas été écrites dans les fichiers de données


écrites dans les fichiers de données

� INACTIVE => toutes les données sont écrites dans les fichiers de données

Le mode ARCHIVELOG


Les fichiers journaux:

Le mode ARCHIVELOG





0101010101010

010101010101010101010101010101010101010010

1010101001010101010101010101010101010101010101010010

1010101001


Fichiers journaux

12

010101010101010101010101010101010101010010

1010101001010101010101017

8

� Mode NOARCHIVELOG



0101010101010101010101010101010101010100101010101001

0101010101010101010101010101010101010100101010101001

1010101001

010101010101010101010101010101010101010010

1010101001

T1

0101010101010101010101010101010101010100101010101001

2

3

T2

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001

78

9

010101010101010101010101010101010101010010

10101010011010101010101010101010101010101010101010010

10101010011

010101010101010101010101010101010101010010

10101010012

010101010101010101010101010101010101010010

10101010011

010101010101010101010101010101010101010010

10101010012

010101010101010101010101010101010101010010

10101010013010101010101010101010101010101010101010010

10101010011

010101010101010101010101010101010101010010

10101010012

010101010101010101010101010101010101010010

10101010013

010101010101010101010101010101010101010010

10101010014


� Mode ARCHIVELOG


Passer en mode ARCHIVELOG (mode MOUNT): ALTER DATABASE ARCHIVELOG

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001

010101010101010101010101010101010101010010

1010101001

TEMPS

1 2 3010101010101010101010101010101010101010010

10101010014

LGWR LGWRARCH ARCH LGWR ARCH LGWR ARCH


� Les étapes de mise en place du mode ARCHIVELOG

� Spécifié un emplacement pour l’archive au niveau de l’OS

• Paramètre: log_archive_dest_[1-10]

� Arrêt de l’instance

� Démarrage en mode MOUNT


Démarrage en mode MOUNT

� Modification du mode d’archive: ALTER DATABASE ARCHIVELOG | NOARCHIVELOG;

� Ouverture de la base de données

� Vérification

Les tablespaces


Les tablespaces:

Les tablespaces de données






Fichier de données 1 Fichier de données 2

Segment Segment Segment

Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Tablespace

Bloc Bloc

Extent

Bloc Bloc

Taille de bloc2, 4, 8, 16, 32K

� Architecture


Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc


• Les différents types de tablespaces

BASE DE DONNEES

TABLESPACE SYSTEM TABLESPACE SYSAUX


TABLESPACE UNDOTABLESPACE TEMPORARY

TABLESPACE APPLICATIF



TABLESPACE DE DONNEES


TABLESPACE DE DONNEES


� Deux type de tablespace

� SMALLFILE (par défaut) => fichier de 2^22 blocs (8KB = 32GB par fichier)

� BIGFILE => fichier de 2^32 blocs (8KB = 32TB)



� Information sur les tablespaces

� DBA_TABLESPACES

� V$TABLESPACE

� DBA_DATA_FILES

� V$DATAFILE


� V$DATAFILE

� DATABASE_PROPERTIES


� Création d’un tablespace

CREATE {BIGFILE | SMALLFILE} TABLESPACE <nom_tablespace>

[ DATAFILE [‘nom_du_fichier’] [ SIZE <valeur> {K | M | G | T } ]

[ AUTOEXTEND { OFF | ON [ NEXT <valeur> { K | M | G | T } ]

[ MAXSIZE { UNLIMITED | <valeur> {K | M | G | T } ] } ], [. . .]

[ { ONLINE | OFFLINE} ]


[ { ONLINE | OFFLINE} ]

[ { LOGGING | NOLOGGING } ]

[ BLOCKSIZE <valeur> K } ;


� Agrandissement d’un tablespace

ALTER TABLESPACE <nom_tablespace>

ADD DATAFILE ‘nom_du_fichier’ [ SIZE <valeur> {K | M | G | T } ]

[ AUTOEXTEND { OFF | ON [ NEXT <valeur> { K | M | G | T } ]

[ MAXSIZE { UNLIMITED | <valeur> {K | M | G | T } ] } ], [. . .]



� Etat des tablespaces

� ONLINE

ALTER TABLESPACE <nom_tablespace> ONLINE;

� OFFLINE

ALTER TABLESPACE <nom_tablespace> OFFLINE [{NORMAL | TEMPORARY | IMMEDIATE}];


ALTER TABLESPACE <nom_tablespace> OFFLINE [{NORMAL | TEMPORARY | IMMEDIATE}];

� READ ONLY

ALTER TABLESPACE <nom_tablespace> [ READ ONLY | READ WRITE ];

� Possibilité de mettre hors ligne un fichier de données uniquement

ALTER DATABASE <nom_base> DATAFILE <nom_fiichier> { ONLINE | OFFLINE };


� Suppression d’un tablespace

DROP TABLESPACE <nom_tablespace> INCLUDING CONTENTS [ AND DATAFILES ] [ CASCADE CONSTRAINTS ] ];


Le tablespace UNDO


Les tablespaces:

Le tablespace UNDO





Le tablespace UNDO

� A quoi sert les segments UNDO

� Conserver des modifications

� Assure la lecture cohérente

� Permet d’annuler une transaction

� Permet de récupérer une transaction


� Permet de récupérer une transaction

Le tablespace UNDO

� Fonctionnement

UNDO

Stock l’ancienne image

TABLE


Nouvelle image

TABLE

Ordre SQL LMD

Le tablespace UNDO

� Création

CREATE {BIGFILE | SMALLFILE} UNDO TABLESPACE <nom_tablespace> [ DATAFILE [‘<nom_fichier>’] [ SIZE <valeur> { K | M | G | T } ] [ AUTOEXTEND { OFF | ON { NEXT <valeur> { K | M | G | T } ] MAXSIZE { UNLIMITED | <valeur> { K | M | G | T } ] } ] [, ...] ] { ONLINE | OFFLINE} ] RETENTION { GUARANTEE | NOGUARANTEE} ]

GUARANTEE : la conservation des données est prioritaire par rapport aux transactions


transactions

NOGUARANTEE :la conservation des données n’est pas prioritaire par rapport aux transactions

Modifier la durée de conservation des données :

ALTER SYSTEM SET UNDO_RETENTION=1800;

UNDO_RETENTION: exprimé en secondes (valeur comprise entre 0 à 2^32)

Le tablespace UNDO

� Tablespace UNDO par défaut d’une instance

ALTER SYSTEM SET UNDO_TABLESPACE=<nom_tablespace>;

� Suppression d’un tablespace UNDO

DROP TABLESPACE <nom_tablespace>;


Le tablespace UNDO

� Information sur l’UNDO tablespace

� DBA_ROLLBACK_SEGS

� DBA_TABLESPACES (champ RETENTION)

� Commande

• SHOW PARAMETER UNDO TABLESPACE;


• SHOW PARAMETER UNDO TABLESPACE;

Le tablespace TEMPORAIRE


Les tablespaces:







� A quoi sert un tablespace TEMPORAIRE

� Opérations de tri importantes

� Création d’index



� Création

CREATE {BIGFILE | SMALLFILE} TEMPORAY TABLESPACE <nom_tablespace> [ TEMPFILE [‘<nom_fichier>’] [ SIZE <valeur> { K | M | G | T } REUSE ]

[ AUTOEXTEND { OFF | ON [ NEXT <valeur> { K | M | G | T } ] [ MAXSIZE { UNLIMITED | <valeur> { K | M | G | T } ] } [, ...] ] { ONLINE | OFFLINE} ];



� Tablespace TEMPORAIRE par défaut

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <nom_tablespace>;

� Suppression d’un tablespace UNDO

DROP TABLESPACE <nom_tablespace>;



� Information sur les tablespace TEMPORAIRE

� V$TEMPFILE

� DATABASE_PROPERTIES


Tablespace de données avec une taille de bloc non standard


bloc non standard

Les tablespaces:

Taille de bloc non standard






� Rappel sur le bloc Oracle

� Sa fonction

• Unité d’échange entre données disque et la mémoire Oracle

• SHOW PARAMETER DB_BLOCK_SIZE




• 2K, 4K, 8K, 16k, 32K

• Les zones mémoires à initialiser

• db_2k_cache_size, db_4k_cache_size

• db_8k_cache_size, db_16k_cache_size

• db_32k_cache_size


� Tailles non standards

db_16k_cache_sizedb_4k_cache_size


Les vues du dictionnaire de données


Le dictionnaire de données







� Appartient à l’utilisateur SYS

� Stocke les informations sur:

• La base de données (structure logique et physique)

• Le nom et les définitions des différents objets crées


• Le nom et les définitions des différents objets crées

• Les contraintes sur les objets

• Les users de connexions et leur caractéristiques (ex: privilèges)

• L’audit sur une base de données


� Les vues du dictionnaire de données

� DBA_* : informations sur les objets de tous les schémas

� USER_* : informations sur les objets appartenant au compte connecté

� ALL_* : informations des vues USER_* et des informations sur les objets pour lesquels des privilèges ont été octroyés au groupe PUBLIC ou à l’utilisateur courant


lesquels des privilèges ont été octroyés au groupe PUBLIC ou à l’utilisateur courant

� Liste des vues du dictionnaire

• DICTIONARY ou DICT


� Les vues dynamiques

� Identifiées avec le préfixe V_$ (synonym V$)

• Accessible uniquement pour l’utilisateur SYS et pour les utilisateurs ayant le privilège SYSDBA

� Liste des vues dynamiques


Liste des vues dynamiques

• V$FIXED_TABLE


� Les vues pour les objets utilisateur

� DBA_CATALOG

� DBA_OBJECTS

� DBA_TABLES

� DBA_TAB_COLUMNS


DBA_TAB_COLUMNS

� DBA_VIEWS

� DBA_INDEXES

� DBA_SYNONYMS

� DBA_CONSTRAINTS

� ...


� Les vues pour le stockage

� DBA_TABLESPACES

� DBA_DATA_FILES

� DBA_SEGMENTS

� DBA_TS_QUOTAS


DBA_TS_QUOTAS

� DBA_EXTENTS


� Les vues pour les utilisateurs et les privilèges

� DBA_USERS

� DBA_ROLES

� DBA_SYS_PRIVS

� DBA_TAB_PRIVS


DBA_TAB_PRIVS

� DBA_COL_PRIVS

� DBA_ROLE_PRIVS

La gestion du stockage



� ArchitectureBASES DE DONNEES

TABLESPACE FICHIER

1,N 1,1

1,N

1,1

1,11,N

0,N1,1 1,1

1,1


SCHEMA SEGMENT

EXTENT

BLOC ORACLE BLOC OS

1,1

1,N

1,N

0,N

0,N

1,11,N

1,N1,1

1,NDB_BLOCK_SIZE


� Les types de segments

� Table

� Index

� Table organisée en index

� Partition d’index

� Segment UNDO


� Segment UNDO

� Segment temporaire

� Segment LOB

� Table imbriquée

� Segment de démarrage

� Partition de table

� Cluster

TABLESPACE

datafile


Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

1

2 1


Extent

Bloc Bloc

Extent

Bloc Bloc

Extent

Bloc Bloc

Segment de table

3

Segment d’index

1Segment d’index


DBA_SEGMENTS DBA_TABLESPACES

1,1

0,N

1,1

1,1

� Les informations sur le stockage


DBA_EXTENTS DBA_DATA_FILES

1,N 1,N

1,1

0,N


� Allocation et libération des extents

� Un extent est alloué lorsque le segment est:

� Crée

� Etendu

� Modifié


� Un extent est libéré lorsque le segment est:

� Supprimé

� Modifié

� Vidé

� Redimensionné automatiquement


� Mode d’allocation des extents

� AUTOALLOCATE: la taille des extents est calculée automatiquement par Oracle

� UNIFORM: la taille des extents est uniforme



� Le bloc oracle

Utiliser pour permettre des insertions ou des mises à jours

En-tête

Espace libre

Table directory

Répertoire des enregistrements


insertions ou des mises à jours nécessitant de l’espace en plusè

Espace libre

Données

Oracle Net et architecture réseau







réseau


Client

LISTENER1

2

34

sqlplus user/password@descripteur

� Processus de connexion


ServeurOracle

Processus serveur

PGA

34

5


� Configuration du listener

LISTENER

Listener.ora

# listener.ora Network Configuration File: $ORACLE_HOME/network/admin/listener.ora

GESCOM


ServeurOracle

Processus serveur

PGA

GESCOM(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=pyramide01) (PORT=1522))

)

SID_LIST_GESCOM =(SID_LIST =(SID_DESC =

(SID_NAME = ORCL) (ORACLE_HOME = /u01/app/ora11g/product/11.2.0/db_1)

))

$ORACLE_HOME/network/admin/listener.ora%ORACLE_HOME%\network\admin\listener.ora


� Gestion du listener

LISTENER

Listener.ora

� START� STOP� STATUS� RELOAD


ServeurOracle

Processus serveur

PGA

� TNSPING


� Configuration du client

tnsnames.ora

# tnsnames.ora Network Configuration File: $ORACLE_HOME/network/admin/tnsnames.ora

gescom =(DESCRIPTION =

(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST =pyramide01)(PORT = 1521))


Client

(ADDRESS = (PROTOCOL = TCP)(HOST =pyramide01)(PORT = 1521)))(CONNECT_DATA =

(SERVER = DEDICATED)(SERVICE_NAME = gescom)

))


� Oracle Net Manager: netmgr



� Net Configuration Assistant: netca


Gérer la sécurité utilisateur


Gérer la sécurité utilisateurs:

Les utilisateurs





Les utilisateurs


� USER = SCHEMA

� Dispose de son propre espace de stockage permanent

� Dispose de son proproe espace de stockage temporaire

� Associé à un profil


� Associé à un profil

� Possibilité de définir un quota

� Activé ou désactivé

Les utilisateurs

� Création

CREATE USER <user> IDENTIFIED { BY PASSWORD | EXTERNALLY | GLOBALLY AS ‘external_name’ }

[ DEFAULT TABLESPACE <nom_tablespace_permanent> ]

[ TEMPORARY TABLESPACE <nom_tablespace_temporaire> ]

[ QUOTA { <taille> [ K | M ] | UNLIMITED } ON <nom_tablespace> [ . . . ] ]

[ PROFILE <nom_du_profil> ]


[ PROFILE <nom_du_profil> ]

[ PASSWORD EXPIRE ]

[ ACCOUNT { UNLOCK | LOCK } ] ;

Les utilisateurs

� Informations sur les utilisateurs

� DBA_USERS

� DBA_PROFILES


Les utilisateurs

� Gestion d’un utilisateur

• Modification

ALTER USER <user> . . .;

• Suppression

DROP USER <user> [ CASCADE ];


DROP USER <user> [ CASCADE ];

Les profils


Les profils

� Utilité

� Gérer les mots de passe

• Complexité

• Expiration

• Historique

• Verrouillage


• Verrouillage

� Gérer les ressources

• Limitation des ressources

Note: profile par défaut DEFAULT

Les profils

� Paramètres de gestion des mots de passe

� FAILED_LOGIN_ATTEMPTS (nombre de tentatives en échecs avant verrouillage)

� PASSWORD_LIFE_TIME (durée de vie du mot de passe en jour)

� PASSWORD_REUSE_TIME (durée de non réutilisation du mot de passe en jour)

� PASSWORD_REUSE_MAX (nombre de changement nécessaire avant réutilisation en jour)


PASSWORD_REUSE_MAX (nombre de changement nécessaire avant réutilisation en jour)

� PASSWORD_LOCK_TIME (durée de verrouillage en jour)

� PASSWORD_GRACE_TIME (durée de grâce en jour)

� PASSWORD_VERIFY_FUNCTION (fonction de vérification de la complexité du mot de passe)

Les profils

� Paramètres de gestion des ressources

� SESSION_PER_USER (nombre de sessions simultanées pour un même utilisateur)

� CPU_PER_SESSION (temps processeur d’une session en centièmes de secondes)

� CPU_PER_CALL (temps processeurs des appels)

� CONNECT_TIME (temps de connexion autorisé en minutes)


CONNECT_TIME (temps de connexion autorisé en minutes)

� IDLE_TIME (période d’inactivité en minutes)

� LOGICAL_READS_PER_SESSION (nombre total de blocs de données lus)

� PRIVATE_SGA (taille d’allocation dans le pool partagé pour un serveur partagé)

� COMPOSITE_LIMIT (composition des limites CPU_PER_SESSION, CONNECT_TIME, LOGICAL_READS_PER_SESSION et PRIVATE_SGA)

Les profils

� Création

CREATE PROFILE <nom_profil> LIMIT

. . .

;


Les profils

� Exemple

• Création du profile PROFIL_APP

create profile PROFIL_APP

limit

failed_login_attempts 3


failed_login_attempts 3

password_life_time 60

password_reuse_time 120

password_lock_time unlimited

sessions_per_user 2;

Les profils

� Informations sur les profils

� DBA_PROFILES


Les profils

� Gestion d’un profil

• Modification

ALTER PROFIL <nom_profil> . . .;

• Suppression

DROP PROFIL <nom_profil>;


DROP PROFIL <nom_profil>;

Les privilèges


Les privilèges

� Type de privilèges

� Privilèges de niveau objet

• Tables, colonnes, contraintes, indexes, vues, MV, séquences, procédures, fonctions, packages, liens de base de données,synonymes, ...

� Privilèges de niveau système

CREATE TABLE


• CREATE TABLE

• CREATE ANY TABLE

• CREATE SESSION

• . . .

Les privilèges

� Privilèges SYSDBA et SYSOPER

� SYSOPER

• ALTER DATABASE NOARCHIVELOG/ARCHIVELOG

• RECOVER DATABASE

• ALTER DATABASE BACKUP CONTROLFILE TO

• ALTER DATABASE OPEN | MOUNT


• ALTER DATABASE OPEN | MOUNT

• SHUTDOWN/STARTUP

� SYSDBA

• SYSOPER PRIVILEGES

• CREATE DATABASE

• ALTER DATABASE BEGIN/END BACKUP

• RESTRICTED SESSION

• RECOVER DATABASE

Les privilèges

� Privilèges de niveau objet

. PRIVILEGES OBJETS

SELECT Table, vue, séquence, MV

INSERT Table, vue, MV

UPDATE Table, vue, MV


DELETE Table, vue, MV

ALTER Table, séquence

EXECUTE Procédure, fonction, package, bibliothèque, type abstrait

READ / WRITE Directory

REFERENCE Table, vue

INDEX Table

Les privilèges

� Octroie des privilèges système

� Syntaxe

GRANT { <privilège> | rôle | ALL PRIVILEGES } [ , ... ] TO { utilisateur | rôle | PUBLIC } [ , ... ] [ IDENTIFIED BY <mot de passe> ] [ WITH ADMIN OPTION ] ;


Les privilèges

� Octroie des privilèges objet

� Syntaxe

GRANT { <privilège> | rôle | ALL PRIVILEGES } [ , ... ] ON <[ schema ].objet>TO { utilisateur | rôle | PUBLIC } [ , ... ] [ IDENTIFIED BY <mot de passe> ] [ WITH GRANT OPTION ] ;


Les privilèges

� Révocation des privilèges

� Syntaxe

REVOKE { <privilège_objet> | <privilège_système> | rôle | ALL PRIVILEGES } [ , ... ] ON <[ schema ].objet>FROM { utilisateur | rôle | PUBLIC } [ , ... ] [ CASCADE CONSTRAINTS ];


Les privilèges

� Informations sur les privilèges

� Les vues

• DBA_SYS_PRIVS : privilèges de niveau système octroyés

• DBA_TAB_PRIVS : privilèges de niveau objet octroyés

• DBA_COL_PRIVS : privilèges de niveau colonne octroyés


• DBA_COL_PRIVS : privilèges de niveau colonne octroyés

• SESSION_PRIVS : privilèges courant de l’utilisateur connecté

� Packages

• dbms_metadata.get_ddl

• dbms_metadata.get_granted_ddl

Les privilèges

� Les rôles


• Permet de simplifier la gestion des privilèges

• Regroupement de privilèges

• Ne peut pas se connecter


• Ne peut pas se connecter

• Doit être activé si mot de passe spécifié

Les privilèges

� Les rôles

� Création

CREATE ROLE <nom rôle> [ IDENTIFIED { BY <mot de passe> | USING [ schema. ] package | EXTERNALLY | GLOBALLY AS ‘external_name’ } ];

� Modification

ALTER ROLE <nom rôle> [ IDENTIFIED { BY <mot de passe> | USING [ schema. ] package |


ALTER ROLE <nom rôle> [ IDENTIFIED { BY <mot de passe> | USING [ schema. ] package | EXTERNALLY | GLOBALLY AS ‘external_name’ } ];

� Activation

SET ROLE <nom rôle> [ IDENTIFIED { BY <mot de passe> ;

� Suppression

DROP ROLE <nom rôle>;

Les privilèges

� Les rôles standard

� CONNECT : permet d’utiliser Oracle

� RESOURCE : permet de créer des objets

� DBA : regroupe tous les privilèges de niveau système


Les privilèges

� Informations sur les rôles

� DBA_ROLES : liste des rôles de la base de données

� DBA_ROLE_PRIVS : liste des rôles et des privilèges octroyés à tous les utilisateurs de la base

� SESSION_ROLES : liste des rôles activés pour la session courante


Les privilèges

� Cas pratique


Les transactions


Les accès concurrents:

Les transactions





Les transactions

� Une transaction c’est quoi ?

� Commence à l’ouverture de session ou à la fin de la précédente transaction

� Un ensemble ATOMIQUE

� Se termine toujours par un COMMIT ou un ROLLBACK

� Une instruction de type LMD (INSERT, UPDATE, DELETE) est une transaction


� Une instruction de type LMD (INSERT, UPDATE, DELETE) est une transaction

Les transactions

� Respect de la norme ACID

A tomicité

C ohérence

I solation


D urabilité

Les transactions

� Principe d’une transaction

ROLLBACK

UPDATE

Début temps

Début de la session


Fin de la transaction précédente

COMMIT

Oracle

INSERT DELETE

Début transaction Fin

transaction

Les transactions

� Structuration d’une transaction

ROLLBACK

INSERT UPDATE DELETE


transaction

tempsSAVEPOINT n SAVEPOINT n+1Début de la session



COMMIT

transaction

Oracle

Les transactions

� Structuration d’une transaction

ROLLBACK

INSERT UPDATE DELETE


transaction

tempsSAVEPOINT n SAVEPOINT n+1



COMMIT

transaction

Oracle

L’isolation


L’isolation

� Les niveaux d’isolation

� READ UNCOMMITED

� READ COMMITED (par défaut)

� REPEATABLE READ

� SERIALIZABLE


SERIALIZABLE

� Oracle supporte les niveaux d’isolations suivants

• READ COMMITED

• SERIALIZABLE

• READ-ONLY = REPEATABLE READ

L’isolation

� Modification

� Au niveau de la transaction

SET TRANSACTION ISOLATION LEVEL <NIVEAU ISOLATION>;

� Au niveau session

ALTER SESSION SET ISOLATION_LEVEL = <NIVEAU ISOLATION>;


ALTER SESSION SET ISOLATION_LEVEL = <NIVEAU ISOLATION>;

Le verrouillage


INSTANCE

SGA

Shared Pool

Library cache

Dictionary cache


Le verrouillage

Instruction LMD

Verrou (INSERT, UPDATE, DELETE)


Processus utilisateur

PMON DBWn LGWR CKPT SMON ARCnPMON

Processus serveur

1

4

3.2

2


PGA

56

UNDO3.1

INSTANCE

SGA

Shared Pool

Library cache

Dictionary cache


2

Le verrouillage

Validation d’une

transaction


Processus utilisateur

PMON DBWn LGWR CKPT SMON ARCnPMON

Processus serveur

4Fichiers de données

PGA UNDO5

REDO

1

3

Le verrouillage

� Les types de verrous

� Les verrous de type LMD

• Le verrou LMD ROW SHARE (LOCK TABLE <nom_table> IN ROW SHARED MODE;)

• Le verrou LMD ROW EXCLUSIVE (LOCK TABLE <nom_table> IN ROW EXCLUSIVE MODE;)

• Le verrou LMD SHARE (LOCK TABLE <nom_table> IN SHARED MODE;)

Le verrou LMD SHARE ROW EXCLUSIVE (LOCK TABLE <nom_table> IN SHARE ROW


• Le verrou LMD SHARE ROW EXCLUSIVE (LOCK TABLE <nom_table> IN SHARE ROW EXCLUSIVE MODE;)

• Le verrou LMD EXCLUSIVE (LOCK TABLE <nom_table> IN EXCLUSIVE MODE;)

Le verrouillage

� Les types de verrous

� Les verrous de type LDD

• Le verrou LDD EXCLUSIVE

• Le verrou LDD SHARED

• Le verrou LDD BREAKABLE PARSE


Le verrouillage

� Information sur les verrous

� V$LOCKED_OBJECT


Maintenance de la base de données Oracle


Oracle

Maintenance de la base de données:

Calcul manuel des statistiques





Maintenance de la base de données

� A quoi servent les statistiques ?

� A améliorer les plans d’éxecution (le moins couteux)

� Les données des statistiques

� La volumétrie des tables (nombre de blocs)

La distribution des différentes valeurs des champs indexés


� La distribution des différentes valeurs des champs indexés

� La taille moyenne des tuples

� Stockage des statistiques

� Dans le dictionnaire de données (dba_tables, dba_indexes...)


� La collecte des statistiques

� Peut se faire

- Sur une table

- Sur un index

- Sur un schema


- Sur un schema

- Sur une instance


� Méthodes manuelle

� analyze table <nom_table> compute statistics;

� analyze index <nom_index> compute statistics;

� EXEC DBMS_STATS.gather_table_stats(‘SCHEMA', 'TABLE');

� EXEC DBMS_STATS.gather_index_stats(‘SCHEMA', ‘INDEX');


� EXEC DBMS_STATS.gather_schema_stats(‘SCHEMA', cascade=>TRUE, estimate_percent=> 60);

� EXEC DBMS_STATS.gather_table_stats(ownname=>'USER', tabname=>‘TABLE', estimate_percent=> 60);

� EXEC DBMS_STATS.gather_index_stats(ownname=>'USER', index_name=>‘INDEX', estimate_percent=> 60);

� EXEC DBMS_STATS.gather_database_stats;


� Méthodes manuelle

� Possibilité de supprimer les statistiques

• EXEC DBMS_STATS.delete_schema_stats(‘SCHEMA',FORCE=>TRUE');

� Possibilité de calculer les statistiques pour le système (non recommandé)

BEGIN

DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL',60);


DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL',60);

END;

/

Calcul automatique des statistiques



Calcul automatique des statistiques





statistiques


� Gérer par une tâche automatique Oracle depuis la 11g

� Géré par le package GATHER_STATS_JOB en 10g

� Possibilité d’activé ou de désactiver une tâche automatique

� Utilisation du package DBMS_AUTO_TASK_ ADMIN

dbms_auto_task_admin.disable | enable (


dbms_auto_task_admin.disable | enable (client_name IN VARCHAR2,operation IN VARCHAR2,window_name IN VARCHAR2);


� Informations sur les tâches automatiques

� Vues

• DBA_AUTOTASK_CLIENT

• DBA_AUTOTASK_CLIENT_HISTORY

• DBA_AUTOTASK_SCHEDULE


• DBA_AUTOTASK_SCHEDULE

• ...

La défragmentation


SEGMENT

La défragmentation

� La fragmentation c’est quoi ?

SEGMENT

INSERT

UPDATE

8K

Bloc Bloc Bloc Bloc Bloc Bloc Bloc Bloc


SEGMENT

Bloc Bloc Bloc Bloc Bloc Bloc Bloc Bloc

SEGMENT

UPDATEDELETE

DEFRAGMENTIONBloc Bloc Bloc Bloc Bloc

� Le niveau de flottaison

SEGMENT

La High Water Mark (ou niveau

de flottaison)

La défragmentation

Oracle Database : 11g DBA 1 (1Z0-052) alphorm.com™©251

Bloc Bloc Bloc Bloc

Bloc Bloc Bloc Bloc

Bloc Bloc Bloc Bloc

Bloc Bloc Bloc Bloc

Bloc Bloc Bloc Bloc

Bloc Bloc Bloc Bloc

Bloc Bloc Bloc Bloc

Bloc Bloc Bloc Bloc

HWM

Premier bloc du segment

Dernier bloc du segment

BLOC PLEIN BLOC PLEIN BLOC PLEIN

Ligne de données

La défragmentation

� Le chaînage des blocs


BLOC PLEIN BLOC PLEIN BLOC LIBRE

Ligne de données

La défragmentation

� Ce qui provoque la fragmentation ?

� Les instructions LMD

• DELETE

• UPDATE


La défragmentation

� Comment supprimer la fragmentation ?

� Via Import/Export

• Expdp/impdp

� En déplaçant le segment d’un tablespace vers un autre tablespaces

• ALTER TABLE <table> MOVE TABLESPACE <tablespace>;


• ALTER TABLE <table> MOVE TABLESPACE <tablespace>;

• ALTER INDEX <index> REBUILD;

� Via la commande truncate

Import/Export des données


Transfert de données:

Export avec Data Pump





Transfert de données

� Architecture générale

Serveur

Export\Import Data Pump

Sauvegarde\RestaurationServeur


ServeurOracle

ServeurOracle

Duplication avec RMAN

Base, Schemas, Tablespaces, Tables


� Data Pump

� Export des données avec expdp

• Nécessite la création d’une directory

CREATE [ OR REPLACE ] DIRECTORY directory AS 'path_name' ;

GRANT READ, WRITE ON DIRECTORY <directory> TO <user>;




� Expdp (expdp help=yes)

� Permet d’exporter

• Une base (export FULL)

• Un schema ou plusieurs schemas

• Une ou plusieurs tables



• Un ou plusieurs tablespaces

- Export de la structure seulement

- Export des données seulement

- Estimation seulement


� Informations sur les exports Data Pump

� DBA_DIRECTORIES

� DBA_DATAPUMP_JOBS

� DBA_DATAPUMP_SESSIONS

� USER_DATAPUMP_JOBS



� ...

Import avec Data Pump



� Data Pump

� Import des données avec impdp

• Nécessite la création d’une directory

CREATE [ OR REPLACE ] DIRECTORY directory AS 'path_name' ;





� Impdp ( impdp help=yes)

� Permet d’importer

• Une base (import FULL)

• Un ou plusieurs schemas




• Un ou plusieurs tablespaces


� Informations sur les exports Data Pump

� DBA_DIRECTORIES

� DBA_DATAPUMP_JOBS

� DBA_DATAPUMP_SESSIONS




� ...

Chargement avec SQL Loader



SQL Loader






� Présentation de SQL Loader

� Utilitaire Oracle pour charger de grandes quantités de données

� Charge les données à partir de fichiers textes et les insère dans une ou plusieurs tables

• Deux modes de chargement


• Deux modes de chargement

- Direct

- En passant par le moteur Oracle

� Programme qui s’éxecute en ligne de commande

• SQLLDR


� Description du fonctionnement de SQL Loader

Control File


BaseOracle

Log File

InputData File

Bad FileDiscard

File

SQL Loader DATA DATA

Enregistrement(s)en erreur(s)


� SQL Loader

� Informations dans le fichier Control File

• Le nom et l'emplacement de l'Input Data File.

• Les noms et emplacement du Fichier Bad File.

• Les noms et emplacement du Fichier Discard File.


• Le format des données de l'Input Data File.

• Les noms de tables à charger ( Loader ).

• La correspondance entre champs Input et colonnes de la table de base de données.

• Les critères de sélection qui définis les enregistrements à insérer dans les tables de base de données.


� SQL Loader

� Composition du fichier Control File

• La clause OPTIONS.

- Les options peuvent être en ligne de commande via SQLLDR ou dans le Fichier de Contrôle.

• La clause LOAD DATA.

Marque le début de chargement.


- Marque le début de chargement.

• La clause INTO TABLE.

- Identifie une ou plusieurs tables de chargement.

• La Partie BEGINDATA.

- Les DATA à charger peuvent être dans un fichier Data ou dans le Fichier de Contr


� SQL Loader

� Syntaxe générique dans du fichier Control File

{LOAD | CONTINUE_LOAD} [DATA]

[CHARACTERSET character_set]

[INFILE clause [INFILE clause...]]

[INSERT | APPEND | REPLACE | TRUNCATE]


[INSERT | APPEND | REPLACE | TRUNCATE]

INTO TABLE clause [INTO TABLE clause...]

[WHEN conditions]

[FIELDS [delimiter clause]]

[TRAILING [NULLCOLS]

[SKIP skip_count]

(field list)

[BEGINDATA]


� SQL Loader

� Informations dans le fichier Log File

• Les noms des fichiers Control File, Bad File, Discard File et Input Data File.

• Les valeurs de paramètres.

• Les champs et types de données qui ont été chargés.


• Messages d'erreurs sur les enregistrement en Fail.

• Le nombre d'enregistrements lu dans le fichier de données.

• Le nombre d'enregistrements rejetée en raison d'erreurs.

• Le nombre d'enregistrements rejetées en raison de critères de sélection.

• Le temps de charge.


� SQL Loader

� Informations dans le fichier Bad File

• Violations de contraintes d'intégrité.

• Tablespace Full.

• Erreur Oracle, ...



� SQL Loader

� Informations dans le fichier Discard File

• Les enregistrements qui ne répondent pas aux critères spécifiés dans le Control File.


Sauvegarde et Restauration


Sauvegarde et restauration:

Présentation et concept





Sauvegarde et restauration

� Types de sauvegardes

� Logique

• Données de la base (export)

� Physique

• Datafile


• Datafile

• ControleFile

• Redo Log

• ArchiveLog


� Sauvegarde logique (DataPump)

� Enregistrement des données dans un fichier externe

• Contient les commandes SQL a éxecuter (non lisible sous Oracle)

� Sauvegarde à un instant T

� Est utilisée pour


� Est utilisée pour

• Une sauvegarde logique de la base

• Montée de version du moteur Oracle

• Sauvegarde ou transfert d’un schéma ou d’une table

� Ne constitue pas une sauvegarde à part entière


� Sauvegarde physique

� Consiste à sauvegarder l’ensemble des fichiers de la base de données

• fichiers de contrôle, les controlfiles

• fichier d’initialisation, le pfile ou le spfile

• fichiers de données, les datafiles


• fichiers de données, les datafiles

• les journaux de transactions, les redologs

� Deux modes de sauvegardes

• A froid: base arrêtée

• A chaud: base ouverte (nécessite le mode ARCHIVELOG)



� Sauvegarde à froid

• Cohérente

• Utilisable pour une restauration




� Sauvegarde à chaud base ouverte

• Nécessite le mode ARCHIVELOG

• Utilisation de la clause BEGIN BACKUP et EN BACKUP

- Permet une copie cohérente de la base de données


- Permet une copie cohérente de la base de données

• Utilisable pour une restauration

- Peut être complétée par l’application des journaux de transactions archivés

- Restauration PITR (Point In Time Recovery) possible



� Les différents types de sauvegardes

• Les sauvegardes complètes, dites full (ensemble des blocs de la base)

• Les sauvegardes incrémentielles (niveau 0 ou 1)

- Incrémentale différentielle de niveau 1


- Incrémentale différentielle de niveau 1

• Sauvegarde tous les blocs modifiés depuis la dernière sauvegarde incrémentale de niveau n -1 (par défaut)

- Incrémentale cumulative de niveau 1

• Sauvegarde tous les blocs modifiés depuis la dernière sauvegarde incrémentale de niveau 0


� Stratégie de sauvegarde

� Exemple

D L M M J V S D

Sauvegarde des archivelog toutes les

30mn

SauvegardeFULL

Sauvegarde des archivelog toutes les 30mn tous les jours


0 1 1 1 1 1 1 0

Sauvegardeincrémentale

diférentielle de niveau 1


� Les moyens de sauvegardes

� Logique (DataPump)

� Physique

• Manuelle

• Scripts


• Scripts

• RMAN (Recovery Manager)

Sauvegarde à froid



� Sauvegarde à froid

� Qu’est ce qu’il faut sauvegarder ?

• v$datafile

• v$logfile

• v$controlfile


• v$controlfile

• v$tempfile

� Les étapes

• Arrêter la base

• Copier les fichiers de la base de données

• Démarrer la base

Sauvegarde à chaud



� Sauvegarde à chaud

� Pré-requis

• Base en mode ARCHIVELOG

- SELECT NAME, LOG_MODE FROM V$DATABASE;




� Qu’est ce qu’il faut sauvegarder ?

• v$datafile

• v$logfile

• v$controlfile


• v$controlfile

• v$tempfile

� Les informations

• v$backup



� Les étapes

• ALTER DATABASE BEGIN BACKUP

• Copier les fichiers de la base de données

• ALTER DATABASE END BACKUP


• ALTER DATABASE END BACKUP

Sauvegarde du fichier de contrôle



� Rappel sur le fichier de contrôle

� Fichier binaire

� Contient des informations sur la structure physique de la base

� Créé pendant la création de la base et modifié en permanence

� Indispensable pour la restauration de la base


� Indispensable pour la restauration de la base


� Quand sauvegarder le fichier de contrôle ?

• A chaque modification de la structure de la base

- Ajout de fichier de données ou fichier journal

- Renommage de fichier de données ou fichier journal

- Suppression de fichier de données ou fichier journal


- Suppression de fichier de données ou fichier journal


� Deux méthodes de sauvegardes

� Au format binaire

• ALTER DATABASE BACKUP CONTROLFILE TO <fichier>;

� Au format texte




- Résultat stocké dans USER_DUMP_DEST

Présentation de RMAN



� RMAN (Recovery Manager)

� Disponible en standard depuis la version 8 de Oracle

� Permet de gérer les sauvegarde/restauration d’une manière optimale

� Successeur de EBU (Enterprise Backup Utility)

� Ecrit en PRO*C


� Ecrit en PRO*C



� Permet de réaliser des sauvegardes

• Globales de la base

• D'espaces disque logiques (tablespace),

• De fichiers de données (datafiles),


• De fichiers de données (datafiles),

• De fichiers de contrôle (controlfiles)

• De fichiers d'archive (archivelog).



� Avantages

• Evite de sauvegarder les blocs Oracle vides

• S'interface avec les outils de sauvegarde externe (gestionnaire de médias).

• Garde la trace des sauvegardes


• Effectue des restaurations globales ou partielles.

• Parallélisation des opérations de sauvegarde/restauration afin d'accroître les performances.

• Gère les périodes de conservation des sauvegardes.

• Place les opérations de sauvegarde/restauration courantes dans le catalogue sous forme de scripts (à la condition d'utiliser le catalogue RMAN).



� Permet aussi

• De dupliquer une base de données de manière simple.

• De mutualiser les scripts de sauvegardes, ils ne sont pas dépendants du système d'exploitation. RMAN dispose de son propre langage de script.


• D'éditer des rapports.

• De vérifier les sauvegardes



� Différents types sauvegardes avec RMAN

• COMPLET (ou FULL) : on sauvegarde tous les blocs

• DIFFERENTIEL (incrémentielles): on sauvegarde uniquement les blocs modifiés depuis la précédente sauvegarde de niveau n ou inférieur


modifiés depuis la précédente sauvegarde de niveau n ou inférieur

• CUMULATIF (incrémentielles): on sauvegarde uniquement les blocs modifiés depuis la précédente sauvegarde de niveau n-1



� Trois niveau de sauvegardes incrémentielles

• Niveau 0 : Base de tous les autres niveaux (Sauvegarde de l'ensemble des blocs contenant des données)

• Niveau 1 : Sauvegarde tous les blocs qui ont changé depuis la plus récente sauvegarde incrémentielle de niveau 0


• Niveau 1 : Sauvegarde tous les blocs qui ont changé depuis la plus récente sauvegarde incrémentielle de niveau 0

• Niveau 2 : sauvegarde tous les blocs qui ont changés depuis la plus récente sauvegarde incrémentielle de niveau 0, 1 ou 2.



� Exemple de sauvegarde INCREMENTIELLE

D L M M J V S D


0 2 2 2 1 2 2 0



� Présentation de l’architecture Globale

Base Base

Oracle

Catalogue RMAN

Base de données cible


Base Oracle

Oracle

BackupSets

BackupPiece


Restauration


Implémenter l'audit de la base de données Oracle:

Audit Oracle





Audit Oracle

Implémenter l'audit de la base de données Oracle

� L'Audit Oracle

� Outil de surveillance et de protection pour les administrateurs

• Pour controler les accès à la base, à des fins de sécurité,

• Pour vérifier que tel ou tel objet est accédé en lecture ou en écriture (sécurité ou analyse de performance),


• Pour vérifier les tentatives d'accès infructueues à des objets.


� L'Audit Oracle

� Quatre niveaux d'audit :

• Connexion / déconnexion : surveille les connexions

• Ordre SQL : audit par type d'ordre SQL utilisé (LMD, DDL)

• Privilège : audit d'un privilège SYSTEM (SELECT ANY, DROP ANY, CREATE


• Privilège : audit d'un privilège SYSTEM (SELECT ANY, DROP ANY, CREATE ANY, * ANY...)

• Objet : un ordre SQL particulier sur un objet particulier (audit SELECT sur une table)


� L'Audit Oracle

� Informations

• SYS.AUD$

- DBA_AUDIT_TRAIL : la plus générique

- DBA_AUDIT_OBJECT.


- DBA_AUDIT_OBJECT.

• Résultats d’audit sur les objets de la base Oracle.

- DBA_AUDIT_SESSION.

• Résultats d’audit sur connections et déconnections des utilisateurs Oracle.

- DBA_AUDIT_STATEMENT

• Résultats d’audit sur les commandes utilisateurs GRANT, REVOKE, AUDIT, NOAUDIT, ALTER SYSTEM.


� L'Audit Oracle

� Activation

• Paramètres

- Valeur de AUDIT_TRAIL différent de « none »

• AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }.


• AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }.

- Valeur de AUDIT_SYS_OPERATIONS = TRUE

• Active ou désactive la vérification des opérations émises par les utilisateurs se connectant avec les privilèges SYSDBA ou SYSOPER.


� L'Audit Oracle

� Valeur de AUDIT_TRAIL (modification avec ALTER SYSTEM SET)

• AUDIT_TRAIL=none (l'audit de la base de données Oracle est désactivé)

• AUDIT_TRAIL=os (indique que toutes les traces d'audit sont dirigées vers le répertoire spécifié dans le paramètre AUDIT_FILE_DEST)

• AUDIT_TRAIL=db (permet la redirection de tous les enregistrements dans la table de trace SYS.AUD$)


• AUDIT_TRAIL=db (permet la redirection de tous les enregistrements dans la table de trace SYS.AUD$)

• AUDIT_TRAIL=db,extended (permet la redirection de tous les enregistrements de traces dans la table de trace SYS.AUD$ avec en supplément les colonnes SQLBIND et SQLTEXT de la table SYS.AUD$ renseignées)

• AUDIT_TRAIL=xml (indique une écriture de tous les enregistrements d'audit dans des fichiers au format XML dirigés vers le répertoire ADUMP)

• AUDIT_TRAIL=xml,extended (indique l'enregistrement des traces d'audit dans des fichiers au format XML, avec les valeurs SQLBIND et SQLTEXT en supplément, le tout dirigé vers le répertoire ADUMP)


� L'Audit Oracle

� Activation de l’audit avec la commande sql « audit »

• AUDIT { sql_statement_clause | schema_object_clause | NETWORK } [ BY { SESSION | ACCESS } ] [ WHENEVER [ NOT ] SUCCESSFUL ] ;

� Arrêt de l’audit


• NOAUDIT ALL;

• ALTER SYSTEM SET AUDIT_TRAIL=none SCOPE=SPFILE;

• TRUNCATE TABLE SYS.AUD$;

Génération de rapports AWR (Automatic Worload Repository)


Worload Repository)

Implémenter l'audit de la base de données Oracle:

Rapport AWR (Automatic





Rapport AWR (Automatic Worload Repository)


� AWR

� Collecte et stocke les données sur

• L’utilisation de la base

• Les requêtes effectuées

• Les ressources utilisées (CPU, mémoire, I/O, TOP ten des requêtes, ...)


• Les ressources utilisées (CPU, mémoire, I/O, TOP ten des requêtes, ...)

� Utilité

• Utile dans le cas d’étude de performance et d’optimisation de base de données.


� AWR

� Activation

EXECUTE dbms_workload_repository.modify_snapshot_settings (retention=>43200, INTERVAL=>10);

� Consultation de la configuration (voir script awr.sql)


Consultation de la configuration (voir script awr.sql)

� Création d’un rapport (en SYSDBA)

• @$ORACLE_HOME/rdbms/admin/awrrpti.sql

Gestion d’une instance ASM


Gestion d’une instance ASM:

Présentation





Présentation ASM

� ASM (Automatic Storage Management)


• Gestion automatique des fichiers (données et journaux)

• Répartit automatiquement les fichiers de bases de données entre tous les disques pour l’équilibrage de charge


disques pour l’équilibrage de charge

• Actualise automatiquement la répartition des données suite a l’ajout ou le retrait d’un disque

• Nécessiste l’installation et le démarrage d’une instance appelée « ASM Instance »

Présentation ASM

� Instance ASM


• Ne monte pas de base de données

• Gère les metadatas requises pour rendre les fichiers ASM disponibles à n’importe qu’elle instance de base de données


n’importe qu’elle instance de base de données

• L’instance ASM et l’instance BDD ont accès au contenu des fichiers

• L’instance BDD communique avec l’instance ASM pour connaître la diposition des fichiers utilisés.

Présentation ASM

� Instance ASM + Instance BDD

Instance BDDSID=gescom

Instance ASMSID=+ASM

SMON SMON

PMON

LGWR

ASMBDPMON

LGWR

RBAL ARBn RBAL


Groupes de disques

Groupes de disques ASM

AIX

Groupes de disques


SUN

Groupes de disques


LINUX

Gestion des performances


Gestion des performances:

Présentation






� Les éléments de performances

� Les IO

� Le cache Oracle

• MEMORY_TARGET = SGA + PGA

� Les statististiques


� Les statististiques

� Les indexes

� Les requêtes et les plans d’exécutions


� Les outils de monitoring des performances

� ADDM ((Automatic Database Diagnostic Monitor)

• Permet d'améliorer les accès à la base

• Identifier les goulots d’étranglements

• D'expliquer les goulots d'étranglements


• D'expliquer les goulots d'étranglements

• Définit des recommandations suite aux différentes analyses


� Les outils de monitoring des performances

� SQL Tuning Advisor (tunning des requêtes)

• Permet de formuler des plans d’éxecution plus optimisés

� SQL Access Advisor (tunning de l ’applicatif)

• Pour identifier et créer un index


• Pour identifier et créer un index

� Mémory Advisor (tunning de la mémoire)

• Vue proactive de la structure de la mémoire + SGA

� Segment Advisor (optimisation du stockage)

• Pour identifier les segments fragmentés

ADDM ((Automatic Database Diagnostic Monitor)


Diagnostic Monitor)

Gestion des performances: ADDM (Automatic Diagnostic Database Monitor) et SQL Tuning Advisor





Monitor) et SQL Tuning Advisor


� ADDM (moteur d'autodiagnostic)

� Permet d'améliorer les accès à la base

� Identifier les goulots d’étranglements

� D'expliquer les goulots d'étranglements

� Permet de trouver une solution aux problèmes identifiés

� Possibilité d’utiliser des alertes


� Possibilité d’utiliser des alertes

� S'appuie sur les données capturées dans l'AWR (Automatic Workload Repository)

� Définit des recommandations suite aux différentes analyses

� Pré-requis

• STATISTICS_LEVEL = TYPICAL ou ALL

• CONTROL_MANAGEMENT_PACK_ACCESS = DIAGNOSTIC+TUNING


� Problèmes détectés par ADDM

� Goulots d'étranglement au niveau de l'UC

� Mauvaise gestion des connexions

� Conflit de verrouillage

� Capacité d'E/S

� Sous-dimensionnement des structures de mémoire Oracle (PGA, SGA, tampon du journal)


� Sous-dimensionnement des structures de mémoire Oracle (PGA, SGA, tampon du journal)

� Instructions SQL à charge élevée

� Temps PL/SQL et Java élevé

� Charge de point de vérification élevée et cause, par exemple, petits fichiers journaux

� Problèmes propres à RAC


� Les recommendations ADDM

� Sur les requêtes SQL

� La gestion de la mémoire

� Le stockage des segments

� La gestion des Rollback


� La gestion des Rollback


� L’analyse de l’ADDM

� Les vues

• DBA_ADVISOR_FINDINGS

• DBA_ADVISOR_OBJECTS

• DBA_ADVISOR_RECOMMANDATIONS


• DBA_ADVISOR_RECOMMANDATIONS

• DBA_ADVISOR_RATIONALE

• ...


� SQL Tunning Advisor

� Permet de formuler des plans d’éxecutions plus efficaces

� Utilisation du package DBMS_SQLTUNE.CREATE_TUNING_TASK


Aide et support Oracle


Aide et support





Aide et support

� La documentation Oracle

� La documentation Oracle est consultable à partir du serveur : http://www.oracle.com ou http://tahiti.oracle.com


Aide et support

� Le support Oracle

� Le site Metalink est le site de hotline en ligne : https://support.oracle.com

• La résolution des erreurs référencées

• Des patchs

• Des scripts d’administration


Conclusion


Conclusion





Conclusion

Conclusion: plan suivi

� Module 1: Architecture d'une instance Oracle

� Module 2: Installation de Oracle 11g R2

� Module 3: Architecture d’une base de données Oracle

� Module 4: Création d’une base de données

� Module 5: Les outils d’administration

� Module 6: Gérer une instance Oracle

� Module 7: Le fichier de contrôle

� Module 14: Les accès concurrents

� Module 15: Maintenance de la base de données

� Module 16: Transfert de données

� Module 17: Sauvegarde et restauration

� Module 18: Implémenter l'audit de la base de données Oracle

� Module 19: Gestion d'une instance ASM


� Module 7: Le fichier de contrôle

� Module 8: Les fichiers journaux

� Module 9: Les tablespaces

� Module 10: Le dictionnaire de données

� Module 11: Gérer les structures de stockage de base de données

� Module 12: Oracle Net et architecture réseau

� Module 13: Gérer la sécurité utilisateur

� Module 20: Gestion des performances

� Module 21: Aide et support

� Module 22: Conclusion

Conclusion

� Rappel des objectifs de la formation

� Décrire, comprendre l’architecture d’une instance et d’une base de données Oracle;

� installer et configurer Oracle Database 11g;

� prise en main des outils d’administration;

� configurer Oracle Net Services;




� surveiller et administrer les données d'annulation UNDO;

� gérer les données et les accès concurrents;

� créer et gérer des comptes utilisateur;

� réaliser les opérations élémentaires de sauvegarde/restauration;

� gérer les performances.

Conclusion

� La suite

� Formation Oracle Administration DBA 2

� Les prochaines formations Oracle sur la plateforme Alphorm:

• Oracle Dataguard

• Oracle RAC

• SQL pour Oracle


• SQL pour Oracle

• PL/SQL

• Oracle Tunning

• Oracle 12c

� A vous de jouer et de vous entrainer avant de passer l’examen de Certification Oracle

FIN


alphorm.com - support de la formation oracle database ... propos de ce cours objectifs décrire,...

Documents