Oracle datapump Import (IMPDP) fails due to Error ORA-31693,ORA-31640,ORA-19505,ORA-27037

Error Details:

ORA-31693: Table data object “COMMON”.”LOC_ITEM_DSPTCH_SPEC” failed to load/unload and is being skipped due to error:
ORA-31640: unable to open dump file “/dev/shm/Q1051842/OMS_O_CUST_COMMON.dmp” for read
ORA-19505: failed to identify file “/dev/shm/Q1051842/OMS_O_CUST_COMMON.dmp”
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

Solution Description:

Issue is due to the mount/file system was not being accessible from the second node in the RAC.
Then I made Cluster=N to force Data Pump to use only the instance where the job is started and ran again data pump job using below parfile

userid=”/ as sysdba”
CLUSTER=N
directory=MYDIR
JOB_NAME=JOB24_10thFeb2015
dumpfile=OMS_O_CUST_COMMON.dmp
logfile=imp_OMS_O_CUST_COMMON_10thFeb.log
TABLE_EXISTS_ACTION=REPLACE
EXCLUDE=db_link
schemas=OMS_O,CUST,COMMON

Block Change Tracking in Oracle 10g

Oracle Block change Tracking improves the incremental Backup performance and is used to record changed blocks in each datafile in a change tracking file. If change Tracking is enabled, RMAN uses block change tracking file to identify changed blocks for incremental backups and avoids full datafile scans during the backup.

Block change tracking feature introduce in Oracle 10g R1. By default, the Block change tracking file is created as Oracle managed file in DB_CREATE_FILE_DEST.

By default, Oracle will not record block change information. You can Enable or disable the change tracking when the database is open or mounted.

Enable and Disable Block Change Tracking:
Run the below command to enable block change tracking
SQL>ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Run the below command to create change tracking file in a specified location.
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING
USING FILE '/oradata/block_change_track.ora' REUSE;

Run the below command to disable block change tracking
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Run the below Query to monitor the status of block change tracking
SQL> SELECT FILE,STATUS,BYTES FROM V$BLOCK_CHANGE_TRACKING;

Moving Block Change tracking file without Database shutdown:
If your database is 24x7 critical production and you cannot shut down, then follow the below steps. Please note that you must disable change tracking and you will lose the old contents of the change tracking file, if you choose this method.

Step1: Disable the change tracking
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Step2: Re-enable it at the new location
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE 'new_location';

Moving Block Change tracking file with Database shutdown:
Step1: Determine the change tracking file:
SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

Step2: shutdown and move or copy the tracking file.
SQL> SHUTDOWN IMMEDIATE
$ cp ‘/old_lockation/block_change_tracking.ora’ ‘/new_location/ block_change_tracking.ora’

Step3: Mount the database and rename change tracking file to new location.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE RENAME FILE ‘old_location’ TO ‘new_location’;

Step4: Open the database
SQL> ALTER DATABASE OPEN;

Block change tracking in RAC (Real Applications Clusters) environment, the file must be located on shared storage so that the file is accessible for all the nodes.

Extract Data From DB With File Count As Filename_Count Of Rows

Need Database From Database In CSV Format And CSV Filename Should Be Like "CSV_RowCount.csv"


1. Create Report.sql File In /home/oracle/ "/home/oracle/Report.sql"

Content Of Report.sql File:


Cat /home/oracle/Report.sql

spool /tmp/data/data_exp.csv
set echo       off
set feedback   off
set heading    off
set linesize   32767
set pagesize   10000
set termout    off
set trimspool  on
set verify     off
set colsep "|"
---------<Place Query>-------------
spool off

2. Create Extract_Data.sh File In /home/oracle "/home/oracle/Extract_Data.sh"

Content Of Extract_Data.sh File:

Cat /home/oracle/Extract_Data.sh

#!/bin/ksh
sqlplus -s <<!
/ as sysdba
spool /home/oracle/Data_Extract.log
/home/oracle/Report.sql
spool off
today=`date '+%Y%m%d'`;
value=$(wc -l </tmp/data/data_exp.csv |awk '{print $1}')
echo "Total number of Rows :$value"
#touch "$today"_"$value.txt"
mv /tmp/data/data_exp.csv /tmp/testdbfs/"$today"_"$value.pip"



3. Schedule Cronjob 

Crontab -l

05 00 * * * /home/oracle/Extract_Data.sh


Note:

Modify column Size , Pages Size As Per Query