Wednesday, June 19, 2013

Oracle 10g/11g:How to restore archive logs from RMAN Backup ?.


Hi,

It is possible when sometimes we removed archives from archive destination and it requires for synchronize standby database.

For this need to restore from one of the available backup like RMAN,hot backup etc.

We here use RMAN backup script to get the deleted or removed archive logs.

--For All Archive logs from RMAN backup.

RUN 
ALLOCATE CHANNEL C1 DEVICE TYPE DISK; 
RESTORE ARCHIVELOG ALL; 
RELEASE CHANNEL C1;
}


--For only single archive by sequence number.


RUN 
ALLOCATE CHANNEL C1 DEVICE TYPE DISK; 
RESTORE ARCHIVELOG LOGSEQ 12345
RELEASE CHANNEL C1;
}

--For range of sequence number of archives.


RUN 
ALLOCATE CHANNEL C1 DEVICE TYPE DISK; 
RESTORE ARCHIVELOG FROM LOGSEQ 1 UNTIL 12345;
RELEASE CHANNEL C1;
}

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.