Oracle Data Pump (Export - Part 1)

Oracle Datapump Overview!

Oracle Data Pump

Oracle data pump is a process used to move the data and metadata between Oracle databases. This method is useful for migrating data among databases of different versions hosted on different operating systems and from on-premises to cloud or on-premises to on-premises. This also means that by using the Data Pump, one can migrate all of the database or a portion of the database from a non-CDB into a PDB, or between PDBs within the same or different CDBs, and from a PDB into a non-CDB.

Did you know?
PDB: Pluggable Database, A self contained database that can be plugged into a CDB.
CDB: Container Database, A higher level container that holds multiple PDBs.
non-CDB: non-container Database, A traditional database that doesnot use multi-tenant architecture.

Oracle Data Pump Export Modes

Full Database Mode Extract. Illustration by Shashi Kallae.

Full Mode

In a full database export mode, the whole database is unloaded using the parameter “FULL.” DATAPUMP_EXP_FULL_DATABASE role is required to perform this operation. A full transportable export exports all objects and data necessary to create a complete copy of the database. For this, you have to specify TRANSPORTABLE=ALWAYS parameter along with the FULL parameter.

Full Transportable export requirements

1. The user who performs the Full Transportable Export must be granted the DATAPUMP_EXP_FULL_DATABASE role.

2. The user performing the export should not have their default Tablespace set to any of the Tablespaces that are being moved or exported. This is a precautionary measure set to handle a smooth and error-free export process.

3. ENCRYPTION_PASSWORD parameter must be applied if the export contains TDE columns or SecureFiles LOB Columns.

4. The source and target platforms must be on platforms with the same endianness (A big-endian system stores the most significant byte of a word at the smallest memory address and the least significant byte at the largest. A little-endian system, in contrast, stores the least-significant byte at the smallest address).

5. If the above are on different endianness, then you must convert the data. You can use DBMS_FILE_TRANSFER package or RMAN convert command to achieve the compatibility.

6. Ensure that the storage segment objects are kept with one type of Tablespace (either administrative or user-defined transportable) to facilitate a smooth and reliable export. Storage for a single object cannot straddle the two kinds of Tablespaces.

7. Auditing cannot be enabled for the Tablespaces stored in the administrative Tablespaces (SYSTEM and SYSAUX), if the audit trail information is stored in the user-defined Tablespace.

8. The Data Pump Version parameter must be set to atleast 12.0, if both source and target databases are running on the Oracle version 12c (This should be considered for Oracle database version 11.2.0.3).

Example Commands

> expdp user_name FULL=y DUMPFILE=expdat.dmp DIRECTORY=data_pump_dir 
      TRANSPORTABLE=always VERSION=12.0 LOGFILE=export.log

Create a directory on the database server @ /home/oracle/

SQL> create directory expdpfull_directory as ‘/home/oracle/expdpfull_directory’;

expdp username/password@service_name FULL=y DIRECTORY=dpump_dir1 DUMPFILE=full_export.dmp LOGFILE=full_export.log

expdp userid/password@exampleoracledatabase dumpfile=fullexpdp.dmp logfile=fullexpdp.log directory=expdpfull_directory full=y

Example:
“username" and “password" are the credentials for a user with the necessary privileges to perform the export.

“service_name" is the service name of the database.

"FULL=y" specifies a full database export.

"DIRECTORY=dpump_dir1" specifies the directory object to be used for the export.

"DUMPFILE=full_export.dmp" specifies the name of the dump file.

"LOGFILE=full_export.log" specifies the name of the log file.

Schema Mode

Schema Mode Extract. Illustration by Shashi Kallae.

1. A schema export is the default export mode. Using the SCHEMAS parameter, you can export the schema using Data Pump.

2. A user must have the “DATAPUMP_EXP_FULL_DATABASE role” to export other schemas, including the schema definitions themselves and related system privilege grants. Otherwise, that user can export only his/her schema.

3. The SYS schema cannot be used as a source schema to export jobs.

