Administration

Datapump Consistent

User Rating:  / 2

Details

Parent Category: Articles

Created on Wednesday, 17 March 2010 23:02

Last Updated on Friday, 12 July 2013 13:49

Published on Wednesday, 17 March 2010 23:02

Written by Guy Lambregts

Hits: 14237

Datapump and consistent database dumps

See also

Datapump

Datapump : Enhanced granularity with "EXCLUDE" and "INCLUDE"

Datapump : Attach  to and manage running datapump jobs

Datapump new features in Oracle 11G

In a busy database the DBA used to take exports with the consistent=Y attribute. The DBA starts the exports at T0 and changes are backed out with Oracle' s read consistency rollback approach. For this reason consistent exports have a higher undo cost.

exp system/secret_password file=some_dump.dmp log.some_dump.log full=Y consistent=Y

But the consistent attribute has disappeared with Oracle 's Datapump. Has it gone ? We can still make a consistent export, can' t we  ? Yes we can.

In stead off consistent=Y we can use the flashback_scn or the flashback_time attribute.  Here are some examples.

expdp system/passwd directory=flsh dumpfile=user001_2.dmp logfile =user001_2.log schemas=usr001 flashback_time=\"TO_TIMESTAMP \(TO_CHAR \(SYSDATE, \'YYYY-MM-DD HH24:MI:SS\'\), \'YYYY-MM-DD HH24:MI:SS\'\)\"

expdp system/passwd directory=flsh dumpfile=user001_2.dmp logfile =user001_2.log schemas=usr001 flashback_time=to_timestamp\(sysdate\)

Can we dump the database as it was 15 minutes ago, assuming the undo tablespace is big enough to satisfy the configured undo_retention ? yes we can, let' s prove that.

[oracle@pcguy ~]$ sqlplus tuning/tuning

SQL*Plus: Release 11.2.0.2.0 Production on Wed Apr 20 14:50:10 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set pagesize 9999

SQL> select * from t_currency_cr ; -- 4 records

CR_PK
------------
EUR
GBP
USD
YEN

SQL> insert into t_currency_cr values ('ZAR'); -- we add a fifth record

1 row created.

SQL> commit;

Commit complete.

SQL> exit
[oracle@pcguy ~]$ expdp system dumpfile=expdp_tuning.dmp logfile=expdp_tuning.log directory=backup reuse_dumpfiles=Y compression=ALL schemas=TUNING flashback_time=to_timestamp\(sysdate-15/1440\)

Export: Release 11.2.0.2.0 - Production on Wed Apr 20 14:52:06 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** dumpfile=expdp_tuning.dmp logfile=expdp_tuning.log directory=backup reuse_dumpfiles=Y compression=ALL schemas=TUNING flashback_time=to_timestamp(sysdate-15/1440)
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 198.2 MB
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/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/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "TUNING"."T_ORDER_OR"                       67.86 MB 1000000 rows
. . exported "TUNING"."T_CURRENCY_CR"                    4.718 KB       4 rows -- we export only 4 rows, the fifth record is backed out
. . exported "TUNING"."T_LOCK"                           4.906 KB       5 rows
. . exported "TUNING"."T_SUPPLIER_SU"                    6.335 KB     100 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/mnt/work/Data/Backup/expdp_tuning.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:53:07

On windows systems I have lost time with the time format for the flashback_time attribute. How to script a batch job ? I use the below code to achieve that. Note that I query v$database and pick up the current_scn and spool that toghether with some other attributes in an export parameter file.