Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

Archive for July, 2014

How to reformat corrupt blocks which are not part of any segment?

Posted by Riyaj Shamsudeen on July 11, 2014

There was a question in . Problem is that there were many corrupt blocks in the system tablespace not belonging to any segment. Both DBV and rman throws errors, backup is filling the v$database_block_corruption with numerous rows. OP asked to see if these blocks can be reinitialized. Also, note 336133.1 is relevant to this issue on hand.

$ dbv file=/oracle/SID/system_1/system.data1

DBVERIFY: Release 10.2.0.5.0 - Production on Fri Jul 11 08:04:18 2014

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /oracle/SID/system_1/system.data1
Block Checking: DBA = 67121421, Block Type = Save undo data block
ERROR: SAVE Undo Block Corrupted.  Error Code = 50
kts4subck: record (3) seq# (0), split flag (0)
and total pieces(0)
…
REM Many such corruptions.
select * from v$database_block_corruption order by 1,2,3,4  ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
----- ------ ------ ------------------ ---------
    1  12557      1                  1 UNKNOWN
    1  12561      1                  1 UNKNOWN
    1  12589      1                  1 UNKNOWN
    1  12593      1                  1 UNKNOWN
..

From the output above, blocks belonging to saved undo data blocks, which probably have undo records referring to other data files also. Since these blocks do not belong to any segment (note 336133.1 has a SQL statement to verify that), we should be able to force the allocation of these blocks to a table, load that table with numerous rows, which, in theory, should re-initialize the blocks. That should eliminate rman and dbv errors.

First, let me take a selfie
Read the rest of this entry »

Posted in corruption, Oracle database internals, recovery | Tagged: , | 1 Comment »

Data visualization, px qref waits, and a kernel bug!

Posted by Riyaj Shamsudeen on July 2, 2014

Data visualization is a useful method to identify performance patterns. In most cases, I pull custom performance metrics from AWR repository and use tableau to visualize the data. Of course, you can do the visualization using excel spreadsheet too.

Problem definition
We had huge amount of PX qref waits in a database:

                                            Tota    Wait   % DB
Event                                 Waits Time Avg(ms)   time Wait Class
------------------------------ ------------ ---- ------- ------ ----------
PX qref latch                    63,669,198 3341       0   34.2 Other
DB CPU                                      3031           31.1
direct path read temp                92,996 199.       2    2.0 User I/O
direct path write temp               12,029 109.       9    1.1 User I/O
direct path read                      5,454 61.1      11     .6 User I/O

Read the rest of this entry »

Posted in 11g, 12c, Oracle database internals, Performance tuning, weird stuff | Tagged: , , , , , , , | 5 Comments »

 
Follow

Get every new post delivered to your Inbox.

Join 193 other followers