Feed aggregator

ODTUG Kaleidoscope, the Oracle Developer Tools User Group annual conference which next year is running in Washington DC, has a call for papers that closes on November 10th. This year, I’m the content lead for the BI, DW and Hyperion Reporting track and together with the rest of the committee, we’re trying to pull together an exceptional agenda based around OBIEE, OWB, ODI, Discoverer and the Hyperion reporting tools. By June next year when the conference runs, the 11g versions of OBIEE and ODI should be out, and OWB11gR2 is already with us, so this will be probably the first conference where we’ll be able to talk about our experiences with the tools with them actually out on general release.

If you saw details of our BI Forum in Brighton last May, but you were based outside of Europe and couldn’t make it, this would be an excellent event to put forward a paper for, and attend in June next year. If you’re Europe-based but fancy a trip over to DC in June (or indeed from anywhere outside the States), this is a great excuse to get over and meet other BI & DW developers from North America and around the world. ODTUG is a worldwide user group that focuses solely on development tools, and the sessions we run are practical, focused on tips and techniques and led by developers like yourselves. If you’re considering putting a paper forward, do so now and we’ll hope to see you in June 2010. Also, if you want to discuss potential topics with me, just drop me a line and I’d be happy to offer advice.

In other news, you might have seen a few weeks ago a posting where I mentioned that I was standing for a position on the ODTUG board. I’m so keen on what ODTUG are doing, particularly around creating a worldwide community for BI, DW and Hyperion developers, that I decided to stand for the ODTUG election and I’m very pleased to announce that I secured a place on the board. Thank you very much to everyone who voted for me, and I’m particularly looking forward to working with the rest of the ODTUG board, and the membership, to bring more great BI and DW content to the user group. With the imminent release of OBIEE 11g, together with updates to OWB and ODI, we’re at an inflection point with the various Oracle tools and I’m really keen that ODTUG remains the premier destination for help and support around their adoption.

I’m also looking forward to working with Shyam Nath from BIWA, and Faun De Henry from OAUG, so that all of the international user groups can work together to meet the needs of developers and customers using Oracle’s BI & DW tools and applications.

I got an email this week from old friend and Essbase practitioner, Bryan Bain, about an issue he was encountering when trying to upgrade a customer from Hyperion 11.1.1.1 to Hyperion 11.1.1.3. As we focus on software development, we don't run production systems on our servers and thus never have a need to upgrade

Bryan emailed me back a short time saying he had "found the solution and it's pretty cool. You should probably blog about it, but you have to give me credit. :)". Better yet, I convinced Bryan to be a guest blogger here. So, without further adieu, here is Bryan's writeup:

The issue was encountered with a 11.1.1.1 system being upgraded to 11.1.1.3.

When we attempted to upgrade 11.1.1.1 to 11.1.1.3 using the Oracle installer, the “Apply maintenance release” option was disabled. Click on the graphic below to see a full size copy of the dialog.

We determined the original installation was performed while logged into the Windows server as . Unfortunately, had left the company and thus the login was no longer available. As in many companies, getting IT to resurrect the login would’ve taken an act of Congress.

After some research, we found the initial installation places a small file, called oracle.products, in the users home directory (i.e. C:\Documents and Settings\). The Oracle installer apparently looks for this file and, if it is not found, the installer will not recognize that any Oracle products are installed and thus applying a maintenance release would not be possible. The simple solution was to copy this file from the home directory to the home directory for the user login we were using.

The lesson for all of us doing infrastructure work is to always perform installation steps using a permanent, non-expiring ID. When I set up an environment, I typically ask for a non-expiring ID that will be used for the installation and to run the EPM services. If you run the install using this ID, you would never run into this issue during subsequent upgrades/patches.

Thanks Bryan for sharing this information!

Since I will likely have to transition blogging systems after the Oracle acquisition, I decided to avoid the trouble and just begin using WordPress now. I intend to use this site for future posts regarding Oracle and Sun performance... and I will likely repost material as well.

My new Oracle/Sun performance blog is:

http://glennfawcett.wordpress.com

I already made a post about the value of predictable IO latency with Exadata V2. I hope you enjoy it and this new site.

Take care,
Glenn

Since I'm going through and relearning everything, I'm creating this page as an index page for all future posts. Yes, I could use a label and have blogger do it automatically...I'm just trying to make more work for myself.

• Administrative Services

Hi all, and welcome back to blogrotate. It’s been a busy week here at Pythian which reduced the amount of time I had for cruising the news, so this weeks edition will be a short one. Here’s a few of the stories that tweaked our interest this week.

Operating Systems

The Machine SID Duplication Myth is an article on the Microsoft Technet blog by Mark Russinovich. It goes through an in depth explanation of what SID’s are used for, and notes that Sysinternals has officially retired the NewSID utility as of Nov 03, 2009. This is of particular interest to anyone who created desktops and laptops via saved images as NewSID was a staple utility after the machine was imaged to ensure it did not conflict with other machines on the network.

Michael Larabel published some CentOS 5.4 vs. OpenSuSE 11.2 vs. Ubuntu 9.10 Benchmarks on the Phoronix site. I was suprised to see that CentOS beat the others on the majority of tests run, at least in part due to issues with the ext4 filesystem that both SuSE and Ubuntu use as their defaults.

Over at the Computerworld blog, Steven J. Vaughan-Nichols writes about 5 Reasons why Ubuntu 9.10 is better than Windows 7. I agree with most of what he says, but generally Linux is still not an easy conversion for a die hard Windows user. It sure did spark a huge amount of debate in the commentary.

