ORA-1555 during db export - LOB

[Former Member]

Hello,

I'm facing ora-1555 during database export of 10.2.0.2 database which has around 900 GB of data.

This error occurs 3 times, in these tables:

SIXMLTST

ECSCR_XSD

REPOSRC

All tables contain RAWSTRING field.

First two are really small tables which contain XML and XSD data. Around 300 and 1000 records, both sizes smaller than 1 MB.

REPOSRC has approx.  2.5 mil. records and 2.2 GB in size.

I tried to resume export twice, always with same result. Export was run with SAP system down, last time I tried it with system up, but with no change.

I also read lot of information online, yet I don't know how to proceed. Especially because of error when processing such small tables so it looks like it is not an insufficient space issue - which is often discussed.

system has oracle parameters set:

undo_management = auto

undo_retention = 0

undo_tablespace = PSAPUNDO

PSAPUNDO has 111 GB and it is empty.

This is full error message:

DbSl Trace: OCI-call 'OCILobRead' failed with rc=1555
DbSl Trace: ORA-1555 occurred when reading from a LOB
(EXP) ERROR: DbSlLobGetPiece failed
  rc = 99, table "SIXMLTST"
  (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

Could you help me to fix this issue?

Is it possible to continue somehow without need to run export from scratch?

Regards,

Martin

[Former Member]

Hi Martin,

Generally This will be a space issue, it will be fixed by increasing the roll back segment size.

But, you mentioned that 111GB of free space exists in PSAPUNDO in that case it's ruled out.

So, I believe the process should be restarted as the snapshot is too old.

Thanks,

Pavan

[Former Member]

Hi Pavan,

do you suggest to start export again from beginning?

[Former Member]

Hi Martin,

Yes, that's the option which I suggest since the snap shot is too old.

Thanks,

Pavan

[former_member188883]

Hi Martin,

For Oracle 10.2.0.2 there is a mention of bug in SAP 983230.

Refer thread for detailed discussion on similar issue and solution http://scn.sap.com/thread/1638943

Regards,

Deepak Kori

[Former Member]

Hi Deepak,

I cannot see the solution in discussion you referred, but after browsing trough mentioned notes I bumped into workaround Note 563359 - Performance optimization for tables with LOB columns  which refers to SQL query that should turn on buffering.

Do you think that is the solution?

[former_member188883]

Hi Martin,

There is a mention of SAP note 983230 - LOB corruption for NOCACHE LOBs in ASSM (ORA-1555/ORA-22924)

Please have a look into this note . Oracle bug fix has been mentioned for this issue.

Regards,

Deepak Kori

[Former Member]

Yes, note 563359 was mentioned in note 983230 as a workaround. I think I will try to use workaround which does not look like risky change. Otherwise I would need to apply that merge fix which looks bit dangerous. I don't want to "kill" this system.

[former_member188883]

Hi Martin,

It always good to have a permanent solution (if available) rather than applying a workaround.

Anyways keep us posted .

Regards,

Deepak Kori