Oracle 11g: problem with datapump

[Robert Grzesiak]

Welcome,

I have problem with datapump exp/imp using DBMS_DATAPUMP package:
This problem occures same times, not always in RAC, in cases:
- start running 1 job exp from DBO schema, next start running 2nd exp job from the same schema with another SCN
- parallel runs 2 jobs: exp from DBO schema and imp on the another schema
- other cases - generally if parallel runs exp and imp jobs on the same time
errors, for example (not always the same tables):

ORA-31693: Nie udało się wczytać/usunąć z pamięci obiektu "DBO"."SYSYSPAR" danych tabeli; obiekt jest pomijany wskutek błędu:
ORA-02354: błąd podczas eksportowania/importowania danych
ORA-01466: nie można odczytać danych - definicja tabeli jest zmieniona

ORA-31693: Nie udało się wczytać/usunąć z pamięci obiektu "PROC"."PRREL_R" danych tabeli; obiekt jest pomijany wskutek błędu:
ORA-02354: błąd podczas eksportowania/importowania danych
ORA-01466: nie można odczytać danych - definicja tabeli jest zmieniona

I thing that it is same internal problem with datapump with parallel tasks.
If jobs runs sequentially everything is ok...


Regards
Robert

[ddf]

It isn't an internal problem, you are trying to export the same data from two different snapshots at basically the same time.

That's what the series of error messages is telling you.


David Fitzjarrell

[Robert Grzesiak]

Exactly: I'm starting expdp 1 with slashback_scn 1 and after few/several minutes I'm starting expdp 2 with slashback_scn 2, because next, using impdp I need 2 different new schemas with the specified scn's state.
But... Why I can't do it parallely ? Is there any restriction in flashback mechanism on it ? If I do it sequentially: exp1 on scn1 and next after finishing start exp2 on scn2 - everything is ok.

Regards
Robert

[ddf]

Post the exact expdp command you are using. Without that information no one can help you solve this issue.


David Fitzjarrell

[Robert Grzesiak]

l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'SCHEMA',
job_name => 'DBO_EXP_' || p_prodSchema);

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'dbo' || p_prodSchema || '.dmp',
directory => 'PROD_LOG',
REUSEFILE => 1);

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'dboexp' || p_prodSchema || '.log',
directory => 'PROD_LOG',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_LIST',
value => '''DBO''');

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'NAME_EXPR',
value => '!=''RETRESC''',
object_path => 'TABLE');

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'EXCLUDE_PATH_LIST',
value => '''ROLE_GRANT''');

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'EXCLUDE_PATH_LIST',
value => '''SYSTEM_GRANT''');

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'EXCLUDE_PATH_LIST',
value => '''GRANT''');

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'EXCLUDE_PATH_LIST',
value => '''DEFAULT_ROLE''');

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'EXCLUDE_PATH_LIST',
value => '''SYNONYM''');

DBMS_DATAPUMP.SET_PARAMETER (
handle => l_dp_handle,
name => 'FLASHBACK_SCN',
value => p_scn);

DBMS_DATAPUMP.start_job(l_dp_handle);

DBMS_DATAPUMP.WAIT_FOR_JOB (l_dp_handle, l_job_state);

dbms_output.put_line('Status ' || l_job_state);


and the 2nd expdp is runnning by the same function/method...

Regards
Robert

[ddf]

It's the use of FLASHBACK_SCN causing your 'problem'; you cannot have two simultaneous sessions of expdp running against the same database, performing the same export, without encountering the errors you see. I understand what you're trying to do but you can't since Oracle won't allow it.


David Fitzjarrell