Alert Log Rotation Script In Oracle

 Alert Log Rotation Script In Oracle

====================================

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 : DBACLASS 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



HOME / SHELL SCRIPT / ALERT LOG ROTATION SCRIPT IN ORACLE

Alert Log Rotation Script In Oracle

7845 views Less than a minute 0


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.


1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

 

 

# $Header: rotatealertlog.sh

# *====================================================================================+

# | AUTHOR : DBACLASS 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

Comments

Post a Comment

Popular posts from this blog

Shell script to monitor asm diskgroup usage

 Shell script to monitor asm diskgroup usage =========================================== REQUIREMENT: Write a shell script, which will trigger a mail alert, if the utilization of the asm diskgroup reached 90 percent. SOLUTION: 1. Below is the shell script. Make sure to update ORACLE_HOME, ORACLE_SID inside the shell script. cat /export/home/oracle/asm_dg.sh export ORACLE_HOME=/oracle/app/oracle/product/12.1.0.2/dbhome_1 export ORACLE_SID=PRODDB1 export PATH=$ORACLE_HOME/bin:$PATH logfile=/export/home/oracle/asm_dg.log sqlplus -s "/as sysdba" > /dev/null << EOF spool $logfile SET LINESIZE 150 SET PAGESIZE 9999 SET VERIFY off COLUMN group_name FORMAT a25 HEAD 'DISKGROUP_NAME' COLUMN state FORMAT a11 HEAD 'STATE' COLUMN type FORMAT a6 HEAD 'TYPE' COLUMN total_mb FORMAT 999,999,999 HEAD 'TOTAL SIZE(GB)' COLUMN free_mb FORMAT 999,999,999 HEAD 'FREE SIZE (GB)' COLUMN used_mb FORMAT 999,999,999 HEAD 'USED SIZE (GB)' COLUMN pct...
Read more

Shell Script To Monitor Lag In Standby Datbase Using Dgmgrl

 Shell Script To Monitor Lag In Standby Datbase Using Dgmgrl =========================================================== Below script is helpful in monitoring lag in standby database and send mail to DBAs in case the lag is increasing. For the script to work, make sure dataguard broker is enabled between primary and standby database.   SEE DGBROKER ARTICLE:  How to setup dgbroker in oracle 12c: Useful dgmgrl commands:   SCRIPT PREPARATION: PRIMARY DB UNIQUE_NAME – > PRIMDB STANDBY DB UNIQUE_NAME -> STYDB cat /home/oracle/dgmgrl_standby_lag.sh #!/bin/bash export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1 export ORACLE_SID=primdb export PATH=$ORACLE_HOME/bin:$PATH echo -e “show database stydb”|${ORACLE_HOME}/bin/dgmgrl sys/orcl1234 > DB_DG_DATABASE.log cat /home/oracle/DB_DG_DATABASE.log  | grep “Apply Lag”  > FILTERED_DB_DG_DATABASE.log time_value=`cut -d ” ” -f 14 FILTERED_DB_DG_DATABASE.log` time_param=`cut -d ” ” -f 15 FILTERED_DB_...
Read more