Monday, March 26, 2007

Application Express 3.0

We have eagerly been waiting for the release of APEX 3.0 , Last week, APEX 3.0 was made available for download. We use APEX extensively for internal application development and are porting many oracle forms applications that were internally developed to APEX. The users love APEX, the IT people love APEX, I as a DBA love APEX. We were looking forward to the PDF printing most of all, the ability to build PDF reports in APEX was going to be a huge feather in the cap for oracle and there was serious talk about having a vendor port a major application from a Delphi fat client to APEX, the vendor was on board, everything seemed to line up.

Then we installed APEX 3.0, the upgrade and installation on our test environment went perfectly, no problems at all. We start to read the documentation about the PDF printing, and we realize. We need to install XML/BI publisher to take advantage of the robust PDF printing that was all the rage. Well, that burnt us. Yes we can install the FOP stuff and do really basic reporting, but we had hoped the PDF generation was part of APEX 3.0.

We have had XML/BI publisher on the radar for awhile, knowing we will have to go to it with our Oracle E-Business suite upgrade later this year. I guess I had upgrade our evaluation copy to the newest version and see what has changed and try and figure out some business cases for spending the C$45,872 per CPU for XML/BI publisher.


We still love APEX 3.0, wonderful product, if your not using it, you should figure out a reason to.



Thursday, March 22, 2007

Oracle sues SAP

Interesting


http://www.theregister.co.uk/2007/03/22/oracle_sues_sap/


I had seen some rumours floating around that Oracle was going to try and take over parts of SAP, guess the rumours were wrong, but some legal work was brewing. Makes you wonder who stated the rumours to begin with.



Monday, March 12, 2007

Ramblings

I am bored, I am sitting at my desk staring at a very slowly moving tail -f on an rman log copying a production database to test, 52 gig of data, the fun never ends here. It is almost 8pm and I have been here since 7am this morning, let the ramblings begin.

I am an avid follower of Jonathan Lewis and his articles on the oracle optimizer. I have his book "Cost-Based Oracle Fundamentals", and I have actually managed to read the entire thing from cover to cover. I can guarantee to you that most of it went straight in, and straight out leaving little behind. I hope at least that enough of it remains behind to have a positive effect at a later date.

I had an actual case to use Tom Kyte and Jonathan Lewis blog entries on ordering a query, showing a developer he can't rely on the order in a table, because there is no order in a normal heap table. Didn't take much, simply forwarded him the links and let him try to find a way to prove them wrong. I haven't heard back from him.

We have 2 ISV's, lets call the Bob and Doug that have really been causing me grief lately, these are small shops that unfortunately have developed two systems that have become integral to our production. It is amazing, these two companies are located within a few kilometers of each other, they have no idea of each other's existence, but they cause me the same troubles. The troubles are always the same, no apparent in house testing of patches or upgrades, they appear to be under the impression that is what our IT staff is for - testing the ISV's code. Bob is database happy, they keep asking for more and more databases on our side. In our environment (they VPN in) they have a production, a test, a dev, and a QA instance for each province we operate in - total of 12 instances each around 50 gig in size. Now, that doesn't seem like much, but we do absolutely no development internally - none at all. These instances are here basically because they don't have the server space available to have what they think they need so they burden us with the responsibility of keeping the databases backed up and in good working order. The copy I mentioned at the opening is being done on their behalf. We pay them support, yet we maintain their support environment.

Doug on the other hand is amazingly skilled at stalling problem fixes long enough so that the users simply forget and develop work around. When Doug does release patches or upgrades, something always, consistently goes wrong, never during our testing of course. They give us a list of what they changed, we test that, and do a general test of everything else, this particular example is year end stuff. In October they release an update that worked pretty well, they only had to release the update to us for testing 4 times which is a new minimal record, with the maximum being 21 times. We test, users signed off and away we went. January comes along, users do their month end and everything works great. February rolls in and we are doing an internal audit between Doug's system and our financial system, and the auditors notice a rather minor $60K variance. Tracking it back, turns out that in the update in October the developers at Doug's company slightly modified a view that is only used at month/year end, "for performance purposes" and never told us about it. Their solution to the performance problems was removing a rather important table from the query which tracked and accounted for user manual changes in the data.

