Monday, June 3, 2013

Oracle 10g:Get Unix/Linux Mount Point info /Alert logs in Mail By Java Source

Execute Unix/Linux commands by Java Source


This is very easy with few steps.

1.CREATE JAVA SOURCE into oracle 10g database.

Install jvm.Connect to SQL with sysdba priviledges.
@$ORACLE_HOME/javavm/install/initjvm.sql  

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Host" AS
import java.io.*;
public class Host {
  public static void executeCommand(String command) {
    try {
      String[] finalCommand;
      if (isWindows()) {
        finalCommand = new String[4];
        // Use the appropriate path for your windows version.
        //finalCommand[0] = "C:\\winnt\\system32\\cmd.exe";    // Windows NT/2000
        finalCommand[0] = "C:\\windows\\system32\\cmd.exe";    // Windows XP/2003
        //finalCommand[0] = "C:\\windows\\syswow64\\cmd.exe";  // Windows 64-bit
        finalCommand[1] = "/y";
        finalCommand[2] = "/c";
        finalCommand[3] = command;
      }
      else {
        finalCommand = new String[3];
        finalCommand[0] = "/bin/sh";
        finalCommand[1] = "-c";
        finalCommand[2] = command;
      }
 
      final Process pr = Runtime.getRuntime().exec(finalCommand);
      pr.waitFor();

      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_in = null;
          try {
            br_in = new BufferedReader(new InputStreamReader(pr.getInputStream()));
            String buff = null;
            while ((buff = br_in.readLine()) != null) {
              System.out.println("Process out :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_in.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process output.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_in.close();
            } catch (Exception ex) {}
          }
        }
      }).start();
 
      new Thread(new Runnable(){
        public void run() {
          BufferedReader br_err = null;
          try {
            br_err = new BufferedReader(new InputStreamReader(pr.getErrorStream()));
            String buff = null;
            while ((buff = br_err.readLine()) != null) {
              System.out.println("Process err :" + buff);
              try {Thread.sleep(100); } catch(Exception e) {}
            }
            br_err.close();
          }
          catch (IOException ioe) {
            System.out.println("Exception caught printing process error.");
            ioe.printStackTrace();
          }
          finally {
            try {
              br_err.close();
            } catch (Exception ex) {}
          }
        }
      }).start();
    }
    catch (Exception ex) {
      System.out.println(ex.getLocalizedMessage());
    }
  }
 
  public static boolean isWindows() {
    if (System.getProperty("os.name").toLowerCase().indexOf("windows") != -1)
      return true;
    else
      return false;
  }

};
/

2.CREATE Procedure for executing unix command.

CREATE OR REPLACE PROCEDURE host_command (p_command  IN  VARCHAR2)
AS LANGUAGE JAVA
NAME 'Host.executeCommand (java.lang.String)';
/

3.Give Privileges to user who wants to execute commads.

DECLARE
  l_schema VARCHAR2(30) := 'SYSTEM'; -- Adjust as required.
BEGIN
  DBMS_JAVA.grant_permission(l_schema, 'java.io.FilePermission', '<<ALL FILES>>', 'read ,write, execute, delete');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'writeFileDescriptor', '');
  DBMS_JAVA.grant_permission(l_schema, 'SYS:java.lang.RuntimePermission', 'readFileDescriptor', '');
END;
/

4.Test Commands.

SET SERVEROUTPUT ON SIZE 1000000
CALL DBMS_JAVA.SET_OUTPUT(1000000);

BEGIN
   host_command (p_command => '/bin/df -h');
END;
/

5. Create procedure to mail the info

CREATE OR REPLACE PROCEDURE usp_get_mount_point_info
as
l_output DBMS_OUTPUT.chararr;
l_lines  INTEGER := 2000;
vmsg varchar2(10000);
begin
vmsg:='<html><br /><br /><br /><table border=1 align="center" ><tr><td colspan=1 align="center"><b>Unix Ssystem Mount point Info</b></td></tr>';

vmsg:=vmsg||'<br /><br /><br /><table border=1 align="center" ><tr><td align="center"><b>Mount Points</b></td></tr>';

DBMS_OUTPUT.enable(1000000);

DBMS_JAVA.set_output(1000000);

host_command('/bin/df -h');

DBMS_OUTPUT.get_lines(l_output, l_lines);

FOR j IN 1 .. l_lines LOOP

    --DBMS_OUTPUT.put_line(l_output(i));
    --vmsg:=vmsg||'<tr><td>'||l_output(j)||'</td><td></tr>';   
    vmsg:=vmsg||'<tr><td>'||substr(l_output(j),25,length(l_output(j)))||'</td><td></tr>';   

END LOOP;

vmsg:=vmsg||'</table><br /><br /></html>';

UTL_MAIL.SEND(sender=>'***.***@*****.co.in', recipients=>'bhavesh.thakkar@youbroadband.co.in', cc =>'***.***@***.***.co.in' , subject=>'DatabaseName Mount Point  Info', message => vmsg,  mime_type => 'text/html');

end;
/

6.Schedule this info as per your requirement.