Consistency Check with Data Pump

Overview

Overview about Oracle Data Pump, purpose and usage.

General Information

Always use and evaluate the Oracle Alert Log while Data Pump is used!
It is a Utility for unloading data and metadata into a set of operating system files called a dump file set.
Exporting and importing data and structures
Among others provides EXPDP and IMPDP executables and a package called DBMS_DATAPUMP
Use this only with combinations of ANALYZE and DBV (or RMAN)
In case of corrupted table blocks found, the Alert Log shows and documents it, but the same is no visible in the command line while executing Data Pump.
In case of a corrupted LOB segment, Data Pump exits with the first corrupted block. Further corruptions in the LOB segment does not get recognized.
Since this method was not expressly developed as a check for corruptions, only the regular checks that take place anyway during access to data are executed.
In case it is executed from within BR*Tools the result is analyzed and provided by BR*Tools. Otherwise you have to analyze the logfile of expdp/impdp

What IS checked by Data Pump

Data from table including LOBs

What IS NOT checked by Data Pump

Indexes / Index blocks
Oracle Datafiles
Archived Redo Logs
Redo Logs
Control File
Parameter File (SPFILE)
Oracle Wallet File

Checking a single object

OS> brspace -u / -c force -f tbexport -l expdp -o full -t "<table name>"

or in case BR*Tools SSFS is used

OS> brspace -u // -c force -f tbexport -l expdp -o full -t "<table name>"
OS> expdp <username>/<password> TABLES=<table> DUMPFILE=<log file name>

Checking the entire database

OS> brspace -u / -c force -f tbexport -l expdp -o full -t "*"

or in case BR*Tools SSFS is used

OS> brspace -u // -c force -f tbexport -l expdp -o full -t "*"
OS> expdp system/manager full=y dumpfile=<file name> logfile=<log file name>

Evaluating the output

Export: Release 12.1.0.2.0 - Production on Wed Oct 7 15:04:34 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
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 "SAPSR3"."SYS_EXPORT_TABLE_01": sapsr3/******** TABLES=TESTCOMP CONTENT=DATA_ONLY
DUMPFILE=testcomp_exp_dataonly
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
. . exported "SAPSR3"."TESTCOMP" 5.132 KB 11 rows
Master table "SAPSR3"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SAPSR3.SYS_EXPORT_TABLE_01 is:
/oracle/OL1/admin/OL1/dpdump/testcomp_exp_dataonly.dmp
Job "SAPSR3"."SYS_EXPORT_TABLE_01" successfully completed at Wed Oct 7 15:04:37 2015 elapsed 0 00:00:02

Above example output shows no errors. In case corruption has been detected the error is clearly visible here or in the Alert Log.