OWB 11.1 on an 10.2 database with ‘Split Repositories’

I’m currently working for a customer that is using OWB 10.2.0.1 and a 10.2.0.4 database. All on Windows. Not the best environment in my humble opinion, but hey, it is a customer. They can decide that.

We were having some problems with OWB that have been solved in OWB 10.2.0.4 but also in 11.1.0.6. In other words, we had to do some migration. Because Oracle 11g is the way to go (there were more business-like terms used to persuade the business to accept this decision of course), we decided to skip OWB 10.2.0.4 and start using OWB 11.1.0.6. The DBA’s were not ready to start using database 11g so we also decided to stay on 10.2.0.4 with the database. This means you have to do some extra steps in your database to get OWB 11 running. Perfectly documented in this part of the administration guide for OWB. Worked like a charm.

The difficulties came later on. We have an architecture that looks very much like the Split Repositories Implementation in the same manual. In the said architecture there is only one control center service running, according to the manual. And it is running against the run-time environment. If I tried that I could not deploy from my design repository to my run-time environment and if I stopped the control center service against the run-time environment I could not run any mappings.

So why not start two control center services? One in the design environment and one in the run-time environment. I tried that, but I ran into some obscure java errors that said ‘a file could not be opened because it is in use by another program’. Just that: a file. Not a name, not a directory, just a file. Period. Time to go file hunting.

I imagined it to be a log file of OWB that was opened by the control center service. Both services were using the same OWB_HOME so I went looking there. In <OWB_HOME>/owb/admin/log where 2 files clearly related to the control center service. One was a log file (OWBSYS.LOG) that I traced to a setting in the run time parameters table of the repository owner. The table is owned by owbsys and is called wb_rt_platformproperties. Look for a property_path with value “property.RuntimePlatform.0.messages” and “property.RuntimePlatform.0.errors”. They both have the default property_value “%RTHOME%\owb\log\OWBSYS.log”. I changed these parameters so both control center services would use a different log file. Indeed they did if I started one service, stopped it, started the other service. But if I started both services I still got the ‘file already in use’ message. I left the setting for the parameter because I like to get different log files for different services and went hunting further.

The next log file is created when starting the service with <OWB_HOME>\owb\bin\win32\run_service.bat (or the shortcut to it in the programs menu -> Oracle -> Warehouse Builder -> Administration -> Start Control Center Service). In this bat-file the output of the bat-file itself is sent to a log file using redirection (the >run_service.log at the end of the command). I first started one control center service then changed the bat-file so it would log to a different file and then started the second control center service. And lo and behold, I got me two running control center services.

Eureka!

I then had to do some fiddling to the bat-file because I did not want to change the log file every time I had to start the services. In Unix or Linux this is easy. You just add `date “+%Y%d%m_%H%M%S”` to the name of the file and you get an unique file name every time you start. On Windows this is not so easy. I googled around a bit and found some rather complex bat-files that seemed to work if I started them from the command line but worked differently when using the shortcut. It can probably be solved by someone more savvy than me in the wonders of cmd bat files but I decided to take the easy way out. I used the following bat file to find the number of log files that where already present. Add 1 to that count and append the file name with the resulting count. Now my control center services start every time! Even with split repositories.

This is how the bat file now looks (changed parts in bold and italic, you probably have to scroll a bit to see all changes):

