Linux Kernel Upgrade on Exadata(manual way)

October 24, 2014 Leave a comment

Kernel upgrade can be applied node by node on exadata so there will be no service interruption. Kernel upgrades are required when you need new functionality or when you hit bugs on the current kernel version. I had to upgrade kernel of a box. It is a good experience and The following procedure is based on kernel upgrade on Oracle Linux 5.8 with Unbreakable Enterprise Kernel [2.6.32], a compute node of exadata.

PRE-UPGRADE

==> If you have EM12C the targets on the host will be unavailable for upgrade period. Put them in blackout state so that no false alarms generated from them.

==> Run the upgrade step on X-windows like vnc. This will prevent any disconnection issues from ssh clients.

==> Disable all NFS mounts on the system. check the locations /etc/rc.local , /etc/fstab

==> Is there any asm operations going on the system. Wait for them to finish. Make sure no rebalance job is running on the ASM part. check v$asm_operation.

==> Backup the grup startup file /boot/grub/grub.conf. you might need it for rollback.

==> Shutdown the crs and disable crs auto start. Also shutdown any databases or listeners that are not registered with the csr.
[root@host1 ~]# /u01/app/11.2.0.3/grid/bin/crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.
[root@host1 ~]# /u01/app/11.2.0.3/grid/bin/crsctl stop crs -f

==> Make sure crs is not running
[root@host1 ~]# ps -ef | grep d.bin
root 66664 60395 0 09:55 pts/1 00:00:00 grep d.bin

==> Reboot the system and make sure it is able to restart before any kernel changes  :)

==> Check the ilom problem page and make sure there is no problem on the server. If there are any like memory problems etc. fix them.

==> Record the current kernel
[root@host1 ~]# uname -r
2.6.32-400.11.1.el5uek

==> Check the server version and make sure the next kernel is designed for the server.
[root@host1 ~]# dmidecode -s system-product-name
SUN FIRE X4170 M3

==> Make sure enough space is available
[root@host1 ~]# df -h

==> Shutdown any database or listeners that hasn’t been registered with the crs. check the crs for the last time.
[root@host1 ~]# ps -ef | grep d.bin
root 66664 60395 0 09:55 pts/1 00:00:00 grep d.bin

UPGRADE
==> upgrade the kernel

[root@host1 ~]# rpm -ivh kernel-uek-firmware-2.6.32-400.34.1.el5uek.noarch.rpm kernel-uek-2.6.32-400.34.1.el5uek.x86_64.rpm ofa-2.6.32-400.34.1.el5uek-1.5.1-4.0.58.1.x86_64.rpm
Preparing… ########################################### [100%]
1:kernel-uek-firmware ########################################### [ 33%]
2:kernel-uek ########################################### [ 67%]
3:ofa-2.6.32-400.34.1.el5########################################### [100%]

==> Reboot the system
[root@host1 ~]# reboot

POST-UPGRADE
==> Check ilom for any errors. Check /var/log/messages for any errors.

==> Check the new kernel version
[root@host1 ~]# uname -r
2.6.32-400.34.1.el5uek

==> Start the crs and enable crs auto start
[root@host1 ~]# /u01/app/11.2.0.3/grid/bin/crsctl enable crs
CRS-4622: Oracle High Availability Services autostart is enabled.
[root@host1 ~]# /u01/app/11.2.0.3/grid/bin/crsctl start crs

==> Check if crs is starting

[root@host1 ~]# ps -ef | grep d.bin
root 11852 1 4 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/ohasd.bin reboot
oracle 12013 1 0 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/oraagent.bin
oracle 12025 1 0 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/mdnsd.bin
oracle 12109 1 1 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/gpnpd.bin
root 12119 1 0 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/orarootagent.bin
oracle 12122 1 1 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/gipcd.bin
root 12137 1 1 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/osysmond.bin
root 12150 1 0 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/cssdmonitor
root 12167 1 0 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/cssdagent
oracle 12169 1 1 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/diskmon.bin -d -f
oracle 12187 1 2 10:22 ? 00:00:00 /u01/app/11.2.0.3/grid/bin/ocssd.bin
root 12389 10620 0 10:23 pts/0 00:00:00 grep d.bin
[root@host1 ~]#

==> Enable any NFS mount on the system and mount them

==> On EM12c end the blackout period for the targets.

Now you can move on the other server in the cluster.

Change exadata flashcache mode from WriteThrough to WriteBack

October 24, 2014 Leave a comment

We can enable the WriteBack mode for flashcaches without shutting down the ASM or any instance on the Exadata. This will be done in a rolling fashion one cell disk at a time. You have to make sure you finish one cell before starting the operation on the next cell.

I will follow the following document to change the flashcache mode in a Exadata Database Machine X3-2 Eighth Rack System. Also you can find why we need to use WriteBack mode and other useful information in that document.

Exadata Write-Back Flash Cache – FAQ (Doc ID 1500257.1)

4. How to determine if you have write back flash cache enabled?

