Chủ Nhật, 29 tháng 11, 2020

Các lệnh hay sử dụng khi chuyển dữ liệu sử dụng DataPump (expdp, impdp) trong Oracle Database

Mục đích: Các lệnh hay sử dụng khi chuyển dữ liệu sử dụng DataPump (expdp, impdp) trong Oracle Database

Keyword: data pump import, export
Help: impdp help=y; expdp help=y
--0. Check
 --OS:      
    ps -ef |grep expdp
    ps -ef |grep impdp
        
--DB    
    -- job data pump
    select * from DBA_DATAPUMP_JOBS;
    
    -- session
    select * from dba_datapump_sessions;
        
    -- longops
    col table_name format a30
        
    select substr(sql_text, instr(sql_text,'"')+1, 
                   instr(sql_text,'"', 1, 2)-instr(sql_text,'"')-1) 
              table_name, 
           rows_processed, 
           round((sysdate
                  - to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
                 *24*60, 1) minutes, 
           trunc(rows_processed / 
                    ((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))
                 *24*60)) rows_per_min 
    from 
       v$sqlarea 
    where 
      upper(sql_text) like 'INSERT % INTO "%' 
      and 
      command_type = 2 
      and 
      open_versions > 0;
      
    select 
       sid, 
       serial#
    from 
       v$session s, 
       dba_datapump_sessions d
    where 
       s.saddr = d.saddr;
    select 
       sid, 
       serial#, 
       sofar, 
       totalwork 
    from    v$session_longops;
        

--1. Create database directories
SQL> SELECT * FROM dba_directories WHERE directory_name = 'DATA_PUMP_DIR';

SQL> CREATE DIRECTORY binhdir AS '/home/oracle/binhtv';

SQL> CREATE OR REPLACE DIRECTORY binhdir AS '/home/oracle/binhtv';

SQL> GRANT read, write ON DIRECTORY binhdir TO scott;

(default directory từ 10g R2 là DATA_PUMP_DIR)

--2. Expdp
(DATAPUMP_EXP_FULL_DATABASE role )

• Full Database
-- Dùng sys: ALL cả Metadata và DATA
expdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=testdb%U.dmp logfile=testdb.log COMPRESSION=ALL CONTENT=ALL FULL=y  PARALLEL=16;

-- Dùng sys: Chỉ Metadata
expdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=testdb%U.dmp logfile=testdb.log COMPRESSION=ALL CONTENT=METADATA_ONLY FULL=y  PARALLEL=16;

-- Dùng sys: Chỉ DATA
expdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=testdb%U.dmp logfile=testdb.log COMPRESSION=ALL CONTENT=DATA_ONLY FULL=y  PARALLEL=16;

--Giau pass binhtv, Chỉ metadata
expdp binhtv DIRECTORY=binhdir DUMPFILE=testdb%U.dmp logfile=testdb.log COMPRESSION=ALL CONTENT=METADATA_ONLY FULL=y  PARALLEL=16; 

-- Chỉ metadata
expdp binhtv/binhtv123 DIRECTORY=binhdir DUMPFILE=testdb%U.dmp logfile=testdb.log  COMPRESSION=ALL CONTENT=METADATA_ONLY FULL=y  PARALLEL=16;

-- Dump toan bo DB
expdp binhtv/binhtv DIRECTORY=binhdir DUMPFILE=testdb%U.dmp logfile=testdb.log  COMPRESSION=ALL CONTENT=ALL FULL=y  PARALLEL=16;

-- Parallel 2
expdp  "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=testdb%U.dmp logfile=testdb.log  COMPRESSION=ALL CONTENT=ALL FULL=y  PARALLEL=2;

• Schemas:
- Nhiều schema
expdp  "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=scott_binhtv%u.dmp SCHEMAS=scott, binhtv COMPRESSION=ALL PARALLEL=15

- 1 schema
expdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=scott%U.dmp LOGFILE=scott.log SCHEMAS=scott parallel=16 content=metadata_only COMPRESSION=all cluster=no
expdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=dbavietnam%U.dmp LOGFILE=dbavietnam.exp.log SCHEMAS=dbavietnam parallel=16 content=metadata_only COMPRESSION=all cluster=no
• Tablespaces:
# expdp  "' / as sysdba'"  DIRECTORY=binhdir DUMPFILE=users.dmp TABLESPACES=users COMPRESSION=ALL 