Mandriva Linux 2010 is out. Check out the release information and feature set at the Mandriva blog site. I’ll have a closer look at this if I ever get the time.

Hardware

In the world of hard drives the trend has always been to bump up the amount of bits the drive can hold to combat the constant increase in data size. Another way of dealing with this could be deduplication of data which should reduce the amount of storage required for the same information. Could a hard drive dedupe data has more on this subject.

Security

Ryan Paul writes HTTPS, SSL attack vector discovered; fix is on the way. This vulnerability was discovered by Marsh Ray and Steve Dispensa from security company PhoneFactor but not publicized pending a fix. There is a temporary workaround from the OpenSSL team, hopefully it’ll be resolved quickly.

Not long after Windows 7 was released, John Leydon at The Register writes that Naked Win 7 still vulnerable to most viruses. He’s reporting on testing done by the Sophos security firm which showed that 7 out of 10 of the malware tested still managed to run in the default configuration. So even if you upgraded to Windows 7 you still need to run that anti virus.

Even Linux is not safe from security threats (nothing ever is IMHO). Bug in latest Linux gives untrusted users root access by Dan Goodin gives you the details. Patches for RedHat linux are already out, keep your system up to date to make sure you get the patch as soon as it’s available.

Virtualization

Red Hat takes on VMware with server virtualization solution by Ryan Paul discusses RedHat’s newest foray into the virtualization market with their solution called Enterprise Virtualization for Servers. This solution uses RedHat’s recently acquired KVM and is prominent in the recent RedHat Enterprise Linux 5.4 release.

So as not to be left out, Cisco, EMC, and VMware join hands and plunge into cloud with their new joint venture called Acadia. You can also read more about this in Cisco, EMC, VMware & Intel Form Acadia.

In the “I totally called it” department

I mentioned in a recent version of this blog that Microsoft was backing a Family Guy episode. I said at the time that I did not see how they could funny it up, apparently Microsoft could not see it either. Joe Fay gives us the skinny in Microsoft drops Family Guy like a hot deaf guy joke. Apparently the humour was not in keeping with the clean, family friendly image that Microsoft wants to convey. Seriously? I suggest someone at Microsoft watch any of the Seth McFarlane shows before signing on with him. I am guessing it was not a matter of foul language however, expletives are an occupational hazard when using Windows.

Til next time, keep your cache full and your swap empty.
Brad

If you’ve been to any of the Oracle BI Applications roadmap presentations, you may well have seen a slide that sets out “Oracle’s Integrated Analytic Solution”, which looks like this:

(from Oracle Business Intelligence Applications Roadmap, Oracle Open World 2009)

Now we all know about the ERP Analytics (Oracle BI Applications running against Oracle EBS, Peoplesoft and SAP sources) and the CRM Analytics (BI Applications against Siebel), and the EPM applications are the ex-Hyperion application such as Planning, Financial Management and Profitability Management. But what about the Industry Analytic applications: how do they work, do they share data and an architecture with the Oracle BI Applications, and what do they look like?

Unlike the regular Oracle BI Applications, which are considered horizontal applications as they are applicable to all different industries, the Oracle Vertical Analytic Applications are specific to particular industries such as energy, life sciences, automotive and insurance. The vertical that Oracle have got the most emphasis on at the moment though is financial services, following the purchase of a majority share in i-flex (now renamed Oracle Financial Services Software) and Oracle’s position now as one of the major suppliers of banking, capital and risk management software. Oracle’s aim over the past year has been to bring together the Reveleus banking BI and data warehousing software from i-flex, plus software they recently acquired through the subsequent purchase of Mantas (fraud and money-laundering prevention) and combine this with the Oracle BI Enterprise Edition platform into something now called the Oracle Financial Services Analytical Applications.

The Oracle Financial Services Analytical Applications, like EPM Suite and the BI Apps, consists of a number of modules, some of which are based on i-flex / Reveleus technology (Oracle Financial Services Funds Transfer Pricing, Profitability Management etc) but with one, Oracle Financial Services Profitability Analytics, based on OBIEE technology with an i-flex derived underlying relational data model.

Reveleus is the business intelligence framework developed by i-flex, and is based on their own front-end technology and what appears to be a Microsoft SQL Server and Analysis Services back-end. Similarly, the Mantras technology pre-dates the Oracle aquisition and again uses a mix of custom and non-Oracle technology. Going forward though, Oracle’s strategy around financial services analytic applications is to create a suite of products, based around Oracle BI EE and the Oracle Financial Services Analytical Applications Infrastructure. This framework sits within the usual storage – data sources – middleware – foundation – EPM workspace architecture, but using its own data model, and sits in the area of the architecture usually occupied by the BI Applications and EPM Suite.

Now this is where it gets interesting, as there are clear links between these vertical applications and the rest of the Oracle BI and EPM stack. Unlike the horizontal BI Applications, these vertical applications use their own industry-specific data model, with the financial services one called the “Oracle Financial Services Analytics Applications Data Model”. This is a relational data model that is used by a number of frameworks and applications to provide banking-specific analytics using shared dimensions, metadata and metrics. The application that we are interested in that uses this overall data model, and is based around OBIEE, is Oracle Financial Services Profitability Analytics, the screenshot for which is at the start of this article. Placed in their own architectural technology stack, you can see the familiar dashboards, reporting and alerts provided by OBIEE, with more complex and industry-specific calculations provided by ex i-flex technology.

