Wednesday, December 13, 2006

Offline MSWindows Patching

Installing a fresh operating system and connecting it to the Internet to install the latest patches can be risky.

The program available here will download needed patches so they can be put to an ISO CD or DVD. The program even has a simple GUI to choose the download options.

Wednesday, December 06, 2006

Oracle and Tuxedo Transaction Deadlock

Follow these steps to locate the Tuxedo logs of the services that have deadlocked in an Oracle database. Tuxedo service debugging should have already been implemented before the deadlock occurred.

Part of Oracle alert.log:
Mon Dec 4 13:59:36 2006
ORA-000060: Deadlock detected. More info in file /u103/oracle/admin/DB/udump/db_ora_1567.trc.

Optionally confirm time of deadlock:
$ grep 'SESSION ID' db_ora_1567.trc
*** SESSION ID:(176.15362) 2006-12-04 13:59:27.820

Find the Tuxedo program name and machine names. Replace "host" with a string that matches your application server host names.
$ grep host db_ora_1567.trc
Node name: dbhost
Unix process pid: 1567, image: oracle@dbhost (TNS V1-V3)
O/S info: user: arbor, term: , ospid: 10159, machine: app018
program: om_Customer@app018 (TNS V1-V3)
application name: om_Customer@app018 (TNS V1-V3), hash value=0
OSD pid info: Unix process pid: 1567, image: oracle@dbhost (TNS V1-V3)
O/S info: user: arbor, term: , ospid: 12110, machine: app020
program: om_OrderProces@app020 (TNS V1-V3)
application name: om_OrderProces@app020 (TNS V1-V3), hash value=0

On machine app018 go to Tuxedo service log directory:
$ cd log

Find the files with the service and time that were in the database deadlock trace file:
$ ls -otr om_Customer* om_OrderProces*|tail -9
-rw-r--r-- 1 arbor 3733 Dec 4 00:01 om_OrderProcessing-061204-000033.03.10169.server.sql00100
-rw-r--r-- 1 arbor 303300 Dec 4 00:01 om_OrderProcessing-061204-000026.03.10705.server.001
-rw-r--r-- 1 arbor 3378 Dec 4 12:10 om_Customer-061204-120950.03.10159.server.sql00100
-rw-r--r-- 1 arbor 46855616 Dec 4 19:57 om_OrderProcessing-061204-000003.03.10169.server.dbg
-rw-r--r-- 1 arbor 5525130 Dec 4 19:57 om_OrderProcessing-061204-000003.03.10169.server.001
-rw-r--r-- 1 arbor 882855649 Dec 4 19:57 om_Customer-061204-012635.03.10159.server.dbg
-rw-r--r-- 1 arbor 259093081 Dec 4 19:57 om_Customer-061204-012635.03.10159.server.001
-rw-rw---- 1 arbor 3017100 Dec 4 19:57 om_OrderProcessing.3.err
-rw-rw---- 1 arbor 324921588 Dec 4 19:57 om_Customer.3.err

These tuxedo log files along with the database deadlock trace file can be used to determine the cause and potential resolution of the deadlock condition.

Tuesday, December 05, 2006

Oracle alert.log "ORA-" Monitoring

Oracle DBAs often have a purchased tool or home-grown scripts to monitor the Oracle alert.log for errors.
DBAs will often search the alert.log for lines that begin with "ORA-", even though not all ORA- messages are database errors (user cancelling a long-running will query will create a ORA- message but this is not a database error).

Aside from the fact that all ORA- messages are not errors, not all errors are identified with ORA-. This is a list of some of the messages that do not begin with ORA- that a DBA may be interested in monitoring.

Fri Oct 27 14:35:43 2006
Failure to extend rollback segment because of 30036 condition
Failure to extend rollback segment because of 30036 condition
Failure to extend rollback segment because of 30036 condition


Thu Oct 26 07:11:29 2006
Failure to extend rollback segment 22 because of 30036 condition
FULL status of rollback segment 22 set.


Tue Oct 10 09:57:03 2006
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump
PMON failed to acquire latch, see PMON dump


These two messages may be seen once when the instance is starting:
Deprecated system parameters with specified values:

Oracle instance running on a system with low open file descriptor limit. Tune your system to increase this limit to avoid severe performance degradation.


The number 30036 means ORA-30036.
Failure to extend rollback segment 8 because of 30036 conditionFULL status of rollback segment 8 set.

There are a couple ORA- messages for when the archive log destination is full.
Wed Jan 31 21:58:41 2007
ARC1: Evaluating archive log 5 thread 1 sequence 11273
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive log 5 thread 1 sequence 11273
ARCH: Archival stopped, error occurred. Will continue retrying
Wed Jan 31 21:58:41 2007
ORACLE Instance SID - Archival Error
ARCH: Connecting to console port...
Wed Jan 31 21:58:41 2007
ORA-16014: log 5 sequence# 11273 not archived, no available destinations
ORA-00312: online log 5 thread 1: '/u101/oradata/SID/redo05a.log'
ARCH: Connecting to console port...
ARCH: