Script to delete archive log from Standby database

 

Please find the below Script to delete archive logs file from Standby database location which will help us to avoid manual task to delete archive logs once those are applied on Standby database.

You can schedule below script to delete archive logs from Standby Database:

[oracle@bms04dbafde04p ~]$ cat /home/oracle/stdby_archive_deletion_script/ORCLDG_stdby_archive_deletion.sh



#!/bin/ksh
#
#
# Remove applied archivelog;
#
#
#export DATE=$(date +%m%d%y_%H%M%S)
ORACLE_SID=FDEPDBDG; export ORACLE_SID
ORACLE_BASE=/applications/oracle/d19.3.0/oracle_base; export ORACLE_BASE
ORACLE_HOME=/applications/oracle/d19.3.0/oracle_home; export ORACLE_HOME

logfile=/home/oracle/stdby_archive_deletion_script/ORCLDG_Max_sequence.txt
logfile1=/home/oracle/stdby_archive_deletion_script/ORCLDG_GAP.txt
logfile2=/home/oracle/stdby_archive_deletion_script/ORCLDG_Diskdrp_space.txt
rmanfile=/home/oracle/stdby_archive_deletion_script/ORCLDG_rman.cmd
$ORACLE_HOME/bin/sqlplus -S /nolog <<EOF > $logfile
connect / as sysdba
set head off
set pages 0
select max(sequence#) from v\$archived_log where applied = 'YES';
exit
EOF
strin=`awk '{print $1 = $1 - 100}' $logfile`
strin2=`echo $strin | cut -d" " -f1`
#echo $strin2
echo "delete noprompt archivelog until time 'sysdate-1';" > $rmanfile
$ORACLE_HOME/bin/rman target / <<EOF
@$rmanfile
exit
EOF
$ORACLE_HOME/bin/sqlplus -S /nolog <<EOF > $logfile1
connect / as sysdba
SELECT ARCH.THREAD# "Thread",
       ARCH.SEQUENCE# "Last Sequence Received",
       APPL.SEQUENCE# "Last Sequence Applied",
       (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V\$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V\$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V\$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#;
exit
EOF
$ORACLE_HOME/bin/sqlplus -S /nolog <<EOF > $logfile2
connect / as sysdba
set lines 200
set pages 200
SELECT G.NAME,
sum(b.total_mb) total_mb,
sum((b.total_mb - b.free_mb)) used_mb,
sum(B.FREE_MB)   free_mb,
decode(sum(b.total_mb),0,0,(ROUND((1- (sum(b.free_mb) / sum(b.total_mb)))*100, 2))) pct_used,
decode(sum(b.total_mb),0,0,(ROUND(((sum(b.free_mb) / sum(b.total_mb)))*100, 2))) pct_free
FROM v\$asm_disk b,v\$asm_diskgroup g
where b.group_number = g.group_number (+)
group by g.name;
exit
EOF