Einsatz von Oracle bei DB Research

Einsatz von Oracle bei DB Research
Guido Haase
DOAG Regionaltreffen
23.10.2007
Agenda
1
Wer sind wir? Was machen wir?
2
Oracle Infrastruktur und Konventionen
3
Migration Oracle 9.2 (Linux, Latin) nach 10.2 (AIX, UTF-8)
4
Einsatz von Oracle Features
5
Anhang
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 2
Wer sind wir? Was machen wir?
Wer sind wir?
Deutsche Bank Research
Think Tank der Deutschen Bank (DB) für Trends in Wirtschaft, Gesellschaft
und Finanzmärkten. Leiter ist Prof. Dr. Norbert Walter.
Ca. 90 Mitarbeiter, davon ca. 50% Analysten (meist Volkswirtschaftler) und ca.
50 % Service-Abteilungen. (Research IT, Marketing, Übersetzung, …).
Öffentliche Research-Ergebnisse / Publikationen stehen im Internet unter
www.dbresearch.de zur Verfügung.
Vortragender (Guido Haase)
Mitarbeiter von DB Research, Research IT. Team-Leiter ist Franz Stevens.
Arbeitsschwerpunkt: Datenbanknahe Software-Entwicklung (Java, Oracle)
Oracle-Erfahrung seit 1994, Dipl.-Ing. (BA), Dipl.-Math.
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 3
Wer sind wir? Was machen wir?
Was machen wir als Research IT?
ReWARE (Research Warehouse)
= Solution / Plattform für (makroökonomische) Research-Abteilungen (DB Research)
Oracle Datenbank (Rebase) als zentrales Repository
– Ziel: Keine Insellösungen mit Access / Excel
Vielzahl technischer Komponenten
–
–
–
–
Anwendungen (Namen beginnen nach Konvention mit RE…)
Office-Add-Ins (Word, Excel) zur Automatisierung und Zugriff auf Rebase
Hintergrundprozesse (Monitoring, Mailing, Reporting, Loader, …)
Web-Infrastruktur / Web-Angebot
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 4
Wer sind wir? Was machen wir?
Was machen wir als Research IT?
Publikationserstellung und Verteilung
Kunden und Abos
Dokumente
ReCAS
MS-Office
ReREGION
HTML-Editor
ReWAS
WebContent
ReBUILD
ReADMIN
Stammdaten
DB LDAP
ReWEB
Web-Angebot
Notes
ReMAIL
Mail Delivery
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 5
Rebase
Content / Stammdaten
Länder/Regionen
Wer sind wir? Was machen wir?
Was machen wir als Research IT?
Zeitreihen-Management (Data Warehouse Charakter)
Excel-Add-In
Zeitreihen-Lieferanten
Eurostat
ReCHART
Publikationscharts
Bloomberg
ReTIS
Suche, Workspace
ETL
Global Insight
ReCORA
Country Rating
Rebase
…
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 6
ReSEM
Sector Model
3. Party
eViews, Stata, …
Wer sind wir? Was machen wir?
Basis-Technologien / Tools
Oracle (10.2)
– Unternehmenslizenz für Oracle Server EE (inkl. Partioning)
– Tools: TOAD, PowerDesigner (Schemapflege)
Java (1.5)
– Fat Clients: WebStart, Swing, JDBC (Thin)
– Web: Apache/Tomcat, Servlets, JDBC
– Tools: JBuilder, NetBeans, JClassChart, Open Source (FOP, POI, JCalendar, …)
Microsoft (Office-Add-Ins)
– VBA (DB-Zugriff: OLE DB)
– .NET (DB-Zugriff: ODP - Oracle Data Provider for .NET)
Tools: Visual Studio, ODT (Oracle Developer Tools for VS.NET)
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 7
Wer sind wir? Was machen wir?
Rahmenbedingungen
Heterogene Benutzergruppen und Frontends
–
–
–
–
Analysten wollen Office (Excel) / Analyse-Software (Statistikprogramme)
Publikationsassistenten wollen Office (Word) / einfache Tools (Windows L&F)
Marketing, Webmaster wollen Automatisierung, Multi-Media
(End-) Kunden wollen Print-Publikationen (PDF) / Web / Blackberry / Mail
Dynamische und flexible Anforderungen des Fachbereichs (DB Research)
– Evolutionäre Entwicklung (kurze Entwicklungszyklen, häufiges Deployment)
Strenge DB-Vorgaben bezüglich Sicherheit
– 4 Eyes Principle, Separation of Duty, Least Privileges, Account-Management, Logging
– Offizielle Vorgaben für Software-Entwicklung folgen Wasserfall-Modell
– Change Management, Regelmäßige Revision
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 8
Wer sind wir? Was machen wir?
Domänenlogik
PL/SQL als Domain-Layer (nach Fowler: Transaction Script)
– Komplexität der Domänenlogik (Geschäftslogik) meist gering
Ausnahme: Vorhersage-Modelle im Zeitreihenbereich, Rechtesystem
– Viele CRUD-Anwendungen, häufiges Hinzufügen von Attributen
Empfehlung: http://martinfowler.com/articles/enterprisePatterns.html
Martin Fowler:
Patterns für Enterprise
Application-Architecture
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 9
Agenda
1
Wer sind wir? Was machen wir?
2
Oracle Infrastruktur und Konventionen
3
Migration Oracle 9.2 (Linux, Latin) nach 10.2 (AIX, UTF-8)
4
Einsatz von Oracle Features
5
Anhang
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 10
Oracle-Infrastruktur und Konventionen
Oracle-Versionen und Instanzen
Oracle-Versionen und Plattformen (historisch)
Bis 2001:
Oracle 8.1.5 (Windows NT 4), Oracle 8.1.6 (HPUX)
2001-2004:
Oracle 8.1.6 (Sun Solaris),
2004-2007:
Oracle 9.2 (Linux)
Oracle 8.1.6 (HPUX)
Seit Mai 2007: Oracle 10.2 (AIX)
Administration von interner Infrastrukturgruppe (GTO/IES)
Oracle Instanzen (2007)
Rebase Produktion und Standby (BCP-Lokation)
Internet Produktion (Teilkopie von Rebase) und Standby
UAT (User Acceptance Test)
Development, Daily (tägliche Kopie)
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 11
Oracle-Infrastruktur und Konventionen
Produktions-Instanz
Produktions-Datenbank (Rebase)
Oracle-Version:
Enterprise Edition 10.2.0.3, 64 Bit with Partioning
Rechner :
IBM p550, AIX OS 5.3.4
Prozessor:
2mal 4-core boards, 1.6 GHz
Hauptspeicher:
24 GB RAM (20 SGA)
Datenvolumen:
Tablespaces: ca. 220 GB (400 GB SAN)
Schema-Objekte
Ca. 500 Tabellen (überwiegend "Entity"-Tabellen)
Ca. 300 PL/SQL-Packages, 170.000 Line of Code
Ca. 8 Mio. Zeitreihen, 700 Mio. Zeitreihenwerte (partitionierte IOT, ca. 50 GB)
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 12
Oracle-Infrastruktur und Konventionen
Schema-Konventionen
Trennung von Applikations-Schema und Connect-User
– Ein „ReWARE-Owner-Schema“ (locked) mit Tabellen, PL/SQL (Definer Right)
– Alle Tabellen, Views, Packages besitzen ein Public Synonym.
– Applikationen greifen stets über Public Synonym und technischen User zu.
Alle Schema-Objekte und Kommentare in Englisch
– Englische Begriffe sind meist kürzer als deutsche. Fachsprache ist meist Englisch.
IDs grundsätzlich vom Typ VARCHAR2(20), <Synonym>0..0<SequenceNo>
– Vorteil: Eindeutige ID innerhalb der DB, Vorteile in Java (nullable, String=Objekt)
– Nachteil: Erhöhter I/O und Platzbedarf und geringere Performance (Joins!)
– Alternative: GUID (Global Unique Identifier), select sys_guid() from dual
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 13
Oracle-Infrastruktur und Konventionen
Schema-Konventionen
DDL
– Alle Schema-Änderungen werden per Skript erst getestet und dann deployed.
– Produktive Deployments über DB-Tool Remedy (4-Augen-Prinzip)
DML
– Alle Schreibzugriffe (DML) über PL/SQL-Prozeduren. IDs als OUT-Parameter.
– Array-In-Schnittstellen über "flachgeklopfte" Object Type Collections.
– Updates, Deletes werden in eine Tabelle (update_logs) per Trigger protokolliert.
Sparsame Verwendung von Views
– Beispielsweise als externe Schnittstellen bzw. bei erwarteten Änderungen.
– Komplexes Lesen über PL/SQL mittels PIPE ROW
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 14
Oracle-Infrastruktur und Konventionen
Schema-Konventionen
Beispiel für DML-Logging
user_groups (usgr)
usgr_id
creator
cr_date
updator
up_date
updator_system
name
status
label_teit_id
description
relm_buse_id
VARCHAR2(20)
VARCHAR2(20)
DATE
VARCHAR2(20)
DATE
VARCHAR2(30)
VARCHAR2(80)
VARCHAR2(4)
VARCHAR2(20)
VARCHAR2(2000)
VARCHAR2(20)
update_logs (uplo)
not null
not null
not null
not null
not null
not null
not null
not null
not null
not null
null
uplo_id
syno_name
obj_id
obj_id_part2
operation
audit_date
updator
updator_system
sys_att_name
old_value
new_value
VARCHAR2(20)
VARCHAR2(30)
VARCHAR2(20)
VARCHAR2(20)
VARCHAR2(1)
DATE
VARCHAR2(20)
VARCHAR2(30)
VARCHAR2(40)
VARCHAR2(4000)
VARCHAR2(4000)
CREATE OR REPLACE TRIGGER recasp.usgr_uplo_tr
AFTER INSERT OR UPDATE OR DELETE
ON recasp.user_groups FOR EACH ROW
DECLARE
v_op VARCHAR2(1) := 'U';
BEGIN
IF inserting THEN
wri_logs.ins_uplo('I', 'USGR', :new.usgr_id, null, null);
RETURN;
END IF;
IF deleting THEN v_op := 'D'; END IF;
wri_logs.ins_uplo(v_op, 'USGR', :old.usgr_id, 'NAME', :old.name,
...
END;
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 15
:new.name);
not null
not null
not null
null
not null
not null
not null
not null
not null
null
null
Oracle-Infrastruktur und Konventionen
Benutzer- und Rechteverwaltung
Eigene Benutzerverwaltung bzw. Tabelle (ca. 120.000 User)
– Aufwändig wegen Security-Vorgaben (password expiry, locking, logging, ...)
Wenige Named User (DBAs/Entwickler, Technische Connect User)
– Teilweise problematisch, da Tools (z.B. Access) Named User brauchen
Jede Applikation nutzt einen Connect-User mit minimalen SQL-Rechten
– Einsatz von Secure Application Role (set_role nach erfolgreicher Anmeldung)
Eigene Rechteverwaltung (siehe auch Anhang)
– Systemrechte (entsprechen i. W. Oracle Rollen), z.B. rewas_admin, webmaster
– Objektrechte, z. B. Lesezugriff auf Zeitreihen, Editor einer Publikationsreihe
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 16
Agenda
1
Wer sind wir? Was machen wir?
2
Oracle Infrastruktur und Konventionen
3
Migration Oracle 9.2 (Linux, Latin) nach 10.2 (AIX, UTF-8)
4
Einsatz von Oracle Features
5
Anhang
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 17
Migration Oracle 9.2 nach 10.2
Grundlagen Unicode Zeichensatz
Dokumentation
– http://www.oracle.com/technology/tech/globalization/index.html, http://www.unicode.org
UNICODE-Encodings (Implementierungen der Codepoints)
– UCS-2 (Universal Character Set): 2 Byte fix, Unicode Standard 3.0, kompakt für asiatische
Schriftzeichen, Standard für Java
– UTF-8 (UCS Transformation Format 8-Bit): 1-4 Bytes (variable), 7-bit ASCII Obermenge,
Europäische Zeichen: 1-2 Byte, asiatische Zeichen 3 Byte, Internet-Standard
– UTF-16: Erweiterung von UCS-2 mit 2 oder 4 Bytes, Windows-Standard (ab Windows 2000)
Oracle-Unicode-Zeichensätze
–
–
–
–
Namensstandard: <Language><Bit Size><Encoding> (AL=All Languages)
AL32UTF8: UTF-8-Codierung, Neuester Unicode-Standard, Unicode Database Character Set
AL16UTF16: UCS-2-Codierung, Neuester Unicode-Standard, Default National Character Set
UTF8: Heute Deprecated, Nur Unicode 3.0, seit Oracle 8
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 18
Migration Oracle 9.2 nach 10.2
Zeichensatz-Migration
Ziel: Zeichensatz-Umstellung
–
–
Von: WE8ISO8859P15 (Western Europe ISO 8859 Page 15): (=Latin-1+Euro)
Neu: AL32UTF8 (National Character Set bleibt bei AL16UTF16)
Konsequenzen
–
–
–
Erhöhter Speicherbedarf (10-30%) für SQL CHAR-Typen
Erhöhter Prozessorbedarf bei Stringverarbeitung in PL/SQL (laut DOKU)
Umstellung von nls_length_semantic von BYTE auf CHAR
– Note: 144808.1 "Examples and limits of BYTE and CHAR semantics usage"
– Instanz-Parameter, Parameter gilt nur für neu erstellte Objekte!
– Umstellung über Export/Import als Teil der Migration (csalter-Skript nicht anwendbar)
Nützliche SQL-Funktionen
–
–
UNISTR('\<UCS2 code>'): Erzeugung von Unicode select UNISTR('\20AC') FROM dual
Beispiel. EuroZeichen: Latin-15: A4, Java/C#: \u20AC, HTML: € oder €
DUMP(<column>,1016): Hexadezimale Anzeige von Spalteninhalten
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 19
Migration Oracle 9.2 nach 10.2
Probleme der Zeichensatzumstellung
Datenbankseitige Probleme
Physikalische 4000-Byte-Grenze für VARCHAR!
– Anzahl der speicherbaren Zeichen reduziert sich in Abhängigkeit von Daten!
– Teilweise Umstellung auf CLOB vor der Migration
– Oracle Tool csscan liefert gute Dienste zur Analyse vor der Migration.
nls_length_semantic gilt nicht für die Deklaration von PL/SQL-Variablen ?!?
– Verwendung von %TYPE, %ROWTYPE funktioniert.
– variable VARCHAR(100) entspricht variable VARCHAR(100 Byte) !
„alter index ... rebuild online“ deutlich beschränkter (ORA-1450).
– Ging vorher für VARCHAR2(2000), jetzt nur noch VARCHAR2(946 BYTE)
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 20
Migration Oracle 9.2 nach 10.2
Probleme der Zeichensatzumstellung
Applikationsseitige Probleme
Änderungen im Source-Code notwendig:
– Streaming (Servlets, Files, …) innerhalb der Anwendungen auf UTF-8 umstellen!
(Default meist betriebssystemabhängig, Windows: MS Windows 1252 CodePage)
– z.B. POI erfordert explizite Angabe des Encodings setEncoding(HSSFCell.ENCODING_UTF_16)
– z.B. DatabaseMetaData.getColumns(), rs.getInt("column_size") liefert Byteanzahl!
Windows SQL-IDEs sind häufig nicht Unicode-fähig!
– sqlplus(w) / TOAD können kein Unicode darstellen
– Oracle SQL Developer in "Tools->Preferences->Encoding" auf UTF-8 umstellen
– Office, ODT (Oracle Developer Tool) können Unicode.
Hausgemachte Probleme:
– Verwendung eines zeichensatzabhängigen Verschlüsselungsalgorithmus (RC4)
– Änderung des Monatnamens bei TO_CHAR mittels 'MON' von 'Mär' auf 'MRZ'
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 21
Migration Oracle 9.2 nach 10.2
Datenbank-Migration
Vorbereitung (Testen!)
– Analyse und Bereinigung möglicher Längenprobleme in PL/SQL und beim Import (csscan)
– Umstellung der Java-Anwendungen auf 10.2 JDBC-Treiber (bei uns ORA-600 mit 9.2-Treiber)
Migration
– Erzeugen der Oracle 10.2-Instanz mit nls_length_semantic=BYTE ! (wichtig)
– Umstellen auf nls_length_semantic=CHAR, Tabellen anlegen, Import der Schemata mit Daten
– Änderung einiger Oracle Text-Indizes, Aufbau der Oracle Text-Indizes
(User CTXSYS kein DBA mehr, Datastores nicht mehr in CTXSYS, alle Preferences mit Schema)
Nachbearbeitung
– Umstellung von Perfstat auf AWR (Automatic Workload Repository)
– Optimizer-Probleme und sonstige Probleme gelöst.
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 22
Migration Oracle 9.2 nach 10.2
Probleme durch 10.2 / Neue Plattform
GROUP BY impliziert kein ORDER BY mehr
Oracle JVM deutlich langsamer als früher bzw. als Betriebssystem JVM
Teilweise ORA-600 bei Oracle Text / SCORE
– Workaround: alter session set "_projection_pushdown"=false;
Probleme mit CBO / offensiven SQL-Statements
– z.B. zu frühe / häufige Ausführung von teuren PL/SQL-Funktionen in SQL.
Workaround:
SELECT ... FROM ... WHERE ... AND proc_result(...)=1 umformuliert zu
SELECT * FROM (SELECT ..., proc_result(...) result FROM ... WHERE ...)
WHERE result=1
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 23
Migration Oracle 9.2 nach 10.2
Fazit
Wechsel der Oracle-Version von 9.2 auf 10.2 relativ problemlos
– Ein Muss wegen 9.2 Desupport
– Gewonnene Features / Gesamtperformance rechtfertigen den Aufwand
– Wie erwartet einige Probleme mit dem Optimizer.
Wechsel des Zeichensatzes.
– Datenbank-Migration (bei uns) relativ einfach z.B. VARCHAR2(2000)->VARCHAR2(4000)
– Überraschend problematisch und aufwändig bezüglich bestehender Anwendungen.
– Der Gewinn ist erst mittelfristig / langfristig. Eine Investition in die Zukunft !
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 24
Agenda
1
Wer sind wir? Was machen wir?
2
Oracle Infrastruktur und Konventionen
3
Migration Oracle 9.2 (Linux, Latin) nach 10.2 (AIX, UTF-8)
4
Einsatz von Oracle Features
5
Anhang
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 25
Einsatz von Oracle Features
4 Oracle Editionen und Features
http://www.oracle.com/database/product_editions.html
SELECT * FROM V$OPTION ORDER BY PARAMTER;
SELECT * FROM dba_registry ORDER BY comp_name;
Feature
CPU / RAM / DB Size
Express
Standard
Enterprise
1 CPU / 1 GB / 4GB
Edition 1: 2 Sockets
No Limit
(Windows, Linux)
Edition: 4 Sockets / RAC
Java Support
Ja
Ja
Virtual Private Database
Ja
Secure Application Role
Ja
Oracle Text
Patitioning
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 26
Ja
Ja
Ja
Option
Einsatz von Oracle Features
4.1 Java Stored Procedures
Dokumentation (10g)
– Java Developer's Guide (218 Seiten), JDBC Developer's Guide (484 Seiten)
Grundlagen Oracle JVM
–
–
–
–
Oracle 10g JVM ist J2SE 1.4.2 kompatibel (Oracle 9i: 1.3, Oracle 11g: 1.5)
PL/SQL-Package dbms_java dient zur Administration.
Jede Oracle Session verhält sich wie eine eigene JVM.
Core Java sind „native compiled“ / Ahead-of-Time compilation.
– Eigene Klassen werden interpretiert und
können mittels ncomp compiliert werden.
– Oracle 11g unterstützt (wieder) Just-In-Time Compiler (JIT).
– Oracle JVM interagiert mit Oracle Libraries und nicht mit OS.
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 27
Einsatz von Oracle Features
4.1 Java Stored Procedures
Entwicklungsprozess
– Entwicklung in gewohnter IDE (z.B. JDeveloper)
– Abweichungen zur normalen JVM:
– Connection ist implizit gegeben und verwendet JDBC server-side internal driver.
DriverManager.getConnection("jdbc:default:connection:")
–
–
–
–
GUIs sind nicht sinnvoll / supported.
Threading erfolgt über Oracle-Mechanismen. Eigene Threads sollten vermieden werden.
Einstiegspunkt sind statische Methoden (nicht die main-Methode).
Java System.out kann auf serveroutput umgeleitet werden.
set serveroutput on; exec dbms_java.set_output(999999);
– Aufruf der Java-Klassen (statische Methode) mittels PL/SQL-Wrapper
CREATE OR REPLACE PROCEDURE test (para1 VARCHAR2)
AS LANGUAGE JAVA
NAME 'testpackage.Testklasse.test(java.lang.String)';
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 28
Einsatz von Oracle Features
4.1 Java Stored Procedures
Deployment
– Laden des Source-Codes oder der kompilierten Klassen mittels loadjava-Utility
– Klassen/Properties werden als Schema-Objekte geladen (gilt auch für jar).
Klassennamen stehen mit Kurznamen / Alias in user_objects.
select
dbms_java.longname(object_name)
from user_objects
where object_type='JAVA CLASS'
– Tabelle JAVA$OPTIONS speichert
Einstellungen der Kompilierung.
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 29
Einsatz von Oracle Features
4.1 Java Stored Procedures
Beispiel: Zugriff auf Deutsche Bank LDAP-Verzeichnis (Group Directory)
Verwendung von JNDI (Java Naming and Directory Interface)
– Tutorial: http://java.sun.com/products/jndi/tutorial/getStarted/TOC.html
– Verarbeitung ähnlich zu JDBC, Abfragesprache aber LDAP Filter
– Java schreibt Ergebnis in "GLOBAL TEMPORARY TABLE" db_dir_temp, damit alle
Clients das Ergebnis lesen können.
– Performance von LDAP teilweise deutlich langsamer als SQL (TimeLimit setzen)
PL/SQL-Package (db_dir)
– Umsetzung von Parametern in LDAP-Filter (UPN-Notation)
search(p_mail_address => '%haase%', p_db_location_city => 'frankfurt')
LDAP-Filter: (&('mail=*haase*')('dblocationcity=frankfurt'))
Alternative: Implementierung über PL/SQL-Package dbms_ldap (seit 9.2)
– siehe auch DOAG News 3/2007-Artikel "Datenqualität für Verzeichnisdienste"
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 30
Einsatz von Oracle Features
4.1 Java Stored Procedures
Beispiel: PL/SQL-Mailing
Verwendung von JavaMail zur Versendung von Mails mit Anhang
– Dokumentation: http://java.sun.com/products/javamail
– Laden von mail.jar, activation.jar notwendig
Prozedur send_mails als Wrapper für Java Stored Procedure.
– Body kann vom Typ CLOB sein. Anhänge vom Typ BLOB oder Dateien in UTL_FILE_DIR.
PL/SQL-Package mail_send erlaubt (asynchrone) Versendung von Mails.
– Mails werden in eine Mail-Tabelle (Queue) gespeichert und asynchron versendet.
– Versendung kann über 2 SMTP-Server erfolgen und wird im Fehlerfall wiederholt.
Alternative: Implementierung über utl_mail
– SMTP_OUT_SERVER ist Paramter der init.ora?
– Attachment ist RAW (32K-Genze) und kein BLOB?
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 31
Migration Oracle 9.2 nach 10.2
4.1 Java Stored Procedures (Fazit)
Architektonisch fragwürdig
–
–
–
–
Kein Ersatz für Applikationsserver !
Skalierung auf der Datenbank? Lizenzkosten?
Mögliche Performance-Probleme in Java reist Datenbank mit runter. Monitoring?
Deployment nicht unkritisch
Moderater Einsatz zur Erweiterung fehlender PL/SQL-Features ist ok.
– Falls man überhaupt PL/SQL verwendet
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 32
Einsatz von Oracle Features
4.2 Virtual Private Database (VPD)
Dokumentation
– Oracle Database Security Guide 10.2, Kapitel 14 & 15 (ca. 50 Seiten)
– siehe auch DOAG News 3/2007-Artikel "Fine Grained Access Control"
Fine-grained Access Control (Row Level Security)
– Grundidee: Server modifiziert SQL durch Anhängen von Prädikaten
Prädikat wird (dynamisch) durch eine Policy Function geliefert.
FUNCTION policy_function(v_schema VARCHAR2, v_object VARCHAR2)
RETURN VARCHAR2
–
–
–
–
Policies können für Tabellen und Views vergeben werden.
Policies wandeln eine Tabelle faktisch in eine View (transiente View).
Policies können dynamisch (Default), statisch oder Context-Sensitive sein.
RETURN-Wert=NULL: keine Einschränkung. "1=0": kein Ergebnis.
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 33
Einsatz von Oracle Features
4.2 Virtual Private Database (VPD)
Administration
–
–
–
–
–
–
PL/SQL-Package dbms_rls dient zur Administration.
Mehrere Policies pro Objekt werden mit AND verknüpft.
Policies können gruppiert und gruppenweise aktiviert/deaktiviert werden.
Für select, insert, update, delete sind unterschiedliche Policy-Functions möglich.
Für den User SYS gelten keine Security Policies (z.B. Export-Problem).
View dba_policies zeigt Policies. v$vpd_policy zeigt Prädikate für aktuelle Cursor.
Virtual Private Database (VPD)
– Grundidee: Kombination aus Fine-grained Access Control und Appl. Context
– Oracle 10.2 erlaubt auch Column-Level VPD
(deshalb RLS=Row Level Security ein unglücklicher Name).
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 34
Einsatz von Oracle Features
4.2 Virtual Private Database (VPD)
Application Context
–
–
–
–
Grundidee: Speicherung von globalen / session-abhängigen Attributen.
Jeder Context kann eine Menge von Attributen und Werten speichern.
Es gibt verschiedene Contexttypen: z.B. global (SGA), session-based (UGA).
Oracle liefert den Context USERENV für lesenden Zugriff auf Umgebung.
Beispiel: SELECT SYS_CONTEXT('userenv','host') FROM dual
Secure session-based Application Context ist für VPD relevant.
– Grundidee: Setzen der Attributwerte erfolgt in einem Trusted PL/SQL-Package.
Beispiel:
CREATE CONTEXT testcontext USING test_context_package;
In Trusted Package: dbms_session.set_context('testcontext', 'dep_id', '10')
In Policy Function: RETURN 'dep_id=sys_context(''testcontext'',''dep_id'')'
– Aufruf der Trusted PL/SQL-Package vor erstem Datenzugriff (z.B. Logon-Trigger)
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 35
Einsatz von Oracle Features
4.2 Virtual Private Database (VPD)
Warum machen wir VPD?
– Ursprünglich wurde ReWARE als Plattform für eine Abteilung entwickelt
– Neue Anforderung: Weitere Mandanten mit teilweiser Datenteilung bzw. Trennung
– VPD ist/war einfachste Lösung, da (fast) keine Änderungen am Code notwendig sind.
Mögliche Alternativen (auf der Datenbank)
– Kontrolle der Sichtbarkeit über Views oder Lesen über PL/SQL
– Eigenes Schema pro Mandant (GRANTs auf geteilte Daten)
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 36
Einsatz von Oracle Features
4.2 Virtual Private Database (VPD)
Vorgehen
– Neue Tabelle Realms mit Daten zu Mandanten (Client-Begriff war bereits vergeben)
– Benutzer werden Mandanten zugeordnet (z.B. durch Abteilungszugehörigkeit).
– Context (REWARE), eine Trusted-Package und Policy-Package(s) angelegt.
– Änderungen an mandantenabhängigen Tabellen:
– Erweiterung um Realm-Id mit Foreign Key auf Tabelle Realm
– Verknüpfen mit Security Policy (DBMS_RLS.ADD_POLICY)
– Before-Insert Trigger setzt Realm-Id aus Context.
Der Realm des Erstellers einer Zeile wird dadurch zum Besitzer der Zeile.
– Applikationen setzen Context-Attribute nach erfolgreichem Login beim Start.
(Kein Context -> keine Daten über Prädikat '1=0' sichtbar)
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 37
Einsatz von Oracle Features
4.2 Virtual Private Database (VPD)
CREATE TRIGGER recasp.tosc_before_ins_upd_tr
BEFORE INSERT recasp.topic_schemas
FOR EACH ROW
DECLARE
BEGIN
IF :new.relm_buse_id IS NULL THEN
:new.relm_buse_id :=
sys_context('REWARE', 'REWARE_USERID');
END IF;
END;
topic_schemas (tosc)
tosc_id
relm_buse_id
creator
cr_date
updator
up_date
updator_system
topi_schema
type
label_teit_id
extendable
thes_id
VARCHAR2(20)
VARCHAR2(20)
VARCHAR2(20)
DAT E
VARCHAR2(20)
DAT E
VARCHAR2(30)
NUMBER(2)
VARCHAR2(1)
VARCHAR2(20)
VARCHAR2(4)
VARCHAR2(4)
not null
not null
not null
not null
not null
not null
not null
not null
not null
not null
null
null
tosc_relm_fk
-- einmalig Secure session-based Application Context:
CREATE CONTEXT reware USING recasp.reware_security_context;
-- Trusted Package
CREATE OR REPLACE PACKAGE
recasp.reware_security_context
AS
PROCEDURE set_user_context(p_userid VARCHAR2);
PROCEDURE clear_context;
END;
DBMS_RLS.ADD_POLICY(
object_schema
=>
,object_name
=>
,policy_name
=>
,function_schema =>
,policy_function =>
,statement_types =>
,update_check
=>
'RECASP'
'TOPIC_SCHEMAS'
'TOSC_READ_POLICY',
'RECASP',
'RL_SECURITY.GENER_READ_SEC'
'SELECT'
FALSE);
DBMS_RLS.ADD_POLICY(
object_schema
=>
,object_name
=>
,policy_name
=>
,function_schema =>
,policy_function =>
,statement_types =>
,update_check
=>
'RECASP'
'TOPIC_SCHEMAS'
'TOSC_WRITE_POLICY'
'RECASP'
'RL_SECURITY.GENER_WRITE_SEC'
'INSERT,UPDATE,DELETE'
TRUE);
realms (relm)
buse_id
creator
cr_date
updator
up_date
short_name
VARCHAR2(20)
VARCHAR2(20)
DATE
VARCHAR2(20)
DATE
VARCHAR2(20)
not
not
not
not
not
not
null
null
null
null
null
null
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 38
-- Policy Functions
CREATE OR REPLACE PACKAGE recasp.rl_security AS
FUNCTION gener_read_sec (d1 VARCHAR2, d2 VARCHAR2)
RETURN VARCHAR2;
FUNCTION gener_write_sec(d1 VARCHAR2, d2 VARCHAR2)
RETURN VARCHAR2;
END;
Einsatz von Oracle Features
4.2 Virtual Private Database (VPD)
Folgerungen / Anmerkungen
– Ausgewählte Named User (neben SYS) dürfen ohne Context global lesen.
– Explizites Anmelden an andere Mandanten notwendig (Syntax: User@<Realm>)
(Zulässige Mandantenwechsel werden über das eigene Rechtesystem gesteuert)
– Rechtesystem kennt mittlerweile globale und mandanten-abhängige Rechte.
Probleme
– Perfomance-Tuning schwierig (SQL-Trace zeigt z.B. nur Original-SQL).
– Policy-Function sollte aus Performance-Gründen kein SQL enthalten.
– Deployment der Policy-Function bei permanenter Tabellennutzung schwierig (Lock)
Fazit
– Ein gutes Feature (falls man es braucht)
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 39
Einsatz von Oracle Features
4.3 Oracle Text
Dokumentation
– Text Application Developer‘s Guide (ca. 200 Seiten), Text Reference (ca. 500 Seiten)
– siehe auch DOAG News 3/2007-Artikel "Thesauri und Text-Retrieval mit Oracle 10g"
Einsatzgebiete von Oracle Text / Indextypen
– Volltextsuche in Dokumenten
– Indextyp: CONTEXT, Query-Operator: CONTAINS
– Synchronisierung: asynchron über ctx_ddl.sync_index
– Katalog-Anwendungen
– Indextyp CTXCAT, Query-Operator: CATSEARCH
– Synchronisation: transaktional
– Performant für „Mixed Queries“ bei kleinen Textfragmente bzw. Dokumenten
– Dokument-Klassifikation
– Indextyp CTXRULE, Query-Operator: MATCHES
– XML-Suche
– Indextyp CTXPATH, Query-Operator: existNode(), Index für Spalte XMLTYPE
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 40
Einsatz von Oracle Features
4.3 Oracle Text
Grundidee
– Ein Textindex speichert pro Wort (Token) die Dokumente, die das Wort enthalten.
Sonderzeichen (z.B. Satzzeichen) werden nicht indidiziert.
– Überblick über den Oracle Text Indexing Process
Stemming, Fuzzy Search
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 41
Einsatz von Oracle Features
4.3 Oracle Text
Aufbau eines Context-Index
– Erstellen des Indexes mittels
CREATE INDEX <IndexName> ON <TableName> (<Column>)
INDEXTYPE IS ctxsys.context PARAMETERS ('<Preferences>')
– Optionales Anpassen der Index-Präferenzen über ctx_ddl.create_preference
– Monitoring über ctxsys-Views ctx_index_errors, ctx_pending, ctx_indexes, …
Einige Abfrage-Features eines Context-Index
–
–
–
–
–
Logische Operatoren, z.B. where contains(text_col, 'dog and cat')>0
Stemming, z.B. where contains (text_col, '$dog')>0
Proximity Searching, z.B. where contains(text_col, 'near((dog,cat),1)')>0
Wildcard Searching, z.B. where contains(text_col, 'cat%')>0
Scoring, z.B.
select SCORE(1) from xyz where contains(text_col, 'cat%', 1)>0
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 42
Einsatz von Oracle Features
4.3 Oracle Text
Beispiel 1: Zeitreihensuche
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 43
Einsatz von Oracle Features
4.3 Oracle Text
Volltextindex für Zeitreihen
Zeitreihen-Tabelle time_series (tise)
tise_id:
VARCHAR2(20)
tise_freq: VARCHAR2(2)
tise_desc: VARCHAR2(2000)
…
CREATE INDEX recasp.tise_desc_i ON
recasp.time_series (tise_desc)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('stoplist ctxsys.empty_stoplist
lexer
recasp.tise_lexer
wordlist recasp.tise_wordlist
storage recasp.recasp_storage_l
memory
50M');
SQL: Alle Tageszeitreihen (tise_freq='D') mit Kalkstein/Chalk (inkl. Stemming) im Beschreibungsfeld
SELECT tise_id FROM tise
WHERE contains(tise_desc, '$kalkstein or $chalk')>0
AND tise_freq = 'D'
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 44
Einsatz von Oracle Features
4.3 Oracle Text
Beispiel 2: Volltextsuche in Dokumenten
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 45
Einsatz von Oracle Features
4.3 Oracle Text
Volltextindex für Dokumente
CREATE INDEX BLOR_IM ON blob_records(blobelem)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('stoplist recasp.multilang_stoplist
filter
ctxsys.auto_filter …);
Dokument-Tabelle (blor)
blor_id:
VARCHAR2(20)
blobelem: BLOB
ctx_doc.filter(…)
Dokument-Text-Tabelle (blot)
Tabelle(n) mit Metadaten zum Dokument
query_id: NUMBER
title: VARCHAR2(200)
document: CLOB
author: VARCHAR2(200)…
Datastore-Prozedur:
recasp.dose_text_datastore(…)
Dokument-Such-Tabelle (dose)
blor_id:
VARCHAR2(20)
dose_text: VARCHAR2(2)
publ_date
DATE
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 46
XML
CREATE INDEX DOSE_IM ON docu_search(dose_text)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('section
recasp.dose_text_sectioner
datastore recasp.dose_text_datastore …);
Einsatz von Oracle Features
4.3 Oracle Text (Fazit)
Ein Feature, was man häufiger einsetzen sollte!
– Volltextsuche ist häufig eine gute Ergänzung zur strukturierten Suche.
– Anwender sind Google gewohnt (einfache Suche für "Simple Search").
– Basistechnologie für Oracle Secure Enterprise Search.
Handhabung
–
–
–
–
Die initiale Anwendung ist sehr einfach.
Optimale Nutzung (z.B. Konfiguration) kostet etwas Zeit / Erfahrung.
Oracle Text kostet aufgrund der Architektur Ressourcen.
Vorsicht bei Multimedia-Dateien (Bilder, Audio, Video) mit den Filtern.
Teilweise Performance-Probleme bei großen Datenmengen
– Ausführungsplan des Optimizer teilweise unverständlich schlecht
– Initiale Suche bei nicht geladenem Cache und großen Daten kann sehr lang sein.
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 47
Agenda
1
Wer sind wir? Was machen wir?
2
Oracle Infrastruktur und Konventionen
3
Migration Oracle 9.2 (Linux, Latin) nach 10.2 (AIX, UTF-8)
4
Einsatz von Oracle Features
5
Anhang
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 48
Anhang
dbresearch Homepage
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 49
Anhang
Google Web-Suchsyntax
Such-Grundlagen
–
–
–
–
–
–
Automatische "und"-Suche. Case-Insesitive-Suche.
Wortgruppensuche (Phrasen) über Anführungszeichen "wort1 wort2"
Stoppwörter sind über +Stoppwort oder Wortgruppensuche suchbar.
Ausschließende Begriffe über -Wort.
Ergebnisses nach Relevanz sortiert.
Wortstammsuche/Wortvarianten (implizit) nur für Englisch!
Erweiterte Suche / Features
–
–
–
–
–
Eingabe-Korrekturvorschlag (Meinten Sie: …)
Anzeige der Links auf eine Seite. Anzeige indizierter Seiten aus dem Cache.
Einschränkung auf Dateityp, Sprache, Position (Titel, URL, Site), Index-Zeitraum.
Odersuche über "OR" (Logische Ausdrücke/Klammerung scheint nicht zu gehen)
Zugverbindungen z.B. Frankfurt München 13:30
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 50
Disclaimer
© Copyright 2007. Deutsche Bank AG, DB Research, D-60262 Frankfurt am Main, Deutschland. Alle Rechte vorbehalten.
Bei Zitaten wird um Quellenangabe „Deutsche Bank Research“ gebeten.
Alle Meinungsaussagen geben die aktuelle Einschätzung des Verfassers wieder, die nicht notwendigerweise der Meinung
der Deutsche Bank AG oder ihrer assoziierten Unternehmen entspricht. Alle Meinungen können ohne vorherige
Ankündigung geändert werden. Die Meinungen können von Einschätzungen abweichen, die in anderen von der Deutsche
Bank veröffentlichten Dokumenten, einschließlich Research-Veröffentlichungen, vertreten werden. Die vorstehenden
Angaben werden nur zu Informationszwecken und ohne vertragliche oder sonstige Verpflichtung zur Verfügung gestellt.
Für die Richtigkeit, Vollständigkeit oder Angemessenheit der vorstehenden Angaben oder Einschätzungen wird keine
Gewähr übernommen.
G. Haase · DOAG Regionaltreffen 23.10.2007 · Seite 51