使用validate验证数据检查数据坏块

validate database;
select count(*) from gv$database_block_corruption;

You can use the VALIDATE command to manually check for physical and logical 
corruptions in database files. This command performs the same types of checks as 
BACKUP VALIDATE, but VALIDATE can check a larger selection of objects. For 
example, you can validate individual blocks with the VALIDATE DATAFILE ... 
BLOCK command.
When validating whole files, RMAN checks every block of the input files. If the 
backup validation discovers previously unmarked corrupt blocks, then RMAN 
updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the 
corruptions.
Use VALIDATE BACKUPSET when you suspect that one or more backup pieces in a 
backup set are missing or have been damaged. This command checks every block in a 
backup set to ensure that the backup can be restored. If RMAN finds block corruption, 
then it issues an error and terminates the validation. The command VALIDATE 
BACKUPSET enables you to choose which backups to check, whereas the VALIDATE 
option of the RESTORE command lets RMAN choose.

Recovery is Repairing Media Corrupt Block (Doc ID 2370639.1)
How to Format Corrupted Block Not Part of Any Segment (Doc ID 336133.1)
如何格式化不属于任何段的损坏块 (Doc ID 1526163.1)
Use RMAN to format corrupt data block which is not part of any object (Doc ID 1459778.1)

识别坏块
sqlplus / as sysdba
set pagesize 2000
set linesize 280
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
     , greatest(e.block_id, c.block#) corr_start_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
       - greatest(e.block_id, c.block#) + 1 blocks_corrupted
     , corruption_type description
  FROM dba_extents e, v$database_block_corruption c
 WHERE e.file_id = c.file#
   AND e.block_id <= c.block# + c.blocks - 1
   AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
     , header_block corr_start_block#
     , header_block corr_end_block#
     , 1 blocks_corrupted
     , corruption_type||' Segment Header' description
  FROM dba_segments s, v$database_block_corruption c
 WHERE s.header_file = c.file#
   AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
     , greatest(f.block_id, c.block#) corr_start_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
     , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
       - greatest(f.block_id, c.block#) + 1 blocks_corrupted
     , 'Free Block' description
  FROM dba_free_space f, v$database_block_corruption c
 WHERE f.file_id = c.file#
   AND f.block_id <= c.block# + c.blocks - 1
   AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

--Doc ID 472231.1
--------------------------------------------------------------------
set pages 999
select 'alter database datafile '||file_id||' autoextend on maxsize 4G;' from dba_data_files where tablespace_name='SYSAUX';

set pages 999
select 'alter database datafile '||file_id||' autoextend off;' from dba_data_files where tablespace_name='SYSAUX';

set pagesize 50000
set linesize 200
select/*+ ordered use_hash(a,c) */
  'alter database datafile '''||a.file_name||''' resize '
   ||round(a.filesize - (a.filesize - c.hwmsize-10) *0.99)||'M;'
from
(select file_id,file_name,tablespace_name,round(bytes/1024/1024) filesize from dba_data_files) a,
(select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c
where a.tablespace_name='SYSAUX'
and
a.file_id = c.file_id
and
a.filesize - c.hwmsize > 10;

set pagesize 50000
set linesize 200
select/*+ ordered use_hash(a,c) */
  'alter database datafile '''||a.file_name||''' resize '
   ||round(a.filesize - (a.filesize - c.hwmsize-100) *0.9)||'M;',
  a.filesize,
  c.hwmsize
from
(select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files) a,
(select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c
where a.file_id = c.file_id
and
a.filesize - c.hwmsize > 100;

set pages 999
set line 300
col file_name for a60
col TABLESPACE_NAME for a30
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_data_files order by 1 ;

set pages 999
set line 300
col file_name for a60
col TABLESPACE_NAME for a30
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_data_files where tablespace_name='SYSAUX' order by 1 ;

=====================表空间检查-按剩余大小排序
set pagesize 9999
set linesize 160
SELECT ts.tablespace_name,
       NVL(ROUND(total_d.max_bytes / 1024 / 1024, 0), 0) MAX_m,
       NVL(ROUND(total_d.total_bytes / 1024 / 1024, 0), 0) total_m,
       NVL(ROUND((total_d.max_bytes - total_d.total_bytes +
                 free_d.free_bytes) / 1024 / 1024,0),0) free_m,
       ROUND(NVL((total_d.max_bytes - total_d.total_bytes +free_d.free_bytes),0) 
         / (NVL(total_d.max_bytes, 0) + 0.000001),4) * 100 pct_free
  FROM (SELECT tablespace_name,
               SUM(decode(ddf.MAXBYTES, 0, ddf.BYTES, ddf.MAXBYTES)) max_bytes,
               SUM(bytes) total_bytes
          FROM dba_data_files ddf
         GROUP BY ddf.tablespace_name) total_d,
       (SELECT tablespace_name, SUM(bytes) free_bytes
          FROM dba_free_space dfs
         GROUP BY dfs.tablespace_name) free_d,
       dba_tablespaces ts
 WHERE ts.tablespace_name = total_d.tablespace_name(+)
   AND ts.tablespace_name = free_d.tablespace_name(+)
   AND ts.Contents <> 'TEMPORARY'
 ORDER BY FREE_M;