12 Replies Latest reply: Mar 13, 2009 9:25 AM by Dean Gagne-Oracle RSS

    Flashback EXPDP

    420143
      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
          Hello

          Yes, are you getting any errors while exporting?

          Regrds
          • 2. Re: Flashback EXPDP
            420143
            No, there are no errors. At the end of the logfile reads successfull!
            • 3. Re: Flashback EXPDP
              OrionNet
              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
                Thanks for the fast answer!

                roger
                • 5. Re: Flashback EXPDP
                  OrionNet
                  Hello,

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

                  regards
                  • 6. Re: Flashback EXPDP
                    Dean Gagne-Oracle
                    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
                      What can I do, when I want to create a consistent export between tables?
                      • 8. Re: Flashback EXPDP
                        Dean Gagne-Oracle
                        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
                          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-Oracle
                            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
                              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-Oracle
                                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