Sunday, July 27, 2008

10g Migration Ramification Part 1: ORA-1426, Numeric Overflow

Oracle 11g was released over a year ago, but lets face it, not everyone is running Oracle 10g, let alone 11g. July 31st marks the end of the 'free' Premier Support Oracle offered up to all Oracle 9.2.0.8 customers last summer. With that spirit in mind, I thought I would share some of my Oracle 10g Migration Ramifications. ('Upgrade' is a boring word and doesn't have a lot of words that rhyme with it. It is also a great homage to Adam Sandler's 'Cajun Man').

In part 1, I will discuss an ORA-1426 you may encounter in PL/SQL code if you are using large literal integers and expecting a NUMBER type to hold it.

The code:

DECLARE
lv_results NUMBER := 0;
BEGIN
lv_results := 999999999 * 3;
END;
/

Oracle 9i Results:

PL/SQL procedure successfully completed.

Oracle 10g Results:

DECLARE
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 4


I believe the 10g result is related to a change made in Oracle 10.1. There are various PL/SQL optimizations and new features added in Oracle 10g Release 1 and Release 2. Check out Metalink DocID: #311971.1 "New Features for Oracle10g PL/SQL 10.x" for a good list. I believe what we are seeing in the code above is an optimization made to the PL/SQL engine. In the example above, the PL/SQL engine is attemping to use an integer math engine to perform its calculations. This makes sense the math involves two integers. It would be faster to use integer math over floating-point.

If we change the code slightly, we can force floating-point math and avoid the numeric overflow.

New Oracle 10g Code:

DECLARE
lv_results NUMBER := 0;
BEGIN
lv_results := 999999999 * 3.0;
END;
/


New Oracle 10g Results:

PL/SQL procedure successfully completed.


You could also avoid the issue altogether by using variables of type NUMBER.

The moral of the story with any database migration (or application change) is testing, testing, testing. Even the smoothest, most tested migration is going to come up with a few small issues (or one really big one). Even when you are armed with a list of all the new features, possible bugs etc, there are bound to be surprises for developers and dbas alike.

Good luck with getting your 9i databases migrated (or 8i , or even, 8 and 7)

Thursday, July 24, 2008

ASP State and SQL Server: The Mirror Has Three Faces

In the spirit of Jonathan Lewis' recent post, Sometimes, Everything I Write Sucks!, comes today's installment of the Prestidigitation of Oracle. Today's prestidigitation is that I'm not going to write about Oracle (or why I need to blog more), but I'm going to focus on a topic I've been dealing with lately which is implementing the ASPState database with SQL Mirroring.

This is part one in the series and will go over the set up of a custom ASP session state database to be used with ASP.NET applications which is connected to a SQL Server database that is set up in a SQL Mirroring set up. This article isn't going to cover the actual SQL Mirroring setup. A lot of what I did was learned from an article on Storing Session State in a SQL Server Database.

The ASP session state database has three options you can install with. A non-persistent database that utilizes tempdb, A persistent database that uses a default database called ASPState, and a persistent database that has a name of your choosing. (i.e. Custom database). Hence, the mirror has three faces. Given the premise that we have a SQL Server 2005 database set up for SQL Mirroring and that we want to keep the session state persistent between restarts as well capable of mirroring. The only choice I see for this is to store the ASP session state information in the applications database. In other words, using the custom option. This keeps the session information encapsulated in the same database as the application itself, and allows the information to be mirrored to another SQL Server database.

Installation
Installing the ASP session state database is fairly straight-forward. The first thing you'll need is the ASPNET_REGSQL.EXE executable. I found this under the .NET Framework 2.0 directory. I my case, this was the C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 directory.
I prefer Windows authentication, so the installation is fairly straight-forward.

ASPNET_REGSQL.EXE -E -ssadd -sstype c -d -s

This will install a couple tables and a bunch of stored procedures into your database. Voila, you now have a custom ASP session state database that should be ready for SQL Mirroring.

One other step I performed is one of security. Since the ASP session state database is stored procedure based, I need to give the application user the ability to execute stored procedures (i.e. The app will not connect as dbo or sysadmin).

use
GO
CREATE ROLE db_executor;
GO
GRANT execute TO db_executor;
GO

You now have a role that will assume execute privileges on any stored procedure in the database. Assign the role to your application user and everything should work excellent.

In the next installment, I hope to cover the testing of persistence in a SQL Mirror fail-over scenario. Don't expect it too soon (the prestidigitation continues) and look for more Oracle posts to come soon.

Wednesday, July 23, 2008

IOUG RAC Attack! ... I surrender!!!

The Oracle RAC SIG and IOUG are putting on an event in a few weeks in Chicago, August 4th and 5th. It is called the IOUG RAC Attack! (awesome name, I wonder who came up with it). Since my current job entails supporting an Oracle RAC environment (2 5-node clusters) and we have some newer members who are a bit green in the RAC arena, I thought it would make sense to check into the event and agenda to see if it is worthwhile to send some people to this.

