欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

ORACEL坏查询对象批量脚本

程序员文章站 2024-04-06 14:09:01
...

查询坏块 SQL set lines 120SQL select * from v$database_block_corruption; FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO ---------- ---------- ---------- ------------------ --------- 3 35418 1 0 FRACTURED 3 61344 1 0 FRACTURED 3 31065 1 0

查询坏块

SQL> set lines 120
SQL> select * from v$database_block_corruption;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO      
---------- ---------- ---------- ------------------ ---------      
         3      35418          1                  0 FRACTURED      
         3      61344          1                  0 FRACTURED      
         3      31065          1                  0 CORRUPT        
         3      36673          1                  0 CORRUPT        
         3      36721          1                  0 CORRUPT        
         3      42881          1                  0 CORRUPT        
         1      66738          1                  0 CORRUPT        
         3      36329          1                  0 CORRUPT        
         3      36617          1                  0 CORRUPT        
         3      32404          1                  0 FRACTURED      
         3      36281          1                  0 FRACTURED      
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO      
---------- ---------- ---------- ------------------ ---------      
         3      36625          1                  0 FRACTURED      
         1      39041          1                  0 CORRUPT        
         3      36713          1                  0 CORRUPT        
        10      69927          1                  0 FRACTURED      
        26      94244          1                  0 CORRUPT        
已选择16行。

查询坏块对应对象

SQL> set pagesize 2000
SQL>          set linesize 250
SQL>      SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2       , greatest(e.block_id, c.block#) corr_start_block#
  3       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4       , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5         - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6       , null description
  7    FROM dba_extents e, v$database_block_corruption c
  8   WHERE e.file_id = c.file#
  9     AND e.block_id = c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13       , header_block corr_start_block#
 14       , header_block corr_end_block#
 15       , 1 blocks_corrupted
 16       , 'Segment Header' description
 17    FROM dba_segments s, v$database_block_corruption c
 18   WHERE s.header_file = c.file#
 19     AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22       , greatest(f.block_id, c.block#) corr_start_block#
 23       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 24       , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25         - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 26       , 'Free Block' description
 27    FROM dba_free_space f, v$database_block_corruption c
 28   WHERE f.file_id = c.file#
 29     AND f.block_id = c.block#
 31  order by file#, corr_start_block#;
OWNER            SEGMENT_TYPE       SEGMENT_NAME                 PARTITION_NAME                      FILE# CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
---------------- ----------------- ----------------------- ------------------------------- ------ ----------------- --------------- ---------------- --------------      
SYS              TABLE              OBJ$                                                        1             39041           39041                1                     
                                                                                                1             66738           66738                1 Free Block          
SYSMAN           INDEX              MGMT_METRICS_1HOUR_PK                                       3             31065           31065                1                     
SYS              TABLE              WRH$_SQL_BIND_METADATA                                      3             32404           32404                1                     
SYS              TABLE              WRH$_BG_EVENT_SUMMARY                                       3             35418           35418                1                     
SYS              INDEX PARTITION    WRH$_FILESTATXS_PK      WRH$_FILEST_1232289473_41482        3             36281           36281                1                     
SYS              TABLE PARTITION    WRH$_SYSTEM_EVENT       WRH$_SYSTEM_1232289473_41482        3             36329           36329                1                     
SYS              TABLE PARTITION    WRH$_SGASTAT            WRH$_SGASTA_1232289473_41482        3             36617           36617                1                     
SYS              INDEX PARTITION    WRH$_SGASTAT_U          WRH$_SGASTA_1232289473_41482        3             36625           36625                1                     
SYS              INDEX PARTITION    WRH$_PARAMETER_PK       WRH$_PARAME_1232289473_41482        3             36673           36673                1                     
SYS              TABLE PARTITION    WRH$_SERVICE_STAT       WRH$_SERVIC_1232289473_41482        3             36713           36713                1                     
SYS              INDEX PARTITION    WRH$_SERVICE_STAT_PK    WRH$_SERVIC_1232289473_41482        3             36721           36721                1                     
SYS              TABLE PARTITION    WRH$_LATCH              WRH$_LATCH_1232289473_41482         3             42881           42881                1                     
SYS              TABLE              WRI$_ADV_ACTIONS                                            3             61344           61344                1                     
EXAM             TABLE              EXAM_ITEMS_OLD                                             10             69927           69927                1                     
CPR              TABLE              NEED_MONITOR                                               26             94244           94244                1                     
已选择16行。                                                                                                                                                             
SQL>                                                                                                                                                                     
SQL> spool off
  • 假坏块引起恐慌
  • 记录一次ORA-00600[kdsgrp1]分析
  • Read by other session等待事件
  • table中各种坏块对select/dml操作影响
  • shell脚本获得extents分布
  • 数据库报ORA-00607/ORA-00600[4194]错误
  • 记录一次ORA-600[13013]处理过程
  • latch: cache buffers chains