2008-10-03

ORACLE DPUMP

1 Database directory

  • CREATE OR REPLACE DIRECTORY [oracle_dir] as '[path_to_directory_on_disk]';
  • DROP DIRECTORY [oracle_dir]
  • SELECT directory_name, directory_path FROM dba_directories;
  • GRANT READ, WRITE ON DIRECTORY [oracle_dir] TO [user]

2 Dpump Order

  1. type definitions
  2. table definitions
  3. table data
  4. table indexes
  5. integrity constraints, views, procedures, triggers
  6. bitmap, function-based, domain indexes

3 Views

  • SELECT job_name,owner_name,state FROM dba_datapump_jobs;

4 expdp:

  • EXPDP [user]/[pass] SCHEMAS=[schema] LOGFILE=[log_file] DIRECTORY=[directory] DUMPFILE=[dump_file] PARALLEL=[parallel write to many files - use witch option %U as sufix in file name]
  • expdp [user]/[pass] directory=[dir] dumpfile=[file]_%U.expdp logfile=[logfile] filesize=4G \ job_name=[name] cluster=N parfile=[file].par

5 impdp:

  • IMPDP [user]/[pass] DUMPFILE=[dump_file] PARFILE=[parameter_file]

6 impdp parameter file

REMAP_SCHEMA=[old_user]:[new_user>]
REMAP_TABLESPACE=[old_ts]:[new_ts]
REMAP_TABLESPACE=[old_ts]:[new_ts]
DIRECTORY=[oracle directory]
LOGFILE=[logfile]
PARALLEL=[a number of processes]
STATUS=[time in sek]
JOB_NAME=[job name]

schemas=[schema]
include=TABLE:"LIKE '[table_prefix]%'"
include=VIEW:"LIKE '[view_prefix]%'"
include=SYNONYM:"LIKE '[synonym_prefix]%'"
include=PACKAGE:"LIKE '[package_prefix]%'"
include=INDEX:"LIKE '[index_prefix]%'"
include=TRIGGER:"LIKE '[trigger_prefix]%'"
include=SEQUENCE:"LIKE '[sequence_prefix]%'"

7 Parameters

7.1 include/exclude param

  • include #only object with iclude will be exported/imported
  • exclude #all objects exclude few followed by exclude parameter will be exported/imported

7.2 parallel param

  • oracle white paper - Parallel Capabilities of Oracle Data Pump
  • only for enterprise edition
  • significantly reduce the elapsed time for large indexes
  • split job between more worker processes
  • MCP(Master Control Process) controlling the pool of worker processes

7.2.1 remarks

  • set the degree of parallelism to two times the nr of CPU
  • for expdp parallel <= nr of dump files
  • for impdp parallel <= nr of files in the dump file set

7.2.2 expdp

  • in typical export there are both data and metadata, the first worker process will unload the metadata: ts, schemas, grants, roles, tables, indexes etc. all the rest unload the data, if the metadata worker finishes and there are still data objects to unload it will start unloading the data too

7.3 dumpfile param

  • PX process ( Parallel Execution Processes)
  • parallel param <= dumpfile param
  • during expdp each woker or parallel execution Process requires exclusice access to the dump file, so having fewer dump files than the degree of parallelism will mean that some workers of PX processes will be unable to write the information they are exporting
  • during impdp the workers and PX processes can all read from the same files, but parallel parameter should be significantly larger than the number of files in the dump file set

7.4 version param

  • expdp version=10.2

8 Checking current state of dpump process

  • SELECT job_name,state FROM v$datapump_job
  • SELECT * FROM v$datapump_session

9 Estimating dump size:

  • EXPDP [user]/[pass] FULL=y ESTIMATE_ONLY=y DIRECTORY=[oracle_dir] logfile=[logfile]
  • !warning
    during estimation, dump size will be lower than original db size, because for indexes during dump only their definition is written, and finally indexes are rebuid, you must notice that index is based on ROWID which changing during dump

10 Importing tables:

  • IMPDP [user]/[pass] TABLES=[tabela] REMAP_SCHEMA=[src_schema]:[dst_schema] DIRECTORY=[directory] PARALLEL=[nr] DUMPFILE=[dumpfile]

11 Dpump by NETWORK_LINK:

source db activity:

  • CREATE DATABASE LINK [link_name] CONNECT TO [src_user] IDENTIFIED BY [src_password] USING [src_tnsname]
  • choose mode IMPDP or EXPDP (the difference is that IMPDP connect with source db, get data and write them
    directly to destination db, to specified schema, in the other side EXPDP write data only to file)
  • IMPDP [dst_user]/[dst_password] NETWORK_LINK = [link_name] DIRECTORY = [dir_name] SCHEMAS = [schema_name] LOGFILE = [logfile_name]
  • EXPDP [dst_user]/[dst_password] NETWORK_LINK = [link_name] DIRECTORY = [dir_name] SCHEMAS = [schema_name] LOGFILE = [logfile_name] DUMPFILE = [file_name]

12 Console:

  • EXPDP/IMPDP [user]/[pass] ATTACH=[job_name]
  • commands:
    COMMANDDESC
    help
    C-clog mode => console mode
    exit_clientconsole mode => system
    continue_clientconsole mode => log mode
    add_file=plik1,plik2add files
    kill_jobstop job end exit
    pararell=[nr]change numbers of process
    start_jobstart job which was stoped
    status
    stop_job[=IMMEDIATE]stop job for a while

13 ERROR

13.1 ORA-39087

  • failed directory

13.2 ORA-29283

  • failed grants for directory

13.3 ORA-31633

[ symptom ]

  • unable to create master table "SYSTEM.EXPDP_MANUAL"

[ solution ]

  • drop table system.expdp_manual
Etykiety: ,