[root@testdbadm01 ~]# dcli -g ~/cell_group -l root cellcli -e "list 
cell attributes flashcachemode"
testceladm01: WriteThrough
testceladm02: WriteThrough
testceladm03: WriteThrough

5. How can we enable the write back flash cache?

Before proceeding any further, make sure all the griddisks are online and there are no problems on the cells.

[root@testdbadm01 ~]#  dcli -g cell_group -l root cellcli -e list 
griddisk attributes asmdeactivationoutcome, asmmodestatus
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm01: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm02: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE
testceladm03: Yes        ONLINE

On the 1/8th Rack system flashcache is reduced to half size. Becuase of that the size appears to be 744 GB.

[root@testdbadm01 ~]#  dcli -g cell_group -l root cellcli -e list flashcache detail
testceladm01: name:                      testceladm01_FLASHCACHE
testceladm01: cellDisk:                  FD_03_testceladm01,FD_02_testceladm01,FD_00_testceladm01,FD_01_testceladm01,FD_06_testceladm01,FD_05_testceladm01,FD_04_testceladm01,FD_07_testceladm01
testceladm01: creationTime:              2013-07-29T17:55:28+03:00
testceladm01: degradedCelldisks:
testceladm01: effectiveCacheSize:        744.125G
testceladm01: id:                        5adas74d-asdc-4477-382d-30c14052c23d
testceladm01: size:                      744.125G
testceladm01: status:                    normal
testceladm02: name:                      testceladm02_FLASHCACHE
testceladm02: cellDisk:                  FD_03_testceladm02,FD_07_testceladm02,FD_02_testceladm02,FD_00_testceladm02,FD_06_testceladm02,FD_01_testceladm02,FD_04_testceladm02,FD_05_testceladm02
testceladm02: creationTime:              2013-07-29T17:55:40+03:00
testceladm02: degradedCelldisks:
testceladm02: effectiveCacheSize:        744.125G
testceladm02: id:                        80c140a3-0c14-40ba-8364-10c1460d802f
testceladm02: size:                      744.125G
testceladm02: status:                    normal
testceladm03: name:                      testceladm03_FLASHCACHE
testceladm03: cellDisk:                  FD_06_testceladm03,FD_03_testceladm03,FD_00_testceladm03,FD_04_testceladm03,FD_05_testceladm03,FD_02_testceladm03,FD_01_testceladm03,FD_07_testceladm03
testceladm03: creationTime:              2013-07-29T17:55:25+03:00
testceladm03: degradedCelldisks:
testceladm03: effectiveCacheSize:        744.125G
testceladm03: id:                        6950c148-992b-4347-a1e1-e60c1406bda6
testceladm03: size:                      744.125G
testceladm03: status:                    normal

Repeat the following procedure for all the cells. We have 3 cells on 1/8 the rack exadata. Operate only on one cell at a time. Before proceeding the next cell, make sure the cell is operational. I will follow the Support ID but I prefer to use the cellcli commands in the cellcli command prompt.

Also I prefer to do any disk operation during off peak hours.

1. Drop the flash cache on that cell

CellCLI>  drop flashcache
Flash cache testceladm01_FLASHCACHE successfully dropped

2. Check if ASM will be OK if the grid disks go OFFLINE. The following command should return ‘Yes’ for the grid disks being listed:

CellCLI> list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
         DATA_TEST_CD_00_testceladm01    ONLINE  Yes
         DATA_TEST_CD_01_testceladm01    ONLINE  Yes
         DATA_TEST_CD_02_testceladm01    ONLINE  Yes
         DATA_TEST_CD_03_testceladm01    ONLINE  Yes
         DATA_TEST_CD_04_testceladm01    ONLINE  Yes
         DATA_TEST_CD_05_testceladm01    ONLINE  Yes
         DBFS_DG_CD_02_testceladm01      ONLINE  Yes
         DBFS_DG_CD_03_testceladm01      ONLINE  Yes
         DBFS_DG_CD_04_testceladm01      ONLINE  Yes
         DBFS_DG_CD_05_testceladm01      ONLINE  Yes
         RECO_TEST_CD_00_testceladm01    ONLINE  Yes
         RECO_TEST_CD_01_testceladm01    ONLINE  Yes
         RECO_TEST_CD_02_testceladm01    ONLINE  Yes
         RECO_TEST_CD_03_testceladm01    ONLINE  Yes
         RECO_TEST_CD_04_testceladm01    ONLINE  Yes
         RECO_TEST_CD_05_testceladm01    ONLINE  Yes

3. Inactivate the griddisk on the cell

