Should you know your database technology?

Techrepublic |

In the past I had couple of opportunities to work with firms those specialize in Small to Medium scale business solutions built on top of Microsoft SQL Database technology. Much of such opportunities turned in to 100% futile as the vendors firmly believed the “Database” was a customer’s responsibility & insuring performance was solely at customer’s dispersal. I will get back to the subject after a brief introduction to how things go all the way wrong with these partners.


We were pretty happy with the cost effective ERP Suite & just prior signing the contract, question about who will setup and configure the Database(technology) came up. The solution provider was quick to respond with “Database maintenance is your part, so as it is mentioned in the draft contract”. Agreeing to the maintenance part, that is insuring the availability and backing up the data, we repeated the question who will “setup” the database and parameterize it for best performance, as their ERP Suite was replacing something really huge & users will expect the same slick and quick responses from the new system.

The vendor was so confused & told us few things like below:

  1. There is nothing to configure
  2. Microsoft SQL Database is not like Oracle, you don’t need to worry about anything. Just install it and forget it. Server takes care of it (heeheehee)
  3. Just take a backup
  4. Nothing to configure about memory. You got 32GB memory right? more than enough. If needed we can always add more memory
  5. Yes, you can even install the database on your Windows XP machine and we think the latest Windows server is 2013 (Not exaggerated, we have an email with Windows Server 2013 mentioned in the supported OS list)

With each passing moment, they started getting agitated and the Project Manager from their end started asking questions like “We are a solution developer, why do you expect us to know “everything” about database?”. We had to tell them they don’t know anything about the database technology on top of what they design and develop their entire solution.

They lost the opportunity, which grew to approximately 1 million USD project gradually, using Oracle technologies (the worst blunder they made was “All you need a single license for the database as our application connects to database as same user for all modules.”)


A bit different scenario. This time the vendor was smarter. In order to impress us, they have designed the database to look so large, that it sized more than 25-30GB(more than 70% dedicated to Transaction Logs) before the solution was even launched. This company had a pretty wrong idea about tables and views. To “read faster” all their tables had all the columns those were required by their solution. In addition much of their views were having more than 200+ columns and many dozen inline queries to fetch additional data, making a simple query painfully slow.

On top of it, they implemented an always open URL (Obviously, keeping it open is “Your” (customer) responsibility) for processing some data.

Every other time, whenever we pointed out the difficulties with fetching data from their tables and views for custom reporting, we were given answers those should not be quoted here.

We terminated the contract after completing one (painful) year.

So, the question is, Should you know your database technology?

Much of the Prime time database technologies are pre-configured to a certain extend & a developer as a single individual may not be too interested to ponder deep into the available provisions to set it up the instance for optimized performances, mainly because everything works from the development machine, for the “development” perspectives.

Well, this is not the case when a proper business solution with commercial intend is developed based on a particular database technology. The software vendor have to understand the database technology they are targeting for their business solution to such an extended that, their product could benefit the customer in terms of continued availability and deliver insure optimized performances.

So, how to size and parameterize the database for optimized performances? This requires an experienced DBA/team to assist you with these fine tuning and parameterization activities. While Oracle database fine tuning and parameterization possibilities far more stretched than Microsoft SQL Server, you have the ease of using a GUI tool for the later, that helps you to setup many optimization parameters without the help of a DBA. MySQL also comes with a beautiful GUI manager for much of the configurations. However, just having some wonderful tools alone do not help you to achieve the maximum performance! One must know what and how to configure the database and OS specific parameters to get the maximum through output from the database technology. Yes, you need an experienced DBA or a team of DBAs to achieve this & I believe, for a successful software, a software developer must invest adequate efforts and funds in this sector with highest priority, may much before they start designing their solution!

An example case, we had our Oracle database 10g instance set with 2GB memory for SGA and 1GB for PGA for almost 4-5 years by our part-time DBA, while the server had another 40GB memory to spare. Our ERP (Oracle EBS R12) lagged, stuttered for this entire period just because he never attempted to fine tune the instance once after the implementers handed it over to us. His last excuse was “So, everything was working, why we have to trouble something that is working fine”. We terminated his contract. Our new partner setup the instance with highest possible SGA/PGA combinations and made a dozen parameterizations based on the OS specifications. It was followed by internal team introducing HugePages on Linux environment & we never looked back. Well, it took us some time to get there though to “find a right partner to work with”.

