Day by day, alert log size will grow in Oracle database. So for housekeeping, we need to move the existing alert log to a backup location and compress there. Upon moving the alert log, the database will create a fresh alert log automatically.

  • Below is the shell script.

  • WE need to define the ORACLE_HOME in the script. and ORACLE_SID will be passed as an argument while running the script.

    # $Header: rotatealertlog.sh 
    # *====================================================================================+
    # | AUTHOR : EXATREE SUPPORT TEAM
    # | |
    # +====================================================================================+
    # |
    #!/bin/bash
    echo ========================
    echo Set Oracle Database Env
    echo ========================
     
    ORACLE_SID=$1; export ORACLE_SID
    ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1
    ORACLE_BASE=/oracle/app/oracle; export ORACLE_BASE
    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib; export LD_LIBRARY_PATH
    PATH=$ORACLE_HOME/bin:$PATH;export PATH
    TO_DATE="20`date +%y%m%d`"; export TO_DATE
     
    echo =======
    echo Extract Alert log location
    echo =======
    export VAL_DUMP=$(${ORACLE_HOME}/bin/sqlplus -S /nolog <<EOF
    conn /as sysdba
    set pages 0 feedback off;
    prompt
    SELECT value from v\$parameter where NAME='core_dump_dest';
    exit;
    EOF
    )
    export LOCATION=`echo ${VAL_DUMP} | perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'`
    export ALERTDB=${LOCATION}/alert_$ORACLE_SID.log
    export ELOG=$( echo ${ALERTDB} | sed s/cdump/trace/)
    
    echo =======
    echo Compress current
    echo =======
    
    if [ -e "$ELOG" ] ; then
     mv ${ELOG} ${ELOG}_${TO_DATE};
     gzip ${ELOG}_${TO_DATE};
     > ${ELOG}
    else
     echo not found
    fi
    
    exit
    

    2. Configure in crontab:

    SCHEDULE - Weekly once

    Here, we have passed the ORACLE_SID (PRODDB) as argument 

    00 22 * * 5 /u01/app/oracle/dbscripts/rotatealertlog.sh PRODDB
    
    SHELL script SHELL SCRIPT


    Related Topics

    Shell script to report failed login attempt in oracle
    Shell script to monitor asm diskgroup usage
    shell script for file system alert
    Shell script for monitoring blocking sessions
    Shell script for monitoring Alert log
    Shell script to delete old archives using RMAN
    Shell script to monitor goldengate process
    Shell script to monitor lag in standby datbase using dgmgrl
    Alert log rotation script in oracle
    Tablespace monitoring shell script

    You May Also Like

    Tablespace monitoring shell script
    How to find execution history of an sql_id
    Find user commits per minute in oracle database
    How to Downgrade oracle 19C Grid to 12C GRID
    Script to get cpu usage and wait event information in oracle database
    Exception in thread -main" java.lang.OutOfMemoryError: GC overhead limit exceeded with bsu.sh
    How to monitor parallel queries in oracle db
    How to move AUD$ table to another tablespace using DBMS_AUDIT_MGMT
    How to install trace file analyzer( TFACTL)
    How to disable enable log shipping in standby using dgmgrl

    From This Website

    How to drop SQL baselines in oracle
    how to send mail using utl_mail in oracle 11g
    Apply database patch from OEM Cloud control
    OUTBOUND_DBLINK_PROTOCOLS in oracle 12.2
    How to change the case (Lower to Upper and Vice Versa) in R
    DEFERRED_SEGMENT_CREATION parameter in oracle
    Shell script for monitoring blocking sessions
    Change dbsnmp password for target db in oem 12c
    TNS-01106: Listener using listener name has already been started
    SET ROWLIMIT in oracle 18C - New feature