ORACLE RAC Troubleshooting Best Practices

When we are working with Oracle RAC setup before start the database we want to know weather the Cluster environment service are on line.The below commands are generally used to check the status of Cluster environment.

Checking CRS Status:

The below two commands are generally used to check the status of CRS. The first command lists the status of CRS on the local node where as the other command shows the CRS status across all the nodes in Cluster.

crsctl check crs <<-- for the local node
crsctl check cluster <<-- for remote nodes in the cluster

[grid@rac1 ~]$ crsctl check crs
CRS-4638: Oracle High Availability Services is online
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
For the below command to run, CSS needs to be running on the local node. The "ONLINE" status for remote node says that CSS is running on that node. When CSS is down on the remote node, the status of "OFFLINE" is displayed for that node.

[grid@rac1 ~]$ crsctl check cluster
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online

No Of Nodes configured in Cluster:
The below command can be used to find out the number of nodes registered into the cluster. It also displays the node's Public name, Private name and Virtual name along with their numbers.
[grid@rac1 ~]$ olsnodes -n -i -s
rac1    1       rac1-vip        Active
rac2    2       rac2-vip        Active

[grid@rac1 ~]$ crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.1.0]

[oracle@rac1 ~]$ cluvfy comp crs -n all -verbose

Verifying CRS integrity

Checking CRS integrity...
The Oracle clusterware is healthy on node "rac2"
The Oracle clusterware is healthy on node "rac1"

CRS integrity check passed

Verification of CRS integrity was successful.

Backups & Restore of MySQL Databases Using mysqldump

If you work with MySQL at any level you may need to backup your databases & restore.MySQL is most popular open source RDBMS widely use.MySQL that has always disappointed me  because of there are so many free tools for backing up databases confused when selecting the exact tool.Backups are extremely easy in MySQL to create with mysqldump."mysqldump" is the only backup tool that most MySQL users know and use, and it’s available whatever MySQL.

Database Backup

Backup a single database
 you can also use the shortcuts -u and -p to specify username and password
mysqldump --user=... --password=...   your_database_name >  your_database_name.sql

Backup multiple databases
mysqldump --user=... --password=... --databases db1 db2 > backup.sql

All the databases
mysqldump --user=... --password=... --all-databases > backup.sql

Restoring DB Backup

Created the destination database & then created the users & give appropriate privileges.Restoring is usually done this way:

mysql –u root –p[password] restoredbname < dbname_backup.sql

Watching Content of Embedded H2 Database Through Browser in WSO2 Products

H2 is a RDBMS written in Java.This database can be used in embedded mode,only thing is add the h2*.jar to the class path & do not have any other dependencies.All the WSO2 products are default shipped with H2 database.Graphical interfaces to run SQL commands and display their results. there are H2 Database Query Tool also available.


1. Download a  WSO2 product and extract

2. Open the file "carbon.xml" in the location repository/conf

3. Enable the followe H2DatabaseConfiguration only

<H2DatabaseConfiguration>
        <property name="web" />
        <property name="webPort">8082</property>
        <property name="webAllowOthers" />
        <!--property name="webSSL" />
        <property name="tcp" />
        <property name="tcpPort">9092</property>
        <property name="tcpAllowOthers" />
        <property name="tcpSSL" />
        <property name="pg" />
        <property name="pgPort">5435</property>
        <property name="pgAllowOthers" />
        <property name="trace" />
        <property name="baseDir">${carbon.home}</property-->
    </H2DatabaseConfiguration>
</Server>

In here we do not need to start PG server therefor only enabling
        <property name="web" />
        <property name="webPort">8082</property>
        <property name="webAllowOthers" />
is enough

4. Save the file and close

5. Start the server


So now that If wanted to watch the content of this db browser page should open with the URL http://localhost:8082 to in browser


Login

Select [Generic H2] and JDBC URL : jdbc:h2:<file path to CARBON_HOME>/repository/database/WSO2CARBON_DB
username : wso2carbon
password  : wso2carbon

 You are now logged in & ow h2 database use WSO2 client that can run SQL commands and display their results

How to Convert WSDL to Java Using WSO2 Application Server

WSDL(Web Services Development Language) is an XML document used to describe web services & how to access them.

To convert WSDL to Java in there are tool/command available(EX:-Using Maven,Apache ant). WSDL facilitate to write the web service using the tool and command available.However the WSO2 Application Server support to achieve this substantially without a depth knowledge of java .To develop a  web services two approaches can be used.Java-first & WSDL-first.Further WSDL-first is a thinking design and Java-first coding is an implementation.Hence it is  easy to develop WSDL-first approach.

Therefore we need to install WSO2 AS which is not a big task and steps such as downloading, running the system, accessing service via browser should follow.

1. Download the WSO2 Application Server binary distribution.
2. Extract the zip archive where you want the WSO2 AS installed
3. Set the JAVA_HOME environment variable to your Java installation, and the PATH environment variable to the Java /bin directory.
4. Execute the WSO2 AS start script from the bin folder.(Linux/Unix :sh wso2server.sh/Windows :wso2server.bat)
5. Check your WSO2 AS instance using the URL https://localhost:9443/carbon which will take you to the WSO2 AS Management Console.
6. Login as "admin" using the default password "admin"


