If you have several 11g or 12c databases on the same machine, it can be tedious if your morning routine involves checking their respective ALERT logs. Thanks to ADRCI, and a shell script of mine, you can save yourself time.
In case you did not know, the Oracle Automatic Diagnostic Repository Command Interpreter (ADRCI) allows you to:
- display problems (Oracle errors)
- display incidents (occurences of problems)
- display/search the ALERT log
- display/search the LISTENER log
- display/search TRACE files
- purge TRACE files
- package information to be sent to Oracle Support
ADRCI> show alert -p "message_text like '%ORA-%' and originating_timestamp >=systimestamp-180" -term ;
This searches throught your ALERT log for ORA- messages originating in the last 180 days.
My script combines the display of INCIDENTS and of the ALERT log for each database on your machine. So that if you see 0 incidents, you might not even bother to scour the ALERT LOG. If you do see incidents, then you know straight away what they are and when they occured, without scrolling through the ALERT log, which will still open up if you want to peruse it.
Here are the contents of the script for Windows:
REM 160825 opens the alert logs of the Oracle databases of this machine YOURSERVERNAME
echo alertlog of 1STDB
adrci exec="set base C:\\oracle;set home diag\\rdbms\\1stdb\\1stdb; show incident ; show alert;"
echo alertlog of 2nddb
adrci exec="set base C:\\oracle;set home diag\\rdbms\\2nddb\\2nddb;show incident ; show alert;"
echo alertlog of 3rddb
adrci exec="set base C:\\oracle;set home diag\\rdbms\\3rddb\\3rddb;show incident ; show alert;"
- the double backslashes are compulsory on Windows
- the names of the folders are case-sensitive (if the folder name is upper-case, type it in upper case in the script)
- if you do not know your ADRCI base and/or your ADRCI home for each database, you can obtain that with a SELECT * FROM GV$DIAG_INFO ;
On Windows, create a bat file with that script and create a shortcut to it (in C:\Users\Public\Desktop for instance). Then double-clicking on that shortcut will run the bat file (yes, that's one mouse-click!):
You will then see in a DOS window the incidents for the 1st database (or hopefully, 0 incidents), and the alert log for the 1st database will open up in Notepad. You can peruse it if you will, then close it, and in the same DOS window, you will see the incidents (or hopefully, 0 incidents) for the 2nd database, and the alert log for the 2nd database will open up in Notepad.
This process will repeat for each of your server's databases (or rather, for those you have included in the script).
I think this is a nifty way to peruse ALERT logs when you have a handful of databases to check. It is faster than going to each alert.log location, and than launching 15 ADRCI commands.
Don't hesitate to suggest/request improvements.