• Tables:
expdp  "' / as sysdba'"  TABLES=scott.dept DIRECTORY=binhdir DUMPFILE=dept.dmp nologfile=y COMPRESSION=ALL  

expdp  "' / as sysdba'"  TABLES=scott.dept, binhtv.tab1 DIRECTORY=binhdir DUMPFILE=dept%U.dmp logfile=exp_dept.log COMPRESSION=ALL PARALLEL=8;

• Flashback_scn, version
expdp cdc_nodba/xxx schemas=MC_OWNER include=TABLE:\"IN(\'MC_SUBSCRIBER\')\" directory=binhdir dumpfile=MC_SUBSCRIBER220714.dmp logfile=expMCSUBSCRIBER.log flashback_scn=<CURRENT_SCN>  version=10.2.0.3.0

• Partitions
expdp userid="'/ as sysdba'" parallel=8 ESTIMATE=STATISTICS  tables=BINH_OWNER.TAB1:DATA20130706,BINH_OWNER.TAB1:DATA20130704 directory=binhdir  dumpfile='DATA201307DATA201308_EXP_FIX2%U.dmp' NOLOGFILE=Y

• Sử dung parfile: Export những dữ liệu >=2020
$ vi exp_hlr_log_be2020.par

expdp userid="'/ as sysdba'" cluster=N parallel=8 directory=binhdir dumpfile=tab1_be2020%u.dmp logfile=exp_tab1_be2020.log tables=BINH_OWNER.TAB1 REUSE_DUMPFILES=YES compression=ALL query=BINH_OWNER.TAB1:"WHERE exec_datetime< to_date('01/01/2021','dd/mm/yyyy')" 

$ nohup expdp userid="'/ as sysdba'" parfile=exp_tab1_be2020.par &

CÁC THAM SỐ KHÁC:
COMPRESSION: Nén để giảm size, các tùy chọn: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

CONTENT : [ALL], DATA_ONLY and METADATA_ONLY.

ESTIMATE Tính toán dung lượng
Sử dụng [BLOCKS] và STATISTICS.
expdp binhtv/binhtv DIRECTORY=binhdir ESTIMATE=BLOCKS FULL=y  

ESTIMATE_ONLY ESTIMATE_ONLY={y | [n]}
Tính toán dung lượng mà không export

expdp binhtv/binhtv DIRECTORY=binhdir ESTIMATE_ONLY=y full=y  PARALLEL=16;

expdp binhtv/binhtv ESTIMATE_ONLY=y full=y;

expdp binhtv/binhtv DIRECTORY=binhdir   ESTIMATE_ONLY=y COMPRESSION=ALL

PARALLEL=16;
expdp binhtv/binhtv DIRECTORY=binhdir ESTIMATE=STATISTICS FULL=y  PARALLEL=16;
 schemas=SA,MIG  PARALLEL=16;

expdp binhtv/binhtv DIRECTORY=binhdir ESTIMATE_ONLY=y SCHEMAS=scott  PARALLEL=16;

NOLOGFILE=y

INCLUDE SCHEMAS=HR

DUMPFILE=expinclude.dmp

DIRECTORY=binhdir

LOGFILE=expinclude.log

INCLUDE=TABLE:"IN ('EMPLOYEES', 'DEPARTMENTS')"

INCLUDE=PROCEDURE

INCLUDE=INDEX:"LIKE 'EMP%'"
include=view,procedure, function, package

EXCLUDE Bỏ qua các object
Vi dụ, EXCLUDE=SCHEMA:"='HR'".
EXCLUDE=PROCEDURE,PACKAGE, FUNCTION
exclude=function, include package like '%FRED%';

FILESIZE Xác định cỡ file theo đơn vị gì
FULL
Export entire database [N].
FILESIZE=integer[B | K | M | G]
expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr_3m.dmp FILESIZE=3M

FLASHBACK_SCN

expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr_scn.dmp FLASHBACK_SCN=384632

