Tracing oracle session to identify errors in query

Sometimes, you need to trace an oracle session, in order to identify a failing query.
After you enable the trace, most often using the package dbms_monitor.session_trace_enable, you'll see something similar to below extract from the generated raw trace file (before running tkprof):



PARSING IN CURSOR #128 len=249 dep=0 uid=548 oct=6 lid=548 tim=1366217870145327 hv=4176214642 ad='c9f69fae0' sqlid='bs24wq7wfrymk'
update TEST1  set SYS_CREATION_DATE=SYS_CREATION_DATE,SYS_UPDATE_DATE=SYSDATE,OPERATOR_ID=:b0:b1,APPLICATION_ID=:b2:b3,DL_SERVICE_CODE=:b4,DL_UPDATE_STAMP=:b5:b6,AUTO_GEN_PYM_TYPE=:b7,BL_BILL_METH_DATE=TO_DATE(:b8,'YYYYMMDD') where BAN=:b9
END OF STMT
PARSE #128:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1739222879,tim=1366217870145325
EXEC #128:c=0,e=128,p=0,cr=3,cu=1,mis=0,r=0,dep=0,og=1,plh=1739222879,tim=1366217870145508
ERROR #128:err=1841 tim=1366217870145530

So now we have the failing DB statement and the error number.
All is left is to run "oerr", to identify the error:

>oerr ora 1841

01841, 00000, "(full) year must be between -4713 and +9999, and not be 0"
// *Cause: Illegal year entered
// *Action: Input year in the specified range

How to validate oracle redo/archived logs? ( Oracle 11g R1)

Recently we've learnt that one of our archived redo logs was corrupted (by sending it to a third party tool, CDC, which could not use it).
The question was, is there a way to validate the archived redo log?

The answer is yes and the method is:


SQL> alter system dump logfile  '/u01/app/oracle/Dmp/TESTDB_1799337628_4642.arc';
alter system dump logfile  '/u01/app/oracle/Dmp/TESTDB_1799337628_4642.arc'
*
ERROR at line 1:
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 1822722 change 10154296873806 time
04/17/2013 03:14:22
ORA-00334: archived log: '/u01/app/oracle/Dmp/TESTDB_1799337628_4642.arc'


This command will generate a detailed trace file in the user dump destination.

We could even generate a smaller trace file, having the error inside, by running the same command, but on a block range:


SQL> alter system dump logfile  '/u01/app/oracle/Dmp/TESTDB_1799337628_4642.arc' dba min 4642 1822700 dba max 4642 1822800;
alter system dump logfile  '/u01/app/oracle/Dmp/TESTDB_1799337628_4642.arc' dba min 4642 1822700 dba max 4642 1822800
*
ERROR at line 1:
ORA-00368: checksum error in redo log block
ORA-00353: log corruption near block 1820688 change 10154296880830 time
04/17/2013 03:14:24
ORA-00334: archived log: '/u01/app/oracle/Dmp/TESTDB_1799337628_4642.arc'

 More details on My Oracle Supprt, ID 1031381.6

Startup fails with ORA-27154: post/wait create failed

The other day I was getting a strange message trying to bring up one of our Oracle 11g databases; on the same box, we have about 25 instances:


SQL> startup
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates

The error messages in this case are misleading, in my case there was no disk space issue. Many articles point in the direction of a issue with the number of UNIX semaphores in the system.

In my case, by decreasing the PROCESSES initialization parameter to 200, down from 800, solved the issue and the database came up.

Oracle 11G: How to disable the default scheduler maintenance Window for good?

Some workshops go by canceling the scheduler default scheduler maintenance window and prefer to manage  statistics manually, by this having more control; besides, they don't want to overload the machine at any time.

So the run:

SQL>ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = '' SCOPE=BOTH;

Surprisingly, the alert log shows that the maintenance window keeps being activated, see sample of excerpt:

Setting Resource Manager plan SCHEDULER[0x2C44]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Solution:

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:' scope=both;

After this, the maintenance plan default window is gone, for good.

How to print current line and next line at once, using "awk" ?

A bit of what I call "awk magic" :-)

I have a simple text file, like this:


$cat my_file.txt

1
2
3
4
5
6

 My desire output is like this:

1 2
3 4
5 6

Solution:


awk '{print $0 p; p=" "$0}' my_file.txt|awk -F" " '{print $2"  "$1}'
  1
1  2
2  3
3  4
4  5
5  6



We need to get rid of the 1st line ; the 2nd awk was needed to reverse the order of the 2 elements returned by awk utility.

This may be helpful sometimes, when you have a list of numbers and need to prepare like a script, which will take as parameters ranges.

How to trace "ORA-00942: table or view does not exist"

Is happening sometimes that either a third party application or a developer script/application is failing with:
ORA-00942: table or view does not exist
The question is, how to identify the "missing" table, without enabling some intensive tracking?
The answer is by enabling event 00942, as in the below example:



SQL> ALTER system SET EVENTS='00942 TRACE NAME ERRORSTACK FOREVER, LEVEL 1';

System altered.

The next step will be to ask to re-run the application and then identifying the error and the trace file name inside the alert.log.

After the problematic statement is identified, it is a good idea to disable the event:

SQL> ALTER system SET EVENTS='00942 TRACE NAME ERRORSTACK OFF';

System altered.

This is just an example of an error, this method will work for many other error messages.

UNIX "find" command: how to avoid "permission denied" error messages?

Sometimes, when we use "find" UNIX command to locate a file, by its name for example, we are getting a lot of  "permission denied" messages, and it's very hard to analyze the output. This is true especially when we search starting with "/", meaning that we will look through all the directories on the box.

A quick fix is to redirect these messages to to /dev/null and hence getting a clean and readable output, as below:


[host1]/ >find . -type f -name "my_file"  2>/dev/null
./usr/florin/Test_Dir/my_file