Weblog for the AMIS Technology corner
DBA
Using the Oracle XMLDB Repository to Automatically Shred Windows Office Documents (Part 1)
Dec 20th
People who have attended the UKOUG presentation this year where Mark Drake, Sr. Product Manager XML Technologies / XMLDB, Oracle HQ, and I demonstrated the first principles of the XDB Repository, might have been impressed with its (GEO/KML Spatial, Image EXIF info) capabilities combined with Google Earth. This post will zoom in on how to consume automatically content of Windows Office document (docx).
UKOUG 2011 – Drag, Drop and other Stuff. Using your Database as a File Server
View more presentations from Marco Gralike
Most (APEX) people know the PL/SQL Gateway functionality of the XDB Protocol Listener, but this is only one very small part of the XDB Repository functionality. To be precise only one “servlet” part of it. Those “servlets” can be based on Java, C or PL/SQL. The PL/SQL Gateway, as it’s name suggests, is based on the PL/SQL part. Another “servlet”, the Native Database Web Service (NDWS), which enables you to create a database SOA endpoint service and more, is based on C code. Beside demonstrating the WebDAV ACL driven security features and database extensibility/interfacing facilities based on the database (no cost option) XMLDB functionality, it also explain one of the coolest features, IMHO, introduced in Oracle 11gR1 called: XDB Repository Events.
There is one big problem in all of this. It is very, very sparsely documented and although there is some, or was some, code out there on the worldwide web, it took me a while to get a feeling of all the specifics at hand. This post will extend on some of the posts I already created to give to a head start of what is possible. This post will demonstrate what you can do with Windows Office documents, nowadays embedded (zipped) XML content, with extentions called: docx, xlsx or pptx…
UKOUG 2011 – Drag, Drop and other Stuff. Using your Database as a File Server
Dec 12th
Last Thuesday, Mark Drake, Senior Product Manager and I, delivered a good presentation during UKOUG in Birmingham about how to use your database, via XMLDB functionality, as a file server. The presentation demonstrated as well how you could extent the “standaard” file server (aka your database) functionality with features like, WebDAV driven ACL Security and XDB Repository Events, which enable you to automatically trigger event driven actions based on, for example, file naming, content, creator or others.
You can find the presentation on Slideshare or enjoy it here.
UKOUG 2011 – Drag, Drop and other Stuff. Using your Database as a File Server
View more presentations from Marco Gralike
During the demonstration in the second half of the presentation, we demonstrated how you can automatically consume the EXIF and KML information embedded in pictures to use it with the Google Earth API. All the code used for this Google Earth demonstration, and more, can be found on the Oracle XMLDB Sample Code page.
Have fun trying it out and if there any questions, post them on the OTN Oracle XMLDB forum.
UKOUG 2011: Using your Database as a Fileserver
Nov 21st
UKOUG 2011 is nearby and one of the coolest things in Oracle 11g and onwards is, IMHO, a functionality called XDB Repository Events. Most of you probably know that based on XMLDB functionality in the database, the database also can be used in a File server kind of way by enabling the XDB Repository HTTP/FTP or WebDav functionality via DBMS_XDB. XDB Repository Events are a kind of “triggers” that enable you to automatically trigger/do something based on the events triggered in this file/folder environment. For example, it is possible to automatically create duplicate files in the XDB Repository or secure them. Other possibilities are to read the content of such a file and insert that content, on the fly during the copy/paste action, into a relational table.
Most APEX enthousiast know of the PL/SQL Gateway, which is a small part of the functionality that is called the XDB Protocol Listener. Besides PL/SQL support, it also enables you to secure your data, as mentioned, trigger actions based, for example on MIME type, mount your database as a Logical Volume (currently only via WebDAV, eg. DAVFS) of your operating system. The XDB Protocol Listener can support your own solutions based on PL/SQL (like REST WebServices), but also C or Java based methods, and, out-of-the-box, Native Database Web Services (SOAP support) or direct XML content access via other “servlets” like the “ORADB/DBUriServlet” servlet method.
Review: The Method-R Profiler
Nov 10th
I was lucky enough to get my hands on the Method-R Profiler and had a test drive with it. So first of all what is it? As you might expect a profiler, a profiler for Oracle performance problems. The tool makes use of Oracle’s trace facilities. If you have a performance problem with the database or a specific issue in you code, you are able to set a specific part of your code, or if must be, the whole database in trace mode. The database will write the output of your trace to the UDUMP, user dump, destination or in the trace directory of your database diagnostic section on the database server, depending on your database version.
Consistent Analysis
Pete Finnigan has a nice post about how you can achieve this called “How to set trace for others sessions, for your own session and at instance level“. As also is mentioned in this post, the Oracle TKPROF tool can be used to analyze the generated trace files. One of the disadvantages, IMHO, is that if you give such a TKPROF generated report to, lets say 10 Oracle (DBA) performance interested people, you will get afterwards 10 different analyses regarding what is reported in those reports. This is, among others, also caused by the fact that people most of the time sort the report in different ways. If you are not sure what the cause is for your problem, can’t determine it within context, this might even obfuscate the issue even more.
TKPROF is one of the most known tools used for this task. Of course their are also others like the Oracle Trace Analyzer that might do a better job in your case. As in all cases, while using tooling, use the one who suits you best for the task, but then again, you might not want 10 different opinions about “What’s wrong” or “Might be wrong…?”.
This is where the Method-R Profiler might be of use for you. As the naming references, the profiler is based on the Method-R performance analyzing method. As mentioned on the site of the Method-R main site: “Method R is a fast, effective, commonsense approach to optimizing a system. It was first documented in the book Optimizing Oracle Performance by Method-R founding members Cary Millsap and Jeff Holt.” This book has been, is still, one of the landmarks on the subjects of Oracle performance analysis, and a consistent way, based on response time statistics, to get an answer where your time went or, in case of performance problems, where too much time went, where it shouldn’t…
Rman 11gR2 changes tag labeling of the inc backup in incrementally updated backups
Oct 14th
In (rman) incrementally updated backups, only incremental backups are done after the first full backup to the Fast Recovery Area.
From 11gR2 on the incremental backup pieces will get the same tag as the datafile copies, and that’s actually different behaviour from pre- 11gR2 versions of the database.
I stumbled on this different behaviour when a backup script, that worked perfectly on 11gR2, got ported to 10gR2 and 11gR1 ( 11.1.7 ).
The script contains a system cleanup of all incrementals, done before a certain date, specifying the tag of the copy [ 'COPY_DATABASE' ].
On 11gR2 the inc1 backups got deleted fine, but the delete on 10gR2 and 11gR1 just wouldn’t work.
By checking v$backup_piece…
select distinct tag
from v$backup_piece
where instr(tag,'TAG')=0;
…and getting no result, it became clear that the tag of the copy datafiles, was’t also the tag of the incrementals.
Once I knew, it was easy to correct the problem.
I just added the tag of my copy datafiles to the incremental backup, and that did the trick.
All incrementals that were supposed to get cleared from the pre-11gR2 system, were deleted.
The incrementals without tag, already on the system, were deleted manually.
My original 11gR2 incremental backup, and delete:
run{
allocate channel d1 device type disk;
backup
filesperset 8
incremental level 1
for recover of copy with tag 'COPY_DATABASE' database;
release channel d1;
}
run {
allocate channel d2 device type disk;
delete noprompt
backupset
tag 'COPY_DATABASE'
completed before 'trunc(sysdate-7)';
release channel d2;
}
The pre-11gR2 version of the incremental backup.. the delete hasn’t changed:
run{
allocate channel d1 device type disk;
backup
filesperset 8
incremental level 1
tag 'COPY_DATABASE'
for recover of copy with tag 'COPY_DATABASE' database;
release channel d1;
}
How to switch datafiles to FRA and back again
Oct 14th
If you, like me, like to use (rman) incrementally updated backups, a copy of all datafiles will be present in the Fast Recovery Area. That becomes quite handy if you are in sudden need of extra disk space for your database, and the FRA still has ample space left. By switching one or more datafiles to the copy in FRA, you can very quickly use this extra disk space for your database, while not even having to shut down.
I developed a view to generate all the rman commands to execute the switch to FRA, and the switch back to it’s original location and name. Use it at your own discretion.
CREATE OR REPLACE FORCE VIEW rman_switch_datafiles
AS
SELECT tablespace_name
, file_name
, file_id
, '================================================'||chr(10)
||' switch datafile '||file_id||' to copy in FRA '||chr(10)
||'================================================'||chr(10)
||'run'||chr(10)
||'{'||chr(10)
||'sql ''alter tablespace '||tablespace_name||' offline'';' ||chr(10)
||'switch datafile '||file_id||' to copy;' ||chr(10)
||'recover datafile '||file_id||';' ||chr(10)
||'sql ''alter tablespace '||tablespace_name||' online'';'||chr(10)
||'}'||chr(10) rman_switch2copy
, '========================================================='||chr(10)
||' switch datafile '||file_id||' in FRA back to original '||chr(10)
||'========================================================='||chr(10)
||'run'||chr(10)
||'{'||chr(10)
||'backup as copy datafile '||file_id||' format '''||file_name||''';'||chr(10)
||'sql ''alter tablespace '||tablespace_name||' offline'';' ||chr(10)
||'switch datafile '||file_id||' to copy;' ||chr(10)
||'recover datafile '||file_id||';' ||chr(10)
||'sql ''alter tablespace '||tablespace_name||' online'';' ||chr(10)
||'}'||chr(10) rman_switchback2original
FROM dba_data_files
WHERE tablespace_name IN ( select tablespace_name
from dba_tablespaces
where contents = 'PERMANENT'
AND TABLESPACE_NAME != 'SYSTEM' )
ORDER BY 1,2,3
/
One remark… if a datafile is switched to the FRA copy, your controlfile is updated , and the original path and filename is lost. So, before switching the datafile to FRA, it’s prudent to save the ‘back to original” stat.
2 dagen seminar door Steven Feuerstein: Best of Oracle PL/SQL (8 en 9 december)
Oct 13th
Steven Feuerstein
In dit tweedaagse seminar neemt Steven Feuerstein je mee ver voorbij de basismogelijkheden van PL/SQL. Steven zal tijdens dit seminar de best practices behandelen die hij op tientallen plekken in de wereld heeft verzameld en die hij ook mede door zijn nauwe samenwerking met het PL/SQL product team van Oracle kan verifiëren en aanscherpen. Hij laat via code-voorbeelden en interactieve sessies zien hoe je als ontwikkelaar in staat bent om snel, hoogwaardige, goed onderhoudbare en uitbreidbare applicaties kunt maken.
Er is tijdens dit seminar voldoende ruimte om vragen te stellen en ideeën met Steven Feurestein uit te wisselen.
Bij dit seminar zijn de volgende zaken inbegrepen:
- Een exemplaar van het boek Oracle PL/SQL Programming (5th edition) van Steven Feuerstein (twv €71,-).
- Een PL/SQL quiz onder leiding van Steven Feuerstein met leuke prijzen.
- Ook ontvang je een gratis jaarabonnement op het PL/SQL Channel. Hiermee heb je toegang tot 27 uur videotraining (normaal $395).
Dit is de kans om in Nederland in een select gezelschap met één van de experts op PL/SQL gebied kennis te maken en je expertise in Oracle PL/SQL verder te vergroten. Meer informatie of inschrijven kan via deze link.
(NB: Deelname voor deze twee dagen kost 1050 euro)
OOW 2011 – Oracle XMLDB and Big Data
Oct 6th
Last day of Oracle Open World and I am currently attending the last presentations. The first presentation, “Oracle XMLDB: A noSQL Approach to Managing all your Unstructured Data”, deals with the no-SQL approach and using Oracle XML DB in the context of using it with “Big Data”, that is unstructured data. The title of the presentation is “a bit” misleading due it reference to noSQL data handling. XML is mostly used in the area’s of structured, data centric, semi-structured an unstructured, that is document centric data. Due to the flexibility of XML, it can be used for bridging those data content forms. Via the XDB repository, xmltype storage and xmlindex, that content can be moved into the XML DB part of the Oracle database, mapped and categorized. You can use repository events to shred and filter this map while the data is going in regarding interfacing via FTP or WebDAV. In all the presentation addressed a lot of already known fact of the XMLDB functionality and not really how to use it with huge amounts of unstructured data.
ODTUG KScope Preview 2011
Jun 14th
Ook dit jaar, namelijk op dinsdag 14 Juni, organiseert AMIS de ODTUG Preview. Het jaarlijkse congres van de ODTUG, de Oracle Development Tools Users Group, vind dit jaar plaats in Longbeach, California van 26 tot en met 30 juni. Het is niet voor iedereen weggelegd om daar naar toe te gaan. AMIS biedt, alweer voor het vijfde achtereenvolgende jaar, aan geïnteresseerden de kans om een selectie van de presentaties die daar te zien zijn bij te wonen. Een aantal Europese sprekers zal tijdens de AMIS ODTUG preview presentatie laten zien die ook in de Verenigde Staten worden gehouden.
Tijdens de AMIS ODTUG Preview zullen er drie keer drie parallelle sessies worden gehouden met verschillende onderwerpen zoals APEX, database development, ADF, JHeadstart en SOA.
Programma:
| Tijd | Track 1 | Track 2 | Track 3 |
|---|---|---|---|
| 16:30 | Welkom en Registratie | ||
| 17:00 | XFILES, the APEX 4 Version: The Truth is in There…Marco Gralike & Roel Hartman | ADF Developers – Make the Database Work for YouLucas Jellema | Pipelined Table FunctionsPatrick Barel |
| 18:00 | Dinner | ||
| 19:00 | APEX Face/Off – Designing a GUI with APEX Templates and ThemesChristian Rokitta | BPMN: The New Silver Bullet?Lonneke Dikmans | Building Highly Reusable ADF TaskflowsSteven Davelaar |
| 20:15 | Who’s Afraid of Analytic Functions?Alex Nuijten | Overview of Eventing in Oracle SOA Suite 11gRonald van Luttikhuizen | …and Thus Your Forms ‘Automagically’ DisappearedLuc Bors |
Dit evenement is met name bedoeld voor ontwikkelaars.
Uiteraard zijn er aan dit event geen kosten verbonden, maar het aantal plaatsen voor dit evenement is beperkt, wacht niet te lang. Vol is vol.
Inschrijven via www.amis.nl
Details over de presentaties
Hieronder volgen de abstracts van [enkele van] de presentaties:
Building Highly Reusable ADF Taskflows – Steven Davelaar
If well-designed, ADF Task Flows are self-contained reusable UI services, with a clearly defined contract. In this presentation. Steven will explain step-by-step how you can create highly reusable and configurable ADF Task flows.He will explain and demonstrate how to build one taskflow that is used for data entry, for lookup in a popup window, for deeplinking from other taskflows or external sources like e-mail, as a master taskflow as well as a detail taskflow. After this session you will understand how you can dramatically increase the level of reuse in your application, and you will have learned a number of advanced ADF techniques.
Pipelined Table Functions – Patrick Barel
“If you can do it in SQL, use SQL.” But sometimes even the very powerful version of SQL that Oracle provides is not enough and you need more, like loops, conditions, etc. If you can make the output of a function like it’s a table, then you can use it in SQL and have access to all the power PL/SQL provides. After this session, you will know how to create functions that can be used as tables in SQL.
Who’s Afraid of Analytic Functions? – Alex Nuijten
Recognizing when Analytic Functions can be useful in your daily work is a lot harder than learning the syntax. In this presentation, real life examples are discussed to unleash the power of Analytic Functions.
APEX Face/Off – Designing a GUI with APEX Templates and Themes – Christian Rokitta
This presentation is a practical guide to the concept of APEX themes and templates. It outlines skills, tooling, and steps necessary to create a professional looking, custom APEX GUI.
XFILES, the APEX 4 Version: The Truth is in There… – Roel Hartman & Marco Gralike
Version control is not built into APEX. This session will show how to use power of the underlying XML Database to build a version control system for APEX … built in APEX itself!
BPMN: The New Silver Bullet? - Lonneke Dikmans
Given Oracle BPM Suite is now integrated into SOA Suite via SCA, does this mean BPMN is the new silver bullet in modeling and executing processes? Should developers really be switching from BPEL to BPMN? This session will take delegates through BMPN 2.0 in detail, showing how to model and execute BPMN in Oracle BPM Suite. It will also explain if and when processes should be modeled in Oracle BPA Suite. In addition, the session will discuss guidelines on when to use BPMN vs. BPEL. Finally, some pointers are given regarding the granularity of the business process: some of process logic can be defined in ADF task flows, others are better left in the process flow. The session illustrates the different concepts with demos from the Oracle BPA Suite and Oracle BPM Suite.
Overview of Eventing in Oracle SOA Suite 11g – Ronald van Luttikhuizen
Services and events are highly complementary instead of competing paradigms in the ICT landscape. Oracle SOA Suite 11g emphasizes the importance of events by introducing the Event Delivery Network (or EDN) into the SCA infrastructure. This session provides an introduction of eventing in SOA Suite 11g. It will start by explaining the basics of events [also with respect to services], introduce several messaging patterns such as fire-and-forget and publish/subscribe, and explain some real life examples of using events in an SOA landscape. It will then dive into the underlying eventing infrastructure of Oracle WebLogic Server 11g and Oracle SOA Suite 11g that is based on JMS and AQ, and demo their use both inside and outside SCA composites using resource adapters, PL/SQL, and Java. The session will finish with the introduction of Oracle SOA Suite 11g’s EDN and the use of (composite) sensors in SCA composites.
ADF Developers – Make the Database Work for You – Lucas Jellema
Most ADF applications interact with an (Oracle) RDBMS. It has been proven many times over that it really pays off to strike a good balance between ADF and Database – exploring which tool is best suited for which job and how the two can optimally leverage each other. This session discusses the various points of interaction between database and ADF and when to use which – for example Cursors, Types and Collections, XML as well as interaction via HTTP. The session will focus on ADF BC but will mention JPA and plain JDBC as well. It also highlights some special database features – hierarchical query, flashback, result cache, View with Instead Of Trigger, Analytical Functions – that ADF applications can leverage or should employ – security, database tracing, cache refresh through DB Query ResultSet Change Notification, constraints, and exception handling. Attendees learn what the database can do for their ADF application. They will be able to make better design decisions about how to implement functionality, which tier(s) to use, and how they can best work together.
…and Thus Your Forms ‘Automagically’ Disappeared – Luc Bors
This session is A Forms Modernization Story Featuring JHeadstart Forms2ADF. It talks about considerations to make when you are about to decide whether or not to leave Forms and go for ADF. You will see the process of conversion step by step starting with the business case and ending at the new ADF application. You will hear about issues you run into when using Forms2ADF for automated conversion and you will get hints on how to use Forms2ADF.
My First Steps with RAC One Node 11gR2 (11.2.0.2)
Apr 5th
I’ve been using RAC (10gR2) for years now and I was wondering how RAC One Node differs from RAC. That’s why I used a few ‘old’ RAC servers that we had ‘laying’ around to install a RAC One Node Cluster. I also used that cluster to get some hands on experience with ASM but that is not the topic for this post, so I will not go into that further.
I assume that installing the software is a breeze for everyone who has some experience with RAC and who has read the manual so I will not go into that as well. Once you have fulfilled the prerequisites it’s a matter of next, next, finish. Read the rest of this entry »
