Problem

According to the SecureTransport Installation Guide, to install SecureTransport with external Oracle Database we should have an account created with the specific privileges and the Oracle DB should have specific tweaks.

Once a new database is created using the Oracle Database Configuration Assistance, how to prepare it for SecureTransport?

Resolution

All configurations and tweaks must be executed with the Oracle Database's "system" account.

Use the system account to run the commands

Create tablespaces

AUTOEXTEND should be enabled. The size might be modified for each environment.

SQL> create tablespace ST_DATA datafile 'st_data.dbf' size 500M autoextend on;
SQL> create tablespace ST_FILETRACKING datafile 'st_filetracking.dbf' size 10000M autoextend on;
SQL> create tablespace ST_SERVERLOG datafile 'st_serverlog.dbf' size 10000M autoextend on;

Add user and set quotas

<STUSER> should be replaced with the username. <PASSWORD> should be replaced with the actual password.

SQL> create user <STUSER> identified by <PASSWORD>;
SQL> alter user <STUSER> default tablespace ST_DATA;
SQL> alter user <STUSER> quota unlimited on ST_DATA;
SQL> alter user <STUSER> quota unlimited on ST_FILETRACKING;
SQL> alter user <STUSER> quota unlimited on ST_SERVERLOG;

The credentials created in this step should be specified during the SecureTransport installation.

Grant privileges to the ST user

SQL> grant create operator to <STUSER>;
SQL> grant create procedure to <STUSER>;
SQL> grant create sequence to <STUSER>;
SQL> grant create table to <STUSER>;
SQL> grant create session to <STUSER>;
SQL> grant execute on DBMS_LOB to <STUSER>;

Database tweaks required for SecureTransport

These tweaks are global and apply to the whole database, not just the SecureTransport user.

SQL> alter system set sessions=1000 scope=spfile;
SQL> alter system set processes=1000 scope=spfile;
SQL> alter system set open_cursors=1000 scope=spfile;
SQL> alter system set memory_target=1000M scope=spfile;

Restart of the Oracle Database is required in order to apply the tweaks.