The Oracle Data Pump utilities — EXPDP (Export Data Pump) and IMPDP (Import Data Pump) — are powerful tools used for logical backup and restore in Oracle Database.
They are faster and more flexible than the old exp/imp utilities.
Logical backups using Datapump:
Table level
Schema level
Entire database
Tablespace level
Transportable tablespace level
Create Data Pump Directory at OS level:
[oracle@ora101 ~]$ mkdir -p /u01/app/datapump/
Create directory inside the database:
SQL> create directory DATA_PUMP as '/u01/app/datapump/';
Directory created.
Grant permissions on directory to system user:
SQL> GRANT READ,WRITE on directory DATA_PUMP to system;
Grant succeeded.
View directory information:
SQL>
SQL> select * from dba_directories where DIRECTORY_NAME='DATA_PUMP';
OWNER
--------------------------------------------------------------------------------
DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ORIGIN_CON_ID
-------------
SYS
DATA_PUMP
/u01/app/datapump/
0
Table Level Export:
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log
[oracle@ora101 datapump]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES,HR.JOBS dumpfile=HR_JOBS_EMPLOYEES.dump logfile=HR_JOBS_EMPLOYEES.log
[oracle@ora101 datapump]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES,INFODBA.DEPT1 dumpfile=HR_EMPLOYEES_INFODBA.DEPT1.dump logfile=HR_EMPLOYEESINFODBA.DEPT1.log
[oracle@ora101 datapump]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES,INFODBA.DEPT1 dumpfile=HR_EMPLOYEES_INFODBA.DEPT1.dump logfile=HR_EMPLOYEESINFODBA.DEPT1.log PARALLEL=4
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEESDATA_ONLY.dump logfile=HR_EMPLOYEESDATA_ONLY.log content=DATA_ONLY
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEESMETADATA_ONL.dump logfile=HR_EMPLOYEESMETADATA_ONL.log content=METADATA_ONLY
Table Level Import:
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log table_exists_action=skip
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES REMAP_TABLE=HR.EMPLOYEES:HR dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES REMAP_SCHEMA=HR:INFODBA dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP tables=HR.EMPLOYEES REMAP_SCHEMA=HR:INFODBA REMAP_TABLE=EMPLOYEES:EMPLOYEES1 dumpfile=HR_EMPLOYEES.dump logfile=HR_EMPLOYEES.log
Schema Level Export:
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP schemas=HR dumpfile=HR.dump logfile=HR.log PARALLEL=4
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP schemas=HR,INFODBA dumpfile=HR_INFODBA.dump logfile=HR_INFODBA.log PARALLEL=4
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP schemas=HR dumpfile=HR_DATA_ONLY.dump logfile=HR_DATA_ONLY.log content=DATA_ONLY PARALLEL=4
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP schemas=HR dumpfile=HR_METADATA_ONLY.dump logfile=HR_METADATA_ONLY.log content=METADATA_ONLY PARALLEL=4
Schema Level Export:
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP dumpfile=HR.dump logfile=HR.log schemas=HR
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP dumpfile=HR.dump logfile=HR.log remap_schema=HR:HR
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP dumpfile=HR.dump logfile=HR.log remap_schema=HR:APP
Database Level Export:
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP FULL=Y dumpfile=PROD_FULL.dump logfile=PROD_FULL.log
Database Level Import:
Import full database
On source
SQL> select name from v$tablespace;
On target
SQL> select name from v$tablespace;
Create missing tablespaces on target
Make sure target tablespace has enough free space
Drop all non-oracle schemas (done during refresh)
DROP USER HR CASCADE;
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP FULL=Y dumpfile=PROD_FULL.dump logfile=PROD_FULL.log
[oracle@ora101 datapump]$ impdp system/Manager1 directory=DATA_PUMP FULL=Y dumpfile=PROD_FULL.dump logfile=PROD_FULL.log table_exists_action=skip
Tablespace Level Export:
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP TABLESPACES=HR
dumpfile=TBS_HR.dump logfile=TBS_HR.log
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP TABLESPACES=HR,MANIT
dumpfile=TBS_HR_MANIT.dump logfile=TBS_HR_MANIT.log
[oracle@ora101 ~]$ expdp system/Manager1 directory=DATA_PUMP TABLESPACES=HR
dumpfile=TBS_HR.dump logfile=TBS_HR.log PARALLEL=4 COMPRESSION=ALL
Option Purpose
PARALLEL=4 Faster export
COMPRESSION=ALL Reduce size
EXCLUDE=STATISTICS Skip stats
FLASHBACK_TIME=SYSTIMESTAMP Consistent backup
LOGTIME=ALL Detailed logs
Option What Happens Use Case
SKIP Ignore table Safe runs
APPEND Add rows Data merge
TRUNCATE Delete + reload Refresh
REPLACE Drop & recreate Migration
Data Pump Export Progress %:
Use below query to get the percentage (%) completion of the export process
SELECT SID, SERIAL#, USERNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;
SELECT * FROM dba_datapump_sessions;
SET LINES 200
COL job_name FORMAT A25
COL state FORMAT A12
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs;
SELECT sid, serial#, opname,
ROUND(sofar/totalwork*100,2) "%DONE"
FROM v$session_longops
WHERE opname LIKE 'SYS_EXPORT%' OR opname LIKE 'SYS_IMPORT%';
No comments:
Post a Comment