Amazon Deals

Wednesday, May 21, 2014

NFS mounted file system owned by nobody:nobody

This can happen if there is a mismatch between how NFS export was created and how it is mounted in your machine. In one situation we encountered the NetApp volume was exported as NFS version 4.0 but the /etc/fstab in Linux server was configured with NFS version 3. When the volume was mounted in Linux, the folder was owned by nobody:nobody and you could not use chown to change it.

The solution was to export the volume from NetApp as NFS version 3 and remount it in Linux.

Tuesday, May 6, 2014

Informatica Session Log - LM_2006 Unable to create log file

An Informatica job failed due to failure to create a session log file. The NFS mount point that hosted the SessLog folder had plenty of space. There was no problem touching a file on the folder as Informatica user, so there was no issue with the permission.

We discovered that we could create files with shorter name but if the file name is longer than 70 characters, it failed. It did not make sense as the NFS mount was on ext3 file system which allowed file names to be 255 characters long.

Upon further investigation we found that session log folder had over a million files. We deleted half the number of files as they were old log. Informatica was happy about it and completed the failing jobs without any issue.

A Linux file system uses inodes to manage files. However running df -hi showed only 20% utilization of inodes, so it is still a mystery to us.

You can use this command to get details on number of inodes

# tune2fs -l /dev/mapper/osvg-rootlv | grep Inode

Monday, April 7, 2014

SQL Server Error: 1222 - Lock request time out period exceeded

This is one annoying error which prevents you from doing anything on SQL Server Management Studio.

When you work on Management studio, your actions may create temporary tables, call number of system stored procedures and if there are any other actions happening on the database your management studio connection may be blocked. After sometime you will get this error.

Old version of SQL Server enterprise manager would simply freeze, so this is kind of an improvement.


In one situation, I had a script running which failed. The script had many DDLs and DMLs interspersed, so when I started to look into changes that were made so far, I was hit with this error.

To come out of this, I simply ran a ROLLBACK on the last query window. The reason I hit this one was due to a bug in the script which created a nested transaction and there was no corresponding COMMIT or ROLLBACK. The script terminated with an error and one OPEN TRANSACTION.

The problem was GO statement after BEGIN TRANSACTION like

BEGIN TRANSACTION
DELETE dbo.ApplicationTable WHERE AppID = 3
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.ApplicationTable ADD Note Char (255)
UPDATE dbo.ApplicationTable set Note='Nothing'

Running SELECT @@TRANCOUNT would return 1. The developer intended to put GO after COMMIT, but had it after BEGIN TRANSACTION.
































Monday, March 31, 2014

SQL Server ALTER AUTHORIZATION vs ALTER SCHEMA

The goal is to change the schema of a table from dbo to TestUser.

In pre-SQL Server 2005 world I could have used sp_changeobjectowner to change owner of the table. However with SQL Server 2005, the concept of SCHEMA and OWNER comes into picture.

SCHEMA and OWNER may sound like the same thing and you may expect to behave same but they are not.

For example when I run this statement

ALTER AUTHORIZATION ON dbo.TestTable TO TestUser

The TestTable is still in dbo schema, however TestUser becomes the owner with all privileges on the table. You can see the privileges TestUser has by running

EXECUTE AS USER='TestUser';
  SELECT * FROM sys.fn_my_permissions('dbo.TestTable','OBJECT');
REVERT;


But if you check the sys.sysobjects table the TestTable is still under dbo schema.

select name, USER_NAME(uid) from sys.sysobjects where name='TestTable'

To move the table to TestUser schema, you will have to use ALTER SCHEMA command as

ALTER SCHEMA TestUser TRANSFER dbo.TestTable

Please keep in mind that when you change the object owner, any other users who had GRANTs on that object will be revoked.

Thursday, March 27, 2014

ORA-01031 Insufficient Privilege when using Toad







You can get ORA-01031 for valid reason if you do not have SYSDBA or SYSOPER privilege and you try to connect to a database as those user type. 




However I ran into this error using Toad 11.6 64-bit on Windows 7 PC when connecting as Normal user. The difference here was I could click OK button and it would open Toad. I could not figure out what was going on. I had another PC with Toad 10.6 that worked fine with the same user and database.

Upgrading to version 12.1 fixed the issue. The database I was trying to connect to was 12c as well, not sure if this error is only happening on 12c with Toad 11.6. Since I already upgraded I can't tell now.