-
sqlplus automation problem in a .bat file
Hi friends, I have a big problem with sqlplus on windows 2000.
I'm trying to monitor how many users are connected in each database server of my network, but some servers are not always up.
I'm using sqlplus in windows 2k for generate this report, but when I try to connect to a not-running server it sends me an error (ORA-12560 TNS: protocol adapter error) and the .bat who calls cannot continue. Obviously this error is produced because the server is not running, but I need that my .bat continue executing the next instruction to obtain the rest of the information.
I need to automate this .bat via the schedule task in order to run this script every 10 min and to know statistic information about my users.
How can avoid that sqlplus stops and continue with the rest of my .bat ?
This is my code:
-- .bat file --
sqlplus system/manager@server1 @extractinfo.sql param1
sqlplus system/manager@server2 @extractinfo.sql param2
sqlplus system/manager@server3 @extractinfo.sql param3
sqlplus system/manager@server4 @extractinfo.sql param4
Thanks in advance, any help will be very useful.
-
Re: sqlplus automation problem in a .bat file
Originally posted by Turin
I'm using sqlplus in windows 2k for generate this report, but when I try to connect to a not-running server it sends me an error (ORA-12560 TNS: protocol adapter error) and the .bat who calls cannot continue. Obviously this error is produced because the server is not running,
Well, obviously not. TNS-12560 is for other reasons. This is what it looks like when the server is not running:
Code:
SQL> conn system/manager@sys1
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
SVR4 Error: 2: No such file or directory
Warning: You are no longer connected to ORACLE.
SQL>
--OR---
Code:
SQL> conn / as sysdba
Connected.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> conn system/manager
ERROR:
ORA-01034: ORACLE not available
Warning: You are no longer connected to ORACLE.
SQL>
-
Put an exit at the end of each called script, otherwise the first invocation of sqlplus will keep you there.
sqlplus system/manager@server1 @extractinfo.sql param1
sqlplus system/manager@server2 @extractinfo.sql param2
sqlplus system/manager@server3 @extractinfo.sql param3
sqlplus system/manager@server4 @extractinfo.sql param4
-
I really like to automate it, in any case of error.
How can avoid that sqlplus stops waiting for the user interaction when there is a connection error?
Thanks again
-
Easy option : call sqlplus with NOLOG , this way :
C:\>sqlplus /NOLOG @test_connection.sql
and in your test_connection.sql script, you use something like :
whenever sqlerror exit
connect user/pass@alias
exit
Regards,
Chiappa
-
THANK YOU VERY MUCH!
Thank all of you!
Your ansers were very useful. Now I can finish my script.
Regards
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|