Collaboration for Business Console - Preparing Oracle Database

Knowledge base
,
1

To use Collaboration with an Oracle database management system, you need to create an application user and an empty schema for Collaboration data.

This section describes the steps you need to perform to configure Collaboration with Business Con-sole for the first time on Windows. If you are upgrading from an earlier version, see Upgrading Collaboration (from previous version).

If you are using a Linux system, use the .sh files instead of the .bat files.

1. Pre-requisites #

To configure an Oracle database, you need the following components:

  • Working Oracle database system.
  • ojdbc6.jar database driver. You can download this driver from the Oracle website to a directory on a physical drive (not a mounted drive) of your choice.
  • SQL scripts and all additional files. These scripts and files are located in the installation directory at <Software AG_directory>\ecp\server\scripts\oracle. For example, if you installed Collaboration in c:\SoftwareAG directory, you can find the files in C:\SoftwareAG\ecp\server\scripts\oracle directory.
  • Oracle SQL*PLUS tool available on the system on which you are running the scripts.

2. Configuring envset.bat #

Before you create application user and create an empty schema by using scripts, perform these steps to configure the settings used by the scripts.

1.    Edit the envset.bat file to specify the connection data for the Oracle instance. Locate the following lines in the file and replace the text specified in angled brackets with the settings appropriate for your environment:

SET CIP_ORA_BIN_PATH=<Path to sqlplus.exe>SET TARGET_HOST=<OracleHostnameOrIP> SET TARGET_PORT=<OraclePort> SET TARGET_SERVICE_NAME=<OracleServiceName>

Replace <Path to sqlplus.exe> with the path to where you installed the Oracle sqlplus tool, <OracleHostnameOrIP> with the fully qualified hostname (or IP address) of your Oracle server, <OraclePort> with the port of the oracle DBMS, and <OracleServiceName> with the service name of your Oracle DBMS.

For example, if SQL*Plus is installed in C:\oraclexe\app\oracle\product\11.2.0\server\bin folder, Oracle DBMS is running on myoracle.example.com machine on port 1521, and Oracle DBMS uses the XE service, you would edit the file as shown below: 

SET CIP_ORA_BIN_PATH=C:\oraclexe\app\oracle\product\11.2.0\server\bin\
SET TARGET_HOST= myoracle.example.com
SET TARGET_PORT=1521
SET TARGET_SERVICE_NAME=XE2.
 2. Edit the following lines to change the name of the application user and password: 
SET CIP_APP_USER=<AppUserName>
SET CIP_APP_PWD=<AppUserPwd>
SET CIP_TENANT_SCHEMA_PWD=<SchemaUserPwd>

For example, if the username and password of the application user is “ecpUser” and “TopSecret”, and the schema user password is “AnotherSecret”, you would edit the file as shown below: 

SET CIP_APP_USER=ecpuser
SET CIP_APP_PWD=TopSecret
SET CIP_TENANT_SCHEMA_PWD=AnotherSecret3.

3. Edit these lines to specify the DBA user you intend to use to create the application user and the DBA user’s password: 

SET CIP_INSTALL_USER=<AdminUsername>
SET CIP_INSTALL_PWD=<AdminUserPwd>

4.    Edit these lines to specify the data table space name and the temporary table space: 

SET CIP_TS_DATA=<DataTableSpaceName>
SET CIP_TS_TEMP=<TempTableSpaceName>

After you save the changes, you can create the application user as described in Creating Application User.

3. Creating Application User #

Collaboration uses an application user to connect to the Oracle database. This step is required only if you are configuring the DBMS for use with Collaboration for the first time, this step is not required when you are upgrading from a previous version.

1.    Run the prepare_database.bat script. 2.    Use the CIP_APP_USER and CIP_APP_PWD parameters in the inside envset.bat file to specify the user name and password for the application users. See Configuring envset.bat. 3.    Specify the user name and password when you register the Oracle DBMS in the Collaboration register. See Registering the Oracle DBMS with Collaboration.

4. Creating Database Schema #

Collaboration’s database schema user name by default is “ecp_default”. It is recommended that you retain “ecp_default” as the schema user name.

Run the cip_create_schema_for_tenant.bat script and pass the schema user name as parameter: 

cip_create_schema_for_tenant.bat <schemaUserName>