Profitabiltiy Analytics uses an OBIEE semantic model that reports against summary data taken from the Oracle Financial Services Applications (ex i-flex) data model, in a similar way to the E-Business Suite materialized views that Daily Business Intelligence uses. The summaries used for Profitability Analytics are stored in the same schema as the Oracle Financial Services Applications data model, with Oracle BI Enterprise Edition reporting against just these in order to provide good query performance. There’s no DAC with this application, with aggregation scripts used instead to create the summaries used by OBIEE, and all of these need to be installed on an Oracle 9.2 or higher database. A bit of configuration is needed to define start and end dates and the instrument configuration, but as it’s designed solely to work against Oracle’s own banking data model it’s not quite as much work as setting the horizontal BI applications up.

The Profitability Analytics tables in the current release are a bit more limited than the horizontal ones, in that it’s not really designed to allow changes to historical data (which may be an appropriate restriction in banking applications), there’s no SCD2 support and a few early limitations in how the LTSs in the repository have been set up. But it’s based on OBIEE, uses the OBIEE dashboard, requests and iBots framework and there is some scope for customization, in terms of bringing in additional data and metrics from the Oracle Financial Services Applications data model rather than from “universal” or ERP/CRM sources that you can do with the BI Apps. It also uses the Market Segmentation feature of OBIEE to do market and customer analysis, and security, strangely, isn’t set up by default with the configuration manual goes through a couple of examples of using the BI Server Security Manager and Party security dimension in the semantic model.

So, the Financial Services Profitability Analytics part of the Oracle Vertical Analytical Applications looks a bit like the short-lived Fusion Analytics module, based on the DBI summaries but using OBIEE technology, before Oracle changed emphasis to the BI Applications after the CRM and ERP data models were combined. Whilst it’s extendable via the OBIEE semantic model and the limited scope for customization against the Oracle Financial Services Applications data model, it’s something that is banking-specific and something that a bank might deploy alongside the horizontal BI applications, which would in turn cover their generic applications such as GL, HR and Purchasing.

The applications from Reveleus and Mantras look interesting and cover even more narrow banking and risk-focused areas, the technology under them is a little unclear from the documents on the Oracle website (possibly because they are considered “batteries included” and based on Microsoft database and OLAP technology), and you can imagine that within Oracle there’s a big push to get Hyperion technology into this stack, particularly by extending and verticalizing existing application such as Planning and Profitability Management.

Still, it’s an interesting look into the vertical applications that Siebel, and now Oracle have produced using the OBIEE technology that we’re all using, and certainly if you were a bank, pharmaceuticals company, insurance company or whatever, there’d be quite a compelling argument to get Oracle BI across the whole enterprise, delivering the generic content using the BI Applications, industry-specific content using the Vertical Analytic Apps, Siebel covering the CRM side and other, home-built systems covered using standalone OBIEE together with ODI or OWB11gR2. Coupled with what’s happened in banking and financial services over the past couple of years, if you’re an Oracle salesman in the financial services industry there’s quite a bit of interesting stuff to talk to your customers about now.

Last Friday in the Sydney Oracle Meetup I talked about Oracle Flashback technology and how it helps to reduce downtime. The session generated great interest among the attendees, which led to interesting discussions and many questions about the subject.

Some of the question couldn’t be answered during the meeting so I’ve followed up on them and I’m posting the answers here since they may be of interest for many others.

Q: Is there a separate background process for writing flashback logs?

A: Yes. RVWR (Recovery Writer, a.k.a Flashback Writer) was introduced in Oracle 10g to write flashback data from the Flashback Buffer in the SGA to the flashback database logs on disk.

Q: Do I need to shutdown and mount the database to turn flashback on?

A: NO! This is a very common misconception. ALTER DATABASE FLASHBACK ON is an online operation.

To turn flashback on, all Oracle needs to do is to start saving the before-images of the database blocks before they are changed. This guarantees that the database can be flashbacked to the exact point-in-time when the flashback mode was turned on.

Q: What happens if RVWR cannot write to disk?

A: It depends on the context where the write error occurs:

• If there’s a Guaranteed Restore Point, the database crashes to ensure the restore point guarantee is not voided.
• If there isn’t a Guaranteed Restore Point and it’s a primary database, the Flashback Mode will be automatically turned off for the database, which will continued to operate normally.
• If there isn’t a Guaranteed Restore Point and it’s a standby database, the database will hang until the cause of the write failure is fixed.

Q: Is it possible to specify the size of the Flashback Buffer in the SGA?

A: Yes, but indirectly. The size of the Flashback Buffer is set to 2 * LOG_BUFFER.
For performance reasons, it’s recommended to set LOG_BUFFER to at least 8MB for
large databases running in Flashback Mode.

Q: Can RMAN be used to backup flashback logs?

A: No. Flashback Logs are not backed up. Even if the command BACKUP RECOVERY AREA is used to backup the contents of the FRA to tape only the following file types are backed up: full and incremental backup sets, control file autobackups, datafile copies, and archived redo logs.

Flashback Logs are considered to be transient files and cannot be backed up by RMAN. They are not needed for media recovery.

Q: When are the flashback logs deleted?

A: Flashback logs are managed by Oracle only. Oracle will try to keep as much Flashback logs as needed to satisfy the DB_FLASHBACK_RETENTION_TARGET parameter. However, if there’s space pressure in the Flash Recovery Area (FRA), flashback logs may be deleted to make room for other things, like backups and archived logs, for example.

• If the fast recovery area has enough space, then a flashback log is created whenever necessary to satisfy the flashback retention target.
• If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then a flashback log is reused.
• If the database must create a new flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
• If the fast recovery area is full, then an archived redo log that is reclaimable according to the FRA rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.
• No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. Thus, retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely.

