Page Comparison

The following sections describe how to replace the default H2 databases with Oracle:

Creating the database tables

You can create the database tables manually by running a script or automatically by using a startup parameter.

Using the script

You can create database tables manually by executing the following scripts. 

1. You can create the database tables manually by logging in to the created database and running the following scripts in SQL*Plus:

• To create tables for the registry and user manager database, run the below command:

  Code Block
  language powershell
  SQL> @$<PRODUCT_HOME>/dbscripts/oracle.sql

• To create tables for the identity databases, use the below script:

  Code Block
  language powershell
  SQL> @$<PRODUCT_HOME>/dbscripts/identity/oracle.sql

• To create tables for the storage databases, use the below script:

  Code Block
  language powershell
  SQL> @$<PRODUCT_HOME>/dbscripts/storage/oracle.sql

2. Start the Carbon instance as follows:

   $ ./$<PRODUCT_HOME>/bin/wso2server.sh

Using a startup parameter

You can create database tables automatically when starting the product for the first time by using the -Dsetup parameter as follows:

• For Windows users:
  

  $<PRODUCT_HOME>/bin/wso2server.bat -Dsetup

• For Linux users:
  

  $ ./$<PRODUCT_HOME>/bin/wso2server.sh -Dsetup

Creating A Datasource For The Database

After creating the database, you create a datasource to point to it in the following files:

...

Edit the default datasource configuration defined in the master-datasources.xml file, located in the <PRODUCT_HOME>/repository/conf/datasources/  directory, as follows. The database configurations in registry.xml and user-mgt.xml refer to this datasource.

<datasource>
     <name>WSO2_CARBON_DB</name>
     <description>The datasource used for registry and user manager</description>
     <jndiConfig>
           <name>jdbc/WSO2CarbonDB</name>
     </jndiConfig>
     <definition type="RDBMS">
           <configuration>
               <url>jdbc:oracle:thin:@SERVER_NAME:PORT/DB_NAME</url>
               <username>regadmin</username>
               <password>regadmin</password>
               <driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>
               <maxActive>80</maxActive>
               <maxWait>60000</maxWait>
               <minIdle>5</minIdle>
               <testOnBorrow>true</testOnBorrow>
               <validationQuery>SELECT 1 FROM DUAL</validationQuery>
               <validationInterval>30000</validationInterval>
           </configuration>
     </definition>
</datasource>

Following are the database configuration options:

• url - The URL of the database.
• username - The name of the database user.
• password - The password of the database user.
• driverClassName  - The class name of the database driver.
• maxActive - The maximum number of active connections that can be allocated from this pool at the same time, or enter a negative value for no limit.
• maxWait - The maximum number of milliseconds that the pool will wait (when there are no available connections) for a connection to be returned before throwing an exception. You can enter zero or a negative value to wait indefinitely.
• minIdle - The minimum number of active connections that can remain idle in the pool without extra ones being created, or enter zero to create none.

...

Replace the default values in the datasource configurations of the following files with your custom values accordingly, to point to the created registry and user manager, identity and storage databases.

...

Creating The Database

Follow the steps below to set up a Oracle database:

Setting up the database and user

1. Create a new database by using the Oracle database configuration assistant (dbca) or manually.

2. Make the necessary changes in the Oracle tnsnames.ora and listner.ora files in order to define addresses of the databases for establishing connections to the newly created database.

3. After configuring the .ora files, start the Oracle instance using the following command:

   $ sudo /etc/init.d/oracle-xe restart

4. Connect to Oracle using SQL*Plus as SYSDBA as follows:

   $ ./$<ORACLE_HOME>/config/scripts/sqlplus.sh sysadm/password as SYSDBA

5. Connect to the instance with the username and password using the following command:

   $ connect

6. As SYSDBA, create a database user and grant privileges to the user as shown below:

   Code Block
   language powershell
   Create user USER_NAME identified by PASSWORD account unlock; grant connect to USER_NAME; grant create session, dba to USER_NAME; commit;

   For example:

   Image Removed

7. Exit from the SQL*Plus session by executing the quit command.

Copying the JDBC driver

1. Copy the Oracle JDBC libraries (for example: <ORACLE_HOME/jdbc/lib/ojdbc14.jar) to the <PRODUCT_HOME>/repository/components/lib/ directory.
2. Remove the old database driver from the <PRODUCT_HOME>/repository/components/dropins/ directory.

...

When using the ojdbc6.jar with WSO2 servers, there is a possibility of throwing a timezone region not found error. To overcome this issue, set the Java property as follows:

export JAVA_OPTS="-Duser.timezone='+05:30'"

...

If it is necessary to set this property permanently, define it inside wso2server.sh as a new JAVA_OPT property.

Changing the identity/storage databases

The topics above show how to change the WSO2_CARBON_DB, which is used to store registry and user manager information. If you set up a separate database for identity/storage related data, the instructions are the same. In summary:

1. Add the datasource to the master-datasources.xml file. 

2. Create the database tables using the following scripts: 

   For the identity database	Use <PRODUCT_HOME>/dbscripts/identity/oracle.sql
   For the storage database	Use <PRODUCT_HOME>/dbscripts/storage/oracle.sql