Saturday, June 26, 2010

New Opportunities , Onwards and Upwards into the local market again

Looking at my last blog post (related to Oracle that is) it looked to have occurred back in Oct/Nov of 2008, right before I joined pythian. I'm sure I left a lot of blog topics for the Pythian blog in my head. It was a fun ride working for pythian. I think my 19-20 months working there gave me the skills and experience you just can't get anywhere else in the same time frame. I have the utmost respect for my co-workers there, as well as for Paul Vallee as the Founder, President, and COO!!!

I'm not one to reflect too much on the past, so it is time to move on and push forward. I will start my new job , locally, at American Family Insurance. I look forward to being reunited with some old colleagues I've worked with in previous positions, and especially look forward to the new challenges and experiences I'll gain working there.

I also plan on dusting off this blog and being a bit more prolific in my writing, assuming I have the time. I've spent my spring coaching for the Sun Prairie Soccer Club. What a rewarding experience that was. I look forward to coaching my daughter's U-8 team this fall. This summer, I plan on playing twice a week in a local pick-up game. It has been 10+ years since I've played any sort of organized soccer and I'm loving the chance to get my skills back.

Back to the blog. I have no plans on blogging about things related to my job, but do plan on working diligently in my VirtualBox Virtual machines (OEL 5.5) with 11GR2. I have a couple ideas wandering around my head. Heck, maybe even Chet will talk me into guest blogging over at oraclenerd.com. It all comes down to time. With three kids, a full-time job, and my extra curricular activities, time is at a premium. However, my commitment remains to database technologies.

Speaking of commitments. The Wisconsin Oracle Users Groups really needs to get some legs and a head of steam. I think this is a voice and outlet that many are now missing the Wisconsin Oracle community. I am hoping to do what I can to get this user group back up and running. Ambitious, I am sure.

Monday, March 16, 2009

OT: Spring Ahead with The Pretender ... Never Surrender

I have been anticipating the arrival of alice.com in recent months. In the beginning, my interest was more due to the fact that I had worked with some of the alice employees. I think one is a Prairie Dog, one may be a Gopher, and I'm not sure what the other's are. Regardless, after my initial followings, I became intrigued at what this company was going to become. I love online shopping, and I love getting deals on household items. I also love free stuff. Let's face it, that is the real reason I'm writing this. The free stuff I can win in the latest Spring Cleaning Giveway is the main reason for this blog. I'm No Pretender. With that, I segue into my favorite cleaning song. Currently, it would have to be "The Pretender" by Foo Fighters. I especially enjoy this while cleaning the dishes after a week of not cleaning pots, pans, or the elusive electric griddle. Granted, the type of cleaning I tend to do involves more than one song, but this one sticks out in my head.

Well, here's hoping I am lucky this week and win the new iPod Shuffle. It talks to you!!!

Friday, November 21, 2008

10g Migration Ramification Part 2: Making the database a safer place