CellCLI> alter griddisk all inactive
GridDisk DATA_TEST_CD_00_testceladm01 successfully altered
GridDisk DATA_TEST_CD_01_testceladm01 successfully altered
GridDisk DATA_TEST_CD_02_testceladm01 successfully altered
GridDisk DATA_TEST_CD_03_testceladm01 successfully altered
GridDisk DATA_TEST_CD_04_testceladm01 successfully altered
GridDisk DATA_TEST_CD_05_testceladm01 successfully altered
GridDisk DBFS_DG_CD_02_testceladm01 successfully altered
GridDisk DBFS_DG_CD_03_testceladm01 successfully altered
GridDisk DBFS_DG_CD_04_testceladm01 successfully altered
GridDisk DBFS_DG_CD_05_testceladm01 successfully altered
GridDisk RECO_TEST_CD_00_testceladm01 successfully altered
GridDisk RECO_TEST_CD_01_testceladm01 successfully altered
GridDisk RECO_TEST_CD_02_testceladm01 successfully altered
GridDisk RECO_TEST_CD_03_testceladm01 successfully altered
GridDisk RECO_TEST_CD_04_testceladm01 successfully altered
GridDisk RECO_TEST_CD_05_testceladm01 successfully altered

4. Shut down cellsrv service

CellCLI> alter cell shutdown services cellsrv 

Stopping CELLSRV services... 
The SHUTDOWN of CELLSRV services was successful.

5. Set the cell flashcache mode to writeback

CellCLI> alter cell flashCacheMode=writeback
Cell testceladm01 successfully altered

6. Restart the cellsrv service

CellCLI> alter cell startup services cellsrv

Starting CELLSRV services...
The STARTUP of CELLSRV services was successful.

7. Reactivate the griddisks on the cell

CellCLI> alter griddisk all active
GridDisk DATA_TEST_CD_00_testceladm01 successfully altered
GridDisk DATA_TEST_CD_01_testceladm01 successfully altered
GridDisk DATA_TEST_CD_02_testceladm01 successfully altered
GridDisk DATA_TEST_CD_03_testceladm01 successfully altered
GridDisk DATA_TEST_CD_04_testceladm01 successfully altered
GridDisk DATA_TEST_CD_05_testceladm01 successfully altered
GridDisk DBFS_DG_CD_02_testceladm01 successfully altered
GridDisk DBFS_DG_CD_03_testceladm01 successfully altered
GridDisk DBFS_DG_CD_04_testceladm01 successfully altered
GridDisk DBFS_DG_CD_05_testceladm01 successfully altered
GridDisk RECO_TEST_CD_00_testceladm01 successfully altered
GridDisk RECO_TEST_CD_01_testceladm01 successfully altered
GridDisk RECO_TEST_CD_02_testceladm01 successfully altered
GridDisk RECO_TEST_CD_03_testceladm01 successfully altered
GridDisk RECO_TEST_CD_04_testceladm01 successfully altered
GridDisk RECO_TEST_CD_05_testceladm01 successfully altered

8. Verify all grid disks have been successfully put online using the following command:

(Currently DATA_TEST diskgroup started syncronization)

CellCLI> list griddisk attributes name, asmmodestatus
DATA_TEST_CD_00_testceladm01 SYNCING
DATA_TEST_CD_01_testceladm01 SYNCING
DATA_TEST_CD_02_testceladm01 SYNCING
DATA_TEST_CD_03_testceladm01 SYNCING
DATA_TEST_CD_04_testceladm01 SYNCING
DATA_TEST_CD_05_testceladm01 SYNCING
DBFS_DG_CD_02_testceladm01 OFFLINE
DBFS_DG_CD_03_testceladm01 OFFLINE
DBFS_DG_CD_04_testceladm01 OFFLINE
DBFS_DG_CD_05_testceladm01 OFFLINE
RECO_TEST_CD_00_testceladm01 OFFLINE
RECO_TEST_CD_01_testceladm01 OFFLINE
RECO_TEST_CD_02_testceladm01 OFFLINE
RECO_TEST_CD_03_testceladm01 OFFLINE
RECO_TEST_CD_04_testceladm01 OFFLINE
RECO_TEST_CD_05_testceladm01 OFFLINE

9. Recreate the flash cache

CellCLI> create flashcache all
Flash cache testceladm01_FLASHCACHE successfully created

10. Check the status of the cell to confirm that it’s now in WriteBack mode:

CellCLI> list cell attributes flashCacheMode
writeback

11. Repeat these same steps again on the next cell. However, before taking another storage server offline, execute the following making sure ‘asmdeactivationoutcome’ displays YES: (Currently RECO_TEST diskgroup started synchronization)

CellCLI> list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
DATA_TEST_CD_00_testceladm01 ONLINE Yes
DATA_TEST_CD_01_testceladm01 ONLINE Yes
DATA_TEST_CD_02_testceladm01 ONLINE Yes
DATA_TEST_CD_03_testceladm01 ONLINE Yes
DATA_TEST_CD_04_testceladm01 ONLINE Yes
DATA_TEST_CD_05_testceladm01 ONLINE Yes
DBFS_DG_CD_02_testceladm01 ONLINE Yes
DBFS_DG_CD_03_testceladm01 ONLINE Yes
DBFS_DG_CD_04_testceladm01 ONLINE Yes
DBFS_DG_CD_05_testceladm01 ONLINE Yes
RECO_TEST_CD_00_testceladm01 SYNCING Yes
RECO_TEST_CD_01_testceladm01 SYNCING Yes
RECO_TEST_CD_02_testceladm01 SYNCING Yes
RECO_TEST_CD_03_testceladm01 SYNCING Yes
RECO_TEST_CD_04_testceladm01 SYNCING Yes
RECO_TEST_CD_05_testceladm01 SYNCING Yes

