Setting up Oracle

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

Creating The Database

Follow the steps below to set up a Oracle database:

Setting up the database and user

Create a new database by using the Oracle database configuration assistant (dbca) or manually.
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.
After configuring the .ora files, start the Oracle instance using the following command:
```
$ sudo /etc/init.d/oracle-xe restart
```
Connect to Oracle using SQL*Plus as SYSDBA as follows:
$ ./$<ORACLE_HOME>/config/scripts/sqlplus.sh sysadm/password as SYSDBA
Connect to the instance with the username and password using the following command:
```
$ connect
```

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

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

For example:

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

Copying the JDBC driver

Copy the Oracle JDBC libraries (for example: <ORACLE_HOME/jdbc/lib/ojdbc14.jar) to the <PRODUCT_HOME>/repository/components/lib/ directory.
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'"

The value of this property should be the GMT difference of the country.

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

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.

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:
```
SQL> @$<PRODUCT_HOME>/dbscripts/oracle.sql
```
To create tables for the product specific databases, run the below command:
```
SQL> @$<PRODUCT_HOME>/dbscripts/<PRODUCT_NAME>/oracle.sql
```

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.

Replace these settings with your own custom values:
```
<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.
The default port for Oracle is 1521.