Majority of the places, wherever I were asked to investigate the poor performances (especially Microsoft SQL Server), noticed that the database was installed using the defaults and the only one maintenance activity for the instance is limited to full database backup daily. None of the maintenance possibilities like automating the indexing or statistics gatherings were implemented, making the solutions to lag and painful over a period of time.

While vendors like those I have introduced in the very beginning of the article would easily escape their responsibility by stating “database maintenance is your responsibility”, as a developer and as “NOT A Certified DBA” I will argue that.

I remember another interesting scenario when the vendor was asked which edition of the database should be installed for their solution. Initially struggling with release numbers and later making statements like “Enterprise edition will have more features” and failing to list few features never landed this vendor in a very bad light. They completely ignored the fact that we were a business that was already using software solutions like Oracle Applications & never thought of answering questions related to database.

A thorough study must be done by the solution developer to identify the best edition of database (Enterprise, Standard or Express) that is suitable for the customer. This is especially important when a solution with generic nature is adapted by businesses of different sizes. A software solution developer must understand that, every business tries to limit the investment for software implementations and unnecessary licensing costs by suggesting a wrong edition of database technology could force the customer to reject their product altogether and discard the project.

I’ve started my developing career with dBASE3 (Without knowing much about it) & last 20 years, worked with different database technologies & as a core application developer, strongly believe that, the developer MUST know many things about the database technology, based on which the targeted solution is being developed. As an individual I might be pardoned, however as a software vendor, I might lose wonderful opportunities just by not knowing enough about the database technology that’s the core of my business application!

So what you think, should I know my database technology?

This article was initially posted with my LinkedIn account.

MS SQL Northwind & Pub databases | ‘sp_dboption’

Hi guys

Today I am going to upload two SQL scripts using which you could build Northwind & Pub databases against latest versions of MS SQL Server. The latest tested at my end is SQL Server 2014

I have packaged these scripts to avoid the error “Could not find stored procedure ‘sp_dboption’.” error that arises when Microsoft provided SQL scripts are executed against latest database technologies.

You may download the SQL scripts from this link

MD5: 5831113f5ec890986312d2ed532a13e2

SHA-1: 57484de9bea37c5e0b8ba84454e8093612d96df6



Oracle Weblogic | Reports 11g | cgicmd.dat | Key mapping issues


Hi guys

I’ve been installing and configuring Weblogic 10.3.6 & 12c (recently) and came across a peculiar issue, where the reports server failing to map the keys I’ve specified with cgicmd.dat file.

The best I could recollect from my memories was, I only added the keys to a single instance of cgicmd.dat file, as there are many instances of the same file once after classic domains are created.

Under linux installations you will find the file in 3 different locations once after the domain created and WLS_REPORTS server started.


[root@wls Middleware]# find /u01/Oracle/Middleware/ -name cgicmd.dat

[root@wls Middleware]#


Under usual scenarios you need to add the keys ONLY to the cgicmd.dat file present in



With my latest installations of 12c & 11g, I had issue getting the keys mapped properly & after loads and loads of googling I decided to add the same key to all available instances of cgicmd.dat one after another.

So I started with the first instance of cgicmd.dat file, that was found under


which is WLS_REPORTS temporary folder. I added the key, restarted the WLS_REPORTS server & the key was mapped as required!

This could be a nasty bug because I don’t remember adding the keys to multiple files while I tried out forms and reports 11g Release 2 almost 2 years back!

The most interesting part of the entire exercise is, once the key mapping happens successfully, you can remove the keys from cgicmd.dat file that in the temporary location and everything works! Could be the magic of cache Winking smile

So if you come across key mapping issues, you know where to look at for a quick fix.

for Windows7bugs


Oracle services disappear from Windows Services panel after upgraded to Windows 8.1

Just a week back we upgraded two of our development laptops to Windows 8.1 64bit PRO, which were having Oracle 10g locally installed.

Once after the upgrade, we realized that the Windows services panel totally missing the entries for Oracle services (yes WTF from our end as well)

There is a question thrown at

which is yet to receive any answers. We were in the same situation and started our experiments to find a solution.

