...

Traditional packaging requires users to maintain a reference database, but often this is expensive and cumbersome to manage. Using an Oracle Ephemeral Database eliminates the need to manage an additional database on the pipeline. A default installation requires the use a Reference Database. You simply configure Liquibase to When using Oracle PDBs, configuration can be setup to have Liquibase create a temporary copy of a database in the pipeline to be used in place of the Reference Database. Once the copy is made, you will ensure that all of your changes work on the copy, implement them on your original database’s changelog, and then destroy the database copy. Follow this guide to learn how to implement this workflow.

Pre-requisites

• Use Requires Liquibase Enterprise 8.6 and higher

• Based Ephemeral database connection is based on Oracle multi-tenant PDB technology

• Ephemeral database connection requires a user on ORCLCDB with SYSOPER privileges and the ability to create pluggable databases

...

• Ephemeral database feature works ONLY for PDB databases

• Ephemeral database feature works ONLY for Oracle 19c and 21c

• Ephemeral database feature does not work for Oracle RDS

• You cannot place the temporary copied database in a pipeline or run a status on it because technically it does not exist in the pipeline.

Usage

<graphics and info regarding:

During packager operation it will create a pluggable PDB copy, package all the changes on it and drop the PDB copy after it, if Using an Oracle Ephemeral Database eliminates the need to manage an additional database on the pipeline. The ephemeral packaging feature creates a pluggable PDB copy when the packager runs, then packages all the changes on it, and drops the PDB copy after packaging is complete. If the final deploy is not skipped - it will deploy newly packaged changes on the desired db def database (which this is the DEV database in most of the examplescases)>

Backup and Restore Package Methods

.

Image Added

Backup and Restore Package Methods

In order to implement an Ephemeral Database, the databaseBackupRestoreMethod packageMethod package method on the deployPackager.properties needs to be configured.

Two new package methods are supported. You can use an internal or external container depending on your needs. If your internal container is secured and permissions do not allow you to make changes within it, the external package method is recommended.

See: Use the Required deployPackager.properties File

BackupRestoreOracleEphemeralInternalPdb

Create ORCLCDB SYSOPER User

You must create a new user on oracle CDB with the following privileges to create an ephemeral copy.

# Should be created on ORCLCDB
CREATE USER c##liquibase_ephemeral_user IDENTIFIED BY password;
GRANT CREATE SESSION TO c##liquibase_ephemeral_user CONTAINER=all;
GRANT CREATE PLUGGABLE DATABASE TO c##liquibase_ephemeral_user CONTAINER=all;
GRANT SYSOPER TO c##liquibase_ephemeral_user CONTAINER=all;

BackupRestoreOracleEphemeralExternalPdb

BackupRestoreOracleEphemeralExternalPdb differs from BackupRestoreOracleEphemeralInternalPdb, that it makes a copy between two different DB instances, so in all info below we are going to have database A and B, so we would like to make an ephemeral copy of the A’s database PDB to database B. To be able to use BackupRestoreOracleEphemeralExternalPdb as databaseBackupRestoreMethod our customer would need to prepare an additional set up for us.

Create ORCLCDB SYSOPER User

On the database A we will to create a new user on oracle CDB with the following privileges to be able to create an ephemeral copy

...

See: Use the Required deployPackager.properties File

1. Choose the Internal or External Method

First you must decide if you are using the internal or external Ephemeral method.

2. Create ORCLCDB SYSOPER User

Once that is decided, you will create a user. You must create a new user on Oracle CDB with the associated privileges to create an ephemeral copy for both internal and external methods.

3a. BackupRestoreOracleEphemeralInternalPdb

These are the Internal method privileges given to the user on the container.

-- connect as SYSDBA to an ORCLCDB to create common user
CREATE USER c##liquibase_ephemeral_user_a IDENTIFIED BY password;
GRANT CREATE SESSION TO c##liquibase_ephemeral_user_a CONTAINER=all;
GRANT CREATE PLUGGABLE DATABASE TO c##liquibase_ephemeral_user_a CONTAINER=all;
GRANT SYSOPER TO c##liquibase_ephemeral_user_a CONTAINER=all;

On the database B we will to create a new user on oracle CDB with the following privileges to be able to create an ephemeral copy and additionally create a link with database A

3b. BackupRestoreOracleEphemeralExternalPdb

These are the external method privileges given to the users for both the source and external containers.