From looking at the agenda and the quality speakers they have lined up, I'm finding it difficult to find reasons not to recommend this event to my colleagues. For $650 (2-day event, with a hands on lab) this looks to be good bang for your buck. I'd love to go myself, but my schedule just won't allow it. I'm especially interested in hearing what Alex Gorbachev has to say in the Oracle Clusterware lecture as well as Jeremy Schneider's Oracle Services lecture. Hopefully one of my colleagues attend and take good notes and share with the group when they return. *hint hint*.

With that, let the registration begin.

On a semi-related note. It might be cool for the Oracle RAC SIG or IOUG (or both) to put together online badges for its members. It would promote both organizations in the Social Networking world and blogosphere (If they already have them, my most humblest of apologies)

Disclaimer: I'm in no way affilliated with the Oracle RAC SIG or IOUG (aside from being a member of both).

Tuesday, July 22, 2008

9 Reasons I Need to Blog More

Almost on a daily basis, I sit and stair at the Prestidigitation of Oracle and wonder what I should blog about today. I know I have a lot of ideas floating around in my head and all it takes is that mental kick in the arse to get it out. And yet, here I am sitting on July 22nd, 2008 with only 3 blog posts since starting in February (That is a) not a good average and b) 2 posts, as the first one was an inaugural post). This is my attempt to kick my brain in the arse and do what I set out to do this year. Seth Godin blogged the other day about 12 ways to get your blog to the top of digg. I thought I'd try some of the tactics he mentioned in an earlier blog. I've also been semi-inspired by Fred Wilson's A VC Blog over the course of the year. While he tends to dwell in a world completely foreign to me, it is rarely not an interesting read and can get my creative juices going. Without further ado, and in no particular order:


1. Everybody is doing it
- I just threw that in there for the high school affect. I'm really not a follower (well, I am on twitter). You can definitely argue that I am on the bandwagon a bit late. But, better late than never. In all seriousness, I read a lot of interesting blog articles from Oracle professionals, venture capitalists, Social networking sites etc. I think I have something to offer and give back to the community.


2. More Blogging = Less TV time
- I love TV, I really do. The DVR was the best (and worst) invention to come in the past 5-10 years. Although, now that I think about it, I can sit in front of my laptop with a favorite TV show in the background. Maybe I should find a better hobby :)


3. What goes better with beer than blogging?
- I've seen lists like "Best Beer Drinking Sports" and the like. Well, blogging is a perfect fit for beer drinking. I love beer, I love making it, drinking it, and finding new beer. The thought of sittin back, relaxing with a cold one, and writing the thoughts in my head seems like a perfect blend.


4. Time to increase my Oracle breadth
- I spent my most of my days working on company-related database issues. There doesn't seem to be nearly enough time to do R&D. Keeping an Oracle-related blog is a great way to force me to do my own research and improve my Oracle DBA skills on my own. Now I just need to get this laptop's memory increased so I can get Oracle 11g running on VirtualBox and I'll be in business. I've started keeping a daily list of topics I want to blog about. The next step is to get that done.


5. Aspirations
- I have aspirations to present at Oracle technical conferences. I look at blogging as a great first step to getting to that goal. This will force me to come up with ideas and not only find my voice, but also an audience (I hope).

6. Peer Pressure
- I suppose that sounds an awful lot like reason #1. However, it is a bit different. I look at peers and see the amazing things they are doing on their blogs and the great content they are creating and sharing with the oracle community (Look at Dan Norris, Eddie Awad, Greg Rahn, Don Seiler to name a few) and I say "hey, I can do that, and can hopefully add my own perspective".

- An interesting aside related to Greg Rahn. He probably doesn't realize it, but without Greg Rahn, I probably wouldn't even be an Oracle DBA. In 2000, I had just been hired on as a full-time developer at a local utility. Greg was a DBA there. He resigned later that year , I think to find his fortunes out west during the Dot Com boom. That left an opening on the DBA team. I was approached by one of their Senior DBAs to be a DBA. I thought it was a crazy idea. Those DBAs didn't do anything ;) I was a C developer, I knew it all. Luckily, I decided to take the leap and have been loving it ever since. Thanks Greg for resigning and opening up that door to me ;)


7.Twitter: Sometimes 140 characters just isn't enough
- I love twitter. It is a somewhat emerging social networking idea. Some think blogging is dead and microblogging is the wave of the future and replace blogging. I'm not sure I agree at this point but I do find it a very useful medium to share ideas and expand my horizons. Sometimes (okay, a lot of times) my thoughts take up more than 140 characters. That is where the Prestidigation of Oracle needs to take over.

8. Unload some of those brain cells.
- I'm a big believer in the Use It or Lose it Principle. I think this applies to daily life. Whether it be that new whiz-bang Oracle feature you just read about, the latest training class you are thinking about taking or documenting that new package you just wrote.

9. Comfort Zone
- I think it is real easy to get into a comfort zone. Life can get hectic and it is simple to get into a routine. You need to challenge yourself and shake things up to grow. I look at blogging as a natural way to do this. While I feel I've always been a decent writer, it has generally been with little purpose (essays, papers, etc). I need to find a purpose and hopefully this can get me on the right track.