Re: expdp and ORA-01555

Rich,

We don't have that problem any more. We deleted the row and re-inserted.

I looked at the PL/SQL in Note:787004.1. If you compare it with the two notes it mentions, i.e. 452341.1 and 253131.1, you'll see 787004.1 is missing a lot of exceptions. How can you assume corrupted LOBs can only throw errors ORA-1578, 1555, and 22922? So I suggest you not use the "simple" "faster" code and use the slower one.

Please pass back Oracle Support's analysis. Thanks.

Yong Huang

• On Sun, 4/5/09, Rich <richa03_at_gmail.com> wrote:

From: Rich <richa03_at_gmail.com>
Subject: Re: expdp and ORA-01555
To: tanel.poder.003_at_mail.ee
Cc: "Yong Huang" <yong321_at_yahoo.com>, "Oracle-L Freelists" <oracle-l_at_freelists.org> Date: Sunday, April 5, 2009, 10:34 AM

This is ORA-1555 on expdp, now on read only tablespace. Same table as before.

Solved this a while ago by moving the table to a separate tablespace and setting TS to read only before export, however, it is back.

We have also changed to:
Oracle 10.2.0.4 64-bit on RHEL 5.1 and different server (different reasons).

Tried to set event for 1555 error:
alter system set events '1555 trace name errorstack forever, level 12' however, there is no trace file in udump after the error.

The error is reported in the alert log and the datapump worker process trace (*dw01*) as well as the export log.

Don't have another server with which to test - this is production.

Checked for corruption per Note 787004.1 twice - no corrupt LOBs.

Also, have SR open with Oracle, again, but thought I would try to see if Yong (or any others) are [still] encountering this.

Any help?

On Wed, Aug 27, 2008 at 11:39 PM, Tanel Poder <tanel.poder.003_at_mail.ee>wrote:

> Ah ok, I had missed the LOB corruption part in your post, thus neither
> retention nor pctversion settings help.
>
> If corruption is involved, this might be happening:
>
> 1) expdp scans through LOB blocks
> 2) ...and hits a corruption error which means that the lob block reading
> function returns a failure code
> 3) there's a bug in code which assumes that whenever this lob block reading
> function fails, it means the block was already overwritten (thus raises
> ORA-1555
>
>
> --
> Regards,
> Tanel Poder
> http://blog.tanelpoder.com
> http://n.otepad.com - n.ote this!
>
>
> > -----Original Message-----
> > From: Yong Huang [mailto:yong321_at_yahoo.com]
> > Sent: Thursday, August 28, 2008 02:27
> > To: Tanel Poder; richa03_at_gmail.com
> > Cc: 'Oracle-L Freelists'
> > Subject: RE: expdp and ORA-01555
> >
> > Thanks. I tried retention. Still the same error.
I guess all
> > these are for future space usage, not the already
corrupted blocks.
> >
> > I'm guessing the cause of the error in
10.2.0.3 is the same
> > as in 10.2.0.2. But Oracle partially fixed the
problem so the
> > PL/SQL block in Note:452341.1 doesn't detect
it. I haven't
> > tried to get an error stack. I won't be
surprised if it looks
> > like Richa's.
> >
> > Yong Huang
      

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