Hello,
My manager tasked me with developing a method to connect to each database instance across several servers through the listener every day to ensure that all databases are available to our customers. He wanted to be told by his dba group, and not by the customer, if there were any issues. Monitoring with Enterprise Manager (or cloud control) is not a viable solution at our site
Initially I created a batch script that any of the dbas could run first thing in the morning. This script looped through all the instances on each server and ran a simple script (select * from v$instance;). I checked if an error was returned and output a simple report saying if any problems occurred. Life was good until my manager found out that I had hard coded passwords into the script. He said he wanted a solution that did not involve passwords.
I tried using tnsping, but that just gets you to the listener, it doesn't actually go to the database.
The solution I finally settled on was to pass a dummy ID/password to the database, and then look for "ORA-01017: invalid username/password" errors. This would tell me that the database was up and running, but it just could not connect because of the invalid credentials. This seemed like it worked for the first 6 or so databases, but then I started receiving "ORA-03113: end-of-file on communication channel" every so often.
My script looks like this:
for A in (DBA1
DBA2
DBA3
DBA4
DBA5
DBA6
DBA7
DBA8
DBA9
DBA10
DBA11
DBA12
DBA13
DBA14
DBA15
) do (echo A >> sid_check.out
echo exit | sqlplus -l -s db_user_name/db_user_pwd@A >> sid_check.out
echo. >> sid_check.out)
(percent signs deleted so this would post)
Like I said, it connect fine through the first 6 or so, then gets the communication error afterwards.
The database I am connecting to is Oracle 12.2 running on HP-UX. Patches are up to date.
I'm running this through a Windows 10 PC with Oracle 11 client. (a fellow dba uses the 12 client with the same results).
Expected results:
dba1
ERROR:
ORA-01017: invalid username/password; logon denied
Returned results:
dba7
ERROR:
ORA-03113: end-of-file on communication channel
Process ID: 0
Session ID: 0 Serial number: 0
Any idea why it will have issues after several successfully connections?
Thanks
Harry