PARFILE expdp hr/hr parfile=hr.par

JOB_NAME  

expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=exp_job.dmp JOB_NAME=exp_job

NOLOGFILE=y

PARALLEL expdp hr/hr DIRECTORY=dpump_dir1 LOGFILE=parallel_export.log 
JOB_NAME=par4_job DUMPFILE=par_exp%u.dmp PARALLEL=4
(Nếu chỉ metadata_only thì ko parallel, dữ liệu đầy đủ sẽ parallel)

SKIP_UNUSABLE_INDEXES

VERSION expdp userid=fpt_support/fpt123456 tables=comp_type1 directory=binhtvdir  dumpfile= comp_type1.dmp logfile=expdp_comp_type1.log VERSION=10.2

CLUSTER cluster=no

Vd full: 

expdp binhtv/binhtv DIRECTORY=binhdir DUMPFILE=testdb%U.dmp  ESTIMATE_ONLY 
COMPRESSION=ALL CONTENT=METADATA_ONLY schemas=SA,MIG  PARALLEL=16;
expdp binhtv/binhtv DIRECTORY=binhdir DUMPFILE=testdb%U.dmp  ESTIMATE_ONLY  schemas=SA,MIG  PARALLEL=16;

3. Imdp

(DATAPUMP_IMP_FULL_DATABASE role )

• Full 

$ impdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=expfull.dmp LOGFILE=impdp_full FULL=y CONTENT=METADATA_ONLY

$ impdp "' / as sysdba'"  DIRECTORY=binhdir DUMPFILE=testdb%U.dmp  LOGFILE=impdp_testdb.log  FULL=y CONTENT=ALL;

impdp "' / as sysdba'"  DIRECTORY=binhdir DUMPFILE=testdb%U.dmp  LOGFILE=impdp_testdb.log  FULL=y  TABLE_EXISTS_ACTION=APPEND CONTENT=ALL;

Schemas:

- Nhiều schema
impdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=test%U.dmp  LOGFILE=impdp_test.log SCHEMAS=test1,test2 parallel=8

impdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=test%U.dmp  LOGFILE=impdp_test.log SCHEMAS=test1,test2 TABLE_EXISTS_ACTION=APPEND parallel=8

- 1 schema
# impdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=scott%U.dmp LOGFILE=scot.impdp.log  SCHEMAS=scott parallel=16

# impdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=scott%U.dmp LOGFILE=scot.impdp.log parallel=16 REMAP_SCHEMA=scott:scott_new;

# impdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=expdp.scott.dmp SCHEMAS=scott;

# impdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=scott%U.dmp SCHEMAS=scott_new REMAP_SCHEMA=scott:scott_new PARALLEL=8
impdp "' / as sysdba'" DIRECTORY=binhdirDUMPFILE=dbavietnam%U.dmp LOGFILE=dbavietnam.imp.log REMAP_SCHEMA=dbavietnam:voucher_test  parallel=16;

impdp "' / as sysdba'" DIRECTORY=binhdirDUMPFILE=dbavietnam%U.dmp LOGFILE=dbavietnam.imp.log REMAP_SCHEMA=dbavietnam:voucher_test  TABLE_EXISTS_ACTION=APPEND parallel=16;

• Tables:
impdp "' / as sysdba'" TABLES=scott.dept nologfile=y DIRECTORY=binhdir dumpfile=dept.dmp
impdp hr DIRECTORY=binhdir DUMPFILE=expfull.dmp TABLES=employees,jobs
impdp hr DIRECTORY=binhdir DUMPFILE=expdat.dmp 
TABLES=sh.sales:sales_Q1_2008,sh.sales:sales_Q2_2008

-- Đổi tên từ tab1 thành tab1_new, tab2 thành tab2_new
impdp "' / as sysdba'" DIRECTORY=binhdir dumpfile=reg%U.dmp logfile=imp_reg.log TABLES=binhtv.tab1,binhtv.tab2  REMAP_TABLE=binhtv.tab1:binhtv.tab1_new,binhtv.tab2 :binhtv.tab2_NEW   TABLE_EXISTS_ACTION=APPEND   parallel=8;

