2011-09-30

how to see status of rollback for smon

select sysdate,b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn
xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks
from x$ktuxe a, undo$ b
where a.ktuxesta = 'ACTIVE' and a.ktuxecfl like '%DEAD%'
and a.ktuxeusn = b.us#;

2011-09-28

ORA-29770: global enqueue process LCK0

ORA-29770 LMHB Terminates Instance as LMON Waited for Control File IO or LIBRARY CACHE or ROW CACHE Event for too Long [ID 1197674.1]

how to free filesystem cache in Linux

echo 1 > /proc/sys/vm/drop_caches ( as of 2.6 kernel )
http://mtj.wordpress.com/2006/10/18/clearing-linux-filesystem-read-cache/

2011-09-26

you don't need RAC

http://www.my-idconcept.de/downloads/You_Probably_Dont_Need_RAC.pdf
RAC is very cool technology. But it’s expensive.
So let’s play around with Larry’s vision of cheap Intel-based Linux clusters. Let’s buy those two cheap, 4-cpu Intel boxes and put them together in a cluster with Oracle9i and RAC on top:
Price for the hardware: About US$15.000,- or so .
Price for the OS (Linux): About US$0.50,- or thereabout (it depends!)
Price for Oracle w/ RAC: US$480.000,-

So that’s half a million to Oracle. Put another way: It’s 1 dollar to the box movers for every 32 dollars Oracle gets.
Psychologically it’s hard for the customers to understand that they have to buy something that expensive to run on such cheap hardware. The gap is too big, and Oracle will need to address it soon.

There’s nothing like RAC on the market, but that doesn’t mean you have to buy RAC. I usually joke that it’s like buying a car for US$10.000,- that has all the facilities you need from a good and stable car. Airbags and ABS brakes are US$500.000,- extra, by the way. Well, airbags and ABS are wonderful to have and
they increase your security. But it’s a lot of money compared to the basic car price.

2011-09-23

slow datapump export in 10.2.0.4

Export Slow After Applying 10.2.0.4 Patchset On Top Of 10.2.0.3 [ID 783835.1]

Symptoms

Export is slow after applying patchset 10.2.0.4.0 on top of 10.2.0.3.

An export (exp) that previously took approximately 8 hours now takes 2 days after applying the patchset 10.2.0.4.0.

Cause

The cause of slow performance is incorrect statistics.

You have to narrow down where the possible bottlenecks are by tracing the exp process:

-- Determine SPID of export process
select distinct (p.spid), s.sid, s.serial#
from   v$process p, v$session s, v$mystat m
where  s.PADDR = p.ADDR and lower (s.program) like '%exp%';

-- connect to the process as sysdba
connect / as sysdba
oradebug setospid xxxx <-- use SPID here
oradebug unlimit

-- Create process state dumps
oradebug dump errorstack 3
... wait 30 seconds
oradebug dump errorstack 3
... wait 30 seconds
oradebug dump errorstack 3

This generates a trace file in your user_dump_dest (from sqlplus: show parameter user_dump_dest).

When reviewing the file you saw the following:

Current SQL statement for this session:
SELECT CNO, CNAME, TYPE, CONDITION, CONDLENGTH, ENABLED, DEFER, INAME, IDXSYSGEND FROM SYS.EXU8CON WHERE OBJID = :1 AND TYPE IN (1, 2, 3, 7, 11, 12, 14, 15, 16, 17) ORDER BY CNO
Also, from the RDA, the AWR report seems to indicate that there is a lot of CPU time spent on
querying constraints.  Specifically, the following query:

SELECT CNO, CNAME, TYPE, CONDITION, CONDLENGTH, ENABLED, DEFER, INAME, IDXSYSGEND FROM SYS.EXU8CON WHERE OBJID = :1 AND TYPE IN (1, 2, 3, 7, 11, 12, 14, 15, 16, 17) ORDER BY CNO
You can use the trace file generated from the "oradebug dump errorstack 3" to get the bind variable for the above query.  Search the trace file for "Current cursor": Current cursor: 57, pgadep: 0 ...and then search on "Cursor#57"

Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=800003ffefcf2ec0 bln=22 avl=04 flg=05
value=535520 <---- This is your Bind Variable's value.

Now you can determine how long it is taking to complete that query:

SQL> set pages 1000 lines 132
SQL> set autotrace on
SQL> SELECT CNO, CNAME, TYPE, CONDITION, CONDLENGTH, ENABLED, DEFER, INAME, IDXSYSGEND FROM SYS.EXU8CON WHERE OBJID = 535520 AND TYPE IN (1, 2, 3, 7, 11, 12, 14, 15, 16, 17) ORDER BY CNO ;

That should tell you how long it takes to to complete that query.

Solution

Workarounds:

1.  Export with constraints=n

Or:

2. Gather dictionary stats again and run the query again.

SQL> exec dbms_stats.gather_dictionary_stats;

SQL> set pages 1000 lines 132
SQL> set autotrace on
SQL> SELECT CNO, CNAME, TYPE, CONDITION, CONDLENGTH, ENABLED, DEFER, INAME, IDXSYSGEND
     FROM SYS.EXU8CON
     WHERE OBJID = 535520 AND TYPE IN (1, 2, 3, 7, 11, 12, 14, 15, 16, 17)
     ORDER BY CNO ;

Compare times for query to run before and after statistics are gathered.

11.2.0.3 is here

