Flashback_Scn and flashback_Time are two important feature of the datapump 11g.If we want to run a large export whilst the database is in use then ideally we should always use one of the two flashback parameters. The export operation is performed with data that is consistent as of the specified SCN.FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive. FLASHBACK_TIME : The SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback utility. The export operation is performed with data that is consistent as of this SCN. The FLASHBACK_SCN parameter pertains only to the Flashback Query capability of Oracle Database. It is not applicable to Flashback Database, Flashback Drop, or Flashback Data Archive. We can get the scn number from the following query : 1.) select current_scn from v$database; 2.) select dbms_flashback.get_system_change_number from dual; Here is Demo of the flashback_scn SQL> select current_scn from v$database; CURRENT_SCN ----------- 1140271 SQL> create table hr.test as select * from test; Table created. SQL> select current_scn from v$database; CURRENT_SCN ----------- 1140487 C:\\>expdp system/ramtech@noida directory=dpump schemas=hr dumpfile=flashback_hr.dmp logfile=flashlog.log flashback_scn=1140271 Export: Release 11.1.0.6.0 - Production on Saturday, 16 April, 2011 11:35:45 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\": system/********@noida directory=dpump schemas=hr dumpfile=flashback_hr.dmp logfile=flashlog.log flashback_scn=1140271 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 512 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA . . exported \"HR\".\"COUNTRIES\" 6.375 KB 25 rows . . exported \"HR\".\"DEPARTMENTS\" 7.015 KB 27 rows . . exported \"HR\".\"EMPLOYEES\" 16.80 KB 107 rows . . exported \"HR\".\"JOBS\" 6.984 KB 19 rows . . exported \"HR\".\"JOB_HISTORY\" 7.054 KB 10 rows . . exported \"HR\".\"LOCATIONS\" 8.273 KB 23 rows . . exported \"HR\".\"REGIONS\" 5.484 KB 4 rows ORA-31693: Table data object \"HR\".\"TEST\" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-01466: unable to read data - table definition has changed Master table \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: D:\\DPUMP\\FLASHBACK_HR.DMP Job \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" completed with 1 error(s) at 11:37:50 The above error show that the table \"test\" is not include in the export operation because the SCN mention is of before the table \"test\" creation. The below export will show the export upto current SCN when database is in use. C:\\>expdp system/ramtech@noida directory=dpump schemas=hr dumpfile=flashback_hr1.dmp logfile=flashback_log.log Export: Release 11.1.0.6.0 - Production on Saturday, 16 April, 2011 11:44:50 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\": system/********@noida directory=dpump schemas=hr dumpfile=flashback_hr1.dmp logfile=flashback_log.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 512 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA . . exported \"HR\".\"COUNTRIES\" 6.375 KB 25 rows . . exported \"HR\".\"DEPARTMENTS\" 7.015 KB 27 rows . . exported \"HR\".\"EMPLOYEES\" 16.80 KB 107 rows . . exported \"HR\".\"JOBS\" 6.984 KB 19 rows . . exported \"HR\".\"JOB_HISTORY\" 7.054 KB 10 rows . . exported \"HR\".\"LOCATIONS\" 8.273 KB 23 rows . . exported \"HR\".\"REGIONS\" 5.484 KB 4 rows . . exported \"HR\".\"TEST\" 5.054 KB 8 rows Master table \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: D:\\DPUMP\\FLASHBACK_HR1.DMP Job \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" successfully completed at 11:46:41 Enjoy .....
Friday, October 14, 2011
How To Flashback_Scn and flashback_Time parameter of Data Pump
Flashback_Scn and flashback_Time are two important feature of the datapump 11g.If we want to run a large export whilst the database is in use then ideally we should always use one of the two flashback parameters. The export operation is performed with data that is consistent as of the specified SCN.FLASHBACK_SCN and FLASHBACK_TIME are mutually exclusive. FLASHBACK_TIME : The SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback utility. The export operation is performed with data that is consistent as of this SCN. The FLASHBACK_SCN parameter pertains only to the Flashback Query capability of Oracle Database. It is not applicable to Flashback Database, Flashback Drop, or Flashback Data Archive. We can get the scn number from the following query : 1.) select current_scn from v$database; 2.) select dbms_flashback.get_system_change_number from dual; Here is Demo of the flashback_scn SQL> select current_scn from v$database; CURRENT_SCN ----------- 1140271 SQL> create table hr.test as select * from test; Table created. SQL> select current_scn from v$database; CURRENT_SCN ----------- 1140487 C:\\>expdp system/ramtech@noida directory=dpump schemas=hr dumpfile=flashback_hr.dmp logfile=flashlog.log flashback_scn=1140271 Export: Release 11.1.0.6.0 - Production on Saturday, 16 April, 2011 11:35:45 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\": system/********@noida directory=dpump schemas=hr dumpfile=flashback_hr.dmp logfile=flashlog.log flashback_scn=1140271 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 512 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA . . exported \"HR\".\"COUNTRIES\" 6.375 KB 25 rows . . exported \"HR\".\"DEPARTMENTS\" 7.015 KB 27 rows . . exported \"HR\".\"EMPLOYEES\" 16.80 KB 107 rows . . exported \"HR\".\"JOBS\" 6.984 KB 19 rows . . exported \"HR\".\"JOB_HISTORY\" 7.054 KB 10 rows . . exported \"HR\".\"LOCATIONS\" 8.273 KB 23 rows . . exported \"HR\".\"REGIONS\" 5.484 KB 4 rows ORA-31693: Table data object \"HR\".\"TEST\" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-01466: unable to read data - table definition has changed Master table \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: D:\\DPUMP\\FLASHBACK_HR.DMP Job \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" completed with 1 error(s) at 11:37:50 The above error show that the table \"test\" is not include in the export operation because the SCN mention is of before the table \"test\" creation. The below export will show the export upto current SCN when database is in use. C:\\>expdp system/ramtech@noida directory=dpump schemas=hr dumpfile=flashback_hr1.dmp logfile=flashback_log.log Export: Release 11.1.0.6.0 - Production on Saturday, 16 April, 2011 11:44:50 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\": system/********@noida directory=dpump schemas=hr dumpfile=flashback_hr1.dmp logfile=flashback_log.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 512 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA . . exported \"HR\".\"COUNTRIES\" 6.375 KB 25 rows . . exported \"HR\".\"DEPARTMENTS\" 7.015 KB 27 rows . . exported \"HR\".\"EMPLOYEES\" 16.80 KB 107 rows . . exported \"HR\".\"JOBS\" 6.984 KB 19 rows . . exported \"HR\".\"JOB_HISTORY\" 7.054 KB 10 rows . . exported \"HR\".\"LOCATIONS\" 8.273 KB 23 rows . . exported \"HR\".\"REGIONS\" 5.484 KB 4 rows . . exported \"HR\".\"TEST\" 5.054 KB 8 rows Master table \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: D:\\DPUMP\\FLASHBACK_HR1.DMP Job \"SYSTEM\".\"SYS_EXPORT_SCHEMA_01\" successfully completed at 11:46:41 Enjoy .....
