8 Replies Latest reply on Aug 30, 2019 3:09 AM by abez

    Expdp error with snapshot too old

    abez

      Hi,

       

      I'm doing expdp for single big table.

      Expdp was triggered with nohup using parameter file. Table size is 1.6 TB

       

      more ECAP2_exptbl.par

      dumpfile=EXP_DUMP:expdpECAP2_2019aug_%U.dmp

      logfile=EXP_DUMP:expdpECAP2_2019aug.log

      filesize=50G

      schemas=ECAP2

      include=TABLE:"IN ('JBPM_BYTEBLOCK')"

       

      Output log :

      Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

      Total estimation using BLOCKS method: 1635. GB

      Processing object type SCHEMA_EXPORT/TABLE/TABLE

      Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

      Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

      Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

      Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

      ORA-31693: Table data object "ECAP2"."JBPM_BYTEBLOCK" failed to load/unload and is being skipped due to error:

      ORA-02354: error in exporting/importing data

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

      Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

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

       

      Action taken:

      I have increased undo_retention from 900 to 2700

       

      show parameter undo

      NAME                                 TYPE        VALUE

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

      temp_undo_enabled                    boolean     FALSE

      undo_management                      string      AUTO

      undo_retention                       integer     2700

      undo_tablespace                      string      UNDOTBS1

       

      Any further action i can proceed to complete my export process.

       

      TQ

      Abez

        • 1. Re: Expdp error with snapshot too old
          Gaz in Oz

          If your exporting from an 11.2 EE edition or higher, you could use parallel=n, where n denoes the number of datapump slaves to start.

          Using parallel=n may speed up the overall expdp process enough to get round the Ora-01555, if the error was due to other transactions taking up undo while your export was running.

          • 2. Re: Expdp error with snapshot too old
            Joerg.Sobottka

            UNDO_RETENTION is in SECONDS. Writing 1.6 TB will be a little bit longer than 45 minutes... Try with 3 hours (10.800 seconds), 5 (18.000 seconds) hours or even more... Be aware that your undo tablespace can get very, very huge than.

            • 3. Re: Expdp error with snapshot too old
              Emad Al-Mousa

              Hi,

               

              increase both the UNDO tablespace and UDNO_RETENTION parameter

              • 4. Re: Expdp error with snapshot too old
                abez

                Hi,

                 

                I have increased my undo tblspace up to 600G plus the retention to 3 hrs and still facing the same error but the free size almost the same..

                 

                TABLESPACE_NAME         SIZEMB     FREEMB
                ------------------------------ ---------- ----------
                UNDOTBS1                           585540    584930.63

                 

                NAME                                 TYPE        VALUE
                ------------------------------------ ----------- ------------------------------
                temp_undo_enabled                    boolean     FALSE
                undo_management                      string      AUTO
                undo_retention                       integer     18000
                undo_tablespace                      string      UNDOTBS1

                 

                Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

                With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

                Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" parfile=ECAP2_exptbl.par

                Estimate in progress using BLOCKS method...

                Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

                Total estimation using BLOCKS method: 1635. GB

                Processing object type SCHEMA_EXPORT/TABLE/TABLE

                Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

                Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

                Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

                Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

                Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

                ORA-31693: Table data object "ECAP2"."JBPM_BYTEBLOCK" failed to load/unload and is being skipped due to error:

                ORA-02354: error in exporting/importing data

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

                Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

                • 5. Re: Expdp error with snapshot too old
                  Emad Al-Mousa

                  increase more the undo retention

                  • 6. Re: Expdp error with snapshot too old
                    abez

                    Done. Finally.

                     

                    ;;;
                    Export: Release 12.1.0.2.0 - Production on Thu Aug 29 08:41:43 2019

                    Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
                    ;;;
                    Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
                    Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" parfile=ECAP2_exptbl.par
                    Estimate in progress using BLOCKS method...
                    Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
                    Total estimation using BLOCKS method: 1635. GB
                    Processing object type SCHEMA_EXPORT/TABLE/TABLE
                    Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
                    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
                    Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
                    Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
                    Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
                    . . exported "ECAP2"."JBPM_BYTEBLOCK"                    1247. GB 1290094481 rows
                    Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
                    ******************************************************************************

                     

                    OWNER TABLE_NAME                size (MB)         actual_data (MB)    wasted_space (MB)

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

                    ECAP2   JBPM_BYTEBLOCK       1525308.52      1106624.38         418684.1

                     

                    I plan to reclaim wasted space for this table based on fragmentation query.

                    How should I proceed ..  just imp back the table ?

                    • 7. Re: Expdp error with snapshot too old
                      Joerg.Sobottka

                      What's your undo_retention now? This is why I said, try it with 3 hours, 5 hours or even more. 1.6TB  is really huge.

                       

                      Importing 1.6TB and recreating all indexes? As you do have enterprise edition, create a new tablespace and make an alter table move or use DBMS_REDEFINITION package to move the data online to the new tablespace. Both will work and releases space.

                      When you do use alter table move (in 12.1 there isn't an online table move), the indexes must be rebuild. With DBMS_REDEFINITION you can specify the indexes in the beginning.

                      For both ways you need to have the space for table and indexes twice.

                       

                      But this should be another post, as it does not fit to the Snapshot too old message, which is fixed now.

                      • 8. Re: Expdp error with snapshot too old
                        abez

                        my undo was set to 90000