Oracle Data Pump – Export Import -3

Hi,

In this article I will continue to explain the data pump technology in Oracle databases.

data pump

 

Read the following first and second articles before this article.

 

 

Exports in Data pump technology can be compressed and encrypted in for security and compression. You can take compressed and encrypted export like following.

[oracle@MehmetSalih ~]$  expdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP DUMPFILE=HR.dmp LOGFILE=HR.log compression=all encryption=all

 

You can export of lots of Schemas  in a same process( MEHMET, SALIH, DEVECI )like following.

[oracle@MehmetSalih ~]$ expdp \"/ as sysdba\" SCHEMAS=MEHMET,SALIH,DEVECI DIRECTORY=DATAPUMP DUMPFILE=mehmet_salih_deveci.dmp LOGFILE=mehmet_salih_deveci.log compression=all encryption=all

 

In the following example, the personel table of the HR schema is exported and imported.

expdp \"/ as sysdba\" tables=HR.personel DIRECTORY=DATAPUMP DUMPFILE=personel.dmp LOGFILE=personel.log

 

We can transfer this export file to the test server with scp command as follows.

[oracle@MehmetSalih ~]$  scp /backup/HR.dmp oracle@192.168.63.63:/Backup

 

This export file transfered to the test server is imported into the related test database as follows. Table will be appended with old and current values.

impdp \"/ as sysdba\" tables=HR.personel DIRECTORY=DATAPUMP DUMPFILE=personel.dmp LOGFILE=personel.log table_exists_action=append

 

You can export and import lots of tables in the same export process like following scripts.

expdp \"/ as sysdba\" tables=HR.personel,HR.sales DIRECTORY=DATAPUMP DUMPFILE=personel_sales.dmp LOGFILE=personel_sales.log

impdp \"/ as sysdba\" tables=HR.personel,HR.sales DIRECTORY=DATAPUMP DUMPFILE=personel_sales.dmp LOGFILE=personel_sales.log table_exists_action=replace

 

You can take full database export with datapump. But full database export is not like RMAN, In general terms, data pump does not provide for full backup like RMAN. RMAN is prefered for database full backup instead of datapump. Data pump is very effective for table and schema moving or backup.

You can take full database export with datapump like following.

expdp \"/ as sysdba\" DIRECTORY=DATAPUMP DUMPFILE=FULL_EXPORT.dmp LOGFILE=FULL_EXPORT.log FULL=Y

 

We can import the above HR schema directly into the test database.

impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=replace

On the other hand, we can speed up export and import  process by adding parallelism. Parallelism count depends on the number of cpu. For this, data pump technology allows us to speed up this process with parallel parameters. Parallelism can be given as follows. This number of parallelism should not more than the number of cpu of the physical server.

 

expdp \"/ as sysdba\" tables=HR.personel DIRECTORY=DATAPUMP DUMPFILE=personel%U.dmp LOGFILE=personel.log PARALLEL=8

 

One of the things that should be considered during the export import process is whether the data to be kept is in the same tablespaces. The tablespaces of tables to be moved are either created with the same name if the tablespaces are not exists in the target database. Or if the tablespaces are not exists in the target database, you can use remap tablespaces option to remap old tablespaces with new tablespaces.

We can provide this with the remap_tablespace parameter as follows. In the following example, while the HR schema in the source database is kept in the DATA01 tablespace, it will be kept in the existing DATA tablespace in the target database because there are no tablespace in the test database. We can provide this as follows.

 

impdp \"/ as sysdba\" SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=replace REMAP_TABLESPACE=DATA01:DATA

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

3 thoughts on “Oracle Data Pump – Export Import -3