The user that sent the data should have read the reports and caught it early, so Doug made sure the fault landed solely on the users shoulder. So, the users had to make two correcting entries in the GL. Luckily the discrepancy was small and we didn't have to change our year end results. We are still waiting on Doug to give us a document on any changes necessary to their application for DST.

We are hiring an oracle applications support person and 2 IBM Lotus Notes (shudder) developers. I haven't quite figured out where the management plans to seat them, our cube farm is pretty packed together with us each only getting about a 9 foot square of space. Maybe they think we don't need that easy access to the fire escape, they can cram one in there. What they plan to do with the other two, I do not know. Possibly stack us up, lay some flooring across the tops of the cubes and put the cubes two high. We do have a very tall ceiling.

As for my "shudder" about IBM Notes, I have no bad feelings toward the developers that use Notes, I just hate IBM Lotus Notes, it is simply one of the worst programs ever created. We have the newest version of it (7x) and it still sucks. The only time the IBM team that develops Lotus Notes stops building something that sucks is when they start to build vacuum cleaners.

Oh boy, we need to work on the I/O on this test system, I swear there are gerbils in that server running back and forth with some floppies in their mouths transferring the data between disks. 52% done.

I managed to get management sign off on upgrading an oracle 7 database to oracle 10gR2. Apparently the company that supports the application uses 10gR2 internally, even though the majority of their customers are still on oracle 7. The application is 100% web based using some web language I can't remember so the upgrade is apparently really easy and they are going to supply us with the necessary scripts. That will only leave us one oracle 7 database in production.

uggg... I have to type slower, the RMAN log hasn't moved in minutes.

I have been keeping up with what was happening at Hotsos this year by reading Doug Burn's blog. His house mate of the month is amusing and his technical knowledge and writing style are well above average. I came across Don Burleson's personal blog, I follow the forum he hosts. Well, lets just say that starts a whole new chapter on that fellow for me. I know he needs to plug his and his fellow Rampant press author's books, but come on :).

I see my RSS reader is showing me that David Aldridge has posted again, finally after a very long time.

The Conversion of '07 continues later this week, this is the final test before we have to do it in production. I will write a note or two on how it goes.

Well, enough rambling for now.








Sunday, March 11, 2007

DST over

Well, the time is passed, no problems related to DST have cropped up. We all breathed a sigh of relief. Some frantic last minute patches were put in on our large JSP application, when I say last minute, I mean just after midnight this morning, a few hours before the time switch.

The only major casualty, which is out of our hands, is our cell phone provider seems to have had some issues. All of the cell phones switch just fine, but none of the blackberries did. Oh well, manual switch of the time and good to go.

DST is over...


for now.



Thursday, March 08, 2007

OEM 10gR3

For those of you that remember, about a year ago, we installed and got OEM 10gV2 installed and running, and I was hopeful. Well, about the only thing OEM was used for was downtime reporting by a manager. The occasional email from the system when something came down, but not much more. No new agents installed on new servers, nothing kept up to date, basically OEM 10gV2 was a dismal failure.


Well, we have decided to upgrade to OEM 10gV3 and take another kick at the can and see if we can get OEM configured the way it should be and use it the way it should be. Our big push in this came from our Oracle Technical Sales representative. He came by (for free) and learned our environment over 2 days and then he presented some very compelling reasons for using OEM during a 7 hour presentation. Every single one of the reasons was expensive, but he got the managers convinced.

We will be upgrading (or reinstalling) to V3 by the end of March. Our sales rep gave us a 60 day free trial of all of the OEM packs on all of our servers to get me hooked, as well as 5 full days of the technical sales rep here helping out configuring and the proper way to use it. For the estimated $900K bill (before discount) to have the OEM packs on every database and the non-oracle database servers monitored as well, and our SQL Server databases plugged in too. Oracle is willing to spend some time.

We are also looking at purchasing Oracle Fusion Middleware as our SOA solution, so later this year, oracle is going to make some money on us.