impdp "' / as sysdba'" DIRECTORY=binhdir dumpfile=reg%U.dmp logfile=imp_reg.log TABLES=binhtv.tab1,binhtv.tab2  REMAP_TABLE=binhtv.tab1:binhtv.tab1_new,binhtv.tab2 :binhtv.tab2_NEW    parallel=8;

• Partitions
impdp userid="'/ as sysdba'" parallel=8 REMAP_SCHEMA=BINH_OWNER:BINH_OWNER REMAP_TABLESPACE=DATA201406:IMPORT_TBS,DATA201407:IMPORT_TBS,DATA201311:IMPORT_TBS,DATA201403:IMPORT_TBS,DATA201310:IMPORT_TBS,DATA201405:IMPORT_TBS,DATA201308:IMPORT_TBS,DATA201309:IMPORT_TBS,DATA201312:IMPORT_TBS,DATA201401:IMPORT_TBS,DATA201307:IMPORT_TBS,DATA201402:IMPORT_TBS,DATA201404:IMPORT_TBS directory=binhdir dumpfile='DATA201307DATA201308_EXP%U.dmp' TABLES=BINH_OWNER.TAB1:DATA20130706,BINH_OWNER.TAB2:DATA20130707 LOGFILE='DATA201307DATA201308_IMP_FIX2.log' JOB_NAME='imp_fix2_DATA201307DATA201308' TABLE_EXISTS_ACTION=APPEND DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS

• Sử dung parfile:

vi imp_tab1.par
cluster=N 
parallel=8
directory=EXP_TAPE 
dumpfile=tab1_be2020%u.dmp
logfile=imdp_tab1_be2020.log
tables=BINH_OWNER.TAB1
content=DATA_ONLY

nohup impdp userid="'/ as sysdba'" parfile=imp_tab1.par &
• Rows:

• Tablespaces:
# impdp "' / as sysdba'" DIRECTORY=binhdir DUMPFILE=users.dmp TABLESPACES=users

CÁC OPTION:
CONTENT impdp hr TABLES=employees CONTENT=DATA_ONLY 

DUMPFILE=dpump_dir1:table.dmp

NOLOGFILE=y
impdp hr TABLES=employees CONTENT=metadata DUMPFILE=dpump_dir1:table.dmp

NOLOGFILE=y

DATA_OPTIONS {DISABLE_APPEND_HINT | SKIP_CONSTRAINT_ERRORS}
impdp hr TABLES=employees CONTENT=DATA_ONLY 

DUMPFILE=dpump_dir1:table.dmp DATA_OPTIONS=skip_constraint_errors
impdp hr DIRECTORY=dpump_dir1 DUMPFILE=dpump_dir2:exp1.dmp, exp2%U.dmp

ENCRYPTION
                impdp hr TABLES=employee_s_encrypt DIRECTORY=dpump_dir
DUMPFILE=dpcd2be1.dmp ENCRYPTION_PASSWORD=123456

ESTIMATE ={[BLOCKS] | STATISTICS}

LOGFILE impdp hr SCHEMAS=HR DIRECTORY=dpump_dir2 LOGFILE=imp.log

DUMPFILE=dpump_dir1:expfull.dmp

NOLOGFILE ={y | [n]}

PARALLEL Default: 1
impdp hr DIRECTORY=dpump_dir1 LOGFILE=parallel_import.log 

JOB_NAME=imp_par3 DUMPFILE=par_exp%U.dmp PARALLEL=3
Các file sinh ra là par_exp01.dmp, par_exp02.dmp, and par_exp03.dmp.

PARFILE ví dụ file hr_imp.par như sau:
TABLES= countries, locations, regions
DUMPFILE=dpump_dir2:exp1.dmp,exp2%U.dmp
DIRECTORY=dpump_dir1
PARALLEL=3 

Câu lệnh impdp sử dụng parfile: impdp hr PARFILE=hr_imp.par

QUERY với query_imp.par:
QUERY=departments:"WHERE department_id < 120"

PARFILE=query_imp.par NOLOGFILE=Y

REMAP_DATA impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp

TABLES=hr.employees REMAP_DATA=hr.employees.first_name:hr.remap.plusx

