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#;
You've got to have a dream. When you lose your dreams, you die.
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/
http://mtj.wordpress.com/2006/10/18/clearing-linux-filesystem-read-cache/
2011-09-27
problems with system statistics in 11GR2
Bug 9842771 appears as solved in 11.2.0.3 .
http://antognini.ch/2010/11/workload-system-statistics-in-11g/
http://antognini.ch/2010/11/workload-system-statistics-in-11g/
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.
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]
An export (exp) that previously took approximately 8 hours now takes 2 days after applying the patchset 10.2.0.4.0.
You have to narrow down where the possible bottlenecks are by tracing the exp process:
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:
querying constraints. Specifically, the following query:
Now you can determine how long it is taking to complete that query:
That should tell you how long it takes to to complete that query.
1. Export with constraints=n
Or:
2. Gather dictionary stats again and run the query again.
Compare times for query to run before and after statistics are gathered.
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 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
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.
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
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
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 .
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]
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
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
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...
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...
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.
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.