Link to home
Start Free TrialLog in
Avatar of Enrique Gomez Esteban
Enrique Gomez Esteban

asked on

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.
Avatar of Alex [***Alex140181***]
Alex [***Alex140181***]
Flag of Germany image

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
Avatar of Enrique Gomez Esteban
Enrique Gomez Esteban

ASKER

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.
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
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

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>
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
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
And,

Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922 (Doc ID 787004.1)
SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 ...
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
It seems that some backgroung process were running with the EXPDP concurrently. I will update the results asap.
>>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.
TABLESPACE "SGCEIPROD_DATA_1" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
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.
And the very same reason i asked for object size, lob segment was next.

Thanks
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>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.
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.
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.