Other than that flashback logs are deleted according to the below:

• When flashback mode is turned off all flashback logs are deleted ONLY if there’s no guaranteed restore points. If there’s at least one guaranteed restore point, no flashback logs are deleted.
• When the oldest guaranteed restore point is deleted and flashback mode is off, all flashback logs older than the second oldest guaranteed restore point are deleted. If flashback mode is on for the database OR the guaranteed restore point is not the oldest no flashback logs are deleted.

Q: How to list restore points in RMAN?

A: You can use either the RC_RESTORE_POINT view in the recovery catalog or the command
LIST RESTORE POINT [ALL|restore_point_name] in RMAN.

Q: After flashback’ing to a point-in-time before a RESETLOGS operation is it possible to flash forward to the incarnation after the RESETLOGS?

A: Yes, it’s perfectly possible.

Q: Can you see the progress of a FLASHBACK DATABASE operation?

A: Yes, you can. During a FLASHBACK DATABASE operation you can query V$SESSION_LONGOPS from another session to see the progress of the flashback.

The FLASHBACK DATABASE operation has two distinct phases: the actual flashback and the media recovery that happens afterwards to bring the database to a consistent state.

While the actual flashback is running you’ll see the following message in V$SESSION_LONGOPS:

Flashback Database: Flashback Data Applied : 238 out of 282 Megabytes done

During the media recovery, the following messages will be seen:

Media Recovery: Redo Applied : 263 out of 0 Megabytes done
Media Recovery: Average Apply Rate : 1164 out of 0 KB/sec done
Media Recovery: Last Applied Redo : 626540 out of 0 SCN+Time done
Media Recovery: Elapsed Time : 232 out of 0 Seconds done
Media Recovery: Active Time : 116 out of 0 Seconds done
Media Recovery: Active Apply Rate : 1859 out of 0 KB/sec done
Media Recovery: Maximum Apply Rate : 1859 out of 0 KB/sec done
Media Recovery: Log Files : 15 out of 0 Files done
Media Recovery: Apply Time per Log : 7 out of 0 Seconds done

Q: How should I set the database to improve Flashback performance?

A: Oracle’s recommendations are:

• Use a fast file system for your flash recovery area, preferably without operating system file caching. It is recommended to use a file system that avoids operating system file caching, such as ASM.
• Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.
• If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance
• For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.

All over the web I’d read that DTS packages could be stored on SQL Server 2005 64-bit, but not executed on this server. Workarounds I’ve seen range from creating SSIS packages with Execute DTS tasks, migrations to SSIS using the wizard or third party tools, and running the DTS Packages from a 32-bit server against the 64-bit target.

Recently (and much to my embarrassment after making that statement), a colleague demonstrated that this is not correct. DTSRun.exe can be found on SQL Server 2005 64-bit installations (although it might not be supported by Microsoft).

Upon investigating and testing on my own, I found that on the servers I checked, DTSrun.exe is located in: C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\.

I mocked up a simple DTS package that picks up a flat file, creates a table, and then imports the data into it with a transformation step. I saved it as a structured storage file, which I copied to the 64-bit server, generated a DTS Run line (F.Y.I. – DTSRunUI.exe is not located on these servers), then executed it from a command prompt on the 64-bit server. And it worked!

SSIS is a good platform with many noticeable improvements over DTS (also some new quirks), but if you need a workaround or just can’t justify a complete re-write of your DTS packages to your manager, this may be an option for you.

I tested some simple commands, (not the entire functionality) so I strongly recommend you test your packages before relying on this in a production environment.

Please note that DTSRun.exe is not included in SQL Server 2008 installations.

Yesterday Oracle released Soa Suite 10.1.3.5.1, the version which you can install on Weblogic 10.3.1 ( FMW11g version ). This is a full version so you don't early versions or extra patches to makes this work.

We need to download Weblogic 10.3.1 and Soa Suite 10.1.3.5.1

first step is to install Weblogic 10.3.1, I use C:\oracle\Soa10gWls as my wls middleware home folder

Now we can go to the Soa suite part, first we need to create a bpel, esb and wsm repository.

Extract the soa suite install zip and go to the rca folder located in ias_windows_x86_101351\Disk1\install\soa_schemas\irca

We need to set a database home for the jdbc driver.
set ORACLE_HOME=C:\oracle\product\11.1.0\db_1
We can use the jdk of the new weblogic install
set JAVA_HOME=C:\oracle\Soa10gWls\jdk160_11

Now we can start irca.bat

After a succesfull install of the repository we can start the soa suite installer in this folder ias_windows_x86_101351\Disk1

Very important the destination path must be in a folder of the just created wls middleware home so I use C:\oracle\Soa10gWls\soa10g


As weblogic home location use C:\oracle\Soa10gWls\wlserver_10.3

We are ready with the install

Now we to start script for wsm go to the C:\oracle\Soa10gWls\soa10g\config\ folder and start
configureSOA.bat

Last step is to create a Soa domain just like Soa Suite 11g and select the Soa Suite 10.1.3.5.1 option


Provide the orabpel and oraesb schema passwords.


Start the admin server and go to http://localhost:7001/console where we can take a look at the server. The soa suite server is called soa10g_server1


When we want to start the soa server we need to go the soa domain bin folder
C:\oracle\Soa10gWls\user_projects\domains\soa1013_domain\bin
and use "startManagedWebLogic.cmd soa10g_server1" to start the server.

This are the default installation url's of the Soa Suite applications
http://localhost:9700/esb
http://localhost:9700/BPELConsole
http://localhost:9700/ccore

