RE: My worst nightmare - ORA-8103
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: Maureen English <maureen.english@xxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Jun 2014 21:05:30 +0000
The error number is still a puzzle - I would have expected 1578 or 1410
(corrupt block, or invalid rowid), but I guess if a block has got corrupted so
that it seems to belong to an object with a higher data object id than expected
then you might get an 8103. (That's speculation - there may be other more
obvious reasons why you'd get an 8103 that I've overlooked).
Recover from last know is the "obvious" strategy - but if you've been doing
rman backups then there's a "block recover" option that could tell rman to pick
specific blocks from the file backup and roll forward through archived redo
logs from there. I guess this is why Oracle support have been trying to find
the rowids of the lost data. Traditionally you do this by forcing a tablescan
(e.g. select count(*) where non-indexed, nullable column = value you don't
expect to see) and checking what gets into the dump file. Recover the guilty
block, and repeat until no more corrupt blocks. Perhaps you've already got the
list of blocks since you know there are 480 rows missing, though.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________________
From: Maureen English [maureen.english@xxxxxxxxxx]
Sent: 10 June 2014 21:39
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: Re: My worst nightmare - ORA-8103
No partitioned objects.
Per Oracle Support, it's due to lost I/O. The blocks are apparently empty, not
formatted,
data is probably not recoverable. The Support Analyst suggested restore the
datafiles for
the tablespace from the last good backup then doing a restore/recover to roll
forward to
the current time.
- Maureen
On 6/10/2014 11:07 AM, Jonathan Lewis wrote:
>
>
> Ora-8103 is "object no longer exists" - how does this tie in with a corrupted
> block ? Is this a partitioned object with a missing partition ?
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> @jloracle
>
> ________________________________________
> From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
> of Maureen English [maureen.english@xxxxxxxxxx]
> Sent: 10 June 2014 19:28
> To: oracle-l@xxxxxxxxxxxxx
> Subject: My worst nightmare - ORA-8103
>
> Hi,
>
> Original post to BOracle list...apologies to those who are seeing this again.
>
> On 5/31, the refresh of a materialized view in our reporting instance failed
> with an ORA-8103 error, as did a gather stats job for the same table in the
> production database. We had an application upgrade done on 6/1 and copied
> our production database to a preprod version on 6/2, so the errors weren't
> caught immediately.
>
> Users complained that the current data wasn't available in the reporting
> instance
> but my attempts to refresh and recreate all failed with the same ORA-8103
> error.
>
> I've been working with Oracle Support since early last week and keep hitting
> brick walls. At the moment, we've managed to copy 'uncorrupted' rows out of
> the
> table in our preproduction database and are 480 rows short in a table with
> 550M
> rows in it. Oracle is currently working to identify the rowids of the corrupt
> blocks based on the output in a trace file generated by a failing query.
>
> I'm looking at Document 336133.1 while I wait for more info from Oracle.
>
> Anyone have any comments/suggestions/other info that might help identify and
> fix
> the problem? We really can't afford to lose that much data. We're working on
> recovering our database to a different location to try to get back any data
> that
> we lose, but since I don't have any idea what caused the corruption, I'm lost.
>
> - Maureen
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