Without boring you with details, here is the complete solution

Re-configure your listener, do not delete, just re-configure using the GUI. This will re-create the Windows service for listener.




Use oradim to create a new service (Please note, this scenario is strictly for creating services while they are missing, if you want to recreate services which are already available, YOU must drop them)

From an elevated command prompt



D:\oracle\product\10.2.0\db_1>oradim -new -sid %ORACLE_SID% -intpwd PASSWORD -st
artmode M -pfile D:\oracle\product\10.2.0\admin\sct\pfile\init.ora

There is a high possibility that you may find the init.ora file like following

Volume in drive D is New Volume
Volume Serial Number is 866B-F095

Directory of D:\oracle\product\10.2.0\admin\sct\pfile

10/31/2013  12:31 PM    <DIR>          .
10/31/2013  12:31 PM    <DIR>          ..
02/18/2013  09:24 AM             2,613 init.ora
02/18/2013  09:24 AM             2,613 init.ora.11820139272
10/31/2013  12:31 PM               745 sqlnet.log
3 File(s)          5,971 bytes
2 Dir(s)  97,706,209,280 bytes free


just copy the init.ora.NNNNNNNNNNN file to init.ora

Now start registry edit. This is required for the situation when you will notice the database service you have just created starts, however will not mount the database.

Navigate to



(We are using Windows 8.1 64bit)

By the right side details panel, find the key “ORA_<SID>_AUTOSTART” which will have a value “FALSE”. Change this value to “TRUE” and get back to Windows services panel and start the database service.

That’s all folks

if you are not cursed, the Oracle database service must start, mount the database without any troubles.

referenced threads:

For Windows7bugs


Oracle Database 11g Release 2 (11.2) Installation On Oracle Linux 6


We always refer for exclusive tips on installing various Oracle products and we would suggest, it is one of the best repositories you can also depend upon for.

Recently we were having a requirement to migrate our legacy 10.1.2.x Oracle database to 11gR2 and we followed this thread by to.

Our scenario was involving a VM instead of physical server which was hosted on Windows 8 64 Professional edition using Oracle’s VirtualBox 4.2.18

Following the straight forward instructions we installed Oracle Linux 6.2 X86_64 (64bit) and added Oracle’s own public repository for yum repository (instructions here)

Next we ran an update cycle, and our distro was upgraded to 6.4 from 6.2 and the first issue we realized was X terminal. To resolve, we did the following few activities

Uninstalled the VirtualBox addons

Re-configured the X terminal

Re-installed VirtualBox addons

Then went ahead with instructions available with the oracle-base post for the 11gR2 installation.

Even though all the instructions were tailored for a successful installation of 11GR2 we were stuck as soon as we tried to start the installation process by calling


A number of Java exceptions where thrown and while digging around, came to a conclusion that, it was due to the exporting DISPLAY and later allowing access to the same.

We found a thread on stackoverflow, with a solution and the solution was

as root



$export DISPLAY

$xhost +

then as user oracle

$su – oracle


$export DISPLAY

$cd /stage/database


A number of .so files were upgraded to recent versions during the 6.2 to 6.4 upgrade process, hence the installer will prompt missing library files which you can safely ignore (We would recommend you to resolve such warning if you are doing a production installation, a test scenario could overlook these wanings)

It is painful, however, please do confirm all the recommended packages or new versions are already available with your linux installation prior going ahead with the installation.

We installed unixODBC package(s) using the add remove software, a newer version than the one suggested by Oracle and proceeded with the database installation.

Even though we were able to complete the installation successfully, left us with huge concerns like the time and efforts required, the level of knowledge about linux and complexities resolving the dependency which is almost nothing in the case of Windows environment.

Anyway, we hope you would enjoy another quality post by us!



Oracle Database, Send SMS through SMS Gateway

Tested on 10g database with default installation(no additional packages were installed to achieve the results)

There are many APIs (developed by 3rd parties) which will allow you to send SMS from a Oracle database on demand. Many times such APIs would become costly, depending upon your requirements.

Here, we are providing a zero cost solution, incase if your SMS gateway provider allows you to send SMS through a web service portal.