All disk groups are synchronized now we can proceed on the next cell.

CellCLI> list griddisk attributes name,asmmodestatus,asmdeactivationoutcome
DATA_TEST_CD_00_testceladm01 ONLINE Yes
DATA_TEST_CD_01_testceladm01 ONLINE Yes
DATA_TEST_CD_02_testceladm01 ONLINE Yes
DATA_TEST_CD_03_testceladm01 ONLINE Yes
DATA_TEST_CD_04_testceladm01 ONLINE Yes
DATA_TEST_CD_05_testceladm01 ONLINE Yes
DBFS_DG_CD_02_testceladm01 ONLINE Yes
DBFS_DG_CD_03_testceladm01 ONLINE Yes
DBFS_DG_CD_04_testceladm01 ONLINE Yes
DBFS_DG_CD_05_testceladm01 ONLINE Yes
RECO_TEST_CD_00_testceladm01 ONLINE Yes
RECO_TEST_CD_01_testceladm01 ONLINE Yes
RECO_TEST_CD_02_testceladm01 ONLINE Yes
RECO_TEST_CD_03_testceladm01 ONLINE Yes
RECO_TEST_CD_04_testceladm01 ONLINE Yes
RECO_TEST_CD_05_testceladm01 ONLINE Yes

FINALLY
After changing the flashcache modes on all cells, check if flashcache modes are changed to write-back for all cells.

[root@testdbadm01 ~]# dcli -g ~/cell_group -l root cellcli -e "list cell attributes flashcachemode"
testceladm01: writeback
testceladm02: writeback
testceladm03: writeback
Categories: 11g, Exadata, Oracle Tags: ,

A Beginner’s Guide to NoSQL

October 13, 2014 Leave a comment

What is it going to be? MySQL, MS-SQL, Oracle or PostgreSQL? After all, nothing can be as amazing as a good old RDBMS that employs SQL to manage the data.

Well, allow me to introduce to you an entirely unique and unconventional Database model – NoSQL. Just like every other fine article out there, we too shall begin ith…eh….disclaimers!

NoSQL stands for not-only-SQL. The idea here is not to oppose SQL, but instead provide an alternative in terms of storage of data. Yet, for the obvious reason that most users are well versed with SQL, many NoSQL databases strive to provide an SQLlike query interface.

Why NoSQL?  

That’s a valid question, indeed. Well, here are the reasons:

  • Managing Large Chunks of Data: NoSQL databases can easily handle numerous read/write cycles, several users and amounts of data ranging in petabytes.
  • Schema? Nah, not needed: Most NoSQL databases are devoid of schema and  therefore very flexible. They provide great choices when it comes to constructing a schema and foster easy mapping of objects into them. Terms such as normalization and complex joins are, well, not needed!
  • Programmer-friendly: NoSQL databases provide simple APIs in every major programming language and therefore there    is no need for complex ORM frameworks. And just in case APIs are not available for a particular programming language, data can still be accessed over HTTP via a simple RESTful API, using XML and/or JSON.
  • Availability: Most distributed NoSQL databases provide easy replication of data and failure of one node does not affect the availability of data in a major way.
  • Scalability: NoSQL databases do not require a dedicated high performance server. Actually, they can easily be run on a cluster of commodity hardware and scaling out is just as simple as adding a new node.
  • Low Latency: Unless you are running a cluster of a trillion data servers (or something like that, give or take a few million  of           them),  NoSQL can help you achieve extremely low latency. Of course, latency in itself depends on the amount of data that can be successfully loaded into memory.

Triple stores save data in the form of subject-predicate-object with the predicate being the linking factor between subject and object. As such, Triple Scores too are variants of network databases. For instance, let’s say “Jonny Nitro reads Data Center Magazine.” In this case, Jonny Nitro is the subject, while Data Center Magazine is the object, and the term ‘reads’ acts as the predicate linking the subject with the object. Quite obviously, mapping such semantic queries into SQL will prove difficult, and therefore NoSQL offers a viable alternative. Some of the major implementations of Triple Stores are Sesame, Jena, Virtuoso, AllegroGraph, etc.

SQL ideology 

Basically, NoSQL drops the traditional SQL ideology in favor of CAP Theorem or Brewer’s Theorem, formulated by Eric Brewer in 2000. the theorem talks about three basic principles of Consistency, Availability and Partition Tolerance (abbreviated as CAP), adding that a distributed database can at the most satisfy only two of these. NoSQL databases implement the theorem by employing Eventual Consistency, which is a morerelaxed form of consistency that performs the task over a sufficient period of time. This in turn improves availability and scalability to a great extent.This paradigm is often termed as BASE  – implying Basically Available, Soft state, Eventual Consistency.

