用dbv 检查数据文件的情况[oracle@orahost ~]$ dbv file=/oracle/oradata/orcl/users01.dbfDBVERIFY: Release 10.2.0.5.0 – Production on Tue May 6 06:48:02 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.DBVERIFY – Verification starting : FILE = /oracle/oradata/orcl/users01.dbfPage 310 is marked corruptCorrupt block relative dba: 0x01000136 (file 4, block 310)Bad check value found during dbv:Data in bad block:type: 6 format: 2 rdba: 0x01000136last change scn: 0x0000.00261975 seq: 0x1 flg: 0x04spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0x19750601check value in block header: 0xe1fdcomputed block checksum: 0x570ePage 334 is marked corruptCorrupt block relative dba: 0x0100014e (file 4, block 334)Bad check value found during dbv:Data in bad block:type: 6 format: 2 rdba: 0x0100014elast change scn: 0x0000.00261976 seq: 0x1 flg: 0x04spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0x19760601check value in block header: 0x735ecomputed block checksum: 0x765bPage 351 is marked corruptCorrupt block relative dba: 0x0100015f (file 4, block 351)Bad check value found during dbv:Data in bad block:type: 6 format: 2 rdba: 0x0100015flast change scn: 0x0000.00261975 seq: 0x1 flg: 0x04spare1: 0x0 spare2: 0x0 spare3: 0x0consistency value in tail: 0x19750601check value in block header: 0x7148computed block checksum: 0x3127DBVERIFY – Verification completeTotal Pages Examined : 800Total Pages Processed (Data) : 647Total Pages Failing (Data) : 0Total Pages Processed (Index): 3Total Pages Failing (Index): 0Total Pages Processed (Other): 129Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 18Total Pages Marked Corrupt : 3Total Pages Influx : 0Highest 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 2008Copyright (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-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=32 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/oracle/oradata/orcl/system01.dbfinput datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbfinput datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbfinput datafile fno=00006 name=/oracle/oradata/orcl/ggusr01.dbfinput datafile fno=00007 name=/oracle/oradata/orcl/testblock01.dbfinput datafile fno=00005 name=/oracle/oradata/orcl/testbak.dbfinput datafile fno=00008 name=/oracle/oradata/orcl/testbak2.dbfinput datafile fno=00004 name=/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:36Finished backup at 06-MAY-08坏块信息会被记录在视图 V$DATABASE_BLOCK_CORRUPTION 中。11g RMAN 会生成一个 trace 文件[oracle@orahost ~]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 – Production on Tue May 6 06:59:22 2008Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsWelcome to Yau’s oracle world !this is a test environmentfighting deal friend !!!rem****下面的查询可以将视图 v$database_block_corruption 中记录的坏块匹配到对应的段或者是空闲块。****remSQL> set lines 200 pages 10000SQL> col segment_name format a30SQL>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 – 110 AND e.block_id + e.blocks – 1 >= c.block#11 UNION12 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_corrupted16 , ‘Segment Header’ description17 FROM dba_segments s, v$database_block_corruption c18 WHERE s.header_file = c.file#19 AND s.header_block between c.block# and c.block# + c.blocks – 120 UNION21 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_corrupted26 , ‘Free Block’ description27 FROM dba_free_space f, v$database_block_corruption c28 WHERE f.file_id = c.file#29 AND f.block_id <= c.block# + c.blocks – 130 AND f.block_id + f.blocks – 1 >= c.block#31 ORDER BY file#, corr_start_block#;no rows selectedrem***********上面没有记录，判断坏块是游离的，，不存在任何的segment上面*************remSQL> alter database datafile ‘/oracle/oradata/orcl/users01.dbf’ autoextend off;SQL> alter database datafile ‘/oracle/oradata/orcl/users01.dbf’ resize 7M;SQL> show userUSER 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——————————USERSSQL> 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.5625SQL> beginfor 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 USERSORA-06512: at line 3使用dbv检查数据文件的情况[oracle@orahost ~]$ dbv file=/oracle/oradata/orcl/users01.dbfDBVERIFY: Release 10.2.0.5.0 – Production on Tue May 6 09:25:28 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.DBVERIFY – Verification starting : FILE = /oracle/oradata/orcl/users01.dbfDBVERIFY – Verification completeTotal Pages Examined : 896Total Pages Processed (Data) : 677Total Pages Failing (Data) : 0Total Pages Processed (Index): 3Total Pages Failing (Index): 0Total Pages Processed (Other): 102Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 114Total Pages Marked Corrupt : 0Total Pages Influx : 0Highest block SCN : 2897787 (0.2897787)现在使用rman进行下全库备份RMAN> backup database format ‘/oracle_backup/bak0326_%t_%s’;Starting backup at 06-MAY-08using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/oracle/oradata/orcl/system01.dbfinput datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbfinput datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbfinput datafile fno=00006 name=/oracle/oradata/orcl/ggusr01.dbfinput datafile fno=00007 name=/oracle/oradata/orcl/testblock01.dbfinput datafile fno=00005 name=/oracle/oradata/orcl/testbak.dbfinput datafile fno=00008 name=/oracle/oradata/orcl/testbak2.dbfinput datafile fno=00004 name=/oracle/oradata/orcl/users01.dbfchannel ORA_DISK_1: starting piece 1 at 06-MAY-08channel ORA_DISK_1: finished piece 1 at 06-MAY-08piece handle=/oracle_backup/bak0326_653995568_172 tag=TAG20080506T092608 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:01:55Finished backup at 06-MAY-08Starting Control File and SPFILE Autobackup at 06-MAY-08piece handle=/oracle/recovery_dest/ORCL/autobackup/2008_05_06/o1_mf_s_653995683_41zf1775_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 06-MAY-08
不曾见谁。则见朵花儿闪下来，好一惊。
