expdp, ora-1555, Query Duration = 0

During a migration of a database, a remote export/import procedure (through database-link) fails intermittently. Some tables are skipped during export, with the familiair ‘ORA-0155’ error. Snapshot too old. Procedure hasn’t changed. What’s going on, besides the obvious reasons for an ORA-0155. Database version changed from 10.2.0.4 to 11.2.0.4.

Examining the expdp-log file:

ORA-31693: Table data object “<owner>”.”<table>” failed to load/unload and is being skipped due to error:

ORA-29913: error in executing ODCIEXTTABLEPOPULATE callout

ORA-01555: snapshot too old: rollback segment number  with name “” too small

ORA-02063: preceding line from <db-link>

Examining the alert file:

ORA-01555 caused by SQL statement below (SQL ID: 3ksaxkz10m79g, Query Duration=0 sec, SCN: 0x0003.dcf545d6):

Tue Jul 03 08:12:48 2018

SELECT “INCIDENT_BELLIJST_ID”,”INCIDENT_ID”,”CONTACTPERSOON”,”CATEGORIE”,”TIJDSTIP”,”BEREIKT”,”VOICEMAIL”,”NIET_VAN_TOEPASSING_IND”,”EXTRA_BELLIJST_IND”,”HANDMATIG_TOEGEVOEGD_IND”,”TOELICHTING”,”DATE_CREATED”,”CREATED_BY”,”DATE_MODIFIED”,”MODIFIED_BY” FROM RELATIONAL(“<owner>”.”<table>”) AS OF SCN (16591963606)  “KU$”

ORA-01555 caused by SQL statement below (SQL ID: 7js4tuj9gzuy5, Query Duration=0 sec, SCN: 0x0003.dcf545d6):

Tue Jul 03 08:12:49 2018

My Oracle Support doesn’t give me very much hope. A ton of doc’s about ORA-1555 on remote queries / exports.

Helpful: In a Distributed Query, ORA-1555 Can Appear on the Remote DB Even When Query Duration is Small (Doc ID 560983.1) :

If the ORA-1555 is reported with Query Duration=0 sec like:

ORA-01555 caused by SQL statement below (SQL ID: 22upfny427j7a, Query Duration=0 sec, SCN: 0x0013.bcb8cadf)

the solution includes recreating indexes.

These errors can be caused by Indexes/table mismatch and you need to drop/recreate (not rebuild) all table indexes. [ Reference: Doc ID 1131474.1 ] :

The issue can be caused by two reasons:

1. Indexes/table mismatch

OR

2. Similar issue is reported as Bug 8231583 Abstract: ORA-1555 WHEN QUERY LENGTH < TUNED RETENTION

So.. dropping and recreated all the table indexes. Well, that didn’t help.

Is it solved in version 12? : 12.1.0.2.4 Patch:20684004

There is one that looks alike:

But… This can’t be.

A colleague of mine decided to examine the code, and it appeared to be quite simple, but still wondering that this worked all the time – or maybe it didn’t, and not noticing anybody…

In the code the scn-number of the local database was queried:

select current_scn
into v_curr_scn
from v$database;

Then, the export was done on the remote database.

– start schema export via dblink
DBMS_OUTPUT.PUT_LINE(‘**** START DATAPUMP_NETWORK_SCHEMA_EXPORT ****’ );

h1 := dbms_datapump.open
( operation => ‘EXPORT’
, job_mode => ‘SCHEMA’
, job_name => v_job_name
, remote_link => v_link_name
, version => ‘LATEST’);

DBMS_OUTPUT.PUT_LINE( ‘Successfully opened job: ‘||v_job_name);

dbms_datapump.set_parallel(handle => h1, degree => 1);
dbms_datapump.set_parameter(handle => h1, name => ‘KEEP_MASTER’, value => 0);
dbms_datapump.set_parameter(handle => h1, name => ‘INCLUDE_METADATA’, value => 1);
dbms_datapump.set_parameter(handle => h1, name => ‘METRICS’, value => 1);
dbms_datapump.set_parameter(handle => h1, name => ‘FLASHBACK_SCN’, value => v_curr_scn);
dbms_datapump.set_parameter(handle => h1, name => ‘DATA_ACCESS_METHOD’, value => ‘AUTOMATIC’);
dbms_datapump.metadata_filter(handle => h1, name => ‘SCHEMA_LIST’, value => ‘ ”<owner>” ‘);

DBMS_OUTPUT.PUT_LINE(‘Successfully set job parameters and filter’ );

The bold printed line gives it away. Using the local scn-number on the remote database was the reason. Commented the line, and it all worked flawless.

Regardz.