ITPub博客

首页 > 数据库 > Oracle > [20160106]type为TEMPORARY对象重现和清理

[20160106]type为TEMPORARY对象重现和清理

原创 Oracle 作者:lfree 时间:2016-01-06 15:01:14 0 删除 编辑

[20160106]type为TEMPORARY,name为file#.block#对象重现和清理.txt

--我自己重复做一个测试,并且做一些补充:

http://www.xifenfei.com/2015/12/type%E4%B8%BAtemporaryname%E4%B8%BAfile-block%E5%AF%B9%E8%B1%A1%E9%87%8D%E7%8E%B0%E5%92%8C%E6%B8%85%E7%90%86.html

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLESPACE SUGAR DATAFILE
  '/mnt/ramdisk/book/sugar01.dbf' SIZE 100M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

create table t tablespace sugar as select * from dba_objects;
create index i_t_object_id on t(object_id) tablespace sugar;

SCOTT@book> select header_file,header_block from DBA_SEGMENTS where segment_name='I_T_OBJECT_ID' AND OWNER='SCOTT';
HEADER_FILE HEADER_BLOCK
----------- ------------
          6         1410

2.测试:
--这里通过dd 把该block重置为空块,然后rman检查坏块,证明我们处理正常把index的segment header弄成了空块.

SCOTT@book> select name from v$datafile where file#=6;
NAME
----------------------------------------
/mnt/ramdisk/book/sugar01.dbf

SCOTT@book> @ &r/bbvi    6         1410
BVI_COMMAND
------------------------------------------------------------------------------------------
bvi -b 11550720 -s 8192 /mnt/ramdisk/book/sugar01.dbf

--关闭数据库再破坏。

--dd if=/dev/zero of=/mnt/ramdisk/book/sugar01.dbf bs=8192 count=1 seek=1410 conv=notrunc,使用dd要小心注意方向不要搞错,范围不要扩大。
$ dd if=/dev/zero of=/mnt/ramdisk/book/sugar01.dbf bs=8192 count=1 seek=1410 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.6228e-05 seconds, 146 MB/s

--重新启动数据库。
SYS@book> select * from V$DATABASE_BLOCK_CORRUPTION ;
no rows selected

$ dbv file=/mnt/ramdisk/book/sugar01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jan 6 10:57:06 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/sugar01.dbf
Page 1410 is marked corrupt
Corrupt block relative dba: 0x01800582 (file 6, block 1410)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined         : 12800
Total Pages Processed (Data) : 1868
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 388
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 206
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 10337
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2636580 (0.2636580)

SYS@book> select * from V$DATABASE_BLOCK_CORRUPTION ;
no rows selected
--奇怪这样并没有标识。

RMAN> backup validate check logical datafile 6;
Starting backup at 2016-01-06 10:58:32
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=123 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    FAILED 0              10337        12800           2636580
  File Name: /mnt/ramdisk/book/sugar01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1868
  Index      0              388
  Other      1              207

validate found one or more corrupt blocks
See trace file /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_7068.trc for details
Finished backup at 2016-01-06 10:58:36

SYS@book> select * from V$DATABASE_BLOCK_CORRUPTION ;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6       1410          1                  0 ALL ZERO

--index segment header异常

SCOTT@book> alter index i_t_object_id rebuild;
alter index i_t_object_id rebuild
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 6, block # 1410)
ORA-01110: data file 6: '/mnt/ramdisk/book/sugar01.dbf'

--我的测试报ORA-01578错误。作者报ora-08103错误。有一点不同。

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

SCOTT@book> drop index i_t_object_id;
Index dropped.

SCOTT@book> select segment_name,segment_type,tablespace_name from user_segments where TABLESPACE_NAME='SUGAR';
SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME
-------------------- ------------------ ------------------------------
T                    TABLE              SUGAR
6.1410               TEMPORARY          SUGAR

--清理TEMPORARY对象,以sys用户登录:

SYS@book> exec dbms_space_admin.segment_corrupt('SUGAR',6,1410);
PL/SQL procedure successfully completed.

SYS@book> exec dbms_space_admin.segment_drop_corrupt('SUGAR',6,1410);
PL/SQL procedure successfully completed.

SCOTT@book> select segment_name,segment_type,tablespace_name from user_segments where TABLESPACE_NAME='SUGAR';
SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME
-------------------- ------------------ ------------------------------
T                    TABLE              SUGAR

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

3.继续探究:

--虽然清除了,但是遗留1个小问题,这段空间不能再使用。

$ dbv file=/mnt/ramdisk/book/sugar01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jan 6 11:02:25 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/sugar01.dbf
Page 1410 is marked corrupt
Corrupt block relative dba: 0x01800582 (file 6, block 1410)
Completely zero block found during dbv:
DBVERIFY - Verification complete

Total Pages Examined         : 12800
Total Pages Processed (Data) : 1868
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 388
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 206
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 10337
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2636580 (0.2636580)

--重建索引覆盖看看。
SCOTT@book> create index i_t_object_id on t(object_id) tablespace sugar;
Index created.

SCOTT@book> select header_file,header_block from DBA_SEGMENTS where segment_name='I_T_OBJECT_ID' AND OWNER='SCOTT';
HEADER_FILE HEADER_BLOCK
----------- ------------
          6         1666
--可以发现没有使用那块区域。

$ dbv file=/mnt/ramdisk/book/sugar01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jan 6 11:03:32 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/sugar01.dbf
Page 1410 is marked corrupt
Corrupt block relative dba: 0x01800582 (file 6, block 1410)
Completely zero block found during dbv:
DBVERIFY - Verification complete
Total Pages Examined         : 12800
Total Pages Processed (Data) : 1868
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 388
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 206
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 10337
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2637331 (0.2637331)

