Quick Tip: tnsping

Tnsping is a nice little utility that comes with Oracle. Lot of developers don't know about this. If you have SQL*Plus available on your machine, chances are you also have tnsping. You can use this tool to troubleshoot Oracle connectivity issues, sort of like ping for TCP/IP. To use it, just type

tnsping <Service Alias> [count]

Where Service Alias is defined in tnsnames.ora.

Typically, if tnsping returns an error, chances are the alias doesn't exist in tnsnames.ora file or a typo. Just add/correct that in the file, you will be able to ping the Oracle instance.

On a Windows system, this is where an introduction to tnsnames.ora would end. But not on Unix.

Gotcha on Unix

When we had connectivity issue with Oracle 11g database this morning, we kept getting errors while trying to tnsping.I logged in with the oracle id and checked the contents of tnsping.ora file. It looked OK. Also, with this id, I was able to login to Oracle without any problem. This was a puzzle.

Then I remembered the file level permission on *nix systems. I looked at the permissions attribute of the file, tnsnames.ora (If you do a ls -l on Unix, it shows you that). Bingo! The file had didn't have "read" access for Other (public).

-rw-rw-r-- tnsping.ora

In this case, even though  the file was there, it wasn't "visible" to other id's, because of missing read permission. When I added that (r in bold), the other user was able to tnsping and connect to Oracle finally.

Note on connectivity

If tnsping is successful, it merely tells us that the SQL*Net listener is running correctly on the server side. This doesn't guarantee that the database itself is running. You need to login to find that out.

References

1. http://edstevensdba.wordpress.com/2011/02/27/tnsping-101/


2. http://www.orafaq.com/wiki/Tnsnames.ora


3. http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/TDT2/Default.aspx


4. http://docs.oracle.com/cd/B19306_01/network.102/b14212/connect.htm#sthref1535


5. http://www.dartmouth.edu/~rc/help/faq/permissions.html

Gotcha: Oracle11g Password Case Sensitivity

We are in the process of upgrading to Oracle 11g. Today, a friend at work asked about an issue he had with connecting to Oracle 11g from Unix. He was able to connect fine in SQL*Tools, SQL*Plus etc, but not when connecting from a script. The script kept failing with invalid user name or password error. The difference was that the script actually used an encrypted password and decrypted it using ccrypt utility.

We tried the decrypt option on command line, and it turns out the password was all upper case. Same script used to work in 10g. Next, we tried SQL*Plus with upper case password. It failed while the same thing worked in Oracle 10g. There we realized that Oracle 11g passwords may be case sensitive.

A beautiful post about the same, confirmed this. Apparently, passwords are case sensitive in Oracle 11g by default. This can be overridden as mentioned in the above link. Just to be complete, I am including that here:

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

You can also look at Oracle page about authentication for more details. AskTom talks about this change.

It was a real gotcha for us today.

References
http://www.oracle-base.com/articles/11g/case-sensitive-passwords-11gr1.php
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams211.htm
http://docs.oracle.com/cd/B28359_01/network.111/b28531/authentication.htm#DBSEG3225
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:708040800346734217

Ubuntu in action... - NDAS

Last week, my Windows PC stopped working. (I have to look into it later). At the moment, I am working with my taxes and I needed some files from the old machine, desperately. Luckily, I've been backing up. For this, I use IOCell Networks (also Ximeta)¹, NetDisk. NetDisk is a Network Direct Attached Storage (NDAS) device. This seems to be a combination of Network Attached Storage (NAS) and Direct Attached Storage (DAS). As a device, NetDisk is really an external hard disk enclosure that is network enabled and you add hard disk (I added a 1TB drive to it). The difference from other external drive enclosures is that this is network enabled, so once plugged into Ethernet, it can be accessed from any PC on the network. And Direct Access because, you can just plug in the device directly to Ethernet, not through any computer or server.  To add security, they have added a software layer (IOCell/Ximeta NDAS) that you need to install on each PC to access the drive. You will have to register the software with the specific serial # of the NetDisk. Thus, only specific PCs with the right software and serial # register can access the drive. I had installed this software on my Vista machine and thus worked like a charm. (This post relates only to the Linux version of this software. If you need help with Windows version, please refer to their user manual).

