Creating an ORACLE Database for Settings Persistency

You will now create an ORACLE user that will be used for storing Settings Persistency.
For this task, you will need the following:

Input placeholder used in this Doc task

Signification

Source
<database_name> name of the database that you want to use for storing settings ORACLE Administrator
<dba_username> database administrator user name ORACLE Administrator
<dba_password> database administrator user password ORACLE Administrator

 

Output placeholder  used in later Doc task

Signification

Task used in
<database_name> name of the database that you want to use for storing settings Creating the Enterprise Archive File
<settings_username> name of database user and table space corresponding to settings persistency  Creating the Enterprise Archive File
<settings_password> password of database user corresponding to settings persistency  Creating the Enterprise Archive File

Creating ORACLE Database for Settings Persistency

The following steps describe a way to create the database user for Settings persistency.

On a machine where ORACLE database is created

  1. Login to oracle console.

  2. Export the oracle database.
    Windows: set ORACLE_SID=<database_name>.
    Unix: export ORACLE_SID=<database_name>.

  3. Launch sql prompt.
    sqlplus /nolog

  4. Connect to sql session.
    conn <dba_username>/<dba_username> as sysdba

  5. Create the user, table space and assign required roles.

    1. create user <settings_username> identified by <settings_password>;
    2. grant connect, resource to <settings_username>;
    3. CREATE SMALLFILE TABLESPACE "<settings_username>" LOGGING DATAFILE '/home/data/oracle/oradata/database_name/<settings_username>.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
    4. ALTER USER <settings_username> default tablespace <settings_username>;
    5. ALTER USER <settings_username> QUOTA 100M ON <settings_username>;
    6. GRANT UNLIMITED TABLESPACE TO <settings_username>;

For Example

  1. su - oracle
  2. export ORACLE_SID=WASDB
  3. sqlplus /nolog
  4. create user wkr identified by <settings_password>;
  5. grant connect, resource to wkr;
  6. CREATE SMALLFILE TABLESPACE "wkr" LOGGING DATAFILE '/home/data/oracle/oradata/database_name/wkr.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
  7. ALTER USER wkr default tablespace wkr;
  8. ALTER USER wkr QUOTA 100M ON wkr;
  9. GRANT UNLIMITED TABLESPACE TO wkr;

Testing

  1. To test the Settings persistency database, user and password, use the command line tool on the machine where the web application will run on a WebSphere Application Server to run the following command:

sqlplus  <settings_username> / <settings_password>@<database_name>

If the sqlplus command fails to connect to the database with these credentials, check with a database administrator if the <database_name> definition is properly defined on the machine (check files such as tnsnames.ora, etc.), and if the ORACLE database is installed on a remote server, check if the ORACLE port (default 1521) is accessible from the machine to that remote server.