DATAPUMP (export import) IN ORACLE

-

-----------------------------------

EXPDP (Data Pump Export) Parameters :

------------------------------------

Parameter       Description                                                   Example

DIRECTORY   Specifies the directory object where dump files are written. DIRECTORY=DATA_PUMP_DIR

DUMPFILE   Specifies the name(s) of the dump file(s).                  DUMPFILE=expdp_dump_%U.dmp (for parallel)

LOGFILE       Specifies the name of the log file.                          LOGFILE=expdp.log

SCHEMAS       Exports one or more schemas.                                  SCHEMAS=HR,SCOTT

TABLES       Exports specific tables.                                      TABLES=HR.EMPLOYEES,HR.DEPARTMENTS

FULL       Exports the entire database.                                  FULL=Y

CONTENT       Specifies data to be exported (all, data only, or metadata only). CONTENT=ALL

PARALLEL   Enables parallelism by specifying the number of threads for the job. PARALLEL=4

EXCLUDE       Excludes specific objects or object types from the export. EXCLUDE=TABLE:"IN ('LOG_TABLE')"

INCLUDE       Includes only specified objects or object types in the export. INCLUDE=TABLE:"LIKE 'HR%'"

ESTIMATE_ONLY Estimates the size of the export job without performing it.  ESTIMATE_ONLY=Y

COMPRESSION   Specifies compression for metadata or all data.              COMPRESSION=ALL

ENCRYPTION   Enables encryption for the dump file.                        ENCRYPTION=ALL

VERSION       Specifies the database version for compatibility.              VERSION=12.2


ex -  expdp sbz/sbz DIRECTORY=dp_dir DUMPFILE=sb_tables.dmp TABLES=emp3 CONTENT= METADATA_ONLY;


IMPDP (Data Pump Import) Parameters :

-----------------------------------

Parameter     Description                                                         Example

DIRECTORY    Specifies the directory object where dump files are located.      DIRECTORY=DATA_PUMP_DIR

DUMPFILE Specifies the name(s) of the dump file(s) to import.              DUMPFILE=expdp_dump_%U.dmp

LOGFILE      Specifies the name of the log file.                              LOGFILE=impdp.log

SCHEMAS      Imports one or more schemas.                                      SCHEMAS=HR,SCOTT

TABLES      Imports specific tables.                                          TABLES=HR.EMPLOYEES,HR.DEPARTMENTS

FULL      Imports the entire database.                                      FULL=Y

CONTENT      Specifies data to be imported (all, data only, or metadata only). CONTENT=ALL

PARALLEL Enables parallelism by specifying the number of threads for the job.PARALLEL=4

REMAP_SCHEMA Remaps objects from one schema to another.                          REMAP_SCHEMA=HR:SCOTT

REMAP_TABLESPACE Remaps objects from one tablespace to another.                  REMAP_TABLESPACE=OLD_TBS:NEW_TBS

TRANSFORM Allows transformations such as disabling logging during import. TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

EXCLUDE      Excludes specific objects or object types from the import.          EXCLUDE=TABLE:"IN ('LOG_TABLE')"

INCLUDE      Includes only specified objects or object types in the import.      INCLUDE=INDEX

SKIP_UNUSABLE_INDEXES Skips unusable indexes during import.                  SKIP_UNUSABLE_INDEXES=Y

DATA_ACCESS_METHOD Specifies the method for data access during import.          DATA_ACCESS_METHOD=DIRECT_PATH


ex - impdp sbz/sbz DIRECTORY=dp_dir DUMPFILE=sb_tables.dmp TABLES=emp3 CONTENT= METADATA_ONLY;


DIFFERENCE BETWEEN EXPDP - EXPORT 

| **Feature/Aspect**          | **EXPDP (Data Pump Export)**                                       | **EXP (Traditional Export)**                                |

|-----------------------------|-------------------------------------------------------------------|-----------------------------------------------------------|

| **Introduced In**           | Oracle 10g                                                       | Oracle 7                                                  |

| **Performance**             | Faster due to direct path loading and parallelism.               | Slower, as it uses SQL statements to extract data.         |

