chapitre 5 dba

Les 2 méthodes pour charger des données dans les tables d'une BD Oracle

SQL*Loader ; utilitaire d'export / import Oracle Data Pump

Pourquoi Data Pump a-t-il été créé ?

Les méthodes d'export/import classiques étaient très longues (exemple du cours : pour 33 Go de données, 3h30 d'export et 7 à 8h d'import)

Depuis quelle version Oracle propose Data Pump ?

Depuis Oracle 10g

Oracle Data Pump : définition

Utilitaire (côté serveur) de déplacement de données et de méta-données de masse, de manière très rapide, entre des bases de données Oracle

Data Pump : utilitaire côté client ou côté serveur ?

Côté serveur

Les 2 utilitaires fournis par Data Pump

expdp (export) et impdp (import)

Pourquoi expdp / impdp sont-ils plus performants que l'export/import classique ?

Ils utilisent le traitement parallèle

Prérequis avant d'utiliser Data Pump

Créer un Directory Oracle où seront stockés les exports (fichiers dump et log)

3 façons de lancer Data Pump

Via Enterprise Manager (EM) Database Control ; via les binaires EXPDP et IMPDP situés dans le dossier bin de ORACLE_HOME ; via le package SYS.DBMS_DATAPUMP

Les 4 modes d'export / import avec Data Pump

Mode COMPLET (FULL) ; mode SCHEMA ; mode TABLE ; mode TABLESPACE

Mode d'export/import COMPLET : paramètre

Opération demandée par le paramètre FULL

Mode d'export/import par défaut de Data Pump

