Oracle-修复 TEMPORARY 段中损坏的块
修复 TEMPORARY 段中的块损坏
问题现象
示例1 ：alert日志中抛出ORA-1578的警告并且该损坏对象信息包含“SEGMENT TYPE = Temporary Segment”
Errors in file /oracle/diag/rdbms/orcl/orcl/trace/orcl_smon_8086.trc (incident=2446468):
ORA-01578: ORACLE data block corrupted (file # 1707, block # 233066810)
ORA-01110: data file 1707: '/oracle/dbs/tools.dbf'
Wed Sep 24 16:33:09 2021
Corrupt Block Found
TSN = 73, TSNAME = TOOLS
RFN = 1024, BLK = 2646152, RDBA = 2646152
OBJN = 0, OBJD = 124839000, OBJECT = TOOLS, SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE = Temporary Segment
in this example tablespace TOOLS is a PERMANENT tablespace (dba_tablespaces.contents='PERMANENT').
示例2：alert日志中未抛出详细的损坏对象信息
Corrupt Block Found
TSN = 23, TSNAME = TEMP
RFN = 1, BLK = 4608, RDBA = 4198912
OBJN = 298432, OBJD = 4198912, OBJECT = , SUBOBJECT =
SEGMENT OWNER = , SEGMENT TYPE =
解决方案
确定坏块信息 (rman)
rman target /
# 整库检查
backup validate check logical database ;
# 检查指定的数据文件
backup validate check logical datafile <fileno> ;
sqlplus -S "/ as sysdba" <<EOF
select * from v$database_block_corruption ;
EOF
查询坏块在可用空间或已被占用
set lines 200 pages 10000
col segment_name format a32
col owner for a16
col segment_type for a16
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
ORDER BY file#, corr_start_block#;
修复存储在 TEMPORARY 表空间中的临时段中的损坏
对于 TEMPORARY 表空间中的块损坏，并且如果警报日志中的同一块始终报告损坏，请创建一个新的临时表空间并将所有用户切换到该表空间，然后删除受影响的临时表空间。
新建临时表空间
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/ups/data/oradata/o11g/temp02.dbf' SIZE 20480K AUTOEXTEND ON NEXT 640K MAXSIZE 32764m;
修改用户默认临时表空间
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
删除以前的临时表空间
drop tablespace TEMP including contents and tempfiles;
修复存储在 PERMANENT（普通表空间） 表空间中的临时段中的损坏
查看指定表空间中对象信息
select owner, segment_name, segment_type, header_file, header_block, round(bytes/1024/1024) obj_size_mb
from dba_segments
where segment_type = 'TEMPORARY'
and tablespace_name = '&TSNAME'
;
确认损坏的块是Segment Header
像示例1中alert日志都是报告相同的损坏块，则损坏的块可能是段头
select segment_name, segment_type
from dba_segments
where tablespace_name = '&TSNAME'
and header_file = &FILE_NUMBER
and header_block = &BLK;
依据示例1的信息代入上面SQL
select segment_name, segment_type
from dba_segments
where tablespace_name = 'TOOLS'
and header_file = 1707
and header_block = 233066810;
SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
1707.233066810 TEMPORARY
修复坏块
若上面查询返回一行记录，则使用 dbms_space_admin 包修复坏块
exec dbms_space_admin.segment_corrupt('&TSNAME', &RFN, &BLK)
exec dbms_space_admin.segment_drop_corrupt('&TSNAME', &RFN, &BLK)
exec dbms_space_admin.tablespace_rebuild_bitmaps('&TSNAME')
删除临时 Segment 并重建表空间位图数据。
示例1的信息代入上面SQL
select relative_fno
from dba_data_files
where tablespace_name = 'TOOLS'
and file_id = 1707;
RELATIVE_FNO
------------
1024
exec dbms_space_admin.segment_corrupt('TOOLS', 1024, 233066810)
exec dbms_space_admin.segment_drop_corrupt('TOOLS', 1024, 233066810)
exec dbms_space_admin.tablespace_rebuild_bitmaps('TOOLS')
构建脚本
set lines 5000
set long 9999
set pages 999
set head off
spool corrupt.sql
select 'exec dbms_space_admin.segment_corrupt('''||s.tablespace_name||''''||','||replace (segment_name,'.',',')||')'||';' sql_cmd from dba_segments s ,dba_tablespaces t where s.TABLESPACE_NAME=upper('&Tablespace_name')
and s.segment_type='TEMPORARY'
and t.contents='PERMANENT'
and s.TABLESPACE_NAME=t.TABLESPACE_NAME;
spool off
@corrupt.sql
spool drop.sql
set lines 5000
set long 9999
set pages 999
set head off
select 'exec dbms_space_admin.segment_drop_corrupt('''||s.tablespace_name||''''||','||replace (segment_name,'.',',')||')'||';' sql_cmd from dba_segments s ,dba_tablespaces t where s.TABLESPACE_NAME=upper('&Tablespace_name')
and s.segment_type='TEMPORARY'
and t.contents='PERMANENT'
and s.TABLESPACE_NAME=t.TABLESPACE_NAME;
spool off
@drop.sql
exec dbms_space_admin.tablespace_rebuild_bitmaps('&tablespace_name');
总结
普通的表空间中出现TEMPORARY段类型对象的原因
- 在CTAS (create table tb2 as select * from tb1) 过程中，SQL未完成而终止该命令时，则该表(tb2)对应的段为TEMPORARY类型，段名称用段头的'file#.block#'格式命名。只有在SQL执行的最后将TEMPORARY段改为TABLE段
- 在表和索引的MOVE，REBUILD阶段也会产生(TEMPORARY)临时段，可以通过DBA_SEGMENTS视图得到对应信息
- 当段头异常，且删除该对象时就会出现type为TEMPORARY，名字为file#.block#的格式的对象
临时表空间出现坏块
- 在磁盘排序产生临时段，需要在V$TEMPSEG_USAGE视图中获取
- 临时表的段也是需要在V$TEMPSEG_USAGE视图中获取
附录
参考文档
-
How to clear a block corruption in a TEMPORARY segment (Doc ID 1332088.1)
-
ORA-600 [5463] While SMON is Doing Temporary Segment Drop (Doc ID 422039.1)
-
Primary Note for Handling Oracle Database Corruption Issues (Doc ID 1088018.1)
重现type为TEMPORARY,name为file#.block#对象
准备测试数据
创建测试表空间
create tablespace test datafile '/ups/data/oradata/o11g/test01.dbf'
size 128M autoextend on;
创建测试表
create table t1 tablespace test as select object_id, object_name, subobject_name from dba_objects;
create index idx_t1 on t1(object_id) tablespace test;
select header_file,header_block from DBA_SEGMENTS
where segment_name='IDX_T1';
HEADER_FILE HEADER_BLOCK
----------- ------------
5 642
破坏segment header
通过dd 把该block重置为空块,然后rman检查坏块
关闭数据库
shutdown immediate;
dd操作
dd if=/dev/zero of=/ups/data/oradata/o11g/test01.dbf bs=8192 count=1 seek=642 conv=notrunc
启动数据库并检查
rman target /
backup validate check logical datafile 5;
检查坏块
select * from V$DATABASE_BLOCK_CORRUPTION;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 642 1 0 ALL ZERO
select segment_name, segment_type
from dba_segments
where tablespace_name = 'TEST'
and header_file = 5
and header_block = 642;
alert日志输出信息
重现TEMPORARY对象
重建索引
alter index idx_t1 rebuild online;
重建索引操作过程会产生临时段
删除索引
drop index idx_t1;
select owner, segment_name, segment_type, header_file, header_block, round(bytes/1024) obj_size_kb
from dba_segments
where 1=1
-- and segment_type = 'TEMPORARY'
and tablespace_name = 'TEST'
;
此刻，问题便重现了。
清理TEMPORARY对象
exec dbms_space_admin.segment_corrupt('TEST',5,642);
exec dbms_space_admin.segment_drop_corrupt('TEST',5,642);
检查确认
select owner, segment_name, segment_type, header_file, header_block, round(bytes/1024) obj_size_kb
from dba_segments
where 1=1
-- and segment_type = 'TEMPORARY'
and tablespace_name = 'TEST'
;
清除实验数据
drop table t1 purge;
drop tablespace test including contents and datafiles;
