olsnodes hangs!!!

Alright, our SA team wanted to change the SAN storage and had to reboot the servers. Once the RAC servers got rebooted, it wont let me get to the prompt when trying to login with Oracle user.

After debugging a while, I found that its due to . oraenv in the .profile thats halting my login. So, removed . oraenv with in the .profile and loged in.

Now, its time to digg in to see why . oraenv is halting.

I had my own peice of code kept in that oraenv file to get the node# by using olsnodes command and thats where its halting the process!!!

Strange isnt it. Generally olsnodes command should return the number of nodes in the RAC in seconds. When I run olsnodes it just hangs and never comes back.

Found this metalink id: 729349.1. !!!an unpublished bug#6004127!!!

Basically, what its doing is going thru all the log files under $CRS_HOME/log//clients/ directory to create a new css*.log file and my box had too many to halt the command :)

So, remove all those log files from that folder and olsnodes works like a champ...

Long running Update SQL

Not the first time that I have done such thing(s) but this time I decided to write it here.

There was this update statement which was written quite efficiently to update a table rows by matching with another table entries. The other table will have the original rowid of the main table that's being updated (basically need to modify the main table column type so had to create a temp table with all the records for those columns and stored the rowid also to make the later update faster).

So, when the table ddl operation is done, the update statement triggers and even with the rowid search criteria the update was taking more than one hour (killed the process after an hour).

So, made that as a pl/sql block by updating a single row at a time in a loop and it finishes in less than a minute.

So, pl/sql block to update a table is not bad as every body thinks. Of-course its like a small code rather than a single SQL but its sure very efficient as the single update statement has to do all searching, matching and updating in the memory where as the pl/sql block don't have to deal all of the records at once.

update sql:

update table1 a

set (a.col1, a.col2) = (select b.ncol1, b.ncol2

from temp_table1 b

where a.rowid = b.org_rowid)

where exists (select 1 from temp_table1 where a.rowid = org_rowid);

pl/sql Block:

begin

for i in (select org_rowid, ncol1, ncol2 from temp_table1) loop

update table1

set col1 = i.ncol1, col2 = i.ncol2

where rowid = i.org_rowid;

end loop;

end;

Hope this might help to some1.

I really appreciate if there is any better of handling this.