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.GININFORM ES CLUSTER=N
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_D ATA
Total estimation using BLOCKS method: 58.30 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/O WNER_GRANT /OBJECT_GR ANT
Processing object type TABLE_EXPORT/TABLE/INDEX/I NDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRA INT/CONSTR AINT
Processing object type TABLE_EXPORT/TABLE/INDEX/S TATISTICS/ INDEX_STAT ISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRA INT/REF_CO NSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATIST ICS/TABLE_ STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATIST ICS/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/dmpSGCIPROD160 118.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.
;;
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
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_D
Total estimation using BLOCKS method: 58.30 GB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/O
Processing object type TABLE_EXPORT/TABLE/INDEX/I
Processing object type TABLE_EXPORT/TABLE/CONSTRA
Processing object type TABLE_EXPORT/TABLE/INDEX/S
Processing object type TABLE_EXPORT/TABLE/CONSTRA
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATIST
Processing object type TABLE_EXPORT/TABLE/STATIST
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
**************************
Dump file set for SYS.SYS_EXPORT_TABLE_04 is:
/rman/PSAUX/dmpSGCIPROD160
Job "SYS"."SYS_EXPORT_TABLE_04
--------------------
Any suggestion?
I changed the UNDO-RETENTION parameter: alter system set undo_retention=32000 sid'*'
before running the expdp sentence.
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.
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.
ASKER
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
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 ?
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%'
ASKER
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>
--------------------------
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
"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
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
Thanks,
Suri
select sum(bytes)/1024/1024 from dba_segments where segment_name='GININFORMES' and owner='SGCEIPROD';
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
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)
Export Receives The Errors ORA-1555 ORA-22924 ORA-1578 ORA-22922 (Doc ID 787004.1)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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);
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ...
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 ...
ASKER
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
La sentencia genera el resultado:
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='GININFORMES'
SUM(BYTES)/1024/1024
--------------------
80
ASKER
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.
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
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
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
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.
"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.
ASKER
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.
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
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