I feel it’s going to be good fun - I’m only on my way to UKOUG 2006 but it’s already an adventure. I had the flight Ottawa -> London booked over Washington where I should have had 2 hours for connection. Registration went fine almost until the very end (had to give away my water though and Babette was finishing here coffee on the way) but at the US customs I’ve got the problem. Apparently, there is a new rule for transit passengers - they suspended permission for all international connections without a transit visa. It used to be so that transit passengers stay in the terminal and no need for any kind of visa. Not anymore and it’s valid “until further notice” as stated in their systems.

Being stubborn enough (the common quality here at Pythian ;-) and I’m not going to skip the fun just because of some stupid immigration rules so… I had to go back to the check-in counter, get my baggage and re-book my ticket to avoid a stopover in the States. They re-booked me on the direct flight Ottawa-London and that cost me only $200 after some negotiations (originally $2000+) and 2 hours of my time early in the morning. Unfortunately, I’ve got split up with Babette and I hope she is fine on her own there.

Babette, if you read this post by any chance, I should be in the Hotel on Monday at about 11:00. I also left you a message at the receptions desk.

Back to the UKOUG - I have three sessions there. These are first three right on Tuesday after the Opening Technical Keynote by Tom Kyte:

• Extending Oracle 10g Grid Control - New Metrics, Targets and Plugins - 11:00-12:00, Hall 6
• Refreshing Test Databases Using Oracle Transportable Tablespaces and Veritas Storage Checkpoints - 12:10-12:55, Hall 6. Here is the surprise - I’ll show how to do the same with ZFS. I’m really excited about that.
• Oracle 10g Change Tracking Inside Out - 13:50-14:00, Hall 9. Consider this if you want to get down to X$ tables and tracing.

Babette has two presentations the same day:

• Moving from Oracle Names to Oracle Internet Directory - 11:00-12:00, Hall 11b

• Oracle 10g Data Pump 101 - 17:50-18:35, Hall 5

See you there! I’m off to my flight. Again…

Or, How To Become an Export/Import Migration Superstar!

We’ve already established that I like broccoli. I get the taste from my dad who takes it with a glob a salad dressing and munches on it. It beats munching on Doritos. He and I both also do this with cucumbers. I don’t get it, but it tastes good and I know I’ll get a real kick out of it if I see my kids doing this someday too. In any case, one thing my dad doesn’t do is work with Oracle, and sadly, because of this, he’ll never get to add the Export/Import superstar trophy to his collection.

Over the last weekend I had the pleasure (I say pleasure because this was actually a very smooth operation) to do an 8i -> 10g, Solaris -> Linux migration. Talk about going in head–first. Now, whenever I run into a situation like this, we generally recommend a two–step process so that we can iron out bugs and be able to isolate causes. However, we were under serious time constraints, and we decided to just go with the following simple plan and move straight there. I had plenty of salad dressing to keep me company overnight.

Step 1 - export
Step 2 - import data
Step 3 - import everything else

That was the plan. We tried this a few times and when I finally had all the indexes etc. where I wanted them on the new server, and since we did some minor storage rejigging, I actually took a norows there and used that on the object import.

Now for the fun part. Typically, if you stick with a standard export/import, it’ll take you say… 10 hours. That’s what it usually took me. 30GB of data and 30GB of indexes. Now, you may ask, what makes me a superstar? Well, the fact that I dropped this down to about 5 hours, that’s what. This assumes you have big pages set up and Oracle able to address the space for a massive SGA.

The tricks:

1. Assuming a full export, use direct=y if you’re not using any predicates in the export.
2. Set your buffer to be big (10MB at least).
   Now the good stuff:
3. Alter all your constraints novalidate prior to export if you can bring the app down, and if you can take a consistent export. This helps in reinstating the constraints instantly without forcing Oracle to validate constraints on massive tables
4. I set workarea_size_policy=manual
5. Set sort_area_size=6GB (yes, 6 GB). Combined with 4, it let Oracle build indexes with more space in memory for all the sorts without the need to spill to disk.
6. Set massive online redo logs at 2GB each, 2 members each, 6 groups.

