When i export Oracle data by R3load I got the error for LOB table.

[lucas_chung]

Dear all.

I got some error.

Version : oracle 10.2.0.2

Patch : Patch 5636728 : applied on Tue Apr 06 17:48:49 CEST 2010

Created on 15 Feb 2007, 21:35:47 hrs US/Pacific

Bugs fixed:

5636728

Patch 7237154 : applied on Tue Apr 06 13:51:09 CEST 2010

Created on 14 Aug 2008, 12:30:32 hrs PST8PDT

Bugs fixed:

4430244, 5752105, 6140309, 5558627, 5747462, 5376783, 6376915, 7237154

5212539, 7015250, 6455795, 5913430, 5386204, 5093837, 5667736, 6033289

5970301, 5245129, 6996749, 5959612, 6819785, 4771672, 6086497, 5728380

5657620, 5396550

-

---

Error log .

1. in the Export_monitor.java.log File

java version "1.4.2.12"

Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2.12-061213-02:03)

Java HotSpot(TM) 64-Bit Server VM (build 1.4.2 1.4.2.12-061213-10:06-IA64W IA64W, mixed mode)

Export Monitor jobs: running 1, waiting 0, completed 73, failed 0, total 74.

Unloading of 'SAPSSEXC_3' export package: ERROR

Export Monitor jobs: running 0, waiting 0, completed 73, failed 1, total 74.

2. in the Export_monitor.log File

TRACE: 2010-04-06 15:27:59 com.sap.inst.migmon.LoadTask processPackage

Unloading of 'SAPSSEXC_3' export package from database:

/usr/sap/EDE/SYS/exe/run/R3load -e SAPSSEXC_3.cmd -datacodepage 4102 -l SAPSSEXC_3.log -stop_on_error

ERROR: 2010-04-06 15:28:01 com.sap.inst.migmon.LoadTask run

Unloading of 'SAPSSEXC_3' export package is interrupted with R3load error.

Process '/usr/sap/EDE/SYS/exe/run/R3load -e SAPSSEXC_3.cmd -datacodepage 4102 -l SAPSSEXC_3.log -stop_on_error' exited with return code 2.

For mode details see 'SAPSSEXC_3.log' file.

Standard error output:

sapparam: sapargv( argc, argv) has not been called.

sapparam(1c): No Profile used.

sapparam: SAPSYSTEMNAME neither in Profile nor in Commandline

INFO: 2010-04-06 15:28:29

All export packages are generated.

INFO: 2010-04-06 15:28:29

All export packages are processed.

WARNING: 2010-04-06 15:28:29

1 error(s) during processing of packages.

INFO: 2010-04-06 15:28:29

Export Monitor is stopped.

3. in the SAPSSEXC_3 log file.

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

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

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

/usr/sap/EDE/SYS/exe/run/R3load: job finished with 1 error(s)

/usr/sap/EDE/SYS/exe/run/R3load: END OF LOG: 20100406152801

To solve , the following actions was performed..

alter table DYNPSOURCE modify lob (FIELDINFO) (pctversion 100);

alter table DYNPSOURCE modify lob (LOGICINFO) (pctversion 100);

alter table DYNPSOURCE modify lob (EXTENSIONS) (pctversion 100);

alter table DYNPSOURCE modify lob (FIELDINFO) (retention);

alter table DYNPSOURCE modify lob (LOGICINFO) (retention);

alter table DYNPSOURCE modify lob (EXTENSIONS) (retention);

alter system set undoautotune='FALSE';

alter system set undo_retention=65000;

But I still have the problem. How can i fix it.

If you have experienc as same problem. please share the solution.

Thanks.

[stefan_koehler · Accepted solution]

Hello Lucas,

you got the following issue:

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

I explained this whole problem just round about two weeks ago in this thread - please check it:

Regards

Stefan

[lucas_chung · Accepted solution]

Dear sir.

Thanks for your message.