4. If the objects in one schema refer to the objects in a different schema, then both the referencing schema and referenced schema should be included in the list of schemas to be exported.

5. The reason for the above is to ensure that all the dependencies are captured in the export file, maintaining the integrity of the objects during the import process.

Example Commands

expdp userid/password@exampleoracledatabase dumpfile=schemaexpdp.dmp logfile=schemaexpdp.log directory=expdpfull_directory schemas=schema1,schema2

Table Mode

Table Mode Extract. Illustration by Shashi Kallae.

1. Only a specified set of tables, partitions, and their dependent objects can be exported in table mode.

2. The table mode export can be performed using the Oracle Dump Pump by specifying the TABLES parameter.

3. Only object metadata is unloaded if the “TRANSPORTABLE=ALWAYS” parameter is used along with the “TABLES” parameter. To move the actual data, the data files should be copied from the source database to the target database. Once done, these files should be processed by the Oracle recovery manager (RMAN).

4. From the above step:3, if the table being exported contains encrypted columns such as TDE columns or SecureLOBs columns, then the ENCRYPTION_PASSWORD parameter must also be used.

5. The “DATAPUMP_EXP_FULL_DATABASE role” must be granted to the user who is performing the Table mode export in order to export the tables from other users’ schema.

6. Type definitions typically include data types and constraints associated with the columns in the table. Note that the type definitions are not exported in the table mode.

7. The type definitions are expected to already exist in the target database at import time.

Example Commands

expdp userid/password@exampleoracledatabase dumpfile=tablesexpdp.dmp logfile=tablesexpdp.log directory=expdpfull_directory tables=table_name.test

Tablespace Mode

Table Space Mode Extract. Illustration by Shashi Kallae.

1. Data Pump can be used to export the Table spaces using the TABLESPACES parameter.

2. In tablespace mode, only the tables contained in a set of tablespaces and their dependent objects are unloaded.

3. Privileged users get all the tables, and unprivileged users get only those that reside in their schemas.

Example Commands

Syntax:

expdp hr DIRECTORY=dpump_dir1 DUMPFILE=expdat.dmp TABLESPACES=users,example

Transport Tablespace Mode

Transport Table Space Mode Extract. Illustration by Shashi Kallae.

1. Transportable Tablespace export process is to move or transport tablespaces and their data from one database to another using Data Pump with “TRANSPORT_TABLESPACES” parameter.

2. Only the metadata for the tables and their dependent objects within a specified set of tablespaces are exported.

3. The specified tables within the specified set of tablespaces must be “self-contained”, which means all storage segments of all tables and indexes within the specified tablespace set must be entirely contained within that set.

4. Storage segments refer to the physical storage structures of database objects, including tables and indexes.

5. Type definitions for the columns of the tables in the specified tablespaces are exported and imported. The schemas owning those types must be present in the target database.

6. Transportable Tablespace exports cannot be restarted once stopped.

7. In Transport tablespace exports, the target database must be at the same or later release level as the source database.

8. ENCRYPTION_PASSWORD parameter should be used, if any of the tablespaces being exported contains tables with encrypted columns such as TDE columns (Transparent Data encryption) or SecureLOB Columns. If not used the below warning message occurs.

ORA-39396: Warning: exporting encrypted data using transportable option without password

Example Commands

Syntax:

expdp system/password@service_name TRANSPORT_TABLESPACES=tablespace_name1,tablespace_name2 DIRECTORY=dpump_dir1 DUMPFILE=transport_tablespace.dmp LOGFILE=transport_tablespace.log

Conclusion

An Oracle Database export can be performed using any of the above-mentioned methods using Oracle Data Pump. But, as a Database Administrator, it becomes your responsibility to choose which method best suits your requirements at that time. Sometimes, you may be asked to move only a particular schema, sometimes just a Table or a Tablespace.

For a smooth and seamless export, always follow the suggestions provided by Oracle support and tune them according to your organization's requirements, as this is not a one-size-fits-all solution.