The source database is the first database in a pipeline. The external database will be an ephemeral copy of the source database made on the external container.

Source container user privileges:

-- connect as SYSDBA to a SECONDSource DBORCLCDB to create common user 
CREATE USER c##liquibase_ephemeral_user_bsource IDENTIFIED BY password; 
GRANT CREATE SESSION TO c##liquibase_ephemeral_user_bsource CONTAINER=all;
GRANT CREATE PLUGGABLE DATABASE TO c##liquibase_ephemeral_user_b
CONTAINER=all;
GRANT SYSOPER TO c##liquibase_ephemeral_user_bsource CONTAINER=all;

--- you need to know hostname, port and service name of DB2 database instance
--- you need to know credetials for database user which has enough priviliges 
---     to perform copy operation 
CREATE PUBLIC DATABASE LINK liquibase_db_link
    CONNECT TO c##liquibase_ephemeral_user_a IDENTIFIED BY password
    USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=172.18.0.2)(PORT=1521))
            (CONNECT_DATA=(SERVICE_NAME=ORCLCDB))
        )';

Also there is a possibility to use TNS name during link creation, e.g.

CREATE DATABASE LINK liquibase_db_link

External container user privileges:

On the external database create a new user on oracle CDB with the following privileges. This allows Liquibase to create an ephemeral copy and create a link to the source database.

-- connect as SYSDBA to an External ORCLCDB to create common user
CREATE USER c##liquibase_ephemeral_external IDENTIFIED BY password;
GRANT CREATE SESSION TO c##liquibase_ephemeral_external CONTAINER=all;
GRANT CREATE PLUGGABLE DATABASE TO c##liquibase_ephemeral_external CONTAINER=all;
GRANT SYSOPER TO c##liquibase_ephemeral_external CONTAINER=all;

This is the database link on the external container to the source database.

--- you need to know hostname, port and service name of database instance
--- you need to know credentials for database user which has enough privileges 
--- to perform copy operation 
CREATE PUBLIC DATABASE LINK liquibase_db_link
    CONNECT TO c##liquibase_ephemeral_user_asource IDENTIFIED BY password
   USING TNS_NAME;

To check if database link has been created correctly please run query below, if you don’t get errors - database link is correct.

SELECT * FROM dual@liquibase_db_link;

Associated deployPackager.properties

These are packaging properties available specifically for the ephemeral database packaging method.

...

Property Name

...

Description

...

oracleEphemeralSourceName

...

[Optional]

1. defines a name for an Oracle source PDB

2. if not provided we will try to get it ourselves from a DB

3. validation - it should up to 30 characters long, contain no spaces [ ], slashes [/] and semicolons [;], also it can’t start from a number

...

oracleEphemeralCopyName

...

[Optional]

1. defines a name for an Oracle copy PDB

2. if not provided we will try to get a source PDB name from a DB and add _eph postfix to it, so if original bucket name was bucket_01, copied bucket name will be bucket_01_eph

3. validation - it should up to 30 characters long, contain no spaces [ ], slashes [/] and semicolons [;], also it can’t start from a number

...

oracleEphemeralDatabaseLinkName

...

[Optional]

1. defines a name for a database link which is used in BackupRestoreOracleEphemeralExternalPdb

2. if not provided a default name is used which is liquibase_db_link

3. validation - it should up to 128 characters long, contain no spaces [ ], slashes [/] and semicolons [;], also it can’t start from a number

...

oracleEphemeralCopyWithData

...

[Optional] By default we do not copy databases with data. If you would like to copy both the structure and data, you will use this property.

1. a boolean flag to turn on making a copy with data

2. by default it is false and we make a copy of the PDB without data (in other words we copy tables, but do not copy their content, the only table for which we copy content is our tracking DATABASECHANGELOG table)

...

oracleEphemeralSkipFinalDeploy

[Optional] This property allows you to:

• package

• run SQL rules

• create the ephemeral database copy

• package changes onto the ephemeral database

• drop the ephemeral database

• And then SKIP the final deploy of the ephemeral database changes to the dev database.

• a boolean flag to turn off final deploy during packager to be able to skip final deployment after packaging, so our customer can package files and skip deployment to any DB

• by default is false and we make a final deploy, which is a default packager behaviour

See: Use the Required deployPackager.properties File

Changes to the Project Repository and datical.project

Follow these steps to implement the ephemeral database configuration for your workflow.

Create an Ephemeral dbDef

