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:
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:
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:
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:
s|@localhost:1521:corpus|@$DB_HOST:$DB_PORT/${ORACLE_SID:-corpus}|
where $ORACLE_SID = XEPDB1
.