Transcript Slide 1

1
Hidden Gems of Oracle Data Pump
Jim Stenoish, Director
Lee Barton, Senior Manager
Agenda
• Compression and Encryption
• Metadata Differ
<Insert Picture Here>
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 Encryption
Why 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 Encryption
Why 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
exp
Pipe
Compress or
encrypt
Dump
File
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
imp
Pipe
Compress or
encrypt
Dump
File
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 Encryption
Command 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 Encryption
Compression
• 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 and Encryption
File Size Impact of Data Pump Compression
Compression Method
Dump File Size
• Expdp schemas=oe,sh
• 60 MB
compression=none
• gzip of dump file after
• 1.1 MB, 83% reduction
export
in size
• UNIX compress of dump • 1.6 MB, 74% reduction
file after export
in size
• 1.5 MB, 75% reduction
in size
15
Compression and Encryption
expdp 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 Encryption
expdp 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 Encryption
expdp 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 Encryption
Support 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 encryption
Resource Cost of Encryption
• __ ____ ____ __________ ______ __ ____ __
__________
• __ __________ ______ __ ____ __ ____
______
• ___ ___ ____ ______ _______ __ ____ ____
________ ____ ___ _______ _________ _____
20
Compression and encryption
For more information…
• Oracle Data Pump (Booth W-058) Virtual Collateral
Rack
• Oracle Technical Network (OTN)
http://www.oracle.com/technology/products/databas
e/utilities/index.html
21
The Metadata Differ
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
“Full” XML
Dictionary
Transformed 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 API
I.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
ALTER_XML
Document
Document
SQL ALTERs
ALTERXML
ALTERDDL
Transform
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
50
51
52