Le mode SCHEMA (permet l'export ou l'import d'un ou plusieurs schémas de la base)

Mode TABLE : paramètre et rôle

Commandé par le paramètre TABLES ; permet de sélectionner des tables à exporter à partir d'un schéma

Mode TABLESPACE : rôle

Permet d'exporter les tables d'au moins un espace disque logique (tablespace)

Mode TABLESPACE : remarque importante

Seules les TABLES sont exportées, et non les tablespaces eux-mêmes

Les 3 types de fichiers générés par Data Pump

Fichiers SQL, fichiers DUMP, fichiers LOG

Fichiers SQL (Data Pump)

Contiennent les instructions LDD de création des objets de la base de données

Fichiers DUMP (Data Pump)

Contiennent les données exportées

Fichiers LOG (Data Pump)

Contiennent le journal d'historique de l'exécution du job (export ou import)

Privilèges nécessaires pour l'export / import Data Pump

EXP_FULL_DATABASE et IMP_FULL_DATABASE

À quoi servent EXP_FULL_DATABASE et IMP_FULL_DATABASE ?

Exporter/importer l'intégralité d'une BD, un tablespace, un schéma autre que le sien ou une table située dans un autre schéma

Quels utilisateurs peuvent faire un import / export d'une BD sans privilège supplémentaire ?

Tous ceux qui possèdent le rôle DBA

Commande pour afficher les paramètres d'export / d'import

expdp help=y et impdp help=y

Paramètre CONTENT

Précise les données à décharger : ALL (par défaut), DATA_ONLY ou METADATA_ONLY

CONTENT : 3 valeurs valides

ALL (défaut), DATA_ONLY (données seulement), METADATA_ONLY (métadonnées seulement)

Paramètre DIRECTORY

Objet Directory utilisé pour les fichiers dump et les fichiers log

Paramètre DUMPFILE

Liste des fichiers dump de destination (par défaut expdat.dmp), ex : DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp

Paramètre EXCLUDE

Exclut des types d'objets précis, ex : EXCLUDE=TABLE:EMP

Paramètre INCLUDE

Inclut des types d'objets précis, ex : INCLUDE=TABLE_DATA

EXCLUDE vs INCLUDE

EXCLUDE = exclure certains types d'objets de l'opération. INCLUDE = n'inclure que certains types d'objets

Paramètre FILESIZE

Précise la taille de chaque fichier dump, en octets

Paramètre FULL

Exporte (ou importe) toute la base de données ; valeur par défaut : N

Paramètre SCHEMAS

Liste des schémas à exporter (par défaut : le schéma de connexion)

Paramètre TABLES (export)

Identifie une liste de tables à exporter, d'UN seul schéma

Paramètre TABLES (import)

Identifie une liste de tables à importer

Paramètre TABLESPACES

Identifie une liste de tablespaces à exporter (ou à importer)

Paramètre TRANSPORT_FULL_CHECK

Vérifie les segments de stockage de toutes les tables (valeur par défaut : N)

Paramètre TRANSPORT_TABLESPACES (export)

Liste des tablespaces dont les métadonnées seront déchargées

Paramètre TRANSPORT_TABLESPACES (import)

Import des métadonnées de tablespaces transportables

Paramètre COMPRESSION

Réduit la taille du contenu du fichier de vidage ; mots-clés valides : METADATA_ONLY et NONE

Paramètre ENCRYPTION_PASSWORD

Clé de mot de passe pour la création de données de colonne cryptées

Paramètre REMAP_TABLESPACES (import)

Association de l'objet à un autre tablespace

Paramètre PARALLEL (import)

Modification du nombre de processus de travail actifs pour le job en cours

Paramètres présents à l'export ET à l'import

CONTENT, DIRECTORY, DUMPFILE, EXCLUDE, FILESIZE, FULL, INCLUDE, SCHEMAS, TABLES, TABLESPACES, TRANSPORT_FULL_CHECK, TRANSPORT_TABLESPACES

Paramètres propres à l'import (dans le cours)

REMAP_TABLESPACES et PARALLEL

Paramètres propres à l'export (dans le cours)

COMPRESSION et ENCRYPTION_PASSWORD

Exemple du cours, étape 1 : préparer l'utilisateur

create user exp_imp identified by expimp; grant connect, resource to exp_imp; grant exp_full_database, imp_full_database to exp_imp;

Exemple du cours, étape 2 : créer le Directory

connect sys/sys as sysdba ; create or replace directory DATAPUMP as 'C:\oraclexe'; grant read, write on directory DATAPUMP to exp_imp;

Privilèges à donner sur un Directory pour utiliser Data Pump

grant read, write on directory nom_directory to utilisateur;

Exemple du cours, étape 3 : table à exporter

create table test(a number); puis 3 insert (valeurs 1, 2, 3) et commit;

Exemple du cours, étape 4 : export de la table test

expdp exp_imp/expimp tables=exp_imp.test DIRECTORY=DATAPUMP DUMPFILE=test.dump LOGFILE=test.log

Résultat de l'export de l'exemple

Création de 2 fichiers, test.dump et test.log, dans le répertoire Oracle Directory 'C:\oraclexe'

Exemple du cours : où lance-t-on la commande expdp / impdp ?

On tape « host » dans SQL*Plus, puis on exécute la commande à l'invite système (C:>) ; ensuite « exit » pour revenir à SQL

Exemple du cours, étape 5 : import des données dans la table test

impdp exp_imp/expimp content=DATA_ONLY tables=exp_imp.test DIRECTORY=DATAPUMP DUMPFILE=test.dump LOGFILE=test.log

Résultat de l'import de l'exemple (content=DATA_ONLY)

Les 3 lignes exportées sont ajoutées une 2e fois à la table test, qui en contient donc 6

Vue pour suivre le déroulement d'un job d'export ou d'import

DBA_DATAPUMP_JOBS

Colonnes de DBA_DATAPUMP_JOBS (exemple du cours)

OWNER_NAME, JOB_NAME, OPERATION, JOB_MODE, STATE

Exemple de ligne de DBA_DATAPUMP_JOBS

OWNER_NAME = DVP ; JOB_NAME = SYS_IMPORT_TABLE_01 ; OPERATION = IMPORT ; JOB_MODE = TABLE ; STATE = DEFINING

Package PL/SQL pour piloter Data Pump par programme

SYS.DBMS_DATAPUMP

DBMS_DATAPUMP.OPEN

Crée un nouveau job (état valide : Undefined)

DBMS_DATAPUMP.ADD_FILE

Spécifie un fichier pour le jeu de fichiers dump, le fichier log ou la sortie SQL_FILE

ADD_FILE : états valides

Defining (export et import) ; Executing et Idling (seulement pour spécifier des fichiers dump d'un job d'export)

DBMS_DATAPUMP.ATTACH

Permet à une session de surveiller un job ou de redémarrer un job arrêté ; échoue si le jeu de fichiers dump ou la table maître du job ont été supprimés ou modifiés

DBMS_DATAPUMP.DETACH

Déconnecte une session utilisateur d'un job (valide dans tous les états)

DBMS_DATAPUMP.DATA_FILTER

Restreint les données traitées par un job

DBMS_DATAPUMP.METADATA_FILTER

Restreint les métadonnées traitées par un job

DBMS_DATAPUMP.METADATA_REMAP

Remappe les métadonnées traitées par un job

DBMS_DATAPUMP.METADATA_TRANSFORM

Modifie les métadonnées traitées par un job

DATA_FILTER vs METADATA_FILTER

DATA_FILTER restreint les DONNÉES traitées ; METADATA_FILTER restreint les MÉTADONNÉES traitées

DBMS_DATAPUMP.GET_DUMPFILE_INFO

Récupère les informations d'en-tête d'un fichier dump

DBMS_DATAPUMP.GET_STATUS

Obtient l'état d'un job

DBMS_DATAPUMP.LOG_ENTRY

Ajoute une entrée au fichier log

DBMS_DATAPUMP.SET_PARALLEL

Spécifie le parallélisme d'un job (états : Defining, Executing, Idling)

DBMS_DATAPUMP.SET_PARAMETER

Modifie le traitement par défaut d'un job (état : Defining)

DBMS_DATAPUMP.START_JOB

Démarre ou reprend l'exécution d'un job (états : Defining, Idling)

DBMS_DATAPUMP.STOP_JOB

Lance l'arrêt d'un job (états : Defining, Executing, Idling, Stop Pending)

DBMS_DATAPUMP.WAIT_FOR_JOB

Attend la fin d'un job

Procédures DBMS_DATAPUMP dont le seul état valide est Defining

DATA_FILTER, METADATA_FILTER, METADATA_REMAP, METADATA_TRANSFORM, SET_PARAMETER

Les 16 procédures du package DBMS_DATAPUMP

ADD_FILE, ATTACH, DATA_FILTER, DETACH, GET_DUMPFILE_INFO, GET_STATUS, LOG_ENTRY, METADATA_FILTER, METADATA_REMAP, METADATA_TRANSFORM, OPEN, SET_PARALLEL, SET_PARAMETER, START_JOB, STOP_JOB, WAIT_FOR_JOB

Export programmé avec DBMS_DATAPUMP : ordre des appels

OPEN (operation=>'EXPORT') ; ADD_FILE (fichier dump) ; ADD_FILE (fichier log) ; METADATA_FILTER ; START_JOB ; DETACH

Import programmé avec DBMS_DATAPUMP : ordre des appels

OPEN (operation=>'IMPORT') ; ADD_FILE (dump) ; ADD_FILE (log) ; SET_PARAMETER (TABLE_EXISTS_ACTION) ; START_JOB ; DETACH

Paramètres de DBMS_DATAPUMP.OPEN (exemple du cours)

operation (EXPORT ou IMPORT), job_mode (ex : TABLE), job_name (ex : 'DVP_EXPORT') ; retourne un handle (p_dph NUMBER)

DBMS_DATAPUMP.OPEN : valeur retournée

Un handle (numéro de job, ex : p_dph NUMBER) à passer à toutes les procédures suivantes

ADD_FILE : filetype=>1

Fichier dump (ex : filename=>'dvp.dmp', directory=>'DATAPUMP')

ADD_FILE : filetype=>3

Fichier log (ex : filename=>'tab_dvp.log', directory=>'DATAPUMP')

Filtrer un schéma dans un export programmé

DBMS_DATAPUMP.metadata_filter(handle=>p_dph, name=>'SCHEMA_EXPR', value=>'IN (''DVP'')');

Paramètre pour écraser une table existante lors d'un import programmé

DBMS_DATAPUMP.set_parameter(handle=>p_dph, name=>'TABLE_EXISTS_ACTION', value=>'REPLACE');

Gestion d'erreur dans les exemples DBMS_DATAPUMP

EXCEPTION WHEN OTHERS THEN dbms_output.put_line('erreur :'||sqlerrm||' Job-ID :'||p_dph);

Instruction pour afficher les messages dbms_output

SET SERVEROUTPUT ON SIZE 1000000

SQL*Loader : définition

Utilitaire qui charge les données de fichiers externes dans des tables d'une base de données Oracle

SQL*Loader : point fort

Puissant moteur d'analyse (parse) des données, qui ne limite que très peu le format des données du fichier

Éléments d'entrée de SQL*Loader

Input data files (données à charger) et Control file (instructions de chargement)

Éléments de sortie de SQL*Loader

Log file ; Bad file ; Discard file (optionnel) ; données insérées dans les tables/index de la BD

Les 4 fichiers de SQL*Loader (selon le plan du cours)

Log File, Bad File, Discard File, Control File

Schéma SQL*Loader : les 2 étapes internes

Field processing (traitement des champs) puis Record selection (sélection des enregistrements)

Schéma SQL*Loader : enregistrements REJETÉS par le field processing

Envoyés dans le Bad file

Schéma SQL*Loader : enregistrements DISCARDED à la sélection

Envoyés dans le Discard file (optionnel)

Schéma SQL*Loader : enregistrements rejetés par le serveur Oracle

Envoyés dans le Bad file (ex : violation de contrainte)

Schéma SQL*Loader : enregistrements SELECTED

Transmis au serveur Oracle, qui les insère dans les tables

Log File : rôle

Enregistre les activités de SQL*Loader durant un chargement de données