as you mention url. i already check it. but he also could not solve the problem.

and i checked notes 983230, and i applied patch . but i didn't perform the script on the notes.

select rowid rid, dbms_lob.getlength(<your_clob_column>) len

from <your_table_with_clcob_column> )

what is <your_clob_column> and <your_table_with_clcob_column>?

Please ket me know it, I still have the problem.

thanks

[stefan_koehler · Accepted solution]

Hello Lucas,

> Please ket me know it, I still have the problem.

i just want to remind you:

"This fix corrects the bad redo generation and so will not repair any existing corruption or any existing redo with the problem"

So even if you have applied the patch, the LOB is and will stay corrupted.

> what is <your_clob_column> and <your_table_with_clcob_column>?

I have checked it with my SAP release (7.00 EHP4)

SQL> desc SAPSR3.DYNPSOURCE
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROGNAME                                  NOT NULL VARCHAR2(120)
 DYNPNUMBER                                NOT NULL VARCHAR2(12)
 R3STATE                                   NOT NULL VARCHAR2(3)
 FIELDINFO                                          BLOB
 LOGICINFO                                          BLOB
 EXTENSIONS                                         BLOB

So in this case you have to execute three sub queries:

SQL> select rowid rid, dbms_lob.getlength(FIELDINFO) len from DYNPSOURCE
SQL> select rowid rid, dbms_lob.getlength(LOGICINFO) len from DYNPSOURCE
SQL> select rowid rid, dbms_lob.getlength(EXTENSIONS) len from DYNPSOURCE

Regards

Stefan

[lucas_chung · Accepted solution]

Dear sir.

It's performed by sapsr3 user.

alter table DYNPSOURCE modify lob (FIELDINFO) (pctversion 90);

alter table DYNPSOURCE modify lob (LOGICINFO) (pctversion 90);

alter table DYNPSOURCE modify lob (EXTENSIONS) (pctversion 90);

alter table DYNPSOURCE modify lob (DYNPSOURCE) CACHE;

alter table DYNPSOURCE modify lob (LOGICINFO) CACHE;

alter table DYNPSOURCE modify lob (EXTENSIONS) CACHE;

alter table DYNPSOURCE modify lob (FIELDINFO) (retention);

alter table DYNPSOURCE modify lob (LOGICINFO) (retention);

alter table DYNPSOURCE modify lob (EXTENSIONS) (retention);

As above sql , Additionally changing later, i still have the problem.

How can i fix it?

Please let me know it.

Thanks

P

[stefan_koehler · Accepted solution]

Hello Lucas,

where did you have these SQLs from?

If you have applied the patch new corruptions can not occur anymore (if the LOB is defined without cache option), but the already corrupted data is still damaged.

