Seems like you have media corruption. I would consult Automatic Diagnostic Repository (ADR) contents if there are open failures in your database. You can do that via Enterprise Manager or using RMAN command-line utility:
[oracle@oca ~]$ rman target=/
RMAN> list faliure;
If there are failures with status OPEN
listed, you can ask Data Recovery Advisor (DRA) to analyze them and recommend you the solution to repair the failures:
RMAN> advise failure;
Since you have block corruption, DRA will probably suggest you to recover the corrupted blocks and will create the appropriate script which you can run manually or in the same flow with RMAN:
RMAN> repair failure;
After failures are repaired they're automatically closed by DRA. You can check if all failures are closed with one more list failure
command in RMAN, and if not you can get another advice from DRA.
EDIT:
Since the recovery you performed didn't last long (elapsed time: 00:00:00
), I infer that the datafile wasn't written to by RMAN, and maybe even checked. Thus I would recommend you to proactively validate the database; to do that, just issue validate database
in RMAN and analyze the output. RMAN will check all datafiles (including control file and spfile) and show you if there are any corrupted blocks.
In your comment, you said that RMAN returns the message "Block Media Recovery requires Enterprise Edition" in response to one of your commands, and based on the response from RMAN to validate database
command I suspect this feature is also not available in your software edition. If your database is running in ARCHIVELOG
mode (check with select log_mode from v$database
) you could just completely restore and recover your database without loss of committed transaction, but before doing this I would make sure that your media (hard disk) is not damaged.
Apparently you dropped the object already. Now to calm you down - corrupted blocks now most probably are in free space and do no harm. They just annoy you during backups. To check that:
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#;
If it really shows here you have two options. You can simply ignore it. Once the block will be assigned to some object it will be reformated and corruption will go away. If you want to fix corrupted blocks then you will have to create the object which would occupy your corrupted blocks.
Let's say your corrupted block exists in tablespace USERS
and datafile /ora/users01.dbf
. First you have to make datafiles of tablespace not autoextendable. That is not to inflate datafile size. And then you'll create filler table.
create table filler (
padding varchar2(1000)
) tablespace users nologging pctfree 90 pctused 10;
Then you have to extend table's segment so that it would incorporate corrupt blocks:
alter table filler allocate extent
(datafile '/ora/users01.dbf' size 100m);
In such way you can add several extents adjusting their sizes. After you'll check that corrupt blocks now longer belong to free space you can fill the table:
insert /*+append */ into filler select rpad('x',1000) from
dba_objects
-- filler
where rownum <= 50000;
First insert/select goes from dba_objects then it is faster to insert/select from filler table itself.
Now you just have to check datafile using RMAN:
RMAN> BACKUP VALIDATE CHECK LOGICAL DATAFILE 333;
And drop filler table:
DROP TABLE filler PURGE;
Easy. :)
Best Answer
It is not neccessary. You can leave the corruption in place if you have correctly marked the affected blocks as corrupt. But you must accept that tools such as RMAN will alert you about these blocks every time you perform an action on them. e.g. whenever you take a backup of this datafile.