Autonomous Database Alert Log for Troubleshooting - DBA perspective
As we, all know that oracle Cloud Infrastructure’s Autonomous Database is a fully managed, preconfigured database environment that are completely managed by Cloud OPS team at the backend with respect to Backups, Patching, upgrades and other infrastructure activities.
There are situations that we might need access to alert log for checking certain information. Good Example would be like configuring Data Integration tools or deploying new applications that has been errored out at database level. Being typical DBA’s we try to look into Database ALERT LOG file for some hints to troubleshoot further and to fix the issues. Some time we might need to upload the alert log contents to Oracle support for the case that requires further investigation.
Viewing Autonomous Database alert log file via SQL: There is a fixed table called X$DBGALERTEXT. Oracle reads the log.xml file, parses the data and returns as rows. In addition, we can use V$DIAG_ALERT_EXT view. MESSAGE_TYPE and MESSAGE_LEVEL columns to check the
contents.
Based on the above scenario and logic, here is a simple script that has been developed to have a copy of alert log file in local compute VM that has connectivity to Autonomous Database.
Only 4 variable information needs to be passed in to script and run it in the background. The script will collect the data every 2 minutes and will be written in to the files. Older files are automatically Compressed and kept the in the same directory.
#Variables
- USER=”ADMIN”
- PWD=”Password_123″
- CONNSTR=”atp_high”
- SPOOLPATH=”/home/opc/ALOG”
- TIMEDIFF=”2″
Usage : nohup ADB_ALERT.sh &
#!/bin/bash
# Built by Raghavoracelgg@gmail.com
# This script is to serve the purpose of having a local copy of ALERT log for ADB-S Environment
#variables
USER="ADMIN"
PWD="Password_123"
CONNSTR="atp_high"
SPOOLPATH="/home/opc/ADB-Alertlog"
TIMEDIFF="2"
############
export HOME=/home/opc
ORACLE_HOME=`cat $HOME/.bash_profile|grep $ORACLE_HOME|cut -d= -f2`
export ORACLE_HOME
if [[ -z "$ORACLE_HOME" ]]; then
echo "ORACLE_HOME path needs to be set in .bash_profile" 1>&2
exit 1
fi
if [[ -z "$SPOOLPATH" ]]; then
echo "SPOOLPATH needs to be set in the script" 1>&2
exit 1
fi
if [[ -z "$TIMEDIFF" ]]; then
echo "TIMEDIFF Parameter needs to be set for minimum of 1 minute" 1>&2
exit 1
fi
PATH=$PATH:$ORACLE_HOME/bin
while :
do
ALERT_LOG=`$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect $USER/$PWD@$CONNSTR
set linesize 160 pagesize 0
set feedback off
SET TRIMOUT ON
SET TRIMSPOOL ON
col time for a20
col message_text for a120 head Message
define TIMEDIFF='$TIMEDIFF'
COLUMN SPOOL_FILE_ALERT NEW_VALUE SPOOL_FILE_ALERT NOPRINT
SELECT NVL(SUBSTR(PDB_NAME, INSTR(PDB_NAME, '_')+1), PDB_NAME)||'_ALERT.log' SPOOL_FILE_ALERT FROM DBA_PDBS;
SPOOL &SPOOL_FILE_ALERT APPEND
SELECT to_char(originating_timestamp,'DD/MM/YYYY HH24:MI:SS') time, message_text FROM v\\$diag_alert_ext where originating_timestamp > sysdate-($TIMEDIFF/1440) order by RECORD_ID ;
spool off
exit
EOF`
sec=60
minu=$((sec * TIMEDIFF))
sleep $minu
NAME=`ls $SPOOLPATH/*_ALERT.log`
if [ -e $NAME-$(date +'%Y-%m-%d' -d "yesterday") ]
then :
else
mv $NAME $NAME-$(date +'%Y-%m-%d' -d "yesterday")
gzip $NAME-$(date +'%Y-%m-%d' -d "2 day ago")
fi
done
That’s it. Now you have the local copy of alert log on Autonomous Database…..Hope you found this article useful.
Disclaimer: The views expressed on this document are my own and do not necessarily reflect the views of Oracle.