Cameron Cameron, Senior Consultant

“In this world nothing can be said to be certain, except death and taxes.”  – Benjamin Franklin

If your enterprise is running on an Oracle database, here’s another certainty – you need to maintain Oracle Support.

Full disclaimer: I really, really, really hate to deal with Oracle Support. Just like what I suspect is the overwhelming majority of seasoned Oracle professionals out there, I will exhaust every other option that I can think of before opening a SR. But the reason why you need Oracle Support isn’t for the SRs; rather it’s because you need access to the My Oracle Support (MoS) notes and patches.

In the past year, House of Brick was presented with an interesting set of challenges from a client: we needed to migrate 35 of their databases off of HP-UX and over to Linux; they would take the lessons learned from our migrations and migrate the rest of their 120+ databases; the database versions ranged from 10.2.0.4 to 11.2.0.3; and several of the databases were for Oracle E-Business Suite (EBS) 11i. Oh, and they had dropped Oracle Support and gone with a third-party support vendor. This third-party support vendor, while having a staff of knowledgeable analysts, was legally unable to provide any MoS notes, or patches, that the client had not already downloaded into their archive prior to dropping Oracle Support.

We’ve worked on a lot of platform migrations, including those that involved EBS, and we have a proven track record of success. We know how to do platform migrations, including those involving EBS. However, we usually utilize Oracle documentation to do what we do, and sometimes that documentation is only available on My Oracle Support.

So, here’s a tale of how much we missed having Oracle Support…

For any who aren’t aware, HP-UX and Linux have different endian formats.

SELECT platform_name, endian_format

FROM   v$transportable_platform

WHERE platform_name like ‘HP-UX%’

   OR platform_name like ‘Linux%’

ORDER BY 1;

PLATFORM_NAME                 ENDIAN_FORMAT

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

HP-UX (64-bit)                Big

HP-UX IA (64-bit)             Big

Linux IA (32-bit)             Little

Linux IA (64-bit)             Little

Linux x86 64-bit              Little

This endianness format difference means that you can’t just copy the datafiles from one platform to the other and bring up the database. Instead, it is necessary to either convert the datafiles, or go through a full export/import (either traditional or Data Pump) in order to bring the data into a new database on the target platform.

Replatforming Methods Considered

Below are some of the replatforming methods that we considered:

Oracle GoldenGate Replication

This is a very expensive option, which was not in budget, so this was immediately eliminated. I mention it only for the sake of completeness.

Cross Platform Transportable Tablespaces (XTTS)

This is a good method for moving large databases, as the datafiles themselves are being moved. Picture a tablespace as being a USB drive on your computer, which you can unplug and then plug-in to a new computer.

Pros

• It is facilitated by Data Pump (or traditional export/import) and RMAN, so there shouldn’t be any new commands for a DBA to learn
• Very few database and data type restrictions
• Database can be upgraded during migration

Cons

• Database is largely unavailable from the beginning of the migration until it is complete
• Bugs associated with this method, notably bug 13001379

Fast Cross Platform Transportable Tablespaces (XTTS)

Fast XTTS is an XTTS method that utilizes a pre-converted RMAN copies and converted incremental backups. For details on this method, read Jeff Stonacek’s article.

Data Pump Export – Import

This is the tool that should be in every Oracle DBA’s toolbox.

Pros

• Very stable and reliable product
• Very few database and data type restrictions
• Database can be upgraded during migration

Cons

• While portions of a Data Pump export/import can be parallelized, not all of them can. Specifically, metadata is handled in a single-threaded fashion; this especially impacts Oracle EBS databases, which have large amounts of metadata.
• Named pipes cannot be used. While the NETWORK_LINK parameter allows you to transfer data directly to a remote database without having to write out intermediary files, not all data types are supported using this parameter. For example, Oracle EBS has data types that are not supported.

Traditional Export – Import

Pros

• Has been in use for years and is very reliable
• Very few database and data type restrictions
• Database can be upgraded during migration
• Supports named pipes – I mention this because House of Brick has developed a proprietary tool that ships a database, piece by piece, over the network to a new database and named pipes are required for this process

Cons

• Very time consuming for larger databases
• Is deprecated?

Choosing a Replatforming Method

So armed with this list, we immediately had to discard the fastest method – Fast XTTS. Why? Because it isn’t supported until version 11.2.0.4, and the client didn’t have access this version. Also, the primary MoS note for this method, 1389591.1 “11G – Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup”, has a zip file with a set of supporting scripts attached to it. Again, without Oracle Support the client didn’t have access to this file.

The next method we considered was traditional XTTS. This also had to be discarded, because of our prior experience with Oracle bug 13001379. Bug 13001379 is a Data Pump bug, occurring only when using the TRANSPORT_TABLESPACES option, which can produce incorrect metadata (in the destination database) on tables that have had a column dropped from it in the past; basically, data corruption. This bug was not fixed until version 11.2.0.4. We know that in Oracle EBS there are many tables where table structures are modified, and it didn’t seem prudent to “try it out” on the non-EBS databases, especially without having the validation query contained in MoS note 1440203.1 (“Alert: TTS Bug 13001379 Can Cause Dictionary Corruption for Some Tables in Versions < 12.1”).

This really left us with only the Data Pump export – import method. Out of all of Oracle’s tools, we were left with the most basic. Even then, we got caught by a Data Pump related bug, which thankfully, the client had had the foresight to download the fix for prior to dropping Oracle Support.

Still Had to Depend on Luck

For the EBS databases, we almost had to give up before we even got started. To replatform an EBS database, you need to start with an empty database and then run a series of scripts to install products and create data structures, so that the database has the appropriate structure in which to import the EBS schemas in to. Oracle supplies these scripts in the form of EBS application patches. Some of these patches will run 50+ additional scripts, so the chances of anybody identifying the scripts and figuring out the order in which to run them are slim-to-none. We were fortunate that, after some thorough searching, the customer found that they had previously downloaded the appropriate patch that allowed us to do the migration.

We faced all of these challenges in a single engagement. This doesn’t even touch on the quarterly security patches that Oracle releases. How many of you have corporate security teams that will allow you to run your enterprise systems with published vulnerabilities? How many auditors will let your systems get by without current security patches?

It was funny,- after hitting a snag in the replatforming project, the customer’s project manager asked “If money weren’t an object, is there anything that we can do to make sure that this process goes more smoothly?”. Without any hesitation I answered, “Yes, you can purchase Oracle Support!”

Conclusion

In the end, we were able to satisfy all of our customer’s needs. But without Oracle Support, they are still locked into versions of the software that have either reached end-of-life or are about to. Can your business do that and remain viable? Are you using third-party software that will require current versions of Oracle software in order for your support to remain certified and supported?

After reading this story, you may have come to the same realization as our client – you need Oracle Support. Death, taxes and Oracle Support: three certainties in life.