1 / 23

Data Pump en introduksjon og praktiske erfaringer.

Data Pump en introduksjon og praktiske erfaringer. Tron Malmø-Lund OUGN Vårseminar 2010. Agenda. <Insert Picture Here>. Introduksjon Litt teori Hva er datapump Konfigurasjon Parametre Monitorering. Introduksjon. Min historie med data pump Var fornøyd med exp/imp

shilah
Download Presentation

Data Pump en introduksjon og praktiske erfaringer.

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. Data Pump en introduksjon og praktiske erfaringer. Tron Malmø-LundOUGN Vårseminar 2010

  2. Agenda <Insert Picture Here> • Introduksjon • Litt teori • Hva er datapump • Konfigurasjon • Parametre • Monitorering

  3. Introduksjon • Min historie med data pump • Var fornøyd med exp/imp • Syntes DataPump var unødvendig tungvint • Litt ny tankegang • Konfigurering • Barnesykdommer • Exclude/include opsjonene sjarmerte • Det er fremtiden

  4. <Insert Picture Here> Data Pump Litt teori

  5. “Oracle Data Pump is a new feature of Oracle Database 11g that provides high speed, parallel, bulk data and metadata movement of Oracle database contents. A new public interface package, DBMS_DATAPUMP, provides a server-side infrastructure for fast data and metadata movement. In Oracle Database 11g, new Export (expdp) and Import (impdp) clients that use this interface have been provided. Oracle recommends that customers use these new Data Pump Export and Import clients rather than the Original Export and Import clients, since the new utilities have vastly improved performance and greatly enhanced functionality.” Oracle Data Pump FAQ: What is Data pump

  6. Hva er DatapumpLitt teori

  7. Hva er DatapumpLitt teori • Lignerpå exp/imp – men er et nyttverktøy • Automatiskinstallert • Alt forgårinneidatabasen • Benytter et Directory • Default: data_pump_dir ($ORACLE_BASE/admin/<db_navne>/dpdump) • SQL> create directory dpdir as 'c:\dumpfiler'; • SQL> grant read,write on directory dpdir to scott; • Integrerti Grid Control (og db console)

  8. Hva er DatapumpLitt teori • Kommandolinjeklienter: expdp og impdp • PL/SQL pakker: DBMS_DATAPUMP og DBMS_METADATA • expdp og impdp lager PL/SQL kall basert på parametre man angir • C:> expdp full=Y dumpfile=expfull%U.dmp exclude=SCHEMA:"='HR'” • Bruk parameterfil • C:> expdp parfile=dpparfile • dpparfile: • full=Y • dumpfile=expfull%U.dmp • exclude=SCHEMA:"='HR'”

  9. Hva er DatapumpLitt teori • Opererer på eget skjema • Ikke SYS bruker • DATAPUMP_EXP_FULL_DATABASE • DATAPUMP_IMP_FULL_DATABASE • Data Pump er self-tuning • Velgerautomatisk: • Direct path • External table • Conventional path

  10. Hva er DatapumpLitt teori attach/status expdp • Master process expdp scott/tiger full=Y dumpfile=expfull%U.dmp expdp scott/tiger attach • Worker process • Master Table expfull01.dmp DATA_PUMP_DIR

  11. <Insert Picture Here> Data Pump Parametre

  12. Hva er DatapumpParametre • Filtrering • Exclude • Include • Query • Sample • DDL • Content {ALL | DATA_ONLY | METADATA_ONLY} • Sqlfile • Remap_Schema • Remap_Data

  13. Hva er DatapumpParametre • Administrative • Cluster • Compression • Directory - DATA_PUMP_DIR ($ORACLE_BASE/admin/<service_id>/dpdump) • Dumpfile (husk %U) • Reuse_dumpfiles • Logfile • Job_name • Estimate_only • Parfile • Remap_datafile / Remap_Tablespace • Table_exists_action{SKIP | APPEND | TRUNCATE | REPLACE} • Transform

  14. Hva er DatapumpParametre CREATE TABLE "HR"."EMPLOYEES" ( "EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 10240 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" ; impdp hr TABLES=hr.employees DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp TRANSFORM=SEGMENT_ATTRIBUTES:n:table CREATE TABLE "HR"."EMPLOYEES" ( "EMPLOYEE_ID" NUMBER(6,0), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25) );

  15. Hva er DatapumpParametre • Exclude eller Include • Kan ha mange klausuler • exclude=GRANTS, VIEW, PACKAGE, REF_CONSTRAINT • exclude=INDEX:"LIKE ‘TEST_%'“ • include=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')“ • Avhengigeobjekterblirogtatt med • database_export_objectsschema_export_objectstable_export_objects • Ved for mange klausuler bruk tabell • INCLUDE=TABLE:"IN (SELECT object_name FROM scott.expdp_table )"

  16. Hva er DatapumpParametre • Network_Link=source_database_link • Krever en DB-link • Henterrettfra DB uten å gå via dumpfil SQL> create user new_scott identified by tiger; SQL> grant connect, resource to new_scott; SQL> grant read, write on directory dmpdir to new_scott; SQL> grant create database link to new_scott; SQL> conn new_Scott/tiger SQL> create database link old_scott connect to scott identified by tiger using 'orcl'; C:> impdp new_scott/tiger DIRECTORY=dmpdir NETWORK_LINK=old_scott remap_schema=scott:new_scott

  17. <Insert Picture Here> Data Pump Monitorering

  18. Hva er DatapumpMonitorering • Grid Control (db console) • Dba_datapump_jobs, v$session_longops • Logg • Attach • Fraexpdp / impdp • Status • Continue_client • Kan pause, stoppe, restarte, endreparallelitet, filstørrelseogantall filer C:\>impdp new_scott/tiger attach

  19. Hva er DatapumpMonitorering

  20. Hva er DatapumpMonitorering

  21. Hva er DatapumpMonitorering

  22. Hva er Datapump? eller

More Related