Tuesday, February 2, 2016

Expdp fails with ORA-31693 ORA-02354 ORA-01555

one of our systems getting  ORA-31693 ORA-02354 ORA-01555 while  running expdp job...
it is taking very long and failing for some tables...

ORA-31693: Table data object "OWNER"."TABLE_NAME" 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 17 with name "_SYSSMU17_1038933293$" too small

we did not see this error before. but once we upgraded to 11.2.0.4.6... we are seeing these errors...


solution:-

issue was because we are using FLASHBACK_TIME in our daily expdp shell.....
check from oracle (Using FLASHBACK_TIME Option With DataPump Import (IMPDP) Fails With Error ORA-1555 (Doc ID 467123.1) applies to Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.4 [Release 9.2 to 11.2]

corrected the syntax for flashback_time in the script and it works...

FLASHBACK_TIME=TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS')



solution 2:- 
try to increase the stream pool size 64m and try to run the export again see if it is behaving the same....


Hope this helps....

No comments:

Post a Comment

PostgreSql: Useful Commands-

 1)   ************************* Users ***************************  -- List of users with roles assigned: SELECT usename AS role_name,   CASE...