ORA-01555 on DYNPSOURCE during SAP system export

[TomCenens]

Dears

(RTF) ########## WARNING ###########

Without ORDER BY PRIMARY KEY the exported data may be unusable for some databases

(DBC) Info: No commits during lob export

DbSl Trace: ORA-01555 occurred when reading from a LOB

(EXP) ERROR: DbSlLobGetPiece failed

rc = 99, table "DYNPSOURCE"

(SQL error 1555)

error message returned by DbSl:

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-22924: snapshot too old

(DB) INFO: disconnected from DB

The same error occurs with R3load, Oracle EXP, R3trans

I tried enlarging PSAPUNDO, PSAPTEMP and changing undo_retention, lob segement PCTVERSION

Each time at around 1.500 million logical reads the export fails with the exact same file size:

100105216 DYNPSOURCE.001

SQL> select table_name, segment_name, pctversion, retention

from dba_lobs where table_name in (' 2 DYNPSOURCE');

TABLE_NAME SEGMENT_NAME PCTVERSION

-

---

-

---

-

---

RETENTION

-

---

DYNPSOURCE SYS_LOB0000296057C00004$$

43200

DYNPSOURCE SYS_LOB0000296057C00005$$

43200

DYNPSOURCE SYS_LOB0000296057C00006$$

43200

Anyone has any ideas how to solve this issue.

In meantime I keep on searching and I'll post updated on issue.

Kind regards

Tom

[volker_borowski2 · Accepted solution]

Hi,

is your system up and running during this export?

Normally a 1555 occurs with new undomanagement only, when

there is need to access Undo information, that has already been overwritten.

This means, while your exports runs, some other transaction has

changed and comitted data from your table, that you have not read yet.

So when it comes to read that changed area, your select sees a mismatch

between that timestamp in the tableblock and the timestamp of your "open cursor".

So it decides to get the old value from undo, do give you consistent data of the time

when your cursor was opened, but data needed from undo to do so is already been overwritten.

You can try to set the undo retention to a very low value.

How big is your table and how long does it run when the abort kicks in?

Is that export going parallel or single threaded?

Volker

[TomCenens · Accepted solution]

Hello Volker

Thanks for your reply

SAP system was down during export

I've read the relevant SAP notes, documentation and SDN forum entries but so far no luck fixing the issue.

Kind regards

Tom

[TomCenens · Accepted solution]

Update:

Looks like there are inconsistencies in table DYNPSOURCE that are preventing proper export

Using ABAP program TWTOOL07 we found an error already on a screen.

to be continued....

[stefan_koehler]

Hello Tom,

the important part is the empty undo segment:

ORA-01555: snapshot too old: rollback segment number with name "" too small

We already discussed this issue in a different thread - please check it for the "solution":

Regards

Stefan

[TomCenens]

The export is now continuing after removing the inconsistency out of table DYNPSOURCE.

Thanks everyone for your replies and hopefully it can be useful for someone in the future.

[ivan_martis3]

Hi Tom,

How did you remove the inconsistency out of the table DYNPSOURCE ? I am getting the error

"The databae view "DYNPSOURCE" is write-protected, so it cannot be  changed"

Regards

Ivan Martis