关于数据块出现的游离坏块的实验：

用dbv 检查数据文件的情况
[oracle@orahost ~]$ dbv file=/oracle/oradata/orcl/users01.dbf


DBVERIFY: Release 10.2.0.5.0 - Production on Tue May 6 06:48:02 2008


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


DBVERIFY - Verification starting : FILE = /oracle/oradata/orcl/users01.dbf
Page 310 is marked corrupt
Corrupt block relative dba: 0x01000136 (file 4, block 310)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x01000136
 last change scn: 0x0000.00261975 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x19750601
 check value in block header: 0xe1fd
 computed block checksum: 0x570e


Page 334 is marked corrupt
Corrupt block relative dba: 0x0100014e (file 4, block 334)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x0100014e
 last change scn: 0x0000.00261976 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x19760601
 check value in block header: 0x735e
 computed block checksum: 0x765b


Page 351 is marked corrupt
Corrupt block relative dba: 0x0100015f (file 4, block 351)
Bad check value found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x0100015f
 last change scn: 0x0000.00261975 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x19750601
 check value in block header: 0x7148
 computed block checksum: 0x3127






DBVERIFY - Verification complete


Total Pages Examined         : 800
Total Pages Processed (Data) : 647
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 3
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 129
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 18
Total Pages Marked Corrupt   : 3
Total Pages Influx           : 0
Highest block SCN            : 2531497 (0.2531497)


通过以上的记录，看到数据库出现了坏块的情况


使用rman识别数据库中损坏的对象，（ID 1623348.1）
[root@orahost ~]# su - oracle
[oracle@orahost ~]$ rman target /


Recovery Manager: Release 10.2.0.5.0 - Production on Tue May 6 06:54:39 2008


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


connected to target database: ORCL (DBID=1327540369)


RMAN> backup validate check logical database;


Starting backup at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=32 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00006 name=/oracle/oradata/orcl/ggusr01.dbf
input datafile fno=00007 name=/oracle/oradata/orcl/testblock01.dbf
input datafile fno=00005 name=/oracle/oradata/orcl/testbak.dbf
input datafile fno=00008 name=/oracle/oradata/orcl/testbak2.dbf
input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 06-MAY-08


坏块信息会被记录在视图 V$DATABASE_BLOCK_CORRUPTION 中。11g RMAN 会生成一个 trace 文件


[oracle@orahost ~]$ sqlplus / as sysdba


SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 6 06:59:22 2008


Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.




Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Welcome to Yau's oracle world !


this is a test environment


fighting deal friend !!!


rem****下面的查询可以将视图 v$database_block_corruption 中记录的坏块匹配到对应的段或者是空闲块。****rem
SQL> set lines 200 pages 10000
SQL> col segment_name format a30
SQL> 
SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
  2  , greatest(e.block_id, c.block#) corr_start_block#
  3  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
  4  , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
  5  - greatest(e.block_id, c.block#) + 1 blocks_corrupted
  6  , null description
  7  FROM dba_extents e, v$database_block_corruption c
  8  WHERE e.file_id = c.file#
  9  AND e.block_id <= c.block# + c.blocks - 1
 10  AND e.block_id + e.blocks - 1 >= c.block#
 11  UNION
 12  SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
 13  , header_block corr_start_block#
 14  , header_block corr_end_block#
 15  , 1 blocks_corrupted
 16  , 'Segment Header' description
 17  FROM dba_segments s, v$database_block_corruption c
 18  WHERE s.header_file = c.file#
 19  AND s.header_block between c.block# and c.block# + c.blocks - 1
 20  UNION
 21  SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
 22  , greatest(f.block_id, c.block#) corr_start_block#
 23  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
 24  , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
 25  - greatest(f.block_id, c.block#) + 1 blocks_corrupted
 26  , 'Free Block' description
 27  FROM dba_free_space f, v$database_block_corruption c
 28  WHERE f.file_id = c.file#
 29  AND f.block_id <= c.block# + c.blocks - 1
 30  AND f.block_id + f.blocks - 1 >= c.block#
 31  ORDER BY file#, corr_start_block#;


no rows selected
rem***********上面没有记录，判断坏块是游离的，不存在任何的segment上面*************rem


SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' autoextend off;


SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' resize  7M;




SQL> show user 
USER is "TEST"
SQL> alter user test default tablespace users;


User altered.


SQL> create table bb(a varchar(11));


Table created.


SQL> begin
  2  for i in 1..1000 loop
  3  insert into bb values('yau');
  4  commit ;
  5  end loop;
  6  end;
  7  /


PL/SQL procedure successfully completed.


SQL> select tablespace_name from dba_segments where segment_name='BB';


TABLESPACE_NAME
------------------------------
USERS


SQL> begin                                                        
  2  for i in 1..1000 loop
  3  insert into bb values('yau');
  4  commit ;
  5  end loop;
  6  end;
  7  /


PL/SQL procedure successfully completed.




SQL> /


PL/SQL procedure successfully completed.




SQL>  select tablespace_name,file_id,sum(bytes)/1024/1024 size_m from dba_free_space where file_id=4 group by tablespace_name,file_id;


TABLESPACE_NAME                   FILE_ID     SIZE_M
------------------------------ ---------- ----------
USERS                                   4     1.5625


SQL> begin
for i in 1..100000 loop
  2    3  insert into bb values('yau');
  4  commit ;
  5  end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table TEST.BB by 128 in tablespace USERS
ORA-06512: at line 3


使用dbv检查数据文件的情况


[oracle@orahost ~]$ dbv file=/oracle/oradata/orcl/users01.dbf


DBVERIFY: Release 10.2.0.5.0 - Production on Tue May 6 09:25:28 2008


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


DBVERIFY - Verification starting : FILE = /oracle/oradata/orcl/users01.dbf




DBVERIFY - Verification complete


Total Pages Examined         : 896
Total Pages Processed (Data) : 677
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 3
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 102
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 114
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 2897787 (0.2897787)


现在使用rman进行下全库备份


RMAN> backup database format '/oracle_backup/bak0326_%t_%s';


Starting backup at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00006 name=/oracle/oradata/orcl/ggusr01.dbf
input datafile fno=00007 name=/oracle/oradata/orcl/testblock01.dbf
input datafile fno=00005 name=/oracle/oradata/orcl/testbak.dbf
input datafile fno=00008 name=/oracle/oradata/orcl/testbak2.dbf
input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oracle_backup/bak0326_653995568_172 tag=TAG20080506T092608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 06-MAY-08


Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oracle/recovery_dest/ORCL/autobackup/2008_05_06/o1_mf_s_653995683_41zf1775_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAY-08