Re: Oracle 11g: problem with datapump

On Friday, May 16, 2014 2:16:30 PM UTC-6, Robert Grzesiak wrote:
> W dniu piątek, 16 maja 2014 16:25:15 UTC+2 użytkownik ddf napisał:
>
> > On Friday, May 16, 2014 1:53:58 AM UTC-6, Robert Grzesiak wrote:
>
> >
>
> > > W dniu czwartek, 15 maja 2014 17:42:10 UTC+2 użytkownik ddf napisał:
>
> >
>
> > >
>
> >
>
> > > > On Thursday, May 15, 2014 4:00:13 AM UTC-6, Robert Grzesiak wrote:
>
> >
>
> > >
>
> >
>
> > > >
>
> >
>
> > >
>
> >
>
> > > > > 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
>
> >
>
> > >
>
> >
>
> > > >
>
> >
>
> > >
>
> >
>
> > > >
>
> >
>
> > >
>
> >
>
> > > >
>
> >
>
> > >
>
> >
>
> > > > 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
>
> >
>
> > >
>
> >
>
> > >
>
> >
>
> > >
>
> >
>
> > >
>
> >
>
> > >
>
> >
>
> > > 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
>
> >
>
> >
>
> >
>
> > Post the exact expdp command you are using. Without that information no one can help you solve this issue.
>
> >
>
> >
>
> >
>
> >
>
> >
>
> > David Fitzjarrell
>
>
>
>
>
> 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

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 Received on Sat May 17 2014 - 02:29:58 CEST