Re: Weird question

Thank you for the response.

Ended up being an issue with a stuck KTSJ background process writing to the flashback data archive - the session was writing for hours and finally completed and everything broke free and our ORA-01555 errors went away.

I don't understand why Oracle is throwing ORA-01555 errors now for so many different reasons. It's horrible to try to troubleshoot.

Basically we KTSJ sessions (responsible for writing Flashback Data Archve data to the archive) throwing ORA-01555 errors where all the queries used "versions" clause with a min/max SCN value. All the queries had a 0 second duration so it looked like either

a.) index corruption
b.) lob corruption
c.) invalid SCNs inside the blocks (hence my question)

Ultimately it appears that the stuck KTSJ session had some "hold" on an SCN that wasn't valid - once it completed , the SCNs in SYS.SYS_FBA_BARRIERSCN also cleared up.

Oracle has opened a bug for this in 19.14 (Bug # 34730450)

Thanks,

Chris

On Sun, Oct 23, 2022 at 8:57 AM Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Before I resorted to their solution I would try force reading the table
> through SGA (as opposed to PGA). Referencing old rollback that is not
> actually needed to get a current block version (not being actually needed
> is implied by their solution suggestion that a CTAS would work) sounds like
> an artifact similar to delayed block cleanout (which I am supposing
> discarding the old undo happened during the upgrade). IF that is done into
> the PGA the problem is not corrected even though individual current blocks
> may be produced, but the “newer” version (current) of the block **should**
> be written back if the read is through the SGA. IF CTAS can select valid
> current block versions, I don’t understand why a full table scan cannot.
> CTAS has no magical ability to create data that I am aware of (and it
> shouldn’t.)
>
>
>
> I **thought** there was a hint to force an FTE though the SGA, but I
> can’t put my hands on it. IF you read all the block addresses restricted to
> one row per block into a new table and read the table with the problem via
> referencing the row block addresses table, that should force the read via
> the SGA and clean out all the blocks of the table except for actually empty
> blocks. Perhaps someone will chime in with that ?hint?.
>
>
>
> I **think** that should “touch” all the blocks that need to be touched,
> but I don’t have a “broken” database to test it on and I have no idea how
> to simulate your exact situation.
>
>
>
> Good luck!
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Chris Taylor
> *Sent:* Sunday, October 23, 2022 2:17 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Weird question
>
>
>
> Is there any way to force Oracle to "touch" every block in a table and
> update/refresh the SCN without running an update/delete?
>
>
>
> Does an update/delete followed by a rollback cause the SCN to advance, or
> does the rollback put the SCN back as well? (I'm assuming it requires a
> commit to advance the SCN)
>
>
>
> THe genesis of this question comes from a situation similar to this:
>
> *Flashback Version Query Fails With Error - ORA-01555 After 18c Upgrade
> (Doc ID 2596214.1)*
> SOLUTION
>
> Run CTAS of each table to generate new block scns for each block.
> Making the block scn current will stop the ORA-1555 errors.
>

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