Jload SystemCopy export Error J2EE_CONFIGENTRY ORA-01555

[former_member278174]

HI all

   We have  NW 2004S PI  on 64 bits and we need to migration PI to a new hardware.But in the "export  java dump" phase DB Error during export of J2EE_CONFIGENTRY.

     system info windows 2003 sp1 64X and oracle 10.2.0.2

      PSAPUNDO FREE_SPACE 14600.625 MB

     I try change parameter undo_retention for 600,But error still exists.

SQL> select count(*) from sapsr3db.J2EE_CONFIGENTRY;

  COUNT(*)
----------
     39594

25.08.12 20:53:27 com.sap.inst.jload.Jload dbExport
SEVERE: DB Error during export of J2EE_CONFIGENTRY

25.08.12 20:53:27 com.sap.inst.jload.Jload printSQLException
SEVERE: Message: ORA-01555: snapshot too old: rollback segment number  with name "" too small
ORA-22924: snapshot too old

25.08.12 20:53:27 com.sap.inst.jload.Jload printSQLException
SEVERE: SQLState: 72000

25.08.12 20:53:27 com.sap.inst.jload.Jload printSQLException
SEVERE: ErrorCode: 1555

25.08.12 20:53:27 com.sap.inst.jload.db.DBConnection disconnect
INFO: disconnected

[former_member189725]

Can you increase the undo retention to 1000. Then restart the oracle database. Then try to restart the export.

Regards

Ratnajit

[former_member278174]

Thank Ratnajit, I try change parameter undo_retention for 1000,But error still exists.

[Former Member]

If at all anything undo_retention needs to be reduced and not increased from 600 to 1000. As it is retention time of undo info. Further it comes into picture only if PSAPUNDO is auto extensible.

You can simply try to extend your UNDO tablepace by a 1 GB or so and then try again. PSAPUNDO is 14 GB which is pretty big for PI but still try 1 GB more. Also confirm if PSAPUNDO is really the UNDO tablespace.Check using undo_tablespace parameter.

In case of export/import this issue can occur if number of R3load jobs is too high. If so try reducing the number or wait for most of the jobs except this job to get over and then retry.

Regards.

Ruchit.

[former_member278174]

Thanks Ruchit

     These methods I tried them all,But the problem still exists.

     I try to use oracle tools EXP file=c:\expdat.dmp  tables=(psapsr3db.J2EE_CONFIGENTRY),But the problem still exists.

     I suspect that this piece of the data in the table has a problem,But don't know how to clean up.

Regards.

Qiping

[former_member189725]

Is the SAP application down when you are doing the export ?  Also I believe the ABAP export is complete and JLOAD is  a single process to export data out of the JAVA schema . Did you bounce the database ?

You can find the optimal undo retention using the following sql

Put the below sql in a file

***********************************

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
        SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
        ROUND((d.undo_size / (to_number(f.value) *
       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
   FROM (
        SELECT SUM(a.bytes) undo_size
           FROM v$datafile a,
                v$tablespace b,
                dba_tablespaces c
          WHERE c.contents = 'UNDO'
            AND c.status = 'ONLINE'
            AND b.name = c.tablespace_name
            AND a.ts# = b.ts#
        ) d,
        v$parameter e,
        v$parameter f,
        (
        SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
              undo_block_per_sec
          FROM v$undostat
        ) g
WHERE e.name = 'undo_retention'
   AND f.name = 'db_block_size';

********************************************

Execute as

sql> @undo.sql

set the value of undo_retention to the output value temporarily.

Run the export using sapinst.

Check if that works.

Regards

Ratnajit

[former_member278174]

Thanks Ratnajit

     Inquires the results as follows:

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]   OPTIMAL UNDO RETENTION [Sec]

------------------------ --------------------------------------------------------------------------- ----------------------------

14884                                            1000                                         74844

Regards.

Qiping

[former_member189725]

Is the SAP application down ?

can you manually run the select statement and see if you face issues. Also stop and start the database before executing the statement.

Regards

Ratnajit

[former_member278174]

Thanks Ratnajit

     SAP application is down, Inquires the results as follows:

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]   OPTIMAL UNDO RETENTION [Sec]

                   14884 1000                    15241216

Regards.

Qiping

[former_member189725]

I suggest you do a reorg of the table and see if that works.

Regards

Ratnajit

[former_member278174]

Thanks Ratnajit

     The table reorg faild,Thank.

BR1124I Starting online reorganization of table SAPSR3DB.J2EE_CONFIGENTRY ...

BR0280I BRSPACE time stamp: 2012-08-24 11.22.37

BR0301E SQL error -12008 at location tab_onl_reorg-20, SQL statement:

'BEGIN DBMS_REDEFINITION.START_REDEF_TABLE (UNAME => '"SAPSR3DB"', ORIG_TABLE => '"J2EE_CONFIGENTRY"', INT_TABLE => '"J2EE_CONFIGENTRY#$"',  OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID); END;'

ORA-12008: error in materialized view refresh path

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

ORA-22924: snapshot too old

ORA-06512: at "SYS.DBMS_REDEFINITION", line 50

ORA-06512: at "SYS.DBMS_REDEFINITION", line 1343

ORA-06512: at line 1

BR0280I BRSPACE time stamp: 2012-08-24 11.22.54

BR1106E Reorganization of table SAPSR3DB.J2EE_CONFIGENTRY failed

BR0280I BRSPACE time stamp: 2012-08-24 11.22.54

BR1141I 1 of 1 table processed - 39403 of 39403 rows done

BR0204I Percentage done: 100.00%, estimated end time: 11:22

BR0001I **************************************************

[Former Member]

Hi,

Is PSAPUNDO aut-extensible. If not remember it will pick up the value OPTIMAL UNDO RETENTION [Sec] as undo_retention. Just try to make PSAPUNDO data files autoextensible with a reasonable maxsize so that the sum total doesn't exceed more than say 18 GB ??? (a trial number)..You may even start with a lower value (bit of trial and error) .Please check OSS note 1035137 for a clear understanding of undo segments and particularly AUM.

Regards.

Ruchit.

[Former Member]

Also what error is oracle export throwing up ?

Please remember Oracle exp can't be imported using SAPinst. You need to use imp utility.

Better stick to SAPinst.

Regards.

Ruchit Khushu.

[former_member189725]

I suggest you create a new undo tablespace and change the undo_tablespace parameter to the new tablespace .Also change the undo_retention to approximately 1 hr (3600 sec). Then bounce the database .

Also suggest you update the statistics on the table.Also check oracle alert log if you find any issues.

Then try the export .

Regards

Ratnajit