This document outlines the steps required to create a data-less backup of Oracle schemas.

 Prerequisites

This document assumes that you are able to run the following commands on the database server: sqlplus, expdp and impdp. The commands below are run using the SYS AS SYSDBA user, but this is not a requirement.

 1 - Create a directory object for the export location

Use the CREATE DIRECTORY command to create an alias for the export location on disk where the backup will be written to. The example below creates a DATAEXPORT alias for the /opt/oracle/backup  path in a sqlplus prompt:

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

Note: The directory path provided must exist, as it will not be created by the export process.

If the export is being undertaken by a user without administrative privileges, then additional permissions may need to be granted. The commands below grant the HR user the required additional permissions to perform the export:

SQL> GRANT READ, WRITE ON DIRECTORY DATAEXPORT TO HR;
SQL> GRANT EXP_FULL_DATABASE to HR;

 2 - Create the export

The expdp command can then be used to create the backup file. The command below performs a data-less export of the HR and DEV schemas:

$ expdp SCHEMAS=HR,DEV DIRECTORY=DATAEXPORT DUMPFILE=hr_dev.dmp CONTENT=METADATA_ONLY

Where:

• SCHEMAS is a comma separated list of schemas to include in the backup.
• DIRECTORY is the directory alias where the backup file should be created. Here we use the DATAEXPORT alias created in step 1.
• DUMPFILE specifies the filename of the backup 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.

This results in a backup file located at: /opt/oracle/backup/hr_dev.dmp. This backup file can then be copied to another Oracle database instance to be imported.

Restoring a backup file

The backup file generated in the previous section can be imported using the impdp command:

$ impdp CONTENT=METADATA_ONLY DUMPFILE=DATAEXPORT:hr_dev.dmp

Where:

• CONTENT again specifies METADATA_ONLY as this is a data-less backup.
• DUMPFILE  is the full location of the backup file in the format DIRECTORY_ALIAS:FILE_NAME.

There are a few things to note:

• The schemas stored in the backup file must already exist on the target database. In the earlier backup section, the HR and DEV schemas were backed up, therefore the HR and DEV schemas must exist on the target server where the backup is being restored.
• The schemas being restored on the target database must be empty (i.e. have no objects), otherwise the import will fail with errors.
• The directory alias DATAEXPORT may need to be recreated on the target server where the backup is being restored. 

Restoring a backup file with schema mapping

It may be desirable to rename the schemas when they're restored to a target database. For example, if the backup file contained the HR and DEV schemas, then it might be desirable to rename them to HR_COPY and DEV_COPY respectively when restoring the backup. This can be achieved using a impdp command per schema as the example below shows:

$ impdp SCHEMAS=HR CONTENT=METADATA_ONLY DUMPFILE=DATAEXPORT:hr_dev.dmp REMAP_SCHEMA=HR:HR_COPY
$ impdp SCHEMAS=DEV CONTENT=METADATA_ONLY DUMPFILE=DATAEXPORT:hr_dev.dmp REMAP_SCHEMA=DEV:DEV_COPY

The first command restores the HR schema from the backup file to the HR_COPY schema, whilst the second command restores the DEV schema from the backup file to the DEV_COPY schema. The SCHEMAS argument specifies the schema to import from the backup file, whilst the REMAP_SCHEMA argument specifies the mapping to apply in the format OLD_SCHEMA_NAME:NEW_SCHEMA_NAME .