天萃荷净

结合案例来重现和清理type为TEMPORARY,name为file#.block#对象

在数据库的使用中你可能注意到了,在某些情况下,可能出现某些segment的type为TEMPORARY，名字为file#.block#的格式的对象.这里我通过试验重现该对象并且给出清理方法

1.创建表空间/用户

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_oracleplus tablespace test

2 as select * from dba_objects;

Table created.

SQL> create index idx_t_oracleplus on t_oracleplus(object_id) tablespace test;

Index created.

SQL> select header_file,header_block from DBA_SEGMENTS

2 where segment_name='IDX_T_oracleplus' AND OWNER='CHF';

HEADER_FILE HEADER_BLOCK

----------- ------------

5 267

2.破坏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

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

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

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

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

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

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_oracleplus rebuild;

alter index chf.idx_t_oracleplus rebuild

*

ERROR at line 1:

ORA-08103: object no longer exists

3.重现TEMPORARY对象

通过删除index,然后发现了我们久违的TEMPORARY类型的对象出现

SQL> drop index chf.idx_t_oracleplus;

Index dropped.

SQL> col SEGMENT_NAME for a20

SQL> select segment_name,segment_type from user_segments;

SEGMENT_NAME SEGMENT_TYPE

-------------------- ------------------

T_oracleplus 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_oracleplus TABLE

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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】如何清理type为TEMPORARY,name为file#.block#对象