@echo off
set STARTUP_TYPE=%1
shift
set NODEID=%1
shift
set RTHOME=%1
shift
set RTUSER=%1
shift
set HOST=%1
shift
set PORT=%1
shift
set SERVICE=%1
shift
pushd %RTHOME%\owb\bin\win32\
set JVM_OPTIONS=-Xmx768M
set JAVAPATH=%RTHOME%\jdk
set OWB_HOME=%RTHOME%
set JDK_HOME=%JAVAPATH%
set ORA_HOME=%RTHOME%
set OEM_HOME=%RTHOME%
set IAS_HOME=%RTHOME%
set ORACLE_HOME=%RTHOME%
set PATH=%RTHOME%\bin;%JAVAPATH%\jre\bin\client;%JAVAPATH%\jre\bin;%RTHOME%\owb\bin\adminrem if exist ..\..\..\owb\lib\int\rtpplatform.jar goto check_common
rem echo cannot find rtpplatform.jar, exiting
rem goto exitrem :check_common
rem if exist ..\..\..\owb\lib\int\rtpcommon.jar goto run_service
rem echo cannot find rtpcommon.jar, exiting
rem goto exit
rem :run_service
:: aanpassing om control center service voor 2 db's op dezelfde machine te laten lopen
set tel=0
for /f "tokens=*" %%a in ('dir %RTHOME%\owb\log\run_service*.log /B/A-D') do set /a tel+=1
set /a tel+=1
SET RUNSRVCLOG=%RTHOME%\owb\log\run_service_%tel%.log
%JAVAPATH%\jre\bin\javaw.exe %JVM_OPTIONS% -DORACLE_HOME="%RTHOME%" -DOCM_HOME="%RTHOME%" -DOCM_ORACLE_HOME="%RTHOME%" -classpath %RTHOME%\owb\bin\admin\;%RTHOME%\owb\bin\admin\launcher.jar -DOWB_HOME=%OWB_HOME% -DJDK_HOME=%JDK_HOME% -DORA_HOME=%ORA_HOME% -DOEM_HOME=%OEM_HOME% -DIAS_HOME=%IAS_HOME% Launcher %RTHOME%\owb\bin\admin\owb.classpath oracle.wh.runtime.platform.service.Service %STARTUP_TYPE% %NODEID% %RTUSER% %HOST% %PORT% %SERVICE%  > %RUNSRVCLOG%
:exit
popd

This is how the bat file used to look:

@echo off
set STARTUP_TYPE=%1
shift
set NODEID=%1
shift
set RTHOME=%1
shift
set RTUSER=%1
shift
set HOST=%1
shift
set PORT=%1
shift
set SERVICE=%1
shift
pushd %RTHOME%\owb\bin\win32\
set JVM_OPTIONS=-Xmx768M
set JAVAPATH=%RTHOME%\jdk
set OWB_HOME=%RTHOME%
set JDK_HOME=%JAVAPATH%
set ORA_HOME=%RTHOME%
set OEM_HOME=%RTHOME%
set IAS_HOME=%RTHOME%
set ORACLE_HOME=%RTHOME%
set PATH=%RTHOME%\bin;%JAVAPATH%\jre\bin\client;%JAVAPATH%\jre\bin;%RTHOME%\owb\bin\adminrem if exist ..\..\..\owb\lib\int\rtpplatform.jar goto check_common
rem echo cannot find rtpplatform.jar, exiting
rem goto exitrem :check_common
rem if exist ..\..\..\owb\lib\int\rtpcommon.jar goto run_service
rem echo cannot find rtpcommon.jar, exiting
rem goto exit
rem :run_service
%JAVAPATH%\jre\bin\javaw.exe %JVM_OPTIONS% -DORACLE_HOME="%RTHOME%" -DOCM_HOME="%RTHOME%" -DOCM_ORACLE_HOME="%RTHOME%" -classpath %RTHOME%\owb\bin\admin\;%RTHOME%\owb\bin\admin\launcher.jar -DOWB_HOME=%OWB_HOME% -DJDK_HOME=%JDK_HOME% -DORA_HOME=%ORA_HOME% -DOEM_HOME=%OEM_HOME% -DIAS_HOME=%IAS_HOME% Launcher %RTHOME%\owb\bin\admin\owb.classpath oracle.wh.runtime.platform.service.Service %STARTUP_TYPE% %NODEID% %RTUSER% %HOST% %PORT% %SERVICE%  > %RTHOME%\owb\log\run_service.log
:exit
popd

Change OWB location details

If you have used your locations to deploy objects to them (who doesn’t?), you cannot change the connection details easily. In the Design Center they are greyed out. The only way you can change them is by starting the control center, unregistering the location, reregister with the new details and deploy all objects again to regain the right deployment status. The last is something you cannot always do because that would mean e.g. dropping tables that already contain data you do not want to lose. Not an ideal situation.
Today I was reading this post about upgrading to 11gR2 which was a good post. Although I’m currently upgrading to OWB 11gR1 as 11gR2 is not yet available on Windows. Stewart Bryson had some trouble because during the upgrade his connection details where automagically updated. He first described how to change the connection details in the ‘old-fashioned’ way I just mentioned. But more importantly he described a second possibility I did not know of. It is possible to change the details using the OWB Repository browser! I do not start the Repository browser very often because it tends to slow down once you have had your share of deploying and executing. A simple view suffices most of the time (I’ll reserve that one for a later post).
But it meant I did not see all functionality, alas. It could have saved me some time.
Anyway, at least in 11gR1 (I’ll look into 10gR2 shortly) you can change host, port, sid and password from a convenient browser screen as presented below. Nice.