11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER is available for linux x86 and linux x86-64

See patch 10404530 on metalink

2011-09-21

TRAILLING "/" IN ORACLE_HOME DURING CRS PATCH CAUSING INSTANCE ACCESS ISSUE

Bug 6894855 TRAILLING "/" IN ORACLE_HOME DURING CRS PATCH CAUSING INSTANCE ACCESS ISSUE

2011-09-20

fast_start_parallel_rollback

DATABASE HANG DUE TO PARALLEL TRANSACTION RECOVERY [ID 464246.1]

2011-09-16

filesystemio_options and disk_asynch_io

What do filesystemio_options and disk_asynch_io Do?

disk_asynch_io is a kind of master switch, which turns on or off Async I/O to database files on any type of storage, whether it's raw device or filesystem. The filesystemio_options parameter gives finer control over I/O to database files on filesystems. It allows you to turn off async I/O to filesystem files but keep async I/O to raw devices if the "master" switch disk_asynch_io is set to true.

Instance initialization parameter filesystemio_options has four options:
1. "asynch" : means buffered I/O + Async I/O
2. "directIO" : means Direct I/O only
3. "setall" : means Direct I/O + Async I/O
4. "none" : disables Async I/O and Direct I/O

One should always use at least Direct I/O with OCFS/OCFS2. In fact one does not have choice as the database automatically adds that mode whenever it sees the file is on an OCFS volume or OCFS2 volume mounted with the datavolume mount option.
If the user wants aio with OCFS/OCFS2, use setall.
If the user wants aio with ASM/ASMlib, he is expected to set disk_asynch_io=true, this is because ASM bypasses the filesystem layer in this case and ASM I/O is entirely controlled by DISK_ASYNCH_IO parameter. AIO needs to be enabled/disabled by setting disk_asynch_io to parameter values TRUE/FALSE, please see Note 413389.1 .

kksfbc child completion

Session or job spins on 'kksfbc child completion' wait [ID 1354066.1]

2011-09-13

ORA-24247

ORA-24247 Trying To Send Email Using UTL_SMTP from 11gR1 (11.1.0.6) or higher [ID 557070.1]

less rights when running oracle procedures

stored procedures run with the base privs of the OWNER of the procedure 

2011-09-12

ASM and FILESYSTEMIO_OPTIONS + DISK_ASYNCH_IO

ASM Inherently Performs Asynchronous I/O Regardless of filesystemio_options Parameter [ID 751463.1]

direct I/O for Oracle Databases

Pros and Cons of Using Direct I/O for Databases [ID 1005087.1]


meet db2

http://www.ibm.com/developerworks/data/library/techarticle/dm-0907oracleappsondb2/ 
http://www.ibm.com/developerworks/data/library/techarticle/dm-0401gupta/

specific

"Unii dintre noi sunt zidari, alţii sunt avocaţi şi încă alţii sunt ingineri sau prestidigitatori. Fiecare însă are felul său de a lucra, propria scară de performanţă şi propriile repere şi etaloane, specifice domeniului.
Atunci când eşti cu adevărat bun în ceva îţi dai seama cât de puţin şti de fapt şi cât mai ai de învăţat (pe de o parte); lucrezi cu pasiune şi cu dăruire la a ajunge mai bun şi la a învăţa de la alţii (pe de altă parte).
Atunci când eşti bun, pui pasiune în ceea ce faci, faci cu dragoste ceea ce faci şi asta se simte în fiinţa ta şi, cu timpul, constaţi că ajungi să pui pasiune şi dragoste în tot ceea ce faci."

by Radu Mihailescu

2011-09-07

oracle and emc recoverpoint

white paper : http://levipereira.files.wordpress.com/2011/06/recoverpoint_oracle_db_protect_wp.pdf

ro

s-a facut un studiu si romanii sunt printre cei mai nefericiti, aprox 80% s-au declarat nefericiti..suntem la acelasi nivel cu tari unde foametea omoara multi sau au un regim dictatorial.
Romanii nu au suficiente scuze pentru atata nefericire...
Noi ne consideram nefericiti ca nu avem apartamente/case si masini frumoasa ca aia pe care ii vedem la televizor. Oricat am avem nu ne mai ajunge si vrem tot mai mult, de parca de acolo ar veni fericirea...

problem with impdp and jobs

IMPDP doesn't remap Database Jobs to new User Schema [ID 1122424.1]

2011-09-06

Cannot find a rule to create target install from dependencies

"Relink All" On AIX5L Reports "Make: 1254-002 Cannot Find A Rule To Create Target Install From Dependencies" [ID 602079.1]

runcluvfy hangs at timezone

./runcluvfy.sh stage -pre crsinst -n node1,node2 -fixup -verbose

hangs while checking for time zone syncronization:

Check: Time zone consistency
<<hang>>


From our past experience, we have noted that 11.2 install/upgrade hangs at "Check: Time zone consistency" if user installing Grid Infrastructure has entered an interactive shell in the profile script.
Please check the profile script for the O/S user oracle or grid for anything interactive and disable it.

If you are unsure about anything interactive then please upload the login shell profile for O/S user oracle or grid and we will check it.
Problem was solved by commenting the 'IF' statement and the 'bash' command in file '.profile'. Thanks! You may close the SR.

2011-09-04

ORA-30926

How to Troubleshoot ORA-30926 Errors? [ID 471956.1]