This content has been marked as final. Show 12 replies
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
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.
p.s. You can subsitute subpartition for partition above.
You can supply a flashback_time or a flashback_scn
flashback_time="TO_TIMESTAMP(''2009-03-10 12:12:12'', ''YYYY-MM-DD HH24:MI:SS'')"
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.
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.
set echo off
set linesize 132
set heading off
select 'FLASHBACK_TIME=' || TO_CHAR(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
If you run that .sql file and write the output to a parfile -- fb.par
Then add this to your export command:
The output of the above .sql file will look like:
Hope this helps.
p.s. If you let me know what version you are running, I can see if the sysdate problem is fixed or not.
It was fixed in 18.104.22.168, so on that release you can use
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.