NoSQL Data Models

Some of the major and most prominent differentiations among NoSQL databases are as follows:

  1. Document Stores
  2. Hierarchical
  3. Network
  4. Column-oriented
  5. Object-oriented
  6. Key-value Stores
  7. Triple Stores

Document stores

Gone are the days when data organization used to be as minimal as simple rows and columns. Today, data is more often than not represented in the form of XML or JSON (we’re talking about the Web, basically). The reason for favoring XML or JSON     is because both of them are extremelyportable, compact and standardized. Bluntly put, it makes little sense to map XML or JSON documents into a relational model. Instead, a wiser decision would be to utilize the document stores already available. Why? Again, simply because NoSQL databases are schema-less, and there existsno predefined for an XML or JSON document and as a result, each document is independent of the other. The database can be employed in CRM, web-related data, real-time data, etc. Some of the most well known implementation models are MongoDB, CouchDB and RavenDB. In fact, MongoDB has been used by websites such as bit.ly and Sourceforge.

Hierarchical Databases 

These databases store data in the form of hierarchical relevance, that is, tree or parent-child relationship. In terms of relational models, this can be termed as 1:N relationship. Basically, geospatial databases can be used in a hierarchical form to store location information which is essentially hierarchical, though algorithms may vary. Geotagging and geolocation are in vogue of late. It is in such uses that a geospatial database becomes very relevant, and can be used  in Geographical Information System.     Major   examples of the same include PostGIS, Oracle Spatial, etc. Also, some of the most well known implementations of hierarchical databases are the Windows Registry by Microsoft and the IMS Database by IBM.

Graph Network Databases

Graph databases are the most popular form of network database that are used to store data that can be represented in the form of a Graph. Basically, data stored by graph databases can grow exponentially and thus, graph databases are  ideal for storing data that changes frequently. Cutting the theoretical part, graph database has perhaps the most awesome example in the likes of FlockDB, developed by Twitter to implement a graph of who follows whom. FlockDB uses the Gizzard Framework to query a database up to 10,000 times per second. A general technique to query a graph is to begin from an arbitrary or specified start node and follow it by traversing the graph in a depth-first or breadth-first fashion, as per the relationships that obey the given criterion. Most graph databases allow the developer to use simple APIs for accomplishing the task. For instance, you can make queries such as: “Does Jonny Nitro read Data Center Magazine?” Some of the most popular graph databases include, apart from FlockDB, HyperGraphDB and Neo4j.

Column-oriented Databases

Column-oriented databases came into existence after Google’s research paper on its BigTable distributed storage system, which is used internally along with the Google file system. Some of the popular implementations are Hadoop Hbase, Apache Cassandra, HyperTable, etc.

Such databases are implemented more like three-dimensional arrays, the first dimension being the row identifier, the second being a combination of column family plus column identifier and the third being the timestamp. Column-oriented databases are employed by Facebook, Reddit, Digg, etc.

Object-oriented Databases

Whether or not object-oriented databases are purely NoSQL databases is debatable, yet they are more often than not considered to be so because such databases too depart from traditional RDBMS based data models. Such databases allow the storage of data in the form of objects, thereby making it highly transparent. Some of the most popular ones include db4o, NEO, Versant, etc. Object-oriented databases are generally used in research purposes or web-scale production.

Key-value stores

Key-value stores are (arguably) based on Amazon’s Dynamo Research Paper and Distributed hash Tables. Such data models are extremely simplifiedand generally contain only one set of global key value pairs with each value having a unique key associated to it. The database, therefore, is highly scalable and  does not store data relationally. Some popular implementations include Project Voldemort (open-sourced by LinkedIn), Redis, Tokyo Cabinet, etc.

Triple stores

Triple stores save data in the form of subject-predicate-object with the predicate being the linking factor between subject and object. As such, Triple Scores too are variants of network databases. For instance, let’s say “Jonny Nitro reads Data Center Magazine.” In this case, Jonny Nitro is the subject, while Data Center Magazine is the object, and the term ‘reads’ acts as the predicate linking the subject with the object. Quite obviously, mapping such semantic queries into SQL will prove difficult, and therefore NoSQL offers a viable alternative. Some of the major implementations of Triple Stores are Sesame, Jena, Virtuoso, AllegroGraph, etc.

Summary

So, what now? Well, you’ve just been introduced to NoSQL. However, does this mean that you should make the switch to it from SQL? Perhaps. Or perhaps not. The answer varies from situation to situation. If you find SQL queries way too much to cope with, chances are you’ll find NoSQL equally difficult. However, if you’re looking for a more flexible      alternative and do not mind getting your hands dirty, you should definitely give NoSQL a spin! The choice, obviously, is yours! Happy data managing to you!

