Migrating and upgrading Oracle form Solaris to Linux, including stored procedures

Question

Hello,

We are planning a migration of an Oracle installation from Solaris to Linux. At the same time we will upgrade to latest version of Oracle. There are a few hundred stored procedures that should be migrated as well.

Present version is Oracle SE 9.2. We will still use Oracle SE, potentially with RAC.

Is there anything that we must consider before doing this? Any problems that you have experienced while doing this? All info that can help us is valuable.

Thanks in advance!

/Niklas

Answer 1

We just went through this, although we two-stepped it - went from 9 to 10 last year, then 10 to 11 this year. I'll try to recall from memory what we found:

A. If you have queries with a GROUP BY you'll want to add a matching ORDER BY, as newer versions of Oracle do not (usually) drop in a SORT step during GROUP BY processing. In other words, if there's a query like

SELECT * FROM SOME_TABLE
  GROUP BY FIELD_1, FIELD_2

you'll want to change this to

SELECT * FROM SOME_TABLE
  GROUP BY FIELD_1, FIELD_2
  ORDER BY FIELD_1, FIELD_2

B. I suggest you keep a copy of your 9.x database around for a while so you can compare the plan generated under 9 to the one generated under 11. The optimizer in 11.x can evaluate some queries very differently than 9.x or 10.x would. We found that one of our larger queries that we'd hinted up appropriately for 10.x ran very slowly under 11.x. After an afternoon of developers and DBA's tearing their hair out we found that the ORDERED hint restored the original plan and performance.

C. If you have any code which is explicitly setting up to use the rule-based optimizer by doing something like

alter session set optimizer_goal = rule

you'll get an error telling you that the rule-based optimizer is obsolete.

D. PL/SQL code which creates a collection but which doesn't initialize it may work under 9.x but will fail under 11.x. An example would be

TYPE tMyArray IS VARRAY(100) OF VARCHAR2(100);

arrMyArray  tMyArray;    -- <-- uninitialized array

To fix this you need to change the variable declaration to

arrMyArray  tMyArray := tMyArray('');

For purposes of reference, the 9 -> 10 upgrade was the most work. 10 -> 11 was pretty much a no-brainer (except for a few queries that had problems - see B).

Answer 2

Dataguard proper is actually an Enterprise Edition feature.