RE: Corrupt unused blocks in sysaux

From: Walker, Jed S <Jed_Walker_at_cable.comcast.com>
Date: Thu, 23 Feb 2012 21:31:06 +0000
Message-ID: <BAA6E28B6241F046AED1E62D8697516C562EA367_at_COPDCEXMB08.cable.comcast.com>



Well, darn it. I did a backup of the SYSAUX tablespace using maxcorrupt and then restored it. It unfortunately appears that this setting just tells Oracle to ignore the fact that the block is corrupt and still backup it up (even though it is unused). Following the restore/recover I still have corrupted blocks. I'll have to research to see if there's a way to tell RMAN to not backup corrupted blocks (but I think that might not make sense logically). Or I need a way to say, reformat the block. run {
 set maxcorrupt for datafile 3 to 5;
 backup as backupset tablespace sysaux;
}

rename file on OS/ASM

rman> restore tablespace sysaux;

Starting restore at 23-FEB-12
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid68 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set restoring datafile 00003 to /var/local/u01/app/oracle/oradata/orcl/sysaux.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oraback/TVNAV/backupset/2012_02_23/o1_mf_nnndf_TAG20120223T210007_7nfb6q95_.bkp channel ORA_DISK_1: restored backup piece 1 piece handle=/u01/app/oracle/oraback/TVNAV/backupset/2012_02_23/o1_mf_nnndf_TAG20120223T210007_7nfb6q95_.bkp tag=TAG20120223T210007 channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 Finished restore at 23-FEB-12

rman> recover tablespace sysaux;

Starting recover at 23-FEB-12
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 23-FEB-12

SQL> select * from v$recover_file;

no rows selected

SQL> Select * from v$database_block_corruption;

     FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------

         3       8450          5                  0 CORRUPT

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Walker, Jed S Sent: Thursday, February 16, 2012 11:28 PM To: oracle-l_at_freelists.org
Subject: RE: Corrupt unused blocks in sysaux

I had to use "set maxcorrupt" to get the backup to work, but since they are unused blocks it should not matter. I am probably going to do the work next week. I'll update afterwards - hopefully with good news.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Walker, Jed S Sent: Tuesday, February 14, 2012 7:47 AM To: Niall Litchfield
Cc: oracle-l_at_freelists.org
Subject: RE: Corrupt unused blocks in sysaux

Thanks for all the tips. My example below was using note 336133.1. That is why I was asking here, the note isn't working. I had considered exporting everything in SYSAUX and recreating but given what SYSAUX is I found that worrisome. Niall: That is a great idea. I had used "backup validate" to see if it would report the issue and it didn't. Obviously because it didn't look at the unused blocks. So, yes, theoretically if I could back up the SYSAUX table, and then restore/recover it from the backup I can probably eliminate the problem.

From: Niall Litchfield [mailto:niall.litchfield_at_gmail.com] Sent: Tuesday, February 14, 2012 12:53 AM To: Walker, Jed S
Cc: oracle-l_at_freelists.org
Subject: Re: Corrupt unused blocks in sysaux

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<mailto:Jed_Walker_at_cable.comcast.com<mailto:Jed_Walker_at_cable.comcast.com<mailto: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 SQL> 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<tel: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

--

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu Feb 23 2012 - 15:31:06 CST

Original text of this message