This document outlines the steps required to provision an environment using an Oracle Data Pump export file. It will primarily focus on configuring the Backup Provisioner to provision the shadow environment. Throughout this document we will assume that a non-privileged SHADOW user is used to connect to the database.

1 - Ensure the dump file is accessible to the target database

If the dump file will be imported into the same database instance from which it was exported, then this step can be skipped.

If the dump file will be imported into a database running on another machine from which it was exported, then we first need to ensure the dump file is accessible to the target database. Firstly, ensure that a directory exists on the target database machine for the dump file to be placed. For example, the following step creates a /opt/oracle/backup directory on the filesystem:

mkdir -p /opt/oracle/backup

Then create a DATAIMPORT directory object within the database that references and filesystem directory, and grants the SHADOW user permission to access that directory:

CREATE DIRECTORY DATAIMPORT AS '/opt/oracle/backup';
GRANT READ, WRITE ON DIRECTORY DATAIMPORT TO SHADOW;

Finally, the dump file can be copied to the target database. In the example below, devdb is an ssh alias for the machine where the dump file was created:

scp devdb:/opt/oracle/backup/dev.dmp /opt/oracle/backup/dev.dmp 

2 - Ensure target schemas exist

In this case the SHADOW schema already exists on our target database. However, if we were importing multiple schemas from a dump file, e.g. SHADOW, HR and INVENTORY, then we would need to ensure that all three schemas exist on the target database before the import is performed.

3 - Grant permissions to import schemas

The user performing the import will need the IMP_FULL_DATABASE privilege. The following SQL grants the SHADOW user the ability to import schemas:

GRANT IMP_FULL_DATABASE to SHADOW;

4 - Configure the backup provisioner

The Backup Provisioner uses a backup file to provision an environment. That is, the backup provisioner can be configured to use a dump file that represents a migration version, and to restore that dump file to the configured database. The backup provisioner will kick in and restore the dump file when required without the user needing to trigger the process directly.

The TOML configuration below gives an example how the shadow environment can be configured to use the backup provisioner:

[environments.shadow]
url = "jdbc:oracle:thin:@//localhost:1521/XE"
user = "SHADOW"
password = "${localSecret.MyPasswordKey}"
schemas = ["SHADOW"]
provisioner = "backup"
 
[environments.shadow.resolvers.backup]
backupFilePath = "DATAIMPORT:dev.dmp"
backupVersion = "001"
oracle.connectionIdentifier = "SHADOW/SHADOW_PASSWORD@localhost:1521/XE"
oracle.importContent = "METADATA_ONLY"
 
[environments.shadow.resolvers.backup.oracle.schemaMapping]
"DEV" = "SHADOW"

The [environments.shadow] section defines an environment, which represents a database and the details needed to connect to that database. See Environments settings for more details on how to setup an environment. The provisioner = "backup" parameter specifies that the shadow environment should use the backup provisioner.

The [environments.shadow.resolvers.backup] section configures the backup provisioner for the shadow environment. Below we take a look at each of the fields defined above:

• backupFilePath - The file path to the dump file that will be restored. The dump file location is given in the format DIRECTORY:filename where DATAIMPORT is the directory created in step 1, and dev.dmp is the dump file name placed in that directory on disk. 
• backupVersion - The version of the database that the backup represents. When creating a baseline for a new Flyway project it's standard to use version 001, as the backup represents the initial state of the database before any subsequent migrations have been applied.
• oracle.connectionIdentifier - The connect identifier used to connect to the target database i.e. An Oracle*Net connect descriptor or a net service name (usually defined in the tnsnames.ora file). The example above uses a connect identifier of the form username/password@[//]host[:port][/service_name]. A tnsnames.ora connection alias could also be specified here for example.
  
  Note: The connect identifier specified must contain or map to all data required to log in to the database without any user interaction. That is, using the connect Identifier should not require a username or password to be manually entered. The example above achieves this by including the username and password in the connect identifier. Alternatively, if using a tnsnames.ora connection alias like /@MYALIAS, then a password must exist in an Oracle Wallet for that alias and the wallet should be configured in the sqlnet.ora.
• oracle.importContent - The content to restore to the target schema. Valid values are:
  • METADATA_ONLY - Loads only database object definitions i.e. no data.
  • ALL - Loads database object definitions and data.

The [environments.shadow.resolvers.backup.oracle.schemaMapping] section is optional and can be useful when wanting to restore schemas under a new name . A mapping between schema names can be specified where the key is the schema name in the dump file and the value is the new schema name that it should be mapped to. For example, our dev.dmp dump file contains content for the DEV schema. However, in this case we want to restore that content to the schema named SHADOW. Therefore, we specify "DEV" = "SHADOW" to indicate that the content for the DEV schema in the dump file should be restored to the SHADOW schema in the database. Multiple mappings can be specified in this section. For example, to restore the schemas DEV1, DEV2 and DEV3 in a dump file to the schemas SHADOW1, SHADOW2 and SHADOW3 respectively, the following configuration would be specified:

[environments.shadow.resolvers.backup.oracle.schemaMapping]
"DEV1" = "SHADOW1"
"DEV2" = "SHADOW2"
"DEV3" = "SHADOW3"

5 - Trigger the environment to be provisioned

As mentioned earlier, Flyway will detect when the dump file needs to be restored to the target database, Usually this is when an interaction with the target database is triggered and the dump file has not been restored yet. For example, if we were to run a flyway info against the shadow environment configured above, then flyway would detect that it requires provisioning and will restore the backup. The output below shows this in action:

$ flyway info -environment=shadow
...
Restored backup to environment shadow in 12432ms
...
Creating Schema History table "SHADOW"."flyway_schema_history" with baseline ...
...
Schema version: 001
 
+----------+---------+----------------------+----------+---------------------+----------+----------+
| Category | Version | Description          | Type     | Installed On        | State    | Undoable |
+----------+---------+----------------------+----------+---------------------+----------+----------+
|          | 001     | Baseline from backup | BASELINE | 2025-03-10 11:24:17 | Baseline | No       |
+----------+---------+----------------------+----------+---------------------+----------+----------+

Running the info command again would not trigger the backup to be restored a second time, as Flyway would identify that it wasn't necessary. However, if the shadow database was Cleaned, then the dump file would be restored when Flyway next interacted with the shadow database.