ARCHIVE LOG Switch - shell script (for Oracle)

I think many of database admins will agree that it is always good idea to switch log file atleast every 20 mins or so. Especially when you have standby server setup. Doing this will ensure that redo logs are cleared up every 20 mins or so and they are shipped and applied onto standby database.

Script Benefits:

This is a simple shell script that uses plsql for checking the mount state of Database (oracle) and switch the archive log file when the open_mode of database is Read Write.  This script can be setup on both Primary and standby DB servers. This will avoid the enabling and disabling process on both servers after switchover/failover. You can setup this on crontab job on both Primary and standby DB servers, This will only switch log file if the DB is on Read Write mode

----------    Script Starts Here    -----------

#!/usr/bin/ksh

## ---- Created by Arvind Toorpu
## ----   Create Date 10/15/2015
## ----   This script can be scheduled on both PRIMARY AND STANDBY as it checks the role and switches logs
## ---- only if it is in PRIMARY ROLE
## ---- This script needs to be executed as DBA role user or user that has access to switch log file
## ---- Always good idea to switch logfile atleast every 20mins
## ----  Configure this using crontab 


mydate=`date "+%d%b%Y-%H:%M"`;
export ORACLE_HOME="/u01/app/oracle/product/11.2/db_1"
export ORACLE_SID="ORCL"
PATH=$PATH:$ORACLE_HOME/bin;export PATH;

sqlplus -s "/as sysdba" > /u01/app/oracle/admin/bin/logs/arch_switch/ARCH_SWITCH_$mydate.log <<EOF

SET SERVEROUTPUT ON;
SET FEED OFF;
declare
DB_UQ_NAME  varchar2(20);
CUR_STATUS  varchar2(20);
DB_ROLE     varchar2(20);
strQuery    varchar2(100);
CURR_ARCH number;
BEGIN
select DB_UNIQUE_NAME,open_mode,DATABASE_ROLE into DB_UQ_NAME,CUR_STATUS,DB_ROLE from v\$database;
select max(SEQUENCE#) into CURR_ARCH from  V\$ARCHIVED_LOG;
if CUR_STATUS='READ WRITE' and DB_ROLE='PRIMARY'
then
strQuery :=' alter system switch logfile';
DBMS_OUTPUT.PUT_LINE('DB_UNIQUE_NAME '||DB_UQ_NAME||' is currently in : '||CUR_STATUS ||' mode with Current ARch :'||CURR_ARCH);
EXECUTE immediate strQuery;
DBMS_OUTPUT.PUT_LINE('Switch logfile completed SUCESSFULLY !! ');
else
DBMS_OUTPUT.PUT_LINE('ERROR - DB_UNIQUE_NAME '||DB_UQ_NAME||' is currently in : '||CUR_STATUS||'.CANT SWITCH LOGFILE !!');
end if;
END;
/

EOF