15. Juni 2010

LOB (BLOB, CLOB) von entfernten Datenbanken lesen ...

English title: Retrieve Lobs from remote databases

Database Links haben, wie die meisten von euch wissen, so ihre Einschränkungen. So lassen sich BLOBS und CLOBs nicht so einfach über einen Database Link abfragen. Probiert man es, so kommt man nicht besonders weit ...
Database Links have, as most of us know, their limitations. One is that you cannot transport a LOB object. An attempt leads to an error message.
SQL> select content from dateien_tab@bloblink where file_name='04_XMLDB.pdf';
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables
Eine Lösung kann allerdings (wieder einmal) Java in der Datenbank sein. Denn in dieser könnte man eine normale JDBC-Verbindung zur entfernten Datenbank öffnen, den Blob selektieren, die Bytes per Java-Streaming lesen und dann lokal (wieder als BLOB) zurückgeben.
Java in the database clould be a solution (again). This is because in a java stored procedure one can open a plain JDBC connection to the remote database, execute a SQL query selecting the LOB column and retrieve the LOB bytes with java streaming. Those bytes could then returned to the SQL layer as a LOB object.
create or replace and compile java source named "JAVA_RemoteBlob" as
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.io.*;

  public class RemoteBlob {
    private static Statement  stmt = null;
    private static ResultSet  rs   = null;
    private static Connection remoteConn = null;
    private static Connection localConn =  null;

    static { 
      try {
         localConn = DriverManager.getConnection("jdbc:default:connection");
      } catch (Exception e) {}
    }

    public static void prepareCursor (String sUser, String sPass, String sConn, String sSql) throws Exception {
      String strUrl = "jdbc:oracle:thin:@" + sConn;
      remoteConn = DriverManager.getConnection( strUrl, sUser, sPass ); 
      stmt = remoteConn.createStatement();
      rs = stmt.executeQuery(sSql);
    }

    public static void closeCursor () throws Exception {
      rs.close();
      stmt.close();
      remoteConn.close();
    }
   
    public static Datum getBlob() throws Exception {
      Datum        localLOB = null;
      Datum        remoteLOB = null;
      InputStream  remoteBytes = null;    
      OutputStream localBytes = null;    
      Reader       remoteChars = null;
      Writer       localChars = null;

      byte[] bBuffer = null;
      char[] cBuffer = null;

      int iBytesRead = 0;

      if (rs.next()) {
  
        remoteLOB = ((OracleResultSet)rs).getOracleObject(1);  

        if (remoteLOB instanceof oracle.sql.BLOB) {
          localLOB = BLOB.createTemporary(localConn, true, BLOB.DURATION_CALL);
          remoteBytes = ((BLOB)remoteLOB).getBinaryStream(0L);
          localBytes = ((BLOB)localLOB).getBinaryOutputStream(0L);
          bBuffer = new byte[((BLOB)remoteLOB).getChunkSize()];
          while ((iBytesRead = remoteBytes.read(bBuffer, 0, bBuffer.length)) != -1) {
            localBytes.write(bBuffer, 0, iBytesRead);
          }
          localBytes.flush();
          localBytes.close();
          remoteBytes.close();
        } else {
          localLOB = CLOB.createTemporary(localConn, true, CLOB.DURATION_CALL);
          remoteChars = ((CLOB)remoteLOB).getCharacterStream(0L);
          localChars = ((CLOB)localLOB).getCharacterOutputStream(0L);
          cBuffer = new char[((CLOB)remoteLOB).getChunkSize()];
          while ((iBytesRead = remoteChars.read(cBuffer, 0, cBuffer.length)) != -1) {
            localChars.write(cBuffer, 0, iBytesRead);
          }
          localChars.flush();
          localChars.close();
          remoteChars.close();
        }
      } else { 
        throw new Exception ("ResultSet Exhausted");
      }
      return localLOB;
    }
  } 
/
sho err

create or replace package remote_lob is
  procedure prepare_cursor(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  );
  procedure close_cursor;
  function get_blob return blob;
  function get_clob return clob;

  function get_blob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return blob;

  function get_clob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return clob;
end remote_lob;
/
sho err