An ephemeral dbDef must be created in the datical.project file to instruct Liquibase which database should be cloned for the ephemeral copy and to set the connection string information. Because this database is transient by definition and only exists during the Deploy Packager operation, it is not eligible on a Liquibase pipeline to have Status run against it.

Methods for creating the Ephemeral dbDef

In order to create a dbDef without including the dbDef on an existing pipeline Liquibase recommends using ONE of the following methods:

1. Create the dbDef using the Liquibase Enterprise GUI and then delete the dbDef from the pipeline.

2. Include the ephemeral dbDef on the Project Creation Script but do not include the dbDef on any pipelines.

3. Manually copy the dbDef from an existing dbDef in the pipeline.

Follow the instructions below based on the desired method.

Method 1: Create the dbDef with the Liquibase Enterprise GUI

1. Create the dbDef using instructions from Creating a Liquibase Enterprise project Using the Liquibase Enterprise GUI

2. Ensure the Name of the database is <dbDef_to_be_cloned>_EPHEMERAL. This can be DEV, QA, STAGE, or PROD.

   Image Removed

3. Enter your Connection Settings.

   1. If you are using a TNSNAMES/LDAP connection, see the section below Using TNSNAMES/LDAP with Oracle Ephemeral Database.

   2. If you are using a BASIC connection Service Name should be ORCLCDB or the name of the container that should house the ephemeral copy. (See Backup and Restore Package Methods section before for more information.)

      1. You will need to enter your container name in the Hostname text box. This entry will depend on if you are using an internal or external backup and restore method. (See Backup and Restore Package Methods section before for more information.)
         

      2. The username and password should be the ORCLCDB user created in step 1. Create ORCLCDB SYSOPER User above.

         Image Removed

4. Contexts and Labels

   1. Contexts should be left blank or set to the same context as the dbDef to be cloned

   2. Labels should generally be set

      1. To the name of the pipeline that houses the dbDef to be cloned

      2. Or use the same pattern that is in use for the dbDef to be cloned

5. After creating the dbDef, remove the Step from the Pipeline by selecting Remove Step

   Image Removed

6. Do not select the box to delete the Deployment Step from the Deployment Plan

   Image Removed

Method 2: Create the dbDef with the Project Creation Script

1. Create the project using the instructions from Creating a Liquibase Enterprise Using the Project Creation Script (project_creator.groovy)

2. Include the ephemeral database on the ProjectNameOracle.dbdefs.tsv.txt. Use name <dbDef_to_be_cloned>_EPHEMERAL

3. Do not reference the ephemeral database as part of any pipeline on ProjectNameOracle.pipelines.tsv.txt

Method 3: Manually create the dbDef by copying from existing dbDef in datical.project

If you are manually copying the dbDef in the datical.project file from the dbDef to be cloned

1. If the password is present in the file, please note this value is Base64 encoded. If modifying this value, the new value will also need to be Base64 encoded.

2. Clear out the dbDefsId for the new _EPHEMERAL dbDef.

   Code Block
   <dbDefs xsi:type="dbproject:OracleDbDef" name="DEV_EPHEMERAL" driver="oracle.jdbc.OracleDriver" hostname="cs-oracledb.liquibase.net" port="1521" username="c##liquibase_ephemeral_user" password="bGlxdWliYXNlX3VzZXI=" labels="current" dbDefsId="" serviceName="ORCLCDB" enableCompression="false" rowsPerBatch="10000"/>

3. Do a Test Connection to another database in the pipeline to populate the dbDefsId field before checking into source control.

Using TNSNAMES/LDAP with Oracle Ephemeral Database

Learn more about traditional LDAP connections here: LDAP Connection to an Oracle Database

If a customer uses TNS names in dbDef, then customer should prepare a TNS name with _eph postfix him self for the ephemeral copy which we are going to create, as in most cases we are not able to modify TNS files or they can be even stored on remote servers (e.g. OID). So if customer’s dbDefs look like that

<dbDefs xsi:type="dbproject:OracleDbDef" name="DEV" hostname="localhost" driver="oracle.jdbc.OracleDriver" tnsName="dev" />
<dbDefs xsi:type="dbproject:OracleDbDef" name="DEV_EPHEMERAL" driver="oracle.jdbc.OracleDriver" tnsName="admin_sysoper" />

then customer should also add dev_eph entry in TNS config pointing to a PDB which we are going to create. To determine service name or SID for the PDB which this TNS will point to, use the next logic:

