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.

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

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

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?

RFS: No standby redo logfiles …

Some day, you may trap the following error message with your standby database alert log (seen on a 10.2.0.3):

RFS: No standby redo logfiles available

The reason for this message is most probably that you have cloned your standby database with rman and configured your dataguard environment to directly write into redo log at standby (using LGWR), instead of just transfering the archive log items from primary (using ARCH), but have missed to provide the necessary standby redo log files. RFS may also log RFS[1]: Unable to open standby log 9: 313 or something similar (the primary will also complain about connection problems to the standby destination target, which is the standby redo log, actually).

RFS: No standby redo logfiles available of size 104857600 bytes

Installing DBPrism 3.0.2.1.0 (in-Oracle-Lucene) on a WinXP-10gR2

Introduction

This post talks, shares experiences, about installing Marcelo Ochoa’s (MO, Marcelo Ochoa’s personal blog) in-oracle-lucene implementation into an oracle 10.2.0.3 on winxp. MO did a really great job in porting a raw lucene to an oracle database. His essential trick is actually to represent the file system layer, lucene usually lives in, within a blob based storage as well as employing the oracle odci-interface. Any further information is available in the online documentation here Lucene Domain Index.

Downloads

The latest version of the code stack is available as lucene-odi-bin-3.0.2.1.0.zip from http://sourceforge.net/projects/dbprism/files/odi/3.0.2.1.0/. Do not care about the sourceforge project being called DBPrism or this other code stack around being tagged ojvm, this is all history. The latest version to comprise the lucene 3.0.2 core base is lucene-odi, lucene-ojvm currently features the lucene 2.9.2 core base as just a maintainance release.

Another necessary download is ant, as a build and installation runtime environment. I sourced apache-ant-1.8.1-bin.zip from http://ant.apache.org/bindownload.cgi. Finally, iff you plan to compile the java code to windows dll’s for better performance (especially on production hosts), you may need to get Microsoft Visual C++ 2010 Express from http://www.microsoft.com/express/Downloads. Its free and will supply, beneath a lot of other stuff, what is expected, i.e. provides for a C++ compiler and dll linker.

Simulating and resolving a dataguard archivelog gap

I was wondering what will happen iff some archivelog gap really happens in a dataguard environment. How to resolve it? What has to be done manually and what happens automatically due to the remote file service (fal), for example.

Well, my attempt in producing the problem goes like this:

• verify normal operation by doing manual log switches on primary and watching the alert.log on primary and standby what happens
• defering the remote log destination (usually #2) on primary
• doing another manual log switches on primary that do now, don’t get shipped to standby
• backup primary with removing all backed up archive logs from the recovery destination
• bounce primary and standby
• activating the remote log destination (usually #2) on primary again, doing another manual log switches on primary, watching the alert.log on primary and standby what happens

Ok, let’s follow the alert.log on primary and standby …