- tweet
-
expdp – impdp
Muslera ve Drogba adlı 2 sunucumuz olsun. Muslera sunucusundaki muslera adlı veritabanında ELMANDER adlı şemanın exportunu alıp Drogba sunucusundaki drogba adlı veritabanına import etmek için aşağıdaki adımları uygularız.
1- Export alacağımız sunucu üzerinde export saklayacağımız dizin tanımlanmamışsa aşağıdaki şekilde oluştururuz.
root@muslera # su – oracle
Oracle Corporation SunOS 5.10 Generic Patch January 2005
-bash-3.00$ mkdir -p /orabkp/export_muslera
-bash-3.00# chmod 775 /orabkp/export_muslera
-bash-3.00# chown oracle:oinstall /orabkp/export_muslera
-bash-3.00$ sqlplus / as sysdba
SQL> CREATE OR REPLACE DIRECTORY
2 ORACLE_BACKUP AS
3 ’/orabkp/export_muslera/’;
3 ’/orabkp/export_muslera/’;
Directory created.
2- Oluşturduğumuz ORACLE_BACKUP dizinine ELMANDER kullanıcısına read, write hakkı veriyoruz
Ayrıca export ve import için gerekli hakları veriyoruz.
SQL> GRANT READ, WRITE ON DIRECTORY ORACLE_BACKUP TO ELMANDER WITH GRANT OPTION;
Grant succeeded.
3- Muslera sunucusundan ELMANDER şemasının exportunu alıyoruz.
-bash-3.00$ expdp ELMANDER /ELMANDER DIRECTORY=ORACLE_BACKUP DUMPFILE= ELMANDER.dmp SCHEMAS= ELMANDER EXCLUDE=STATISTICS
4- Aldığımız exportu zipliyoruz.
root@muslera #/usr/bin/gzip -1 /orabkp/export_muslera/ELMANDER.dmp
5- Drogba sunucusunda import edeceğimiz export için DATA_PUMP_DIR adlı dizini oluşturuyoruz
-bash-3.00# mkdir -p /u01/app/admin/DROGBA/dpdump
-bash-3.00# cd /u01/app/admin/DROGBA/
-bash-3.00# chmod 775 dpdump/
-bash-3.00# chown oracle:oinstall dpdump/
SQL> CREATE OR REPLACE DIRECTORY
2 DATA_PUMP_DIR AS
3 ’/u01/app/admin/DROGBA/dpdump/’;
3 ’/u01/app/admin/DROGBA/dpdump/’;
Directory created.
6- Oluşturduğumuz DATA_PUMP_DIR dizinine ELMANDER kullanıcısına read, write hakkı veriyoruz.Ayrıca export ve import için gerekli hakları veriyoruz.
SQL> GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO ELMANDER;
Grant succeeded.
SQL> GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO EXP_FULL_DATABASE;
Grant succeeded.
SQL> GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO IMP_FULL_DATABASE;
Grant succeeded.
SQL> exit
7- Drogba sunucusuna alınan export kopyalıyoruz.
root@drogba # scp 192.168.2.11 /orabkp/export_muslera/ ELMANDER.dmp.gz /u01/app/admin/DROGBA/dpdump
8- Exportu unzipliyoruz.
gunzip /u01/app/admin/DROGBA/dpdump/ELMANDER.dmp.gz
9- Aldığımız exportun sahibini Oracle yapıyoruz ve read, write, execute hakkı veriyoruz
root@drogba # cd /u01/app/admin/DROGBA/dpdump/
root@drogba # chown oracle:oinstall ELMANDER.dmp
root@drogba # chmod 775 ELMANDER.dmp
10- Drogba veritabanında ELMANDER şemasının kullanacağı tablespaceleri oluşturuyoruz.
Export aldığımız şemada bulunan bütün tablespaceleri oluşturmamız gerekiyor.
SQL> CREATE SMALLFILE TABLESPACE ”TBS_ ELMANDER ”
2 DATAFILE+DATA/DROGBA/DATAFILE/dbf_ elmander ’
3 SIZE 100M AUTOEXTEND ON NEXT 100M
4 MAXSIZE UNLIMITED
5 LOGGING
6 EXTENT
7 MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
2 DATAFILE+DATA/DROGBA/DATAFILE/dbf_ elmander ’
3 SIZE 100M AUTOEXTEND ON NEXT 100M
4 MAXSIZE UNLIMITED
5 LOGGING
6 EXTENT
7 MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> exit
11- Drogba veritabanında ELMANDER kullanıcısı oluşturup , sahip olması gereken hakları veriyoruz.
CREATE USER ELMANDER
IDENTIFIED BY ”ELMANDER”
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE DEFAULT
ACCOUNT UNLOCK;
GRANT CONNECT TO ELMANDER;
GRANT DATAPUMP_EXP_FULL_DATABASE TO ELMANDER WITH ADMIN OPTION;
GRANT DATAPUMP_IMP_FULL_DATABASE TO ELMANDER WITH ADMIN OPTION;
GRANT EXP_FULL_DATABASE TO ELMANDER WITH ADMIN OPTION;
GRANT RESOURCE TO ELMANDER;
ALTER USER ELMANDER DEFAULT ROLE CONNECT, RESOURCE;
GRANT ALTER ANY TABLE TO ELMANDER;
GRANT ALTER TABLESPACE TO ELMANDER;
GRANT BACKUP ANY TABLE TO ELMANDER;
GRANT CREATE ANY TABLE TO ELMANDER;
GRANT CREATE ANY VIEW TO ELMANDER;
GRANT CREATE DATABASE LINK TO ELMANDER;
GRANT CREATE SESSION TO ELMANDER;
GRANT DEBUG CONNECT SESSION TO ELMANDER;
GRANT DROP ANY TABLE TO ELMANDER;
GRANT EXPORT FULL DATABASE TO ELMANDER;
GRANT INSERT ANY TABLE TO ELMANDER;
GRANT SELECT ANY TABLE TO ELMANDER;
GRANT UNLIMITED TABLESPACE TO ELMANDER;
GRANT UPDATE ANY TABLE TO ELMANDER;
ALTER USER ELMANDER QUOTA UNLIMITED ON USERS;
12- Aldığımız exportu import ediyoruz.
root@drogba# su – oracle
Oracle Corporation SunOS 5.10 Generic Patch January 2005
-bash-3.00$ impdp ELMANDER/ELMANDER DIRECTORY=DATA_PUMP_DIR DUMPFILE= ELMANDER.dmp
Ayrıca bazı faydalı edatapump parametreleri aşağıda listelenmiştir.
|
Exporting
|
|
|
database
|
expdp vallep/password directory=datapump full=y dumpfile=data.dmp filesize=2G parallel=2 logfile=full.log
Note: increase the parallel option based on the number of CPU’s you have
|
|
schema
|
expdp sys/password schemas=testuser dumpfile=data.dmp logfile=schema.log
|
|
table
|
expdp vallep/password tables=accounts,employees dumpfile=data.dmp content=metadata_only
|
|
tablespace
|
expdp vallep/password tablespaces=users dumpfile=data.dmp logfile=tablespace.log
|
|
Importing
|
|
|
database
|
impdp system/password full=y dumpfile=data.dmp nologfile=y
|
|
schema change
|
impdp system/password schemas=’HR’ remap_schema=’HR:HR_TEST’ content=data_only
impdp system/passwd remap_schema=’TEST:TEST3’ tables=test log=… dumpfile=… directory=… |
|
Other Options
|
|
|
directory
|
specifies a oracle directory object
|
|
filesize
|
split the dump file into specific sizes (could be used if filesystem has 2GB limit)
|
|
parfile
|
specify the parameter file
|
|
content
|
contents option can be ALL, METADATA_ONLY or DATA_ONLY
|
|
compression
|
compression is used by default but you can stop it
|
|
exclude/include
|
metadata filtering
|
|
query
|
selectively export table data using a SQL statement
|
|
estimate
|
Calculate job estimates where the vaild keywords are blocks and statistics
|
|
estimate_only
|
Calculate job estimates without performing the export
|
|
network link
|
you can perform a export across a network
|
|
encryption
|
you can encrypt data within the data pump file
|
|
parallel
|
increase worker processes to increase throughput, base it on number of CPU’s
|
|
remap_schema
|
move objects from one schema to another
|
|
remap_datafile
|
change the name of the datafile when moving across different systems
|
|
remap_tablespace
|
move from one tablespace to another
|
|
Useful Views
|
|
|
DBA_DATAPUMP_JOBS
|
summary information of all currently running data pump jobs
|
|
DBA_DATAPUMP_SESSIONS
|
displays the user currently running data pump jobs
|
|
V$SESSION_LONGOPS
|
display information like totalwork, sofar, units and opname
|
|
Privileges
|
|
|
IMP_FULL_DATABASE
|
required if using advanced features
|
|
EXP_FULL_DATABASE
|
required if using advanced features
|
kaynak :
http://www.oracle.com/technetwork/issue-archive/2009/09-jul/datapump11g2009-quickstart-128718.pdf
http://www.datadisk.co.uk/html_docs/oracle/data_pump.htm
