Oracle introduit avec la version 10g une nouvelle méthode d'export/import appelée export/import data pump. Cette nouvelle méthodologie d'import/export data pump offre des performances améliorées et plus de souplesse pour le transfert de données en masse d'une instance à l'autre.
La migration d'une instance Oracle 10g d'une plateforme Sun Solaris SPARC vers Sun X86 AMD est le contexte de l'article. Cette migration est réalisée par export/import, la méthode des tablespaces transportables (transportable tablespaces ou TTS) n'étant pas appliquable car des tables et indexes partitionnés sont présents dans les schémas et les fonctionnalités de partitionnement ne sont pas supportées dans la technologie TTS.
Les méthodes d'export/import classiques se sont révélées très longues pour 33 Gb de données : 3h30 d'export et 7 à 8h d'import. La méthodologie des export/import data pump, nouveauté Oracle 10g, a donc été envisagée pour réduire drastiquement ce temps de migration et les tests ont été concluants malgré quelques inconvénients assez simples à résoudre.
Voici le schéma de la migration :
Dans la procédure de migration :
Cet article se concentre sur l'import Data Pump avec l'option network_link et propose une brève étude des performances du parallélisme dans les exports et imports Data Pump.
Oracle Data Pump est une fonctionnalité qui déplace des données et des méta-données en masse de manière très rapide entre des bases de données Oracle. Oracle Data Pump fournit deux nouveaux utilitaires d'export et import (respectivement expdp et impdp) très rapides et capables d'opérer les mouvements de données avec du parallélisme.
Inconvénient : impdp et expdp sont plus des utilitaires serveurs que des utilitaires clients, les fichiers d'export et d'import avec expdp et impdp ne peuvent être créés que dans les systèmes de fichiers sur les machines hébergeant les instances Oracle.
Data pump supporte 4 méthodes de mouvement de données, chacune d'elle a des fonctionnalités et des performances différentes. Par ordre décroissant d'un point de vue performances :
Data pump choisira la meilleure méthode de mouvement de données.
Officiellement, aucun tuning n'est nécessaire pour obtenir le maximum de performances avec Data Pump. Les paramètres d'initialisation à l'installation doivent être suffisants. Il faut toutefois :
L'option network_link des exports/imports data pump permet de délocaliser les traitements via un DB-LINK. Dans le contexte de l'import Data Pump, une instance cible peut être directement chargée à partir de l'instance source grâce à l'option network_link. La mise en place est très simple et l'option network_link va être très utile dans ce cas pratique pour bénéficier des processeurs et du stockage plus performants de la machine cible, la contention demeurant bien évidemment sur l'instance source.
L'option network_link est très performante et permet d'éliminer l'étape intermédiaire de création de fichiers d'export.
Contrairement aux anciens utilitaires imp et exp, un répertoire pour au moins accueillir le fichier de log de l'import data pump doit être défini au sein de l'instance oracle cible RISK avec la commande CREATE DIRECTORY. Dans cet exemple pratique, ce répertoire a pour nom logique dpump_dir :
oracle@RISK > sqlplus "/ as sysdba" SQL > create directory dpump_dir as '/sop/oracle/RISK/export';
Directory created.
Si le compte qui utilise expdp et impdp n'a pas les privilèges sysdba, les droits d'écriture et de lecture doivent être donnés sur ce répertoire logique avec la commande GRANT.
grant read,write on directory <dirname> to <username>
Une passerelle (ou DBlink) est ensuite créée dans l'instance cible RISK vers l'instance source RISKZ avec la commande CREATE PUBLIC DATABASE LINK :
CREATE PUBLIC DATABASE LINK <linkdbname> [CONNECT TO <username> IDENTIFIED BY <password>] USING 'chaîne de base de données'
Pour le cas pratique :
oracle@RISK > sqlplus "/ as sysdba" SQL > create public database link RISKZ connect to dpuser identified by ******** using 'RISKZ';
Database link created.
Le compte dpuser est créé sur l'instance source avec les rôles nécessaires pour réaliser des exports : dba ou create session + exp_full_database. L'attribution des rôles create session et exp_full_database a été choisie.
oracle@RISKZ > sqlplus "/ as sysdba" SQL > create user dpuser identified by ********; SQL > grant create session to dpuser; SQL > grant exp_full_database to dpuser;
La ligne de commande pour lancer l'import data pump d'un schéma résidant dans une instance distante avec un parallélisme défini à 4 et l'option network_link est la suivante :
impdp "'/ as sysdba'" DIRECTORY=<dirname> \
LOGFILE=<logfilename> \
SCHEMAS=<schema> \
NETWORK_LINK=<network_link> \
VERSION=10.2 \
PARALLEL=4
dirname et network_link sont respectivement le répertoire et le DB Link créés précédemment dans l'instance cible.
Pour le cas pratique (import du schéma RISK) :
impdp "'/ as sysdba'" DIRECTORY=dpump_dir \
LOGFILE=RISK_export.log \
SCHEMAS=RISK \
NETWORK_LINK=RISKZ \
VERSION=10.2 \
EXCLUDE=TABLE:\"IN \(\'COHERENCY_MSG\', \'WORKSHEET_LIST\'\)\" \
PARALLEL=4
Deux tables sont écartées avec le paramètre EXCLUDE car elles contiennent des colonnes de type LONG ou LONG RAW. Ce cas particulier est évoqué dans le paragraphe § 3-3-2 qui suit.
Globalement, lorsque l'option NETWORK_LINK est spécifiée dans l'appel des binaires impdp et expdp, il faut absolument par sécurité spécifier le paramètre VERSION, c'est à dire le niveau de version Oracle pour éviter que les binaires ne prennent en compte le paramètre COMPATIBLE des instances, paramètre qui peut être encore défini à des versions antérieures à la version 10g courante. C'est le cas ici, le paramètre COMPATIBLE est encore défini à 9.2.0.0.0 dans les instances cible et source :
SQL > select name, value from v$parameter where name='compatible'
NAME VALUE ---------------- ---------------- compatible 9.2.0.0.0
Lorsque le paramètre de compatibilité VERSION=10.2 n'est pas spécifié, des messages d'erreur effrayants sont renvoyés :
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE_STATISTICS] ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275 ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.KUPW$WORKER", line 6313 ----- PL/SQL Call Stack ----- object line object handle number name 38f6f83b8 15032 package body SYS.KUPW$WORKER 38f6f83b8 6372 package body SYS.KUPW$WORKER 38f6f83b8 2396 package body SYS.KUPW$WORKER 38f6f83b8 6944 package body SYS.KUPW$WORKER 38f6f83b8 1314 package body SYS.KUPW$WORKER 38f5f5cc8 2 anonymous block
Les chargements/déchargements ne sont pas autorisés avec l'option NETWORK_LINK lorsque des tables contiennent des colonnes de type LONG :
ORA-31679: Table data object "RISK"."COHERENCY_MSG" has long columns,
and longs can not be loaded/unloaded using a network link
Pour contourner ce problème, les tables avec des colonnes de type LONG sont exportées dans un fichier avec export data pump en local sur la machine de l'instance source RISKZ après avoir créé un répertoire dpump_dir.
oracle@RISKZ > sqlplus "/ as sysdba" SQL > create directory dpump_dir as '/mut/export/oracle';
Directory created.
oracle@RISKZ > expdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=LONGTABLES.dmp \
LOGFILE=LONGTABLES.log \
TABLES=\(RISK.COHERENCY_MSG,RISK.WORKSHEET_LIST\) \
VERSION=10.2
Le fichier d'export généré et contenant uniquement les tables avec des colonnes long est alors envoyé avec ftp vers la machine de l'instance cible dans le répertoire dpump_dir (/sop/oracle/RISK/export) puis importé avec impdp avec l'option FULL=Y et VERSION=10.2 :
oracle@RISK > impdp "'/ as sysdba'" DIRECTORY=dpump_dir \
FULL=Y \
DUMPFILE=LONGTABLES.dmp \
LOGFILE=LONGTABLES_import.log \
VERSION=10.2
La vue dba_tab_columns est exploitée pour déterminer les tables contenant des colonnes de type LONG ou LONG RAW dans un schéma :
select owner, table_name
from dba_tab_columns
where data_type in ('LONG','LONG RAW')
and owner in ('RISK');
OWNER TABLE_NAME ----------- ------------------------------ RISK WORKSHEET_LIST RISK COHERENCY_MSG
Il n'est pas nécessaire de réaliser une sélection distincte car une table ne peut contenir qu'une seule colonne de type LONG ou LONG RAW.
Il faut être très prudent avec ces tables exotiques lors d'export/import data pump car des effets de bord très indésirables sont possibles sur d'autres tables. En effet, lorsque les tables ayant des colonnes de type LONG ou LONG RAW ne sont pas écartées lors d'un export data pump, en fonction du parallélisme appliqué, d'autres tables n'ayant aucun rapport ne sont pas exportées ou importées :
expdp > . . exported "RISK"."RECOVERY_RATE" 245.8 KB 14513 rows ORA-31679: Table data object "RISK"."COHERENCY_MSG" has long columns, and longs can not be loaded/unloaded using a network link . . exported "RISK"."BUSINESS_EVENTS" 12.30 KB 99 rows
impdp >
. . imported "RISK"."DB2RISK_CODEEQ" 471.3 KB 22608 rows
. . imported "RISK"."RECOVERY_RATE" rows
Dans l'exemple ci-dessus, aucune ligne de la table RECOVERY_RATE n'est importée. Grâce au parallélisme, RECOVERY_RATE est exportée en même temps que la table COHERENCY_MSG pour laquelle l'exception ORA-31679 est levée, exception qui génère un effet de bord désastreux. Cette régression est parfaitement reproductible.
Attention : les contraintes désactivées de type P (clé primaire) ne sont pas importées par la méthodologie export/import data pump.
Il s'agit d'une belle surprise lorsque derrière un script de migration tombe en erreur en essayant de supprimer définitivement une contrainte clé primaire inactive.
La vue dba_constraints permet de retrouver rapidement les contraintes désactivées de type P dans un schéma :
SQL > select owner, constraint_type,
constraint_name, table_name
from dba_constraints
where constraint_type='P' and status='DISABLED'
and owner in ('RISK')
OWNER C CONSTRAINT_NAME TABLE_NAME ------------------------------ - ------------------------------ ------------------------------ RISK P SYS_C002626 RATING_SCALE RISK P SYS_C002628 RATING_SOURCE RISK P SYS_C002629 RISKENTRY
Une contrainte pouvant être composite (composée de plusieurs colonnes), la vue dba_cons_columns apporte les informations complémentaires nécessaires afin de regénérer les contraintes inactives :
select a.table_name, b.column_name, b.position from dba_constraints a, dba_cons_columns b where a.constraint_name = b.constraint_name and a.table_name = b.table_name and a.constraint_type='P' and a.status='DISABLED' and a.owner='RISK' order by a.table_name,b.position
TABLE_NAME COLUMN_NAME POSITION ------------------ -------------------------- -------- RISKENTRY ENTRYID 1 RISKENTRY OWNER 2 RISKUSERS IDENT 1 RULES_BUILDER RULE 1 ...
En déchargeant le résultat de la requête ci-dessous dans un fichier plat avec SQL*Plus
spool /tmp/constraints.txt select a.table_name||';'||b.column_name||';'||b.position from dba_constraints a, dba_cons_columns b where a.constraint_name = b.constraint_name and a.table_name = b.table_name and a.constraint_type='P' and a.status='DISABLED' and a.owner='RISK' order by a.table_name,b.position
RISKENTRY;ENTRYID;1 RISKENTRY;OWNER;2 RISKUSERS;IDENT;1 RULES_BUILDER;RULE;1
L'application du script constraints.awk ci-dessous permet de générer les commandes de recréation des contraintes P inactives
cat constraints.awk
BEGIN {
}
{
if ( NR == 1 ) {
str_altertable = "ALTER TABLE "OWNER"."$1" ADD PRIMARY KEY ("$2
}
else {
if ( $3 == 1 ) {
str_altertable = str_altertable" ) DISABLE;"
print str_altertable
str_altertable = "ALTER TABLE "OWNER"."$1" ADD PRIMARY KEY ("$2
} else {
str_altertable = str_altertable","$2
}
}
}
END {
str_altertable = str_altertable" ) DISABLE;"
print str_altertable
}
Génération des commandes de recréation des contraintes inactives avec le programme awk constraints.awk :
cat constraints.txt | nawk -F";" -v OWNER=RISK -f constraints.awk
... ALTER TABLE RISK.RISKENTRY ADD PRIMARY KEY (ENTRYID,OWNER ) DISABLE; ALTER TABLE RISK.RISKUSERS ADD PRIMARY KEY (IDENT ) DISABLE; ALTER TABLE RISK.RULES_BUILDER ADD PRIMARY KEY (RULE ) DISABLE; ...
Dans tous les tests de performances, les exports et imports data pump ou classiques sont réalisés vers ou depuis des fichiers.
Dans tous les tests de performances sur les exports, 4 mesures sont réalisées. La première mesure est écartée pour prendre en compte la mise en cache des données dans l'instance source. Les exports sont réalisés sur le même support de stockage (système de fichiers ZFS avec des blocs de 8k).
Les tests de comparaison des performances entre export classique et export data pump sont réalisés sur la table INFOS_HISTO qui a une taille de 195 Mb (3 567 487 lignes). La table INFOS_HISTO est classique, elle ne contient pas de typages de données particuliers (LOB, LONG, ...) et n'a pas de caractéristiques particulières (partitionnement, index IOT, cluster...). Aucun parallélisme n'est appliqué sur l'export data pump pour s'assurer que l'on est en mode "mono-thread". Voici les commandes utilisées pour les tests comparatifs :
oracle@RISK > exp "'/ as sysdba'" FILE=INFOS_HISTO.dmp \
LOG=INFOS_HISTO.log \
TABLES=RISK.INFOS_HISTO
oracle@RISK > expdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=INFOS_HISTO.dmp \
LOGFILE=INFOS_HISTO.log VERSION=10.2 \
TABLES=RISK.RISKUSERS \
PARALLEL=1
L'export data pump est 3 fois plus rapide que l'export classique.
Les comparaisons des performances des exports data pump avec parallélisme sont réalisées sur la table FUND_HISTORY_POSITIONS qui a une taille de 3,45 Gb (68616057 lignes). La table FUND_HISTORY_POSITIONS est classique, elle ne contient pas de typages de données particuliers (LOB, LONG, ...) et n'a pas de caractéristiques particulières (partitionnement, index IOT, cluster...).
Plusieurs niveaux de parallélisme sont appliqués pour exporter la table FUND_HISTORY_POSITIONS : de 1 à 6. Voici les commandes utilisées pour les tests comparatifs :
oracle@RISK > expdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=FUND_HISTORY_POSITIONS.dmp \
LOGFILE=FUND_HISTORY_POSITIONS.log VERSION=10.2 \
TABLES=RISK.FUND_HISTORY_POSITIONS
PARALLEL=[1-6]
Le meilleur temps est obtenu avec l'option PARALLEL=2, au delà une légère perte de performances est observée et le temps d'export reste constant. Pour un export data pump d'une table dans un fichier, l'option PARALLEL=2 est optimale car elle permet de paralléliser l'export des données et des meta données ou meta data (index, statistiques, grants etc...). Au delà, la perte de performances s'explique par la coordination des processus en parallèle (worker processes).
En interrogeant V$SESSION avec PARALLEL=4, un process coordinateur DM00 gouverne 4 processus DW qui réalisent l'export des données en parallèle.
SYS oracle@BERYLLIUM (DW03) oracle ACTIVE 1742 BERYLLIUM SYS oracle@BERYLLIUM (DW02) oracle ACTIVE 1740 BERYLLIUM SYS oracle@BERYLLIUM (DW04) oracle ACTIVE 1744 BERYLLIUM SYS oracle@BERYLLIUM (DW01) oracle ACTIVE 1721 BERYLLIUM SYS oracle@BERYLLIUM (DM00) oracle ACTIVE 1719 BERYLLIUM
Le nombre de CPU disponibles est également à prendre en considération lorsque le parallélisme est appliqué.
Les tests sont réalisés sur la table partitionnée HISTORIQUE_GLOBAL qui a une taille de 2,81 Gb (70 millions de lignes environ). La table HISTORIQUE_GLOBAL est partitionnée par intervalles (RANGE) sur la colonne JOUR : une partition correspond à 1 trimestre pour un total de 60 partitions (Q1 1995 à Q4 2010). Plusieurs configurations de parallélisme sont testées :
oracle@RISK > expdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=HG.dmp \
LOGFILE=HG.log VERSION=10.2 \
TABLES=RISK.HISTORIQUE_GLOBAL
PARALLEL=[1,2,4,8]
oracle@RISK > ...
expdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=HG1.dmp,HG2.dmp \
LOGFILE=HG.log VERSION=10.2 \
TABLES=RISK.HISTORIQUE_GLOBAL
PARALLEL=2
...
expdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=HG1.dmp,HG2.dmp,HG3.dmp,HG4.dmp \
LOGFILE=HG.log VERSION=10.2 \
TABLES=RISK.HISTORIQUE_GLOBAL
PARALLEL=4
...
Le graphique montre de manière très nette que le parallélisme apporte uniquement des gains de performances très significatifs sur les exports data pump lorsque le nombre de fichiers d'export est en adéquation avec le paramètre PARALLEL.
Dans tous les tests de performances, les systèmes de fichiers supportant la base Oracle 10g sont des systèmes ZFS Sun Solaris taillés avec une taille de bloc définie à 8K.
L'instance n'est pas en mode archivelog et les fichiers de redo log sont localisés sur des systèmes de fichiers avec une taille de bloc définie à 128K.
Les comparaisons des performances entre import classique et import data pump sont également réalisées sur la table INFOS_HISTO (195 Mb, 3 567 487 lignes). La table INFOS_HISTO est exportée du schéma RISK puis importée dans le schéma SPA avec les deux méthodes (export/import classique et export/import data pump). Aucun parallélisme n'est appliqué sur l'import data pump pour s'assurer que l'import data pump est en mode "mono-thread". Voici les commandes utilisées pour les tests comparatifs :
oracle@RISK > imp "'/ as sysdba'" FILE=INFOS_HISTO.dmp \
LOG=INFOS_HISTO_import.log \
FROMUSER=RISK TOUSER=SPA \
COMMIT=Y
oracle@RISK > impdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=INFOS_HISTO.dmp \
LOGFILE=INFOS_HISTO_import.log \
REMAP_SCHEMA=RISK:SPA \
PARALLEL=1
L'import data pump est 2 fois plus rapide que l'import classique pour une simple table. Le gain en performances est de 200% !
Les comparaisons des performances des imports data pump avec parallélisme sont réalisées à nouveau réalisés sur la table FUND_HISTORY_POSITIONS (3,45 Gb - 68616057 lignes).
Plusieurs niveaux de parallélisme sont appliqués pour importer la table FUND_HISTORY_POSITIONS dans le schéma SPA. Voici les commandes utilisées pour les tests comparatifs :
oracle@RISK > impdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=INFOS_HISTO.dmp \
LOGFILE=INFOS_HISTO.log VERSION=10.2 \
REMAP_SCHEMA=RISK:SPA
PARALLEL=[1,2,4,8]
Comme pour les exports data pump avec parallélisme, le meilleur temps obtenu pour l'import data pump d'une table classique est obtenu avec le paramètre PARALLEL=2, c'est à dire lorsque les méta données (META DATA) et les données sont importées en parallèle. Au delà, les performances sont très légèrement dégradées avec la coordination des process en parallèle.
Le cas de test concerne toujours la table HISTORIQUE_GLOBAL partitionnée par trimestre. La table HISTORIQUE_GLOBAL est montée dans le schéma SPA avec des configurations de parallélisme différentes.
oracle@RISK > impdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=HG.dmp \
LOGFILE=HG_import.log VERSION=10.2 \
REMAP_SCHEMA=RISK:SPA
PARALLEL=[1,4,8]
oracle@RISK > ...
expdp "'/ as sysdba'" DIRECTORY=dpump_dir \
DUMPFILE=HG1.dmp,HG2.dmp,HG3.dmp,HG4.dmp \
LOGFILE=HG_import.log VERSION=10.2 \
TABLES=RISK.HISTORIQUE_GLOBAL
PARALLEL=4
...
Comme pour les exports data pump, le parallélisme durant les imports data pump n'est significatif en performances que lorsque l'import data pump est réalisé à partir d'un nombre de fichiers en adéquation avec le paramètre PARALLEL.
| Version | Date | Commentaires |
|---|---|---|
| 1.0 | 03/2010 | Version initiale |
Utilitaires
Oracle 11g
Généralités sur
Oracle Data Pump
Livre blanc
Oracle : Data Pump pour le mouvement ultra rapide de données
Le parallélisme
avec Oracle Data Pump
Oracle Data
Pump : démarrage rapide (quick start)