I am now off to a presentation where I am going to try my darndest to convince management that we really and truly need to upgrade our oracle 7 and oracle 8 production databases.



DST... Ready?

Well,

We appear to be ready for the big bad early DST. Yesterday we rolled every single one of our test servers forward and waited for the OS to do the switch. No databases came down (yeah!), unfortunately some of our vendor supplied patches for our large JSP based application seemed to have failed badly as the application simply refused to allow data to be entered "PO create date can not be past PO update date" or something like that.

Now, for the databases where test and production are on the same server - well, that is going to be a "fingers crossed" type of fix.There will still be a large number of IT people in on the morning of the 11th for the old 'just in case'.



Saturday, March 03, 2007

Insert into multiple tables from a single query

A friend who does not blog wrote this up for his co-workers, it is straight forward but useful. Enjoy.

A few days ago someone asked if it was possible in an oracle DB to insert into multiple different tables from a single query. I said "Yes of course", they asked "So, How?", I of course said "RTFM". Well, here it is a little easier to read than in "The Fine Manual"

This works all the way back to Oracle 8 so feel free to test it out. But, since it does drop objects, please do it in a test location. Personally I recommend everybody download and install oracle XE ( http://www.oracle.com/technology/software/products/database/xe/htdocs/102xewinsoft.html ) on your PC, gives you a nice safe place to work, play and learn, and as an added bonus Oracle XE comes with Application Express (APEX) already installed and ready to go. Now that I said that, I don't support PC's so who knows what it will change on the configuration for on your PC. Do so at your own risk.


Simply creating some test tables and a sequence for later use in this example.

SQL> CREATE TABLE BASETABLE (BASEID NUMBER PRIMARY KEY,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.03

SQL> CREATE TABLE DEST1 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.01

SQL> CREATE TABLE DEST2 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.03

SQL> CREATE TABLE DEST3 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.06

SQL> CREATE TABLE DEST4 (DESTID NUMBER PRIMARY KEY,BASEID NUMBER UNIQUE,BASEDATA VARCHAR2(30));

Table created.

Elapsed: 00:00:00.03

SQL>

SQL>

SQL> CREATE SEQUENCE DESTID_SEQ;

Sequence created.

Elapsed: 00:00:00.00

SQL>

Insert some data into the base table for use later

SQL> INSERT INTO BASETABLE SELECT ROWNUM*-1,DBMS_RANDOM.STRING('A',30) FROM DUAL CONNECT BY LEVEL <=500;

500 rows created.

Elapsed: 00:00:00.09

SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00

Now the actual insert, you can see the WHEN and ELSE clause of the INSERT statement. You can have as many of those as you want, each inserting different combination of columns for the VALUES section. In this case, I am using a sequence to satisfy the primary key of the DESTx table and then the two column names from the select clause at the end.

SQL>

SQL> INSERT ALL

2 WHEN BASEID=-1 THEN INTO DEST1 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)

3 WHEN BASEID=-10 THEN INTO DEST2 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)

4 WHEN BASEID IN (-100,-200,-300,-400) THEN INTO DEST3 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)

5 ELSE INTO DEST4 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)

6 SELECT BASEID,BASEDATA FROM BASETABLE ORDER BY BASEID DESC;

500 rows created.

Elapsed: 00:00:00.01

SQL> COMMIT;

Commit complete.

Elapsed: 00:00:00.00


Now to show what happened. From the following query you can see that the BASEID of -1 was inserted and the DESTID was the very first record in the insert as shown by the sequence value of 1.

This following data was inserted based on the

WHEN BASEID=-1 THEN INTO DEST1 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)line in the insert statement.

SQL> SELECT * FROM DEST1;

DESTID BASEID BASEDATA

---------- ---------- ------------------------------

1 -1 uzvIPoJevGslWNzcsEULVsOIHrWtkA

Elapsed: 00:00:00.00



From the following query you can see that the BASEID of -10 was inserted, and was the 10th line in the select query return result. This was inserted based on the line

WHEN BASEID=-10 THEN INTO DEST2 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)in the insert statement.

SQL> SELECT * FROM DEST2;

