DENVER -- Upgrading to Oracle 11g, the latest Oracle database release, requires a commitment to planning and testing,...
one veteran of the process told attendees at the Collaborate user conference being held here this week.
"You cannot do the upgrade in isolation," said Maria Anderson, senior database analyst, enterprise infrastructure services for Calgary-based Petro-Canada. "I know we think that as DBAs you do the upgrade and that's all you need to worry about, but you need to involve app support folks as well as the business users. The more time you spend in this part of the project, the more likely it will be a success for you."
There are several methods for completing the upgrade, according to Anderson. Customers can upgrade directly from one of three existing systems -- 9.2.04, 10.1.02 or 10.2.0.1 -- but others will have to take an extra step. The direct upgrade can be done with the Upgrade Assistant, Oracle's graphical user interface tool, or with scripting. Or customers can take an indirect route by using import/export functions (the most common method when creating a new instance on the same server) or via table/copy, which essentially copies over tables in a database linked with an older version.
"Export/import works really well for smaller upgrades," Anderson said. "The direct methods will upgrade the database in place. Afterwards, you'll end up with one 11g instance. The indirect [route] will give you two databases, the old one and the new."
While it may not be much fun, Anderson advised attendees to follow all the documentation they can get their hands on: the new features guide, the upgrade guide, the Oracle Technology Network's 11g homepage, the Oracle Independent User Group website, and select Oracle journals and other expert books that are already emerging.
Anderson then laid out a step-by-step process for getting through the 11g upgrade.
Step 1: Planning
"I make a list of databases I'm going to upgrade and rank them from highest to lowest," she said. "I'm not going to upgrade the HR or financial system first. I'm going to pick instances that are very small where the visibility is quite low."
From there, DBAs should review the new features, research existing bugs -- those that have been resolved and those that haven't -- and develop a high-level plan, including the business users and application support. Closer to the target go-live date, that plan should then be revisited, involving business users in choosing the dates for the upgrade development test and production.
Step 2: Testing the plan
Begin with testing the upgrade itself. Are problems arising? Has anything new been introduced that needs to be accounted for? "You just want to make sure the upgrade worked and it can connect to your database," Anderson said.
DBAs also need to run a functional test -- for example, "Does the job I run every Monday still work?" -- then a performance test.
"You want to make sure your SQL doesn't perform any worse than it did before," Anderson said. "Hopefully, it runs faster. This is where you cannot do upgrades in isolation."
The load test, or stress test, is often overlooked because it's a difficult task, but it's important because that's where a lot of upgrades get into trouble. Testing the Oracle client is similarly challenging, yet critical. Ideally, any issues with these will have been caught in the upgrade test, but it's an important step, Anderson said.
Step 3: Creating documentation
Anderson developed a three-page template for her upgrade, with a list of all the people involved and their contact information, each step of the upgrade with commands and start and stop times, and a list of the backup steps in case the 11g upgrade runs into problems along the way.
"Why put myself through so much work?" she said. "It is a lot of work, but it makes the production upgrade so much easier. Also, it serves as supplement for change management. What are the steps involved? What do you do if you have to back out? It's an audit trail for compliance requirements."
This will also serve as working documentation for the next upgrade.
DBAs should also take care in setting the COMPATIBLE parameter and testing it thoroughly before setting it to 11.0.0.
"You can't really go back," Anderson said. "It actually changes the structure of the underlying logic. Once it's set to 11.0.0, you're not able to downgrade. If there's an app issue or you hit a bug, you're going to have to restore your database to a prior release."
Step 4: Preparing
The 11g upgrade will change items in the scripting environment. For example, DIAGNOSTIC_DEST replaces the bdump, cdump and udump locations.
"You need to think about these things before you upgrade to 11.1," Anderson said. "11g uses version4 time zone files. If you're not using version4 time zone files, you need to apply a patch for that. Now is a good time to review your backup strategy."
Step 5: Upgrading
"The first-time install should not be done on a production server for the first time," she said. "Not that I had any problems with an installation, but it's just not good practice."
In fact, Anderson said, the upgrade went surprisingly like the 10g upgrade.
When running with the utlu11i.sql script, DBAs need to use the upgrade assistant or first run this step themselves against the database being upgraded.
"I took it from $Oracle_Home/rdbms/admin, spooled the output and ran the script," Anderson said. "It gives you information about the database, the things you need to change and modify before you upgrade."
The Database Upgrade Assistant is the preferred method for upgrades, she said.
"I like to have control, and you have less control with the assistant, but you don't have the ability to miss a step," she said. "It's the only way to upgrade your SAP Oracle databases."
Ultimately, Anderson encourages DBAs to be proactive when it comes to upgrading to 11g.
"Initiate these projects," she said. "If you wait for your clients, often you're in a hurry, and it takes time to get these things done. Plus, it makes you look like you're doing a good job."
It's important to remember to work with the business users so they understand the process and to be aware of all bugs and fixes.
"Document the technical steps," she said. "I call it a Technical Implementation Plan (TIP). If you're doing this with a team, it's much easier to plan. The time you spend planning is never, ever wasted."