BTW, works for File locations too.
BTW2, The link to this screen is perfectly hidden under the link called ‘Unknown’ at the end of the line for each location
BTW3, It is available in OWB 10gR2 as well

One more date trick

Tyler Muth has a useful addition to the date functions I have published here before.

If you ever want to know how long ago a date is and you want to display it in ‘human readable’ format you (and I) could use his function.

Like this:

select date_text_format(sysdate - 3/86400) the_date from dual;
select date_text_format(sysdate - 5/1440) the_date from dual;
select date_text_format(sysdate - 1/24) the_date from dual;
select date_text_format(sysdate - 3.141549) the_date from dual;
select date_text_format(sysdate - 15) the_date from dual;
select date_text_format(sysdate - 120) the_date from dual;
select date_text_format(sysdate - 365) the_date from dual;
--------------------------------------------------------------------
3 seconds ago
5 minutes ago
1 hour ago
3 days ago
2 weeks ago
4 months ago
1 year ago

One more for the toolbox.

(Integrity) Constraints in a datawarehouse

In data warehouse land it is not very common to see constraints in the database. I never felt very comfortable with that, but until now I did not get around to analysing why I felt that way. Until I read this article by Tom Kyte. In the article Tom Kyte shows that the CBO (Cost Based Optimizer) can profit from the information that is derived from the presence of constraints by generating better query plans. Better in this case is defined as ‘producing result sets faster’. The examples in the article are not exactly ‘real world’ data warehouse examples. Following Tom Kyte’s line of reasoning I do agree that constraints are capable of improving the performance of queries.

The reasons for not having constraints in a data warehouse are along the lines of ’I have checked the integrity when I did my ETL, so why would I need constraints to confirm that? And besides, constraints would only delay my ETL because they have to be checked before they are really enabled’. I see a couple of flaws in this reasoning:

• I suspect that most constraints in a data warehouse cannot be enabled when actually applied. The quality of the ETL might be good, but is it just as good as a constraint would be? I think not.
• Enabling constraints might take time, but how often do you have to check constraints? Only when doing the ETL, of course. I hope that in your DWH, doing ETL will be during a small part of the time your DWH is being used. Otherwise your DWH will have a problem. The rest of the time your DWH will be used for querying and Tom Kyte just showed that querying can be sped up by applying constraints.

Summing up my pros and cons of applying constraints.

Pro:

• it will improve the data quality of the DWH
• it can speed up the queries in your DWH (querying it is the purpose of your DWH anyway)

Con:

• it will take more time to do your ETL (which is only a means to create your DWH)

My conclusion is that I wil try to incorporate as many constraints as possible in my next DWH. It also means I will have to be smart enough to enable the constraints at just the right moment during my ETL to have an acceptable loading performance.

More date arithmetic

I came across this today. I have been having quite a ball these last weeks with dates, periods and mainly overlapping periods in two tables. Therefore I am currently rather interested in date handling. This is definitely one for the Tips & Tricks book I seem to be gathering here. Nice post.

OWB Process Flows that keep saying they are running

Now and then an OWB started Processflow has trouble finishing. Or more likely I have created an incomplete Processflow. Most of the time this leads to an error when deploying the processflow again:

RPE-02062: ItemType LAAD_CMS cannot be dropped as it has running Processes. You must first abort all the running processes using the Oracle Workflow Monitor

To find out if there are any such processflows I use the following query:

SELECT item_type, item_key
FROM wf_item_activity_statuses_v
WHERE activity_type_code= 'PROCESS'
AND activity_status_code != 'COMPLETE';

And you can tell OWF the processflow is not running anymore with:

DECLARE
t_result VARCHAR2(300);
BEGIN
wf_engine.abortprocess (‘itemtype’,’itemkey’,t_result);
COMMIT;
END;

Where Itemtype and ItemKey must be copied from the results of the query.

There are probably more sophisticated methods to handle these situations (probably from OWF-savvy people), but it gives me a handle to know what is going on and what I can do about it.

Little known but useful facts about Oracle

Laurent Schneider has a blog that I follow and always has tiny facts that you think you know the details about. But, when you look closely, the details are somewhat different from what you thought they were.

