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  |
|
|
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
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Feb 02 18:09:30 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
