11g Gotcha

Something I discovered recently is that the DEFAULT profile for Oracle 11g sets the PASSWORD_LIFE_TIME to 180 instead of UNLIMTED by default.  Applications will encounter an "ORA-28002: the password will expire within X days" error message if you keep the default value.

To change the PASSWORD_LIFE_TIME, you:
ALTER PROFILE default LIMIT password_life_time UNLIMITED;

One thing to keep in mind is that when you first encounter the ORA-28002 error, your dba_users.account_status will go into a "EXPIRED(GRACE)" state.  You can "change" that users password to the same password it is now to clear it.

What's old is new again

We had a fair number of problems related to bind variable peeking in our 10.2.0.3 and 10.2.0.4 dbs.  In fact, the plans became so erratic that we decided to just turn bind variable peeking off by setting _optim_peek_user_binds=false in all the init.ora files.

Flash forward to Oracle 11g (11.2.0.2) and the optimizer has been enhanced to use bind variable peeking much better.  After converting to 11g, one of my queries was running slower than before.  I ran the slow query with hard-coded values and it finished in less than a second.  I then tried the same thing with bind variables and it ran in 2400 seconds, with a different execution plan.

These were the exact symptoms that originally brought me to set _optim_peek_user_binds in the first place.  My first thought was that I left the underscore parameters commented out after the upgrade.  Nope, it was set "correctly", or at least how I thought it should be set.

I then set optimizer_features_enable back to 10.2.0.4 for my session and re-executed the query.  Sure enough, it was back to sub-second response time.  I switched the optimizer_features_enable back to 11.2.0.2 and it was slow again.  Aha, something changed!

More research yeilded some documentation to digest:
http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html
http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm
http://www.pythian.com/news/820/bind-peeking-ad-hoc-queries-stable-performance-on-10g-you-can-only-pick-any-two/

After much reading, thought, and testing, I decided that _optim_peek_user_binds=false was not needed in 11g for my environment.

My first 11g show stopper: Purging the Recyclebin

I've run into my first issue with 11.2.0.2.  After one of my dbs was upgraded, every time I try to purge the recyclebin, I get an ORA-00600 error:

SQL> purge dba_recyclebin;
purge dba_recyclebin
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktcdso-1], [], [], [], [], [], [], [], [], [], [], []

It doesn't matter if the recyclebin is on or off.

I have done about six upgrades and this is the only db with the problem.  I can backup this db using RMAN and restore it on another host and the error still gets generated.

I have about 43 objects owned by SYS and named WRH$* left in the recyclebin after the upgrade, but these are normal after the upgrade. Or, at least, I have these objects in the recyclebin after every 10.2.0.5 -> 11.2.0.2 upgrade I've done so far.

I can purge the objects individually to get rid of the immediate problem, but I am most concerned that this will show up again in another db.

I couldn't find anything on metalink.  I've had a TAR open with Oracle Support for well over a month and it's going nowhere.

Listener Logging in 11g

By default, the listener logging is not turned on in 11r2.  You have to turn it on by specifying:

LOGGING_listener_name=ON

in your listener.ora file.

Something Interesting about the 11g Client

If you're just beginning to implement Oracle 11g, you need to be aware of the Automatic Diagnostic Repository (ADR).  It has nothing to do with AWR, ADDM, ASM, ASSM, or any of the other acronyms Oracle has come up with over the last two releases.

In terms of the database, ADR could be a great thing.  It's integration with Enterprise Manager is probably one of the best parts about the feature.  The database manages the retention policy of how long you want to keep these files around based on the scheduler, all things we know how to deal with.

However, you need to be aware that in a client-only configuration (such as a shared client over NFS) there is nothing to manage the logfiles generated by ADR.  The logs get place in /var/tmp and could potentially be across every machine in your environment.  That could be a nightmare if you're running with several hundred clients or multiple domains.  Left unchecked, it could fill up your root disk if that's where you have /var/tmp. I'll leave it to your imagination how I found out about this new feature.

Instead, you might want to turn ADR off by setting DIAG_ADR_ENABLED=off in your sqlnet.ora file.


Some Great New Features

I've been off exploring Oracle 11g for a little bit trying to figure out a strategy to upgrade my environment. Oracle 11g has a ton of new features, most of which are now "options" (in other words they cost more money).

When Oracle came out with compression at the segment level in 9i, I thought it was a great feature, at least in theory. But as I got to use compression, I found out that DDL didn't exactly work the way you expected. Then I found out rows had to be added in a certain non-standard way and decided that while the feature was great in theory, in practice it was only about 80% of what I really wanted. Fast forward to 11g and we find out that now segment compression works the way you think it should; except now it costs you extra.

Another feature, Data Guard, is quite frankly awesome. Sure, you have to license both hosts in a data guard setup, but that's the penalty you have to pay to have data in two locations. One of the things that always bothered management was that the standby host is essentially idle just waiting for a disaster that may never come. Sure, you can open the standby in read-only mode, but you have to stop keeping it up-to-date in order to do that. Flash forward to 11g and now you have the ability to have the standby database applying logs but you can also run queries against it!  Wow, this will make my management really happy...until they find out they have to pay to use an incremental enhancement to a basic EE feature.

I should not really be surprised.  Oracle came up with a free statistics collection tool called "StatsPack" way back in 8i.  Using the statistics you could do all sorts of historical trending and figure out why your database was slow after the fact.  Flash forward to 10g and we have this new product called Automatic Workload Repository (AWR) ... but now you have to pay for it.  It's really just StatsPack version 2, but now they need to garner extra revenue for it.

Meanwhile, my support bill is due this month so I can pay for "Software Updates and Support" for another year.