Create a monthly dump backup file for FND_CONCURRENT_REQUESTS

 

 We often have this requirement where in table FND_CONCURRENT_REQUESTS table requires frequent purging and due to constant purging of this table, sometimes information of a critical concurrent request also gets purged. To avoid this, a shell script was created to take dump backup of FND_CONCURRENT_REQUESTS tables.

This was scheduled prior to execution of program - Purge Concurrent Request and/or Manager Data

Please note that this will randomly create par file and  store dump file with time stamp details for future references.

#!/bin/csh

#### *** ####

# Script : create_bkp_fnd_conc

# Desc. : Creating monthly backup of fnd_concurrent_requests

# Created : 03/1/2018 - created by Maaz Khan

#### *** ####

# Check if both arguments have been passed.

#if ($#argv != 1) then

# echo 'Error : Improper Usage'

# echo 'Usage : full_export_no_rows <sid> '

# exit(1)

#endif

source $HOME/.bash_profile

export LOGDIR=/home/nas_data/backups/dump/logs

export CYCDIR=/home/oraprod/maaz

#set pass_wd=`cat ~oracle/.passwd`

#set pass_wd=`cat ~oracle/.passwd_dev`

sqlplus "/as sysdba" <<EO_STR3

whenever sqlerror exit failure;

set echo off head off feed off

select 'DUMP_DIR='||DIRECTORY_PATH from dba_directories where directory_name='DUMP_DIR';

spool $LOGDIR/dump_dir.lst

/

spool off

spool $LOGDIR/log_dir.lst

select 'LOG_DIR='||DIRECTORY_PATH from dba_directories where directory_name='LOG_DIR';

spool off

EO_STR3

export EXP_LOGDIR=`grep LOG_DIR $LOGDIR/log_dir.lst | cut -d'=' -f2`

export DMPDIR=`grep DUMP_DIR $LOGDIR/dump_dir.lst | cut -d'=' -f2`

#if ( -r $LOGDIR/$1_full_export_no_rows_restart.log ) then

#set RESTART_STEP=`cat $LOGDIR/$1_full_export_no_rows_restart.log`

# echo ' '

# echo "RESTARTING FROM STEP $RESTART_STEP "

# goto $RESTART_STEP

#endif

# echo "step 001"

echo ' '

echo ' '

# /*************************************************************************

# * This code will create a par file dynamically for table export

# /*************************************************************************

echo 'Creating par file for table export of fnd_concurrent_Requests for ${ORACLE_SID} '`date`

echo userid=system/master10 > $CYCDIR/full_export_no_rows_$ORACLE_SID.par

echo dumpfile=full_export_no_rows_`date +%d%h%y%H_%M_%S`.dmp >> $CYCDIR/full_export_no_rows_$ORACLE_SID.par

echo logfile=full_export_no_rows_`date +%d%h%y%H_%M_%S`.log >> $CYCDIR/full_export_no_rows_$ORACLE_SID.par

echo directory=DUMP_DIR >> $CYCDIR/full_export_no_rows_$ORACLE_SID.par

echo tables='applsys.FND_CONCURRENT_REQUESTS' >> $CYCDIR/full_export_no_rows_$ORACLE_SID.par

echo compression=ALL >> $CYCDIR/full_export_no_rows_$ORACLE_SID.par

echo ' '

# Run export

echo `date`

echo -n "**** Export of table ****"

# Remove previous export dump and log files

#echo "Removing the previous export dump and log files"

#ls -lart $DMPDIR/full_export_no_rows*.dmp

#rm -f $DMPDIR/full_export_no_rows*.dmp

#ls -lart $DMPDIR/full_export_no_rows.log

#rm ${DMPDIR}/full_export_no_rows.log

echo `date`

echo "Starting the export"

echo ' '

expdp parfile=${CYCDIR}/full_export_no_rows_$ORACLE_SID.par

#########################################################################

 

 

 

Sample Output -

 

 

[oraprod@prod scripts]$ sh create_bkp_fnd_conc.sh

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 3 10:08:23 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> SQL> SQL>
DUMP_DIR=/home/nas_data/backups/dump
SQL> SQL>
DUMP_DIR=/home/nas_data/backups/dump
SQL> SQL> SQL> SQL> SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options


Creating par file for table export of fnd_concurrent_Requests for ${ORACLE_SID}  Wed Jan 3 10:08:24 +04 2018

Wed Jan 3 10:08:24 +04 2018
****  Export of table ****Wed Jan 3 10:08:24 +04 2018
Starting the export


Export: Release 12.1.0.2.0 - Production on Wed Jan 3 10:08:24 2018

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, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":  system/******** parfile=/home/oraprod/maaz/full_export_no_rows_PROD.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 715 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "APPLSYS"."FND_CONCURRENT_REQUESTS"         5.624 MB   95717 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /home/nas_data/backups/dump/full_export_no_rows_03Jan1810_08_24.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jan 3 10:10:34 2018 elapsed 0 00:01:58