1. If you provided value for oracleEphemeralCopyName , then it will be it

2. If you didn’t provide value for oracleEphemeralCopyName, but provided it for oracleEphemeralSourceName - it will be oracleEphemeralCopyName value + _eph postfix, e.g. if oracleEphemeralCopyName is bucket_01, then your value will be bucket_01_eph

3. If you didn’t provide any of these values, just add _eph postfix to your source PDB name, e.g if your source PDB name is bucket_01, then your value will be bucket_01_eph

This is tnsnames.ora file example that contains dev alias for DEV DbDef, dev_eph alias which customer needs to create, and admin_sysoper alias for DEV_EPHEMERAL DbDef:

dev =
(DESCRIPTION =
  (ADDRESS =
     (PROTOCOL = TCP)
     (HOST = 172.17.0.3)
     (PORT = 1521)
  )
  (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = bucket_01)
  )
)


dev_eph =
(DESCRIPTION =
  (ADDRESS =
     (PROTOCOL = TCP)
     (HOST = 172.17.0.3)
     (PORT = 1521)
  )
  (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = bucket_01_eph)
  )
)

admin_sysoper =
(DESCRIPTION =
  (ADDRESS =
     (PROTOCOL = TCP)
     (HOST = 172.17.0.3)
     (PORT = 1521)
  )
  (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = ORCLCDB)
  )
)
 USING '(DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=172.18.0.2)(PORT=1521))
            (CONNECT_DATA=(SERVICE_NAME=ORCLCDB))
        )';

TNS option
Also there is a possibility to use TNS name during link creation, e.g.

CREATE DATABASE LINK liquibase_db_link
 CONNECT TO c##liquibase_ephemeral_source IDENTIFIED BY password
 USING TNS_NAME;

To check if database link has been created correctly please run query below, if you don’t get errors - database link should be correct.

SELECT * FROM dual@liquibase_db_link;

4. Optional: Only for TNSNAMES/LDAP Users
Use TNSNAMES/LDAP with Oracle Ephemeral Database

Learn more about traditional LDAP connections here: LDAP Connection to an Oracle Database

If you use TNS names in your dbDef, then you should prepare a TNS name with _eph postfix for the ephemeral copy because Liquibase cannot modify TNS files.

If this is what your dbDefs look like:

<dbDefs xsi:type="dbproject:OracleDbDef" name="DEV" hostname="localhost" driver="oracle.jdbc.OracleDriver" tnsName="dev" />
<dbDefs xsi:type="dbproject:OracleDbDef" name="DEV_EPHEMERAL" driver="oracle.jdbc.OracleDriver" tnsName="admin_sysoper" />

Then you should add a dev_eph entry in your TNS configuration that points to a PDB. To determine the service name or SID for the PDB which this TNS will point to, use one of the following methods depending on your situation:

• If you provided a value for oracleEphemeralCopyName , then this value will be what you add to your TNS configuration.

• If you didn’t provide a value for oracleEphemeralCopyName, but provided it for oracleEphemeralSourceName - it will be the oracleEphemeralCopyName value + _eph postfix.

• If you didn’t provide any of these values, add _eph postfix to your source PDB name.

• If your source PDB name is bucket_01, then your value will be bucket_01_eph

If you have a dev entry in your TNS file, then you should create an alias for the dev_eph entry which points to the ephemeral copy (admin_sysoper is just an example entry for a connection pointing to ORCLCDB.)

Example tnsnames.ora file:

dev =
(DESCRIPTION =
  (ADDRESS =
     (PROTOCOL = TCP)
     (HOST = 172.17.0.3)
     (PORT = 1521)
  )
  (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = bucket_01)
  )
)

dev_eph =
(DESCRIPTION =
  (ADDRESS =
     (PROTOCOL = TCP)
     (HOST = 172.17.0.3)
     (PORT = 1521)
  )
  (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = bucket_01_eph)
  )
)

admin_sysoper =
(DESCRIPTION =
  (ADDRESS =
     (PROTOCOL = TCP)
     (HOST = 172.17.0.3)
     (PORT = 1521)
  )
  (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = ORCLCDB)
  )
)

5. Methods for creating the Ephemeral dbDef

An ephemeral dbDef must be created in the datical.project file to instruct Liquibase how to connect to the cloned ephemeral copy. Because this database is transient by definition and only exists during the Deploy Packager operation, you are not able to run Status against it.

