*NB* The 'sysdate' command below - when using cut&paste from Windows to Unix may fail as the single quote changes when pasting into Linux - please check.
[root@ora01 oracle]# cat rman_arch_del.sh
export ORACLE_HOME=/u01/app/oracle/product/12.2.0
export ORACLE_SID=db01
export PATH=$ORACLE_HOME/bin:$PATH
delbackup () {
rman log=/home/oracle/arch_del.log << EOF
connect target /
DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-4’;
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;
exit
EOF
}
# Main
delbackup
We can delete the archivelogs by force using the following command "sledge hammer" approach
RMAN > delete noprompt force archivelog all;
[root@ora01 oracle]# crontab -l
00 22 * * * su - oracle -c "/home/oracle/rman_arch_del.sh" > /tmp/rmanarch.log
+++++++++++++++++++
At unix level run :
NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; export NLS_DATE_FORMAT
prior to running rman to output date/time in required format.
crosscheck backup;
crosscheck archivelog all;
delete expired backup;
list copy of archivelog all;
delete obsolete recovery window of 14 days;
delete expired archivelog all;
report obsolete;
delete obsolete;
list backup;
++++++++++++++++++++++
A slightly more expanded version :
#!/bin/bash
# del_arch_no_backup.sh Use this script to "cleanly" remove archivelogs when not
# backing up the database.
# Usage: del_arch_no_backup.sh sid num_of_days_to_keep
declare rman_ctl="/tmp/rman_ctl.ctl"
if [[ "$#" = 2 ]]; then
declare sid=$1
declare -i num_days=$2
else
echo "usage: del_arch_no_backup.sh sid num_of_days_to_keep"
exit 1
fi
if [[ ${ORACLE_HOME} = '' ]]; then
echo "ORACLE_HOME is not set"
exit 1
fi
# Check if DB is running
if ! `ps -ef|grep pmon|grep -q ${sid}`; then
echo "Not a running database: ${sid}"
echo "usage: del_arch_no_backup.sh sid num_of_days_to_keep"
fi
# Must keep at least one of day of archivelogs
if [[ ${num_days} < 1 ]]; then
echo "Not a valid number of days"
echo "usage: del_arch_no_backup.sh sid num_of_days_to_keep"
exit 1
fi
echo "connect target" >${rman_ctl}
echo "delete archivelog all completed before 'sysdate-${num_days}';" >>${rman_ctl}
echo "crosscheck archivelog all;" >>${rman_ctl}
$ORACLE_HOME/bin/rman @${rman_ctl} >/dev/null
rm ${rman_ctl}
exit 0
Script to clean up oracle audit files :
[oracle@scripts]$ cat clean_oracle_audit_files.sh
#!/bin/bash
#
# This shell script will dynamically find and remove Oracle audit files after a given number of days.
# This shell script will work on any uncommented entries in the oratab file, including RAC instances,
# ASM and MGMTDB instances.
#
# usage: clean_oracle_audit_files.sh full_path_to_oratab number_of_days_to_keep
#
if [[ "$#" = 2 ]]; then
declare ora_tab=$1
declare -i num_days=$2
else
echo "usage: clean_oracle_audit_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
if [[ !(-f ${ora_tab}) ]]; then
echo "Not a valid ora_tab path/filename"
echo "usage: clean_oracle_audit_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
if [[ ${num_days} < 1 ]]; then
echo "Not a valid number of days"
echo "usage: clean_oracle_audit_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
get_instance_name()
{
#Get all instances including RAC
local sid=$1
ps -eo args|grep pmon|grep $sid|sort -u
}
get_audit_home()
{
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set pages 0
set head off
set feed off
select value from v\$parameter where name = 'audit_file_dest';
EOF
}
while read -r line
do
test_char=${line:0:1}
if [[ "$test_char" != "#" && "$test_char" != "" && "$test_char" != " " ]]; then
IFS=':' read sid ora_home rest <<< "$line"
inst_name=$(get_instance_name $sid)
inst_name=${inst_name:9}
if [[ "$inst_name" != "" ]]; then #Skip non-running databases
declare -x ORACLE_HOME=${ora_home}
declare -x ORACLE_SID=${inst_name}
audit_dest=$(get_audit_home);
find ${audit_dest}/ -daystart -mtime +${num_days} -exec rm {} \;
fi
fi
done < "${ora_tab}"
[oracle@scripts]$ cat clean_oracle_trace_files.sh
#!/bin/bash
#
# This shell script will dynamically find and remove Oracle trace files after a given number of days.
# This shell script will work on any uncommented entries in the oratab file, including RAC instances,
# ASM and MGMTDB instances.
# Updated to include xml files in alert directory.
#
# usage: clean_oracle_trace_files.sh full_path_to_oratab number_of_days_to_keep
#
if [[ "$#" = 2 ]]; then
declare ora_tab=$1
declare -i num_days=$2
else
echo "usage: clean_oracle_trace_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
if [[ !(-f ${ora_tab}) ]]; then
echo "Not a valid ora_tab path/filename"
echo "usage: clean_oracle_trace_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
if [[ ${num_days} < 1 ]]; then
echo "Not a valid number of days"
echo "usage: clean_oracle_trace_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
get_instance_name()
{
#Get all instances including RAC
local sid=$1
ps -eo args|grep pmon|grep $sid|sort -u
}
get_trace_home()
{
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set pages 0
set head off
set feed off
select value from v\$diag_info where name = 'Diag Trace';
EOF
}
get_alert_home()
{
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set pages 0
set head off
set feed off
select value from v\$diag_info where name = 'Diag Alert';
EOF
}
while read -r line
do
test_char=${line:0:1}
if [[ "$test_char" != "#" && "$test_char" != "" && "$test_char" != " " ]]; then
IFS=':' read sid ora_home rest <<< "$line"
inst_name=$(get_instance_name $sid)
inst_name=${inst_name:9}
if [[ "$inst_name" != "" ]]; then #Skip non-running databases
declare -x ORACLE_HOME=${ora_home}
declare -x ORACLE_SID=${inst_name}
trace_dest=$(get_trace_home);
alert_dest=$(get_alert_home);
find ${trace_dest}/ -daystart -mtime +${num_days} -exec rm {} \;
find ${alert_dest}/ -daystart -mtime +${num_days} -exec rm {} \;
fi
fi
done < "${ora_tab}"
[oracle@usze2qmbtora01 scripts]$
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/snapcf_emrep.f'; # default
RMAN> delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
List of Archived Log Copies for database with db_unique_name EMREP
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 5 A 10-JUN-19
Name: /u01/oradata/recovery/EMREP/archivelog/2019_06_10/o1_mf_1_5_ghw4xs8r_.arc
2 1 6 A 10-JUN-19
Name: /u01/oradata/recovery/EMREP/archivelog/2019_06_10/o1_mf_1_6_ghw4xyx4_.arc
Do you really want to delete the above objects (enter YES or NO)? NO
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters are successfully stored
RMAN> delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
RMAN-08138: warning: archived log not deleted - must create more backups
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
RMAN> delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
List of Archived Log Copies for database with db_unique_name ORADB01
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 5 A 10-JUN-19
Name: /u01/oradata/recovery/EMREP/archivelog/2019_06_10/o1_mf_1_5_ghw4xs8r_.arc
2 1 6 A 10-JUN-19
Name: /u01/oradata/recovery/EMREP/archivelog/2019_06_10/o1_mf_1_6_ghw4xyx4_.arc
Do you really want to delete the above objects (enter YES or NO)?
Enter YES and et voilla.
Now put the policy back to it's original value.
The easier option is to use :
RMAN > delete noprompt force archivelog all;
[root@ora01 oracle]# crontab -l
00 22 * * * su - oracle -c "/home/oracle/rman_arch_del.sh" > /tmp/rmanarch.log
+++++++++++++++++++
At unix level run :
NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS'; export NLS_DATE_FORMAT
prior to running rman to output date/time in required format.
###### Clearing down old RMAN / rman logs
From wthin rman :crosscheck backup;
crosscheck archivelog all;
delete expired backup;
list copy of archivelog all;
delete obsolete recovery window of 14 days;
delete expired archivelog all;
report obsolete;
delete obsolete;
list backup;
++++++++++++++++++++++
A slightly more expanded version :
#!/bin/bash
# del_arch_no_backup.sh Use this script to "cleanly" remove archivelogs when not
# backing up the database.
# Usage: del_arch_no_backup.sh sid num_of_days_to_keep
declare rman_ctl="/tmp/rman_ctl.ctl"
if [[ "$#" = 2 ]]; then
declare sid=$1
declare -i num_days=$2
else
echo "usage: del_arch_no_backup.sh sid num_of_days_to_keep"
exit 1
fi
if [[ ${ORACLE_HOME} = '' ]]; then
echo "ORACLE_HOME is not set"
exit 1
fi
# Check if DB is running
if ! `ps -ef|grep pmon|grep -q ${sid}`; then
echo "Not a running database: ${sid}"
echo "usage: del_arch_no_backup.sh sid num_of_days_to_keep"
fi
# Must keep at least one of day of archivelogs
if [[ ${num_days} < 1 ]]; then
echo "Not a valid number of days"
echo "usage: del_arch_no_backup.sh sid num_of_days_to_keep"
exit 1
fi
echo "connect target" >${rman_ctl}
echo "delete archivelog all completed before 'sysdate-${num_days}';" >>${rman_ctl}
echo "crosscheck archivelog all;" >>${rman_ctl}
$ORACLE_HOME/bin/rman @${rman_ctl} >/dev/null
rm ${rman_ctl}
exit 0
Script to clean up oracle audit files :
[oracle@scripts]$ cat clean_oracle_audit_files.sh
#!/bin/bash
#
# This shell script will dynamically find and remove Oracle audit files after a given number of days.
# This shell script will work on any uncommented entries in the oratab file, including RAC instances,
# ASM and MGMTDB instances.
#
# usage: clean_oracle_audit_files.sh full_path_to_oratab number_of_days_to_keep
#
if [[ "$#" = 2 ]]; then
declare ora_tab=$1
declare -i num_days=$2
else
echo "usage: clean_oracle_audit_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
if [[ !(-f ${ora_tab}) ]]; then
echo "Not a valid ora_tab path/filename"
echo "usage: clean_oracle_audit_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
if [[ ${num_days} < 1 ]]; then
echo "Not a valid number of days"
echo "usage: clean_oracle_audit_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
get_instance_name()
{
#Get all instances including RAC
local sid=$1
ps -eo args|grep pmon|grep $sid|sort -u
}
get_audit_home()
{
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set pages 0
set head off
set feed off
select value from v\$parameter where name = 'audit_file_dest';
EOF
}
while read -r line
do
test_char=${line:0:1}
if [[ "$test_char" != "#" && "$test_char" != "" && "$test_char" != " " ]]; then
IFS=':' read sid ora_home rest <<< "$line"
inst_name=$(get_instance_name $sid)
inst_name=${inst_name:9}
if [[ "$inst_name" != "" ]]; then #Skip non-running databases
declare -x ORACLE_HOME=${ora_home}
declare -x ORACLE_SID=${inst_name}
audit_dest=$(get_audit_home);
find ${audit_dest}/ -daystart -mtime +${num_days} -exec rm {} \;
fi
fi
done < "${ora_tab}"
Script to clean up trace files :
[oracle@scripts]$ cat clean_oracle_trace_files.sh
#!/bin/bash
#
# This shell script will dynamically find and remove Oracle trace files after a given number of days.
# This shell script will work on any uncommented entries in the oratab file, including RAC instances,
# ASM and MGMTDB instances.
# Updated to include xml files in alert directory.
#
# usage: clean_oracle_trace_files.sh full_path_to_oratab number_of_days_to_keep
#
if [[ "$#" = 2 ]]; then
declare ora_tab=$1
declare -i num_days=$2
else
echo "usage: clean_oracle_trace_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
if [[ !(-f ${ora_tab}) ]]; then
echo "Not a valid ora_tab path/filename"
echo "usage: clean_oracle_trace_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
if [[ ${num_days} < 1 ]]; then
echo "Not a valid number of days"
echo "usage: clean_oracle_trace_files.sh full_path_to_oratab num_of_days_to_keep"
exit 1
fi
get_instance_name()
{
#Get all instances including RAC
local sid=$1
ps -eo args|grep pmon|grep $sid|sort -u
}
get_trace_home()
{
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set pages 0
set head off
set feed off
select value from v\$diag_info where name = 'Diag Trace';
EOF
}
get_alert_home()
{
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
set pages 0
set head off
set feed off
select value from v\$diag_info where name = 'Diag Alert';
EOF
}
while read -r line
do
test_char=${line:0:1}
if [[ "$test_char" != "#" && "$test_char" != "" && "$test_char" != " " ]]; then
IFS=':' read sid ora_home rest <<< "$line"
inst_name=$(get_instance_name $sid)
inst_name=${inst_name:9}
if [[ "$inst_name" != "" ]]; then #Skip non-running databases
declare -x ORACLE_HOME=${ora_home}
declare -x ORACLE_SID=${inst_name}
trace_dest=$(get_trace_home);
alert_dest=$(get_alert_home);
find ${trace_dest}/ -daystart -mtime +${num_days} -exec rm {} \;
find ${alert_dest}/ -daystart -mtime +${num_days} -exec rm {} \;
fi
fi
done < "${ora_tab}"
[oracle@usze2qmbtora01 scripts]$
## Log in to rman and show configuration items
RMAN> show all
RMAN> show all
2> ;
RMAN configuration parameters for database with db_unique_name ORADB01 are:
RMAN configuration parameters for database with db_unique_name ORADB01 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/snapcf_emrep.f'; # default
## Currently archivelog deletion policy is set to none - so we can delete the archivelogs
RMAN> delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
List of Archived Log Copies for database with db_unique_name EMREP
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 5 A 10-JUN-19
Name: /u01/oradata/recovery/EMREP/archivelog/2019_06_10/o1_mf_1_5_ghw4xs8r_.arc
2 1 6 A 10-JUN-19
Name: /u01/oradata/recovery/EMREP/archivelog/2019_06_10/o1_mf_1_6_ghw4xyx4_.arc
Do you really want to delete the above objects (enter YES or NO)? NO
## Change the parameter so the logs have to be backed up at least once.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters are successfully stored
RMAN> delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/u01/oradata/recovery/archivelog/2019_06_10/o1_mf_1_5_ghw4xs8r_.arc thread=1 sequence=5
RMAN-08138: warning: archived log not deleted - must create more backups
archived log file name=/u01/oradata/recovery/archivelog/2019_06_10/o1_mf_1_6_ghw4xyx4_.arc thread=1 sequence=6
## Logs won't delete so we change it the policy back to default.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored
RMAN> delete archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=269 device type=DISK
List of Archived Log Copies for database with db_unique_name ORADB01
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 5 A 10-JUN-19
Name: /u01/oradata/recovery/EMREP/archivelog/2019_06_10/o1_mf_1_5_ghw4xs8r_.arc
2 1 6 A 10-JUN-19
Name: /u01/oradata/recovery/EMREP/archivelog/2019_06_10/o1_mf_1_6_ghw4xyx4_.arc
Do you really want to delete the above objects (enter YES or NO)?
Enter YES and et voilla.
Now put the policy back to it's original value.
The easier option is to use :
RMAN > delete noprompt force archivelog all;
