We can use the flashback parameter ( FLASHBACK_SCN or FLASHBACK_TIMESTAMP) in expdp to take a consistent backup of the table as of a specific point in time. Flashback method depends upon undo data. So if we are trying to take an export of the table with the flashback for which data is not available in UNDO, then export will fail.
DEMO:
SQL> select count(*) from DBACLASS.OBJECT_LIST; COUNT(*) ---------- 109728 SQL> SET numwidth 20 SQL> SELECT dbms_flashback.get_system_change_number FROM DUAL; GET_SYSTEM_CHANGE_NUMBER ------------------------ 11837958118968 -- >>> We will use this SCN value in expdp
Let’s insert some new data:
SQL> insert into DBACLASS.OBJECT_LIST select * from DBACLASS.OBJECT_LIST;
109728 rows created.
SQL> commit;
SQL> SQL> select count(*) from DBACLASS.OBJECT_LIST;
COUNT(*)
----------
219456
Now prepare PARFILE with FLASHBACK_SCN: ( Use the SCN value from step 1)
cat exp_flash.par dumpfile=dbaclass.dmp logfile=dbaclass.log directory=EXPDIR tables=DBACLASS.OBJECT_LIST flashback_scn=11837958118968
expdp parfile=exp_flash.par Export: Release 12.1.0.2.0 - Production on Mon Nov 19 12:40:59 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA parfile=exp_flash.par Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 29 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER . . exported "DBACLASS"."OBJECT_LIST" 12.35 MB 109728 rows --- >>>> Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /export/home/oracle/dbaclass.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 19 12:41:38 2018 elapsed 0 00:00:31
We are able to take expdp of the table with specific SCN.
Similarly, we can use FLASHBACK_TIME also:
Parfile with FLASHBACK_TIME:
cat exp_flash.par
dumpfile=dbaclass.dmp
logfile=dbaclass.log
directory=EXPDIR
tables=DBACLASS.OBJECT_LIST
FLASHBACK_TIME="TO_TIMESTAMP('19-11-2018 12:43:26', 'DD-MM-YYYY HH24:MI:SS')"
expdp parfile=exp_flash.par Export: Release 12.1.0.2.0 - Production on Mon Nov 19 12:47:36 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Username: / as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": /******** AS SYSDBA parfile=exp_flash.par Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 29 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER . . exported "DBACLASS"."OBJECT_LIST" 12.35 MB 109728 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /export/home/oracle/dbaclass.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Mon Nov 19 12:47:41 2018 elapsed 0 00:00:04
Very good explanation. Cleared concept of FLASHBACK_SCN in expdp