Now, at this critical moment, when I needed to access the back up, I couldn't. My main PC is dead. My wife's laptop runs Windows 7 and is not capable of running Ximeta NDAS software (It freezes - even the version 2.72 they suggest to fix the issue). Then my other PC, where I am typing this from, is running Ubuntu 12.04 for which Ximeta does not have a driver or programs for NDisk. At least, not any installation binaries!

But there was hope. Luckily, Ximeta provides (open source) source code at https://github.com/iocellnetworks/ndas4linux/tree/master/ for Linux. Downloaded the source code (If you go to zip tab in the above page, it will download a file named ndas4linux-master.zip. The source directory has several versions. Which version to use depends on your kernel version. (To find your kernel's version, you can use uname -r). For Ubuntu 12.04, I used version 3.2.0. Though I work with large systems at work, I never venture to compile big programs at home. This time, it's different. Desperate situation require desperate measures. I ventured on compiling the driver for Linux myself. I was amazed. Huge set of files got compiled while I watched without major issues. Once the source code is compiled, we need to install and load it. Here is a list of things to do from their documents:

1. Download a source tarball.


2. Unpack


3. CD into the right folder and run make


4. Change to root or sudo make install


5. Start ndas as root


6. As root, or by sudo, register and enable ndas devices with ndasadmin

 Apart from the documents provided, each directory inside the main folder has a README. Read these for instructions on how and where to compile. Here is a little better instructions on how to build it.

Building the software

This is really confusing, as there are many directories and many make files. To do this correctly, you need to be in the right directory. Change to ndas4linux-master/3.2.0/doc (I use 3.2.0; change to the right version) and read

how_to_export.txt first. Here is the summary of how to compile:

cd ndas4linux-master/3.2.0
make linux-rel

This creates a new directory, ndas4linux-master/3.2.0/build_x86_linux. linux-rel is one of the options. This is the final version. But, you can also build dev or debug versions. Take a look at the doc files.

Installing the software

Once you compiled, you need to install the driver and start it. Below commands do that.

cd ndas4linux-master/3.2.0/build_x86_linux/ndas-3.2.0.x86
make

The make command compiles so many files and actually builds ndasadmin and "installs" in /usr/sbin. This is the command we will use to mount NetDisk.

ndasadmin has several options. Please take a look here. To run this command, you need to be root or use sudo. If you just type sudo ndasadmin on command prompt, it will display various options available.

Starting ndas Driver

After building and installing the driver software, you need to start the driver before you can use the device.

sudo ndasadmin start

 

Mounting the Device

You also need to register the device serial #, before you can mount the device volumes. The readme file, how_to_use_ndasadmin.txt inside the version folder (3.2.0 in my case) lists the steps to install and run NDAS software. To register the device, you need to find the serial number (and write key) of the NetDisk device. These are available at the bottom of the box.

sudo ndasadmin register <SERIAL_NUMBER> -n NetDisk1
sudo ndasadmin enable -s 1 -o r

brings up the NetDisk volumes. NetDisk1 is just name I gave to the device. This can be anything. Also, register option have a slight variation with Serial # and Write key. See the readme file mentioned above.

First, I didn't see them come up. Then I found them listed in "Places" option in Gnome desktop menu. To see the device listing, you can use the following command:

cat /proc/ndas/devs

To disable/unmount the device, use the following commands:

sudo ndasadmin unregister  -n NetDisk1
sudo ndasadmin disable -s 1 -o r

Further Help

Like I said earlier, the doc folder inside the version you are working with (ndas4linux-master/3.2.0/doc) actually contains all the instructions. Here are some of the files in that directory.

how_to_build.txt

how_to_export.txt

how_to_use_ndasadmin.txt

Note that the software seems to be constantly evolving, so it may not support all the functionalities, the actual device can support. And if you need more help, here are some links if you want to learn more about Iocell Ximeta on Linux.

1. http://ndas4linux.iocellnetworks.com/