If you do not change the schema user name as recommended, run the script as shown below: 

cip_create_schema_for_tenant.bat ecp_default

The password for the schema user is specified in the CIP_TENANT_SCHEMA_PWD parameter in the envset.bat file.

5. Adding Oracle JDBC Driver #

1.    Copy the JDBC driver JAR file to a local physical drive, not a mounted volume.

2.    Click Start > All Programs > Software AG > Administration > Collaboration Cloud Controller 9.8 to start the Collaboration Cloud Controller.

3.    At the ACC+ localhost> prompt, type the command below: 

enhance ecp with commonsClasspath local file <pathToJDBCDriverFile>

where <pathToJDBCDriverFile> is the actual path where you copied the JDBC driver JAR file.

For example, if the driver JAR is located in c:\temp\ojdbc6.jar, the command would be: 

enhance ecp with commonsClasspath local file c:\temp\ojdbc6.jar

If the command is successful, you get this message: 

Successfully executed Local Enhancement c:/temp/ojdbc6.jar for instance ecp.

6. Registering Oracle DBMS with Collaboration #

To inform Collaboration about the DBMS to use, you need to register it in the Collaboration service registry.

1.    Click Start > All Programs > Software AG > Administration > Collaboration Cloud Controller 9.8 to start the Collaboration Cloud Controller.

2.    At the ACC+ localhost> prompt, type the command: 

start zoo

3.    Type command: 

list

You get an output similar to: 

ACC+ localhost>list
On node localhost 4 runnables are installed.
zoo        : STARTED (com.aris.runnables.zookeeper-run-prod-98.0.0)
ecp        : STOPPED (com.aris.runnables.ecp-run-prod-98.0.0)
elastic      : STOPPED (com.aris.runnables.elasticsearch-run-prod-98.0.0)
umcadmin     : STOPPED (com.aris.umcadmin.y-umcadmin-run-prod-98.0.0)

If the “zoo” component is not shown as STARTED, wait a little longer, and retype “list” until zoo is shown as STARTED.

4.    To register the DBMS, type command: 

register external service DB_ECP
       host=<OracleHostnameOrIP>
       url=    
"jdbc:oracle:thin:@<OracleHostnameOrIP>:<OraclePort>:<OracleServiceName>" 
       driverClassName=oracle.jdbc.OracleDriver jmxEnabled=true
       maxActive=100  maxIdle=15 logAbandoned=true
       rollbackOnReturn=true maxWait=10000
       removeAbandoned=false defaultAutoCommit=false
       username=<AppUserName>
       password=<AppUserPwd>

Where <OracleHostnameOrIP>, <OraclePort>, <OracleServiceName>, <AppUserName>, and <AppUserPwd> represent the hostname (or IP address), port, service name of the Oracle DBMS,  name of the application user, and the password of the application user. 

For example, if your Oracle DBMS is running on myoracle.example.com machine on port 1521, uses the XE service, application user name is “ecpuser”, and password is “TopSecret”, the command to register the DBMS would be: 

register external service DB_ECP
       host=myoracle.example.com
       url=
       "jdbc:oracle:thin:@myoracle.example.com:1521:xe"
       driverClassName=oracle.jdbc.OracleDriver jmxEnabled=true
       maxActive=100  maxIdle=15 logAbandoned=true
       rollbackOnReturn=true maxWait=10000
       removeAbandoned=false defaultAutoCommit=false
       username= ecpuser
       password=TopSecret

Note: Type the command in one single continuous line.


If the command completes successfully, you should see an output similar to:


{{{

New external service DB_ECP registered with ID db-ecp0000000000 via node localhost

5.    Note the service ID returned.

6.    Run the following command to specify Collaboration the user schema to be used to connect to Oracle DBMS: 

assign tenant default to service <ServiceID> schema=<tenantSchemaName>

Where <ServiceId> is the ID of the registered service, <tenantSchemaName> is the name of the Oracle schema.

For example, if the ID of the registered service is db-ecp0000000000, and the default schema user name is “ecp_default”, the command would be: 

assign tenant default to service db-ecp0000000000 schema=”ecp_default”

If the command succeeds, you should see an output similar to: 

Successfully assigned tenant default to service db-ecp0000000000.

7.    Type “exit” to exit the ACC console.

See also: #