Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Windows batch script having issues connecting to Oracle

Harry MichealsDec 27 2018 — edited Jan 4 2019

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

Comments
Post Details
Added on Dec 27 2018
24 comments
2,887 views