| **Parallel Execution**      | Supports parallelism for faster exports.                         | Does not support parallelism.                             |

| **Direct Path Export**      | Optimized for direct data access without passing through SQL.     | Uses conventional path; no direct access to data blocks.  |

| **Resume Capability**       | Supports job restart after a failure or interruption.            | Does not support job restarts; requires a fresh start.    |

| **Export Format**           | Binary dump files (`.dmp`).                                      | Binary dump files (`.dmp`).                               |

| **Network Mode**            | Can export data directly over a network to a remote database.    | Does not support network exports.                        |

| **Compression**             | Supports compression of metadata and data.                      | Does not support compression.                            |

| **Encryption**              | Supports encryption of dump files.                              | Does not support encryption.                             |

| **Partitioning**            | Can export specific partitions of a table.                      | Does not support partition-level exports.                |

| **Fine-grained Control**    | Can include or exclude specific object types or objects.         | Limited control using `QUERY` or object lists.           |

| **Logging**                 | Provides detailed logs with timestamps and job status.           | Provides basic logs.                                      |

| **Advanced Features**       | Supports transformations (e.g., remapping schemas, tablespaces).| Limited to basic export capabilities.                    |

| **Directory Object**        | Requires the use of an Oracle directory object.                 | Relies on OS-level file paths for dump file locations.    |

| **Cross-platform Support**  | Easier export/import for cross-platform database migrations.     | More manual steps required for cross-platform migration. |


ex -


exp system/password file=full_export.dmp log=full_export.log full=y


imp system/password file=full_export.dmp log=full_import.log full=y


-----------------------

scp * oracle@stan:/u01/dp_dir 

on stanby 

mkdir -p /u01/dp_dir

chmod 775 /u01/dp_dir


GRANT READ, WRITE ON DIRECTORY dp_dir TO sbz;


impdp sbz/sbz DIRECTORY=dp_dir DUMPFILE=sb_tables.dmp TABLES=emp3;


Create Directory Object (if not already done):

-----------------------------


CREATE DIRECTORY dp_dir AS '/u01/dpump';

GRANT READ, WRITE ON DIRECTORY dp_dir TO hr;

Run Data Pump Import:


shell

impdp hr/hr_password DIRECTORY=dp_dir DUMPFILE=hr_tables.dmp TABLES=employees,departments

Additional Options


Content: You can use the CONTENT parameter to specify whether to include data (DATA_ONLY), metadata (METADATA_ONLY), or both (ALL).


shell


expdp hr/hr_password DIRECTORY=dp_dir DUMPFILE=hr_tables.dmp TABLES=employees,departments CONTENT=ALL


Schemas: If you need to export/import tables from specific schemas, use the SCHEMAS parameter.


expdp sbz/sbz DIRECTORY=dp_dir DUMPFILE=sbz_tables.dmp SCHEMAS=sbz TABLES=emp2


Remapping: Use the REMAP_TABLESPACE and REMAP_SCHEMA parameters to change tablespaces or schemas during the import.


shell

impdp hr/hr_password DIRECTORY=dp_dir DUMPFILE=hr_tables.dmp TABLES=employees,departments REMAP_TABLE

==================================

Oracle Data Pump - expdp, impdp :

===================================

Create Data Pump Directory


mkdir -p /u01/dp_dir


Create directory inside the database


create directory datapump as '/u01/dp_dir';

 

Grant permissions on directory :


grant read,write on directory datapump to sys; 


View directory information :


select * from dba_directories;

===================================

Importing Metadata Only

===================================

To import only the metadata (schema definitions, users, roles,  etc.),table structure will copy. you can use the CONTENT=METADATA_ONLY parameter.


expdp sbz/sbz DIRECTORY=dp_dir DUMPFILE=sb_tables.dmp TABLES=emp3 CONTENT= METADATA_ONLY;


expdp c##sbz/sbz DIRECTORY=datapump DUMPFILE=sbz_tables.dmp TABLES=employees CONTENT= METADATA_ONLY;


impdp c##shiv/sbz DIRECTORY=datapump DUMPFILE=sbz_tables.dmp TABLES=employees CONTENT=METADATA_ONLY