REMAP_SCHEMA expdp system SCHEMAS=hr DIRECTORY=dpump_dir1 

DUMPFILE=hr.dmp
impdp system DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott

REMAP_TABLE (REMAP_TABLE=user1.tab1:tab1_new, Import tab1 vào tab1_new

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expschema.dmp

TABLES=hr.employees REMAP_TABLE=hr.employees:emps 
REMAP_TABLESPACE impdp hr REMAP_TABLESPACE=tbs_1:tbs_6 

DIRECTORY=dpump_dir1

DUMPFILE=employees.dmp 

SKIP_UNUSABLE_INDEXES impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp 
LOGFILE=skip.log

SKIP_UNUSABLE_INDEXES=y

TABLE_EXISTS_ACTION Mặc định: SKIP (Chú ý nếu CONTENT=DATA_ONLY được xác định, mặc định là APPEND, không phải SKIP.)

TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE}
impdp hr TABLES=employees DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp
TABLE_EXISTS_ACTION=REPLACE




expdp binhtv/binhtv ESTIMATE_ONLY=y full=y;

expdp binhtv/binhtv DIRECTORY=binhdir   ESTIMATE_ONLY=y COMPRESSION=ALL
4. Sử dụng par file:

$ vi views.par
VIEWS=BINH_OWNER.VIEW1,
CUS_OWNER.VIEW2,BINH_OWNER.VIEW3;

$ vi view_dbaviet.par
INCLUDE=VIEW:"IN (select owner||'.'||view_name from dba_views where owner='BỊNH_OWNER')"

$ vi exp_view_dbaviet.sh
expdp userid="'/AS SYSDBA'" PARFILE=views.par DIRECTORY=binhdir DUMPFILE=view_dbaviet.dmp LOGFILE=view_dbaviet.log JOB_NAME=view_dbaviet

$ nohup ./exp_view_dbaviet.sh &

----
$ vi exp_test_log_be2020.par
cluster=N
parallel=8
directory=binhdir
dumpfile=exp_test_log_be2020%u.dmp
logfile=exp_test_log_be2020.log
tables=BINH_OWNER.TEST_LOG
REUSE_DUMPFILES=YES
compression=ALL
query=BINH_OWNER.TEST_LOG:"WHERE exec_date
me < to_date('01/01/2021','dd/mm/yyyy')"

$ nohup expdp userid="'/ as sysdba'" parfile=exp_test_log_be2020.par &

5. Stop Job và chạy lại sau
Khi export hoặc import gây cao tải ta có thể stop job và chạy lại sau (ví dụ 10 phần công việc mình thực hiện được 7 phần thì sau đó chỉ cần thực hiện 3 phần công việc còn lại):

-- Lấy tên job đang chạy
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE
---------- -------------------- ---------- ---------- ------------
SYSTEM     SYS_EXPORT_FULL_01   EXPORT     FULL       EXECUTING

-- Giả sử đang import, cần kill job

expdp SYSTEM/System123@database attach=SYS_EXPORT_FULL_01   

export>KILL_JOB

- Nếu cần pause để chạy lại sau thì dùng stop_job:

Export>stop_job

hoặc chạy bằng PL/SQL:

DECLARE
           h1 NUMBER;
      BEGIN
           h1:=DBMS_DATAPUMP.ATTACH(‘SYS_EXPORT_FULL_01‘,’SYSTEM‘);
           DBMS_DATAPUMP.STOP_JOB (h1, 1, 0);
       END;

-- Khôi phục lại (resume) job đã stop:
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;

OWNER_NAME JOB_NAME             OPERATION  JOB_MODE   STATE      
---------- -------------------- ---------- ---------- ------------
SYSTEM     SYS_EXPORT_FULL_01             EXPORT     FULL       NOT RUNNING
$ expdp system attach=SYS_EXPORT_FULL_01 

Export> START_JOB[=SKIP_CURRENT]
Export> CONTINUE_CLIENT
Job EXP_FULL has been reopened at Thursday, 09 June, 2011 10:26
Restarting "SYSTEM"."EXP_FULL":  system/******** full=y JOB_NAME=SYS_EXPORT_FULL_01 

Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE

KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master