Home » RDBMS Server » Performance Tuning » ORA-01555 error during expdp (Oracle 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production, Windows Server 2008 R2)
ORA-01555 error during expdp [message #658163] Tue, 06 December 2016 01:30 Go to next message
ashussain
Messages: 35
Registered: March 2011
Location: Saudi Arabia
Member

Hi,

I am getting the ORA-01555 errors in alert log during expdp data pump operations.

Tue Dec 06 01:44:44 2016
ORA-01555 caused by SQL statement below (SQL ID: 5dsywgg3qwjk4, SCN: 0x0000.2699242e):
SELECT * FROM RELATIONAL("XXXXXX"."Tble-1")

Tue Dec 06 02:07:07 2016
ORA-01555 caused by SQL statement below (SQL ID: 8ytcq6w1qgf7s, SCN: 0x0000.26992425):
SELECT * FROM RELATIONAL("XXXXXX"."Tble-2")


Following are the existing settings

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1


SQL> select RETENTION from dba_tablespaces where TABLESPACE_NAME='UNDOTBS1';

RETENTION
-----------
NOGUARANTEE

SQL> select distinct AUTOEXTENSIBLE from dba_data_files where TABLESPACE_NAME like 'UNDO%';

AUT
---
YES

SQL> select sum(bytes)/1024/1024  "size_in_mb" from dba_data_files where tablespace_name='UNDOTBS1';

size_in_mb
----------
      1490

Following are the details from v$undostat

SQL> select begin_time, end_time, undotsn, undoblks, maxquerylen, maxqueryid, activeblks, unexpiredblks, expiredblks, tuned_undoretention from v$undostat 
where trunc(begin_time)=trunc(sysdate) order by begin_time;

Begin Time                    End Time                         UNDOTSN   UNDOBLKS MAXQUERYLEN MAXQUERYID    ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
----------------------------- ----------------------------- ---------- ---------- ----------- ------------- ---------- ------------- ----------- -------------------
06-DEC-2016 00:01:12          06-DEC-2016 00:11:12                   2       1515         657 0rc4km05kgzb9       1264          3328       10080                1561 
06-DEC-2016 00:11:12          06-DEC-2016 00:21:12                   2        303        1266 0rc4km05kgzb9       1264          4480        9184                2171 
06-DEC-2016 00:21:12          06-DEC-2016 00:31:12                   2        314        1875 0rc4km05kgzb9       1264          4608        9184                2779 
06-DEC-2016 00:31:12          06-DEC-2016 00:41:12                   2        410        1276 0rc4km05kgzb9       1008          2432       10336                2119 
06-DEC-2016 00:41:12          06-DEC-2016 00:51:12                   2        343         678 0rc4km05kgzb9       1008          1536       11232                1521 
06-DEC-2016 00:51:12          06-DEC-2016 01:01:12                   2        775          84 0rc4km05kgzb9       1008           896       11360                 927 
06-DEC-2016 01:01:12          06-DEC-2016 01:11:12                   2        374         692 0rc4km05kgzb9       1008          1664        9184                1535 
06-DEC-2016 01:11:12          06-DEC-2016 01:21:12                   2        340        1300 0rc4km05kgzb9       1008          2312        9184                2144 
06-DEC-2016 01:21:12          06-DEC-2016 01:31:12                   2        322         703 0rc4km05kgzb9       1008          1280       10344                1546 
06-DEC-2016 01:31:12          06-DEC-2016 01:41:12                   2        368        1312 0rc4km05kgzb9       1008          1792        9704                2155 
06-DEC-2016 01:41:12          06-DEC-2016 01:51:12                   2        285         717 0rc4km05kgzb9        880          1288       10464                1500 
06-DEC-2016 01:51:12          06-DEC-2016 02:01:12                   2        658        1326 0rc4km05kgzb9        880          1672       10080                2108 
06-DEC-2016 02:01:12          06-DEC-2016 02:11:12                   2        381         732 0rc4km05kgzb9        752          2184        9952                1515 
06-DEC-2016 02:11:12          06-DEC-2016 02:21:12                   2        368        1341 0rc4km05kgzb9        752          2696        9440                2123 
06-DEC-2016 02:21:12          06-DEC-2016 02:31:12                   2        340         722 0rc4km05kgzb9        752          1408       10856                1504 
06-DEC-2016 02:31:12          06-DEC-2016 02:41:12                   2        353         394 3a9jsbg695926        752          2176       10088                1177 
06-DEC-2016 02:41:12          06-DEC-2016 02:51:12                   2        353         734 0rc4km05kgzb9        752          1792       10216                1516 
06-DEC-2016 02:51:12          06-DEC-2016 03:01:12                   2        674         138 0rc4km05kgzb9        752          2304        9832                 921 
06-DEC-2016 03:01:12          06-DEC-2016 03:11:12                   2       2770         747 0rc4km05kgzb9        752          4096        9960                1468 
06-DEC-2016 03:11:12          06-DEC-2016 03:21:12                   2        493         150 0rc4km05kgzb9        752          5376        9056                 900 
06-DEC-2016 03:21:12          06-DEC-2016 03:31:12                   2        478         758 0rc4km05kgzb9        752          4864        9568                1480 
06-DEC-2016 03:31:12          06-DEC-2016 03:41:12                   2        336        1367 0rc4km05kgzb9        752          5248        9312                2088 
06-DEC-2016 03:41:12          06-DEC-2016 03:51:12                   2        394         770 0rc4km05kgzb9        752           896       13792                1492 
06-DEC-2016 03:51:12          06-DEC-2016 04:01:12                   2       1277         175 0rc4km05kgzb9       1008          1152       12256                 900 
06-DEC-2016 04:01:12          06-DEC-2016 04:11:12                   2        328         783 0rc4km05kgzb9        752          2944        9440                1505 
06-DEC-2016 04:11:12          06-DEC-2016 04:21:12                   2        286         186 0rc4km05kgzb9        752          3072        9440                 900 
06-DEC-2016 04:21:12          06-DEC-2016 04:31:12                   2        377         795 0rc4km05kgzb9        752          1024       11488                1456 
06-DEC-2016 04:31:12          06-DEC-2016 04:41:12                   2        337         197 0rc4km05kgzb9        752          1408       11232                 900 
06-DEC-2016 04:41:12          06-DEC-2016 04:51:12                   2        319         806 0rc4km05kgzb9        752          1024       11360                1467 
06-DEC-2016 04:51:12          06-DEC-2016 05:01:12                   2        707         210 0rc4km05kgzb9       1008          2304       10080                 900 
06-DEC-2016 05:01:12          06-DEC-2016 05:11:12                   2       2022         819 0rc4km05kgzb9        752          4096        9568                1479 
06-DEC-2016 05:11:12          06-DEC-2016 05:21:12                   2        331         222 0rc4km05kgzb9        752          4608        9312                 900 
06-DEC-2016 05:21:12          06-DEC-2016 05:31:12                   2        353         526 0rc4km05kgzb9        752          3328       10080                1430 
06-DEC-2016 05:31:12          06-DEC-2016 05:41:12                   2        303        1134 0rc4km05kgzb9        880          3328       10080                2038 
06-DEC-2016 05:41:12          06-DEC-2016 05:51:12                   2        326         538 0rc4km05kgzb9        752           640       12384                1443 
06-DEC-2016 05:51:12          06-DEC-2016 06:01:12                   2        658        1147 0rc4km05kgzb9        752          2304        9952                2051 
06-DEC-2016 06:01:12          06-DEC-2016 06:11:12                   2        322         550 0rc4km05kgzb9        752          2304       10080                1454 
06-DEC-2016 06:11:12          06-DEC-2016 06:21:12                   2        330        1159 0rc4km05kgzb9        752          2432        9824                2063 
06-DEC-2016 06:21:12          06-DEC-2016 06:31:12                   2        361         563 0rc4km05kgzb9        752           896       11488                1467 
06-DEC-2016 06:31:12          06-DEC-2016 06:41:12                   2        311        1172 0rc4km05kgzb9        752          1664       10464                2015 
06-DEC-2016 06:41:12          06-DEC-2016 06:51:12                   2        307         575 0rc4km05kgzb9        752          1536       10464                1418 
06-DEC-2016 06:51:12          06-DEC-2016 07:01:12                   2        548        1183 0rc4km05kgzb9        752          2560        9568                2027 
06-DEC-2016 07:01:12          06-DEC-2016 07:11:12                   2        286         587 0rc4km05kgzb9        752          1920        9952                1430 
06-DEC-2016 07:11:12          06-DEC-2016 07:21:12                   2        319        1195 0rc4km05kgzb9        752          2304        9696                2039 
06-DEC-2016 07:21:12          06-DEC-2016 07:31:12                   2        373         600 0rc4km05kgzb9        752          1024       10720                1443 
06-DEC-2016 07:31:12          06-DEC-2016 07:41:12                   2        355        1208 0rc4km05kgzb9        752          1792        9952                2051 
06-DEC-2016 07:41:12          06-DEC-2016 07:51:12                   2        393         610 0rc4km05kgzb9        752          1280       10336                1454 
06-DEC-2016 07:51:12          06-DEC-2016 08:01:12                   2        681        1219 0rc4km05kgzb9        752          2176        9824                2001 
06-DEC-2016 08:01:12          06-DEC-2016 08:11:12                   2        378         622 0rc4km05kgzb9        752          1536       10336                1405 
06-DEC-2016 08:11:12          06-DEC-2016 08:21:12                   2        415        1231 0rc4km05kgzb9        752          1920       10208                2013 
06-DEC-2016 08:21:12          06-DEC-2016 08:31:12                   2        496         635 0rc4km05kgzb9        752          1152       10592                1418 
06-DEC-2016 08:31:12          06-DEC-2016 08:41:12                   2        416        1243 0rc4km05kgzb9        752          1920        9952                2026 
06-DEC-2016 08:41:12          06-DEC-2016 08:51:12                   2        550         647 0rc4km05kgzb9        752          1664       10208                1429 
06-DEC-2016 08:51:12          06-DEC-2016 09:01:12                   2        950        1255 0rc4km05kgzb9        752          2944        9312                2038 
06-DEC-2016 09:01:12          06-DEC-2016 09:11:12                   2        345         659 0rc4km05kgzb9        752          2944        9312                1380 
06-DEC-2016 09:11:12          06-DEC-2016 09:19:26                   2        313          63 0rc4km05kgzb9        752          2176       10208                1867

Following are the details in AWR report (01:00 till 03:00 of today)......note that the error was reported at 01:44:44 & 02:07:07

Undo Segment Summary			DB/Inst: XXXX/xxxx  Snaps: 19912-19914

--->Min/Max TR (mins) - Min and Max Tuned Retention (minutes) 
--->STO - Snapshot Too Old count, OOS - Out of Space count 
--->Undo segment block stats: 
--->uS - unexpired Stolen, your - unexpired Released, uU - unexpired reUsed 
--->eS - expired Stolen, eR - expired Released, eU - expired reUsed 

Undo   Num Undo         Number of     Max Qry    Max Tx       Min/Max        STO/        uS/uR/uU/
 TS#   blocks (K)    Transactions     Len (s) 	 Concurcy     TR (mins)      STO/ OOS 	 eS/eR/eU 
----   ----------    ------------     -------    --------     ---------      --------    -----------    
   2         4.92          21,305       1,341           6     15.5/35.9      2/0         0/0/0/0/0/0 

Undo Segment Stats                    DB/Inst: XXXX/xxxx  Snaps: 19912-19914
-> Most recent 35 Undostat rows, ordered by Time desc

                Num Undo    Number of Max Qry  Max Tx Tun Ret STO/    uS/uR/uU/
End Time          Blocks Transactions Len (s)   Concy  (mins) OOS     eS/eR/eU
------------ ----------- ------------ ------- ------- ------- ----- ------------
06-Dec 02:51 	     353        1,859 	  734       4      25 0/0   0/0/0/0/0/0 
06-Dec 02:41         353        1,714     394       3      20 0/0   0/0/0/0/0/0 
06-Dec 02:31         340        1,797     722       3      25 0/0   0/0/0/0/0/0 
06-Dec 02:21         368        1,754   1,341       4      35 0/0   0/0/0/0/0/0 
06-Dec 02:11         381        1,643     732       3      25 1/0   0/0/0/0/0/0 
06-Dec 02:01 	     658 	1,921   1,326 	    6 	   35 0/0   0/0/0/0/0/0 
06-Dec 01:51 	     285 	1,258 	  717 	    5 	   25 1/0   0/0/0/0/0/0 
06-Dec 01:41 	     368 	1,562 	1,312 	    5      36 0/0   0/0/0/0/0/0 
06-Dec 01:31 	     322 	1,503 	  703       5      26 0/0   0/0/0/0/0/0 
06-Dec 01:21 	     340        1,734   1,300       6      36 0/0   0/0/0/0/0/0 
06-Dec 01:11 	     374 	2,046     692       5      26 0/0   0/0/0/0/0/0 
06-Dec 01:01 	     775 	2,514      84       6      15 0/0   0/0/0/0/0/0 

Undo Advisor information taken now is as following:

SQL> select dbms_undo_adv.longest_query(sysdate-2,sysdate) from dual;

DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-2,SYSDATE)
----------------------------------------------
                                        1875

