oracle中type为TEMPORARY,name为file#.block#对象重现和清理

作者：袖梨 2022-06-29

在数据库的使用中你可能注意到了,在某些情况下,可能出现某些segment的type为TEMPORARY，名字为file#.block#的格式的对象.这里我通过试验重现该对象并且给出清理方法
创建表空间/用户
SQL> create tablespace test datafile
2 '/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf'
3 size 128M autoextend on;

Tablespace created.

SQL> create user chf identified by oracle;

User created.

SQL> grant dba to chf;

Grant succeeded.
创建模拟对象
本次通过一个index对象经过一些列操作使其变为我们需要的TEMPORARY对象
SQL> conn chf/oracle
Connected.
SQL> create table t_xifenfei tablespace test
2 as select * from dba_objects;

Table created.

SQL> create index idx_t_xifenfei on t_xifenfei(object_id) tablespace test;

Index created.

SQL> select header_file,header_block from DBA_SEGMENTS
2 where segment_name='IDX_T_XIFENFEI' AND OWNER='CHF';

HEADER_FILE HEADER_BLOCK
----------- ------------
5 267
破坏segment header
这里通过dd 把该block重置为空块,然后rman检查坏块,证明我们处理正常把index的segment header弄成了空块
SQL> select name from v$datafile where file#=5;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@xffdbrh5 ~]$ dd if=/dev/zero
>of=/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf
>bs=8192 count=1 seek=267 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000100548 seconds, 81.5 MB/s
[oracle@xffdbrh5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 21 16:30:56 2015

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 901775360 bytes
Fixed Size                  2024944 bytes
Variable Size             138414608 bytes
Database Buffers          759169024 bytes
Redo Buffers                2166784 bytes
Database mounted.
Database opened.

[oracle@xffdbrh5 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Dec 21 16:33:00 2015

connected to target database: ORA10G (DBID=4205072872)

RMAN> backup validate check logical datafile 5;

Starting backup at 21-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-DEC-15

RMAN> exit

Recovery Manager complete.
[oracle@xffdbrh5 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 21 16:33:50 2015

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
5 267 1 0 ALL ZERO
index segment header异常
index异常后,通过rebuild报ora-08103错误
SQL> alter index chf.idx_t_xifenfei rebuild;
alter index chf.idx_t_xifenfei rebuild
*
ERROR at line 1:
ORA-08103: object no longer exists
重现TEMPORARY对象
通过删除index,然后发现了我们久违的TEMPORARY类型的对象出现
SQL> drop index chf.idx_t_xifenfei;

Index dropped.

SQL> col SEGMENT_NAME for a20
SQL> select segment_name,segment_type from user_segments;

SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
T_XIFENFEI           TABLE
5.267                TEMPORARY
清理TEMPORARY对象
SQL> CONN / AS SYSDBA
Connected.
SQL> exec dbms_space_admin.segment_corrupt('TEST',5,267);

PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.segment_drop_corrupt('TEST',5,267);

PL/SQL procedure successfully completed.

SQL> select segment_name,segment_type from DBA_SEGMENTS WHERE OWNER='CHF';

SEGMENT_NAME SEGMENT_TYPE
-------------------- ------------------
T_XIFENFEI TABLE
通过试验证明:当segment header异常,并且删除该对象,就会出现type为TEMPORARY，名字为file#.block#的格式的对象.我们可以通过dbms_space_admin包处理该类异常对象,让他们彻底从数据库中清除掉

联系：手机(13429648788) QQ(107644445)QQ咨询惜分飞
标题：http://www.xifenfei.com/2015/12/type为temporaryname为file-block对象重现和清理.html