Sokrates on Oracle

Archive for the ‘dba’ Category

If you want to sleep well …

Posted by Matthias Rogel on 25. January 2012

… and you are a DBA and you use ASM and you happen to run 11.2 ( but not yet ) …

then, you probably want to apply patch 10422126  ( see and MOS Doc ID 1274852.1 )

I ( and our storage admin ) didn’t sleep well last night 😦

The huge number of logging entries Svetoslav is talking about started for us at 6 pm yesterday, generating about 500 MB / minute.

WARNING: Read Failed. group:2 disk:1 AU:98945 offset:1474560 size:8192
 incarnation:0xe9508f84 synchronous result:'I/O error'
 subsys:System iop:0x2b54bc0f8390 bufp:0x2f41b4000 osderr:0x434c5344 osderr1:0x0

At midnight, around 180 GB was written, so our partition holding the alert log was full. New Connections received ORA-03113.

No signs of any error in the alert of ASM, no signs of any error from the storage, no signs of weird behaviour of the database (beside extremely logging the same error over and over again)

We cleaned all log- and alert-directories, restarted the database and everything looks ok again (like the last 5 months)

Tomorrow, we will apply patch 10422126.

Posted in 11.2, bugfighting, dba | Leave a Comment »

V$BLOCKING_QUIESCE and the difference between killing and disconnecting

Posted by Matthias Rogel on 5. January 2012

Testing how to quiesce an instance today:

SID 94 > alter system quiesce restricted;
(15 minutes ago on test-db 11.2 )
Ok, it waits for several sessions to finish, right ?
( see Doc
Oracle Database waits for all existing transactions in all instances that were initiated by a user other than SYS or SYSTEM to finish (either commit or abort). Oracle Database also waits for all running queries, fetches, and PL/SQL procedures in all instances that were initiated by users other than SYS or SYSTEM and that are not inside transactions to finish. … )

So, we queried V$BLOCKING_QUIESCE and killed all sessions listed there via
alter system kill session ....
and saw them disappear in V$BLOCKING_QUIESCE thereafter.

However, after killing the last one we expected SID 94 to finish the quiesce – command, but: it didn’t.

SQL > select * from v$blocking_quiesce;

no rows selected

SQL > select sw.event
2 from v$session_wait sw
3 where sw.sid=94;

wait for possible quiesce finish

… for several minutes.

bouncing and restarting the instance and repeating the same experiment but using

alter system disconnect session .... immediate

rather than

alter system kill session ....

gave the expected result.

So, when we want to quiesce our instance we’ll use this algorithm.

(We’ll do that only on a  weekend maintenance windows when no interactive user is connected, all batch jobs running will be restartet automatically by our scheduling system when system is unquiesced again.)

But: seems to be a bug to me that V$BLOCKING_QUIESCE showed no entry though quiescing was apparently blocked for minutes.

Posted in 11.2, Bug, dba | Leave a Comment »