And we need to use soaadmin as username to log in and use weblogic1 as password.

Without a doubt acquiring a new skill is hard.  It takes time, practice and a willingness to fail.  I do not want to suggest that you should only attempt those things that you already know or do well.  That is just silly.

You should challenge yourself every day to try new things and learn new skills, just remember that some skills might not be within reach for you.    You might gain a basic level of competence after a lot of hard work but for some things you will never be outstanding.

This is a universal truth [always remember you are unique, just like everyone else].

So what happens when you are not gaining a job critical competency?  When you find yourself unable to get to an appropriate level of competence in a reasonable amount of time (as evidenced by lackluster performance feedback)  you should probably take a step back and regroup moment.

I’d like to say again, that if you do not have a good job fit you will not be top Talent.

Putting it another way, when you have a bad job fit you are probably getting labeled as a “poor performer”.  If you find yourself in a second performance discussion where the topic is does not meet expectations* I think it’s time to take a serious look at the question of job fit.

Instead of going down the mental path of inadequacy and low self esteem, take a moment and find your strengths (you can get help doing this for approx $14, well worth the investment) and then have a hard look at your job.  Is the job you are doing playing to your strengths (I’m going to take a wild guess that there might be a mismatch)?

Now comes the brave part.

Instead of just having yet another discussion with your boss about your lack of competence, do the adult thing and work with her to see if there is a better way to define your job to play to your strengths.

If this is not possible, you probably need to start thinking about getting a different role somewhere else.

If you don’t take action, you are probably going to live forever in the bottom left box of the Talent 9-box, and I promise you that is not the zip code in which you want to reside.

____________

*Also be on the look out for does not meet feedback hidden under a meets expectations rating, this happens more than I care to acknowledge.

John Paredes recently sent over a review copy of a book he’s just had published, entited “The Multidimensional Modeling Toolkit: Making Your Business Intelligence Applications Smart with Oracle OLAP” (ISBN: 978-0-9817753-0-2, OLAP World Press). Interestingly, it’s a book written about Oracle OLAP (which is itself rare) but unlike the recent Oracle Press book on OLAP and Essbase, it looks at it from the OLAP DML perspective, based on Oracle OLAP 10gR2.

As such it’s not an introduction to basic Oracle OLAP via the Analytic Workspace Manager, it’s more something that could have been written in the days of Express and takes a more low-level, philosophical approach to OLAP with a particular emphasis on multi-dimensional concepts and the multi-dimensional building blocks that make up an Analytic Workspace. Whilst illustrated using recent front-end tools such as the Data Viewer within AWM, it eschews the approach taken by Oracle to make OLAP data behave as much like relational data as possible, an instead takes you straight into OLAP DML and the building blocks of a mult-dimensional database. The book does start off with instructions on how to build a cube using AWM, but even in this chapter its not long before we’re into the OLAP Worksheet (the OLAP DML front-end that’s hidden in AWM) where we’re using OLAP DML to add members to dimensions and query the metadata.

As such, it’s a brilliant book for anyone who’s used Analytic Workspace Manager and tools like Discoverer for OLAP and even OBIEE, and has wondered just how the engine within Oracle OLAP actually works. I’ve waited years for a book on OLAP DML and Jon Paredes has produced something that must surely be a labour of love, particularly as the market for Oracle OLAP books in total must be pretty small let alone one that focuses on OLAP DML, something that Oracle are in fact de-emphasizing with the move towards the OLAP API and cube organized materialized views in Oracle OLAP 11g.

One word of warning that I would give though is that with this move towards SQL and PL/SQL-based cube definitions in Oracle 11g, it’s now impossible to create OLAP objects using OLAP DML that are then registered in the OLAP metadata used increasingly by Oracle’s development and query tools, as this metadata has from 11g moved out of the analytic workspace itself and into the standard Oracle relational data dictionary tools. You can still use this manual approach though if working with OLAP data through the OLAP_TABLE function, and I know I’ll be working through John’s book over the next few months to fill out my knowledge of OLAP DML.

So, overall, a great book and it’s great to see something that’s been produced by someone for their love of the technology, and based on Oracle OLAP DML programming which is a bit of a specialist subject these days (I doubt most Oracle developers would even have heard of it). If you’re a developer using Oracle OLAP, particularly if you’ve migrated from another technology such as Holos or TM/1 and are wondering how to get under the controls hidden by Analytic Workspace Manager, or if you’re looking to use Oracle OLAP for high-end statistical or financial analysis, this is a very interesting read and I’d encourage you to get hold of a copy. If you’ve ever wondered just how to do an allocation, a forecast or create a calculation using more than two operators and been frustrated by the simplicity of Analytic Workspace Manager, this book may turn out to be an excellent investment.

… a few things about the use of index space to suprise your friends and amaze your colleagues: If you use “bigfile” tablespaces for your tables this can result in some indexes becoming more space-efficient than they would be otherwise. Creating tables in tablespaces built from multiple datafiles may cause some of their indexes to be less space-efficient [...]

This is the 168th edition of Log Buffer, the weekly review of database blogs. Let’s give the wheel a spin and see who comes first . . . 

MySQL

Brian “Krow” Aker has something to say about Drizzle, InfiniDB, and column-oriented storage: “I have been asked a number of times ‘do you think there is a need for a column oriented database in the open source world?’ The answer has been yes!  . . .  I was very happy to see Calpont do their release of Infinidb last week.”

