This discussion is archived
5 Replies Latest reply: Jan 27, 2012 6:13 AM by BillyVerreynne RSS

Moninter servers

Chanchal Wankhade Journeyer
Currently Being Moderated
Dear All,

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.


Do anybody have code for the same or any idea are wellcome.



Thanks.
Chanchal Wankhade.
  • 1. Re: Moninter servers
    NikolayIvankin Expert
    Currently Being Moderated
    Use Enterprise Manager Grid Control http://www.oracle.com/technetwork/oem/index.html
  • 2. Re: Moninter servers
    819493 Pro
    Currently Being Moderated
    Hi,

    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)

    HtH
    Johan

    Edited by: Johan Nilsson on Jan 26, 2012 11:55 PM
  • 3. Re: Moninter servers
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    CHACHA wrote:

    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.
    You cannot send ICMP packets from PL/SQL itself. Its network interface is limited to the TCP protocol and client sockets only.

    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.
  • 4. Re: Moninter servers
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Dear Billy,

    I have created a function successfully.

    Can you please tell me why its return -1 when i specify wrong port number.
    Ie.
    select tcpportping('10.1.1.12',80) from dual;
    its shows 0.
    select tcpportping('10.1.1.12',1) from dual;
    Its show -1.

    Why...

    Regards,
    Chanchal Wankhade.
  • 5. Re: Moninter servers
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    CHACHA wrote:

    I have created a function successfully.
    Can you please tell me why its return -1 when i specify wrong port number.
    Ie.
    select tcpportping('10.1.1.12',80) from dual;
    its shows 0.
    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.

    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 );
    to:
    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;
    Its show -1.
    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.

     
    <i>
    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...
    </i>

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points