Pointing the Default Databases to Oracle 12c— WSO2 products

In order to change the default database to the Oracle database, you can follow the below steps. Here I am taking the user management database as an example database.

  1. Log to the Oracle server, then you need to create users which use to point your database. For that, you can run the below CREATE USER command.

CREATE USER <USER_NAME> IDENTIFIED BY <USER_PASSWORD>;

eg: CREATE USER C##WSO2UM IDENTIFIED BY WSO2UM;

Here it is simply creating a C##WSO2UM account that is IDENTIFIED or authenticated by the specified password.

2. Now you need to grant specific privileges for the created user to do database transactions.

  • Typically, you’ll first want to assign privileges to the user through attaching the account to various roles, starting with the CONNECT role[1].

GRANT CONNECT TO <USER_NAME>;

eg: GRANT CONNECT TO C##WSO2UM;

  • Next, you’ll want to ensure the user has privileges to actually connect to the database and create a session using the GRANT CREATE SESSION. We’ll also combine that with all privileges using GRANT ANY PRIVILEGES[1].

GRANT CREATE SESSION GRANT ANY PRIVILEGE TO <USER_NAME>;

eg: GRANT CREATE SESSION GRANT ANY PRIVILEGE TO books_admin;

  • We also need to ensure our new user has disk space allocated in the system to actually create or modify tables and data, so we’ll GRANT TABLESPACE like so[1].

GRANT UNLIMITED TABLESPACE TO <USER_NAME>;

eg: GRANT UNLIMITED TABLESPACE TO C##WSO2UM;

  • Then you need to provide table privileges for that particular user like Insert, delete, update, select .. etc. For that, you can provide the privileges as below.

GRANT SELECT, INSERT, UPDATE, DELETE TO<USER_NAME>;

eg: GRANT SELECT, INSERT, UPDATE, DELETE TO C##WSO2UM;

Or else you can provide all table privileges as follows.

GRANT ALL PRIVILEGES TO <USER_NAME>;

eg: GRANT ALL PRIVILEGES TO C##WSO2UM;

  • Then you need to configure the data source at the master-datasource.xml file which located in the <PRODUCT_HOME>/repository/conf/datasources directory as the below.

<datasource>
<name>WSO2UM_DB</name>
<description>The datasource used for API Manager database</description>
<jndiConfig>
<name>jdbc/WSO2UM_DB</name>
</jndiConfig>
<definition type=”RDBMS”>
<configuration>
<url>jdbc:oracle:thin:@<IP_ADDRESS_OF_ORCALE_SERVER>:1521/ora12c</url>
<username>C##WSO2UM</username>
<password>WSO2UM</password><! — idetfied password of your user →
<defaultAutoCommit>true</defaultAutoCommit>
<driverClassName>oracle.jdbc.driver.OracleDriver</driverClassName>
<maxActive>50</maxActive>
<maxWait>60000</maxWait>
<testOnBorrow>true</testOnBorrow>
<validationQuery>SELECT 1 FROM DUAL</validationQuery>
<validationInterval>30000</validationInterval>
</configuration>
</definition>
</datasource>

Here the <validationQuery> must be configured as SELECT 1 FROM DUAL instead of SELECT 1 like orther database (eg: MySQL). Ortherwise it will throw exceptions like belows.

ERROR {org.wso2.carbon.throttle.service.impl.BlockConditionDBUtil} -  Error while executing SQL {org.wso2.carbon.throttle.service.impl.BlockConditionDBUtil}
java.sql.SQLException: Data source is not configured properly.
.....
Caused by: java.sql.SQLException: Failed to validate a newly established connection.
  • Need to add the oracle JDBC driver (oracle.jdbc.driver.OracleDriver) to <PRODUCT_HOME>/repository/components/lib directory
  • Now you can restart the server with -Dsetup mode after doing required changes in other files as well[2], then the required tables will create while starting the server in the database.

[1]. https://chartio.com/resources/tutorials/how-to-create-a-user-and-grant-permissions-in-oracle/

[2]. https://docs.wso2.com/display/AM210/Changing+the+Default+API-M+Databases

--

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Nadee Poornima

Nadee Poornima

Senior Software Engineer at WSO2

More from Medium

Application Frameworks — 2,3,4

CS373 Spring 2022: Sahran Hashim

Code faster with lombok

CS373 Spring 2022: Kristina Zhou