Extended data types for Oracle 18

Set up a fresh Oracle 18 database. The steps described here are required to work with the censhare Server, independently from the Oracle database installation type.

Introduction

With Oracle 12c, the extended data type feature is introduced. To use an Oracle database with the censhare Server, this feature is required. By default, the extended data type feature is disabled. Depending on the Oracle database version, the enabling steps vary. For more information on Oracle 12c, see Oracle Extended Data Types.

As of Oracle 18, the database concept changed, and also the setup of the extended data type. Following, we explain how to configure a Oracle 18 database after bootstrap for the first time.

Prerequisites

Oracle database version 18 or later.
Admin privileges to connect as SYSDBA to the database.

Overview

As of Oracle 18, in a multitenancy database there are two container types.

Note: The Oracle container concept is different from Docker containers.

Container Data Base (CDB$ROOT)

The CDB is the main database. It serves as the main container and is only intended for administration purposes. Only database admins should access it. For security reasons, the database actions are restricted in the CDB (Container Database). It is not intended for user data.

Note: The Container Data Base (CDB) is different from the internal censhare database (cdb)!

Pluggable Data Base (`PDB$SEED` and `XEPDB1`)

The PDB is intended for production data. This container can use extended features. XEPDB1 is the name of the first PDB instance that is created by default. XEPDB1 is used for the censhare data. PDB$SEED is the name for the seed PDB that serves as a template to create new PDBs. PDB$SEED is provided by the system.

The asset_feature table in censhare has the column table that needs the extended data type for the size field. This requires the following steps in the Oracle database:

Extend the max string size in XEPDB1
Run @?/rdbms/admin/utl32k in XEPDB1
Create Tablespace/User corpus in XEPDB1

These steps must be executed when Oracle database bootstraps for the first time. The steps must be finished before the first bootstrap of the censhare Server.

Set the Extended Data Type

Connect as an admin to the database and execute the following commands:

SQL

alter system set max_string_size=EXTENDED scope=spfile;

shutdown immediate;

startup upgrade;

@?/rdbms/admin/utl32k.sql

shutdown immediate;

startup;

alter pluggable database pdb$seed open upgrade;

alter session set container=PDB$SEED;

@?/rdbms/admin/utl32k.sql

alter session set container=cdb$root;

alter pluggable database pdb$seed close;

alter pluggable database pdb$seed open read only;

alter pluggable database XEPDB1 open upgrade;

alter session set container=XEPDB1;

@?/rdbms/admin/utl32k.sql

alter session set container=cdb$root;

alter pluggable database XEPDB1 close;

alter pluggable database XEPDB1 open;

Create tablespaces and system user

Prepare the database to be used with censhare. The first step is to create the censhare system user corpus and the required database tablespaces. log in as an admin and execute the following commands:

SQL

alter session set "_ORACLE_SCRIPT"=true;

alter session set container=XEPDB1;

CREATE TABLESPACE "CORPUS"

    LOGGING

    DATAFILE '/opt/oracle/oradata/XE/CORPUS.dbf'

    SIZE 100M

    AUTOEXTEND ON NEXT 100M

    EXTENT MANAGEMENT LOCAL

    SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE "CORPUS_LOG"

    LOGGING

    DATAFILE '/opt/oracle/oradata/XE/CORPUS_LOG.dbf'

    SIZE 100M

    AUTOEXTEND ON NEXT 100M

    EXTENT MANAGEMENT LOCAL

    SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER corpus

    IDENTIFIED BY "corpus"

    CONTAINER=CURRENT

    DEFAULT TABLESPACE "CORPUS"

    TEMPORARY TABLESPACE "TEMP"

    ACCOUNT UNLOCK;

GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE, CREATE VIEW TO "CORPUS" CONTAINER=CURRENT;

GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO "CORPUS" CONTAINER=CURRENT;

ALTER USER "CORPUS" QUOTA UNLIMITED ON "CORPUS" CONTAINER=CURRENT;

Important: With CONTAINER=CURRENT, the commands are applied to the XEPDB1 container of the Oracle database. Otherwise, the commands would be applied to the CDB$ROOT container, which is only for management purposes of the database.

Create the database schemas

The following script adds the database schema with tables, constraints and other definitions that are required for censhare. Log in as an admin and execute the following command:

SQL

sqlplus64 $DB_USER/$DB_PASSWORD@$DB_HOST:$DB_PORT/${ORACLE_SID:-corpus} < $(pwd)/css/database/create-db.sql

Adapt the connection URL

Adapt the main database configuration file on the censhare Server in ~/cscs/app/services/database/config.$CSS_ID.xml.

CSS_ID is the name of the database, for example config.master.xml. In the configuration in the <connection> XML tag adapt the connection-url attribute:

Replace corpus with XEPDB1

For example: connection-url="jdbc:oracle:thin:@localhost:1521:corpus" changes to connection-url="jdbc:oracle:thin:@localhost:1521:XEPDB1"

Using sed (stream editor), you can apply the following for automation:

BASH

s|@localhost:1521:corpus|@$DB_HOST:$DB_PORT/${ORACLE_SID:-corpus}|

where $ORACLE_SID = XEPDB1.