After the import, I reset everything back down to “normal”. The beauty of this migration was that the the client gave us a significant window of downtime. I cannot stress how much this actually reduces the complexity of the whole operation, and reduces the time needed to verify that everything works. Yay! for clients who provide realistic migration windows, and yay! for less complexity and effort (which equates to cost for all you business–y people) in migration windows.

I’m happy, and the client is happy, and I still have some cucumbers and broccoli left for breakfast.

P.S.: To add to the list of export/import deficiencies and 10.2.0.2 problems — you should note that function–based indexes need to be created manually.

While reviewing some material in advance of my presentation at UKOUG Conference 2006, I found an interesting change in RMAN behavior in Oracle 10g.

The difference is in the way RMAN handles the case when an incremental level 1 backup is taken without an available level 0 backup. This probably won’t affect anyone much, but I found it interesting. And actually, there are scenarios in which it might cause issues.

Pre-10g behavior (also in effect in 10g by setting compatibility to < 10.0.0) produces a level 0 backup in case incremental level 1 is run without level 0.

Oracle 10g behavior is to take a level one backup assuming that a checkpoint SCN of the last level 0 backup is the datafile creation SCN.
It doesn't look like a big deal but imagine the following scenario (I didn't test it - just thinking logically):
Incremental backup strategy is "standard".
Sunday - level 0 backup.
Monday-Saturday - level 1 cumulative.

Now, imagine that on Monday there is an issue and the backup is not available anymore -- either it's lost, or recovery with resetlogs has been done. The next incremental backup stores all the blocks, so we have a valid backup -- no problem here.

However, the keyword in the backup strategy is cumulative. The next incremental level 1 cumulative backup still doesn’t find the level 0 backup and again copies every block in the database. This continues until the next level 0 backup is taken on Sunday. Assuming DBAs are always unlucky, the backup destination tapes will fill up before the incremental level 1 backup is run. Typical, isn’t it?

How can we avoid this? By taking the level 0 backup first time and not regular level 1.

Note that “differential” (when no cumulative keyword is specified) incremental backup won’t cause any issues, as it takes only those blocks changed since the last incremental backup.

I have been working on issues that relate to security certification at a number of our clients, and I can’t say that I have anything good to say about it. I have a very simple reason behind my dislike. Compliance standards are set such that you are protecting against the bulk of the people out there. This is generally very good practice, but when you rely on standardization alone, you open yourself to real danger.

This is not to say that “best practices” aren’t good policy. Sure, I totally agree with picking the “low–hanging fruit” and preventing the bulk of the attackers from casually accessing your data. I have a lock on the front door of my house. I know that it doesn’t prevent a criminal from getting into my house, but it might stop the 15 year old punk who has nothing better to do after cutting algebra class. I don’t have anything that valuable in my house, but if I had something worth $100 million in my living room, you can be sure I would have a big dog and a guy in a ninja suit there ready to stop someone from getting it.

(more…)

The latest release of DBD::Oracle is now ready and can be found at:

CPAN DBD::Oracle

The release has been fully tested with the latest version of DBI (1.53).

Below is the list of the changes and /or fixes in this release.

• Fixed execute_array to comply with DBI standard from Martin J. Evans, Xho Jingleheimerschmidt and others
• Fixed execute_array so it will not throw a Perl warning on undef values in Tuples from John Scoles
• Fixed execute_array so it will take the ora_array_chunk_size DB handle attribute
• Fixed some typos in code and READMEs from John Scoles
• Fixed a few other little bugs dealing with compatibility with Oracle 8 Changes to README from Karl Auer
• Suppress warning in 26exe_array.t from Philip Garrett
• Added support for array context aware execute_for_fetch from Martin J. Evans
• Fixed Makefile.PL for an incompatibility with ExtUtils::MM_Unix v1.50 (invoked byExtUtils::MakeMaker) from Dennis McRitchie
• Updated POD to reflect that OCI after 9.2 no longer strips trailing spaces

Please enjoy,

John Scoles.