In order to create a dbDef without including the dbDef on an existing pipeline Liquibase recommends using ONE of the following methods:

• Method 1: Create the dbDef using the Liquibase Enterprise GUI and then delete the dbDef from the pipeline.

• Method 2: Include the ephemeral dbDef on the Project Creation Script but do not include the dbDef on any pipelines.

• Method 3: Manually copy the dbDef from an existing dbDef in the pipeline.

Follow the instructions below based on the desired method.

Method 1: Create the dbDef with the Liquibase Enterprise GUI

1. Create the dbDef using instructions from Creating a Liquibase Enterprise project Using the Liquibase Enterprise GUI

2. Ensure the Name of the database is <dbDef_to_be_cloned>_EPHEMERAL. The <dbDef to be cloned> needs to be the first database in the pipeline.

   Image Added

3. Enter your Connection Settings.

   1. If you are using a TNSNAMES/LDAP connection, see the section above Use TNSNAMES/LDAP with Oracle Ephemeral Database.

   2. If you are using a BASIC connection, the Service Name is ORCLCDB or the name of the container that houses the ephemeral copy. (See Backup and Restore Package Methods section before for more information.)

      1. Enter your container name in the Hostname text box. This entry depends on if you are using an internal or external backup and restore method. (See Backup and Restore Package Methods section before for more information.)
         

      2. The username and password are the ORCLCDB user created in step 2. Create ORCLCDB SYSOPER User above.

         Image Added

4. Contexts and Labels

   1. Contexts are left blank or set to the same context as the dbDef to be cloned.

   2. Set your Labels to either of these:

      1. To the name of the pipeline that houses the dbDef to be cloned

      2. Or use the same pattern that is in use for the dbDef to be cloned

5. After creating the dbDef, remove the Step from the Pipeline by selecting Remove Step.

   Image Added

6. Do not select the box to delete the Deployment Step from the Deployment Plan

   Image Added

Method 2: Create the dbDef with the Project Creation Script

1. Create the project using the instructions from Creating a Liquibase Enterprise Using the Project Creation Script (project_creator.groovy)

2. Include the ephemeral database on the ProjectNameOracle.dbdefs.tsv.txt.
   Use name: <dbDef_to_be_cloned>_EPHEMERAL

Method 3: Manually create the dbDef by copying from the existing dbDef in datical.project

If you are manually copying the dbDef in the datical.project file from the dbDef to be cloned:

1. If the password is present in the file, please note this value is Base64 encoded. If modifying this value, the new value will also need to be Base64 encoded.

2. Clear out the dbDefsId for the new _EPHEMERAL dbDef.

   Code Block
   <dbDefs xsi:type="dbproject:OracleDbDef" name="DEV_EPHEMERAL" driver="oracle.jdbc.OracleDriver" hostname="cs-oracledb.liquibase.net" port="1521" username="c##liquibase_ephemeral_user" password="bGlxdWliYXNlX3VzZXI=" labels="current" dbDefsId="" serviceName="ORCLCDB" enableCompression="false" rowsPerBatch="10000"/>

3. Do a Test Connection to another database in the pipeline or sync the project to the DMC to populate the dbDefsId field before checking the datical.project changes into source control.

6. Optional Step: Associated deployPackager.properties

These are packaging properties available specifically for the ephemeral database packaging method. They are optional if you would like to override the default values.

See: Use the Required deployPackager.properties File

Troubleshooting:

Potential grants issues

• If oracleEphemeralSourceName packager property is not specified

...

• Liquibase attempts to get the PDB name from

...

• three different tables on the

...

• database. These tables are v$pdbs, dba_data_files

...

• , and v$parameter. If the requested grants are in place as listed on Multi-Schema Projects - Oracle Roles and Permissions

...

• , the PDB name can be located. Both GRANT SELECT ANY DICTIONARY TO DATICAL_ROLE; and GRANT IMP_FULL_DATABASE TO DATICAL_PACKAGER_ROLE;

...

• provide the proper permissions to query the required tables

...

• . If these permissions have not been granted, in order to use ephemeral databaseBackupRestoreMethod, permissions to query v$pdbs

...

• , dba_data_files

...

• and v$parameter tables must be granted.

...

Use Oracle Managed Files (OMF) with Oracle Ephemeral Database

If you are using OMF with BackupRestoreOracleEphemeralExternalPdb, then OMF mode should be the same on both instances of the Oracle database.