In Part 2, I will be discussing a couple of the security changes Oracle has made in 10g Release 2 that have affected my life as a DBA. (I can't speak to 10g Release 1 as I pretty much skipped this release altogether). As a whole, these changes point to Oracle's continuing focus on database security, although it could be argued they have a long way to go.

Changes to the DEFAULT profile:
Oracle has made a change to the out-of-the-box setting of FAILED_LOGIN_ATTEMPTS of the DEFAULT profile. In 9i the default setting was UNLIMITED, however, in 10gR2, the profile has been updated to have a value of 10. What the means is that if a user has 10 invalid attempts without a valid loginlogin, the account will be set to the LOCKED(TIMED) status. Since the DEFAULT profile has PASSWORD_LOCK_TIME set to UNLIMITED, the account will never unlock without manual intervention. This makes an out-of-the box 10gR2 database more secure, in theory, but there is one side-effect. If you do not change the setting or create custom profiles with appropriate settings for application accounts, it is quite simple to engage in Denial of Service attacks against the database.

I've seen this change in security cause production issues mainly with third-party applications that don't have the ability to change a user's profile or with internal processes that are used to reset passwords for users. This has caused increased help desk tickets due to locked accounts even after a password reset was completed.

Thankfully, this is pretty easy to solve depending on your business rules.
  1. Alter the default profile to match your company standards. I'm not the biggest fan of this. I"m not sure if Oracle keeps any changes you make to the DEFAULT profile between upgrades. I'll have to test that sometime when moving to 11g.
  2. Create new profiles with your own security settings. To get around certain third-party applications that create their own users, you may need a system trigger to change the profile after a user is created. You may also need to include automatic 'unlock' functionality in the case of a user that has their password changed and is in a LOCKED(TIMED) status. This could alleviate help desk calls.
  3. To deal with the unlock issue, you could also change business processes that reset passwords to also include an unlock of the account. There are a lot of options when it comes to Oracle Security and profiles. Explore all of them.
Changes to default file permissions to the Oracle binaries:
The Oracle 10g software installation on most Unix/Linux platforms has become more secure out of the box. In fact, Oracle has taken it to the opposite extreme. Prior to 9.2.0.8, the Oracle software installation was pretty wide open from a file permission standpoint. Starting somewhere around 9.2.0.8, the ORACLE_HOME has been locked down to only allow access to the owner and group. From a security standpoint this looks to be a good thing, however this can cause slight headaches for applications and developers that use the Oracle client on these platforms. Thankfully, in Oracle 10gR2 there is a script to loosen security on the ORACLE_HOME. changePerm.ksh is located in $ORACLE_HOME/install on most Unix/Linux platforms. You can run it as-is and it will more or less put the permissions back to the way they were in previous versions. There is also the ability to customize what permissions are loosened.

Database Link passwords no longer stored plain text:
This is a wonderful change from previous versions. For far too long, Oracle has been storing database link passwords in the SYS.LINK$ (and exposed via USER_DB_LINKS) plain text. While this has always been a nicety for DBAs to use, it definitely exposes a security hole in the database. In Oracle 10g, the passwords are now stored encrypted (or maybe it is hashed, I forget) so that DBAs and developers alike can not gain access to schema accounts. Even the USER_DB_LINKS view shows no password when logged in and looking at private database links.

What this means to the DBA is a bit of a change in how they move database links. An old trick was to run the following:

SELECT name,password,host FROM sys.link$;

You will also need the user_id to join to user$. The DBA could easily get passwords and recreate database links. Luckily, in 10g, the DBA doesn't need to know passwords. The old trick of using the password hash works just fine for database links as well.

'SELECT name,xpassword,host FROM sys.link$

If you take the output and format a database link create statement like the following, you will have a working database link:

CREATE DATABASE LINK IDENTIFIED BY VALUES '' USING '';

This assumes you are logged on as the owner of the database link.

There are other methods in recreating the database links in 10g. Refer to the Oracle-L thread that occurred recently for more information

I know there are a lot more security changes in Oracle 10g. These are the big ones that I've encountered in migrating from 9i to 10g. Hopefully this will help any small hiccups in your upgrades.

Friday, October 31, 2008

Don't Cramp My (SQL) Style ... Musings from an Oracle DBA

This post was inspired by a Twitter thread that took place on October 31st between myself, @oraclenerd, @dtseiler (maybe I only included Don for his tongue in cheek, playground banter), and @surfsearcher :
@oraclenerd: "besides commas at the start of a line (sql, plsql), I also hate mixed case sql and plsql"

@oraclenerd: "@piontekdd come on! it's just plain ugly...besides, I've gotta have have at least a little something to bitch about now and again. :) "

@piontekdd: "@oraclenerd life is too short to get upset about personal style :)"

@piontekdd: "@oraclenerd I like mixed case and see the validity in commas at the beginning ;)"

@oraclenerd: "@piontekdd well sir, I would argue that if you like commas at the beginning and the brewers, well... "

@dtseiler: "@oraclenerd I like commas at the beginning and the Brewers. Do we need to take this outside?"

@piontekdd: "@oraclenerd putting commas at the beginning (while not my natural style) is easier to cut and paste lines and to not miss commas.:

@surfsearcher: "@piontekdd the last dba i worked with corrected me on doing it that way, made me change all my sql b/c commas in front is "non-standard"."

@surfsearcher: "@piontekdd this dba was known for enforcing her personal standards to a point well beyond reason. i'm with you on the commas."


I believe the gist of what Chet was getting at was a personal style issue in formatting SQL or PL/SQL code:

select
foo,
bar
from
foobar
where
foo = 'BAR' and
bar = 'FOO'
order by
bar,
foo;

versus

SELECT
foo
,bar
FROM
foobar
WHERE
foo = 'BAR'
AND bar = 'FOO
ORDER BY
bar
,foo;



While I'm sure Chet was just venting over some work issues, it got me to thinking about standards in the IT workplace versus personal style. (Personal baseball tastes and my obvious over use of emoticons on twitter notwithstanding). I'm all for having coding standards, database object standards ... etc for in-house development. However, I do find that coding, whether it be .NET, Java, SQL or PL/SQL, has a bit of personal style to it. This does not mean making code difficult to read or impossible to follow, but I can see the need for some personal latitude when coding. I've experienced my own coding creativity slowed down by worrying too much about the standard and not enough on what I was attempting to solve. Is is not enough to provide excellent documentation and comments in your code?

I have worked as a developer on large systems (Oracle and C), as well as applications where I am the lone developer (Visual C++). On large system, it is important to adhere to some sort of standard to make the code easier to support by others. I've seen the proof that following these standards can keep maintenance costs down. I just don't see how forcing a developer to put a comma in a certain place, or using a certain case of letters, is going to hurt that.

I've been an Oracle DBA for over eight years. I've written my share of SQL and PL/SQL packages. I totally understand the need for certain standards. When designing data models for new applications, it is very beneficial to have object naming standards (tables, indexes, constraints, etc). It is important for developers to have a set of SQL guidelines. (e.g. Using bind variables where appropriate).

When I read @surfsearcher's tweet above, I started to get a bit passionate about writing this. I'm a DBA. At the end of the day, I'm ultimately responsible for what gets promoted to production and for the health of the database. However, my personal preference for where a comma goes shouldn't enter into the equation. It is my job as the DBA to work with the developers, not push my personal style down their throats. It is my belief that DBAs and developers need to work hand in hand in a collaborative way. If I ask someone to change something, I better have a better reason than "It is not standard".

I often see developers and DBAs engage in this 'us vs. them' relationship. I see this in online forums and lists. DBAs referring to developers as 'duhvelopers'. I've seen it in the workplace. DBAs are viewed as over-bearing, control freaks or where the DBA thinks their life would be easier without having developers. It does very little for making a better application. I've seen my share of 'duh' moments in the development life-cycle. I've seen my share of 'duh' moments by DBAs (myself included). I firmly believe a cooperative, open environment must exist for a successful application to be designed, developed, and deployed.

What do you think? Is there room for personal style when coding in the workplace? Can strict naming standards co-exist with personal style/preference? Is all this cramping your style?

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).