1 / 52

Hidden Gems of Oracle Data Pump

Hidden Gems of Oracle Data Pump. Jim Stenoish, Director Lee Barton, Senior Manager. Agenda. <Insert Picture Here>. Compression and Encryption Metadata Differ. Compression and Encryption. Compression and Encryption.

milo
Download Presentation

Hidden Gems of Oracle Data Pump

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Hidden Gems of Oracle Data Pump Jim Stenoish, Director Lee Barton, Senior Manager

  2. Agenda <Insert Picture Here> • Compression and Encryption • Metadata Differ

  3. Compression and Encryption

  4. Compression and Encryption • Compression and encryption of contents of Data Pump dump file is available in 11g • Data Pump compression requires Advanced Compression Option (ACO) • Data Pump encryption requires Advanced Security Option (ASO)

  5. Compression and EncryptionWhy Compress Data Pump Dump Files? Take up less space on disk Are copied faster Require fewer I/Os to read or write data

  6. Compression and EncryptionWhy Encrypt Data Pump Dump Files? • Hides data from users who can read the dump file • More difficult for unauthorized user who gets access to dump files to import the dump file

  7. Compression and Encryption Old exp and imp utilities • Old utilities do no compression or encryption • Files written sequentially by exp • Files read sequentially by imp • DBA needs separate programs to compress or encrypt • Use pipes to channel files between imp/exp clients and the compression/encryption programs

  8. Compression and Encryption“How to” for old exp and imp utilities • For exp • Create pipe • Start exp using pipe as the output file • Start compression or encryption program • Input is the pipe • Output is the compressed or encrypted dump file Export with compression or encryption Dump File Compress or encrypt exp Pipe

  9. Compression and Encryption“How to” for old exp and imp utilities • For imp • Create pipe • Start uncompress or decryption program • Input is the compressed or encrypted dump file • Output is the pipe • Start imp using pipe as the input file Import with compression or encryption Dump File Compress or encrypt Pipe imp

  10. Compression and Encryption Challenges for Data Pump • Old utilities access files strictly sequentially • Export writes sequentially … • …but uses some random access to update dump file • Import is read only… • …but needs random access to read dump file • Dump file is segmented • Metadata is written and read in segments • Table data is written and read in segments • Master Table contains index to objects in dump file. This direct access to each object as needed.

  11. Compression and Encryption How it Works in Data Pump • All compression and encryption handled inside Data Pump • No need to create pipes or run separate programs to compress or uncompress • Segments compressed and/or encrypted just before being written • Segments decrypted and/or uncompressed right after being read • Master table is “table of contents” for dump file with pointers to location in dump file for each object • Compression and encryption are options for dump files only; not for network mode

  12. Compression and EncryptionCommand Line Parameters for Compression • Command line parameter COMPRESSION for expdp • ALL: compresses data and metadata segments • DATA_ONLY: compress only the data segments • METADATA_ONLY: compresses only metadata segments; this is the default value • NONE: neither data nor metadata segments are compressed • Import automatically handles compressed dump files

  13. Compression and EncryptionCompression Advanced Compression Option (ACO) required for ALL or DATA_ONLY No relationship between compression of database objects and the COMPRESSION parameter Compressing the dump uses about 10% more CPU time

  14. Compression Method Dump File Size • Expdp schemas=oe,sh compression=none • 60 MB • gzip of dump file after export • 1.1 MB, 83% reduction in size • UNIX compress of dump file after export • 1.6 MB, 74% reduction in size • 1.5 MB, 75% reduction in size Compression and EncryptionFile Size Impact of Data Pump Compression

  15. Compression and Encryptionexpdp Encryption Parameters – part 1 • ENCRYPTION for expdp • ALL: encrypts data and metadata segments • DATA_ONLY: encrypts only the data segments • ENCRYPTED_COLUMNS_ONLY: encrypts only TDE encrypted columns • METADATA_ONLY: encrypts only metadata segments; this is the default value • NONE, neither data nor metadata segments are encrypted • ENCRYPTION_ALGORITHM • values are AES128 AES192, AES256

  16. Compression and Encryptionexpdp Encryption Parameters – part 2 • ENCRYPTION_MODE • PASSWORD: encryption password used to create encryption key is specified with ENCRYPTION_PASSWORD • TRANSPARENT: wallet is required to provide encryption key, no password needed • DUAL: requires ENCRYPTION_PASSWORD and wallet to construct encryption key; note that only wallet or password required at import time • ENCRYPTION_PASSWORD • required when PASSWORD or DUAL specified for ENCRYPTION_MODE • required when ENCRYPTED_COLUMNS_ONLY specified for ENCRYPTION

  17. Compression and Encryptionexpdp Encryption Parameters – part 3 • Advanced Security Option (ASO) required for encryption • No relationship between encryption of database objects and the ENCRYPTION parameters

  18. Compression and EncryptionSupport for Transparent Data Encryption • ENCRYPTION=ENCRYPTED_COLUMNS_ONLY encrypts just columns encrypted with transparent data encryption (TDE) • Parameters changes from 10.2; refer to the encryption white paper for more information about TDE support in 10.2

  19. Compression and encryptionResource Cost of Encryption • __ ____ ____ __________ ______ __ ____ __ __________ • __ __________ ______ __ ____ __ ____ ______ • ___ ___ ____ ______ _______ __ ____ ____ ________ ____ ___ _______ _________ _____

  20. Compression and encryptionFor more information… • Oracle Data Pump (Booth W-058) Virtual Collateral Rack • Oracle Technical Network (OTN) http://www.oracle.com/technology/products/database/utilities/index.html

  21. The MetadataDiffer

  22. Hidden Gems of Oracle Data Pump:The Metadata Differ New feature of the Oracle Data Pump Metadata API Used to Compare the metadata for 2 objects Show differences Generate ALTERs to make one object like the other Used by Oracle Applications Oracle Enterprise Manager Grid Control Oracle Data Pump And now available for use by customers! Requires OEM Change Management Pack license

  23. The Metadata Differ:Outline Background Problems & Requirements Solutions <Insert Picture Here>

  24. Background: What’s the Data Pump Metadata API? PL/SQL Package: DBMS_METADATA A central facility to extract and manipulate complete representations of Oracle object metadata Catalog views (e.g., USER_TABLES) give partial information For complete metadata use DBMS_METADATA Two styles of interface Programming (OPEN, SET_FILTER, ADD_TRANSFORM…) Browsing (GET_DDL, GET_XML) Available since Oracle9i Used extensively by Data Pump utilities

  25. What’s the Data Pump Metadata API?Example of Browsing API SQL> select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual; CREATE TABLE "SCOTT"."DEPT" ( "DEPTNO" NUMBER(2,0) NOT NULL ENABLE, "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13) ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "SYSTEM"

  26. The Metadata Differ:Outline Background Problems & Requirements Solutions <Insert Picture Here>

  27. Problems & Requirements, Part 1:What Customers Asked For Even in Oracle9i customers wanted a metadata comparison tool: “I want you to compare the metadata for two tables (in different databases), show me if they’re different, and give me the ALTER commands to make one like the other.”

  28. Problems & Requirements, part 2:More Requests An “editable” XML for metadata Can be edited or created from scratch Can serve as a source file for database objects, the way a .c file is the source for programs Can be versioned, put under source control, etc. Also want XML Schemas for validation DBMS_METADATA.GET_XML wasn’t quite good enough – why not?

  29. Brief Technical Detour:What DBMS_METADATA Does Oracle Dictionary Transformed XML “Full” XML Add_transform Get_xml XML (or DDL) Add_transform

  30. “Full” XML, Pro and Con Full XML is perfect for Data Pump Complete representation of dictionary metadata Can be transformed in many ways DDL External table definitions Network queries Etc., etc. But it’s not editable Opaque and undocumented Bit-encoded binary data Instance-specific values (e.g., obj#)

  31. “Full” XML Example:“EMPNO” Column <COL_LIST_ITEM> <OBJ_NUM>57425</OBJ_NUM> <COL_NUM>1</COL_NUM> <INTCOL_NUM>1</INTCOL_NUM> <SEGCOL_NUM>1</SEGCOL_NUM> <PROPERTY>0</PROPERTY> <NAME>EMPNO</NAME> <TYPE_NUM>2</TYPE_NUM> <LENGTH>22</LENGTH> <PRECISION_NUM>4</PRECISION_NUM> <SCALE>0</SCALE> <NOT_NULL>1</NOT_NULL> <CHARSETID>0</CHARSETID> <CHARSETFORM>0</CHARSETFORM> <BASE_INTCOL_NUM>1</BASE_INTCOL_NUM> <BASE_COL_TYPE>0</BASE_COL_TYPE> Etc., etc., etc.

  32. Problems & Requirements: Summary Two requirements: Metadata comparison utility Editable XML for metadata We realized we could solve both problems in an integrated way: Define an editable XML dialect for object metadata Implement a utility to compare documents written in this dialect Implement transforms to convert the diff to ALTERs The rest of this talk will address these 3 topics

  33. The Metadata Differ:Outline Background Problems & Requirements Solutions <Insert Picture Here>

  34. Solution, Part 1: SXML SXML: a SQL-like XML dialect for object metadata Simpler and more intuitive than full XML Looks like a direct translation from SQL to XML Tags correspond to SQL keywords or clause names Can be edited or created from scratch SXML for an existing object can be fetched using the new GET_SXML function

  35. SXML Example(Beginning of SXML for SCOTT.DEPT) <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0"> <SCHEMA>SCOTT</SCHEMA> <NAME>DEPT</NAME> <RELATIONAL_TABLE> <COL_LIST> <COL_LIST_ITEM> <NAME>DEPTNO</NAME> <DATATYPE>NUMBER</DATATYPE> <PRECISION>2</PRECISION> <SCALE>0</SCALE> <NOT_NULL></NOT_NULL> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>DNAME</NAME> <DATATYPE>VARCHAR2</DATATYPE> <LENGTH>14</LENGTH> </COL_LIST_ITEM>

  36. SXML: Support in DBMS_METADATA GET_SXML: a browsing API to fetch SXML for an object select dbms_metadata.get_sxml('TABLE',‘EMP') from dual; “SXML” transform: convert full XML to SXML “SXMLDDL” transform: convert SXML to DDL Currently a limited subset of object types: TABLE, INDEX, TYPE_SPEC, VIEW, etc. (others being added)

  37. Solution, Part 2: The Comparison APII.e., “the Differ” Implemented as a new PL/SQL package: DBMS_METADATA_DIFF Prerequisites XDB must be installed and enabled Requires OEM Change Management Pack license Package has both browsing (COMPARE_SXML) and programming interfaces Inputs: 2 SXML documents Output: SXML diff document

  38. What Does an SXML Difference Document Look Like? An SXML difference document is an SXML document Union of the two input documents Example: columns in the diff document = Columns that are in both input documents + Columns that are in document 1 but not in document 2 + Columns that are in document 2 but not in document 1 Differences are identified with XML attributes src=“1” or “2”: element is in one input document, not both value1=<>: the same element has different values in the two input documents

  39. SXML Difference Document: an Example Compare two similar tables with differences in their column lists: CREATE TABLE TAB1(a NUMBER, b VARCHAR2(10); CREATE TABLE TAB2(b VARCHAR2(20)); Differences: Column “a” is in TAB1 only; in the difference document, this will be shown this way: src=“1” The length of column “b” is different; in the difference document, this will be shown this way: <LENGTH value1="10">20</LENGTH>

  40. SXML Difference Document: an Example (2) <TABLE xmlns="http://xmlns.oracle.com/ku" version="1.0"> <SCHEMA>SCOTT</SCHEMA> <NAME value1="TAB1">TAB2</NAME> <RELATIONAL_TABLE> <COL_LIST> <COL_LIST_ITEM src="1"> <NAME>A</NAME> <DATATYPE>NUMBER</DATATYPE> </COL_LIST_ITEM> <COL_LIST_ITEM> <NAME>B</NAME> <DATATYPE>VARCHAR2</DATATYPE> <LENGTH value1="10">20</LENGTH> </COL_LIST_ITEM> </COL_LIST> </RELATIONAL_TABLE> </TABLE>

  41. Solution, Part 3: What to do with the differences? An inconvenient truth: some differences can’t be fixed create table tab1(a number primary key); create table tab2(a number primary key) organization index; (There is no ALTER to change the physical organization of a table.) Another inconvenient truth: some differences you may not want to fix create table cust1(custno number, custname varchar2(30)); create table cust2(custno number) (You can make cust1 like cust2 by dropping the custname column…but do you really want to destroy all that data?)

  42. Metadata API Difference Processing Returns ALTER if the diff can be fixed Tells you if it can’t You decide which ALTERs to apply The Metadata API never executes the ALTER

  43. Difference Processing: an Example SQL> create table tab1(a number primary key, b number, c varchar2(10)); SQL> create table tab2(a number primary key, c varchar2(20)) organization index; SQL> select dbms_metadata_diff.compare_alter('TABLE','TAB1','TAB2') from dual; ALTER TABLE "SCOTT"."TAB1" DROP ("B") ALTER TABLE "SCOTT"."TAB1" MODIFY ("C" VARCHAR2(20)) -- Cannot ALTER physical organization of TABLE "SCOTT"."TAB1" ALTER TABLE "SCOTT"."TAB1" RENAME TO "TAB2"

  44. Difference Processing: How to Program It Converting the differences to ALTERs is done with transforms ALTERXML transform: SXML diff document  ALTER_XML document An ALTER_XML document is an XML document containing ALTER statements, and Metadata about the ALTERs (optional) Useful for application code that will process the ALTERs programmatically (used by Oracle Enterprise Manager and Oracle Applications) ALTERDDL transform: ALTER_XML document  SQL ALTERs

  45. ALTERXML and ALTERDDL Transforms SXML Difference Document ALTER_XML Document SQL ALTERs ALTERDDL Transform ALTERXML Transform

  46. Browsing APIs Hide the Complexity COMPARE_SXML – compares 2 database objects and returns an SXML difference document COMPARE_ALTER_XML – compares 2 database objects and returns an ALTER_XML document COMPARE_ALTER – compares 2 database objects and returns a set of ALTER statements Nice feature: you can specify database link names and compare objects on different databases

  47. Summary 2 requirements Editable XML for database metadata Compare objects and fix up the differences One solution with 3 parts SXML: intuitive, SQL-like XML dialect DBMS_METADATA_DIFF: compares 2 SXML documents and produces a difference document Transforms to convert the difference document to SQL ALTERs Browsing APIs hide the complexity Future enhancements will be driven by user feedback

  48. For More Information Oracle Data Pump (Booth W-058) Virtual Collateral Rack Oracle Technical Network (OTN) http://www.oracle.com/technology/products/database/utilities/index.html

  49. Questions and Answers

More Related