Oracle Database 10g Data Pump: George H. Claborn, Oracle Corporation Data Pump Data Pump Oracle Database Oracle Database 10g Data Pump PL/SQL DBMS_DATAPUMP Data Pump expdp impdp Web Enterprise Manager 4 Data Pump Streams-based Replication Logical Standby Transportable Tablespaces Oracle Data Pump Data Pump 2 Data Pump Data Pump Enterprise Edition Data Pump Data Pump Data Pump : 1 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
FILESIZE 1 40 189 MB Data Pump Data Pump Data Pump Data Pump API Oracle Database 10g API 4 expdp impdp Data Pump Oracle Database 1 Oracle Oracle Data Pump API 1 DBMS_DATAPUMP.OPEN AQ GET_STATUS MCP 1 MCP 1 MCP Data Pump MCP MCP MCP MCP <instance>_dmnn_<pid> MCP 1 API PL/SQL DBMS_DATAPUMP Oracle 10g PL/SQL : 2 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
START_JOB MCP PARALLEL N MCP <instance>_dmnn_<pid> PQ ET N Oracle RAC Data Pump DP 2 LONG 15% API DBMS_METADATA SQL DDL XML DDL XSL-T XML DDL DDL 1 : XML : 3 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
AQ Data Pump 2 : API : MCP MCP MCP MCP MCP I/O Oracle I/O OS Oracle oracle DBA Oracle DBA /private1/data/dumps dmpdir1 dmpdir1 scott SCOTT expdp expdp scott/tiger dumpfile=dmpdir1:scott.dmp FILESIZE SQL DUMPFILE : 4 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
Data Pump Data Pump Data Pump 1 2 Data Pump 1 15 45 Data Pump 2 I/O I/O I/O Dumpfile=dmpdir1:full1%u.dmp,dmpdir2:full2%u.dmp Dumpfile=dmpdir3:full3%u.dmp,dmpdir4:full4%u.dmp 4 4 full101.dmp full201.dmp full301.dmp full401.dmp full102.dmp full202.dmp full302.dmp Data Pump disk_asynch_io TRUE db_block_checksum FALSE Data Pump AQ API SGA... shared_pool_size API 40 750 MB 2 2 Oracle Enterprise Edition : 5 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
API Data Pump 1 DB API Data Pump Insert into foo (a,b,c,...) select (a,b,c,...) from foo@remote_service_name Data Pump Data Pump create as select@service DML Data Pump Data Pump STOP_JOB ATTACH=<job name> START START=SKIP_CURRENT : 6 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
(Fine-Grained) Data Pump 1 EXCLUDE EXCLUDE 3 Exclude=function Exclude=procedure Exclude=package:"like 'PAYROLL%' " PAYROLL INCLUDE INCLUDE 3 INCLUDE PAYROLL CONTENT CONTENT ROWS=N content=metadata_only content=data_only QUERY QUERY 2 1. 2. : 7 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
STATUS 1) 2) 2 MCP 0-N : EXPDP IMPDP 100% : Control C export>import> Data Pump expdp impdp 3 WHERE expdp impdp EXP_FULL_DATABASE IMP_FULL_DATABASE 3 : Oracle 10g XML XML XML : 8 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
DDL XML DDL XSL-T Impdp REMAP_SCHEMA FROMUSER / TOUSER REMAP_TABLESPACE 1 REMAP_DATAFILE TRANSFORM DDL SQL impdp SQL DDL SQL TABLE_EXISTS_ACTION IGNORE=Y Impdp TABLE_EXISTS_ACTION 4 1. SKIP: 2. APPEND: 3. TRUNCATE: 1) 2) : 4. REPLACE: CONTENT DATA_ONLY METADATA_ONLY BOTH VERSION expdp Data Pump VERSION Data Pump ENTERPRISE MANAGER Data Pump Data Pump Data Pump DBA DBA_DATAPUMP_JOBS: Data Pump USER_DATAPUMP_JOBS: Data Pump DBA_DATAPUMP_SESSIONS: Data Pump V$SESSION_LONGOPS: 1 Data Pump OPNAME Data Pump : 9 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
Oracle 5 Oracle9i Data Pump Oracle9i Data Pump Oracle9i Oracle Database 10g expdp VERSION Data Pump / Data Pump Data Pump Data Pump 10 MCP 2 3 DDL Data Pump XML DDL 7X Data Pump 13% gzip Data Pump Data Pump PARALLEL CPU I/O : 10 Oracle Corporation ORACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES
Data Pump export.log import.log Data Pump Data Pump expdp impdp Data Pump : Oracle Corporation O RACLE DATABASE 10G DATA PUMP: FOUNDATION FOR ULTRA-HIGH SPEED DATA MOVEMENT UTILITIES 11