Oracle 11g feature van de maand: Datapump

Oktober 2008 - Iedereen die met systeemontwikkeling te maken heeft kent het probleem: in je ontwikkel- of testomgeving wil je gegevens uit de productieomgeving hebben, maar niet iedereen hoeft salarissen, bonussen of creditcardnummers te zien.

Bovendien mogen deze gegevens niet herleidbaar zijn. Oracle biedt sinds Grid Control 10.2.0.4 deze functionaliteit in het Data Masking Pack maar hier hangt een prijskaartje aan.

Iets omslachtiger kun je dit ook met gratis meegeleverde tools doen: in de Oracle 11g Datapump zit de remap_data functionaliteit. In deze Whitebook zullen we deze functionaliteit wat uitgebreider bekijken.

Exporteren van data

Je begint met het definieren van een package voor de versleuteling (in dit geval van de creditcardnummers) :

CREATE OR REPLACE PACKAGE hidedata AUTHID CURRENT_USER AS
 FUNCTION newcc (oldid IN NUMBER) RETURN NUMBER;
 END hidedata;


 CREATE OR REPLACE PACKAGE BODY hidedata AS
   TYPE cc_list IS TABLE OF NUMBER
   INDEX BY VARCHAR2(16);
   cc_remaps cc_list;
   cc_seed NUMBER := 000000000000010;
   FUNCTION newcc (oldid IN NUMBER) RETURN NUMBER IS
 BEGIN
   IF NOT cc_remaps.EXISTS(oldid) THEN
     cc_seed := cc_seed + 10;
     cc_remaps(oldid) :=
       ROUND (oldid, -15) + -- 1st digit
       cc_seed + -- 2nd-15th digits
       MOD(oldid,10) ; -- 16th digit
   END IF;
   RETURN cc_remaps(oldid);
 END;
 END hidedata;
 / 

Hieronder zie je de creditcard-houders met hun uitgaven in de productie-database:

We doen een export middels:

expdp hr/hr
tables=holder,activity
 remap_data=holder.cardno:hidedata.newcc
 remap_data=activity.cardno:hidedata.newcc
 directory=dpump_dir1
 dumpfile=hremp2.dmp 

Het versleutelen kost natuurlijk tijd, maar dit blijkt mee te vallen; bij het exporteren van ruim 100.000 rijen gaat de doorlooptijd bij het verhaspelen van 1 kolom van 30 naar 32 seconden.

Importeren van versleutelde data

Doe de import in je testomgeving met:

impdp hr/hr dumpfile=hremp2 directory=dpump_dir1

 Import: Release 11.1.0.6.0 - Production on Tuesday, 26 August, 2008 11:16:00
 Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 ...
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 Master table "HR"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
 Starting "HR"."SYS_IMPORT_FULL_01":  hr/******** dumpfile=hremp2
   directory=dpump_dir1
 Processing object type TABLE_EXPORT/TABLE/TABLE
 Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
 . . imported "HR"."ACTIVITY"                         5.929 KB       5 rows
 . . imported "HR"."HOLDER"                           5.468 KB       3 rows
 Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
 Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
 Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
 Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
 Job "HR"."SYS_IMPORT_FULL_01" successfully completed at 11:16:05 

Het resultaat hiervan is dat de referentiële integriteit behouden is, terwijl de creditcardnummers versleuteld zijn; zie ook de afbeeldingen hieronder.

Conclusie: de remap_data optie is handig. Het kost weliswaar wat meer moeite dan met het DataMaskingPack, maar daar staat tegenover dat hier geen (extra) licentiekosten voor gemaakt hoeven te worden.

Tip: backward compatible export maken

Je kunt ook gemakkelijk vanuit een Oracle 11g database een dumpfile aanmaken om te importeren in een Oracle 10g database. Deze optie was ook al beschikbaar in Oracle 10g maar kan handig zijn bij uitwisseling van gegevens of als je bij performanceproblemen na een upgrade wilt kijken naar het gedrag in Oracle 10g. De standaard manier werkt niet:

expdp hr/hr tables=employees directory=dpump_dir1 
  dumpfile=table.dmp

 Impdp hr/hr dumpfile=TABLE.DMP directory=DATA_PUMP_DIR
 Import: Release 10.2.0.3.0 - Production on Tuesday, 26 August, 2008 14:36:06
 Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 ...
 With the Partitioning, Oracle Label Security, OLAP, Data Mining
 and Oracle Database Vault options
 ORA-39001: invalid argument value
 ORA-39000: bad dump file specification
 ORA-39142: incompatible version number 2.1 in dump file "/path/to/TABLE.DMP" 

Met opgeven van version=10.2 gaat het wel goed:

expdp hr/hr tables=employees directory=dpump_dir1 
  dumpfile=table.dmp version=10.2

 Export: Release 11.1.0.6.0 - Production on Tuesday, 26 August, 2008 14:42:42
 Copyright (c) 2003, 2007, Oracle.  All rights reserved.
 Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 ...
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 Starting "HR"."SYS_EXPORT_TABLE_01":  hr/******** tables=employees
   directory=dpump_dir1 dumpfile=table.dmp version=10.2
 . . exported "HR"."EMPLOYEES"                  16.80 KB     107 rows 

Gevolgd door:

impdp hr/hr dumpfile=TABLE.DMP directory=DATA_PUMP_DIR
  remap_tablespace=example:users

 Import: Release 10.2.0.3.0 - Production on Tuesday, 26 August, 2008 14:54:27
 Copyright (c) 2003, 2005, Oracle.  All rights reserved.
 Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 ...
 With the Partitioning, OLAP, Data Mining and Oracle Database Vault options
 . . imported "HR"."EMPLOYEES"                  16.80 KB     107 rows 

In dit voorbeeld zie je tevens het toepassen van de remap_tablespace optie: in de source database staat de tabel in de TS example, in de target database bestaat deze TS niet en wordt de users TS gebruikt

Meer interessante nieuwe opties

• ENCRYPTION
  Versleutelen van data en/of metadata aan de hand van een op te geven wachtwoord. Bij de Enterprise Edition staat deze mogelijkheid ter beschikking, een hele geruststelling als een backup ‘zoek’ raakt.
• TRANSPORTABLE
  Afgeleide van transportable tablespaces voor 1 of meer tabellen, verkort de doorlooptijd want alleen de metadata hoeft in- en uitgepakt te worden.
• PARTITION_OPTIONS
  Splitsen of samenvoegen van partities.
• REUSE_DUMPFILES
  Hergebruiken van dumpfiles, een oude versie wordt overschreven.
• REMAP_TABLE
  Bij een import een tabel een andere naam geven.
• DATA_OPTIONS
  Als er bij de import non-deferred constraint errors optreden, wordt normaal de complete tabel niet geladen. Nu kun je ervoor kiezen dat de probleemgevallen gelogd worden en de rest gewoon geladen wordt.

Referenties

• Oracle Database Utilities 11g Release 1 (B28319-02)
• Oracle Database New Features Guide 11g Release 1 (B28279-02)
• Data Transformations with Oracle Data Pump