One such gem today: 1 <> 1 and another about 2 weeks ago: commit suicide

Number of days in a year

I tend to forget these things. So I\’d better write them down.

select to_date(\'09-sep-2009\') - trunc(\'09-sep-2009\', \'mm\') days_since_month_begin 
,trunc (to_date(\'09-sep-2009\'),\'yyyy\') first_day_of_year
,interval \'1\' year just_curious
,to_char(trunc (to_date(\'09-sep-2009\'),\'yyyy\')+ interval \'1\' year - 1 , \'ddd\') days_in_year
from dual;


Missing window panes in OWB editors

I have been having this several times. And each time I have to look for the exact location and name of the files you have to handle to fix things. So now I intend to note the reference to the blog entry and at least one forum discussion here. So I will be able to find it.

The problem at hand is that now and again the ‘Palette’ or ‘Object properties’ window pane disappear from the Mapping Editor or the Process Flow Editor. Or any other editor in OWB. No matter how often you activate or deactivate the window pane in the ‘window’ menu, it just won’t appear. I found the solution in the OWB blog, here. But, for my own sake, I will copy it in this blog entry.

Lost your panels in the UI – here is how to fix it…

By jean-pierre.dijcks on May 10, 2007 3:45 PM

I have seen people ask this question a couple of times, and there is actually an easy fix to the problem. The layout for an editor (editors are for example the mapping editor, the data object editor, the profile explorer etc.) is saved per computer to the file system. These files are written everytime the layout is changed and when running the editor OWB reads the layout. That is why, if a panel is “gone” it will stay that way…

When the product is newly installed these editor layout files are not there, so removing them is the solution to “missing panels”. Removal means the panels are appearing as they are in “factory defaults”.

You can find the layout files in “<Oracle Home>/owb/bin/admin” and they are typically either named <editorname>layout.xml (most of them) or <componentname>.layout. For example for mappings, the layout file is MappingEditorLayout.xml, for the console (the designer) the files is called console.layout.

I typically remove the file from the file system, but you may want to try renaming it if you are unsure that this is what you want to try…

Update 11gR2:
Things have changed, for better or worse we’ll have to see. Any way, here’s a link to the 11gR2 way of doing the above.

UKOUG; My schedule and gradually some notes

Monday december 1st

Keynote    ‘Oracle Corporate and Product Strategy Update’, David Callaghan, Oracle

As keynotes go, general and fluffy. Main point: Oracle is (trying to be) complete, open and integrated.

Beginners’ Guide to Partitioning, Mr Jonathan Lewis, JL Computer Consultancy
Indeed a beginners guide. But mr. Lewis could not help himself and he did put some valuable advice in regarding performance aspects. Such as:

• There is a penalty to partitioned indexes which can lead to having to visit every partition which is not expected behaviour
• Bind variables may make it difficult for the CBO to do partition pruning, dates can do that as well, and make sure you use 4-digit years preventing the optimizer from thinking you are talking about the first century
• New to me, but probably not to others, Bloom filter (in 11g, maybe already in 1ogR2?), something to investigate
• Partition exchange when a global (unique) index is present will require checking of the index even when using the NOVALIDATE clause.

And a teaser/challenge to check whether it is possible to solve the performance problem of “where ‘2007-01-01′  between start_date and end_date” with a range-range composite partitioned table.

Oracle Business Intelligence Road Map and Strategy, Mr Nick Tuson, Oracle

General introduction of where Oracle thinks the place of BI is. It is a key support part of Enterprise Performance Management (EPM). And EPM is supported by virtually all Oracle Products, not only the BI products. Oracle uses something that they have called the Common Enterprise Information Model (CEIM). I will try to find a reference or white paper to it later.

BI applications have a new component wich will be the integration of Oracle Data Integrator (ODI, formerly Sunopsis).

Lots of new and changed interfaces, personalised start page, lend from server technology and Fusion middleware integration.

Probably a useful presentation if you are ‘into’ OBIEE, Hyperion and the like. Unfortunately I am not.

Anatomy of Data Breaches: How They Occur and How to Protect Against Them, Ms Tammy Bednar, Oracle

Excellent material of how you can characterise the types of people that are a threat to your systems. Including the measures you can take to prevent the data breaches from happening. If the presenter had been a bit less nervous the presentation would have been even stronger. I hope to very soon see the slides on the UKOUG conference website so I can build on that to win over more people to consider security.

Flash Recovery Area, Mr Tuomas Pystynen, Miracle Finland Oy

As can be expected of Tuomas a thorough investigation of what the FRA is, what kind of files it holds, what the drawbacks are of using it and even a novel use of FRA for making a copy of a database. If you have enough diskroom, of course nothing is free.

Below a short description of the kind of files FRA can hold and a warning (by Tuomas) that you can place other files in the FRA but then they are not seen by the processes that guard the filling up of the FRA and you could end up with a low level error along the lines of ‘file system full’.

• Current control file
• Online logs
• Archived logs
• Control file autobackups and copies
• Datafile copies
• Backup pieces
• Flashback logs

Only the last logs will actually be used in a Flashback feature (flashback database to be precise) so why it is called Flash Recovery Area is a matter of opinion.

The best way… , Thomas Kyte, Database Product Evangelist, Oracle

I would like to call the presentation a kind of ‘rant’ against the people who, just like that, want to know what is the BEST way to do something. And they don’t want to spent time thinking about whether the way they have been given is indeed the best way in their circumstances. Which is almost never the case. I think it is about save to say that we all know the standard answer to the standard ‘best way’ question; It depends. And the above illustrated in a manner that we all would love to be able to copy. But alas, there is only one Tom Kyte.

And then the UKOUG 2008 Opening Party …….

Tuesday december 3rd

‘Oracle Exadata – Extreme Performance’ , Andrew Mendelsohn, Oracle

A short reprise of why we need a database machine or even a storage server. And all that from Oracle. Very interesting, but it will be some time before most of us will see such a database machine from close by. At least for me, the dataware houses I’m working on are not that big.

Monitoring Oracle using Open source software, Mr Jason Lester, DSP Managed Services

A good, well structured presentation of why you would choose Open Source Software (OSS). And in this case not from a technical viewpoint, but from a business viewpoint. Main points being vendor lock-in and dependency on key technicians with commercial software. Also a good overview of what OOS products come into play and how you can integrate several products into a valuable ‘dashboard’. There must be something there for the Techies as well. Looking forward to this document being uploaded as well.

Change, Change, Change, Mr Tom Kyte, Oracle

What can I say, good as ever. Read his blog.

Oracle 11g’s SuperModels: Predictably Enhanced, Ms Melanie Caffrey, Oracle

Weird PL/SQL, Mr Steven Feuerstein, Quest Software

Database Constraints – A Woeful State of Affairs and a Polite Excuse, Dr Hugh Darwen, University of Warwick; Mr Toon Koppelaars, RuleGen BV

Socializing in the COMMUNITY FOCUS PUBS …..

Wednesday, december 4th

Designing Data Warehouse in Oracle 11g, _{Mr Vincent Chazhoor, Lafarge NA}

Data Warehouse modelling for dummies, _{Mr Andrew Bond, Oracle}

Sort Merge: Coming to a join near you, _{Ric Van Dyke, Hotsos}

Active Statistics, _{Mr Wolfgang Breitling, Centrex Consulting Corporation}

Performance Tuning Basics, _{Mr Doug Burns, Bayvale}

Visual Data Modelling with SQLDeveloper, _{Sue Harper, Oracle}

Last, but not least; 25th YEAR CELEBRATION PARTY

Thursday, december 4th

A complete BI+W Architecture, _{Mr. Andrew Bond, Oracle}

What’s in Oracle Warehouse Builder 11GR2, _{Miss Nathalie Nunn, Oracle}

Comparing Materialized Views and Analytic Workspaces in Oracle Database 11g, _{Keith Laker, Oracle}

Deploying a Dimensional Model on the Oracle Database, _{Stewart Bryson, Rittman Mead Consulting}

Managing OWB 10gR2 in a deployed environment, _{Wolfgang Scherrer, infomArt Gmbh}

Variance as a tool: Measuring for Robust Performance, _{Ms Robyn Sands, Cisco}

Optimizing Systems by Eliminating Throwaway, _{Mr Daniel Fink, OptimalDBA}

Early night, much needed.

Friday, december 5th, Sinterklaas, pakjesavond

Not home this night so I won’t get any presents from Sinterklaas this year. Oh well, I don’t know if I have been that good a boy this year.

Oracle 11g Data Warehousing Masterclass, _{Mr Mark Rittman, Rittman Mead Consulting}

Advanced SQL Features, _{Mr Daniel Fink, OptimalDBA}