There in the user interface you will find the tool WSDL2Java among few other tools like Java2WSDL in the left-hand side.

When you go for WSDL2Java tool it will allow you to enter the relevant path of WSDL file and few other options. You can select them as want and click on "Generate" button.

Then it will automatically download the generated code with stub classes to your local machine and you can place it any where accordingly and carry on developing the client.Further we can write our business logic and then expose the service to the client.


Using this WSDL Converter easily generate WSDL version 2.0 document
by using  existing WSDL 1.1 document

All About TABLESPACES

An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.

Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).

If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDO                           ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT

If you want a new tablespace, you can use the CREATE TABLESPACE ... DATAFILE statement as shown in the following script:
SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME  STATUS          CONTENTS
---------------- --------------- ---------
SYSTEM           ONLINE          PERMANENT
UNDO             ONLINE          UNDO
SYSAUX           ONLINE          PERMANENT
TEMP             ONLINE          TEMPORARY
USERS            ONLINE          PERMANENT
MY_SPACE         ONLINE          PERMANENT

If you have an existing tablespace and you don't want it anymore. You can delete a tablespace by using the DROP TABLESPACE statement as shown in the example below:
SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> DROP TABLESPACE my_space;
Tablespace dropped.

After you have created a new tablespace, you can give it to your users for them to create tables in the new tablespace. To create a table in a specific tablespace, you need to use the TABLESPACE clause in the CREATE TABLE statement. Here is a sample script:
SQL> connect SYSTEM/fyicenter
Connected.

SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE my_team TABLESPACE my_space
  2  AS SELECT * FROM employees;
Table created.

SQL> SELECT table_name, tablespace_name, num_rows
  2  FROM USER_TABLES
  3  WHERE tablespace_name in ('USERS', 'MY_SPACE');

TABLE_NAME                     TABLESPACE_NAME    NUM_ROWS
------------------------------ ---------------- ----------
MY_TEAM                        MY_SPACE           - 
EMPLOYEES                      USERS              107
...

If you created a tablespace with a data file a month ago, now 80% of the data file is used, you should add another data file to the tablespace. This can be done by using the ALTER TABLESPACE ... ADD DATAFILE statement. See the following sample script:
SQL> connect HR/fyicenter

SQL> CREATE TABLESPACE my_space
  2  DATAFILE '/temp/my_space.dbf' SIZE 10M;
Tablespace created.

SQL> ALTER TABLESPACE my_space
  2  DATAFILE '/temp/my_space_2.dbf' SIZE 5M;
Tablespace altered.

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES
  2  FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME                             BYTES
--------------- --------------------------------- ---------
USERS           C:\ORACLEXE\ORADATA\XE\USERS.DBF  104857600
SYSAUX          C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO            C:\ORACLEXE\ORADATA\XE\UNDO.DBF    94371840
SYSTEM          C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840
MY_SPACE        C:\TEMP\MY_SPACE.DBF               10485760
MY_SPACE        C:\TEMP\MY_SPACE_2.DBF              5242880

SQL> SELECT TABLESPACE_NAME, FILE_ID, BYTES
  2  FROM USER_FREE_SPACE
  3  WHERE TABLESPAE_NAME IN ('MY_SPACE');
TABLESPACE_NAME                   FILE_ID      BYTES
------------------------------ ---------- ----------
MY_SPACE                                6    5177344
MY_SPACE                                5   10354688
This script created one tablespace with two data files.

ORA-00959: tablespace ‘TB001′ does not exist When Data Pump Import

When importing data from different database, sometimes you get errors like:

ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
ORA-00959: tablespace 'TB001' does not exist
Failing sql is:

This means that the tablespace “TB001″ doesn’t exist in the database where you’re importing the data. For this, you can use REMAP_TABLESPACE option. If you have more than one tablespace that doesn’t exist in the second database, use comma as follows:


REMAP_TABLESPACE=db01_tb001:db02_tbs,db_01_tb002:db02_tbs

To get which remap script you need to create, check TABLESPACE_NAME column for the DBA_SEGMENTS view and find in which tablespaces your objects are reside

Simply View Oracle Session Usage

Here are some of PL/SQL scripts related to gather session statistics that I have developed to monitor the session usage.If the database getting slow it is possible to analyze using this that the resource usage by user connected

 SELECT
  'Currently, '
  || (SELECT COUNT(*) FROM V$SESSION)
  || ' out of '
  || DECODE(VL.SESSIONS_MAX,0,'unlimited',VL.SESSIONS_MAX)
  || ' connections are used.' AS USAGE_MESSAGE
FROM
  V$LICENSE VL

 SELECT
  'Currently, '
  || (SELECT COUNT(*) FROM V$SESSION)
  || ' out of '
  || VP.VALUE
  || ' connections are used.' AS USAGE_MESSAGE
FROM
  V$PARAMETER VP
