Tracking Down Oracle Deadlocks

The Dreaded ORA-00060 error. DEADLOCK DETECTED Fortunately this is not an Oracle problem, but is an application problem. Now the fun part of tracking it down.

1. Locate the Trace file so you can gather some details.

2. Find the resource names in the Trace file…

Resource Name        
TM-00014ef4-00000000 
TM-00014ef4-00000000 

Hopefully this will give you some relavant object information. This will allow you to query the all_objects table looking for the objects.

3. Query the all_objects table

select object_name, object_type from all_objects
where object_id in ( to_number( '14ef4', 'xxxxx' ), to_number( '14ef4', 'xxxxx' ) );

From here you get to do some more digging. The trace file will contain a lot of information, mostly cryptic but hopefully it can help point you in the right direction.

makewhatis

So that took longer than it should. There is a command called whatis that gives you a single line description of a command:

whatis grep

grep (1) - print lines matching a pattern
grep (1p) - search a file for a pattern
grep (rpm) - The GNU versions of grep pattern matching utilities

A nice little tool to use. I just installed CentOS 5 in a VM and noticed that all my whatis commands were returning nothing appopriate…I quickly found the makewhatis command on some articles but could not locate it for CentOS 5 at first. Finally found the location:

/usr/sbin/makewhatis

This command builds the database and now my whatis commands are working.

Un-expire Oracle User account

There is no real un-expire option for an Oracle user account. Say you have an account that goes into expire status…you have to alter the user using the identified by clause to set the password. Problem is if you do not know the original password you have to change it…this could cause big problems.

However here is a cool workaround to remedy the problem:

First find the user int he sys.user$ table using this query:

select password, spare4 from sys.user$ where name='USERNAME';

You should see some results similar to below:

PASSWORD-1FDF39535EDB8EDD
SPARE4-S:5BEFDD3B650E6910F42EAB51EBDFF612205E92620B56166AF480551F41B6

Now take the results and run the ALTER statement on the user: 

alter user USERNAME identified by values 
'S:5BEFDD3B650E6910F42EAB51EBDFF612205E92620B56166AF480551F41B6;1FDF39535EDB8EDD'; 

You concatenate the password field and the spare4 field with a semicolon in between.

Using PS to find resource intensive processes

#Top (X) Memory Consumers
#just change the head command to see more, this will show you top 10 

ps -auxf | sort -nr -k 4 | head -10

#Top (X) CPU Consumers
#just change the head command to see more, this will show you top 10

ps -auxf | sort -nr -k 3 | head -10

Linux uptime and version info

#uptime:

uptime

output: current time, how long system has been running, how many users are logged in, system load averages for past 1, 5, and 15 minutes

#Version info

uname -a
cat /etc/redhat-release
cat /etc/issue

df output format CSV

# File size on your drives in CSV format
# no header on the DF command, append the date to each row
# good for throwin in Excel and doing some trending.

df -Pk | awk '{print $1,$2,$3,$4,$5,$6,strftime("%Y-%m-%d")}' OFS="," | sed -e 1d