What NoSQL databases are present today?

  • Cassandra
    • Data Model: Columnfamily
    • Query API: Thrift
  • CouchDB
    • Data Model: Document
    • Query API: map/reduce views
  • HBase
    • Data Model: Columnfamily
    • Query API: Thrift, REST
  • MongoDB
    • Data Model: Document
    • Query API: Cursor
  • Neo4j
    • Data Model: Graph
    • Query API: Graph
  • Redis
    • Data Model: Collection
    • Query API: Collection
  • Riak
    • Data Model: Document
    • Query API: Nested hashes
  • Scalaris
    • Data Model: Key/value
    • Query API: get/put
  • Tokyo Cabinet
    • Data Model: Key/value
    • Query API: get/put
  • Voldemort
    • Data Model: Key/value
    • Query API: get/put
Categories: big data, nosql, sql Tags: ,

Siebel Web Service Call with SOAP

October 13, 2014 Leave a comment

If you’re currently working with Siebel you might be familiar with their SOAP interface. When the software was originally developed, this was in accordance with best practices. However, today REST has become the dominant standard for connectivity on the web, so if you’re consuming Siebel with Ruby, PHP, Python, .NET, you’re probably looking for a REST API. Siebel does support REST, but you must buy Fusion Middleware, a costly addon to your existing platform.

This tutorial will give you basic steps for consuming the SOAP API directly from Node.js. You’ll see that the SOAP connection is fairly straightforward with the help of some libraries. Our demonstration will use the order creation service – which is what you’d use if you were building an ecommerce site that needed to submit orders to Siebel.

By consuming SOAP directly, we can avoid the extra costs of the REST translation, with very little down side.

Siebel API Overview

Siebel’s API is organized by:

Service – This is a high level grouping that covers a whole area of the API. For instance, we’ll be working with the Order Management service.
Port – This is a specific resource managed by the service. For instance, within the Order Management service you will find two ports – Order and OrderItem.
Method – This is the actual process that you will interact with. Among others each PORT will have the familiar CRUD operations.

Termonology: When I say “service” I often mean a single function, which in Siebel/SOAP terminology is a “method”. Sorry in advance for any confusion.

You can find a reference for the Siebel Order Management API here. Scroll to the bottom section for a list of the methods.

What You’ll Need

Siebel instance v7.8 or later

I’m going to assume that if you’re reading this article you’re probably already using Siebel and have an instance available.

Node.js v0.10.0 or later

You’ll need this to run our sample code. Node includes a package manager (npm) which will bring in all the other dependencies we need.

A terminal

If you’re in Windows I’d recommend git-bash.

A text editor

Whatever you’re comfortable with here. My code is written in CoffeeScript with 2 spaces for indentation, so if you’re going to edit that, make sure you’re not mixing tabs in or you’ll get strange syntax errors.

Overview of the Node-Siebel Project

The primary responsibilities we’ve taken on with node-siebel are:

  1. Generate services that can be called programmatically. We make some assumptions about the data formats in an attempt to make your life easier, so this probably won’t work for a non-Siebel API.
  2. Create a REST router for the services and hand it back to you. This is something you can plug in as express/connect/http middleware in node, and start serving your REST endpoints in just a few lines of code.

Let’s walk through the included tests, which should give you a sense of how to utilize this functionality in your own app. Go ahead and open the tests, which you’ll find here.

The first test, “should generate services” shows the services that will be generated from the sample WSDL, which is for the Order service. Each “service” in our world corresponds to a “method” in the SOAP world.

The next test “should create an order” demonstrates calling a service. If you look at the sample data being passed, you’ll notice that it differs from the message schema in the WSDL. Namely, the header elements are included for you, and you are just responsible for specifying the fields in a single document, represented as an object. This is in attempt to be more consistent with what I would expect from a REST interface. You’ll still need to provide the list/header elements for any sub-documents (e.g. OrderItems). Refer to the WSDL for the exact schema, and print the last request if you’re unsure what’s being sent.

NOTE: The Id and OrderNumber fields have a unique constraint, so you’ll need to change the values in order to run this multiple times.

The next test “should list orders” doesn’t really add much that’s new, but it’s not stateful, so you can safely run it many times.

The last test, “should generate REST API” is an integration test. It should be pretty close to the code you’d need to integrate with your own application.

You’ll notice that in addition to returning a set of services, the serviceGenerator also gives you a router. This can be plugged into connect or express, just as any other middleware would be. It has REST routes preconfigured that will forward your requests to the node-soap client, which communicates with the SOAP interface.

The code used to configure the connect middleware and start a server is standard boilerplate, and well documented in the Connect project.

Using Your Own Siebel Services

Assuming your API follows the same conventions as the Siebel standard methods, things will be easy. (Nothing is easy though, so brace yourself.)

Install Node-Siebel

npm install --save node-siebel

Obtain a WSDL

To communicate with other standard Services, or a custom one that you’ve created, you’ll need a WSDL file. This is an XML file that contains a description of where the service is and what it’s capabilities and requirements are.

