Setting up Oracle for Unblu

If you’ve set the Oracle COMPATIBLE parameter, make sure it’s set to 12.2 or higher before you start working through the instructions below.

To create the unblu database and users on Oracle, run the following commands:

Listing 1. Create the Unblu schema and permissions on Oracle

ALTER SESSION SET "_ORACLE_SCRIPT" = TRUE;
CREATE USER UNBLU_DBO IDENTIFIED BY dbo_secret;
CREATE USER UNBLU IDENTIFIED BY secret;

GRANT CONNECT, CREATE TABLE TO UNBLU_DBO;
GRANT ALTER ANY TABLE TO UNBLU_DBO;
GRANT RESOURCE TO UNBLU_DBO;
GRANT UNLIMITED TABLESPACE TO UNBLU_DBO;

GRANT CONNECT TO UNBLU;

The script sets up and configures two users:

An admin user called UNBLU_DBO for Liquibase upgrades
A non-admin user called `UNBLU`for all other Unblu functionality

If you use Oracle as a multitenant container database (CDB), you must grant the permissions for the pluggable database (PDB), not the CDB.

To use native database locks during Liquibase upgrades, grant the UNBLU_DBO user the following privilege:

Listing 2. Privilege for Unblu database admin user needed to use Liquibase native locks with Oracle

GRANT EXECUTE ON SYS.DBMS_LOCK to UNBLU_DBO;

If you don’t grant this privilege, Liquibase uses the DATABASECHANGELOGLOCK table rather than native database locks to track which update is running. You then have to update the table manually to release the lock if the Liquibase upgrade doesn’t run through successfully.

During the Liquibase migration, the UNBLU user is silently granted the same rights as UNBLU_DBO on all tables. This is done to ensure that the Unblu Collaboration Server doesn’t fail to start up because UNBLU hasn’t yet been granted access to all of the tables they need.

Listing 3. Unblu Oracle configuration

com.unblu.storage.database.platform=org.eclipse.persistence.platform.database.OraclePlatform (1)
com.unblu.storage.database.driver=oracle.jdbc.driver.OracleDriver
com.unblu.storage.database.url=jdbc\:oracle\:thin\:@<server>\:1521\:xe (2)
com.unblu.storage.database.user=unblu
com.unblu.storage.database.password=<user-pwd> (3)
com.unblu.storage.database.schema=UNBLU_DBO
com.unblu.storage.database.liquibaseSchema=UNBLU_DBO
com.unblu.storage.database.adminUser=unblu_dbo
com.unblu.storage.database.adminPassword=<admin-pwd> (4)

1	Use `Oracle12Platform` for Oracle 12 and later.
2	Replace `<server>` with your database hostname or IP address. Change the SID or service name if required.
3	Replace `<user-pwd>` with the password of the `unblu` user.
4	Replace `<admin-pwd>` with the password of the `unblu_admin` user.

Setting up Oracle for Unblu

See also