Vadim of the MySQL Performance Blog said, “As Calpont announced availability of InfiniDB I surely couldn’t miss a chance to compare it with previously tested databases in the same environment.” And he didn’t, as shows his post Air traffic queries in InfiniDB: early alpha. Bob Dempsey and Jim Tommaney of InfiniDB are in on the discussion.

Back to Drizzle for a moment, and Jay Pipes’ item, The Great Escape. “This week, I am working on putting together test cases which validate the Drizzle transaction log’s handling of BLOB columns.  . . .  I ran into an interesting set of problems and am wondering how to go about handling them. Perhaps the LazyWeb will have some solutions.  . . .  The problem, in short, is inconsistency in the way that the NUL character is escaped (or not escaped) in both the MySQL/Drizzle protocol and the MySQL/Drizzle client tools.”

Baron Schwartz has been catching erroneous queries, without MySQL proxy, having been inspired by Chris Calender’s post, Capturing Erroneous Queries with MySQL Proxy.

Nick Goodman promises instant relief from slow MySQL reporting queries using dynamoDB. And no gooey applicator!

Robert Hodges of the Scale-Out Blog looks at replicating from MySQL to Drizzle and beyond. “I am . . . delighted that Marcus Erikkson has published a patch to Tungsten that allows replication from MySQL to Drizzle. He’s also working on implementing Drizzle-to-Drizzle support, which will be very exciting.  . . .  This brings up a question–what about replicating from MySQL to PostgreSQL? What about other databases?”

And back to xaprb, where Baron confesses, I’m a Postgres user, as it turns out.

PostgreSQL

Bruce Momjian has published a new security talk, Securing PostgreSQL From External Attack.

Andrew Dunstan delves into recursion in Recursion, n. See recursion. “Never,” he says, “underestimate the usefulness of silly demos (this is written for a talk next week) to teach things worth knowing.”

Bernd Helmle shares a walk-through of cloning Slony nodes. “The new stable branch 2.0 of Slony-I is out for a while now. Time to blog about one of my favorite new features there, cloning an existing node without doing an initial SUBSCRIBE command.”

SQL Server

It was PASS Summit this week. On Home of the Scary DBA, Grant Fritchey covers the event with several good posts, including PASS Summit 2009 Key Note 3. (Grant is also Geek of the Week! Congratulations, Grant! I guess. Quote: “I think most DBA’s have adminhood thrust upon them. I think the ‘accidental’ DBA is the most prevalent path into becoming a DBA. I became a full time Admin by opening my mouth once too often.”)

Aaron Bertrand also has his summary Blogging from the PASS Keynote: 2009-11-03. (Grant and Aaron both have to specify which keynote they mean, because there’s more than one keynote. This, I guess, is “keynote redundancy”, but I still think PASS needs to normalize.)

Greg Low announces the launch at the PASS Summit of a new book, SQL Server MVP Deep Dives. “This is no ordinary book,” he writes. “Paul Nielsen took up Steve Ballmer’s challenge at a recent MVP summit to do something notable to give back to the community. He organised a large group of SQL Server MVPs to create a unique book and worked with Manning to get it published. The money made on the book was to go directly to a charity and the charity chosen was WarChild.”

Ben Nevarez asks, Are You Using Scalable Shared Databases? “Did you know that you can share read-only databases between several instances of SQL Server?  . . .  Scalable Shared Databases is a very interesting SQL Server feature that many of us seem to almost have forgotten about . . . ”

Here’s Roman Rehak reporting an issue with restoring 2000 backups on 2008. He writes, “Recently we’ve been experiencing a lot of headaches with SQL Server 2008 crashing while restoring a backup taken on a SQL Server 2000 production server. The crash resulted in a stack dump but SQL Server would continue running, although less stable, and sooner or later needed a reboot.”

Meanwhile, Adam Machanic reports on SQL Server 2008: lock escalation, INSERTs, and a potential bug. Adam says, “Lock escalation is a funny thing. I’ve found myself on numerous occasions waging war against its concurrency-sapping existence, and rarely have I found myself wishing that it would work more aggressively. But there is a time and place for everything, and yesterday I discovered that a major change has occurred with regard to lock escalation in SQL Server 2008.”

Oracle

Mohammed Mawla on the Pythian Blog bridges the gap with his item on running the same query against multiple SQL Server AND Oracle instances.

Surachart Opun shares his HOWTO on using DUPLICATE without a connection to target database: “ . . . that’s a 11gR2 Feature. DUPLICATE can be performed without connecting to a target database. This requires connecting to a catalog and auxiliary database.”

Here’s another HOWTO, this one from the great grandson of Husnu Sensoy: How to Install Oracle 11g Release 2 on OEL 5.4 on VirtualBox: Installing Grid Infrastructure. He begins, “In Oracle 11g Release 2 you will find that things have changed even for single instance database installation. I will try to illustrate in this series of posts how to install a single instance Oracle 11g Release 2 database to your Linux machines.”

But let’s step back a bit. Ronny Egners says, Oracle on linux – yes of course – but what linux?. “There is a discussion from December 2008 what Linux (SLES vs. Red vHat vs. Oracle Enterprise Linux) to use for running oracle on Linux by Yann Neuhaus.  . . .  After nearly one year i wanted to catch up the article and check if the pros and cons are still valid or if there changed anything.”

Chen Shapira offers The Senile DBA Guide to Troubleshooting Sudden Growth in Redo Generation, which begins, “I just troubleshooted a server where the amounts of redo generated suddenly exploded to the point of running out of disk space.  . . .  The problem was found and the storage manager pacified, I decided to save the queries I used.  . . .  It was very embarrassing to discover that I actually have 4 similar but not identical scripts . . .  Now I have 5.”