impdp c##shiv/sbz DIRECTORY=datapump DUMPFILE=sbz_tables.dmp TABLES=employees CONTENT=METADATA_ONLY REMAP_SCHEMA=C##SBZ:C##SHIV


TO view the schema of the table in the dump file:


impdp c##shiv/sbz DIRECTORY=datapump DUMPFILE=sbz_tables.dmp CONTENT=METADATA_ONLY SQLFILE=check.sql

Open check.sql and confirm the owner/schema of the employees table.


====================

Importing Data Only

====================

To import only the data without the metadata, you can use the CONTENT=DATA_ONLY parameter.


expdp sbz/sbz DIRECTORY=dp_dir DUMPFILE=sb_tables.dmp TABLES=emp3 CONTENT= CONTENT; 


expdp c##sbz/sbz DIRECTORY=datapump DUMPFILE=sbz_emp1.dmp TABLES=employees CONTENT=DATA_ONLY


impdp c##shiv/sbz DIRECTORY=datapump DUMPFILE=sbz_emp1.dmp TABLES=employees CONTENT=DATA_ONLY REMAP_SCHEMA=c##sbz:c##shiv



expdp c##sbz/sbz DIRECTORY=datapump DUMPFILE=sbz_emp.dmp TABLES=employees


===============================

Both content and metadata :

=============================

expdp sbz/sbz DIRECTORY=dp_dir DUMPFILE=sb_tables.dmp TABLES=emp2 CONTENT=ALL;

====================================

Table Export and Import

====================================

expdp directory=datapump dumpfile=emp.dmp logfile=emp.log tables='sbz.emp2'


Import table where source and target schema are same :


impdp directory=datapump dumpfile=emp.dmp logfile=imp.log tables='sbz.emp2'


Import table to another schema :


impdp shiv/shiv directory=datapump dumpfile=emp.dmp logfile=emp.log tables='emp2' remap_schema='sbz:shiv'


Import tables to another tablespace (only in datapump):


impdp directory=datapump dumpfile=emp.dmp logfile=imp.log tables='emp2' remap_schema='usr1:emp' remap_tablespace='USERS:MYTBS'


Import table to a different name or rename table or remap_table :


impdp directory=datapump dumpfile=emp.dmp logfile=imp.log tables='emp2' remap_table='sbz.emp2:usr1.emp'


impdp c##sbz/sbz directory=datapump dumpfile=employees_metadata.dmp logfile=imp.log tables='employees' remap_table='c##sbz.employees:c##sbz.emp2'


Import only the rows from an exported table without loading table any table definitions :


 impdp directory=datapump dumpfile=emp.dmp logfile=imp_emp.log tables='emp2' content=DATA_ONLY

=============================================

Schema Export and Import

==============================================


Take schema level export :


expdp c##sbz/sbz directory=datapump dumpfile=sbz.dmp logfile=sbz.log schemas='c##sbz'


Import source schema objects into same schema on target :


impdp c##shiv/sbz directory=datapump dumpfile=sbz.dmp logfile=sbz.log remap_schema='c##sbz:c##shiv'


Import source schema objects into a different schema on target :


impdp directory=datapump dumpfile=sbz.dmp logfile=sbz.log remap_schema='sbz:usr1'

==========================================

Rows Export and Import  -   

=========================================

Take row level export :


expdp directory=datapump dumpfile=emprows_bkp.dmp logfile=emprows_bkp.log tables='sbz.emp2' query=\"where id=10\"


Import rows where source and target schema are same


impdp directory=datapump dumpfile=emprows_bkp.dmp logfile=imp_emprows.log tables='sbz.emp2'


=========================================

Full Database Export and Import

======================================

Take database level export :


expdp directory=datapump dumpfile=fullprod.dmp logfile=fullprod.log full=y


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 <username> CASCADE;


impdp directory=datapump dumpfile=fullprod.dmp logfile=imp_fullprod.log full=y


====================================

Data Pump Import Over Network

====================================

When you try to move large tables or schema between two Oracle databases, datapump export might take lot of disk space. 

