Today, I did a test to see if DBV is supposed to catch such kind of block corruption. The overall test steps is described as follows:
1. Create a test tablespace, idx_temp
2. Create a table and an index on idx_temp tablespace
3. Backup idx_temp tablespace
4. Rebuild the index with nologging
5. Some DML activities
6. Lost of idx_temp tablespace datafile
7. Recover tablespace
8. Full scan the index
9. Identify corrupted block by DBV and analyze statement
Note: during test I have learned that FORCE_LOGGING should not be turned on at database level, otherwise the test will fail to demonstrate the points.
SQL> drop table t;
drop table t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop tablespace idx_temp including contents and datafiles;
Tablespace dropped.
SQL> create tablespace idx_temp datafile '/tmp/idx_temp_data_1.dbf' size 200M;
Tablespace created.
SQL> create table t as select rownum id, a.* from dba_objects a;
Table created.
SQL> create index t_idx on t(id) tablespace idx_temp nologging;
Index created.
SQL> alter tablespace idx_temp begin backup;
Tablespace altered.
SQL> ho cp /tmp/idx_temp_data_1.dbf /tmp/idx_temp_data_1.dbf.bak
SQL> alter tablespace idx_temp end backup;
Tablespace altered.
SQL> alter index t_idx rebuild;
Index altered.
SQL> update t set object_name=lower(object_name);
50842 rows updated.
SQL> ho rm -f /tmp/idx_temp_data_1.dbf
SQL> alter tablespace idx_temp offline immediate;
Tablespace altered.
SQL> ho cp /tmp/idx_temp_data_1.dbf.bak /tmp/idx_temp_data_1.dbf
SQL> recover tablespace idx_temp;
Media recovery complete.
SQL> alter tablespace idx_temp online;
Tablespace altered.
SQL> select count(id) from t;
select count(id) from t
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 14, block # 141)
ORA-01110: data file 14: '/tmp/idx_temp_data_1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
SQL> analyze index t_idx validate structure;
analyze index t_idx validate structure
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 14, block # 141)
ORA-01110: data file 14: '/tmp/idx_temp_data_1.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option
It can be seen that full scan the index blocks or using analyze validate structure can identify the block corruption. Let's see how DBVERIFY utility can tell us.
This utility can run against the data file:
dbv file='/tmp/idx_temp_data_1.dbf' logfile='dbv.log'
From the screen, I can see:
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Apr 1 09:09:50 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBV-00200: Block, dba 58720397, already marked corrupted DBV-00200: Block, dba 58720398, already marked corrupted DBV-00200: Block, dba 58720399, already marked corrupted ... DBV-00200: Block, dba 58720513, already marked corrupted DBV-00200: Block, dba 58720514, already marked corrupted DBV-00200: Block, dba 58720515, already marked corrupted
In the log file, I have:
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Apr 1 09:09:50 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. DBVERIFY - Verification starting : FILE = /tmp/idx_temp_data_1.dbf DBVERIFY - Verification complete Total Pages Examined : 25600 Total Pages Processed (Data) : 0 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 114 Total Pages Failing (Index): 0 Total Pages Processed (Other): 140 Total Pages Processed (Seg) : 0 Total Pages Failing (Seg) : 0 Total Pages Empty : 25346 Total Pages Marked Corrupt : 112 Total Pages Influx : 0 Highest block SCN : 1781276169 (2536.1781276169)
So, the DBVERIFY utility did catch corrupted blocks.To find out which segments having corrupted blocks, I have to convert DBA to file# and block# by following, for example:
SQL> SELECT dbms_utility.data_block_address_block(58720399) "BLOCK",
dbms_utility.data_block_address_file(58720399) "FILE"
FROM dual;
2 3
BLOCK FILE
---------- ----------
143 14
SQL> SELECT owner, segment_name
FROM DBA_EXTENTS
WHERE file_id = &p1
AND &p2 between block_id AND block_id + blocks - 1 ;
2 3 4 Enter value for p1: 14
old 3: WHERE file_id = &p1
new 3: WHERE file_id = 14
Enter value for p2: 143
old 4: AND &p2 between block_id AND block_id + blocks - 1
new 4: AND 143 between block_id AND block_id + blocks - 1
OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
OPS$ORACLE
T_IDX
The DBVERIFY can also be used to check segments using segment_id as input. Per Oracle document:
SEGMENT_ID Specifies the segment that you want to verify. It is composed of the tablespace ID number (tsn), segment header file number (segfile), and segment header block number (segblock). You can get this information from SYS_USER_SEGS. The relevant columns are TABLESPACE_ID, HEADER_FILE, and HEADER_BLOCK. You must have SYSDBA privileges to query SYS_USER_SEGS.
However, in my test case, I can not find anything from SYS_USER_SEGS. Don't know why yet.
SQL> select TABLESPACE_ID, HEADER_FILE, HEADER_BLOCK from SYS_USER_SEGS where segment_name='T_IDX'; no rows selected
To obtain the segment_id, I have to do:
SQL> select segment_name, tablespace_name, header_file, header_block
from dba_segments
where segment_name='T_IDX';
2 3
SEGMENT_NAME TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
-------------------- --------------------- ----------- ------------
T_IDX IDX_TEMP 14 139
SQL> select ts#,name from v$tablespace where name='IDX_TEMP';
TS# NAME
---------- ------------------------------
17 IDX_TEMP
Finally I can do: dbv user/pass segment_id=17.14.139 , and in this case, I received same info as in the case of running against the data file.
In retrospective, "anaylyze validate structure" probably was the best option to check table and index block corruption for that database if we suspected there were other corruptions.
No comments:
Post a Comment