remove applied archive logs on primary and standby servers using batch scripts

Implementing RMAN is the best option to remove the applied archive logs. But in some scenario like in this case for me, I have to delete the applied archive log files manually on primary and standby servers to avoid the shortage of disk space.

Though this manual deletion was a temporary task till rman implementation in couple of months, I decided to write the scripts to clear the logs automatically.

The below scripts are written to check the primary and standby servers for the applied log sequence number and delete the old log files.

To remove the applied archive logs on Standby server,

1. Create a batch file as remove_archive_logs.bat
2. Open the above batch file in notepad and copy the below code

set oracle_sid=Stby
e:
cd D:\ARCH\Stby\scripts
sqlplus / as sysdba @stby_arch_files.sql
remove_stby_logfiles.bat

3. Create a sql file stby_arch_files.sql

set head off
set pagesize 0
set feedback off
set linesize 100
spool D:\ARCH\Stby\scripts\remove_stby_logfiles.bat
select 'del '||name from v$archived_log where sequence# between (select applied_seq# - 1700 from v$archive_dest_status where dest_name='STANDBY_ARCHIVE_DEST') and (select applied_seq# - 20 from v$archive_dest_status where dest_name='STANDBY_ARCHIVE_DEST');
/
spool off
exit

And schedule the remove_archive_logs.bat script on the windows scheduler on standby server.

To remove the applied archive logs on Primary server,

1. Create a batch file as remove_archive_logs_primary.bat
2. Open the above batch file in notepad and copy the below code

set oracle_sid=DEV
e:
cd D:\ARCH\DEV\scripts
sqlplus / as sysdba @remove_logs.sql
remove_logfiles.bat

3. Create a sql file remove_logs.sql

set head off
set pagesize 0
set feedback off
set linesize 100
spool D:\ARCH\DEV\scripts\remove_logfiles.bat
select 'del '||name from v$archived_log where name <>'DEV' and sequence# between (select applied_seq# - 1700 from v$archive_dest_status where applied_seq# <> '0') and (select applied_seq# - 20 from v$archive_dest_status where applied_seq# <> '0');
/
spool off
exit

And schedule the remove_archive_logs_primary.bat script on the windows scheduler on primary server.

To use the above script in your database environment, consider changing the values to suit your environment

1700 - This is the number of files to be removed from the standby server. You can increase or decrease depending on the number of archive logs shipped to the standby server each day.

20 - This specifies the log file deletion starts from applied log number - 20.

For example, If the applied log sequence is 1021 then the log files will be deleted till 1001. You may retain the log files from 1001 -1021 or remove the number 20 to delete all the archive logs upto the applied archive logs.