Running a standalone apex listener on a 11gr2

This is just a cookbook version of a get to go of the oracle apex listener on a (windows) oracle 11gR2. Necessary resources comprise the (otn authenticated) installation and developer guide for apex listener (pdf) as well as the download area for apex listener (zip). Note that there is another installation and developer guide for apex listener that comes as html pages with the downloadable package. It does contain release notes as of august 2011 such that it should be more recent than the pdf version (as of February 2011). There also is an apex listener forum hosted by oracle, see the APEX Listener OTN Forum for any user driven discussion threads.

Me, i tested the standalone version of apex listener to be employed in some rapid prototyping developments, establishing an easy backend data connection for nowadays ajax-based web ui’s. Iff you go for some apex listener evaluations pointing to productive environments, do consider deploying the code with a standard j2ee container host.

The installation (and developer) guide is, as mostly with oracle, a well done step-by-step manual just to follow and this is the way i choosed to go as follows: Get the downloadable package from the resource above. It’s an archive versioned as apex_listener.1.1.3.243.11.40.zip to this date and contains a singleton war file along with some documentation.

I already have apex installed with this database instance and decided to extract the archive to E:\oracle\product\11.2.0\dbhome_1\apex-listener, pointed out as the “temp” directory, for whatever reason, in the installation guide. From this root directory i launched the war file along with some documented parameters to depict the deployment directory, the apex image directory, the http port to use, and so on:

E:\oracle\product\11.2.0\dbhome_1\apex-listener\temp>java -Dapex.home=E:\oracle\product\11.2.0\dbhome_1\apex-listener\runtime -Dapex.images=E:\oracle\product\11.2.0\dbhome_1\apex\images -Dapex.port=8585 -jar apex.war

The deployment directory, actually, is the place where the war file will be extracted to and where runtime information as well as configuration data will be written. Do not use -Dapex.erase=true iff you decide to follow my installation hierarchie, because doing so will erase all configuration data too.

Reading an xml file as an oracle external table

There is a couple of posts around that imply or claim an oracle external table to be able to read xml files by design. This is not true, really. It may work for you or actually may fit your current xml file structure but you should generally not approach to read xml by external tables. Here is why.

What external tables or sql loader for better can do actually is reading line oriented, file based data in bulk and quite fast. It offers a lot of settings to parametrize the loading process, the reader, according to your input format. You may for example have a look at the excellent series of posts discussing this options by Jiri starting here Oracle External Tables by Examples part 1 – TAB delimited fields.

Well, this functionality, may also be (mis)used to read xml instances from files. Given a file as the following one may be interested in extracting the contents of the token tags.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<FILE_INFO>
	<PERSON_BILD>
		<token_row><token>Fritz &amp; Fischer</token></token_row>
		<token_row><token>Boris Borsberg</token></token_row>
	</PERSON_BILD>
</FILE_INFO>

Some external table definition may then look like this:

Installing semantic technologies support on a fresh Oracle 11gR2.0.1

The following sketches the prerequisites of using Oracle Database Semantic Technologies, see Semantic Technologies Center and Oracle® Database Semantic Technologies Developer’s Guide as an overview and as the developer’s guide respectively, in your databse applications.

The procedure has been executed on a fresh Oracle 11R2.0.1 running on x64 Windows. The semantic technology stack in fact requires an enterprise edition of Oracle, featuring the partitioning and the spatial options as well as the underlying components XDB, JAVAVM and ORDIM to be installed … and licenced. Do check in advance whether this basement is affordable against your business budget because according to the current Oracle global price list by processor, an enterprise edition is around k$ 47, adding partioning by k$ 11 and spatial by k$ 17 plus updates and support. Do note also, that the standard and the standard one editions of Oracle, offering a subset of spatial called Oracle locator is not not sufficient to run the semantic technology layer. That is, it takes a couple of bucks to even create your first simple triple of sematic data with Oracle.

Checking the Oracle base software installation (anything executed as sysdba)

This section of the article, checking the Oracle base software installation itself, has been proof-taken from Stanley Guan’s blog Xml and More, entry Installation of Oracle Semantic Technologies. I can’t imagine why he has skipped the actual steps involving sematic-related code and objects there, moving me to write another post. However, Overview of Oracle Database Semantic Technologies is also worth a look really.

