This discussion is archived
12 Replies Latest reply: Mar 13, 2009 7:25 AM by Dean Gagne RSS

Flashback EXPDP

420143 Newbie
Currently Being Moderated
Hello

When I use EXPDP I see in my logfile = FLASHBACK automatically enabled to preserve database integrity. But I had not defined a flashback area in the init.ora.
Is my export still consistent?

Thanks. roger
  • 1. Re: Flashback EXPDP
    OrionNet Guru
    Currently Being Moderated
    Hello

    Yes, are you getting any errors while exporting?

    Regrds
  • 2. Re: Flashback EXPDP
    420143 Newbie
    Currently Being Moderated
    No, there are no errors. At the end of the logfile reads successfull!
  • 3. Re: Flashback EXPDP
    OrionNet Guru
    Currently Being Moderated
    Hello,

    You are good to go , and I take it you are not specifying flashback at expdp command.

    The FLASHBACK_SCN parameter pertains only to the flashback query capability of Oracle Database 10g release 1. It is not applicable to Flashback Database, Flashback Drop, or any other flashback capabilities new as of Oracle Database 10g release 1.

    Something to read in your leisure time
    http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#i1007466

    Regards
  • 4. Re: Flashback EXPDP
    420143 Newbie
    Currently Being Moderated
    Thanks for the fast answer!

    roger
  • 5. Re: Flashback EXPDP
    OrionNet Guru
    Currently Being Moderated
    Hello,

    If it helped mark post answered and reward points for any helpful post.

    regards
  • 6. Re: Flashback EXPDP
    Dean Gagne Expert
    Currently Being Moderated
    Hi,

    Flashback will be used for a couple of reason even if not specified. It is always used for partitioned and subpartitioned tables. Let's say you have a partitioned table with 2 partitions - one very large and one very small but not 0. When you export this table you want both partitions exported consistent between partitions. What happens in expdp is the larger partition is exported first and then the smaller partition will be exported at a later time. If flashback was not used and data was being entered in the table, the partitions could be inconsistent. When expdp sees that a table is partitioned and assigns the first partition to be exported, it sets the scn value for all partitions in the table to the same scn value as the first. This way, if the remaining partiions are exported hours later, it will be consistent. What doesn't happen automatically is consistency between tables.

    Another reason flashback is used is with Streams, but I'm not that familiar with streams. There could be other reasons that flashback could be used.

    So, you export isn't necessarily consistent, expecially in the first case I listed above.

    Dean

    p.s. You can subsitute subpartition for partition above.
  • 7. Re: Flashback EXPDP
    420143 Newbie
    Currently Being Moderated
    What can I do, when I want to create a consistent export between tables?
  • 8. Re: Flashback EXPDP
    Dean Gagne Expert
    Currently Being Moderated
    You can supply a flashback_time or a flashback_scn

    expdp ...

    flashback_time="TO_TIMESTAMP(''2009-03-10 12:12:12'', ''YYYY-MM-DD HH24:MI:SS'')"

    or

    flashback_scn = 12345678

    to get the current scn number issue this statement in sqlplus

    select TO_CHAR(dbms_flashback.get_system_change_number) from dual;

    When supplying flashback time, the datapump will convert the time to the scn number closest to that time and use that scn number.
    When supplying flashback_scn, the datapump will use that value.

    It uses that value for all data being exported. If you have 1 or millions of tables, it will use the scn for all data for all of the tables included in the job.

    Hope this helps.

    Dean
  • 9. Re: Flashback EXPDP
    420143 Newbie
    Currently Being Moderated
    and how can I use it, when I want to export the database every night as a second backup with a script? Can I write flashback_time=sysdate?

    Edited by: Roger Bruhin on Mar 13, 2009 8:01 AM
  • 10. Re: Flashback EXPDP
    Dean Gagne Expert
    Currently Being Moderated
    Hi Roger,

    The only problem with using flashback_time=sysdate is that there were bugs where sysdate was being reevaluated for each table. Not quite what you want. This has been fixed, but I don't remember which version it was in. I can see if I can find it. If you don't already use a parfile, you could use this script to create a parfile and then use the parfile.

    get_time.sql
    ===========
    set echo off
    set linesize 132
    connect scott/tiger
    set heading off
    select 'FLASHBACK_TIME=' || TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
    exit

    If you run that .sql file and write the output to a parfile -- fb.par

    Then add this to your export command:

    parfile=fb.par

    The output of the above .sql file will look like:

    FLASHBACK_TIME=2009-03-13 09:32:10

    Hope this helps.

    Dean

    p.s. If you let me know what version you are running, I can see if the sysdate problem is fixed or not.
  • 11. Re: Flashback EXPDP
    420143 Newbie
    Currently Being Moderated
    We use 10.2.0.3 Patch 6 or 11.1.0.7 Patch 6

    Thanks for your support!

    roger
  • 12. Re: Flashback EXPDP
    Dean Gagne Expert
    Currently Being Moderated
    Roger,

    It was fixed in 11.1.0.7, so on that release you can use

    flashback=sysdate

    as part of your expdp command and it should work fine.

    For 10.2.0.3, you will have to use the other method I described above.

    Please let me know if you have any questions.

    If this answered your question, mark which reply ansered the question so others searching for similar issues can get the help they need.

    Thanks

    Dean

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points