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 220.127.116.11.
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
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? : 18.104.22.168.4 Patch:20684004
There is one that looks alike:
|Bug:19591608||TXNMGMTLOCAL: APPSPERF_DBIM:TST1213:12102: ORA-1555 WITH QUERY DURATION = 0|
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:
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.