RE: Corrupt unused blocks in sysaux
- From: Laimutis.Nedzinskas@xxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 14 Feb 2012 10:33:31 +0200
Sysaux is largely a saint cow.
Well, I managed to move objects arround it (into other datafiles.) Then you
can drop or resize the corrupt datafiles) but strictly speeking this is not
supported to my best knowledge.
At least I got a bunch of alert log messages from various processes like
all kinds of advisors but for test db - who cares ?
On the other hand - why not stop awr, advisors, etc while doing this ?
I'd go for it in test env and if dbverify and health check are happy after
that then why not to go into production? The world is not perfect after
all.
>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.
You tried this, didn't you: "How to Format Corrupted Block Not Part of Any
Segment [ID 336133.1]"
I tried this for the following and it worked:
alert:
ORA-00600: internal error code, arguments: [kdddgb5], [11270], [0], [], [],
[], [], []
Current SQL statement for this session:
UPDATE ....
dbverify:..
Block Checking: DBA = 323016891, Block Type = KTB-managed data block
data header at 0x1002712bc
kdbchk: the amount of space used is not equal to block size
used=7103 fsc=0 avsp=592 dtl=7488
Page 55483 failed with check code 6110
---------------------------------------------------------------------------------
Please consider the environment before printing this e-mail
From: "Kenneth Naim" <kennethnaim@xxxxxxxxx>
To: <Jed_Walker@xxxxxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
Date: 2012.02.14 05:03
Subject: RE: Corrupt unused blocks in sysaux
Can you move the objects from sysaux to a new tablespace, drop the sysaux
tablespace (have to be sysdba I upgrade mode I believe), and rename the new
one to sysaux? I'd obviously test this process in another non production
environment first.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [?mailto:oracle-l-bounce@xxxxxxxxxxxxx
]
On Behalf Of Walker, Jed S
Sent: Monday, February 13, 2012 6:18 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Corrupt unused blocks in sysaux
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
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
--
//www.freelists.org/webpage/oracle-l,
-----
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2112/4807 - Release Date: 02/13/12
-----
Checked by AVG - www.avg.com
Version: 2012.0.1913 / Virus Database: 2112/4808 - Release Date: 02/13/12
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