create or replace package body remote_lob is
  procedure prepare_cursor(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) as language java name 'RemoteBlob.prepareCursor(java.lang.String,java.lang.String,java.lang.String,java.lang.String)';

  procedure close_cursor
  as language java name 'RemoteBlob.closeCursor()';

  function get_blob return blob
  as language java name 'RemoteBlob.getBlob() return oracle.sql.BLOB';

  function get_clob return clob
  as language java name 'RemoteBlob.getBlob() return oracle.sql.CLOB';

  function get_blob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return blob is 
    v_blob blob;
  begin
    prepare_cursor(p_user, p_pass, p_conn, p_sql);
    v_blob := get_blob;
    close_cursor;
    return v_blob;
  end get_blob_simple;

  function get_clob_simple(
    p_user in varchar2, p_pass in varchar2, p_conn in varchar2, p_sql in varchar2
  ) return clob is
    v_lob clob;
  begin
    prepare_cursor(p_user, p_pass, p_conn, p_sql);
    v_lob := get_clob;
    close_cursor;
    return v_lob;
  end get_clob_simple;

end remote_lob;
/
sho err
Nun habt Ihr das Package REMOTE_LOB, welches euch erlaubt, BLOBs oder CLOBS aus entfernten Datenbanken abzurufen. Der einzige Wermutstropfen ist, dass Username, Password und Connection-String hier nochmal übergeben werden müssen: die Informationen eines Database Link kann man nicht nutzen, da die das Passwort für die entfernte Datenbank nicht mehr aus den Dictionary Tabellen ausgelesen werden kann. Vor dem Test braucht Ihr noch Java-Netzwerkprivilegien - ohne schlägt der JDBC-Connect fehl ...
Now we have the package REMOTE_LOB which allows you to read BLOB oder CLOB objects from remote databases. The only disadvantage is that you need to pass username, password and the connect string to the java stored procedures. The information contained in an existing database link cannot be used since the remote users' password cannot be read from the dictionary tables. Before testing you need appropriate java privileges - without the JDBC connection will fail with the error message below.
ERROR in line 1:
ORA-29532: "Java call terminated by uncaught Java exception:
java.security.AccessControlException: the Permission (java.net.SocketPermission
sccloud033 resolve) has not been granted to SCOTT. The PL/SQL to grant this is
dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission',
'sccloud033', 'resolve' )
ORA-06512: in "SCOTT.REMOTE_LOB", line 2
ORA-06512: in line 2
Also Privilegien vergeben (als DBA) ...
So we grant the privileges (as DBA) ...
begin
  dbms_java.grant_permission( 
    GRANTEE => 'SCOTT', 
    PERMISSION_TYPE => 'SYS:java.net.SocketPermission',
    PERMISSION_NAME => '{remote-host}', 
    PERMISSION_ACTION => 'connect, resolve' 
  );
end;
/
.... und testen:
... finally we can test ...
SQL> exec remote_lob.prepare_cursor('test','test','sccloud033:1521/orcl','select content from dateien_tab');

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(remote_lob.get_blob) from dual;

DBMS_LOB.GETLENGTH(REMOTE_LOB.GET_BLOB)
---------------------------------------
                                 460800

1 row selected.

SQL> select dbms_lob.getlength(remote_lob.get_blob) from dual;

DBMS_LOB.GETLENGTH(REMOTE_LOB.GET_BLOB)
---------------------------------------
                                 751616

1 row selected.

SQL> select remote_lob.get_blob from dual;

GET_BLOB
--------------------------------------------------------------------------------
D0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF09000600000000000000
00000000050000001502000000000000001000001702000001000000FEFFFFFF0000000025020000

1 Zeile wurde ausgewählt.

SQL> exec remote_lob.close_cursor;

PL/SQL procedure successfully completed.
Mit CLOBs funktioniert das analog. Die Funktionen GET_BLOB_SIMPLE und GET_CLOB_SIMPLE dienen der "Bequemlichkeit" und machen alle drei Schritte auf einmal. Das Grundprinzip könnte sogar verwendet werden, um LOBs aus einer anderen Datenbank auszulesen - schließlich kann man auch den JDBC-Treiber einer anderen Datenbank laden. Das kommt vielleicht noch etwas später - bis jetzt erstmal viel Spaß damit.
CLOBs also work. The functions GET_BLOB_SIMPLE and GET_CLOB_SIMPLE are "convenience functions" which do the three steps (prepare, get, close) at once. The basic principle of these functions could also be used to retrieve LOB objects from non-Oracle databases - a 3rd-party JDBC driver can easily be loaded into the database JVM. More about this later ... for now have fun with this package ...

