Easily Connect WSO2 API Manager With Oracle Database
Using Oracle SQL Developer Tool
WSO2 API Manager provides the capability to create, publish and manage your APIs, and a persistence layer to store all the information related to APIs. There are three main databases used within the API Manager.
- APIM DB: All the API related information
- Registry DB: Configurations and artifacts repository
- User DB: Users/Roles and permission related information
The default database that WSO2 API Manager uses is a file-based H2 database (WSO2CARBON_DB.h2.db) located in the <PRODUCT_HOME>/repository/database folder. This embedded H2 database is suitable for development and testing. For the production environment, however, it is recommended to use an industry-standard RDBMS such as Oracle, PostgreSQL, MySQL, MS SQL, etc. You can use the scripts provided with the WSO2 product to install and configure several other types of relational databases, including MySQL, IBM DB2, Oracle, and more.
Here, I am going to discuss how to configure the WSO2 API Manager with the Oracle database using a few simple steps.
Prerequisites:
- API Manager distribution (latest)
- Running Oracle DB Instance (12c or later)
- SQL Developer (latest)
- Oracle JDBC Driver
Step 01
Set up the Oracle database either in your local machine or in a remote server. For that, you can follow the steps in ‘How to set up an Oracle 12c database’ and configure a database with SID.
Then start the Oracle SQL Developer tool. This tool will help us to do the database tasks easily using a developer-friendly IDE.
Now create a new database connection providing your username/password, hostname, running port and the SID.
Connected to the database successfully, will bring you to an empty worksheet.
Step 02
Now we can create the schemas and tables which are required for the API Manager runtime.
In the opened worksheet, type the path for the relevant DB scripts that are being provided with the API Manager product. For this example, I’m using the same schema for all three DBs, but if you want, you can use different schemas for each database.
- WSO2AM_DB: <APIM_HOME>/dbscripts/apimgt/oracle.sql
- WSO2REG_DB and WSO2UM_DB: <APIM_HOME>/dbscripts/oracle.sql
To execute the script, include the @ sign as a prefix to the path and click “run statement”.
Since I have used the same schema, all the tables will be created in the same schema.
Now all the configurations in the Oracle database side are done.
Step 03
Copy the Oracle JDBC driver file to <APIM_HOME>/repository/components/lib directory.
Open master-datasources.xml located in <APIM_HOME>/repository/conf/datasources path.
Default master-datasources file come up with configurations for the h2 database. Change the sections we want to use in an external database as below to work with the Oracle databases.
Note: In all the below configurations, the Same JDBC URL and username/password were given since I have created all the tables in the same schema.
<datasources-configuration>...<datasource><name>WSO2AM_DB</name><description>The datasource used for API Manager database</description><jndiConfig><name>jdbc/WSO2AM_DB</name></jndiConfig><definition type="RDBMS"><configuration><url>jdbc:oracle:thin:yasas@host_ip:port/ora12c</url><username>yasas</username><password>yasas</password><driverClassName>oracle.jdbc.driver.OracleDriver
</driverClassName><maxActive>50</maxActive><maxWait>60000</maxWait><minIdle>5</minIdle><testOnBorrow>true</testOnBorrow><validationQuery>SELECT 1 FROM DUAL</validationQuery><validationInterval>30000</validationInterval><defaultAutoCommit>false</defaultAutoCommit></configuration></definition></datasource><datasource><name>WSO2_REGISTRY_DB</name><description>The datasource used for registry</description><jndiConfig><name>jdbc/WSO2RegistryDB</name></jndiConfig><definition type="RDBMS"><configuration><url>jdbc:oracle:thin:yasas@host_ip:port/ora12c</url><username>yasas</username><password>yasas</password><driverClassName>oracle.jdbc.driver.OracleDriver
</driverClassName><maxActive>50</maxActive><maxWait>60000</maxWait><minIdle>5</minIdle><testOnBorrow>true</testOnBorrow><validationQuery>SELECT 1 FROM DUAL</validationQuery><validationInterval>30000</validationInterval><defaultAutoCommit>false</defaultAutoCommit></configuration></definition></datasource><datasource><name>WSO2_USER_DB</name><description>The datasource used for user manager</description><jndiConfig><name>jdbc/WSO2UserDB</name></jndiConfig><definition type="RDBMS"><configuration><url>jdbc:oracle:thin:yasas@host_ip:port/ora12c</url><username>yasas</username><password>yasas</password><driverClassName>oracle.jdbc.driver.OracleDriver
</driverClassName><maxActive>50</maxActive><maxWait>60000</maxWait><minIdle>5</minIdle><testOnBorrow>true</testOnBorrow><validationQuery>SELECT 1 FROM DUAL</validationQuery><validationInterval>30000</validationInterval><defaultAutoCommit>false</defaultAutoCommit></configuration></definition></datasource>...</datasources-configuration>
Step 04
Since we are changing the databases used for registry and user management, we have to update two more files. Here, we are going to discuss how to configure the registry XML file according to the new database configurations. We need the <name> of <jndiConfig> element we gave in the master-datasources. According to the above configurations above, it is ‘jdbc/WSO2RegistryDB’
Next, add the below code segment after the existing dbConfig element. Please note that; we have to keep the existing elements as it is.
<dbConfig name=”govregistry”><dataSource>jdbc/WSO2RegistryDB</dataSource></dbConfig><remoteInstance url=”https://localhost"><id>gov</id><dbConfig>govregistry</dbConfig><readOnly>false</readOnly><enableCache>true</enableCache><registryRoot>/</registryRoot></remoteInstance><mount path=”/_system/governance” overwrite=”true”><instanceId>gov</instanceId><targetPath>/_system/governance</targetPath></mount><mount path=”/_system/config” overwrite=”true”><instanceId>gov</instanceId><targetPath>/_system/config</targetPath></mount>
Finally, change the user-mgt.xml. Update the existing dataSource property and set the new JNDI name. In my case, it is ‘jdbc/WSO2UserDB’.
e.g.: change the
<Property name=”dataSource”>jdbc/WSO2CarbonDB</Property>to
<Property name=”dataSource”>jdbc/WSO2UserDB</Property>Now we have configured the WSO2 API Manager with Oracle DB. To test the configurations, you can start the server with a normal command and check the log.
Cheers for no errors in the log file..!!😊