WHERE VP.NAME = 'sessions'

 SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
       ((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
       used_mb,
       NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb
       FROM v$datafile df,dba_free_space dfs
       WHERE df.file# = dfs.file_id(+)
       GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
       ORDER BY file_name;

Oracle Data Pump Export/Import

Oracle Data Pump utility is used for exporting data and metadata into set of operating system files and it is newer, faster and flexible alternative to “export/import” utilities.

1. Create directory object as SYS user.
SQL> create or replace directory export_dir as '/oradata/export’;

2. Grant Read/Write privilege on the directory to the user, who invokes the Data pump export.
SQL> grant read,write on directory export_dir to test_user;

3. Take Data Pump Export

Click here to see Roles/privileges required for Export modes.

Oracle data pump export examples for all 5 modes.

(i) Full Database Export
$ expdp test_user/test123 full=y directory=export_dir dumpfile=expdp_fulldb.dmp logfile=expdp_fulldb.log

(ii) Schema Export
$expdp test_user/test123 schemas=test_user directory= export _dir dumpfile=expdp_test_user.dmp logfile=expdp_test_user.log

If you want to export more than one schema then specify the schema names separated by comma.

(iii)Table Export
$ expdp test_user/test123 tables=emp,dept directory= export _dir dumpfile=expdp_tables.dmp logfile=expdp_tables.log

You can specify more than one table.

(iv) Tablespace Export
$ expdp test_user/test123 tablespaces=test_user_tbs directory= export _dir dumpfile=expdp_tbs.dmp logfile=expdp_tbs.log

You can specify more than one tablespace.

(v) Transportable tablespace
$ expdp test_user/test123 transport_tablespaces=test_user_tbs transport_full_check=y directory= export _dir dumpfile=expdp_trans_tbs.dmp logfile=expdp_trans_tbs.log

Click here to learn more on Transportable Tablespace with examples.

Oracle Data Pump Import :-
Data Pump Import utility is used for loading an export dump files into a target system and we can load one or more files.

Copy the dump file to the target system where you to import.

1. Create directory object as SYS user.
SQL> create directory import_dir as '/oradata/import';

2. Grant Read/Write privilege on the Directory to the user, who invokes the Data Pump import.
SQL> grant read,write on directory import_dir to test_user;

3. Import the data using Data Pump Import.

Oracle data pump import examples for all 5 modes.

(i) Full Database Import
$ impdp test_user/test123 full=Y directory=imp_dir dumpfile=expdp_fulldb.dmp logfile=imp_fulldb.log

(ii) Schema Import
$impdp test_user/test123 schemas=test_user directory=imp_dir dumpfile=expdp_test_user.dmp Logfile=impdp_test_user.log

(iii) Table Import
$ impdp test_user/test123 tables=emp,dept directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log

From 11g, you can reaname a table during the import
REMAP_TABLE=[schema.]old_tablename[.partition]:new_tablename
$ impdp test_user/test123 remap_table=test_user.emp:emp1 directory=imp_dir dumpfile=expdp_tables.dmp logfile=impdp_tables.log

Tables will not be remapped if they already exist even if the TABLE_EXISTS_ACTION is set to TRUNCATE or APPEND

(iv) Tablespace Import
$ impdp test_user/test123 tablespaces=test_user_tbs directory=imp_dir dumpfile=expdp_tbs.dmp logfile=impdp_tbs.log

Above example imports all tables that have data in tablespaces test_user_tbs and it assumes that the tablespaces already exist.

(v) Transportable Tablespace
Click here to to import data using Transportable Tablespace method.

Common Errors with Data pump import (impdp) utility:-

1. ORA-31631: privileges are required
ORA-39122: Unprivileged users may not perform REMAP_SCHEMA remapping
Cause: A user attempted to remap objects during an import but lacked the IMPORT_FULL_DATABASE privilege.
Action: Retry the job from a schema that owns the IMPORT_FULL_DATABASE privilege.

2. ORA-31631: privileges are required
ORA-39161: Full database jobs require privileges
Cause: Either an attempt to perform a full database export without the EXP_FULL_DATABASE role or an attempt to perform a full database import over a network link without the IMP_FULL_DATABASE role.
Action: Retry the operation in a schema that has the required roles.

3. ORA-01950: no privileges on tablespace "string"
Cause: User does not have privileges to allocate an extent in the specified tablespace.
Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.

Click here to learn Roles/ privileges required for Data pump Export and Import.

4. import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
IMP-00017: following statement failed with ORACLE error 3113:
"BEGIN "
"SYS.DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE SYS.DBMS_RULE_ADM.CREATE_EVALUATIO" "N_CONTEXT_OBJ, 'SYS',TRUE);"
Cause: Import fails while executing the following command.
Action: Login as sys and run the following scripts
$ORACLE_HOME/rdbms/admin/dbmsread.sql
$ORACLE_HOME/rdbms/admin/prvtread.plb

CACHE & NOCACHE Hint

When we come to usage of the WSO2 products all product include  db scripts to both oracle.sql and oracle_rac.sql db script. In this two products oracle.sql include NOCACHE and oracle_rac.sql include CACHE.I intend to writing blog post about CACHE and NOCACHE hint.Furtherthis will be useful when we interacting WSO2 product with Oracle.


CACHE & NOCACHE Hint

The CACHE hint instructs the optimizer to place the blocks retrieved for the table at the most recently used (MRU) end of the least recently used (LRU) list in the
buffer cache when a full table scan is performed.
The buffer cache holds copies of data blocks so as they can be accessed quicker by oracle than by reading them off disk.Blocks within the buffer cache are ordered from MRU (most recently used) blocks to LRU (least recently used) blocks. Whenever a block is accessed, the block goes to the MRU end of the list.This hint is useful
for small lookup tables.

In the following example, the CACHE hint overrides the default caching
specification of the table:
SELECT /*+ FULL (hr_emp) CACHE(hr_emp) */ last_name
 FROM employees hr_emp;

SELECT /*+ FULL(hr_emp) NOCACHE(hr_emp) */ last_name
 FROM employees hr_emp;


The different between CACHE/NOCACHE hint is Cache blocks from Full Table Scan & Nocache do not cache blocks from a Full Table Scan.When we comes to Oracle RAC enviornment do need to cache blocks from a full table scan because it has two clustering nodes that needs to Cache blocks.The CACHE and NOCACHE hints affect system statistics table scans (long tables) and table scans (short tables), as shown in the V$SYSSTAT data dictionary view.


Above are collection of things that i gathered relevant to this subject

Axis2 Clustering

Clustering concept is a remarkable and noteworthy model. I have comprehended the axis2 cluster mechanism.

A cluster consists of a group of independent but interconnected server client whose combined resources can be applied to a processing task. A common cluster feature is that it should appear to an application as though it were a single server. Apache Axis2 works the same.

Main requirement of any clustering enterprise deployment are High Availability & scalability. High Availability, which services, monitors, and restarts all other resources as required. Scalability challenges are ability to serve large capacity and speed continues to grow at an exponential rate due to a variety of factors like large number of request.

We can simply enable Axis2 Clustering in the axis2.xml file enabling attribute to true locate in the "Clustering".The services provided by Axis2 clusteringe include the Axis2 clustering management API.

The axis2.xml file clustering configuration section include below main parts.

Clustering Agent & Clustering Agent Parameters
Agents normally have parameters that can be changed in order to tweak their initialization & performance. Following is a brief introduction about the important parameters that are  useful.

synchronizeAll- Ensures Synchronization  that all synchronized content
is identical across the cluster member
maxRetries- Define the timestamps to retry to send message among the
cluster members before the node fail
localMemberHost- Include the IP addresses or host name of the cluster members
memberDropTime- If a if a member does not respond in a cluster axis2.xml include time interval to left the group,It include under this in milliseconds
AvoidInitiation- To be the cluster has automatically initialized this parameter include

        <parameter name="synchronizeAll">true</parameter>
        <parameter name="maxRetries">10</parameter>
        <parameter name="localMemberHost">127.0.0.1</parameter>
        <parameter name="memberDropTime">3000</parameter>
        <parameter name="AvoidInitiation">true</parameter>

State Management
If we have a load balanced cluster State Management it requires synchronizing to the state across the members. State Management Clustering behaviour maintain keeping track of a user's activity across sessions.

Node Management
Node Management, you can add nodes to your cluster, monitor node
status, and perform management actions on nodes

<nodeManager class="org.apache.axis2.clustering.management.DefaultNodeManager"
            enable="true"/>

Group Management
This Managing resource groups in a cluster.It describes adding and
removing resource groups, and changing resource group attribute

<groupManagement enable="true">
    <applicationDomain name="group1"
                      description="This is the first group"

agent="org.apache.axis2.clustering.management.DefaultGroupManagementAgent"/>
    <applicationDomain name="group2"
                      description="This is the second group"

agent="org.apache.axis2.clustering.management.DefaultGroupManagementAgent"/>
</groupManagement>

Members
A set of identifiers are used to give a cluster member an identity within the cluster with different Ip addresses & ports in axis2
cluster

WSO2 Message Broker Integrated With Zookeeper & Cassandra

WSO2 implemented the new Distributed Message Broker by developing the WSO2 Message Broker Integrated with Zookeeper & Cassandra.

When we consider distributed systems this is arguably one the technologies involved in a distributed systems are complicated and not easy to understand because distributed systems are hard to program. Testing a distributed system can also be quite intricate. Verifying of the behaviour and impact of code in the system is not that easy and requires a lot of careful thought and planning during the testing phase.

In this blog post first I elaborate WSO2 Message Broker Integrated With Zookeeper & Cassandra

Apache Zookeeper

Zookeeper is a centralized service coordinator where distributed applications synchronize, all of which services that are used by other distributed applications. When we integrate WSO2 MB with Zookeeper MB the distributed application is going to use the services provided by the Zookeeper. Zookeeper determines which node in the ZooKeeper service is alive at any given time and the handling of the node failures. ZooKeeper supports high-availability, therefore all clients making requests receive consistent and immediate data.

Getting started with Apache Zookeeper

Download Apache Zookeeper (http://www.apache.org/dyn/closer.cgi/zookeeper/)

Create a zoo.config file in zookeeper-3.3.5/conf & include below

# The number of milliseconds of each tick
tickTime=2000
# The number of ticks that the initial
# synchronization phase can take
initLimit=10
# The number of ticks that can pass between
# sending a request and getting an acknowledgement
syncLimit=5
# the directory where the snapshot is stored.
dataDir=/tmp/zookeeper
# the port at which the clients will connect
clientPort=2181

Then locate to bin folder and start Zookeeper server by executing the command “zkServer.sh
start”

In MB instance qpid-config.xml file which locate repository/conf/advanced/ include the Zookeeper host and port configuration

<coordination>
<!-- Apache Zookeeper Host name -->
<ZooKeeperHost>127.0.0.1</ZooKeeperHost>
<!-- Apache Zookeeper port -->
<ZooKeeperPort>2181</ZooKeeperPort>
<!-- Format yyyy-MM-dd HH:mm:ss -->
<ReferenceTime>2012-02-29 08:08:08</ReferenceTime>
</coordination>

Default port for Zookeeper will be “2181”

Configure Cassandra

Download Apache Cassandra from http://cassandra.apache.org/download/

Cassandra will generate several log files that are usefula.there for we need to make create directoryies to include their paths in cassandra.yaml.We create followe directoryies data_file_directories, commitlog_directory, and saved_caches_directory & sure that the paths exist in conf/cassandra.yaml

Create system.log file & include conf/log4j.properties
log4j.appender.R.File=/var/log/cassandra/system.log

Navigate to bin folder and execute sh cassandra -f to start cassandra

There is a configuration in  MB instance to have in the distributed cluster details in MB /repository/conf/advanced/qpid-virtualhosts.xml file
<virtualhost>
<name>carbon</name>
<carbon>
<store>
<class>org.wso2.andes.server.store.CassandraMessageStore</class>
<username>admin</username>
<password>admin</password>
<cluster>ClusterOne</cluster>
<host>localhost</host>
<port>9160</port>
</store>

Running WSO2 MB

Download the WSO2 MB
using the export command Set the JAVA_HOME to your Java home
Execute the ./wso2server.sh in CARBON_HOME/bin
Login user name & password as "admin"


When we navigate to
Home-> Manage-> Message Broker Clusters -> Node List we can view Zookeeper ID c

Now Message broker can running, message sender and receiver.It can send messages to that cluster and consume messages from that cluster using sample client.I will come with the code next time

Web Security With OAuth & OpenID

I was participated to a web security workshop and session while I have captured about web features called OAuth & OpenID. Below I have brief down about the two technologies.

Web Service Security is a major requirement & crucial part of the enterprise  services.WS-Security supports multiple formats for tokens multiple trust domains, multiple signature formats and multiple encryption technologies.Both OAuth and OpenID systems tend to live longer because those are  two powerful features of WS-Security.

OAuth

OAuth is an open protocol that allows users to share their private resources, it can simply stored resources in one site with another site without having to hand out their username and password. Users have to share their credentials with potentially untrustworthy. There for data spread across various websites like Flickr, Twitter.With OAuth can be haven. Further access rights can be granted over the limited period of time without being required to expose their user name and password.WSO2 Identity Server include the feature that delegation via OAuth. It is representing the user as an authority

According to OAuth mechanism when user use of a service to login the authentication process It will redirects the user to where the serviceruns. In here OAuth provider uses its own OAuth credentials (token) toretrieve credentials for User. OAuth provider stores users credentialsalong with User's account allow him to use & access the service. OAuth important when someone hacked all the passwords of OAuth providers Even though user have lost his OAuth provider password the unauthorized person doesn’t have his service user password.

OpenID

OpenID enables to keep control over your own identity by separating the identity. Currently most people use the same username and password for every site that they are accessing to authenticate purposes. However this becomes very insecurity and not a recommended way of accessing. OpenID limit this risk by reducing the number of sites that you can access with same username and password. Using OpenID technology will be allowing one username/password to access any number of websites or online services. Further OpenID provider a secure channel and validate your identity.

In OpenID scenario it provides web redirections to communicate between the relying party and OpenID. Assume you want to login to web service but you don’t have a web service user credentials when other web services supporting OpenID there will be a possibility of using  the supporting OpenID’s web services account as to login to the web service that we dont have user ID. There for web services supporting OpenID authorizations eliminating the need of remember more than one user id and password.

I'm willing to focus on discovering & presenting those two features how support in WSO2 Identity Server.

Retrieve Data from Excel Spreadsheet & Insert Data into a Database using WSO2 Data Service Server

Assume a particular user has some data stored in the form of Excel  files and he needs to get those data exported into a MySQL database.WSO2 Data Service Server allows to expose the data stored in data sources such as relational databases, CSV files , MS-Excel files and Google spreadsheets as web services.

We can be using Nested query features provide by WSO2 Data Service Server when writing this data service. Nested query feature is possible for a particular data service query to feed the result obtained after the execution of that particular query, as an input to some other query and eventually integrate both results to an unified format before presenting it to the user.


Create a mySQL database with one table, EXCEL.

mysql> CREATE DATABASE EXCEL_SHEET;

mysql> CREATE TABLE EXCEL(ID int,Name varchar(100),Address varchar(200));

Now create the data service:
Add Excel spreadsheet as a data source

<data name="Excel">                 
   <config id="dss">                                   
      <property name="excel_datasource">/home/amal/Desktop/EmployeeList.xls</property>                 
   </config> 
</data>

Add EXCEL_SHEET database as a data source.

<config id="Excel_db">
      <property name="org.wso2.ws.dataservice.driver">com.mysql.jdbc.Driver</property>
      <property name="org.wso2.ws.dataservice.protocol">jdbc:mysql://localhost:3306/EXCEL_SHEET</property>
      <property name="org.wso2.ws.dataservice.user">root</property>
      <property name="org.wso2.ws.dataservice.password">mysql123</property>
</config>


Create a query that retrieve the data from Excel spread sheet.In this query the column names of the output mappings should be same as the column titles in Excel spreadsheet.

<query id="getExcel" useConfig="excel_data">
      <excel>
         <workbookname>Sheet3</workbookname>
         <hasheader>true</hasheader>
         <startingrow>2</startingrow>
         <maxrowcount>-1</maxrowcount>
      </excel>
      <result element="Employees" rowName="Employee">
         <element name="ID" column="ID" xsdType="xs:integer" />
         <element name="Name" column="Name" xsdType="xs:string" />
         <element name="Address" column="Address" xsdType="xs:string" />
      </result>
</query>

Then create a operation that use the above query. Using that operation we can test whether query works properly.

<operation name="op1">
      <call-query href="getExcel" />
   </operation>

Click on the try it and check whether operation works properly.
It should return the all EXCEL details when you click on the operation.


Create a query "q2" that calls to  insert data into tables.

<query id="q2" useConfig="EXCEL_list">                                   
      <sql>insert into Employee_List values(?,?,?)</sql>                                   
      <param name="ID" sqlType="STRING" />                                   
      <param name="Name" sqlType="STRING" />                                   
      <param name="Address" sqlType="STRING" />                 
   </query> 


Then you need to give the data retrieved from the Excel spreadsheet as input to the "q2" query.To do this use Data Service nested query feature and call q2 query inside getExcel query.

      <call-query href="outExcel">
         <with-param name="ID" query-param="ID" />
         <with-param name="Name" query-param="Name" />
         <with-param name="Address" query-param="Address" />
      </call-query>


We are almost done with the data service configuration.Now, go to the TryIt screen of the data service and execute the operations as below.

Now when you execute the getExcel operation both data retrieving from Excel spreadsheet and inserting data to tables is happenning.
Check the two tables.Data should have entered.

mysql> select * EXCEL_SHEET;

WSO2 Message Broker to publish in Message Boxes(SQS) using WSO2 ESB

In this post i’ll describe how we can configure WSO2 Message Broker and WSO2 ESB together. (Note : Here I'm using ESB_HOME as WSO2 ESB installation directory path and MB_HOME as WSO2 MB installation directory path.)

In this first instance we are going to get the WSO2 ESB and MB to work together.
Once you have downloaded and unzipped

Open carbon.xml in MB_HOME/repository/conf directroy and set the offset value to 1,then

Start WSO2 Message Broker.


Open event-broker.xml file in ESB_HOME/repository/conf directory and change deliveryManager type "remote".Then uncomment remoteMessageBroker details & set service port to 9444  & qpidPortto 5673 as below


<deliveryManager name="deliveryManager"
                         class="org.wso2.carbon.event.core.internal.delivery.jms.QpidJMSDeliveryManagerFactory"
                         type="remote">
             <remoteMessageBroker>
                <hostName>localhost</hostName>
                <servicePort>9444</servicePort>
                <webContext>/</webContext>
                <userName>admin</userName>
                <password>admin</password>
                <qpidPort>5673</qpidPort>
                <clientID>clientID</clientID>
                <virtualHostName>carbon</virtualHostName>
            </remoteMessageBroker>
        </deliveryManager>

After Start WSO2 ESB

Then create topic name test in WSO2 ESB & subscribe. You can create a subscription to the topic by providing the information on subscription as below

1.Topic
2.Subscription Mode
The default mode for the subscription is "Topic Only". With this mode , user creates the subscription only to the topic

3.Event Sink URL
you can create a subscription by providing the Event Sink URL : https://localhost:9443/services/EventSinkService/getOMElement
(Note : You can create a simple axis2service and use it's URL as the EventSinkURL . Inorder to create an axis2service ,
Browse the location : /wso2mb-1.0.3/samples/services/EventSinkService
Type the command : ant)
Now you can create a subscription by providing the Event Sink URL : https://localhost:9443/services/EventSinkService/getOMElement

Create a topic & message box with same name in WSO2 MB & subscribe as sqs://admin/topic_name.Message box provides the Simple Queue Service(SQS) operations.
In SQS, When a message is received by an user, that message is unaccessible for a specified time duration, and this message is not visible for other users till that specified time duration is over. If the message received user did not delete the message in that time duration, the message is visible for other users. This time duration is named as visibility timeout.

After that we Publish xml message in WSO2 ESB it will successfully prompted to WSO2 MB


Using the message box in WSO2 MB User can view the content of the message box by going through link in list view.

Setting up Governance Registry with Oracle RAC

Oracle Real Application Clusters (RAC) is an option for the Oracle Database for clustering and high availability in Oracle database environments.
In an Oracle RAC environment, some of the commands we have used in our oracle.sql is considered inefficient.If someone is testing products on a Oracle RAC, please rename oracle_rac.sql to oracle.sql before running -Dsetup.

Create a new database. This can be done by either using oracle db configuration assistant (dbca) or manually. Do necessary changes to oracle tnsnames.ora and listner.ora files in order to define databases addresses for establishing connections to newly created database. After configuring them, connect to oracle as System DBA using SQL*Plus

 $ sqlplus sys/sys-password as sysdba

As SYS DBA Create a database user,and grant privileges to the user as below:

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


Setup Configuration File

Edit $GREG_HOME/repository/conf/registry.xml and give the following:


 <currentDBConfig>oracle-db</currentDBConfig>

    <dbConfig name="oracle-db">

        <url>jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)

                 (ADDRESS=(PROTOCOL=TCP)(HOST=host_1) (PORT=1521))

                 (ADDRESS=(PROTOCOL=TCP)(HOST=host_2) (PORT=1521))

                 (CONNECT_DATA=(SERVICE_NAME=service)))

            </url>

        <userName>USER_NAME</userName>

        <password>PASSWORD</password>

        <driverName>oracle.jdbc.driver.OracleDriver</driverName>

        <maxActive>80</maxActive>

        <maxWait>60000</maxWait>

        <minIdle>5</minIdle>

    </dbConfig>

Edit $GREG_HOME/repository/conf/user-mgt.xml and give the following

<Configuration>

        ...

        <Property name="url">jdbc:oracle:thin:@(DESCRIPTION=(LOAD_BALANCE=on)

                             (ADDRESS=(PROTOCOL=TCP)(HOST=host_1) (PORT=1521))

                             (ADDRESS=(PROTOCOL=TCP)(HOST=host_2) (PORT=1521))

                             (CONNECT_DATA=(SERVICE_NAME=service)))</Property>

        <Property name="userName">USER_NAME</Property>

        <Property name="password">PASSWORD</Property>

        <Property name="driverName">oracle.jdbc.driver.OracleDriver</Property>

        <Property name="maxActive">50</Property>

        <Property name="maxWait">60000</Property>

        <Property name="minIdle">5</Property>

    </Configuration>

 Copy JDBC Driver

When we connecting WSO2 Governance Registry with Oracle 11g versions we want to copy the newest version oracle jdbc libraries to
$CARBON_HOME/repository/components/lib.If we used previous one ArrayIndexOutOfBoundsException arise.

 Create the database tables

Windows users can run $GREG_HOME/bin/wso2server.bat -Dsetup to create the database tables when starting WSO2 Governance Registry for the first time. Linux users should use $GREG_HOME/bin/wso2server.sh -Dsetup

Perform a Generic OEL 5.4 installation

Perform a Generic OEL 5.4 installation

prepare the os as with rpm and kernal settings
as follows
General Cd/DVD mount
mkdir /media/cdrom
mount -t iso9660 -o ro /dev/cdrom /media/cdrom
cd /media/cdrom/Server
Specific mount

cd /media/Enterprise\ Linux\ dvd\ 20090908/Server/

# rpm -ivh libaio-devel*rpm

warning: libaio-devel-0.3.106-3.2.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                ########################################### [100%]
   1:libaio-devel           ########################################### [ 50%]
   2:libaio-devel           ########################################### [100%]
[root@host5 Server]# rpm -ivh unixODBC-2*rpm unixODBC-devel-2*rpm 
warning: unixODBC-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing...                
########################################### [100%]
   1:unixODBC               ########################################### [ 25%]
   2:unixODBC               ########################################### [ 50%]
   3:unixODBC-devel         ########################################### [ 75%]
   4:unixODBC-devel         ########################################### [100%]


    

As root, open a terminal window and perform these steps:

Edit /etc/hosts. The file should contain text similar to the following:
vi /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
192.168.100.58  host5.localdomain host5

Configure the kernel.

vi /etc/sysctl.conf

Add the following to the file:

# Kernel paramaters required by Oracle 11gR1
fs.file-max = 6553600
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 4194304
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 262144

For the /etc/sysctl.conf changes to take effect now issue this command.

sysctl -p 

vi /etc/security/limits.conf

Add these lines:

oracle           soft    nproc           2047
oracle           hard    nproc           16384
oracle           soft    nofile          1024
oracle           hard    nofile          65536
grid           soft    nproc           2047
grid           hard    nproc           16384
grid           soft    nofile          1024
grid           hard    nofile          65536

vi /etc/pam.d/login

Add these lines:

session    required     /lib/security/pam_limits.so
session    required     pam_limits.so
       
 vi /etc/profile

Add these lines: 


if [ \$USER = "oracle" ] || [ \$USER = "grid" ]; then
    if [ \$SHELL = "/bin/ksh" ]; then
        ulimit -p 16384 
        ulimit -n 65536
    else
        ulimit -u 16384 -n 65536
    fi
    umask 022
fi

 Create Groups and User for Grid Infrastructure

Lets start this section by creating the recommended OS groups and user for Grid Infrastructure on both Oracle RAC nodes:

[root@racnode1 ~]#                               
groupadd -g 1000 oinstall
                             
groupadd -g 1200 asmadmin
                                
groupadd -g 1201 asmdba
                                
groupadd -g 1202 asmoper

groupadd -g 1300 dba
                               
useradd -m -u 1100 -g oinstall -G asmadmin,asmdba,asmoper,dba -d /home/grid -s /bin/bash -c "Grid Infrastructure Owner" grid

                                
id grid
uid=1100(grid) gid=1000(oinstall) groups=1000(oinstall),1200(asmadmin),1201(asmdba),1202(asmoper)
                              

Set the password for the grid account:

[root@racnode1 ~]#  
                                
passwd grid
Changing password for user grid.
New UNIX password:  
                                
xxxxxxxxxxx
Retype new UNIX password:  
                                
xxxxxxxxxxx
passwd: all authentication tokens updated successfully.


[grid@host5 ~]$ vi .bash_profile

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=host5.localdomain; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/grid; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/grid; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ \$USER = "oracle" ] || [ \$USER = "grid" ]; then
    if [ \$SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
    else
        ulimit -u 16384 -n 65536
    fi
    umask 022
fi

  Create Groups and User for Oracle Database Software

Next, create the the recommended OS groups and user for the Oracle database software

[root@racnode1 ~]#
groupadd -g 1301 oper
  
                                
useradd -m -u 1101 -g oinstall -G dba,oper,asmdba -d /home/oracle -s /bin/bash -c "Oracle Software Owner" oracle

[root@racnode1 ~]#  
                                
id oracle
uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1201(asmdba),1300(dba),1301(oper)
                            

[root@host5 Server]# passwd oracle
Changing password for user oracle.
New UNIX password: 
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password: 
passwd: all authentication tokens updated successfully.
[root@host5 Server]# 

[root@host5 Server]# su - oracle
[oracle@host5 ~]$ vi .bash_profile


# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR

ORACLE_HOSTNAME=host5.localdomain; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

if [ \$USER = "oracle" ] || [ \$USER = "grid" ]; then
    if [ \$SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
    else
        ulimit -u 16384 -n 65536
    fi
    umask 022
fi


  Create the Oracle Base Directory Path

The final step is to configure an Oracle base path compliant with an Optimal Flexible Architecture (OFA) structure and correct permissions. This will need to be performed on both Oracle RAC nodes in the cluster as root.

This guide assumes that the /u01 directory is being created in the root file system. Please note that this is being done for the sake of brevity and is not recommended as a general practice. Normally, the /u01 directory would be provisioned as a separate file system with either hardware or software mirroring configured. 


[root@racnode1 ~]#  
                                
mkdir -p /u01/app/grid
[root@racnode1 ~]#  
                                
mkdir -p /u01/app/11.2.0/grid
[root@racnode1 ~]#  
                                
chown -R grid:oinstall /u01
[root@racnode1 ~]#  
                                
mkdir -p /u01/app/oracle
[root@racnode1 ~]#  
                                
chown oracle:oinstall /u01/app/oracle
[root@racnode1 ~]#  
                                
chmod -R 775 /u01

Oracle date format

Oracle date format With the functions to_char and to_date, a date format can be used. Example: select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') from dual; will return something like: 27/12/2011 14:36:43 Here is a list of all the formats that can be used: Format mask Description CC Century SCC Century BC prefixed with - YYYY Year with 4 numbers SYYY Year BC prefixed with - IYYY ISO Year with 4 numbers YY Year with 2 numbers RR Year with 2 numbers with Y2k compatibility YEAR Year in characters SYEAR Year in characters, BC prefixed with - BC BC/AD Indicator * Q Quarter in numbers (1,2,3,4) MM Month of year 01, 02...12 MONTH Month in characters (i.e. January) MON JAN, FEB WW Weeknumber (i.e. 1) W Weeknumber of the month (i.e. 5) IW Weeknumber of the year in ISO standard. DDD Day of year in numbers (i.e. 365) DD Day of the month in numbers (i.e. 28) D Day of week in numbers(i.e. 7) DAY Day of the week in characters (i.e. Monday) FMDAY Day of the week in characters (i.e. Monday) DY Day of the week in short character description (i.e. SUN) J Julian Day (number of days since January 1 4713 BC, where January 1 4713 BC is 1 in Oracle) HH Hournumber of the day (1-12) HH12 Hournumber of the day (1-12) HH24 Hournumber of the day with 24Hours notation (0-23) AM AM or PM PM AM or PM MI Number of minutes (i.e. 59) SS Number of seconds (i.e. 59) SSSSS Number of seconds this day. DS Short date format. Depends on NLS-settings. Use only with timestamp. DL Long date format. Depends on NLS-settings. Use only with timestamp. E Abbreviated era name. Valid only for calendars: Japanese Imperial, ROC Official and Thai Buddha.. (Input-only) EE The full era name FF The fractional seconds. Use with timestamp. FF1..FF9 The fractional seconds. Use with timestamp. The digit controls the number of decimal digits used for fractional seconds. FM Fill Mode: suppresses blianks in output from conversion FX Format Exact: requires exact pattern matching between data and format model. IYY or IY or I the last 3,2,1 digits of the ISO standard year. Output only RM The Roman numeral representation of the month (I .. XII) RR The last 2 digits of the year. RRRR The last 2 digits of the year when used for output. Accepts fout-digit years when used for input. SCC Century. BC dates are prefixed with a minus. CC Century SP Spelled format. Can appear of the end of a number element. The result is always in english. For example month 10 in format MMSP returns "ten" SPTH Spelled and ordinal format; 1 results in first. TH Converts a number to it's ordinal format. For example 1 becoms 1st. TS Short time format. Depends on NLS-settings. Use only with timestamp. TZD Abbreviated time zone name. ie PST. TZH Time zone hour displacement. TZM Time zone minute displacement. TZR Time zone region X Local radix character. In america this is a period (.)