Re: Weird ORA-1555 error

Hi Matt,

Do you have LOBS in your database ? I have experienced this type of snapshot too old error and it was due to lob corruption. I remember we were running 11.2.0.3 on solaris.When all legitimate reasons for causing snapshot too old error are ruled out, its possible that this may be be due to lob corruption.

There is a script that you can run over lob segments to identify the corrupted rows and work on correcting those. Please find the script below and refer metalink note:Doc ID 452341.1

set serverout on
exec dbms_output.enable(100000);
declare
  page number;
  len number;
  c varchar2(10);
  charpp number := 8132/2;

begin
  for r in (select rowid rid, dbms_lob.getlength (<your_clob_column>) len

            from <your_table_with_clcob_column>) loop     if r.len is not null then

      for page in 0..r.len/charpp loop
        begin
          select dbms_lob.substr (<your_clob_column>, 1, 1+ (page * charpp))
          into   c
          from   <your_table_with_clcob_column>
          where  rowid = r.rid;

        exception
          when others then
            dbms_output.put_line ('Error on rowid ' ||R.rid||' page
'||page);
            dbms_output.put_line (sqlerrm);
        end;
      end loop;

Thanks,
Vishnu

On Wed, Sep 28, 2016 at 3:34 PM, Matt Adams <MAdams_at_equian.com> wrote:

> This database itself is not part of a dataguard environment. However, it
> was cloned (using ZFS snapshots) from a dataguard standby database using a
> process that we run once a month. The standby database is shut down
> during the ZFS snapshot process, then restarted
>
>
>
> Approx 6 weeks ago, it had been the primary, but at that time, we had done
> a manual switchover of the primary/standby roles of the two databases. The
> cloning process took place after that swap.
>
>
>
> Matt
>
>
>
>
>
>
>
> *From:* Rodrigo Nascimento [mailto:rodrigo.nascimento.dba_at_gmail.com]
> *Sent:* Wednesday, September 28, 2016 3:28 PM
> *To:* Matt Adams
> *Cc:* Oracle L
> *Subject:* Re: Weird ORA-1555 error
>
>
>
> Hi, Matt
>
>
>
> Tell me, please, is this a Dataguard environment?
>
> Have you performed a switchover recently?
>
>
>
> Regards,
>
>
>
>
>
> On Wed, Sep 28, 2016 at 4:23 PM, Matt Adams <MAdams_at_equian.com> wrote:
>
> I’ve seen ORA-1555 (snapshot too old) lots of times in the past and they
> always seemed to be caused by 1 of 2 scenarios.
>
>
>
> Either an image of a block needed by a long running query is not available
> because another session has modified and committed a change to the block
> and the ‘before modification’ image of the block is no longer available in
> the rollback segments. (common) Or it’s a delayed block cleanout issue
> where the ITL in the block header needs maintenance. (uncommon, but not
> exceedingly rare)
>
>
>
> I’m now getting an ORA-1555 error that doesn’t seem to fit either case.
>
>
>
> This particular table is roughly 11 million rows in a database 10
> terrabytes in size and not terrible busy. There’s 300 Gig of undo
> tablespace and UNDO_RETENTION is set for 40 hours. (don’t ask why 40
> hours. I didn’t set it up that way and nobody seems to remember why it was
> set that way)
>
>
>
> Running the following query returns an error in anywhere from 2 to 10
> seconds.
>
>
>
> SQL> select count(*) from app_owner.type_history;
>
> select count(*) from hri1_owner.type_history
>
> *
>
> ERROR at line 1:
>
> ORA-01555: snapshot too old: rollback segment number 491 with name
>
> "_SYSSMU491_93190984$" too small
>
>
>
> The error is repeatable, with the same rollback segment (number 491)
> listed every time.
>
>
>
> Since I’m getting this error in sometimes as little as 2 seconds or so,
> the first scenario I described above doesn’t seem to apply.
>
>
>
> Usually, the recommended action to eliminate problems with delayed block
> cleanout is to either gather stats on the table or do a ‘select * from
> table’ to force oracle to visit every block. That doesn’t seem to be
> working here. All attempts to analyze the table or select count(*) from
> the table get the same ORA-1555 error
>
>
>
> This is v 11.2.0.4 (EE) on solaris.
>
>
>
> Searches on the web and metalink (yes, I still call it that) have proven
> fruitless so far.
>
>
>
> Anybody have any thoughts on why this might be occurring?
>
> **** This communication may contain privileged and/or confidential
> information. If you are not the intended recipient, you are hereby notified
> that disclosing, copying, or distributing of the contents is strictly
> prohibited. If you have received this message in error, please contact the
> sender immediately and destroy any copies of this document. ****
>
>
>
>
>
> --
>
> Rodrigo Nascimento
>
> **** This communication may contain privileged and/or confidential
> information. If you are not the intended recipient, you are hereby notified
> that disclosing, copying, or distributing of the contents is strictly
> prohibited. If you have received this message in error, please contact the
> sender immediately and destroy any copies of this document. ****
>

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