You may wrap the entire procedure and call it against a table trigger or through a button click available with user form(s)

 URL_TEXT    VARCHAR2 (32767);
 URL         VARCHAR2 (2000);
 SMS_MSG     VARCHAR2 (160):= 'Congratulations! Your database has been configured propoerly for sending SMS through a 3rd party SMS Gateway';
 --Based ON your SERVICE provider, THE FOLLOWING LINK FORMAT may differ FROM
 --What we have specified below!
 URL :=''
 || UTL_URL.Escape (SMS_MSG, TRUE);
 UTL_HTTP.SET_HEADER (HTTP_REQ, 'User-Agent', 'Mozilla/4.0');
 -- PROCESS Request

Enjoy another quality solution from us :)



Windows 7 64bit, Classic ASP, Visual Studio connection to Oracle Database


26.12.2013 Update

We realized, over a 64bit OS you may need to change the default application pool to accommodate 32bit applications. Please refer to this post

So, you are still using classic ASP or develop 32bit .NET applications using Visual Studio and Oracle databases on a Windows 7 64bit box and completely lost as connection cannot be established?

Here are few workaround solutions

Install the latest Oracle clients (both 64bit and 32bit)

If you installed Oracle client in the following folders (example)


right click on the folder, under security tab check whether authenticated users have read privileges.

If not, provide the basic privileges (Read & Execute, list folder contents, Read) Refer the image


Now add the user “IIS_ISURS” user and provide the same rights mentioned above

RESTART your Windows 7 “64bit” box now!

You can establish the connection from classic ASP like following

    Dim adoCon                'Holds Connection
    Dim rsViewRecords        'Holds Recordset

    Set adoCon = Server.CreateObject("ADODB.Connection")
    if Err.Number <> 0 then 
        response.Write "<hr>ORASESSION Error<br>" & err.number & "  --  " & err.Description & "<hr>"
    end if 

    adoCon.Open ";data source=test;user id=apps;password=apps;plsqlrset=true"

    Set rsViewRecords = Server.CreateObject("ADODB.Recordset")





Oracle imp VS Quest Toad’s import utility Wizard


Quest Toad may the best Oracle database tool developed by a third party. However, this costly tool may not be the best one for importing objects when you are in a process of migrating from an older version of database to relatively new one! (Or minimum our experiences prove this multiple occasions)

Oracle’s import tool (imp.exe  on windows) is a console based utility and could be used without worrying the headaches usually Toad creates.

Below we are providing a “kind” of instructions about importing objects from a full database backup “.dmp” file which is created using Oracle’s export console utility


Prior importing objects, please make sure you have created the following with fresh installed database

  1. Tablespaces
  2. Users
  3. Grants to the users (CONNECT, RESOURCE, DBA etc)
  4. A parameter file, call it “myparam.txt” (preferably in the same directory where you store the “.dmp” file. We prefer to keep both the files with root of a partition itself)
  5. image(Sample parameter file)

Once you created the above objects, you may proceed towards importing the user objects (tables, views, functions, packages, procedures etc)

Now open command prompt and type the following

C:\> imp system/<<password>> PARFILE=paramtext.txt LOG=C:\19052012imp.log

Now sit back while Oracle does the import job for you!




Simple batch file for Oracle database backup

Hi guys
We are too busy now days. Issues @work keeping us working around the clock and giving us hardly any time to update this blog. Quite recently, we decided to expand this forum with more Oracle technology related posts, as we realize, we get maximum traffic towards the posts related to Oracle.

To begin with, we are providing a simple script for exporting the entire database using “system” user today for Microsoft Windows based implementation. This batch file exports a .dmp file to a user specified directory.

FOR /F "tokens=2-4 delims=/ " %%a IN ('date/t') DO exp system/password@connectionstring full = y file=d:\backup\exp_%%b%%a%%c.dmp

Save this script inside a .bat or .cmd file (eg: myorabackupdaily.bat or myorabackupdaily.cmd). An Administrator can easily create a scheduled job to insure that the batch file runs everyday at a particular time. Preferably during nights while transactions are none or less. Particularly, there is no need to shutdown the database in order to facilitate the export.

Once exported to .dmp file, it should bear a nomenclature as following:

exp -> Export
22012011-> Date stamp

hope this article/ batch file is useful for few out there.

For Windows7bugs,