• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

Oracle EXPDP error

Wel I cannot  export the following  big table and the expdp log is as follow:

;;
Export: Release 12.1.0.1.0 - Production on Tue Jan 16 10:46:02 2018

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
;;;
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_04":  "/******** AS SYSDBA" directory=EXPRMAN dumpfile=dmpSGCIPROD160118.dmp logfile=dmpSGCIPROD160118.log TABLES=SGCEIPROD.GININFORMES CLUSTER=N
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 58.30 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
ORA-31693: Table data object "SGCEIPROD"."GININFORMES" 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  with name "" too small
ORA-22924: snapshot too old
Master table "SYS"."SYS_EXPORT_TABLE_04" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_04 is:
  /rman/PSAUX/dmpSGCIPROD160118.dmp
Job "SYS"."SYS_EXPORT_TABLE_04" completed with 1 error(s) at Tue Jan 16 13:02:26 2018 elapsed 0 02:16:20

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


Any suggestion?
I changed the  UNDO-RETENTION parameter: alter system set undo_retention=32000  sid'*'  

before running the expdp sentence.
0
Enrique Gomez Esteban
Asked:
Enrique Gomez Esteban
  • 8
  • 6
  • 4
  • +3
4 Solutions
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Did you double check on UNDO_RETENTION after the change?! I'm asking because I'm missing the "SCOPE=BOTH" clause in your statement and I'm wondering "maybe you just changed the spfile scope"

The default for SCOPE depends on how you started the database:
From https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_2013.htm#SQLRF00902
If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

ALTER SYSTEM SET UNDO_RETENTION = 32000 scope=both;

Open in new window


But before you set this to such a high value, please re-calculate it, e.g. with the help of https://www.akadia.com/services/ora_optimize_undo.html
0
 
Enrique Gomez EstebanConsultantAuthor Commented:
Thanks. I already tried it:
SQL> show parameters undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     32000
undo_tablespace                      string      UNDOTBS1
SQL>
SQL>
SQL> alter system set undo_retention=70000 sid='*' scope=both;

System altered.

Anyway, I wiil double check again.
0
 
Enrique Gomez EstebanConsultantAuthor Commented:
NO. It happens the same error:
ORA-31693: Table data object "SGCEIPROD"."GININFORMES" 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  with name "" too small
ORA-22924: snapshot too old
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
Abhimanyu SuriSr Database EngineerCommented:
Hi Enrique,

What is your UNDO tablespace size ?

SELECT TABLESPACE_NAME,
       BYTES / 1024 / 1024 SZ_MB,
       maxbytes / 1024 / 1024 MXSZ_MB,
       autoextensible,
       increment_by
  FROM dba_data_files
 WHERE tablespace_name LIKE 'UNDO%'

Open in new window

0
 
Enrique Gomez EstebanConsultantAuthor Commented:
TABLESPACE_NAME                     SZ_MB    MXSZ_MB AUT INCREMENT_BY
------------------------------ ---------- ---------- --- ------------
UNDOTBS1                            13536      16384 YES         4096
UNDOTBS2                            16384      16384 YES         4096
UNDOTBS1                            13696      16384 YES         4096
UNDOTBS1                            14080      16384 YES         4096
UNDOTBS1                            13632      16384 YES         4096
UNDOTBS1                            13600      16384 YES         4096
UNDOTBS1                            14080      16384 YES         4096
UNDOTBS1                            14464      16384 YES         4096
UNDOTBS1                            14272      16384 YES         4096
UNDOTBS2                            16384      16384 YES         4096
UNDOTBS2                            16384      16384 YES         4096
UNDOTBS2                            16384      16384 YES         4096
UNDOTBS2                            16384      16384 YES         4096
UNDOTBS2                            16384      16384 YES         4096
UNDOTBS2                            16384      16384 YES         4096
UNDOTBS2                            16384      16384 YES         4096
UNDOTBS2                            10240          0 NO             0
UNDOTBS1                            10240      20480 YES        12800
UNDOTBS1                            15360      25600 YES        12800

19 rows selected.


It is UNDOTBS1. In fact I added 2 datafiles from 10Gb and 15GB for this  job.

The table that is not loaded is not very big.


  CREATE TABLE "SGCEIPROD"."GININFORMES"
   (    "GININF_IDINFORME" NUMBER,
        "GININF_IDUSUARIO" NUMBER,
        "GININF_IDEMPRESA" NUMBER,
        "GININF_IDPRODUCTO" NUMBER,
        "GININF_NOMBRE" VARCHAR2(200),
        "GININF_RAZONSOCIAL" VARCHAR2(255),
        "GININF_INFORME" BLOB,
        "GININF_FECHAALTA" DATE,
        "GININF_FECHAACTUALIZACION" DATE,
        "GININF_IDCONSUMO" NUMBER,
        "GININF_FAVORITO" NUMBER,
        "GININF_NOTA" VARCHAR2(2000),
        "AUDIT_FEC_ALTA" DATE,
        "AUDIT_FEC_MODIF" DATE,
        "AUDIT_USER_ALTA" VARCHAR2(30),
        "AUDIT_USER_MODIF" VARCHAR2(30),
        "GININF_FEC_CIERRE" NUMBER,
        "GININF_CONSOLIDADO" NUMBER,
        "GININF_IDENTIFICATIVO" VARCHAR2(20),
        "GININF_IDFICHEROPDF" VARCHAR2(100),
        "GININF_SISTEMAGUARDADOPDF" VARCHAR2(100),
         CONSTRAINT "GININFORMES_PK" PRIMARY KEY ("GININF_IDINFORME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SGCEIPROD_DATA_1"  ENABLE,
         CONSTRAINT "GININF_IDUSUARIO_FK" FOREIGN KEY ("GININF_IDUSUARIO")
          REFERENCES "SGCEIPROD"."GINUSUARIOS" ("GINUSUA_IDUSUARIO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SGCEIPROD_DATA_1"
 LOB ("GININF_INFORME") STORE AS BASICFILE (
  TABLESPACE "SGCEIPROD_DATA_1" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))


SQL>
SQL>
SQL> select count(*)
  2  from SGCEIPROD.GININFORMES;

  COUNT(*)
----------
    303043

SQL>
0
 
Abhimanyu SuriSr Database EngineerCommented:
Please provide output of below command

select sum(bytes)/1024/1024 from dba_segments where segment_name='GININFORMES' and owner='SGCEIPROD';

Open in new window


Thanks,
Suri
0
 
Abhimanyu SuriSr Database EngineerCommented:
Also, I have noticed that one of the columns in table is LOB.
Please refer this metanote for similar issue and resolution

ORA-01555 And Other Errors while Exporting Table With LOBs, How To Detect Lob Corruption. (Doc ID 452341.1)

Thanks,
Suri
0
 
Abhimanyu SuriSr Database EngineerCommented:
And,

Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922 (Doc ID 787004.1)
0
 
Mark GeerlingsDatabase AdministratorCommented:
The most common cause of the "ORA-01555: snapshot too old error" whether that happens in an export or any other query or program is:

The data you are trying to export (or query) is actively being changed or deleted by other users/processes while you are trying to export/query it.  No, changing the "undo_retention" parameter will usually not help.  Adding some space or some files to the UNDO tablespace may (or may not) help.  Running the export or query (if you can) at a time when the database (or at least this table) is less busy usually will help avoid the ORA-01555 error.

And yes, maybe this error is more common in objects that contain LOB (or BLOB or CLOB) columns.
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
I suppose you have to set retention on the table(s), too:
alter table SGCEIPROD.GININFORMES modify lob (GININF_INFORME) (pctversion 10);
alter table SGCEIPROD.GININFORMES modify lob (GININF_INFORME) (retention);

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
0
 
Geert GOracle dbaCommented:
find the program which changes that table, stop it
and then run the export

or use a backup
as of oracle 12 you can restore a single table from a backup

an export is not a backup ...
0
 
Enrique Gomez EstebanConsultantAuthor Commented:
Hola

La sentencia genera el resultado:


SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='GININFORMES' and owner='SGCEIPROD';

SUM(BYTES)/1024/1024
--------------------
                  80
0
 
Enrique Gomez EstebanConsultantAuthor Commented:
It seems that some backgroung process were running with the EXPDP concurrently. I will update the results asap.
0
 
slightwv (䄆 Netminder) Commented:
>>SQL> select sum(bytes)/1024/1024 from dba_segments where

That really doesn't help because it doesn't take the LOB data size into account unless all the LOB data is stored inline.
0
 
Abhimanyu SuriSr Database EngineerCommented:
TABLESPACE "SGCEIPROD_DATA_1" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
0
 
slightwv (䄆 Netminder) Commented:
Just because you set ENABLE STORAGE IN ROW doesn't mean the entire LOB is stored inline.

Take a peek inside the docs:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adlob/LOB-storage-with-applications.html#GUID-B82B3C24-1FAF-4661-96A0-28241FD2A052

13.3.1 Inline and Out-of-Line LOB Storage
LOB values are stored out-of-line when any of the following situations apply:

If the size of the LOB is greater than approximately 4000 bytes (4000 minus system control information), regardless of the LOB storage properties for the column.

If you update a LOB that is stored out-of-line and the resulting LOB is less than approximately 4000 bytes, it is still stored out-of-line.
0
 
Abhimanyu SuriSr Database EngineerCommented:
And the very same reason i asked for object size, lob segment was next.

Thanks
0
 
slightwv (䄆 Netminder) Commented:
>>And the very same reason i asked for object size

I don't see how the LOB storage is the reason you asked for the table size but OK.

Can you explain why the size is important?  The reason for the ORA-1555 is straight forward.

I'm not really seeing the need to know the overall size of the objects to be exported.  If it is 10 Meg to 1 Petabyte.  There isn't enough UNDO based on the underlying DML changes for the export to complete in time.

>>lob segment was next.

You should have asked for it up front.  You will need to post the SQL for Enrique.
0
 
Abhimanyu SuriSr Database EngineerCommented:
@ slightww

The reason I asked for it, is just to get an understanding of environment.
If you have noticed my post, the size was asked and then I have mentioned that there is a LOB as well.

Now, for << I'm not really seeing the need to know the overall size of the objects to be exported.  If it is 10 Meg to 1 Petabyte.  There isn't enough UNDO based on the underlying DML changes for the export to complete in time >>

Most of the time it not about space is UNDO, it is the amount of activity happening on the object.

As per export estimate dump size ~ 60 GB
Now, to establish a relevancy where that space is being consumed, it all makes sense to ask for object size

Take the stats

No# of rows - 303K
Table Segment Size - 80  MB
Dump Size - 60 GB
Dump Duration - 2 hrs 16 min
UNDO Retention - 9 Hrs
UNDO Size - ~140 GB

That explains:

Space is being occupied by LOB segment, And that's why LOB was next, so no question of asking upfront  :)
UNDO is enough to accomodate table
Export is failing way before retention period

Is it guaranteed ? Probably not.

So irrespective of enough space available in UNDO, there has to be some activity going on in parallel that is either over writing the UNDO segments (probably an unexpired one which has information for the table being exported ).

Hope this helps.
0
 
slightwv (䄆 Netminder) Commented:
>>it is the amount of activity happening on the object.

Not just that single object.  It is overall database activity as long as at least one block in GININFORMES has been modified.
0
 
Abhimanyu SuriSr Database EngineerCommented:
I never said that one object, but undo block getting overwritten has to be for the object.

"Just because you set ENABLE STORAGE IN ROW doesn't mean the entire LOB is stored inline" Doesn't mean that it is not either.
As per DDL pasted, I assumed that it was an informed decision.

At the end of day, it is Question poster's environment.
0
 
Enrique Gomez EstebanConsultantAuthor Commented:
Well thank very much for your support. We were able to run the process during a window time with no other processes running and the EXPDP finished succesfully.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 8
  • 6
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now