2. Juni 2010

Enterprise Manager Alerts als RSS Feed bereitstellen - ein Beispiel

English title: Provide Enterprise Manager Alerts as RSS Feed: An Example

Wie die meisten von euch wissen, kann man mit dem Enterprise Manager Server-Generierte Alerts verwalten - bei bestimmten Ereignissen (die Metriken können definiert werden), wird ein solcher Alert ausgelöst. Loggt man sich in den Enterprise Manager Database Control ein - so sieht man die aktuellen Alerts direkt auf der Homepage. Ein Hinweis: Der folgende Tipp erfordert die Lizensierung des Diagnostic Pack, denn der Zugriff auf die View, mit der wir hier arbeiten, erfordert diese Lizenz.
In Oracle Enterprise Manager you can view and manage server-generated alerts - specific events, (you can define additional event and their metric yourself) trigger alerts. After logging into Database Control you can view see the current alerts on the homepage. Note: To use this tip you need to license the Oracle Enterprise Manager Diagnostic Pack sind the view we're using here is "protected" by that license.
Schade ist nur, dass man sich stets in die Web-Anwendung des Database Control (oder Grid Control) einloggen muss, damit man etwas sieht. Enterprise Manager erlaubt es zwar, Benachrichtigungen auch per Email versenden zu lassen - für den Nachrichtenaustausch gibt es jedoch noch eine weitere, sehr verbreitete Variante: RSS.
But you need to login into Enterprise Manager in order to see the alerts. You can register an email address within Enterprise Manager in order to be notified by mail - but for message exchange there is another very popular format: RSS. This posting shows how to provide an RSS feed containing the current Enterprise Manager alerts.
Die Alerts werden im EM Repository verwaltet. Dieses liegt im Schema SYSMAN der (bei Database Control) verwalteten Oracle-Datenbank oder (bei Grid Control) der Repository-Datenbank. Dieses Posting basiert auf Database Control - es sollte prinzipiell auch mit Grid Control funktionieren, aber dazu muss der Code wahrscheinlich an der einen oder anderen Stelle geändert werden. Außerdem basiert der Tipp auf einer 11g-Datenbank, da ich das PL/SQL Embedded Gateway als Webserver für den RSS Feed nutze. In 10g funktioniert es auch, man muss aber Apache Webserver mit mod_plsql (wie bei APEX) nutzen - darauf möchte ich hier aber nicht näher eingehen.
Mit den Mitteln der Datenbank könnte man einen RSS Feed bauen. Der RSS Feed soll die Alerts anzeigen und bei Klick auf einen der Alerts soll auf die Database Control-Seite dazu verzweigt werden. RSS-Feeds bestehen aus einem bestimmten XML-Format, welches über das Web bereitgestellt wird. Zum Erzeugen des XML können die XML-Funktionen der Datenbank dienen - diese habe ich in einem früheren Blog Posting schonmal vorgestellt. Nach ein wenig Suche hatte ich auch die Datenbanktabelle gefunden, in der die EM Alerts zu finden sind: MGMT$ALERT_CURRENT im Schema SYSMAN. Sie ist dokumentiert, kann von einem "normalen" Datenbankuser aber nicht ohne weiteres betrachtet werden.
The alerts are being managed in the EM Repository, which resides in the SYSMAN schema of (Database Control) the Oracle Database or (Grid Control) the repository database. This posting is based on Database Control - it should work for Grid Control also but you might need apply changes to the posted code. Furthermore I've used an 11g database here since I need the PL/SQL Embedded Gateway as the RSS feed's webserver. In 10g you would need to use the Apache Webserver with mod_plsql (as with APEX) - which is not explained here.
Using the database it is easy to build an RSS feed containing the alerts. Since RSS is an XML format it could be generated using the XML functions of the Oracle database. There was a blog posting about those in the past. After some research I found the database view containing the alert information: MGMT$ALERT_CURRENT in the schema SYSMAN. It is (as you can see) documented but cannot be selected by a "plain" database user.
Das läuft natürlich auf einen wichtigen Sicherheitsaspekt hinaus - und das möchte ich auch diesem Posting auch ganz offen voranstellen: Es ist von Oracle beabsichtigt, dass eben nicht jeder die Alerts betrachten kann (die Web-Oberfläche des Enterprise Manager ist ja durch Login geschützt). Dennoch möchte ich hier zeigen, wie Ihr sie als RSS bereitstellen könnt. Man kann es auf eine sichere Art und Weise implementieren und der eine oder andere von euch findet es vielleicht nützlich.
So I need to say something about the securiry implications: An RSS feed is visible to everyone who knows its URL - Enterprise Manager alerts are only visible to DBA's. That's the reason why there is no public database view. I'd like to show how to provide the RSS feed anyway: You can (as we'll see) implement it in a secure manner and some of you might find it useful.
Der Vorrede genug - hier ist die PL/SQL-Prozedur, welche die Alerts als RSS-Feed aufbereitet und dann mit dem PL/SQL Web Toolkit (HTP, HTF, OWA_UTIL) ausgibt. Für diese Prozedur legt Ihr euch (eben wegen der Sicherheit) am besten einen eigenen Datenbankuser an, (bspw. ALERTRSS) der nur ein Privileg hat: SELECT ON SYSMAN.MGMT$ALERT_CURRENT. Diese Prozedur wird dann als SYS ins Schema ALERTRSS eingespielt. Vor dem Einspielen solltet Ihr jedoch noch in Zeile 5 in Inhalt der Variablen v_em_url so um, dass die URL auf eure Database Control-Installation passt.
Here is the PL/SQL procedure which generates the RSS feed XML containing the contents of SYSMAN.MGMT$ALET_CURRENT. It's based on the PL/SQL Web Toolkit (OWA_UTIL, HTP, HTF etc.). For security reasons I'd recommend to use an own database schema for that procedure (ALERTRSS). Adjust the the content of the PL/SQL variable v_em_url to your EM installation before running the script.
create or replace procedure "ALERTRSS"."EM_ALERT_RSS"(
  p_target varchar2 default null
) is
  v_rssblob blob;
  v_em_url  varchar2(1000) := 'https://{your servername here}:1158/em';
