This document outlines the steps required to create a backup, i.e. dump file, of schemas in an Oracle database. This dump file can be used with the Backup Provisioner to provision an Oracle database environment. Throughout this document we will assume that a non-privileged DEV user is used to connect to the database.

1 - Create an export directory if necessary

A directory must exist for the dump file to be exported to. Privileged users can use the default DATA_PUMP_DIR directory object. If a Non-privileged user is used for the database connection then a privileged user must create a directory object that can be used for the export. Firstly, the directory must be created on the filesystem of the database server. In the example below a directory is created at /opt/oracle/backup:

mkdir -p /opt/oracle/backup

 Next a directory object can be created on the database server that references our newly created filesystem directory. In this case we call the directory object DATAEXPORT:

CREATE DIRECTORY DATAEXPORT AS '/opt/oracle/backup';

Finally, grant the DEV user access permissions to DATAEXPORT:

GRANT READ, WRITE ON DIRECTORY DATAEXPORT TO DEV;

2 - Grant permissions to export schemas

The user performing the export will need the EXP_FULL_DATABASE privilege. The following SQL grants the DEV  user the ability to export schemas:

GRANT EXP_FULL_DATABASE to DEV;

3 - Perform the export

The expdp command can then be used to create the dump file. The command below performs a data-less export of the DEV schema:

expdp SCHEMAS=DEV DIRECTORY=DATAEXPORT DUMPFILE=dev.dmp CONTENT=METADATA_ONLY

Where:

• SCHEMAS is a comma separated list of schemas to include in the backup. In this case only the DEV schema is specified.
• DIRECTORY is the directory alias where the dump file should be created. Here we use the DATAEXPORT directory created in step 1.
• DUMPFILE specifies the filename of the dump file.
• CONTENT specifies whether the backup contains data, metadata or both. In this case it's set to METADATA_ONLY so that no table row data is included. The value ALL can be used to create a dump file containing both DDL and data.

The command above results in a backup file located at: /opt/oracle/backup/dev.dmp.

Remote Database

The command above assumes that you're connecting to a local Oracle DB instance, and will prompt you for a username and password. A connection identifier can be specified as the first argument to connect to a remote database. For example, to export the DEV schema from a remote database at 10.1.0.120, the following command can be used:

expdp DEV/DEV_PASSWORD@10.1.0.120:1521/XE SCHEMAS=DEV DIRECTORY=DATAEXPORT DUMPFILE=dev.dmp CONTENT=METADATA_ONLY

Multiple Schemas

Multiple schemas can be exported by specifying a command separated list to the SCHEMAS option. For example, to export the DEV, HR and INVENTORY schemas, and all data contained in those schemas, the following command would be used:

expdp SCHEMAS=DEV,HR,INVENTORY DIRECTORY=DATAEXPORT DUMPFILE=multi_schema.dmp CONTENT=ALL

Please see here for a full reference of the available options to the expdp command.