DATAPUMP ORACLE 10g

26 Aug

I. Tujuan
Sebagai acuan teori untuk prosedur datapump ORACLE 10g LINUX.
II. Cara Pengerjaan
1. Tujuan menggunakan Export/Import atau DataPump
a. Untuk memindahkan Data Database
Development > QA > Production
b. Melakukan Logical Backup
c. Migrasi Database
Operating System[MS] > Operating System [Linux]
Oracle Version [9i] > 10g
One Character Set > Other Character Set

2. Untuk lebih mengenal perintah expdp
[oracle@ubs ~]$ expdp help=y
Format : expdp KEYWORD=value or KEYWORD=(value1,value2,…,valueN)
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir
SCHEMAS=scott or TABLES=(T1:P1,T1:P2), if T1 is partitioned
Table

3. Persiapan menggunakan perintah expdp
[oracle@ubs ~]$ mkdir /home/oracle/backup
[oracle@ubs ~]$ sqlplus / as sysdba
SQL> create or replace directory dumpfile as ‘/home/oracle/backup’;
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY dumpfile TO master;
Grant succeeded.
SQL> exit

4. Menjalankan perintah expdp
[oracle@ubs ~]$ expdp master/password schemas=master directory=dumpfile dumpfile=master01.dmp logfile=master01.log
Export: Release 10.2.0.1.0 – Production on Saturday, 06 October, 2007 8:55:28
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “MASTER”.”SYS_EXPORT_SCHEMA_01″: master/******** schemas=master directory=dumpfile dumpfile=master01.dmp logfile=master01.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 2.005 GB
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
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/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “MASTER”.”UBJDIVB” 178.8 MB 1659508 rows
. . exported “MASTER”.”GVBJMKA” 171.3 MB 1205875 rows
. . exported “MASTER”.”AC9400″ 151.1 MB 1510169 rows
. . exported “MASTER”.”PG001B” 123.6 MB 1088242 rows
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . exported “MASTER”.”AC9400_AKSES_LAPORAN” 6.351 KB 5 rows
. . exported “MASTER”.”AC9400_BAGIAN” 5.273 KB 3 rows
. . exported “MASTER”.”AR_DT_BARU” 5.953 KB 6 rows
. . exported “MASTER”.”PIB_CAPACITY” 6.507 KB 1 rows
. . exported “MASTER”.”PIB_CAPA_DETIL” 7.171 KB 2 rows
. . exported “MASTER”.”TOAD_PLAN_TABLE” 20.05 KB 81 rows
. . exported “MASTER”.”UBJ_DIV_KE_FC” 29.78 KB 141 rows
. . exported “MASTER”.”XBH111C” 6.265 KB 1 rows
. . exported “MASTER”.”XCO_BERAT_BATU” 27.34 KB 550 rows
. . exported “MASTER”.”CB22ST” 0 KB 0 rows
. . exported “MASTER”.”DIV_BOM” 0 KB 0 rows
. . exported “MASTER”.”DIV_BOMDIV” 0 KB 0 rows
. . exported “MASTER”.”DIV_BOMPJ” 0 KB 0 rows
. . exported “MASTER”.”DIV_BOMPM” 0 KB 0 rows
. . exported “MASTER”.”DIV_BOMPR” 0 KB 0 rows
. . exported “MASTER”.”KOMPUTER” 0 KB 0 rows
. . exported “MASTER”.”LOG_AKTIF_MODEL” 0 KB 0 rows
. . exported “MASTER”.”MK_HASIL_RETURA” 0 KB 0 rows
. . exported “MASTER”.”MK_HASIL_RETURB” 0 KB 0 rows
. . exported “MASTER”.”T1″ 0 KB 0 rows
. . exported “MASTER”.”TOAD_PLAN_SQL” 0 KB 0 rows
. . exported “MASTER”.”U_RETUR_A” 0 KB 0 rows
. . exported “MASTER”.”U_RETUR_B” 0 KB 0 rows
. . exported “MASTER”.”XBH001G” 0 KB 0 rows
Master table “MASTER”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for MASTER.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/backup/master01.dmp
Job “MASTER”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 08:58:35

5. Jika menggunakan perintah exp
[oracle@ubs ~]$ exp userid=master/password file=/home/oracle/backup/master02.dmp log=/home/oracle/backup/master02.txt
Export: Release 10.2.0.1.0 – Production on Sat Oct 6 11:24:45 2007
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user MASTER
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user MASTER
About to export MASTER’s objects …
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export MASTER’s tables via Conventional Path …
. . exporting table AC9400 1510169 rows exported
. . exporting table AC9400USER 3351 rows exported
. . exporting table AC9400_AKSES_LAPORAN 5 rows exported
. . exporting table AC9400_BAGIAN 3 rows exported
. . exporting table ALLOY 632 rows exported
. . exporting table AR_DT_BARU 6 rows exported
. . exporting table ATRIBUT 924 rows exported
. . exporting table A_DIV 28 rows exported
. . exporting table BBOMKOPR 81349 rows exported
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . exporting table XBOMPR 152656 rows exported
. . exporting table XCO_BERAT_BATU 550 rows exported
. . exporting table XTEMP_PRODUK 12055 rows exported
. . exporting table XUBJDIVA 287069 rows exported
. . exporting table XUBJDIVB 912878 rows exported
. . exporting table XUBJDIVC 490328 rows exported
. . exporting table XUBJDIVD 60561 rows exported
. . exporting table X_UBJDIVA 33575 rows exported
. . exporting table X_UBJDIVB 100788 rows exported
. . exporting table X_UBJDIVC 333 rows exported
. . exporting table ZCO_BERAT_BATU 959 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

6. Kecepatan proses, lebih cepat expdp dibandingkan exp
Menggunakan expdp
06 October, 2007 08:55:28
06 October, 2007 08:58:35
hasil = 00:03:07
Menggunakan exp
Sat Oct 6 11:39:14 WIT 2007
Sat Oct 6 11:45:59 WIT 2007
hasil = 00:06:45

7. Besarnya file, lebih kecil expdp dibandingkan exp
[oracle@ubs ~]$ ls -la /home/oracle/backup/
total 3594300
drwxr-xr-x 2 oracle oinstall 4096 Oct 6 11:39 .
drwx—— 20 oracle oinstall 4096 Oct 6 11:39 ..
-rw-r—– 1 oracle oinstall 1675464704 Oct 6 08:58 master01.dmp
-rw-r–r– 1 oracle oinstall 15859 Oct 6 08:58 master01.log
-rw-r–r– 1 oracle oinstall 2001436672 Oct 6 11:45 master02.dmp
-rw-r–r– 1 oracle oinstall 31551 Oct 6 11:45 master02.txt

8. Status job expdp, pada saat expdp dijalankan, kemudian ctrl+c.
[oracle@ubs backup]$ expdp master/password schemas=master directory=dumpfile dumpfile=master03.dmp logfile=master03.log
Export: Release 10.2.0.1.0 – Production on Saturday, 06 October, 2007 12:58:31
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “MASTER”.”SYS_EXPORT_SCHEMA_01″: master/******** schemas=master directory=dumpfile dumpfile=master03.dmp logfile=master03.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Export> status
Job: SYS_EXPORT_SCHEMA_01
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /home/oracle/backup/master03.dmp
bytes written: 4,096
Worker 1 Status:
State: EXECUTING

9. Status job expdp, dengan mengunakan perintah sql
SQL> select * from dba_datapump_jobs;
OWNER_NAME JOB_NAME
—————————— ——————————
OPERATION JOB_MODE
—————————— ——————————
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
—————————— ———- —————– —————–
FARESO SYS_EXPORT_SCHEMA_01
EXPORT SCHEMA
EXECUTING 1 1 3

SQL> /
OWNER_NAME JOB_NAME
—————————— ——————————
OPERATION JOB_MODE
—————————— ——————————
STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
—————————— ———- —————– —————–
PRODGV SYS_EXPORT_SCHEMA_01
EXPORT SCHEMA
EXECUTING 1 1 3

10. Expdp dapat dijalankan secara pararel, kemudian ctrl+c.
[oracle@ubs backup]$ expdp master/password schemas=master directory=dumpfile parallel=4 dumpfile=master_%U.dmp logfile=masterP.log
Export: Release 10.2.0.1.0 – Production on Saturday, 06 October, 2007 14:03:12
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “MASTER”.”SYS_EXPORT_SCHEMA_01″: master/******** schemas=master directory=dumpfile parallel=4 dumpfile=master_%U.dmp logfile=masterP.log
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Export> status
Job: SYS_EXPORT_SCHEMA_01
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Dump File: /home/oracle/backup/master_01.dmp
bytes written: 4,096
Dump File: /home/oracle/backup/master_%u.dmp
Worker 1 Status:
State: EXECUTING

Export> status
Job: SYS_EXPORT_SCHEMA_01
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 4
Job Error Count: 0
Dump File: /home/oracle/backup/master_%u.dmp
Dump File: /home/oracle/backup/master_01.dmp
bytes written: 4,096
Dump File: /home/oracle/backup/master_02.dmp
bytes written: 4,096
Dump File: /home/oracle/backup/master_03.dmp
bytes written: 4,096
Dump File: /home/oracle/backup/master_04.dmp
bytes written: 4,096

Worker 1 Status:
State: EXECUTING
Object Schema: MASTER
Object Type: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Completed Objects: 1
Worker Parallelism: 1

Worker 2 Status:
State: EXECUTING
Object Schema: MASTER
Object Name: UBJDIVB
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 175
Worker Parallelism: 1

Worker 3 Status:
State: EXECUTING
Object Schema: MASTER
Object Name: GVBJMKA
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 175
Worker Parallelism: 1

Worker 4 Status:
State: EXECUTING
Object Schema: MASTER
Object Name: AC9400
Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA
Completed Objects: 1
Total Objects: 175
Worker Parallelism: 1

[oracle@ubs backup]$ ls -la master_**.dmp
-rw-r—– 1 oracle oinstall 468606976 Oct 6 14:05 master_01.dmp
-rw-r—– 1 oracle oinstall 641900544 Oct 6 14:05 master_02.dmp
-rw-r—– 1 oracle oinstall 562462720 Oct 6 14:05 master_03.dmp
-rw-r—– 1 oracle oinstall 2506752 Oct 6 14:05 master_04.dmp
11. Expdp untuk rmap memindahkan tablespace
a. Misalkan diinginkan untuk memindah skema PRODGV dari tablespace USERS ke GV

b. create tablespace baru
SQL> CREATE TABLESPACE gv DATAFILE
‘/u02/gv01.dbf’ SIZE 500M AUTOEXTEND OFF,
‘/u02/gv02.dbf’ SIZE 500M AUTOEXTEND OFF,
‘/u02/gv03.dbf’ SIZE 500M AUTOEXTEND OFF,
‘/u02/gv04.dbf’ SIZE 500M AUTOEXTEND OFF,
‘/u02/gv05.dbf’ SIZE 500M AUTOEXTEND OFF,
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
‘/u02/gv23.dbf’ SIZE 500M AUTOEXTEND OFF,
‘/u02/gv24.dbf’ SIZE 500M AUTOEXTEND OFF,
‘/u02/gv25.dbf’ SIZE 500M AUTOEXTEND OFF
NOLOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

c. memberikan hak ke tablespace baru
SQL> alter USER PRODGV DEFAULT TABLESPACE gv;
SQL> GRANT UNLIMITED TABLESPACE TO GV;

d. memberikan hak akses read & write untuk directory dumpfile
SQL> GRANT READ, WRITE ON DIRECTORY dumpfile TO prodgv;

e. melakukan expdmp terlebih dulu sebelum drop tabel skema PRODGV
[oracle@ubs backup]$ expdp prodgv/password schemas=prodgv directory=dumpfile parallel=4 dumpfile=prodgv_%U.dmp logfile=prodgvP.log

SQL> DROP TABLE PRODGV.AGUSXGVLPP31 CASCADE CONSTRAINTS;
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

f. melakukan impdmp
[oracle@ubs backup]$ impdp prodgv/password remap_tablespace=users:gv directory=dumpfile parallel=4 dumpfile=prodgv_%U.dmp logfile=prodgvP2.log
Import: Release 10.2.0.1.0 – Production on Monday, 08 October, 2007 8:41:36
Copyright (c) 2003, 2005, Oracle. All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “PRODGV”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “PRODGV”.”SYS_IMPORT_FULL_01″: prodgv/******** remap_tablespace=users:gv directory=dumpfile parallel=4 dumpfile=prodgv_%U.dmp logfile=prodgvP2.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “PRODGV”.”GV005B” 248.0 MB 2419730 rows
. . imported “PRODGV”.”GV015A” 240.4 MB 1630369 rows
. . imported “PRODGV”.”GV005BXX” 174.6 MB 1708818 rows
. . imported “PRODGV”.”GV010B” 168.9 MB 945345 rows
. . imported “PRODGV”.”BACKUPD_GVSTBHN_200607″ 136.4 MB 1503043 rows
. . imported “PRODGV”.”GVBOMRC” 146.9 MB 817502 rows
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

5 Responses to “DATAPUMP ORACLE 10g”

  1. Linardi Kurniawan December 22, 2008 at 4:03 pm #

    Kak Setijo, saya sekarang sedang belajar Oracle dan sekarang sedang mendapat pekerjaan meneliti Data Pump, dan problem yang sampai sekarang saya masih belum bisa pecahkan adalah, jika kita menjalankan Data Pump untuk memindahkan data dari server ke client dan sebaliknya, apakah character set dari hasil keluaran Data Pump Export itu mengikuti character set dari server atau apakah dia akan mengikuti character set (NLS_LANG) dari client ???

    • setijoagus December 23, 2008 at 1:27 am #

      u/ kasus Data Pump dipindahkan dari server ke client dan sebaliknya, jujurnya saya belum pernah menjalankan bahkan setahu saya untuk mengurai file hasil dmp perlu tools / software tersendiri, karena hasil file dmp adalah perintah DML.

      Tetapi kalau pertanyaannya apakah Data Pump dapat dijalankan di sisi client ?, jawabanya adalah bisa, selain tentunya Data Pump bisa dijalankan di sisi server

      Untuk character set, setahu saya nls_lang parameter digunakan untuk menampilkan non english charackter Oracle database dan nls_lang dapat di set di init.ora dan dapat juga diset di session level, maka dapat disimpulkan jika di sisi client di set berbeda nls_lang dengan server maka mengikuti client hasil data pump-nya. Nanti kalau ada waktu perlu dicoba atau Anda mau mencoba duluan ????

  2. Linardi December 25, 2008 at 5:21 am #

    Merry Christmas (jika merayakan), saya akhirnya mendapatkan jawabannya, dengan menjalankan prosedur DBMS_DATAPUMP.GET_DATAPUMP_INFO (oh ya, btw, saya sekarang sedang memakai Oracle 11g).

    Dengan prosedur ini, dump file header yang memuat informasi mengenai character set pun dapat dilihat.

    Dan sebagai hasilnya, character set dump file tersebut, selalu berdasarkan character set dari server, jika berminat silakan dicoba…

  3. setijoagus December 26, 2008 at 12:44 am #

    Merry Christmas, too. terimakasih atas infonya :
    1. DBMS_DATAPUMP.GET_DATAPUMP_INFO
    2. selalu berdasarkan character set dari server

  4. Krystle February 12, 2013 at 11:29 am #

    Nice blog here! Also your web site loads up very fast!
    What host are you using? Can I get your affiliate link to your
    host? I wish my website loaded up as fast as yours lol

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: