November 2007

Oracle 10g – Upgrading, Now What?

Recap

In last quarter’s article What You Should Know Before You Upgrade we discussed what goes into making the decision to upgrade to a new version of Oracle. We looked at the reasons why you would upgrade, what activities should be taken and what you should be concerned about when planning the migration.

In this article we will explore four technical approaches for executing that migration, support offerings to leverage, and a collection of our lessons learned and best practices from past migrations.

The Method of Migration

During the planning phase we have already made the decision to either go with an in-place migration or a migration to a different server/platform. Oracle identifies four different migration options:

Database Upgrade Assistant (DBUA)
Manual Upgrade
Export/Import
Data Copying

Each method comes with tradeoffs; the choice is dependent on the type of involvement the DBA wants to have, the available hardware environments, and the number of changes that will have to be made to the new environment. When possible, the easiest migration would be using the DBUA where everything is done automatically and through a GUI. The most involved method is Data Copying as almost all actions are manual.

A short description of each of these methods follows below:

Database Upgrade Assistant

The DBUA is a graphical user interface that will guide you through the upgrade of a database. It is the easiest way of upgrading, but changes can only be made after the migration. It performs checks up front to verify the hardware and OS environment, checking initialization parameters and other factors that might cause errors in the upgrade. It then allows you to select a number of databases to be upgraded. After the migration, a post-update script is run that identifies any issues and recompiles any invalidated objects. This is an in-place migration where the database remains in the same location, the database software is upgraded, and then the database is migrated using scripts.

The advantages of this method are that all tasks are automated, that it supports RAC, single instances, and ASM instances. A disadvantage is that it offers less control over the individual upgrade steps.

There is only one version of the database in this scenario which will be unavailable during the upgrade.

Manual Upgrade

The Manual Upgrade basically relies on the DBA to perform all tasks that the DBUA does inherently. This is also an in-place migration.

An advantage of this method is that the DBA has full control over the upgrade process. Some disadvantages are that it is more work on the DBA, more error prone, harder to automate, and checking the upgrade results are all manual.

There is only one version of the database in this scenario which will be unavailable during the upgrade.

Export/Import

The Export/Import method is usually used when the new environment is not located on the same hardware environment (different server, different OS, different SAN). The new database needs to be created before import, with all required users, table spaces, and data files already present. The export/import needs to be done through the IMP/EXP utilities (if your source environment uses Oracle 9iR2 or older), or using the new Datapump EXPDP/IMPDP utilities coming from an Oracle 10gR1 environment.

The Export/Import method has the advantage that the DBA has full control over how the new database objects are to be set up. They can be either created through the import utility, or they can be set up beforehand and only the data is imported. The size of the import and allotted time for a migration might make this option not workable as the old IMP/EXP utilities are very slow. The new EXPDP/IMPDP utilities are a lot faster (5-10 times faster) but can only be used in an Oracle 10g source and target environment.

There are multiple versions of the database in this scenario. One is still on the source system and remains available and untouched by the migration; the second is on the new environment and will become the new production environment after a successful migration.

Data Copying

The Data Copying method is like the Export/Import method since it requires a target database to be created beforehand (on the same or different hardware environment). However, it requires a lot of manual scripts to be created afterward to create constraints, indexes, etc. The Data copy uses a database link to run “create table as select” (CTAS) statements, if the tables don’t already exist, or to use an “insert /*+append*/ select” statement to load data into already existing tables.

The advantage of the Data Copy over the Export/Import method is that you can load, filter and/or transform data during the load of the new environment (e.g. if you want to load a subset of data into a test database). A disadvantage is that it requires many manual scripts to be created and executed, increasing the risk of errors.

There are multiple versions of the database in this scenario. One is still on the source system and remains available and untouched by the migration; the second is on the new environment and will become the new production environment after a successful migration.

The Migration

Depending on the chosen method, certain actions are to be taken to upgrade the environment. The matrix below lists these actions and whether they are required (X), optional (O) or not relevant (-) for a certain method.

Action/Method	DBUA	Manual	Exp/Imp	Copy
Backup System State	X	X	O	O
Validate Environment with Health Check Validation Engine (HCVE)	X	X	X	X
Create New Oracle Home	X	X	X	X
Install Oracle 10G R2 Software	X	X	X	X
Install Latest Oracle Patches	X	X	X	X
Backup Database(s)	X	X	X	X
Gather Performance Statistics (Statspack or AWR)	X	X	X	X
Run Pre Upgrade Tool (utlu102i.sql)	X	X	-	-
Create Database	-	-	X	X
Upgrade Database	X	X	-	-
Post Upgrade Tool	X	X	-	-
Create Users/Tablespaces	-	-	X	X
Create Objects	-	-	O	O
Import Database	-	-	X	-
Copy Data	-	-	-	X
Create Remaining Objects (indexes, FK’s, constraints, etc)	-	-	O	O
Test Application(s)	X	X	X	X
Gather Performance Statistics (AWR)
Test Backup/Restore process	X	X	X	X

The details for each of these steps are explained in detail in the Oracle 10g R2 Upgrade Guide (http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/toc.htm) and can be found as part of the Oracle Documentation set.

Support

Besides your own ‘in-house’ support organization, there are three other types of support organizations that you should involve in your migration project.

Oracle Support