begin
 select 
  xmlelement("rss", 
   xmlattributes('2.00' as "version"),
   xmlelement("channel",
    xmlelement("title", 'Oracle Enterprise Manager Alerts'),
    (
     select 
      xmlagg(
       xmlelement("item",
        xmlelement("title", message),
        xmlelement(
         "link", 
         v_em_url || '/console/monitoring/metricDetail$'||
         'type='||replace(target_type, '_', '*_')||'$'||
         'target='||replace(target_name, '_', '*_')||'$'||
         'pageType=byDay$'||
         'metricColumn='||replace(metric_column, '_', '*_')||'$'||
         'metric='||replace(metric_name, '_', '*_')||
          decode(key_value, null, '', '$keyValue='||replace(key_value, '_', '*_')) 
        ),
        XMLElement("pubDate", 
         substr(initcap(to_char(COLLECTION_TIMESTAMP, 'DAY','nls_date_language=''english''')), 1, 3)||
         ', '||
         initcap(to_char(COLLECTION_TIMESTAMP, 'DD MON YYYY HH24:MI:SS','nls_date_language=''english'''))||
         ' '||
         replace(SESSIONTIMEZONE, ':','')
        )
       )
      ) 
     from (
       select * from "SYSMAN"."MGMT$ALERT_CURRENT"
       order by collection_timestamp desc
     )
     where target_type=p_target or p_target is null
    )
   )
  ).getblobval(nls_charset_id('AL32UTF8')) into v_rssblob
  from dual;
  owa_util.mime_header('application/rss+xml', false);
  htp.p('Content-Length: '||dbms_lob.getlength(v_rssblob));
  owa_util.http_header_close;
  wpg_docload.download_file(v_rssblob);
  dbms_lob.freetemporary(v_rssblob);