Let’s go grab one from Administration – Web Services.

  1. Make sure the Siebel instance is running.
  2. Navigate to the Siebel Client in Internet Explorer.
  3. Log in as sadmin.
  4. Go to the Administrative – Web Services tab.
  5. Find the service you want to connect to and select it.
  6. Click the Generate WSDL button and complete the wizard.
  7. Transfer the WSDL file to the machine and project directory where you’ll be coding.

For reference, the Administrative – Web Services screen should look like this:

web services interface

Towards the end of your WSDL file you will find one or more <soap:address> fields. You’ll need to modify the hostnames to match the actual location of your dev server. I found in my tests that I needed to change SecureWebService to WebService, and add &WSSOAP=1 at the end. Here’s some info about the setting.

Connecting

Look at our test helper for an example of how to initialize the Node-Soap library… or follow the node-soap docs.

Option A: Programmatic Access

You can probably just use the node-soap client directly if you just need programmatic access. But if you prefer to not have to include the headers in the document, go ahead and follow the “should create an order” test example.

Option B: REST Access

Follow the example on in the main README. This should have everything you need.

Install

npm install node-siebel

Run Tests

npm install -g mocha
mocha

Tutorial

For more details and background, please see the tutorial.

Create A REST Server

With some minor changes, this should allow you to start a REST server that forwards requests to a node-soap client.

This is from example.js.

// get HTTP modules
var connect = require('connect'),
    http = require('http'),
    request = require('request'),
    server = connect();

// create a node-soap client
var soap = require('soap'),
    join = require('path').join,
    wsdlPath = join(__dirname, "../data/OrderWebService.WSDL"),
    username = 'SADMIN',
    password = 'SADMIN',
    sessionType = 'None';

soap.createClient(wsdlPath, function(err, client) {
    if (err) {
      throw err;
    };

    client.addSoapHeader("<UsernameToken xmlns='http://siebel.com/webservices'>" + username + "</UsernameToken>");
    client.addSoapHeader("<PasswordText xmlns='http://siebel.com/webservices'>" + password + "</PasswordText>");
    client.addSoapHeader("<SessionType xmlns='http://siebel.com/webservices'>" + sessionType + "</SessionType>");

    // create a REST router to forward to the node-soap client
    var serviceGenerator = require('node-siebel'),
        router = serviceGenerator(client).router;

    // connect the router and start the server
    server.use(connect.bodyParser());
    server.use(router);
    http.createServer(server).listen(4000);
});

REST endpoints will be generated based on the WSDL you provide. An example Siebel Order Management WSDL is provided which will generate the following endpoints:

POST   /order/:Id  =>  Order/SynchronizeOrder
DELETE /order/:Id  =>  Order/DeleteOrder
GET    /order/:Id  =>  Order/GetOrderById
PUT    /order/:Id  =>  Order/UpdateOrder
GET    /order      =>  Order/GetOrder
POST   /order      =>  Order/InsertOrder

anything else

POST /order/<action>  =>  Order/<action>

Categories: EAI, Oracle, Siebel Tags: , ,

Using Puppet to Manage Oracle

October 13, 2014 Leave a comment

This module contains custom types that can help you manage DBA objects in an Oracle database. It runs afterthe database is installed. IT DOESN’T INSTALL the Oracle database software. With this module, you can setup a database to receive an application. You can:

  • create a tablespace
  • create a user with the required grants and quota’s
  • create one or more roles
  • create one or more services

Setup

What oracle affects

The types in this module will change settings inside a Oracle Database. No changes are made outside of the database. Take the code from “https://github.com/hajee/oracle&#8221;.

Setup Requirements

To use this module, you need a running Oracle database. I can recommend Edwin Biemonds Puppet OraDb module. The Oracle module itself is based on easy_type.

Beginning with oracle module

After you have a running database, (See Edwin Biemonds Puppet OraDb module), you need to install easy_type, and this module.

1
2
puppet module install hajee/easy_type
puppet module install hajee/oracle

Usage

The module contains the following types:

tablespaceoracle_userrole and listener. Here are a couple of examples on how to use them.

listener

This is the only module that does it’s work outside of the Oracle database. It makes sure the Oracle SQL*Net listener is running.

1
2
3
4
listener {'SID':
  ensure  => running,
  require => Exec['db_install_instance'],
}

The name of the resource MUST be the sid for which you want to start the listener.

Specifying the SID

All types have a name like sid\resource. The sid is optional. If you don’t specify the sid, the type will use the first database instance from the /etc/oratab file. We advise you to use a full name, e.g. an sid and a resource name. This makes the manifest much more resilient for changes in the environment.

oracle_user

This type allows you to manage a user inside an Oracle Database. It recognises most of the options that CREATE USER supports. Besides these options, you can also use this type to manage the grants and the quota’s for this user.

1
2
3
4
5
6
7
8
9
10
oracle_user{sid/user_name:
  temporary_tablespace      => temp,
  default_tablespace        => 'my_app_ts,
  password                  => 'verysecret',
  require                   => Tablespace['my_app_ts'],
  grants                    => ['SELECT ANY TABLE', 'CONNECT', 'CREATE TABLE', 'CREATE TRIGGER'],
  quotas                    => {
                                  "my_app_ts"  => 'unlimited'
                                },
}

