28 | 06 | 2017
Latest Articles
Popular Articles

Administration

Datapump Consistent

User Rating:  / 2
PoorBest 

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.

 

export ORACLE_HOME=/u01/app/oracle/product/10.2.0 
export ORACLE_SID=PALLADIUM
export PATH=$ORACLE_HOME/bin:$PATH
export USER=MYSELF
export USER_PWD=SECRET
export directory=EXPORT
export dumpfile=expdp_PALLADIUM.dmp
export BU_DATE=`date +%Y%m%d`
export logfile=expdp_PALLADIUM_$BU_DATE.dmp.log
mv /u01/rman/PALLADIUM_EXPDP/$dumpfile /u01/rman/PALLADIUM_EXPDP/$dumpfile.old
$ORACLE_HOME/bin/sqlplus -s $USER/$USER_PWD <<EOF
set numwidth 16
set linesize 200
set head off
set feedback off
set pagesize0
spool /home/oracle/scripts/expdp_PALLADIUM.par
select 'directory=$directory
        dumpfile=$dumpfile
        logfile=$logfile
        flashback_scn='||current_scn||q'!
        full=y
        exclude=schema:"='SYSMAN'"!' from v\$database;
spool off
exit
EOF
$ORACLE_HOME/bin/expdp $USER/$USER_PWD parfile=/home/oracle/scripts/expdp_PALLADIUM.par
rm /u01/rman/PALLADIUM_EXPDP/$dumpfile.old