Page Comparison

Include Pageshared:Setting up Oracle (V1)shared:Setting up Oracle (V1)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

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 Added

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.

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 product specific databases, run the below command:

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

• Excerpt
  hidden true
  NOTE TO WRITERS: Add the following content only i f it is applicable to your product. To create tables for the identity databases, use the below script: Code Block language powershell SQL> @$<PRODUCT_HOME>/dbscripts/identity/oracle.sql

• Excerpt
  hidden true
  NOTE TO WRITERS: Add the following content only i f it is applicable to your product. 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:

1. 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.

   Info
   Replace these settings with your own custom values:

   Code Block
   language xml
   <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.

   Info
   The default port for Oracle is 1521.

2. Excerpt
   hidden true
   NOTE TO WRITERS: Add the following content as the second step, and replace <PRODUCT_HOME>/repository/conf/<PRODUCT_NAME>/<PRODUCT_NAME>.xml, with the correct folder path of your product. If you changed the product specific database, add the datasource configuration in the  <PRODUCT_HOME>/repository/conf/<PRODUCT_NAME>/<PRODUCT_NAME>.xml file as well.  Info This step is not relevant if you changed the registry and user manager database.

3. Excerpt
   hidden true
   NOTE TO WRITERS: Add the following content only i f it is applicable to your product. If you changed the databases used for identity and storage, copy the WSO2_CARBON_DB datasource configuration for each of them in < PRODUCT_HOME>/repository/conf/datasources/master-datasources.xml file and replace its default values with your custom values.

4. Excerpt
   hidden true
   NOTE TO WRITERS: Add the following content only i f it is applicable to your product. 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. <PRODUCT_HOME>/repository/conf/identity.xml <PRODUCT_HOME>/repository/conf/user-mgt.xml <PRODUCT_HOME>/repository/conf/registry.xml