SQL> select dbms_undo_adv.required_retention from dual;

REQUIRED_RETENTION
------------------
            1889

SQL> select dbms_undo_adv.best_possible_retention from dual;

BEST_POSSIBLE_RETENTION
-----------------------
                3236760

SQL> sselect dbms_undo_adv.required_undo_size(1889) from dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(1889)
--------------------------------------
                                   192

SQL> select dbms_undo_adv.required_undo_size(1889,sysdate-3,sysdate) from dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(379650,SYSDATE-3,SYSDATE)
----------------------------------------------------------
                                                     192

Appreciate your kind support and guidance in resolving this issue.

Thanks & Regards
-Aijaz S Hussain
Re: ORA-01555 error during expdp [message #658166 is a reply to message #658163] Tue, 06 December 2016 01:37 Go to previous messageGo to next message
John Watson
Messages: 7220
Registered: January 2010
Location: Global Village
Senior Member
THe obvious thing to do is increase the size of your undo tablespace. I would double it.
Re: ORA-01555 error during expdp [message #658168 is a reply to message #658163] Tue, 06 December 2016 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you use FLASHBACK_TIME/FLASHBACK_SCN?

Another solution is to not execute expdp at the same time than a batch that modify the data and often commit.

Re: ORA-01555 error during expdp [message #658221 is a reply to message #658168] Wed, 07 December 2016 05:09 Go to previous messageGo to next message
wanear
Messages: 8
Registered: December 2016
Junior Member

increase the undo tablespace
and
raise the undo_retention time,maybe 3600
Re: ORA-01555 error during expdp [message #658232 is a reply to message #658221] Wed, 07 December 2016 06:44 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From what these advice?
Why 3600?
What is the rationale?

Previous Topic: Materlized View
Next Topic: query Dead Slow
Goto Forum:
  


Current Time: Sat Jan 20 06:07:19 CST 2018

Total time taken to generate the page: 0.01911 seconds