-- verify the underlying components, namely XDB, JAVAVM and ORDIM
select comp_id,version,status from dba_registry where comp_id in ('JAVAVM','ORDIM','XDB');

COMP_ID  VERSION      STATUS
-------- ----------   -------
ORDIM    11.2.0.1.0   VALID
XDB      11.2.0.1.0   VALID
JAVAVM   11.2.0.1.0   VALID

-- assure the options partitioning and spatial are available
select parameter, VALUE from v$option where parameter in ('Partitioning', 'Spatial') order by 1;

PARAMETER     VALUE
------------  ------
Partitioning  TRUE
Spatial       TRUE

-- additionally check spatial in detail
set serveroutput on
execute validate_sdo;

select comp_id, control, schema, version, status, comp_name from dba_registry where comp_id='SDO';

COMP_ID   CONTROL   SCHEMA   VERSION      STATUS   COMP_NAME
--------  --------  -------  -----------  -------  ----------
SDO       SYS       MDSYS    11.2.0.1.0   VALID    Spatial

select object_name, object_type, status from dba_objects where owner='MDSYS' and status <> 'VALID';

Es wurden keine Zeilen ausgewõhlt

Moving a vmware player driven virtual machine between local discs

Sometimes, simple things seem to become complicated by the multitude of options being available on how to actually execute them. This was the case for me lately when i tried to just move a vm from some location on my local discs to another. This vm runs in a vmware player 4.0.1 and remembering the steps i executed during installation, that is, copy the source files (some appliance of redmine on suse, great stuff, really, see http://bitnami.org/stack/redmine) to some location and then opening them in the player, i decided to evenly do another copy as well as another open.

However, although the copy vm booted nicely, it somehow lost its network settings such that eth0 failed to come up. At a first glance, i supposed that having two vm’s of the same environment (excluding the disk location) might puzzle vmware player such that i shutdown the copy vm, renamed the original vm disk location and removed the original vm from the vmware player library entry by offer. On retry, i found the same result, eth0 not coming up.

There are other approaches, in fact, but without boring anyone out there to immediate sleep the following steps did work for me eventually:

1. Shutdown this running vm (environment) cleanly
2. Close vmware player (iff not self-closed)
3. Copy, don’t move for backup reasons, this vm top directory (usually the vm name) to the new location
4. Start vmware player again
5. Open the settings of this vm, go to the options tab and change the general setting called working directory to point to the new location
6. Close the settings of this vm by save
7. Launch this vm as usual

have fun!

Reformatting empty but corruptly marked oracle blocks

What actually appeared as a top most error on the monitoring stack was:

channel ORA_DISK_2: backup set complete, elapsed time: 02:57:02
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 01/01/2012 05:17:04

RMAN-03009: failure of backup command on ORA_DISK_2 channel at 01/01/2012 02:20:02
ORA-19566: exceeded limit of 0 corrupt blocks for file H:\ORACLE\SAN_3\ORADATA\WAZ\TEXT\TEXT_DATIDX_05.DBF

Recovering disk failures that comprise undo segements

This is just a snapshot of some sitation that happened quite a while ago on a 10g oracle database. I’m not going to dive into the details that much, just providing some selects to decipher the scenario and show up one of the probable ways of resolving the problem.

Jep, due to a disk failure, as i noticed during the analysis, some statements and things complained about datafiles being offline. An attempt to re-online some datafile resulted in an ORA-00376 error as follows:

SQL> ALTER DATABASE DATAFILE 'H:\app_ORACLE\SAN_4\ORADATA\appxyz\stuff\app_UTL_06.DBF' ONLINE;
ALTER DATABASE DATAFILE 'H:\app_ORACLE\SAN_4\ORADATA\appxyz\stuff\app_UTL_06.DBF' ONLINE
*
FEHLER in Zeile 1:
ORA-00376: Datei 6 kann zur Zeit nicht gelesen werden
ORA-01110: Datendatei 6: 'H:\app_ORACLE\SAN_3\ORADATA\appxyz\UNDOTBS03.DBF'

Looking up dba_data_files produced this output:

Resolving connections errors on oci8 in jdeveloper 11gR2

Got a fresh jdev 11gR2 (11.1.2.0.0, studio) on an existing oracle client home 11.1.0.6.0 and tried to create a connection to an oracle database based on the oci8 option (thick instead of thin driver) in the Create Database Connection dialogue. The result on some test (test connection button) was a:

Test failed: oracle.jdbc.driver.T2CConnection.getLibraryVersionNumber()I

The resolution trick seems to comprise of a copy of the ojdbc6.jar of the oracle client installation, here in d:\Programme\Oracle\product\11.1.0\client_1\jdbc\lib\, to the jdeveloper app server lib path, here in d:\Programme\Oracle\Middleware\wlserver_10.3\server\lib\.

My reference to the problem solution was: this thread entry on the oracle forums.

have fun

Repositioning the wordpress start and posts pages

I was recently asked to provide a general, description-oriented start page on another wordpress blog (ludoix.wordpress.com). An initial direction was given by Gary Barrett with this article on the wordpress support portal. The trick is to set up another page on the Pages view and configure it to have the lowest order index. Afterwards you proceed to the Settings / Reading view and change the selection below Front page displays / Front page to the just created item. That’s great so far, on reloading the blog you’ll now find your new start page on intial display.

However, changing the settings with Settings / Reading / Front page displays I already wondered what to enter with the Front page displays / Posts page select list. I actually expected to find an entry such as posts or default-posts or whatever but to no avail. In fact, leaving out this selection removes the the posts page from the blog completely. Stuck! Just driven by some enlightment of intuition (ha ha) I tried the following: I created another (empty) page on the Pages view, not forgetting to set the order index accordingly. Thereafter, i proceeded to the Settings / Reading view again and configured Front page displays / Posts page to this new (empty) item. And, it worked! That is, wordpress obviously only needs another (empty) container page to put the posts page into.

Another functionality i employed with the new start page was page templates that you’ll find along with the order index on the Pages view. It removes all the sidebar widget stuff from the start page which i find is much more comfortable. Please note however, that the availability and the layout of page templates depend on the theme of the blog.

have fun!

Client-failover for dataguard switchover and failover

Introduction

The following is nothing more than some extension of the oracle metalink document:

[ID 740029.1] Step By Step Guide On How To Configure And Test Client-Failover For Dataguard Switchover And Failover

That is, the article does not only present the cookbook how-to of its anchestor but also examines what happens in the database and what the clients experience will be concerning a so called transparent application failover (TAF). The scenario has been tested on an oracle 10.2.0.3 on windows, please note that the metalink document points to a some more applicable technique available with oracle 11gR2 (Client Failover Best Practices for Highly Available Oracle Databases: Oracle Database 11g Release 2).

Client-failover for dataguard, away from other failover scenarios, essentially aims at using a general tnsnames entry against some server-side database endpoint, no matter what server-side database instance is currently running in a dataguard primary role. This is actually the transparency in a transparent application failover where the application, client here, does not have to care to switch its network configuration in any way (some simple implementation would be, not uncommonly, to provide some tnsnames.ora, tnsnames.ora.prm and tnsnames.ora.stb and rename the files) during a failover or switchover.

Materialized views ddl and dml stati (and the infamous NEEDS_COMPILE)

introduction

Employing materialized views (or snapshot or mview for short) for producing simple sum-up reports is really cool stuff. You just launch some statement like the following:

create materialized view some_mv
  build immediate
  refresh force next (sysdate + 1/24/12)
as
select X.ts, Y.name from (
  select to_char(max(A.ts), 'dd.mm.yyyy hh24:mi') as ts, B.blubb
    from tab1 A, tab1_1 B where A.id = B.id
    group by B.blubb
) X, tab2 Y
  where X.blubb = Y.id;

and will be served with a well performing collection of data that is more or less current (that is why i call that simple). Oracle will care for the refresh of the mview by self-registering a job on calling dbms_refresh.refresh('some_mv');, that starts immediately and takes the interval from the statement. Handing in the force keyword just chooses a fast refresh method iff possible and a complete refresh method otherwise. There are more defaults applied by leaving other keywords unset, the refresh mode for example, but this is not the point of the article. It is:

Why does that … mview always show a state of NEEDS_COMPILE although no ddl action has happend along the objects dependency chain lately?