tablespace

This type allows you to manage a tablespace inside an Oracle Database. It recognises most of the options that CREATE TABLESPACE supports.

1
2
3
4
5
6
7
8
9
10
11
tablespace {'sid/my_app_ts':
  ensure                    => present,
  datafile                  => 'my_app_ts.dbf',
  size                      => 5G,
  logging                   => yes,
  autoextend                => on,
  next                      => 100M,
  max_size                  => 20G,
  extent_management         => local,
  segment_space_management  => auto,
}

You can also create an undo tablespace:

1
2
3
4
tablespace {'sid/my_undots_1':
  ensure                    => present,
  content                   => 'undo',
}

or a temporary taplespace:

1
2
3
4
5
6
7
8
9
10
11
12
tablespace {'sid/my_temp_ts':
  ensure                    => present,
  datafile                  => 'my_temp_ts.dbf',
  content                   => 'temporary',
  size                      => 5G,
  logging                   => yes,
  autoextend                => on,
  next                      => 100M,
  max_size                  => 20G,
  extent_management         => local,
  segment_space_management  => auto,
}

role

This type allows you to create or delete a role inside an Oracle Database. It recognises a limit part of the options that CREATE ROLE supports.

1
2
3
role {'sid/just_a_role':
  ensure    => present,
}

oracle_service

This type allows you to create or delete a service inside an Oracle Database.

1
2
3
oracle_service{'sid/my_app_service':
  ensure  => present,
}

init_param

this type allows you to manage your init.ora parameters

1
2
3
4
init_param{'sid/parameter/instance':
  ensure  => present,
  value   => 'the_value'
}

asm_diskgroup

This type allows you to manage your ASM diskgroups. Like the other Oracle types, you must specify the SID. But for this type it must be the ASM sid. Most of the times, this is +ASM1

1
2
3
4
5
6
7
8
9
10
11
asm_diskgroup {'+ASM1/REDO':
  ensure          => 'present',
  redundancy_type => 'normal',
  compat_asm      => '11.2.0.0.0',
  compat_rdbms    => '11.2.0.0.0',
  failgroups      => {
    'CONTROLLER1' => { 'diskname' => 'REDOVOL1', 'path' => 'ORCL:REDOVOL1'},
    'CONTROLLER2' => { 'diskname' => 'REDOVOL2', 'path' => 'ORCL:REDOVOL2'},
  }
}

At this point in time the type support just the creation and the removal of a diskgroup. Modification of diskgroups is not (yet) supported.

oracle_exec

this type allows you run a specific SQL statement or an sql file on a specified instance.

1
2
3
4
oracle_exec{"instance/drop table application_users":
  username => 'app_user',
  password => 'password,'
}

This statement will execute the sql statement drop table application_users on the instance names instance. There is no way the type can check if it has already done this statement, so the developer must support this by using puppet if statements.

1
2
3
4
5
oracle_exec{"instance/@/tmp/do_some_stuff.sql":
  username  => 'app_user',
  password  => 'password,'
  logoutput => on_failure,  # can be true, false or on_failure
}

This statement will run the sqlscript /tmp/do_some_stuff.sql on the instance named instance. Like the single statement variant, there is no way to check if the statement is already done. So the developer must check for this himself.

When you don’t specify the username and the password, the type will connect as sysdba.

oracle_thread

This type allows you to enable a thread. Threads are used in Oracle RAC installations. This type might not be very useful for regular use, but it is used in the Oracle RAC module.

1
2
3
oracle_thread{"instance_name/2":
  ensure  => 'enabled',
}

This enables thread 2 on instance named instance_name

Limitations

This module is tested on Oracle 11 on CentOS and Redhat. It will probably work on other Linux distributions. It will definitely not work on Windows. As far as Oracle compatibility. Most of the sql commands’s it creates under the hood are pretty much Oracle version independent. It should work on most Oracle versions.

Development

This is an open projects, and contributions are welcome.

OS support

Currently we have tested:

  • Oracle 11.2.0.2 & 11.2.0.4
  • CentOS 5.8
  • Redhat 5

It would be great if we could get it working and tested on:

  • Oracle 12
  • Debian
  • Windows
  • Ubuntu
  • ….

Oracle version support

Currently we have tested:

  • Oracle 11.2.0.2
  • Oracle 11.2.0.4

It would be great if we could get it working and tested on:

  • Oracle 12

Managable Oracle objects

Obviously Oracle has many many many more DBA objects that need management. For some of these Puppet would be a big help. It would be great if we could get help getting this module to support all of the objects.

If you have knowledge in these technologies, know how to code, and wish to contribute to this project, we would welcome the help.

Testing

Make sure you have:

  • rake
  • bundler

Install the necessary gems:

1
bundle install

And run the tests from the root of the source code:

1
rake test
Categories: Database, Oracle Tags: ,
Follow

Get every new post delivered to your Inbox.

Join 529 other followers