If you have a corruption (which you can check with the SQL that is mentioned in sapnote #983230) you can not fix it.

Maybe Oracle or SAP knows an internal procedure how to repair such corruptions, but there is no official way.

Maybe you can also delete the corrupted data in DYNPSOURCE and recreate it - SAP only knows this.

I think the best case is to open a SR, if you don't know how to check and identify the corruption.

Regards

Stefan

[lucas_chung · Accepted solution]

Dear sir.

I already opened this case on sap marketplace.

Thoes querys has selected by replied sap oss message and sap notes.

Anyway. I will try to check for curruption data.

Thanks

Edited by: Lucas Chung on Apr 7, 2010 11:13 AM

[lucas_chung · Accepted solution]

Dear sir.

I checked the corruption data into the DYNPSOURCE table. but i could not found anyone.

patche is applied it on the system. and no corruption.

how can do that?

if you have some solution please let me know it.

Thanks

[Former Member · Accepted solution]

Hi,

How big is your PSAPUNDO table space ?

Your LOB needs a consistent read. seems with all the activity with the R3loads it cannot do this,

is it a case of just increasing the size of the UNDO.

Mark

[lucas_chung · Accepted solution]

Dear sir.

PSAPUNDO SIZE is 32GB, and even though to cleaer the problem. i have droped PSAPUNDO and recreated.

Then free is 100%. please refer to below message...

List of tablespaces for extension

Pos. Tablespace Files/AuExt. Total[KB] Used[%] Free[KB] MaxSize[KB]

1 - PSAPSR3 65/65 697671680 94.30 39793088 664576000

2 - PSAPSR3700 19/19 117391360 56.62 50927744 194560000

3 - PSAPSR3USR 8/8 81920000 63.15 30183936 72704000

4 - PSAPTEMP 1/0 10240000 0.00 10240000 10240000

5 - PSAPUNDO 1/1 32870400 0.00 32870272 32768000

6 - SYSAUX 1/1 1536000 64.26 548992 10240000

7 - SYSTEM 1/1 1536000 60.59 605376 10240000

Thanks.

[stefan_koehler · Accepted solution]

Hello Mark,

in general you are correct, but please check the exact ORA-01555 error. If you would run into such an issue, you would see the undo segment name. By the way R3load itself doesn't generate so much undo data.

It also depends on the type of "consistent reads" for LOBs ([LOB Whitepaper|http://www.oracle.com/technology/products/database/application_development/pdf/lob_performance_guidelines.pdf] - Part "Consistent Reads on LOBs") and storage. Unfortunately this content was not posted in this thread.

@ Lucas

Did you check it with the SQL?

Can you please perform the following command on shell to verify the LOB segment and post the output here:

shell> su - ora<SID>
shell> exp SAPSR3/<PASS> file=DYNPSOURCE.dmp log=DYNPSOURCE.log tables=DYNPSOURCE

Regards

Stefan

[lucas_chung · Accepted solution]

Dear sir.

I already try to export the command, but same problem. and i tried to export by brtools. it is same situation.

It's very strange due to there is no error in alert log file. generally, this error must be written the error in the alert log file.

No error log in the file.

Then I have found something,

by startsap r3, and i logon into sap by GUI. and i found strange situation for DYNPSOURCE table by se11 t-code.

when i select the some contents, i got same error on st22.

/1BCDWB/DBVBSEGA

/1BCDWB/DBZHCZSD902T

/1BCDWB/DBKOND

/1BCDWB/DBKOND

/1BCDWB/DB/BEV1/RBVBAP

/1BCDWB/DBZHEBI001TB

/ASU/XML_MAINTAIN

Database error text........: "ORA-01555: snapshot too old: rollback segment

number with name "" too small#ORA-22924: snapshot too old"

Internal call code.........: "[RSQL/FTCH/DYNPSOURCE ]"

Please check the entries in the system log (Transaction SM21).

If the error occures in a non-modified SAP program, you may be able to

find an interim solution in an SAP Note.

If you have access to SAP Notes, carry out a search with the following

keywords:

"DBIF_RSQL_SQL_ERROR" "CX_SY_OPEN_SQL_DB"

"/1BCDWB/DBDYNPSOURCE" or "/1BCDWB/DBDYNPSOURCE"

"START-OF-SELECTION"

if i delete above lob information from the DYNPSOURCE table.

how about you for that?

[former_member204746 · Accepted solution]

DYNPSOURCE is probably huge.

Check how big it is. you can view this from DB02 (or the good old DB02OLD)

PSAPUNDO might not be big enough even if 32GB seems big.

[stefan_koehler · Accepted solution]

Hello Lucas,

at first a question to Eric.

I am pretty surprised, that everybody points Lucas to the size of PSAPUNDO. How do you come to this conclusion, in case of such an error case (without the undo segment name) and no massive undo generation in the system (which is the case by R3load export).

@ Lucas:

I still disagree and would take a look at the corruption, because of the errors are still pointing to this issue.

> It's very strange due to there is no error in alert log file. generally, this error must be written the error in the alert log file.

No, in general not. If every ORA error would be logged in the alert log file - holy cow.

Did you really run the SQL that was mentioned in the sapnote #983230 or metalink note #452341.1 with all its commands (for example the "set serverout on" is very important) and for every LOB field?

What does the SAP support said?

I am not sure what's exactly stored in DYNPSOURCE - if only the compiled code is stored in there, you maybe can recreate it .. but i am not sure.

Regards

Stefan

[lucas_chung · Accepted solution]

Dear stefan .

Thanks so much for your kindnee.

It's never regarding of undo tablespace. As stefan mentioned, it is one of some table which has Lob column.

Due to it just use the another area without undo. In this point, I aggree with stefan.

SAP replied to me the following message...

increase the PCTVERION for Lob column. It 's alos one of a notes's describe.

Anyway. Now I have regenerated as i written program, and reactivate. but some program is no problem.

some programs is still stayed on error.

Then i will compare some programs that is occurred the error between systems, And i will make some requests to import to problem's system.

and I read the notes and i checked metalink notes on oracle metalink website.

And i applied merge fix patch and execute the metalink notes script.

I could not find the sap notes script. but it could find the some corruption row by metalink notes as you mentioned.

Then I got the problem is not oracle undo problem. It's just sap problem with Lob table.

and I have experience for ORA1555 error in the another case. it is also program syntax error of standard program.

In the case, I have fixed it by import a request from normal system.

Anyway. I am waiting to get the answer from SAP.

Normally, In these case, Mostly program or query problems. due to it used reqursive sql.

I am really appreciate for all replier. even though it is not solved the problem.

Edited by: Lucas Chung on Apr 7, 2010 8:17 PM

[stefan_koehler · Accepted solution]

Hello Lucas,

> It's never regarding of undo tablespace. Due to it just use the another area without undo

That's wrong, i never said that. LOB segments are also using undo segments (check the white paper that i mentioned above) in various cases.

> SAP replied to me the following message...

Just curious - which support level replied this? PCTVERION also uses undo segments, etc. The usage depends on various factors (size of the LOB data, storage in row or not, etc.), but it always uses undo segments.

Quote from the white paper: "PCTVERSION is applicable only to LOB chunks/pages allocated in LOBSEGMENTS. Other LOB related data in table column and the LOBINDEX segment use regular undo mechanism."

As this topic is very difficult to analyze without system access - i think you have reached the limit of the SDN forum. Hope you get some support by SAP.

> I could not find the sap notes script. but it could find the some corruption row by metalink notes as you mentioned.

Ok - fine. Now you are sure that there is a corruption and you can still hope that Oracle (or SAP) is able to fix it with some crazy hacks )

Regards

Stefan

[lucas_chung · Accepted solution]

Hello. stefan.

Okay i will detail check the white paper and lob mechanism. You are right that you never said that. (It's my misunderstand)

Anyway. I need more time to learn for these case.

As you hope. I will do that.

Thanks.

[lucas_chung · Accepted solution]

I have been solved the problem. There are two type problems.

First,

This problem is program error.

The corruption data which is made by metalink script was inconsistent, some data is normal. Another data is abnormal(corrupted).

The /ASU/XML_MAINTAIN program is a root cause. (it is one of the ST-PI componentu2019s program.)

This program has 4 screen. But Screen number 2100 was existed without flow logic.

I donu2019t know that DB export might have implications for abap source screen.

But, It is exactly root cause. Then I have created a request from another system.

And I have imported it to our system. Itu2019s successfully done.

Second,

As I written, Those programs are for view of table contents.

When I selected the program(/1BCDWB/DBKOND) in the DYNPSOURCE. I got the same error (ora-1555, 22924) without in alert log file.

Example. /1BCDWB/DBKOND

SE11 > KOND (table) > contents > system on menu bar > status > can see the program (/1BCDWB/DBKOND) > double click the program.

Selected again in the DYNPSOURCE. Itu2019s normal.

Result of that, it is one of the bugs about sap generation.

Thanks