Embarrassing, Chen! But do you have guilty feelings like Martin Widlake does? He makes a guilty confession. The sin? “I use the Buffer Cache Hit Ratio.”

Last, Tyler Muth introduces Logger, A PL/SQL Logging and Debugging Utility.

That is all for now. Please let’s hear your favourite database blogs in the comments. Until next time!

In other IMS news, Desire2Learn has announced that they are the first LMS provider to support BLTI, a specification designed to support plugging third-party tools into an LMS. According to the press release, here’s what’s included:

• Management interfaces to define integrations to external learning tools (Tool Providers) and to create links
• New Quicklink type to allow links to Tool Providers to be easily incorporated throughout Desire2Learn courses
• Links to external learning tools that can send user, organization, and course context information for a personalized experience in the tool; single-sign-on to learning tools is supported through an oAuth framework using a key/secret shared between the external learning tool and the configured link in Learning Environment
• Management tools to configure mapping from Desire2Learn roles to IMS roles, and from Desire2Learn org unit types to IMS context types

A couple of other platforms are following D2L on this road.

• Sakai has a pre-alpha version which is publicly available for testing, and which has been proposed for eventual inclusion in the next Sakai release (2.7).
• A prototype third-party building block is available for Blackboard through OSCELOT.

This is just the work that’s visible to the public. As is often the case with these things, (a) more work is going on behind the scenes, but (b) you should demand to see working code and a commitment to a supported release within a defined time frame before taking any statements of support by a vendor or project too seriously.

Kudos to Desire2Learn for taking the lead here.

Related posts:

1. Jenzibar Planning to Support IMS Learning Information Services Standard
2. SunGard Announces Support for IMS Learning Information Services
3. LETSI: HR-XML Consortium Seeks Cross-Domain Interoperability

© michael.feldstein for e-Literate, 2009. | Permalink | One comment | Add to del.icio.us
Post tags: Blackboard-Inc., Desire2Learn, IMS, LTI, Sakai

User reported Application servers were unable to connect to sqlnet listener service.
Listener was running, service was READY, but there were 0 handlers for the service.

Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1560)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yyy)(PORT=1560)))

Service "TEST" has 1 instance(s).
Instance "zzz", status READY, has 0 handler(s) for this service...

According to Metalink note 885431.1 we can avoid this issue if our listeners use only one end-point. We need to listen on the VIP address, but don't need to listen on the HOST address.

E.g change

LISTENER_XXX =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myvip)(PORT = 1560)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1560)(IP = FIRST))
)
)

To

LISTENER_XXX =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myvip)(PORT = 1562)(IP = FIRST))
)
)

It’s been years and years since I worked regularly with SQL*Loader-based feeds.

There are loads of tips and tricks regarding formats, encodings, character sets, etc.
I had forgotten nearly everything that I could ever have claimed to know in this area, all aged out of my personal buffer cache, at best distant memories, very distant.

However, I’ve recently had to resurrect some of these distant memories to feed some data via External Tables.

I thought it would be worthwhile to do an incoherent brain dump on some of the particular issues for future reference.

This article is my no means comprehensive and will just touch on some of the considerations specific to what I was doing.

The starting place for documentation on SQL*Loader is the Oracle Database Utilies Manual and similarly for External Tables.

The thing about external tables is that they can use the ORACLE_LOADER or the ORACLE_DATAPUMP access drivers.

My circumstances involved the ORACLE_LOADER driver.

As the names suggest, ORACLE_LOADER is related to SQL*LOADER. In fact, you can use the EXTERNAL_TABLE=GENERATE_ONLY parameter with SQL*LOADER to generate the syntax for your EXTERNAL TABLE.

I think things like this are brilliant but I never use them. I suppose I’m old skool. Neat features that auto generate code mean that a) I don’t necessarily need to understand what’s going on and b) skip the line-by-line sanity checks that I still believe in.

I work in an environment where it’s not so easy to get access to servers to ftp and view files, etc.

As a result, my approach would be to work with SQL*Loader early on and then graduate to external files nearer the time. However, I’m going to skip all that, fast forward past the SQL*Loader bit and move on to the issues.

So the main considerations for loading up data?
- Encoding / Character sets
- Delimiters – field and line
- File Transfer

(I’m going to ignore Endianness for the sake of “clarity”. But it can be a factor. See Byte Ordering for futher information.)

In my experience, when you get errors whilst loading, it’s due to one or more of the reasons above.
More than one?
Sure.

For example, the combination of file transfer mechanism and delimiters.
Ever wondered why there’s confusion over whether you should transfer a file as text (or ascii), as binary? What’s the difference?

As ever, it depends.
- SFTP doesn’t have a text mode, all transfers are binary.
- FTP lets you choose between ascii (default) and binary.
- What about WinSCP and similar tools with their automatic mode?

Automatic modes usually work off the file extension and would pick a text transfer for HTML, TXT, PHP, XML, etc and binary otherwise.

And the differences in mode?

For Text mode, there are two basic methods – either the tool is responsible for doing some conversion to the format supported by the destination or, more commonly, the client converts to a canonical format and the service then does a further conversion to its own format if necessary.

With Binary mode, the raw bytes are transferred as is, i.e. the file is transferred in its original form.

So, the main significance of this related to field and line delimiters – things like tabs and line feed characters are different between platforms, different between Unix and Windows for example.

This was particularly relevant for my file export from SQL Server to Oracle on Linux.

In Windows, a new line is often represented by two characters – one carriage return and one line feed.
In Unix, a new line is normally just a line feed.

