Data pump

Logical Backups

A logical backup of a database involves reading a set of database records and writing them to a
file. These records are read independently of their physical location. In Oracle, the Data Pump
Export utility performs this type of database backup. To recover using the file generated from a
Data Pump Export, you use Data Pump Import.

데이터베이스의 논리적 백업은 데이터베이스 레코드 셋의 읽기와 데이터베이스 레코드 셋을 파일에 쓰는 것을 포함한다. 이 레코드들은 레코드들의 물리적 위치와 독립적으로 읽혀진다. 오라클에서, Data Pump Export utility는 이런 데이터베이스 백업을 수행한다. Data Pump Export로 생성된 파일을 이용해서 복구를 하려면 Data Pump Import를 이용해야 한다.

NOTE
Data Pump files are incompatible with those generated from the
original Export utility.

(Data Pump가 10g 부터 가능한 기능이어서, 기존의 Export utility로 생성된 파일과는 호환이 되지않는다.)


1. Creating a Directory

SQL> create directory DPXFER as '/Temp/DataPumpXfer'; -- DPXFER 라는 디렉토리 오브젝트 생성
Directory created.
SQL> grant read, write on directory DPXFER to rjb; -- 유저 rjb에게 읽기 쓰기 권한 부여
Grant succeeded.
SQL>

NOTE
In a default installation of Oracle Database 10g or 11g, a directory
object called DATA_PUMP_DIR is created and points to the directory
$ORACLE_BASE/admin/database_name/dpdump.

(10g, 11g를 기본설치 했을때, DATA_PUMP_DIR 디렉토리 오브젝트가 $ORACLE_BASE/admin/database_name/dpdump 디렉토리를 가리킴.
sys 유저 이외에 다른 유저가 위 디렉토리를 사용해서 Data Pump를 하려면
grant read, write on directory DATA_PUMP_DIR to scott;
)


2. Starting a Data Pump Export Job

You can store your job parameters in a parameter file, referenced via the PARFILE parameter of
expdp. For example, you can create a file named dp_rjb.par with the following entries:

(파라미터 파일에 파라미터를 저장해서, expdp가 파라미터 파일을 참조하도록 할 수 있음.
dp_rjb.par 라는 파일에 아래 내용을 기록)

directory=dpxfer                            // Data Pump 에 사용할 디렉토리 오브젝트 이름
dumpfile=metadata_only.dmp            // 결과를 저장할 파일 이름
content=metadata_only                    // Export할 내용

The logical data pump directory is DPXFER, the one I created earlier in the chapter. The data
pump export will only have metadata; the name of the dump file, metadata_only.dmp, reflects the
contents of the dump file. Here’s how you initiate a data pump job using this parameter file:

expdp rjb/rjb parfile=dp_rjb.par                // rjb라는 유저로 dp_rjb.par 파일의 내용대로 Export. 아래와 같이 실행해도 똑같음

( expdp rjb/rjb directory=dpxfer dumpfile=metadata_only.dmp content=metadata_only ) // identical command


Oracle will then pass the dp_rjb.par entries to the Data Pump Export job. A schema-type Data
Pump Export (which is the default) will be executed, and the output (metadata only, no table
rows) will be written to a file in the DPXFER directory. Here is the output from the expdp
command:

[oracle@dw ~]$ expdp rjb/rjb parfile=dp_rjb.par

Export: Release 11.1.0.6.0 - Production on Saturday, 25 August, 2007 9:45:57
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition
Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "RJB"."SYS_EXPORT_SCHEMA_01": rjb/******** parfile=dp_rjb.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Master table "RJB"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for RJB.SYS_EXPORT_SCHEMA_01 is:
/Temp/DataPumpXfer/metadata_only.dmp
Job "RJB"."SYS_EXPORT_SCHEMA_01" successfully completed at 09:48:12

[oracle@dw ~]$

The output file, as shown in the listing, is named metadata_only.dmp. The output dump file
contains a binary header and XML entries for re-creating the structures for the RJB schema. During
the export, Data Pump created and used an external table called SYS_EXPORT_SCHEMA_01.


특정 스키마에 대해서, 생성될 결과물의 크기를 측정만 하려면

# expdp userid/passwd estimate_only=Y schemas=ora01

특정 스키마 전체 내용 export

# expdp userid/passwd directory=dpxfer dumpfile=filename.dmp content=all schemas=schemaname



< Oracle Database 11g DBA Handbook 에서 발췌 >

자세한 내용은
http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/dp_export.htm#i1007466