SYS@book> select * from V$DATABASE_BLOCK_CORRUPTION ;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6       1410          1                  0 ALL ZERO

--type为TEMPORARY是清除了,但是如何重用这些空间是1个问题。

SYS@book> exec dbms_space_admin.TABLESPACE_DUMP_BITMAPS('SUGAR');
PL/SQL procedure successfully completed.

*** 2016-01-06 11:07:54.162
Header Control:
RelFno: 6, Unit: 8, Size: 12800, Flag: 9
AutoExtend: YES, Increment: 8192, MaxSize: 4194302
Initial Area: 126, Tail: 12799, First: 224, Free: 1332
Deallocation scn: 2636463.0
Header Opcode:
Save: No Pending Op
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 128, Flag: 0, First: 224, Free: 63264
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFF00000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

--很明显空间没有释放。
SYS@book> select * from dba_extents where file_id=6 order by BLOCK_ID;
OWNER  SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT  T                    TABLE              SUGAR                                   0          6        128      65536          8            6
...
SCOTT  T                    TABLE              SUGAR                                  24          6       1280    1048576        128            6
SCOTT  I_T_OBJECT_ID        INDEX              SUGAR                                   0          6       1664      65536          8            6
...
SCOTT  I_T_OBJECT_ID        INDEX              SUGAR                                  16          6       1792    1048576        128            6
42 rows selected.

--1280+128=1408
--很明显开始1408,1663这些块没有释放。
--检查包dbms_space_admin:
procedure tablespace_fix_bitmaps(
      tablespace_name         in    varchar2 ,
      dbarange_relative_file  in    positive ,
      dbarange_begin_block    in    positive ,
      dbarange_end_block      in    positive ,
      fix_option                in    positive
                      );
--
--  Marks the appropriate dba range (extent) as free/used in bitmap
--  Input arguments:
--   tablespace_name         - name of tablespace
--   dbarange_relative_file  - relative fileno of dba range (extent)
--   dbarange_begin_block    - block number of beginning of extent
--   dbarange_end_block      - block number (inclusive) of end of extent
--   fix_option              - TABLESPACE_EXTENT_MAKE_FREE or
--                             TABLESPACE_EXTENT_MAKE_USED

SYS@book> exec dbms_space_admin.tablespace_fix_bitmaps('SUGAR',6,1408,1663,TABLESPACE_EXTENT_MAKE_FREE);
BEGIN dbms_space_admin.tablespace_fix_bitmaps('SUGAR',6,1408,1663,TABLESPACE_EXTENT_MAKE_FREE); END;

                                                                  *
ERROR at line 1:
ORA-06550: line 1, column 67:
PLS-00201: identifier 'TABLESPACE_EXTENT_MAKE_FREE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

--奇怪使用参数TABLESPACE_EXTENT_MAKE_FREE不行,查看包的定义可以发现TABLESPACE_EXTENT_MAKE_FREE =7.

SYS@book> exec dbms_space_admin.tablespace_fix_bitmaps('SUGAR',6,1408,1663,7);
PL/SQL procedure successfully completed.

--OK通过。

SYS@book> exec dbms_space_admin.TABLESPACE_DUMP_BITMAPS('SUGAR');
PL/SQL procedure successfully completed.

*** 2016-01-06 11:13:34.332
RelFno: 6, Unit: 8, Size: 12800, Flag: 9
AutoExtend: YES, Increment: 8192, MaxSize: 4194302
Initial Area: 126, Tail: 12799, First: 160, Free: 1336
Deallocation scn: 2636463.0
Header Opcode:
Save: No Pending Op
File Space Bitmap Block:
BitMap Control:
RelFno: 6, BeginBlock: 128, Flag: 0, First: 160, Free: 63296
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFF00000000 FFFFFFFF00000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

--很明显这次正确了。中间1段F变成了0.

SCOTT@book> drop index i_t_object_id;
Index dropped.

SCOTT@book> create index i_t_object_id on t(object_id) tablespace sugar;
Index created.

SCOTT@book> select header_file,header_block from DBA_SEGMENTS where segment_name='I_T_OBJECT_ID' AND OWNER='SCOTT';
HEADER_FILE HEADER_BLOCK
----------- ------------
          6         1410
--已经重用那块区域。

SYS@book> select * from V$DATABASE_BLOCK_CORRUPTION ;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6       1410          1                  0 ALL ZERO

SYS@book> alter system checkpoint;
System altered.

$ dbv file=/mnt/ramdisk/book/sugar01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Wed Jan 6 11:18:02 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/sugar01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 12800
Total Pages Processed (Data) : 1868
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 388
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 207
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 10337
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2638535 (0.2638535)

SYS@book> select * from V$DATABASE_BLOCK_CORRUPTION ;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         6       1410          1                  0 ALL ZERO

RMAN> backup validate check logical datafile 6;
Starting backup at 2016-01-06 11:18:58
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              10337        12800           2638535
  File Name: /mnt/ramdisk/book/sugar01.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              1868
  Index      0              388
  Other      0              207
Finished backup at 2016-01-06 11:18:59

SYS@book> select * from V$DATABASE_BLOCK_CORRUPTION ;
no rows selected

--登记的坏块已经消失。

4.我这个表空间的信息少,很容易确定范围:

--通过视图DBA_EXTENTS可以确定访问sys.x$ktfbue。不过通过它好像不行。
--不知道有什么好方法确定范围。不知道那位有什么好方法!

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1972595/,如需转载,请注明出处,否则将追究法律责任。

上一篇: [20160106]ANSI bug.txt
请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292330