Sometimes you see “^M” characters on *nix. What’s this about?
This represents the carriage return part of the newline for Windows as described above.
If you need to, you can get rid of that using dos2unix.

So, in the ACCESS PARAMETERS subsection, if you use the “RECORDS DELIMITED BY NEWLINE” syntax in your external table definiton, what does that mean?

The NEWLINE keyword uses the newline format for your platform – so just a line feed in the case of *nix.

For my process, I decided that I would just go with the format as extracted from SQL Server – a 50:50 decision that there’s no point on expanding on.

So, in my situation, the syntax for an external table should not use the NEWLINE keyword because the format was Windows new lines, the syntax for which can be:

RECORDS DELIMITED BY '\r\n'

Here’s a situation. Things have been going fine in DEV,etc and eventually go to PREPRODUCTION and there’s a problem.

The feeds don’t work. The external table loader isn’t finding the right delimiters.

The question really is how can you tell what characters are in your file?
So, there’s no point looking at the file prior to transfer in case the technological clue (or the person in a manual process) changes the file on transfer. We need to look at it in the destination directory on the destination server.

And what’s the best way to do that?

On a Unix/Linux, a useful command is the od command which dumps files in octal and other formats. For example,

od -c <filename>

which gives ascii characters or backslash escapes.
There’s a lot of output from this command but you don’t need to do the whole file, e.g.

head -2 <filename> | od -x | more

If the file suddenly doesn’t match the expected format, what’s the sort of errors you might get?
Well, if it should have transfered in binary but it was done in text by error, you might get:

KUP-04020: found record longer than buffer size supported

i.e. the lines are running into each other
or under slightly different circumstances

KUP-04023: field start is after end of record

etc.

That’s about it on transfers and delimiters.

What’s there to say about encoding and character sets.
Using the od command above we can see the hex codes to double check the encoding is as expected.
Other that that, in our external table definition we can specify the character set in the ACCESS PARAMETERS section using the CHARACTERSET keyword, e.g.

CREATE TABLE <my_tablename>
(<my_columns>)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY <my_directory>
     ACCESS PARAMETERS
       ( RECORDS DELIMITED BY '\r\n'
       CHARACTERSET AL16UTF16
       FIELDS TERMINATED BY '\t'
       MISSING FIELD VALUES ARE NULL  )
     LOCATION (<my_directory>:'<my_filename>')
  )
REJECT LIMIT 0;

and then the expected characterset conversion will take place.
Note that SQLServer tends to use USC2 as its unicode characterset and UCS2 is a subset of AL16UTF16.

It’s Friday, it’s 5 o’clock, I’ve lost focus. I’m done.

Today, Bimal pinged me about a performance issue with Configurator in Order Entry. He told me that the page CZInitialize.jsp was taking 7 minutes to appear. In a different instance on the same server it was taking 30 seconds. I asked him whether he had collected statistics for ONT. He said he had collected for ONT and APPLSYS.
I searched in Metalink with keywords Configurator performance Order Entry and came across Note 130511.1 which gives this suggestion:
The first step in troubleshooting performance problems is to ensure that the customer has recently gathered statistics and that it was done correctly. The following schemas may impact performance in the OM product suite: ONT, WSH, QP, INV, AK, MRP, HZ, CZ, APPLSYS.
I told Bimal about this and he executed the following commands:
exec fnd_stats.gather_schema_statistics('ONT') ;exec fnd_stats.gather_schema_statistics('WSH') ;exec fnd_stats.gather_schema_statistics('QP') ;exec fnd_stats.gather_schema_statistics('INV') ;exec fnd_stats.gather_schema_statistics('AK') ;exec fnd_stats.gather_schema_statistics('MRP') ;exec fnd_stats.gather_schema_statistics('HZ') ;exec fnd_stats.gather_schema_statistics('CZ') ;exec fnd_stats.gather_schema_statistics('APPLSYS') ;
The issue was resolved immediately. Always check for statistics as a cause for performance problems in a CBO world.

As many of you may know, I’m a big fan of collaboration. I think we all have enough challenges building solutions for our customers, that we all benefit from any kind of collaboration. One such collaboration is the WAAI project. The WAAI acronym is formed by using the first letter of each participating company, being: Whitehorses, Amis, Approach Alliance and – of course – IT-eye.

The way we work is quite simple: every quarter we decide upon what questions we want to have answered. As you can imagine, SOA Suite 11g is quite prominent there. Each and every question has to be answered by delivering a workshop, white paper or presentation. These are collected and presented to an audience consisting of people from the participants. That way we not only build the knowledge, but spread it within our companies as well. And, all of us can use that material to train others, both inside and outside of the companies.

Usually the results are qualitatively really good. Some are extremely good. Such a one has just been posted on OTN, which is a nice feat anyway. You can find the article Jumpstart for OSB development here. We look forward to share a lot more results in the future.

Share and Enjoy:

Permalink | No comments | del.icio.us | Technorati | digg | dzone | StumbleUpon | Software development

I'll be joined by Alex Petrushko from our partner Identigral to talk about how Oracle Virtual Directory can improve your identity management implementation. Alex will be speaking about how a large telco provider used OVD to reduce time it takes... mark.wilcox

I recently did a podcast for Oracle and mentioned the term Manageability By Design (MBD) which I had co-invented with my friend Ravi Kulkarni from IBM. Here is our definition: “An IT System is Manageable By Design if it facilitates the activities and purposes of IT Operations, IT Service Management (ITSM), and Information Security Management.” An IT [...]