The exported dump files may take lot of space on the disk. The best workaround is to use dblink with Oracle datapump to move data from one oracle database to another.


Note: when you use datapump with dblink, there are no dumpfiles created on source. The data is transferred from one database to another over network


Add source database TNS entry into tnsnames.ora of the target database : tnsnames.ora file 


On target, we need to create a database link using the TNS entry created above:


create directory datapump as '/u01/dp_dir';

mkdir /u01/dp_dir

chmod 755 /u01/dp_dir


SQL> create user shiv identified by shiv;

User created.


SQL> grant connect, resource to shiv;

Grant succeeded.


SQL> grant read, write on directory datapump to shiv;

Grant succeeded.


SQL> grant create database link to shiv;

Grant succeeded.


SQL> conn shiv/shiv

Connected.


SQL> create database link prod connect to sbz identified by sbz using 'prod';

Database link created.


impdp shiv/shiv DIRECTORY=datapump LOGFILE=dblink_transfer.log NETWORK_LINK=prod remap_schema=sbz:shiv


To import multiple schemas, make sure to use a DBA user (sys) to perform this action :


impdp sys directory=datapump LOGFILE=dblink_transfer.log network_link=prod schemas=sbz,scott,usr1

================================

Data Pump Performance Tuning

===============================

You can always use DIRECT=y parameter to perform faster exports and imports.

You can also use PARALLEL parameter to start multiple export and import process for faster performance.

IMP-(direct=y it will direct data from datafiles and store it to directory it will skip the db buffer part that's why performance improves and db will not imapact)


Make sure to use %U with the dumpfile name so multiple dumpfiles can be read/write simultaneously 


expdp directory=datapump DIRECT=y dumpfile=sbz_%U.dmp logfile=expdp_sbz.log schemas=sbz parallel=4


(Using the PARALLEL parameter in Data Pump (impdp) allows you to specify the number of active worker processes for the job.

 This can significantly speed up the import process by parallelizing the work.)

 

impdp directory=datapump DIRECT=y dumpfile=sbz_%U.dmp logfile=impdp_sbz.log schemas=sbz parallel=4


==================================

Comments

Post a Comment

Popular posts from this blog

RAC NOTES

-
================================== Oracle Clusterware Startup Process : =================================== 1.File Read Order: - OLR (Oracle Local Registry) - VD (Voting Disk) - OCR (Oracle Cluster Registry) 2.Process Start Order: - OHASD (Oracle High Availability Services Daemon) - Main Wrapper script (etc/inittab) , OLR  - CSSD (Cluster Synchronization Services Daemon) - Main  Voting disk - GPNP profile - KFED/KFOD - CRSD (Cluster Ready Services Daemon)  - Main look for OCR and GPNP profile  Since Oracle 11g Release 2, the ASM spfile (Automatic Storage Management Server Parameter File) is automatically stored in the first disk group created during Grid Infrastructure installation. However, because the Voting Disk and OCR are stored in ASM, ASM must be started for Clusterware to function. This creates a challenge: how do you start ASM if its spfile is also stored on an ASM disk group? Clusterware Startup Sequence and Resolution: =====================================...
Read more

ORACLE CLOUD INFRASTRUCTURE

-
OCI : ======= Oracle Cloud Infrastructure (OCI) is Oracle's cloud computing platform that provides a wide range of cloud services, including computing, storage, networking, databases, and security. It is designed for running enterprise workloads with high performance, security, and reliability. OCI Service Models OCI offers services across three major cloud models: 1)Infrastructure as a Service (IaaS) ------------------------------------- Provides fundamental cloud resources like compute, storage, and networking. Examples: Compute (Bare Metal, Virtual Machines, GPUs) Block, File & Object Storage Networking (Load Balancers, VPN, FastConnect) Oracle Cloud VMware Solution Dedicated Bare Metal Servers 2)Platform as a Service (PaaS) : ------------------------------- Provides managed services for databases, application development, and AI/ML without managing underlying infrastructure. Examples: Oracle Autonomous Database (Self-driving, self-securing, and self-repairing DB) Oracle Dat...
Read more