oracle-pdb-rule

This Maven module is intended to ease the provisioning of an Oracle test database for integration or system tests based on JUnit.

It makes use of Oracles Pluggable Database Feature to create a new pluggable database (PDB) for JUnit tests and remove it again when the JVM shuts down. This approach is interesting, as other approaches like spinning up a database using Docker and the Testcontainers framework, that work well for other databases like Postgres, does not scale for Oracle due to its high resource requirements and long startup and initialization times (> 10 minutes) for provided Oracle Database Docker Images.

The provisioning is plugged into the test execution using a JUnit Rule. Each instance of the class bayern.meyer.junit.rules.OraclePdb creates a PDB with a random name once when the rule is first triggered. The PDB is kept until JVM gracefully shuts down. It's removed using a JVM shutdown hook by this module than. Generated PDB name, username and password are provided by corresponding getters of this rule class.

Tools like Flyway, Liquibase or custom scripts can be used to run DDL scripts within this empty PDB, tools like DbUnit, DbSetup or other ways to initialize the test data.

An Oracle container database (CDB) being capable of provisioning PDBs can e.g. be provisioned once using the Oracle Database Docker Image preparation scripts for Oracle 19c.

Usage

• Maven dependency
• Declaring the test rule
• Accessing the database
• Configuring the CDB access
• Sharing a database between tests
• Troubleshooting
• Background information

Maven dependency

Add this module as dependency to your project

<dependency>
    <groupId>bayern.meyer</groupId>
    <artifactId>oracle-pdb-rule</artifactId>
    <version>0.3</version>
    <scope>test</scope>
</dependency>

Declaring the test rule

To create a new pluggable database before running tests add a @ClassRule

public class AnIntegrationTest {
    @ClassRule
    public static OraclePdb oraclePdb = new OraclePdb();

    @Test
    public void aTest() { /*...*/ }
}

Accessing the database

The provisioned pluggable database can be accessed using provided connection URL, username and password.

public class AnIntegrationTest {
    @ClassRule
    public static OraclePdb oraclePdb = new OraclePdb();

    @BeforeClass
    public static void prepareDatabaseServer() throws SQLException {
        OracleDataSource pdbAdminDataSource = new OracleDataSource();
        pdbAdminDataSource.setURL(oraclePdb.getPdbJdbcUrl());
        pdbAdminDataSource.setUser(oraclePdb.getPdbAdminUser());
        pdbAdminDataSource.setPassword(oraclePdb.getPdbAdminPassword());

        try (Connection connection = pdbAdminDataSource.getConnection(); Statement statement = connection.createStatement()) {
            statement.execute("...");
        }
    }

    @Test
    public void aTest() { /*...*/ }
}

Besides a OracleDataSource can be obtained directly.

public class AnIntegrationTest {
    @ClassRule
    public static OraclePdb oraclePdb = new OraclePdb();

    @BeforeClass
    public static void prepareDatabaseServer() throws SQLException {
        final OracleDataSource pdbAdminDataSource = oraclePdb.getPdbDataSource();

        try (Connection connection = pdbAdminDataSource.getConnection(); Statement statement = connection.createStatement()) {
            statement.execute("...");
        }
    }

    @Test
    public void aTest() { /*...*/ }
}

Configuring the CDB access

The CDB access can be configured programmatically. See JavaDoc for public methods for more details.

public class AnIntegrationTest {
    @ClassRule
    public static OraclePdb oraclePdb = new OraclePdb(new OraclePdbConfiguration.Builder().withCdbJdbcUrl("jdbc:oracle:thin:@localhost:1521/ORCLCDB").withCdbUsername("sys as sysdba").withCdbPassword("oracle").build());

    @Test
    public void aTest() { /*...*/ }
}

Besides CDB access and other setting can be configured using system properties. If both, programmatic and system properties are configured the system properties take precedence.

mvn -DCDB_PASSWORD=MySecretPassword test

Following properties can be configured using -D

• CDB_USERNAME (sys as sysdba)
• CDB_PASSWORD (oracle)
• CDB_HOST (localhost)
• CDB_PORT (1521)
• CDB_NAME (ORCLCDB)
• CDB_DOMAIN () - suffix to add to the CDB name to build the jdbc connection URL
• CDB_JDBC_URL (jdbc:oracle:thin:@${CDB_HOST}:${CDB_PORT}/${CDB_NAME}${CDB_DOMAIN})
• ORADATA_FOLDER (/opt/oracle/oradata)
• PDBSEED_NAME (pdbseed)
• PDB_SEED_PATH (${ORADATA}/${CDBNAME}/${PDBSEED_NAME}/)
• PDB_BASE_PATH (${ORADATA}/${CDBNAME}/)
• CREATE_PDB (true) - Use this property to skip PDB creation and use the database credentials provided for the CDB access to access the test database. This mode can be helpful to test locally without spinning up a new database over and over again.
• KEEP_PDB (false) - Use this property to skip PDB deletion after the tests have finished. This mode can be helpful to locally verify the database content after tests have been executed.
• CREATE_PDB_EAGER (false) - Set this property to true to create the PDB already then the OraclePdb object gets created; otherwise the PDB will be created in the test rules apply method just before the test execution
• GRANT_UNLIMITED_TABLESPACE (false) - Set this property to true to execute GRANT UNLIMITED TABLESPACE TO <PDB_ADMIN_USER> right after PDB creation

Sharing a database between tests

A new pluggable database is created for every @Rule or @ClassRule referenced instance of class bayern.meyer.junit.rules.OraclePdb. To share a database a shared instance of the rule class can be used.

public class PdbProvider {
    public static OraclePdb oraclePdb = new OraclePdb();
}

public class AnIntegrationTest {
    @ClassRule
    public static OraclePdb oraclePdb = PdbProvider.oraclePdb;

    @Test
    public void aTest() { /*...*/ }
}

public class AnotherIntegrationTest {
    @ClassRule
    public static OraclePdb oraclePdb = PdbProvider.oraclePdb;

    @Test
    public void anotherTest() { /*...*/ }
}

Troubleshooting

SLF is used as logging API. Helpful information is logged on info and debug level using the logger bayern.meyer.junit.rules.OraclePdb.

Background information

Following SQL statements are executed for creating a pluggable database and determining its service name

ALTER SESSION SET CONTAINER = CDB$ROOT
CREATE PLUGGABLE DATABASE ${pdbName} ADMIN USER ${pdbAdminUser} IDENTIFIED BY ${pdbAdminPassword} ROLES=(DBA) FILE_NAME_CONVERT=('${pdbSeedPath}','${pdbPath}')
ALTER PLUGGABLE DATABASE ${pdbName} OPEN
ALTER SESSION SET CONTAINER = ${pdbName}
SELECT sys_context('userenv','service_name') FROM dual

Following SQL statement is executed to grant unlimited tablespace (if GRANT_UNLIMITED_TABLESPACE is set to true)

GRANT UNLIMITED TABLESPACE TO ${pdbAdminUser}

Following SQL statements are executed for removing a pluggable database

ALTER SESSION SET CONTAINER = CDB$ROOT
ALTER PLUGGABLE DATABASE ${pdbName} CLOSE IMMEDIATE
DROP PLUGGABLE DATABASE ${pdbName} INCLUDING DATAFILES