This content has been marked as final. Show 5 replies
I've got similar monitoring in place.
I do the following checks:
* Connect to the oracle-instance
* Check that I'm able to count the number of sessions in v$sessions
* Check that none of the log-files are in an erroneous state (v$logfile)
If any of the above checks fail, a mail is sent.
This is all implemented in perl/python, depending on which host operating system the monitoring is running on.
And no, in my case OEM isn't an option. since all instances are located at three different customer networks, where we have no permanent connection to (or between)
Edited by: Johan Nilsson on Jan 26, 2012 11:55 PM
CHACHA wrote:You cannot send ICMP packets from PL/SQL itself. Its network interface is limited to the TCP protocol and client sockets only.
I have 10 Servers at my office. I need to create a procedure in oracle that will moninter these all 10 servers. i.e. If the server apart from hosting oracle database get stop or crashed or not pigging then the procedure should trigger a mail.
So if you want to use the ICMP protocol, the PL/SQL code needs to step into the o/s (run the ping command), or use Java stored procs (capable of ICMP), or hook into an external procedure that provides this functionality.
You can however do TCP port "pings" in PL/SQL - and use these to check the availability of web servers, Oracle Listeners, ssh access and so on. This is done by simply checking whether that service's TCP port is a working network listening endpoint on the server. E.g.
SQL> create or replace function TcpPortPing( 2 ipAddress varchar2, -- IP address or hostname 3 tcpPort number default 80, -- port to "ping" (default: http) 4 timeOut integer default 10 -- max time in seconds to wait for a response (default 10) 5 ) return number is -- "ping" time in seconds 6 -- function: TCP PORT PING 7 -- descr: determines if IP address has a service listening on the specified port 8 -- and returns the response time of that service in seconds 9 -- 10 -- e.g. SQL> select TcpPorting( '10.0.0.1', 80 ) from dual; 11 -- begin 12 -- if TcpPorting( '10.0.0.1', 80 ) >= 0 then 13 -- <do something> 14 -- end if; 15 -- end; 16 -- 17 -- ORA-29260 network error: TNS:no listener 18 E_NO_LISTENER exception; 19 pragma exception_init( E_NO_LISTENER, -29260 ); 20 21 socket UTL_TCP.connection; 22 buffer varchar2(32767); 23 bytesInBuf number; 24 startTime number; 25 begin 26 startTime := DBMS_UTILITY.get_cpu_time; 27 28 socket := UTL_TCP.open_connection( 29 remote_host => ipAddress, 30 remote_port => tcpPort, 31 tx_timeout => timeOut 32 ); 33 34 bytesInBuf := UTL_TCP.Available( socket ); 35 if bytesInBuf > 0 then 36 buffer := UTL_TCP.get_text( socket, bytesInBuf ); 37 end if; 38 39 UTL_TCP.close_connection( socket ); 40 41 return( (DBMS_UTILITY.get_cpu_time-startTime)/100 ); 42 43 exception 44 when E_NO_LISTENER then 45 return( -1 ); 46 when OTHERS then 47 UTL_TCP.close_connection( socket ); 48 raise; 49 end; 50 / Function created.
CHACHA wrote:Latency is less than --1 second-- a 100th of seceomd - get_cpu_time is in 100th of seconds and the function turns that into seconds.
I have created a function successfully.
Can you please tell me why its return -1 when i specify wrong port number.
select tcpportping('10.1.1.12',80) from dual;
its shows 0.
So change the time calculation to a finer grain than seconds. I've never really used this function myself for production purposes and only wrote it for basic testing - which is why it returns seconds. I've never bothered to make the timing more accurate than that.
You will need to use something else in Oracle that returns times in 1000th of seconds or finer.
Change the statement:
return( (DBMS_UTILITY.get_cpu_time-startTime)/100 );
return( DBMS_UTILITY.get_cpu_time-startTime );
And that will then return 100th of seconds - which will still not be good enough in many cases. Note that network responses are typically measured in milliseconds - which is a 1000th of a second.
select tcpportping('10.1.1.12',1) from dual;That means that the socket connection was not accepted. This usually implies that the IP address or hostname was invalid, or that the hostname/IP exists, but that connections to the specified port was not accepted.
Its show -1.
Edited by: Billy Verreynne on Jan 27, 2012 2:07 PM - corrections made.
PS. Note to self - doubelecheck responses written on a Friday later afternoon after a long week...