Oracle Database Commands and Practical Examples: Example Script to delete old dump files or archive log based on time and minutes

--------------- steps for removing old alert log files ---------------

1. Go to alert log file directory
cd /u01/app/oracle/diag/rdbms/smrprodb_dr/PRODB/trace

mkdir /u01/app/oracle/diag/rdbms/smrprodb_dr/PRODB/trace/alert_backup_logs

2. create housekeeping_alert.sh files

cd /u04/DB/scripts/

vi housekeeping_alert.sh

oracle@s1:/u04/DB/scripts> cat housekeeping_alert.sh
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=PRODB
export PATH=$PATH:$ORACLE_HOME/bin

#location of alert log files
cd /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace
#Moving log files
mv /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace/alert_RODB*.log /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace/alert_backup_logs

#Granting permissions for Dump files
chmod -R 775 /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace/alert_backup_logs/

#Compressing files

gzip /u01/app/oracle/diag/rdbms/prodb_dr/PRODB/trace/alert_backup_logs/*.log*

#Removing 60 days old dump files
find /u01/app/oracle/diag/rdbms/smrprodb_dr/SMRPRODB/trace/alert_backup_logs/ -mtime +60 -exec rm {} \;

#Removing 1440 minute old archive log files ####################

#find /u01/app/oracle/fast_recovery_area/RAC/archivelog/*.arc -mmin +1440 -exec rm {} \;

3. chmod 777 housekeeping_alert.sh

oracle@s:/u04/MDB/scripts>

4. schedule it on crontab it will run on every month (next at 2017-10-01 00:00:00)

crontab -e

0 0 1 * * /u04/MDB/scripts/housekeeping_alert.sh

$$$$$$$$$$$$$$ Archive log delete output Example ###########################

find /u01/app/oracle/fast_recovery_area/RAC/archivelog/*.arc -mmin +1440 -exec rm {} \;

oracle@s08:~> cd /u01/app/oracle/fast_recovery_area/RAC/archivelog/

oracle@s08:/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog> ls -ltr

total 4382064

-rw-r----- 1 oracle oinstall 70144 Mar 12 07:18 arch_2_843_1020157972.arc

-rw-r----- 1 oracle oinstall 247096320 Mar 12 07:18 arch_1_1190_1020157972.arc

-rw-r----- 1 oracle oinstall 244623360 Mar 12 07:18 arch_1_1191_1020157972.arc

-rw-r----- 1 oracle oinstall 244738048 Mar 12 07:18 arch_1_1192_1020157972.arc

-rw-r----- 1 oracle oinstall 176008704 Mar 12 07:18 arch_2_844_1020157972.arc

-rw-r----- 1 oracle oinstall 245496320 Mar 12 07:32 arch_1_1193_1020157972.arc

-rw-r----- 1 oracle oinstall 244393472 Mar 12 07:32 arch_1_1194_1020157972.arc

-rw-r----- 1 oracle oinstall 244472320 Mar 12 07:32 arch_1_1195_1020157972.arc

-rw-r----- 1 oracle oinstall 84999680 Mar 12 07:32 arch_2_845_1020157972.arc

-rw-r----- 1 oracle oinstall 244384256 Mar 12 07:32 arch_1_1196_1020157972.arc

-rw-r----- 1 oracle oinstall 27237376 Mar 12 07:32 arch_2_846_1020157972.arc

-rw-r----- 1 oracle oinstall 255912448 Mar 12 07:52 arch_1_1197_1020157972.arc

-rw-r----- 1 oracle oinstall 72956928 Mar 12 07:52 arch_1_1198_1020157972.arc

-rw-r----- 1 oracle oinstall 45056 Mar 12 07:52 arch_1_1199_1020157972.arc

-rw-r----- 1 oracle oinstall 96256 Mar 12 07:52 arch_1_1200_1020157972.arc

-rw-r----- 1 oracle oinstall 175212544 Mar 12 07:52 arch_2_847_1020157972.arc

-rw-r----- 1 oracle oinstall 13312 Mar 12 07:52 arch_2_849_1020157972.arc

-rw-r----- 1 oracle oinstall 45568 Mar 12 07:52 arch_2_848_1020157972.arc

-rw-r----- 1 oracle oinstall 244402688 Mar 13 07:51 arch_1_1201_1020157972.arc

-rw-r----- 1 oracle oinstall 244753920 Mar 13 07:51 arch_1_1202_1020157972.arc

-rw-r----- 1 oracle oinstall 244624384 Mar 13 07:51 arch_1_1203_1020157972.arc

-rw-r----- 1 oracle oinstall 177154560 Mar 13 07:52 arch_2_850_1020157972.arc

-rw-r----- 1 oracle oinstall 244860928 Mar 13 07:52 arch_1_1204_1020157972.arc

-rw-r----- 1 oracle oinstall 245518848 Mar 13 07:52 arch_1_1205_1020157972.arc

-rw-r----- 1 oracle oinstall 246188032 Mar 13 07:52 arch_1_1206_1020157972.arc

-rw-r----- 1 oracle oinstall 87497216 Mar 13 07:52 arch_2_851_1020157972.arc

-rw-r----- 1 oracle oinstall 244379648 Mar 13 07:52 arch_1_1207_1020157972.arc

oracle@sgdcpl08:/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog> find /u01/app/oracle/fast_recovery_area/SMRRAC/archivelog/*.arc -mtime +1 -exec rm {} \;

oracle@sgdcpl08:/u01/app/oracle/fast_recovery_area/SMRRAC/archivelog> ll