end;
/
sho err
Damit die Prozedur im Browser aufgerufen werden kann, kommen (wie oben schon erwähnt) zwei Varianten in Frage. Ab Oracle11g steht das Embedded PL/SQL Gateway bereit - in 10g muss man den Apache Webserver mit mod_plsql nutzen. Das Embedded PL/SQL Gateway nutzt den Listener selbst als Webserver.
To call this procedure from the browser we need a webserver. There are basically two methods for this. In Oracle11g we have the Embedded PL/SQL Gateway, in Oracle10g we need to use Apache Webserver with mod_plsql - as described above. The Embedded Gateway uses the Oracle Listener as the HTTP server; I'll now describe how to configure it.
Damit das PL/SQL Embedded Gateway generell funktioniert, muss der HTTP-Protokollserver aktiviert sein - das erreicht Ihr mit den Skript catxdbdbca.sql im Verzeichnis $ORACLE_HOME/rdbms/admin. Die Portnummer könnt Ihr dabei frei wählen. Wenn der HTTP-Protokollserver läuft (erkennbar an der Ausgabe von lsnrctl status), könnt Ihr (als SYS) den Database Access Descriptor (DAD) für das Schema ALERTRSS einrichten. Damit der Web-Zugriff außerdem ohne separaten Login erfolgen kann, müsst Ihr noch den User ANONYMOUS entsperren. Das ist auch der Grund, warum der User ALERTRSS kein CREATE SESSION Privileg braucht. Es ist nur die Prozedur ALERTRSS.EM_ALERT_RSS zugänglich - und das auch nur für Anfragen per Browser.
Firstly you need to enable the HTTP protocol server (if not done already). There is a SQL script for that: catxdbdbca.sql in $ORACLE_HOME/rdbms/admin. You can choose the TCP/IP port number. Use lsnrctl status to check whether the listener has opened the HTTP port. After that you can configure a new Database Access Descriptor (DAD) in order to access the procedure. The following script does this. Finally unlock the database user ANONYMOUS which makes the procedure accessible without any login - that's the reason why the user ALERTRSS does not need the CREATE SESSION privilege. This setup makes only the procedure ALERTRSS.EM_ALERT_RSS accessible - and only for browser requests.
begin
  dbms_epg.drop_dad(
     dad_name => 'ALERTRSS_DAD'
  );
end;
/
sho err

begin
  dbms_epg.create_dad(
    dad_name => 'ALERTRSS_DAD',
    path     => '/alertrss/*'
  );
  DBMS_EPG.SET_DAD_ATTRIBUTE( 
    dad_name => 'ALERTRSS_DAD', 
    attr_name => 'database-username', 
    attr_value => 'ALERTRSS'
  );
  DBMS_EPG.SET_DAD_ATTRIBUTE( 
    dad_name => 'ALERTRSS_DAD', 
    attr_name => 'default-page', 
    attr_value => 'EM_ALERT_RSS'
  );
  DBMS_EPG.AUTHORIZE_DAD(
    dad_name => 'ALERTRSS_DAD',
    user => 'ALERTRSS'
  );
end;
/
sho err

alter user anonymous account unlock
/
Nun könnt Ihr den RSS-Feed bereits aufrufen und euch die Alerts ansehen - und zwar mit folgender URL:
From now on the URL path /alertrss matches to new procedure providing the RSS feed. Try it.
  http://{host}:{port}/alertrss
Im Firefox sollte das dann in etwa so aussehen ... ein Klick auf einen der Links verzweigt direkt auf die entsprechende Seite im Oracle Enterprise Manager. Da das RSS Format standardisiert ist, kommen nun natürlich auch andere Newsreader in Betracht: So kann auch der Thunderbird die Nachrichten darstellen. Und das RSS Format eignet sich sehr gut zur Integration mit anderen "Nachrichtendiensten" oder gar mit den Alert-RSS-Feeds anderer Datenbanken ...
In Firefox it should look as follows - clicking on a link branches directly to the alert's related EM database control page. Since RSS is a standardized format there are many newsreader applications available. The Oracle database alerts could then be displayed among other news (or alerts from other databases) - the DBA has a consistent view.

Beliebte Postings