DESTID BASEID BASEDATA

---------- ---------- ------------------------------

10 -10 AzRwrjLpzvxtacxBOitYhGDGDuKmaU

Elapsed: 00:00:00.01


From the following query you can see that the BASEIDs of -100,-200,-300 and -400 were inserted.This was inserted based on the

line WHEN BASEID IN (-100,-200,-300,-400) THEN INTO DEST3 VALUES (DESTID_SEQ.NEXTVAL, BASEID,BASEDATA)in the insert statement.


SQL> SELECT * FROM DEST3;

DESTID BASEID BASEDATA

---------- ---------- ------------------------------

100 -100 uJixIEqFTeZEBDOCPYkJgyipInuTdt

200 -200 ikmTNgdjGTjkINEGbxEFifWAetPBMt

300 -300 gKcFyianMOtGzdJzVlkjqaLPiwBkic

400 -400 prucyUxTqhPhUTzarsJRyFQYlOUlWz

Elapsed: 00:00:00.01

From the following query you can see the remainder of the records in the BASETABLE were inserted into the DEST4 table. If you look you can see that BASEID of -1,-10,-100 and -200 are missing. You will have to trust me that -300 and -400 are missing in the result set as well, but I didn't want this running too long.


SQL> SELECT * FROM DEST4 ORDER BY DESTID;

DESTID BASEID BASEDATA

---------- ---------- ------------------------------

2 -2 fPNMkRbJAEoeaWejzrAigZjKqZVzUl

3 -3 NDmRQNKmPhAnzfuWhLQDnWIcRVpjLF

4 -4 DoNnVEskItQAfANavQVHdJWdOeZbAc

5 -5 SNacUWsrPCPyLwDBxEtndSsiiSTmPW

6 -6 gLxiVlWXsdcLPhDgLThISCutKBfuOj

7 -7 sZCNlljiTveZPIUgyEBPalpJPrMdck

8 -8 UOwvqNxyPXcpsxRmjsxLQGfEsHQOqO

9 -9 WDwQqUnMHjDautMrYYBMCcjIoNWMKg

11 -11 BOfKwqtFZWQuLVEHFhMRHrfBGyeTfQ

<SNIP>

99 -99 VjmavGgzdQroTHutlhcOQjiqlTiLHW

101 -101 cjuHxrklWRaQmRJZyVShliswLRCgBm

<SNIP>

199 -199 xvaXYHPkexmFOkXCDBOODqjEatyMwY

201 -201 fXwQaaSTWAEDrYDqnRHVxLqcQEkbCZ

<SNIP>

500 -500 eLqsjEKEzWTmQUTsEtHFcRVEkEiQZz

494 rows selected.

Elapsed: 00:00:01.06

Now simply the cleanup.

SQL> DROP SEQUENCE DESTID_SEQ;

Sequence dropped.

Elapsed: 00:00:00.03

SQL> DROP TABLE BASETABLE;

Table dropped.

Elapsed: 00:00:00.03

SQL> DROP TABLE DEST1;

Table dropped.

Elapsed: 00:00:00.03

SQL> DROP TABLE DEST2;

Table dropped.

Elapsed: 00:00:00.04

SQL> DROP TABLE DEST3;

Table dropped.

Elapsed: 00:00:00.03

SQL> DROP TABLE DEST4;

Table dropped.

Elapsed: 00:00:00.01

SQL>

SQL> SPOOL OFF


New Look

I decided to finally allow Google to move my blog to the new now no longer beta blogger.

It looks good. New "spot", I decided on a different look.

If you care, let me know if you have any issues with it.

Thanks.

Friday, March 02, 2007

More on DST

When did oracle start being ran by a bunch of morons?

This DST patch is simply a joke. There must be a punch line in here someplace, because there should be no way that a company the size and past capability of oracle can screw something this simple up so many times and on so many levels.


One of Oracle's marketing slogans


Oracle Is The Information Company


I guess only if your information is not time stamped.


What a crock... SQL Server simply looks just a little better now, but I am sure after the next 9 days of being awake at work patching databases again, I won't remember I said that.