Oracle provides different levels of support before, during and after a migration, and/or go-live. A requirement before even considering an upgrade is having a current support and maintenance contract. This will allow you to access the Metalink website (http://metalink.oracle.com) where you can investigate and report problems, find a huge knowledge base, and download the latest patches and patch sets.

A good initial document about Oracle Support can be found at the following website: http://www.oracle.com/lang/de/collateral/downloads/ORACLE_Customer_Quick_Reference_Guide_Oracle-Suite.pdf

Oracle provides a free 2-hour web demo on how to leverage Oracle Support. They will go over all aspects of the Metalink site, as well as discuss the added support they can provide during go-live. In a production go-live situation, they will assign you a customer support representative who will keep an eye on your progress and can expedite any issues and bugs that might pop up during the last days and hours before the implementation. During that two week period, your reported bugs will be automatically upgraded to the next severity level.

It is very important when working with Oracle Support to not have an “Oracle Wait on Customer” status. When a service request is opened, they will quickly come back with an initial set of questions and queries for you to investigate and they will place the Service Request in status “@CUS” (at customer). A quicker turn-around on your side keeps them more focused on working on the issue. Be prepared for a Severity 1 Service Request to put an additional strain on your own resources; Oracle requires a 24/7 coverage at the customer site in addition to Oracle having the SR “follow-the-sun” where they are transferring the SR from time zone to time zone and from continent to continent, until it is resolved. Getting a SR raised to a Severity 1 level usually requires an executive at the customer to interact with Oracle support as well (CIO/VP level).

Sometimes a bug is fixed for an Operating System or in a later patch level set than you are running, and it might require a certain persuasion with Oracle Support regarding the negative financial impact the bug will have if it is not fixed. Oracle may then decide to develop a backport patch for your OS/patch level. Additionally you will need to keep a constant eye on the Metalink website for newly released patches.

Application Support

Whenever applications that use Oracle as their backend are included in an Oracle migration, make sure to involve them early on in your planning. First of all check with the vendor to see if their application is supported (and more importantly certified) on the target database release and patch level. Are there any known issues, workarounds, specific parameter settings, etc?

Secondly, involve them during the migration and testing. You want to raise any issues between the application and database interactions to both Oracle and the application vendor and have them check into the problem from both angles. If they are a strategic partner, it also raises your complaint level with Oracle.

Hardware Support

As with application support, keep you hardware vendor abreast of all your activities and involve them in the configuration and testing phase. Again, if they are a strategic partner, they might have valuable input regarding parameter and configuration settings and can raise the importance of an issue within Oracle

Lessons Learned

SEI has performed a number of Oracle 10g migrations, and we’re happy to share with you lessons learned:

Problem	Action	Lessons Learned
Tools not supported for Oracle version/OS Oracle 10gR2 was,but Oracle Warehouse Builder 10gR2 was not, supported on HP-TRU64	Change environment and infrastructure to HP-Ux Itanium	Check availability of versions and patch levels for a certain Operating System and environment. Also check Metalink for the product map for individual releases and supported platforms.
HP EVA San Support	HP and Oracle patches were needed to provide the necessary performance and support for the EVA	Involve your hardware vendor in support issues. File reports with both hardware vendor and Oracle to get faster results.
OLTP problem on GLOBAL TEMP TABLE with an ON COMMIT DELETE ROWS in a RAC environment causing excessive communication between RAC instances.	Two hidden parameters were set. Later fixed by a patch.	Work with Oracle; sign up to receive patch and security notifications through the Metalink website.
Migration delays due to vendors not having certified Oracle 10g yet.	In one instance, some vendors for smaller 3^rd party tools had not completed their 10g certification.	Involve vendors early on in the planning so you are aware of all potential risks for a go-live date.
Manually changed hidden parameters held up filing an Oracle SR.	Oracle does not support an environment in which the customer himself changes any hidden parameters. Parameters had to be reset to the original state and issue had to be reconfirmed before they started working on the SR.	Only change hidden parameters when Oracle Support tells you to.
Upgrade from version older than 8.1.7.4 (e.g. 7.x, 8.0, 8.1.6, 8.1.7.3)	First upgrade database to 8.1.7.4, then upgrade to 10gR2	Check the Upgrade documentation before starting on an upgrade to see if the from-to version conversion is supported or if additional steps are required.
ANALYZE Table vs. DBMS_STATS. Oracle will eliminate the ANALYZE TABLE function in Oracle 11g and is pushing towards the DBMS_STATS statistics gathering. DBMS_STATS will deliver better statistics used by the CBO (Cost Based Optimizer). Using both statements on the same table in a mix will hurt performance	Choose for one set of statistics and make sure you always keep them up-to-date. If statistics get stale the access plans will be very unpredictable. In order to get one set of statistics, run both an ANALYZE TABLE DELETE STATISTICS and DBMS_STATS. DELETE_TABLE_STATISTICS, and then rebuild the statistics using a DBMS_STATS. GATHER_TABLE_STATISTICS.	Check Oracle’s recommendations and product map for certain features and options. One set of settings that have proven to work for us: - Granularity ALL - MethodOpt: For All Columns Size 254 - Cascade: TRUE - Degree: # of CPU’s – 1
RBO vs. CBO results in completely different execution plans	Oracle is getting away from the RBO (Rule Based Optimizer) in favor of the CBO (Cost Based Optimizer). The explain plans can be completely different between the two. Make sure you keep your statistics up-to-date and as detailed as possible for the best result. Also make sure you have good and current system statistics taken at a time of load.	-
Nested Loops vs. Hash Joins	The new optimizer really shines when it can use a hash join instead of a nested loop. But in cases where you really want the optimizer to force to do a nested loop, you can do so using the USE_NL hint.	You can greatly change the performance of a query by adding or removing hints. These hints will take precedence over the preferred plan the optimizer generates. But beware that the performance can change both for better or worse.
Optimizer Settings can impact your performance positively or negatively	Changing settings like Optimizer_features_enable, optimizer_index_cost_adj, etc. can change the result of an explain plan. Changing settings will enable or disable certain features build into every Oracle release and patch set, or will prefer to do index scans above table scans or v.v.	Test these settings using an ALTER SESSION statement to not impact other users of the system. Especially when changing the Optimizer_features_enable setting, test the end results as we have seen scenario’s where the result set differed between a query executed under an 8.1.7 setting compared with a 10.2.0.2 setting.
Incorrect index use when using bind variables that used global constants in a script instead of a hardcode value.	Change the query to use a hard coded value instead of a parameter.	Tuning queries, the manual way.
Testing settings and queries; avoid the pitfall of having the data in buffer during the 2^nd test. In testing the performance difference between two queries, whether it is with different hints or different optimizer settings, make sure you compare apples-to-apples	Before each run, perform an ALTER SYSTEM FLUSH BUFFER_CACHE and ALTER SYSTEM FLUSH SHARED_POOL. This will remove any data from the buffer. Be warned!!! This is an ALTER SYSTEM statement so all users on the system will be impacted by this.	End users were comparing queries that ran faster the 2^nd time, merely because there was almost no load on the system and the data stayed in a very large buffer cache most of the day.
Oracle 600 error on a simple query, switching tables in from clause resolved it. Select from A, B where A.id=B.id* would fail, rewritten to Select from B,A where A.id=B.id* would succeed	Turned out to be a bug. Applying a patch resolved it.	If you find any strange behavior, check Metalink. Chances are that somebody else already ran into it before and that there is a patch out for it. Make sure to read the patch notes, as it might have known other issues that you may or may not be able to live with.
Two patches had to be applied to fix two separate issues, but according to the release notes, the patches were mutually exclusive. Installing the one patch would uninstall the other.	Worked with Oracle Support. Had an executive explain the need (operational/financial) to create either a backport patch (as it was fixed in a later patch set) or a merge patch in which both patches were applied into one new patch.	Work with Oracle support and explain your needs. It might require executive level involvement on the customer side, but special patches can be made available to you.
STATISTICS become stale after a certain amount of time and/or number of changes.	Making sure you’re tables have initial statistics is extremely important. Oracle will set table as having “stale” statistics after a certain amount of time (30 days according to a default hidden parameter) or after a certain number of changes to the table (more than 10% of the rows as determined by the last stats gathering have been inserted, updated and deleted. E.g. a table with 100K rows will go stale if you have 4000 inserts, 4000 updates and 4000 deletes on it (12% of 100K rows). The number of changes can be checked by looking in the DBA_TAB_MODIFICATIONS view in the SYS schema. A truncate table and then repopulate will therefore set the table statistics to stale.	You can keep statistics current by running statistics after large DML actions, or by running a scheduled DBMS_STATS.GATHER_SCHEMA_STATS with the option GATHER STALE, which will only refresh statistics on those tables that need it. (Risk of this last option is that in scheduling this to run once a week might leave you with stale objects for a certain period of time if the last time you ran the statistics and they were skipped because they were just under the threshold.

Best Practices

Some common ‘best practices’ to keep in mind when upgrading to Oracle 10g R2.

Test the Upgrade/Test the Application/Test the Recovery Strategy
Gather Performance Statistics before and after the upgrade
Run a pre-upgrade analysis using the Pre-upgrade tool to identify any potential issues
Read release notes for the Oracle Release and each patch to be installed to be aware of any known issues and setup requirements
Try to automate as much possible and create spool files for each step
Leverage the DBUA where possible. It supports the conversion from 32-bit to 64-bit, except for Oracle 10gR1 to Oracle 10gR2 in which case a separate script (utlip.sql) needs to be executed at the end of the install before the upgrade
Make sure there is enough storage available for the SYSTEM, REDO, UNDO, and TEMP logs
Use the Oracle Flexible Architecture (OFA) for datafile and system file locations
Migrate from Rule Based to Cost Based Optimizer
Set the Compatible and Optimizer_Features_Enable to the latest version/patch level for the best optimizer results
Consider the use of the Automatic Memory Management, Automatic Workload Repository, Automatic Database Diagnostic Monitor, SQL Tuning Advisor, other new monitoring diagnostics, and tuning features.
Gather Object and System Statistics
Tune your system for 90% performance, then your queries for the remaining 10%.

Finally

Upgrading to the latest Oracle version is not a trivial task. It needs to be treated as any high-priority project, with planning, design, execution, and control phases. Make sure you have properly trained staff or consultants to help you through this project, as well as a current Oracle Support & Maintenance contract. The goal should not just be a database environment running Oracle 10g; it should be a well-performing, functionally correct working environment of applications and databases.

Make sure you read the Metalink article 316889.1 (“Complete checklist for manual upgrades to Oracle 10g R2”) and the Oracle 10g R2 Upgrade Guide. We also recommend that you read the release notes specific for your hardware environment for each version and patch so that you are aware of any known issues or installation requirements.

And finally, always make sure you have a fallback plan to go back to the old version and environment in case of an emergency.