Re: Corrupt unused blocks in sysaux

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 14 Feb 2012 07:53:23 +0000
Message-ID: <CABe10sZoRa1iP4mEgh4uh4Ta6K3odWzB0n56MY7DaNmUuKfUhg_at_mail.gmail.com>

Jed,
I'm afraid I think you'll end up recreating the database (either via export/import or by reversing streams) See Note 950128.1. Most but not all of SYSAUX is moveable.
Couple of wilder ideas.

• Try backing up the datafile with RMAN (it shouldn't write the empty blocks but I'd expect it to fail reading them)
• Try shutting down the db and physically moving the file to a different device (should tell you if the file is physically corrupt), you could then do a rename.

The 'correct' thing to do is covered in the note though :(

On Mon, Feb 13, 2012 at 11:18 PM, Walker, Jed S < Jed_Walker_at_cable.comcast.com> wrote:

> Someone just brought me to a system that recently had an outage. A disk
> had gone bad and been replaced (supposedly RAID1). The database was
> restarted and appears to be working normally; however, there are some
> corrupt blocks. I first saw this in the alert log and then verified with
> dbv. So, now I have corrupt blocks in SYSAUX that are not used by an object.
> Unfortunately there are no RMAN backups as it appears they use streams to
> replicate to another database.
>
> I tried creating an object in the sysaux tables so I could allocate
> extents to encompass the blocks and then insert rows hoping to reformat
> them; however, when I tried to allocate over the blocks I get a corruption
> message. I'm thinking these are corrupt on disk.
>
> I re-found dbms_repair but the options for marking blocks corrupt seem to
> be if the blocks are in an object, not free.
>
> Any ideas on what else I can do? I'd really like to just mark off the
> blocks and be done with it. (This project is hopefully obsolete later this
> year).
>
>
> DBVERIFY - Verification starting : FILE =
> /var/local/u01/app/oracle/oradata/orcl/sysaux.dbf
> Page 8450 is influx - most likely media corrupt
> Page 8451 is marked corrupt
> Page 8452 is marked corrupt
> Page 8453 is marked corrupt
> Page 8454 is marked corrupt
>
> select segment_name, segment_type, owner
> from dba_extents
> where file_id = 3
> and (
> 8451 between block_id and block_id + blocks -1
> or
> 8452 between block_id and block_id + blocks -1
> or
> 8453 between block_id and block_id + blocks -1
> or
> 8454 between block_id and block_id + blocks -1
> )
> /
>
> no rows selected
>
> connect jed/*
>
> SQL> create table remcorr (n number, c varchar2(4000)) nologging
> tablespace sysaux;
>
> Table created.
>
> SQL> alter table remcorr allocate extent;
>
> Table altered.
>
> SQL> alter table remcorr allocate extent (size 200m);
> alter table remcorr allocate extent (size 200m)
> *
> ERROR at line 1:
> ORA-01578: ORACLE data block corrupted (file # 3, block # 8451)
> ORA-01110: data file 3: '/var/local/u01/app/oracle/oradata/orcl/sysaux.dbf'
>
>
> Jed S. Walker, OCP
> Principal Engineer, Databases
> National Video Advanced Services
> Office: 303.267.6759
> P Please only print this email if necessary. Consider the environment and
> cost.
>
> CONFIDENTIAL NOTICE
> This electronic mail transmission and any accompanying documents contain
> information belonging to the sender, which may be confidential and legally
> privileged. If you are not the intended recipient, any disclosure,
> copying, distribution or action taken in reliance on the message is
> strictly prohibited. If you have received this message in error, please
> delete it immediately. Thank You
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 14 2012 - 01:53:23 CST