Using an Oracle database server with a single SID for DevTest databases

Products

• CA Application Test
• CA Continuous Application Insight (PathFinder)

Issue/Introduction

Question

I have an Oracle database with a single Service Identifier (SID). How may I use this for both the Enterprise Dashboard and the main DevTest databases?

Answer

In order to achieve this one may do the following:-

1. Create separate table spaces for each database
2. Create an user for each of the databases 
3. Assign the necessary privileges to the user
4. Assign one user to each table space
5. Configure DevTest

In the example that follows the following is assumed - 

• A table space of initial size 10GB will be created for both the Enterprise Dashboard and the DevTest databases - this table space will be allowed to grow as required.
• The DevTest database user will be DTUSER and the associated database schema named DT
• The Enterprise Dashboard user will be EDUSER and the associated database schema named ED
• That the databases will be stored in /db/DevTest

These assumed values should be adjusted to suit your needs.

Steps 1 - 4 are performed using the SQLPLUS tool.

Step 1 - Create separate table spaces for each database

At the prompt within SQLPLUS, issue the following commands to create the table spaces for each of the databases

create tablespace DT datafile '/db/DevTest/DT.dbf'

  2  size 10240m

  3  autoextend on

  4  ;

 

create tablespace ED datafile '/db/DevTest/ED.dbf'

  2  size 10240m

  3  autoextend on

  4  ;

Step 2 - Create an user for each of the databases 

create user DTUSER identified by DTUSERPASSWORD;

create user EDUSER identified by EDUSERPASSWORD;

Step 3 - Assign the necessary privileges to the user

Note: These rights can be reduced to normal user priviliges after Enterprise Dashboard and Registry have first run

grant connect, create table, create sequence, create trigger, create session, dba to DTUSER ;

grant connect, create table, create sequence, create trigger, create session, dba to EDUSER ;

 

Step 4 - Assign one user to each table space

alter user DTUSER default tablespace DT ;

alter user EDUSER default tablespace ED;

Step 5 - Configure DevTest

1. Configure the Enterprise Dashboard

Edit the appropriate configuration file (local.properties for version 8.0 to 9.0, dradis.properties for version 9.1 and later). In the Oracle Database section, provide the following, substituting the values of username, password, dbhost, dbport and SID.

dradis.db.driverClass=oracle.jdbc.driver.OracleDriver

dradis.db.url=jdbc:oracle:thin:@dbhost:dbport:SID

dradis.db.user=EDUSER

dradis.db.password=EDUSERPASSWORD

 

and set 

dradis.db.internal.enabled=false

 

2. Configure the Registry

In the Oracle section of the site.properties, enter the following

lisadb.pool.common.driverClass=oracle.jdbc.driver.OracleDriver

lisadb.pool.common.url=jjdbc:oracle:thin:@dbhost:dbport:SID

lisadb.pool.common.user=DTUSER

lisadb.pool.common.password=DTUSERPASSWORD

 

and set

lisadb.internal.enabled=false

A note on table space sizing.

It may be desirable to allocate a fixed space for each database as opposed to allowing the space to grow. This may be achieved by creating the table space as a set of files, and omitting the autoextend option. For instance, to create a 50GB Enterprise Dashboard table space, one may do the following:

create tablespace ED datafile 

  2 '/db/DevTest/ED_01.dbf' size 10240m,

  3 '/db/DevTest/ED_02.dbf' size 10240m,

  4 '/db/DevTest/ED_03.dbf' size 10240m,

  5 '/db/DevTest/ED_04.dbf' size 10240m,

  6 '/db/DevTest/ED_05.dbf' size 10240m;

 

Additional Information

The CREATE TABLESPACE command https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7003.htm

DevTest requirements for UTF8 database support TEC1631279

 

Environment

Release: LSASVR99000-8.1-LISA-Server
Component: