Monday, July 14, 2008

Mounting a filesystem on Solaris

It happened once when I got a call on a sunday night which is monday early morning at around 3:30 AM that a database is down and is not coming up when the server was rebooted. I like any other DBA went into the server and tried bringing up the database manually and found an error at the SQL prompt that file cannot be created/filesystem is not in use.

Immediately I had to check with the status of all the filesystems that constitute this database and found about four mount points were missing. It was almost sunday evening in US and I could not even get hold of an SA to bring up the filesystems. This required me to get in there myself and try to fix things, the privilege of root access through sudo was a vital advantage I had.

It is normally the SA who knows the filesystem related parameters to bring up the filesystems with. A person like me who was not really involved when the filesystem was created and who does not even know what parameters to pass for the mount command of UNIX but who only knows the simple syntax of mount is not the appropriate one in such a scenario.

Yet, I gave it a call at my own risk and started looking for ways to get the parameters to pass in the mount command. Where to look at ? was the question I was asking myself. Instantly I remembered the file named /etc/mnttab which has an entry of filesystem type,mounting directory and other arguments specific to the filesystem for all the filesystems.

Let me explain briefly something about the mnttab file which is only accessible to the root user. This is a dynamic file whose contents change as and when a filesystem is mounted/unmounted. Although in my situation the filesystems were not mounted yet I saw an entry for them in the mnttab file, especially the mount options for the filesystem. This could be possible only when the cache might not have been cleared.

Mount options are the fourth field in mnttab file. Mount command takes a number of arguments but the arguments that I used in this scenario were

-F - Takes a value for the filesystem type (ufs/vxfs/nfs..etc.)
-o (lower) - Takes multiple other options for the filesystem type as a comma(,) seperated list

Here is an example of using flags with "mount" command.

UNIX> mount -F ufs -o rw,intr,largefiles,logging,onerror=panic,suid /dev/dsk/c0t0d0s3 /export/oracle

the multiple values following -o flag are explained below.

rw (read/write) - Indicates whether reads and writes are allowed on the file system.

suid (setuid) - Permits the execution of setuid programs in the file system.

intr/nointr - Allows and forbids keyboard interrupts to kill a process that is waiting for an operation on a locked file system.

nologging/logging - controls the logging/nologging behaviour of the filesystem. Logging here means the metadata of the filesystem.

largefiles - Allows for the creation of files larger than 2 Gbytes. A file system mounted with this option can contain files larger than 2 Gbytes.

onerror - Specifies the action that the ufs file system should take to recover from an internal inconsistency on a filesystem. An action can be specified as:

panic — Causes a forced system shutdown. This is the default.
lock — Applies a file system lock to the file system.
umount — Forcibly unmounts the file system.

/dev/dsk/c0t0d0s3 - This is the device to be mounted with a mount point directory.

/export/oracle - Mount point directory

If the mount options are not available to bring up the mount points, it is still easy to bring the mountpoints online with just the device name and mount point directory as shown below. A simple "mountall" also does the job, it brings all the mount points that are listed in a file called /etc/vfstab.

The /etc/vfstab has an entry for every mount point in the system, manually made by the SA himself which denotes that the filesystem has to come online automatically when the system reboots. The below pasted are the methods of mounting filesystems without using any mount point options.

UNIX> mount /dev/dsk/c0t0d0s3 /export/oracle

or

UNIX> mountall

or

UNIX> mountall -l
- Where -l denotes the action to be performed on the local filesystems and not network mapped filesystems.

The above mount command was executed for all the missing filesystems to bring them up with their appropriate mountpoint specific values from the /etc/mnttab file. Just to double check with the newly mounted filesystem either a mount or a df command could be used to see if the filesystems are really up.

Sunday, July 13, 2008

ORA-01031: insufficient privileges

How many times have you come across ORA-01031 error in Windows ?, the error message in itself makes sense only in Windows because the same in UNIX could be resolved with a simple "chown -R" command.

ORA-01031: insufficient privileges

The error puzzles an individual so much that he/she considers changing the user privileges on the very first go, which is one of the steps to resolve this error though. But I had to do something else to resolve it to get into the SQL prompt.

I had to install an Oracle server on my PC and it did get successful with a starter database. I created some schemas, tables and other objects of my own choice then shut the DB down and shut my computer as well when done completely. The next day when I wanted to login to the DB for some more R & D, I get the so called " ORA-01031: insufficient privileges " error.

I was struck dumb for a while as no one else logged into my PC since it went down and I had logged into the system with the usual login id that I use. But then I remembered changing something in the sqlnet.ora file the previous day, I thought of rolling back the changes to make it work and fortunately it worked.

I have always had a habit of commenting "NAMES.DEFAULT_DOMAIN" parameter as I did not want it to bother the connections coming in/going out between different databases on my PC, but along with it I commented something else which was "SQLNET.AUTHENTICATION_SERVICES". The parameter had a value of "NTS" within parentheses. When "SQLNET.AUTHENTICATION_SERVICES" was uncommented and tried again, I was able to login back to the database. The contents of my sqlnet.ora file are as shown below.


# SQLNET.ORA Network Configuration File: E:\oracle\ora91_EE\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.

#NAMES.DEFAULT_DOMAIN = home.com

SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)


Upon doing some more R & D with different PCs having multiple Oracle installations, it was found that following error is also reported ORA-12705: invalid or unknown NLS parameter value specified when the ORACLE_HOME is not set appropriately for a particular ORACLE_SID. Before we proceed any further, we need to understand the reference of ORACLE_HOME taken from two locations.

Location-1
Start -> Programs -> Oracle Installation Products -> Home Selector: This shows a drop down box with the names of Oracle installations and their default ORACLE_HOME path. This has to be an appropriate value for the version of DB you want to work with.

Location-2
Right Click My Computer -> Properties -> Advanced(tab) -> Environmental Variables: There is a section called System Variables which shows up all the system wide environmental variables that are set already, ORACLE_HOME is one among them. Unless ORACLE_HOME is set in here,echo %ORACLE_HOME% from the command prompt does not show the value of ORACLE_HOME. It is observed that this takes precedence over the above mentioned Location 1.

The ORA-12705 error came out of the blue when the ORACLE_HOME for a particular ORACLE_SID was not set correctly but got resolved immediately with the precise value of ORACLE_HOME set in System Variables(Location 2).

There are so many other workarounds linked to resolving ORA-01031 error which an individual applies only when confronted with such situations or by simply poking around at MetaLink.

Tuesday, July 8, 2008

Recursive search for a pattern

I Wish I could use a command "grep -R" like other commands with an -R flag, viz., rm -R,chmod -R and chgrp -R etc.

This is a situation most of us would have come across, when you know a particular file has a specific pattern but you do not know where exactly is the file located. This becomes worse when you have the file about 5 levels down the path, is that easy ??? or make it 10,15,20 levels down.

Let us have an elaborate understanding of this. We are currently at "/user/oracle/dba/tst/tst1/" as the Present Working Directory(pwd).Let us see the files and direcories here with a simple ls -ltra.


HOST : /user/oracle/dba/tst/tst1> ls -ltr
total 6
drwxrwxr-x 3 oracle oracle 512 Mar 3 15:36 ./
drwxrwxr-x 3 oracle oracle 512 Mar 29 20:46 tst2/
drwxrwxr-x 3 oracle oracle 512 Mar 29 20:48 ../


Let us now change to tst2 and do a ls -ltr


HOST : /user/oracle/dba/tst/tst1> cd tst2
HOST : /user/oracle/dba/tst/tst1/tst2> ls -ltr
total 8
drwxrwxr-x 3 oracle oracle 512 Mar 3 15:36 ../
drwxrwxr-x 3 oracle oracle 512 Mar 3 15:36 tst3/
drwxrwxr-x 3 oracle oracle 512 Mar 29 20:46 ./
-rw-rw-r-- 1 oracle oracle 45 Mar 29 20:46 one


Under tst2 there is a file and a directory. This file "one" has the following contents.


HOST : /user/oracle/dba/tst/tst1/tst2> cat one
This is file named one under tst2/ directory


The directory "tst3" can further be navigated to "tst4" which has another directory "tst5", which in turn has a file named "two" and its contents are shown below.


HOST : /user/oracle/dba/tst/tst1/tst2> cd tst3
HOST : /user/oracle/dba/tst/tst1/tst2/tst3> ls -ltr
total 6
drwxrwxr-x 3 oracle oracle 512 Mar 3 15:36 tst4/
drwxrwxr-x 3 oracle oracle 512 Mar 3 15:36 ./
drwxrwxr-x 3 oracle oracle 512 Mar 29 20:46 ../


HOST : /user/oracle/dba/tst/tst1/tst2/tst3> cd tst4
HOST : /user/oracle/dba/tst/tst1/tst2/tst3/tst4> ls -tlr
total 6
drwxrwxr-x 3 oracle oracle 512 Mar 3 15:36 ../
drwxrwxr-x 3 oracle oracle 512 Mar 3 15:36 ./
drwxrwxr-x 2 oracle oracle 512 Mar 29 20:44 tst5/


HOST : /user/oracle/dba/tst/tst1/tst2/tst3/tst4> cd tst5
HOST : /user/oracle/dba/tst/tst1/tst2/tst3/tst4/tst5> ls -tlr
total 6
drwxrwxr-x 3 oracle oracle 512 Mar 3 15:36 ../
drwxrwxr-x 2 oracle oracle 512 Mar 29 20:44 ./
-rw-rw-r-- 1 oracle oracle 46 Mar 29 20:45 two


HOST : /user/oracle/dba/tst/tst1/tst2/tst3/tst4/tst5> cat two


This is file named two under tst5/ directory.

Let us not bother about anything else but the files one, two, their contents and their locations. File "one" is located under "tst2/" directory and file "two" is located about 3 directories down the path under "tst5/" directory.

Now from "/user/oracle/dba/tst/tst1/" directory, we are looking for a file having a pattern "file named" under the current directory and further in the recursive occuring directories. The crux of our exercise is we do not know how far we should go ???. We do not know whether to go about 5 levels down the path or more than that

Say for an instance we have a file sitting about 30 levels down the path, that sure makes me a little jittery if I were to go through each directory and do a grep of the required pattern on all the files. However, I would grin at it and use a simple "find" command to achieve the results as shown below.


HOST : /user/oracle/dba/tst/tst1> find ./ -print -exec grep "file named" {} \;
./
./tst2
./tst2/tst3
./tst2/tst3/tst4
./tst2/tst3/tst4/tst5
./tst2/tst3/tst4/tst5/two
This is file named two under tst5/ directory
./tst2/one
This is file named one under tst2/ directory


To get the exact filename that you are looking for is a little wary job. You get the pattern displayed on the screen with a couple of files above and below the contents of the files. Thank goodness !!! "grep" outputs the pattern which is being looked for like it always does.

Now, the file name just above the pattern is the actual source of the contents. As shown above, "./tst2/tst3/tst4/tst5/two" is the file that has the pattern we are looking for. Besides, file "./tst2/one" is an easy one to know since it as at the end of the output.

USAGE : This method can be used in situations where one needs to find a file with a known pattern that is to be changed for a fix. I have indeed come across this situation quite a lot of times, hope you would have too.

Friday, July 4, 2008

Oracle 9i Database Parameters - Reference

A lot of times we've come across a few init.ora parameters which we are not exactly aware of what it actually does, what is its default value, whether it is static / dynamic and all that jazz. Here is a quick link to parameter descriptions for Oracle 9iR2 and a link to the parameters that can be changed at session level using an ALTER SESSION statement. Here is also a link to the explanation of all the v$ views.

Thursday, July 3, 2008

Inodes and Links

INODES
When a filesystem is created in a UNIX OS, a range of inode numbers are assigned to it. Any file/directory created under this filesystem gets an inode number from the list. So, there is an association of a file/directory with an inode number and an inode provides access to an OS datablock.

In general, inodes contain two parts logically.
First: inodes contain information about the file, including its owner, its permissions, and its size.
Second: inodes contain pointers to data blocks associated with the file.

The first field of a 'ls -li' command output denotes the inode for a corresponding file/directory. No two files/directories can have the same inode numbers even it be from different filesystems. One can assume that a single inode number is a unique number in the whole of OS.

LINKS
A link can be thought of as a pointer from one file to another.The third field of a 'ls -li' command output denotes the number of links created for a file/directory.Links are of two types.

Hard links
Syntax : ln source_filename link_name

Hard link always increment the value of the link field for a particular file/directory. The link number for a file/directory is directly proprotional to the number of hard links created for the particular file/directory

UNIX-$> ls -ltri one
121674 -rwxr-xr-x 1 oracle oracle 184 Feb 22 12:07 one

In the above output the third field is equal to 1. Now, a hard link is created for the file 'one'

UNIX-$> ln one one_1UNIX-$> ls -ltri one*

121674 -rwxr-xr-x 2 oracle oracle 184 Feb 22 12:07 one_1
121674 -rwxr-xr-x 2 oracle oracle 184 Feb 22 12:07 one

The above example shows the filename alone differs, but everything else remains unchanged. Both the files have the same inode number and same number of links for them. This is the only reason why a hard link cannot span across different filesystem, since the linked files all share the same inode number and inode numbers come in a specific range for different filesystems. Removing one of the two files does not affect the existence of the other.

UNIX-$> rm one
UNIX-$> ls -ltr one*

121674 -rwxr-xr-x 1 oracle oracle 184 Feb 22 13:19 one_1

Soft links
Syntax : ln -s source_filename link_name

This is also called a symbolic link but more commonly known as soft link in technical jargon(As far as I know). Soft links do not allow the filenames to share the same inode numbers. A point to be noted about a soft link is it has an 'l' variable in the user permissions field of the 'ls -ltri' command output. Soft links can span across file systems. Depending on the location of the soft link, inode number changes.

If the soft link is created on the same filesystem as that of the source file then it gets an inode number from the range of the current filesystem inodes else it gets an inode from the range of the target file system on which it is created.

UNIX-$> ln -s one_1 one_2
UNIX-$> ls -ltri one*

121674 -rwxr-xr-x 1 oracle oracle 184 Feb 22 13:19 one_1
121644 lrwxrwxrwx 1 oracle oracle 3 Feb 22 13:20 one_2 -> one_1

The above soft link is created in the same location as that of the source file. The one shown below is created in a different filesystem, hence a different inode number range.

846 lrwxrwxrwx 1 oracle oracle 30 Feb 22 13:21 one_3 -> /ora1/oracle/dba/one_1

In both the examples above, the link number is unchaged, but the size differs and also the name of the link has a pointer to the original file. So a soft link only stores a pointer to the original file. However commands like vi,cat,more or pg etc. on 'one_3' or 'one_2' run as if it were executed on the original file.

Removing a soft link is a cautious job, one should not use the softlink name along with the pointer to remove a softlink as it has adverse effects.

UNIX-$> rm one_2 -> one_1

The above command will either remove the source file along with the softlink or it could even make the source file empty. The advisable command to remove a soft link is as shown below.

UNIX-$> rm one_2
UNIX-$> ls -ltri one*

121674 -rwxr-xr-x 1 oracle oracle 184 Feb 22 13:19 one_1

It is hoped that this material provides a simple but effective explanation of Inodes and Links.