Forum Stats

  • 3,758,442 Users
  • 2,251,389 Discussions
  • 7,870,190 Comments

Discussions

Categories

reg: deletion of dump files

chandra_1986
chandra_1986 Member Posts: 284 Blue Ribbon
in SQL & PL/SQL

HI Experts,


have written the below script:-EXP_TAB_cmprss.sh to take the export backup through script.


Need to add in the start of script to delete export dump 3 days old and then start export backup,

script should not execute without deleting old backups, 

if it fails to delete old backup , script should terminate and send us alert. 


# $Header: EXP_TAB_cmprss.sh

# *====================================================================================+

# 

# | |

# +====================================================================================+

# |

# | FILENAME

# |

# |

# | DESCRIPTION

# | Daily Export backup script of a list of table

# | PLATFORM

# | Linux/Solaris


# +===========================================================================+

#!/bin/bash

echo Set Oracle Database Env

export ORACLE_SID=$1

export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

export PATH=$ORACLE_HOME/bin:$PATH:/usr/local/bin

export TIMESTAMP=`date +%a%d%b%Y`

export EXP_DIR=/export/home/oracle


echo =======

echo Export command

echo =======

echo $ORACLE_HOME

$ORACLE_HOME/bin/expdp \'/ as sysdba\' directory=DB_DUMPS dumpfile=expdp_tab_${TIMESTAMP}_%U.dmp logfile=expdp_log_${TIMESTAMP}.log 

tables=DBATEST.ORDER_TAB PARALLEL=5 COMPRESSION=ALL


echo SEND MAIL TO STAKE HOLDERS

echo =======

mailx -s "$ORACLE_SID $TIMESTAMP Export backup logfile" [email protected] < $EXP_DIR/expdp_log_${TIMESTAMP}.log

echo Export completed at $TIMESTAMP

exit



in EXP_DIR we have below files generated after running script


-rwxrwxrwx 1 tuser tuser 1.4K May 6 12:25 EXP_TAB_cmprss.sh

-rwxrwxrwx 1 t2n5463 odba 3.2G May 6 13:13 expdp_tab_Thu06May2021_05.dmp

-rwxrwxrwx 1 t2n5463 odba 3.1G May 6 13:13 expdp_tab_Thu06May2021_03.dmp

-rwxrwxrwx 1 t2n5463 odba 22G May 6 13:13 expdp_tab_Thu06May2021_02.dmp

-rwxrwxrwx 1 t2n5463 odba 2.9G May 6 13:13 expdp_tab_Thu06May2021_04.dmp

-rwxrwxrwx 1 t2n5463 odba 25G May 6 13:13 expdp_tab_Thu06May2021_01.dmp

-rw-r--r-- 1 t2n5463 odba 8.7K May 6 13:13 expdp_log_Thu06May2021.log




Any suggestion how can we add inside the script to , add in the start of script to delete export dump 3 days old and then start export backup,

script should not execute without deleting old backups, 

if it fails to delete old backup , script should terminate and send us alert. 

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,729 Black Diamond

    Your export creates dump files in OS directory Oracle directory object DB_DUMPS is pointing to while you showing EXP_DIR files. Is Oracle directory object DB_DUMPS pointing to same OS directory as EXP_DIR (/export/home/oracle)? Anyway, command below will delete all files in directory $EXP_DIR named expdp_tab_*.dmp (* is wildcard mathing any number of any characters - you can come up with more precise match if needed) created 3 or more days ago:

    find $EXP_DIR/expdp_tab_*.dmp -type f -ctime +3 -exec rm {} \;

    SY.

    chandra_1986
    · Share on TwitterShare on Facebook
  • chandra_1986
    chandra_1986 Member Posts: 284 Blue Ribbon

    Thanks experts,

    we have files like, containing string characters like: Thu06May2021 is above find command will accordingly fetch the details.

    so 3 days old like above Monday dumps files need to delete.

    -rwxrwxrwx 1 t2n5463 odba 3.2G May 6 13:13 expdp_tab_Thu06May2021_05.dmp

    -rwxrwxrwx 1 t2n5463 odba 3.1G May 6 13:13 expdp_tab_Thu06May2021_03.dmp

    -rwxrwxrwx 1 t2n5463 odba 22G May 6 13:13 expdp_tab_Thu06May2021_02.dmp

    -rwxrwxrwx 1 t2n5463 odba 2.9G May 6 13:13 expdp_tab_Thu06May2021_04.dmp

    -rwxrwxrwx 1 t2n5463 odba 25G May 6 13:13 expdp_tab_Thu06May2021_01.dmp

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,729 Black Diamond
    edited May 6, 2021 7:04PM

    Why do extra unnecessary coding if OS maintaint file create time? -ctime +3 looks for files created more than 3 days ago.

    SY.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,729 Black Diamond

    Now when I said created it applies just to this particular case. Attribute ctime is last change time not true file create time but since I doubted anyone changes export dump file we can consider it as create time.

    SY.

    · Share on TwitterShare on Facebook
  • chandra_1986
    chandra_1986 Member Posts: 284 Blue Ribbon

    Thanks experts,


    I tired with :-


    find  /export/home/oracle/expdp_tab_*.dmp -type f -ctime +1 -exec ls {} \;

    to check one day old files, somehow not showing any o/p.

    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,729 Black Diamond

    One day old means 24 hours before now. So if, for example, file was last changed(created) yesterday after 3:06 pm (my current time) it will not show up. You can also try using mtime (modification time) instead of ctime.

    SY.

    · Share on TwitterShare on Facebook