Oralms - rbogusze/oracleinfrastructure GitHub Wiki
ORALMS (ORacle ALert log Monitoring System) - Alert log monitoring. This is the real time monitoring of all alertlogs, and some other logs that I find essential in day to day monitoring.
It became a habit for me to constantly watch the alert log of the database I was responsible for. Simple `tail -f` was sufficient. As the nuber of databases has grown in time I needed a central console that will allow me to see the logs from many places in one console.
This is how this project was born. To consolidate many `tail -f` from lots of databases.
The implementation is rather simple. I use low priviledged account that is able to login to host with database and do a remote `tail -f`, format the output and present it locally. Probably this should be done with syslog or other tool already there for the job.
Anyway this is what works for me.
We need to create an unpriviledged user that will be able to connect to database host for us, without being asked for password, and do the tail -f of database alert log.
Install some additional packages on the logwatch host
# yum install perl-LDAP gcc expect
On the DB host that will be monitored create an unprivileged user, for example named logwatch on each database host. We will connect as that user to tail the alert log from logwatch host.
# adduser logwatch # passwd logwatch
Allow for others to read the alertlog file. E.g.
$ chmod o+r /TEST/u01/app/oracle/admin/TEST/bdump/alert_TEST2.log
Now the trick is to connect remotely with ssh without the question for password. This should be done using the private/public key authentication, but for places where it is not possible (for whatever reasons) there is an expect wrapper that deals with plain ssh login.
I do not want to repeat the procedure that has been explained many times. For example use the procedure described in secion ‘Configuring the Secure Shell’
http://download.oracle.com/docs/cd/B19306_01/rac.102/b28759/preparing.htm#BABGFGBJ
The final result should be the ability to login to remote hosts without beeing asked for password.
From orainf host login to host2 and vmware10202 and make sure that you can read the alert log.
$ ssh -l logwatch dbhost logwatch@dbhost$ tail /TEST/u01/app/oracle/admin/TEST/bdump/alert_TEST.log
! Attention. This is not the way it should be setup. By using plain ssh we have to somewhere store the passwords. They are stored not in plain text, but just a hash so it is not really much consolation and can not be considered secure. Do it at your own risk. (as everything elase by the way) !
Passwords are stored in plain text file ~/.passwords file. They are not stored in plain text, but in a hash. The index is random and is here just to avoid storing this hash in LDAP, where just an index is stored.
Lets go through the procedure of adding a new password. Assume that we have a password ‘ora’ that will be used to login as logwatch user to remote host.
First lets generate a random index number $ echo $RANDOM index: 13518 Lets has this index. Hash index goes to LDAP $ echo '13518' | base64 MTM1MTgK In LDAP we will have the following attributes set: orainfOsLogwatchUser: logwatch orainfOsLogwatchUserAuth: password orainfOsLogwatchIndexHash: MTM1MTgK Now let's create the actual password hash. This goes to .password file $ echo 'ora' | base64 b3JhCg== Joining the plain index number and password hash is what is stored in the file. $ cat .passwords 13518 b3JhCg==
The way the password is read starts with the LDAP hash. After decryption it we get the plain index number. With the number we search the .passwords file and get actual password hash, which after decryption reveals the actual password. Security by obscurity, but allows not to store passwords in LDAP and store them in a plain text file where it is not so obvious which password is for what purpose.
Just not to leave the password in clear text delete the bash history
$ history -c
Again we use the LDAP to decide where are the alert logs located and which user should be used to login to remote host.
orainfOsLogwatchUser: logwatch orainfDbAlertLogFile: <provide actual locations> orainfDbAlertLogMonitoring: TRUE orainfOsLogwatchUserAuth: password/(do not set this attribute is public/private key is used, this is the default then)
Download the scripts that does the work
$ cd $ svn checkout https://github.com/rbogusze/oracleinfrastructure/trunk/oi_oralms
Test the script responsible for gathering the logs
$ cd oi_oralms $ ./oralms_gather_monitor.sh
Now you should be able to see in ‘/tmp/global_alert_raw.log’ that the logs are coming. Test it by logging to database host and add some test message to alert log. You should shortly see that on ‘global_alert_raw.log’.
Few explanations about the format. Each line is preceeded with meta information describing from which database it is comming. I use the format
<LDAP name>_<hostname>
As many of the messages recorded in alertlogs are informational in nature I decide to filter those I find useless. To do that I use the logsurfer package.
Install logsurfer
# cd /opt # wget https://distfiles.macports.org/logsurfer/logsurfer+-1.6b.tar.gz or # wget http://www.mirrorservice.org/sites/distfiles.macports.org/logsurfer/logsurfer+-1.6b.tar.gz or you can find it in ~/scripto/bin # md5sum logsurfer+-1.6b.tar.gz ad72ac6f3d13d41f6ec74a16dd7666c0 logsurfer+-1.6b.tar.gz # tar xvzf logsurfer+-1.6b.tar.gz # cd logsurfer+-1.6b # ./configure # make # make install
To make life more colorful I use the tool ‘ack’
# yum install ack
or if ack is not found in repository you use
# curl https://beyondgrep.com/ack-2.14-single-file > ack or you can find it in ~/scripto/bin # md5sum ack-2.14-single-file e74150a1609d28a70b450ef9cc2ed56b ack-2.14-single-file # chmod 755 ack # mv -i ack /bin/
Now running the console can be done in two following steps
$ cd oi_oralms $ screen $ ./oralms_filter.sh filter_all.conf Now press 'Ctrl + a c' to open new console in screen $ ./oralms_view.sh Now press 'Ctrl + a c' to open new console in screen
This will print a timestamp every 15min
$ cd /tmp $ watch -n 900 'date >> ./global_alert_raw.log'
Go back to alert log view by pressing ‘Ctr + a n’ you should see some aler log messages. Try rebooting a database or causing an ORA-600 to see some nice colors :)
You can run manually the steps to have a total control or let the crontab do it for you. Basically there are three steps:
| oralms_gather_monitor.sh | gather the alert logs from remote locations | Produces /tmp/global_alert_raw.log and files in /tmp/oralms |
| oralms_filter.sh | Filters all the trash that is defined in filter_all.conf | Takes /tmp/global_alert_raw.log and produces /tmp/global_alert.log |
| oralms_view.sh | Adds colors | Takes /tmp/global_alert.log |
Everything can be automated from crontab.
$ crontab -e # # ORALMS # @reboot (. ~/.bash_profile; sleep 30; /home/orainf/oi_oralms/oralms_gather_monitor.sh) @reboot (. ~/.bash_profile; sleep 180; cd ~/oi_oralms; /usr/bin/screen -dmS alert_log ./oralms_filter.sh filter_all.conf) @reboot (. ~/.bash_profile; sleep 240; cd tmp; /usr/bin/screen -dmS timestamp /usr/bin/watch -n 900 '/bin/date >> /tmp/global_alert_raw.log') */42 * * * * /home/orainf/oi_oralms/oralms_gather_monitor.sh */16 * * * * /home/orainf/oi_oralms/delete_stale_tail.sh 2>&1 >> /tmp/global_alert.log 58 */1 * * * /home/orainf/oi_oralms/report.sh 2>&1 >> /tmp/global_alert.log * * * * * /home/orainf/oi_oralms/create_html_page.sh > /var/www/html/alert_log.html
After that the only thing that you have to do to see the logs is:
$ cd ~/oi_oralms
$ ./oralms_view.sh
It happens often for mu to run this from virtualised machine, then I usually add this as root:
# crontab -l @reboot rm -Rf /tmp/*