Thursday, July 30, 2009

Refresh Fast

To enable refresh fast on a materialized view with joins

1. create materialized view logs on the base tables with rowids and participating columns
CREATE MATERIALIZED VIEW LOG ON FM_ADDON_SECTOR  WITH ROWID
(addon_id,departure,destination,SECTOR_NO,RETURN_COMPULSORY,STOPOVER_ONLY,
IN_BOUND_FOR_IN_DATE,CALC_METHOD)INCLUDING NEW VALUES;


2. add rowid of participating tables in the select list

select ...
,rt.rowid rtid
,oas.rowid oasid
,oadepz.rowid oadepzid
,rdepz.rowid rdepzid
,dadesz.rowid dadeszid
,rdesz.rowid rdeszid
,das.rowid dasid
,daa.rowid daaid
,oaa.rowid oaaid
,oa.rowid oaid
,da.rowid daid
....


3. Then create the materialized view with

CREATE MATERIALIZED VIEW "ROUTES"
nologging
BUILD IMMEDIATE
USING INDEX
REFRESH FAST on commit
AS SELECT ...


Wednesday, July 22, 2009

Java Code Sinppet: OWL Demo with SWRL

In the classpath :
protege.jar
and every jar in the
Protege_3.4\plugins\edu.stanford.smi.protegex.owl
directory
public class OWLDemo {

public static void main(String[] args) {
try {
String uri = "file:///C:/MSC/project/old/travel.owl";
// String uri = "http://localhost:8080/owl/travel.owl";
OWLModel owlModel = ProtegeOWL.createJenaOWLModelFromURI(uri);
OWLNamedClass classs= owlModel.getOWLNamedClass("Trade_Association");

Collection c = classs.getNamedSubclasses();
Iterator itr = c.iterator();

while(itr.hasNext()){
OWLNamedClass cc = (OWLNamedClass) itr.next();
System.out.println(cc.getNestedBrowserText());
System.out.println(cc.getName());
}

SWRLFactory factory = new SWRLFactory(owlModel);
// factory.replaceImps(owlModel); // to run the same query again uncomment
SWRLImp imp = factory.createImp("Query-2", "Use_Inconjunction_With(?x, ?y) -> sqwrl:select(?x, ?y)");

SWRLRuleEngineBridge bridge = BridgeFactory.createBridge("SWRLJessBridge", owlModel);
bridge.infer();
SQWRLResult result = bridge.getSQWRLResult("Query-2");
while (result.hasNext()) {
System.out.println("X: " + result.getObjectValue("?y").getIndividualName());
System.out.println("Y: " + result.getObjectValue("?x").getIndividualName());

result.next();
}

} catch (Exception ex) {

}

}

}


A Useful Guide

Friday, July 17, 2009

Enterprise Manager Error

Symptoms

emctl start dbconsole 
fails and emdctl.trc has the following
Thread-4135626432 WARN  http: snmehl_connect: connect failed to
(hostname:3938): Connection refused (error = 111)


caused by agentTZRegion pointing to wrong timezone in emd.properties file (more on metalink note 751634.1)

find a supporting timezone from $ORACLE_HOME/sysman/admin/supportedtzs.lst file

and set the TZ value with
 export TZ=time_zone
ORACLE_HOME/bin/emctl resetTZ agent

emctl start dbconsole


If emctl has never started, when trying to reset the TZ (more on metalink note 388280.1)


ERROR at line 1:
ORA-20233: Invalid agent name hostname:3938
ORA-06512: at "SYSMAN.MGMT_TARGET", line 3737
ORA-06512: at line 1
will occur. This could be confirmed with

select target_name, target_type from mgmt_targets;


To execute mgmt_target.set_agent_tzrgn procedure, there should be a target call 'oracle_emd' in table.

To fix this

$ export EMDROOT=$ORACLE_HOME
$ export EMSTATE=$ORACLE_HOME

and comment the agentTZRegion parameter in the AGENT_HOME/sysman/config/emd.properties file. Then reset the agent timezone and start again


emctl resetTZ agent
emdctl validateTZ agent
emctl start dbconsole


Even all of the above it may still not work and following described on the metalink note 821137.1 finally did the trick.

1.
 grant execute on utl_smtp to public;
grant execute on utl_tcp to public;
grant execute on utl_file to public;
grant execute on dbms_sql to public;
grant execute on DBMS_OBFUSCATION_TOOLKIT to public;


2. Delete the files manually
$ORACLE_HOME/hostname_sid
$ORACLE_HOME/oc4j/j2ee/OC4J_DBConsole_hostname_sid


3. create the dbcontrol again
emca -config dbcontrol db -repos create


More infor is available on following metalink notes

Note 278100.1 - How to Drop, Create and Recreate DBconsole in Database R2.
Note 750768.1 - Quick Guide to Avoid Issues in Creating DB Control Using EMCA.

Monday, July 13, 2009

OLAPIHISTORYRETENTION

caused by
 BUG 3386542 - OLAPI trggers that are installed with seed database, (OLAPISTARTUPTRIGGER and OLAPISHUTDOWNTRIGGER), does not handle absence of Oracle OLAP.


Can also occur when oracle standard edition is installed in which OLAP is missig.

solution is detailed on metalink note 266728.1


Disable OLAPISTARTUPTRIGGER and OLAPISHUTDOWNTRIGGER to avoid error from being generated.

ALTER TRIGGER SYS.OLAPISTARTUPTRIGGER DISABLE;
ALTER TRIGGER SYS.OLAPISHUTDOWNTRIGGER DISABLE;



Friday, July 3, 2009

Changing OBIEE default port

OBIEE default port is 9704 to change it ...

Before making any changes backup the files

1. Open the file named default-web-site.xml in OracleBI\oc4j_bi\j2ee\home\config and change the port value

2. Change the port in OracleBI\oc4j_bi\j2ee\home\applications\bioffice\bioffice\WEB-INF\bioffice.xml file

3. Change the port in OracleBI\xmlp\XMLP\Admin\Configuration\xmlp-server-config.xml

4. Change the port in OracleBIData\web\config\instanceconfig.xml

If port is changed to 80 (web port) in Linux only root user can run an application which binds to a port less than 1024.

After the changes restart OC4J