[20160106]type為TEMPORARY物件重現和清理
[20160106]type為TEMPORARY,name為file#.block#物件重現和清理.txt
--我自己重複做一個測試，並且做一些補充：
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dba_segments之segment_type='TEMPORARY'
- 物件的初始化和清理物件
- 重複照片清理工具PhotoSweeper X ，如何清理重複圖片
- 淺談Using filesort和Using temporary 為什麼這麼慢
- Allocation of Temporary Segments for Temporary Tables and Indexes (28)Index
- 『無為則無心』Python物件導向 — 54、重寫和super()函式Python物件函式
- 【去重】當SYS和SYSTEM使用者出現重複資料庫物件時的應對措施資料庫物件
- 【重溫基礎】11.Map和Set物件物件
- Umbrella for mac(重複檔案清理軟體)Mac
- oracle taf type型別為session和select的區別Oracle型別Session
- oracle temporary tableOracle
- 黑馬程式設計師匠心之作-4.2物件的初始化和清理程式設計師物件
- Duplicate Photos Fixer Pro for Mac(重複圖片清理)Mac
- 陣列物件去重陣列物件
- js為物件新增和刪除屬性JS物件
- jQuery物件和DOM物件之間的轉換實現jQuery物件
- 『無為則無心』Python物件導向 — 46、類和物件Python物件
- PHP實現url重寫和.htaccessPHP
- grant_type為client_credentials和password二者的區別client
- Logstash 中type 和 tags
- Duplicate Photos Fixer Pro for Mac重複照片清理軟體Mac
- Advanced Duplicate Cleaner for mac - 智慧重複檔案清理Mac
- 重複照片清理軟體：Duplicate Photos Fixer Pro MacMac
- 重複照片清理軟體：Duplicate Photos Fixer Pro for MacMac
- Java之ArrayList去除重複元素（包括字串和自定義物件）Java字串物件
- [譯] Story 中 Type Mode 在 iOS 和 Android 上的實現iOSAndroid
- Temporary failure in name resolution - DNS引發的問題和思考AIDNS
- js 去掉陣列物件中的重複物件JS陣列物件
- 如何禁止JavaScript物件重寫？JavaScript物件
- List中去除重複物件物件
- 重編譯 invalid 物件編譯物件
- [Oracle Script] Temporary Sort UsageOracle
- Restrictions on Altering Temporary TablesREST
- Operations that Require Temporary Segments (26)UI
- Go 中 type var string 和 type var = string 的區別Go
- 編譯錯誤 --- does not name a type和field `XX' has incomplete type編譯
- 現代新的簡化”物件“和”物件導向“的定義物件
- 【區分】Typescript 中 interface 和 typeTypeScript