2. https://github.com/iocellnetworks/ndas4linux/wiki/How-to-export-NDAS-source-for-different-architectures


3. http://ndas4linux.iocellnetworks.com/trac/index.cgi/browser/ndas4linux/release


4. http://ndas4linux.iocellnetworks.com/kermit/index.cgi/wiki/Usage


5. http://ndas4linux.iocellnetworks.com/kermit/index.cgi/wiki/HowToBuildDEB

---

^{1 IOCell acquired Ximeta's NDAS; so you will see the names used interchangeably here and the web. See here}

SQLite Database

I've been working with different types of Databases over the years from dBase to B-Trieve to Oracle enterprise database. Along the way, I looked at or picked up quite a few - Integra SQL, Informix, MS Access, MySQL, SQL Server(s), Watcom SQL/Sybase SQL Anywhere, DB2, DB2/400 etc. While SQL databases are good, I miss the simplicity of dBase or B-Trieve files. After many years now, I think I found the lost love now!! SQLite!

In my intro I talk about "beautiful software". This is how I felt about this software. Installation is simple, you just download and unzip 1 EXE - sqlite3.exe. (or install in Linux using apt-get or yum). Done! When you are ready to create a database, you type sqlite3 <database name>. That's it!!! You want to work with the same database again, just type,

sqlite3 <database name>

The file size is what amazes me - it's a mere 50k. They've really packed a lot inside it. For one, it supports almost full (basic) SQL. I was able to actually run SQLs meant for Oracle in Sqlite. The command line utility achieves what other big databases didn't. SQL*Plus can learn a lot from this tool. And it's highly portable - in fact, I created a database in windows at work and brought home to Linux. It works like a charm.

For one, they differentiated the tool commands by .prefixing with period (.). So, you want help, just type .help. You want to see the tables in the database you just opened, just type .tables.

What impressed me with the tool, other than the simplicity is, that it provides table export imports naturally. Commands?

.import <filename> <table>

What about exporting table or query output? Just use,

.dump [<tablename>]

.output <filename>

Don't you want to export to CSV or HTML etc.? Sure, just use .mode. Then, you want a different separator? Use .separator. (Though HTML mode only dumps the tags for table rows, as it's output is often embedded in CGI outputs, but you can always add the missing tags, right?). Have you tried exporting a table from Oracle SQL*Plus? It's like extracting teeth, so we end up writing programs/procedures to do it.

True, Oracle (and other databases) is much larger and has a lot more bells and whistles, but for a simple (personal) database task, SQLite is the natural choice. Granted, this is just file based database, but smaller tasks this is a great tool to have in your toolbox. My specific task this time, is to import the log files into a database table, so I can query and procedure usage reports and charts for our recently re-factored web app. In the past, I used MS Access for this type of activity, but with a slow machine I have, Access doesn't fair well with hundreds of thousands of log records.

I've only scratched the surface with SQLite. It seems to be used in embedded applications. There is an API available to work with it C/CPP. Python (Perl, PHP etc) come with native support for SQLite. It's a pleasure to work with SQLite in Python. More on this later. Apart from these, SQLite offers extensibility by allowing to load (.load) external modules. You can also use Virtual Tables (like Oracle External table) to access a CSV file directly.

I can go on and on, but I will stop ranting and raving about the greatness of this software. If this post made you want to look up more on SQLite, please try the links below.

• http://www.sqlite.org


• http://en.wikipedia.org/wiki/SQLite


• http://www.sqlite.org/whentouse.html


• http://www.sqlite.org/selfcontained.html


• http://www.askyb.com/sqlite/learn-sqlite-in-1-hour/


• http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles


• http://code.btbytes.com/2010/09/16/sqlite-csv-loading-using-virtual-table-extension.html


• http://docs.python.org/2/library/sqlite3.html


• http://www.sqlite.org/vtab.html


• http://www.drdobbs.com/article/print?articleId=202802959&siteSectionName=database


• http://www.yolinux.com/TUTORIALS/SQLite.html


• http://robert.accettura.com/blog/2008/02/26/and-the-winner-for-most-embedded-db-is-sqlite/