Friday, June 27, 2014

Writing a simple class mediator and deploying it in WSO2 ESB

Recently I was playing around with WSO2 ESB for my own pet project. In particular, I wanted to write my own mediator to do some custom message processing on inbound messages. I am pleasantly surprised by the ease with which you can write and deploy a class mediator in WSO2 ESB. I just want to give you a sense of it by showing you an example mediator written for an already existing client and service shipped WSO2 ESB binaries. (The real mediator that I am writing involves quite some coding as it is written to process any messages going through the ESB. For this post, let's focus on the basics by using existing samples.) Specifically, StockQuoteService and stockquote clients are utilized.

First we create the mediator class by extending AbstractMediator:

package org.example.mediator;

import org.apache.synapse.MessageContext;
import org.apache.synapse.Mediator;
import org.apache.synapse.mediators.AbstractMediator;
import org.apache.axiom.soap.SOAPBody;
import org.apache.axiom.soap.SOAPFactory;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import javax.xml.namespace.QName;

public class MyMediator extends AbstractMediator {

    private static final Log log = LogFactory.getLog(MyMediator.class);

    public MyMediator(){}

    public boolean mediate(MessageContext mc) {
       //get the SOAP body from message context
    SOAPBody soapBody = mc.getEnvelope().getBody();
       //now you can modify the SOAP body as you wish

        return true;

    public String getType() {
        return null;

    public void setTraceState(int traceState) {
        this.traceState = traceState;

    public int getTraceState() {
        return 0;

Create a base folder, say mymed, and place MyMediator class in mymed/src/main/java/org/example/mediator. You may create other non-mediator classes and place according to the package name.

Save the following pom.xml file in mymed/ folder.
<project xmlns:xsi="" xmlns=" xsi:schemalocation="">
           <name>Maven Incubating Repository</name>
           <name>Apache Maven2 Repository</name>



Inside mymed/ folder run "mvn clean install". This will create org.example.mediator-1.0.jar in mymed/target folder.

Copy this jar to WSO2-ESB-HOME/repository/components/lib.

Now run the ESB server:
cd WSO2-ESB-HOME/bin
Run (or .bat in windows)

Now you should be able to log into the ESB console by typing https://localhost:9443/carbon.

As explained here, deploy the StockQuoteService in the axis2server provided with the ESB.

cd WSO2-ESB-HOME/samples/axis2server/src/SimpleStockQuoteService
Run ant

This will create the SimpleStockQuoteService.aar and deploy it in the axis2server's repository.

Now start the axis2server:
cd WSO2-ESB-HOME/samples/axis2server
Run (or .bat in windows)

In order to make sure everything is fine, let's run the client shipped with the ESB.
cd WSO2-ESB-HOME/samples/axis2Client
Run "ant stockquote -Daddurl=http://localhost:9000/services/SimpleStockQuoteService -Dtrpurl=http://localhost:8280 -Dsymbol=IBM -Dmode=quote"

This should return a random stock quote price.

Now, as explained here, we create a proxy service name StockQuoteProxy to mediate requests to SimpleStockQuoteService.

In the in sequence of this proxy service, we add our mediator inline as shown below. If you cannot load the mediator class successfully, there is something wrong with your mediator. You will have to go back and check your code.

Now execute the stockquote client again, you will see the modifications that the mediator does to the SOAP message are reflected in the SimpleStockQuoteService's received message. That demonstrates the successful deployment of the class mediator!

You may add another mediator in the out sequence to modify the reply messages from the service to the client following similar steps as above as well

Tuesday, June 17, 2014

Oracle database vs. Instance

Most novices tend to get confused by these two terms. Are they referring to the same thing? To add to the confusion, the term database instance is used  as well. Database and instance are not the same thing. They are two different things, but they are related.

An instance is a collection of background processes and a shared memory area.

A database is a collection of files in the OS (data files, control files, temp files and redo files).

So how are they related and how do they co-exist?

- An instance can exist without a database. What? You can start an Oracle instance but not have access to any database files? Yes, you can. In fact, this is how you create a database. First you start an instance and then you create a database. This is exactly what happens when you install Oracle software only option. There is no database to mount and open [mount and open? why don't you just open? why are there two steps? I will explain it a little later in this post.]. Very first thing you do is to create one after connecting to an instance.

- An instance can access only one database. In Oracle terms, you can mount only one database per instance.

- A database can be accessed by multiple instances. Why would you want to do that? This provides scalability by letting multiple processes to process user requests in parallel and also provides higher availability by continuing to server even if a few instance are down. This is exactly what Oracle RAC (Real Application Cluster) installations do!

Still confused? Let me go through an example to show these concepts. I am using the most popular version of Oracle database here (11g). (Hint: stay tuned for the latest database 12c posts)

You can install Oracle either software only or software + database. To explain the concept of instance and database, I have performed a software only installation. That is, no default database is created at the time of installation.

First we connect to an idle instance:

bob@example> cd $ORACLE_HOME/bin
bob@example> sqlplus / as sysdba

SQL*Plus: Release - Production on Tue Jun 17 10:32:58 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to an idle instance.

"Connected to an idle instance" usually indicates that you connected "as sysdba" and the instance you connected to is not started. This gives the privilege to start the database. You will see a process by the name oraclemysid created at this point. Instance name is selected as oracle + SID (System Identifier) name. You specify the SID at the time of installation.

bob@example> ps -ef | grep mysid
bob  15680 15679  0 10:32 ?        00:00:00 oraclemysid (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

Now let's try the following command at the SQL prompt:

SQL> startup nomount
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/scratch/bob/app/bob/product/11.1.0/db_1/dbs/initmysid.ora'

Ooops, errors! Before I explain the above error. Let's go through main startup modes as I promised at the beginning of the post.

There are three main modes of startups:
1. startup nomount
2. startup mount
3. startup open (or simply startup)

startup nomount - you start an instance without mounting a database. Why would you do that? Well, if you don't have a database you have no other option but to start the instance without mounting. You would also do this when you want to create a new database or re-create control files.

startup mount (if you have already issued startup nomount, you can type alter database mount; to do the same) - you start an instance and mount the database, but leave the database closed. But why would you simply mount and still have the database closed? Isn't it practically same as no mount? No, this mode is different. Even though this mode does not allow general database activities, it does allow to perform certain DBA tasks on the database such as backing up or archieving.

startup open (if you have already issued startup mount, you can type "alter database open;" to do the same) - This opens the database with full access. There are ways to restrict access to database by providing additional parameters with these commands.

Now let's get to the error. We got the error because Oracle cannot locate the parameter file. Unless you have specified the PFILE parameter with the startup nomount command, Oracle look for the default parameter file $ORACLE_HOME/dbs/initmysid.ora  (that is init + SID name + .ora).

Let's create one. It should have the db_name parameter to identify the database that we want to associate with the instance.


Create the following file and add the following name. orcl is the global database name given at the time of installation.

vim initmysid.ora

db_name = orcl

Now connect to an idle instance and do startup nomount again. It should succeed.

bob@example> cd $ORACLE_HOME/bin
bob@example> sqlplus / as sysdba
SQL> startup nomount
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2142976 bytes
Variable Size             159386880 bytes
Database Buffers           50331648 bytes
Redo Buffers                5296128 bytes

Now let see what processes it created.

bob@example> ps -ef | grep mysid
bob  16097     1  0 10:41 ?        00:00:00 ora_pmon_mysid
bob  16099     1  0 10:41 ?        00:00:00 ora_vktm_mysid
bob  16103     1  0 10:41 ?        00:00:00 ora_diag_mysid
bob  16105     1  0 10:41 ?        00:00:00 ora_dbrm_mysid
bob  16107     1  0 10:41 ?        00:00:00 ora_psp0_mysid
bob  16111     1  0 10:41 ?        00:00:00 ora_dia0_mysid
bob  16113     1  0 10:41 ?        00:00:00 ora_mman_mysid
bob  16115     1  0 10:41 ?        00:00:00 ora_dbw0_mysid
bob  16117     1  0 10:41 ?        00:00:00 ora_lgwr_mysid
bob  16119     1  0 10:41 ?        00:00:00 ora_ckpt_mysid
bob  16121     1  0 10:41 ?        00:00:00 ora_smon_mysid
bob  16123     1  0 10:41 ?        00:00:00 ora_reco_mysid
bob  16125     1  0 10:41 ?        00:00:00 ora_mmon_mysid
bob  16127     1  0 10:41 ?        00:00:00 ora_mmnl_mysid
bob  16128 15679  0 10:41 ?        00:00:00 oraclemysid (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

As you can see it creates a set of background processes such as PMON, MMAN, SMON, DBWn, LGWR, etc. We will go over Oracle background processes in a future post. For now, it is suffice to know that there are many background processes involved to get an Oracle instance up and running. This also creates a shared memory area in your OS (not shown here).

We still don't have a database. Next, we are creating one.

SQL> create database;

Database created.

The above statement creates and opens the database in read/write mode.

SQL> select open_mode from v$database;


Note that we used most simplest create database command. It creates the files at default locations. You can specify where to store data, control, log, redo files.

You can query the following views to find the default locations in our case:

SQL> select name from v$controlfile;


SQL> select member from v$logfile;


SQL> select name from v$datafile;


I hope you got a good hang of what an Oracle database and an instance are.

PS: In order master advanced concepts, know the basics (the foundation) very well. The goal of these posts is to give you a good foundation so that you can figure out advanced techniques yourself.

Sunday, June 15, 2014

Oracle Database Access Control Model

In school, we learn that relational databases use RBAC (Role Based Access Control) model whereas operating systems use DAC (Discretionary Access Control) model. But is it really true about relational databases? Well, it is partially true. Modern databases utilize a combination of RBAC and DAC models. To complicate further, they may provide MAC (Mandatory Access Control) features as well. For example, in Oracle database, OLS (Oracle Label Security) and DV (Database Vault) options are providing MAC features. In this blog post, I am going to describe the access control model used by core Oracle database (optional components will be discussed in a future blog. - why we really need them and what purpose they serve, etc.)

Before we get into the access control model, let's quickly go over some basics.

Privileges and Roles:
- A privilege is a right to run a particular type of SQL statement on the database. For example, a privilege to connect to the database, a privilege to read a table, a privilege to execute a procedure, etc.
- A role is a group privileges and possibly consisting of other roles. When a user is granted a role, the user can exercise all the privileges associated with the role. There are some differences between a directly granted privilege vs the same privilege granted via a role. We are leaving this discussion to a future post. Usually roles are created to manage privileges for a user group or an application.

Let's look at privileges in some more details. There are two types of privileges
1. System privileges
They are quite powerful and granted with due diligence. A system privilege is a right to perform a particular action (e.g. CREATE SESSION system privilege) or an action of any schema object of a particular type (e.g. SELECT ANY TABLE system privilege). Note that there are some exceptions. For example. SELECT ANY TABLE does not gives the rights to access dictionary tables.

2. Schema object privileges (or simply object privileges)
They are much less powerful than system privileges. An object privilege is a right to perform a particular action on a particular schema object (e.g. emp table of scoot). From the point of view of security, it is better to grant object privileges over system privileges as users are granted only the privileges that they need for the job. However, from the management point of view, it could be a nightmare to manage if there thousands of object privileges. In practice, most organizations prefer to use system privileges over object privileges due to ease of management.

With the above background knowledge, let us now go through the RBAC + DAC model that Oracle database employs.

The DAC model gives the owner of an object the ability to grant or revoke access to other subjects (users or roles). For example, user bob has a table foo. Bob can grant select and insert access on table foo to another user alice and grant only select on foo to a role bar. Later, Bob can revoke access on table foo from either alice or the role bar. 

Notice that not every one can grant access to others on a given object. If a user has the privilege to exercise the right on an object (e.g. SELECT on table), it does not mean that he/she can grant that privilege to others. Let's take the above example again. Unless, Alice is granted select/insert on with GRANT OPTION, Alice cannot grant these privileges on to others. 

The RBAC model gives a user access to an object based on its assigned roles in the database system. Usually roles are defined based on job functions. For example, Tom is an employee in the HR department and, hence, assigned the role hr_emp. The system defines that the role hr_emp has access to HR tables in the database. Therefore, Tom can access all HR tables. However, Tim, who is not an employee in the HR department and not assigned the role hr_emp, cannot access HR tables. Similar to GRANT OPTION for privileges, a user can grant a role granted to him/her only if the user is granted that role with ADMIN OPTION. This capability gives the DAC enforcement over RBAC.

Friday, June 13, 2014

Parent and Child Cursors in Oracle Database

What are parent and child cursor in Oracle database server? Why do we have cursors? This post is going to explain them in simple terms.

When we say a cursor in database, the iterator over a result set of a database query comes into mind. True, at the client side, cursor is an iterator. However, at the server side, a cursor is a data structure that the database server creates for SQL query.

At the server, each SQL query received go through a life cycle. It is important to have a rough idea of what this life cycle is in order to better understand cursors. Let me summarize what happens when you submit a query to Oracle database. It goes through the following phases.

1. Open
2. Parse - syntax analysis, semantic analysis, privilege check, save
3. Bind - think of as passing input parameters for a function
4. Define - how do you want the output
5. Execute
6. Fetch

Remember SQL is a language. Similar to other languages, we need a compiler, optimzier and code generator. It is expensive to go through all of these operations each time when a query is submitted. If we can save the compiled objects, if a same or similar query is re-submitted instead of going through compile-optimize-code-generate phases we can simply re-use these compiled objects. This is exactly what cursors try to do.

During the parse phase, after successful syntax analysis, semantic analysis, and privilege check, Oracle database server allocates a data structure for the statement in UGA memory belonging to the session if a matching structure is not already available. This structure is called a cursor. A cursor holds the parsed statement and other information for processing.

For each SQL statement, Oracle actually associates it with two cursors; parent and child cursors. Why two cursors? For the same SQL statement, there could be differences such as two different schemas, different bind values, different literals, etc. The parent holds the SQL statement and each child holds information related to the differences.

You can access parent cursors from V$SQLAREA view and child cursors from V$SQL. Let me explain these cursors through an example.

SQL> conn / as sysdba
SQL> create user u1 identified by p1;
SQL> create user u2 identified by p2;
SQL> grant dba to u1, u2;
SQL> conn u1/p1
SQL> create table t(c int);
SQL> conn u2/p2
SQL> create table t(c int)
-- Notice that we have the same in both u1 and u2's schemas.

-- Remove saved cursors (for demonstration purposes)
SQL> conn / as sysdba
SQL> alter system flush shared_pool; 

SQL> conn u1/p1
SQL> select * from t;
-- The above statement creates a parent and child cursor. 
SQL> select sql_id from v$sqlarea where sql_text like 'select * from t%';


SQL> select child_number from v$sql where sql_id = '27uhu2q2xuu7r';


-- Now u1 executing the same statement again
SQL> select * from t;

If you look at v$sqlarea and v$sql, you won't find any new entries, but rather, Oracle is reusing the previous cursors as nothing has changed.

SQL> conn u2/p2
-- Now u2 executing the same statement
SQL> select * from t;

SQL> select sql_id from v$sqlarea where sql_text like 'select * from t%';


SQL> select child_number from v$sql where sql_id = '27uhu2q2xuu7r';


Since it is the exact same statement that u1 executed, all statements share the same parent cursor. However, notice that their are two child cursors. Due to various reasons, child cursors may not be shared. In this specific case, even though the SQL statement is exactly the same, they are executed on two different schemas. Hence, two child cursors. v$sql_shared_cursor view has information regarding as to why cursors cannot be shared. The following picks a few reasons from the above view:

SQL> select child_number, bind_mismatch, optimizer_mode_mismatch, literal_mismatch, auth_check_mismatch from v$sql_shared_cursor where sql_id = '27uhu2q2xuu7r';

------------ - - - -
           0 N N N N
           1 N N N Y

As you can see, in this case, two different cursors are created as authentication checks mismatch.

Notice that both have the same exact query plan:
SQL> select * from table( dbms_xplan.display_cursor('27uhu2q2xuu7r', 0 ) );

SQL_ID  27uhu2q2xuu7r, child number 0
select * from t1

Plan hash value: 3688435342

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |

SQL> select * from table( dbms_xplan.display_cursor('27uhu2q2xuu7r', 1 ) );

SQL_ID  27uhu2q2xuu7r, child number 1
select * from t1

Plan hash value: 3688435342

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| T1   |     1 |    13 |     2   (0)| 00:00:01 |

What if u1 execute the following two exactly the same statements?
select * from t;
select * from u1.t;

SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select * from %t';

SQL_ID          SQL_TEXT
b0pqkh4zgw6xt   select * from u1.t1
27uhu2q2xuu7r   select * from t1

It creates two parent cursors. But why? it does not make sense to have two as they are the same. Wait! if you look carefully they are semantically different. In the first statement, t is resolved be a table in the executor's schema whereas in the second statement, t is always in u1's schema.

What if u1 execute the following two statements? Will we have two parent cursors?
SQL> conn u1/p1
SQL> select * from t where c = 5;
SQL> select * from t where c = 10;

SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select * from %t';

SQL_ID          SQL_TEXT
9gkwnw4g49hbt   select * from t1 where c1 = :"SYS_B_0"
3n9xkpf0ja5b6   select * from t1 where c1 = 10

We still have two parents. Why? The answer depends on the cursor_sharing parameter.

SQL> show parameter cursor_sharing;

NAME             TYPE        VALUE
---------------- ----------- ------
cursor_sharing   string      EXACT

The default cursor_sharing criteria is EXACT. For each different SQL statement, it creates a new parent cursor. Since the above two SQL texts are different, we have two parent cursors. Can't we change this behavior and save some more processing by sharing the parent cursor in situation like this? Yes, we can. We can use FORCE or SIMILAR cursor_sharing criteria to instruct Oracle to share cursors in such situations.

-- Let's change the cursor_sharing parameter to force.
SQL> conn / as sysdba
SQL> alter system set cursor_sharing = force;

Now u1 executing the above two statements again.
SQL> conn u1/p1
SQL> select * from t where c = 5;
SQL> select * from t where c = 10;

SQL> select sql_id, sql_text from v$sqlarea where sql_text like 'select * from %t';

SQL_ID          SQL_TEXT
9gkwnw4g49hbt   select * from t1 where c1 = :"SYS_B_0"

Now there is only one parent cursor for both statement even though they do not have exact SQL text. They only differ by a literal. Notice that it is replaced by a bind variable and both generate the same query plan.