In preparation for working with a client, I just tested a migration from Oracle10gR1 to Oracle10gR2 on Red Hat Linux ES 3. This may only be a "dot" release, 10.1 to 10.2, but it is not a simple upgrade. So I chose the following approach:

• Install R2 in its own Oracle Home (which you have to do anyway).

• Create the application specific tablespaces, users, and roles in the R2 database.

• Grant roles to users in the R2 database.

• Export the application schemas from R1.

• Import the application schemas into R2.

• Create public synonyms.

This approach was simpler and seemed less prone to catastrophic failure. And it worked well. I ran into three minor "gotchas" along the way.

• Package libaio-0.3.96 is required by R2. So I installed it from the Red Hat Linux installation CD 3.

• The minimum requirements for the kernel parameters rmem_default and rmem_max have increased from R1 to R2. The new minimum is 262144. These parameters may be updated in the /etc/sysctl.conf file. The server must be rebooted for the changes to take effect.

The above requirements are detected by the Oracle installer and a log displayed detailing the issues..

The third issue was more of a surprise. The application schema owner had been granted the CONNECT and RESOURCE roles in the R1 database and the application worked with no problem. However in R2, the import resulted in errors when creating the schema views. A little investigation resulted in the following information:

In R1, the CONNECT role had the following privileges:

• ALTER SESSION

• CREATE CLUSTER

• CREATE DATABASE LINK

• CREATE SEQUENCE

• CREATE SESSION

• CREATE SYNONYM

• CREATE TABLE

• CREATE VIEW

In R2, the CONNECT role has only CREATE SESSION privileges!

In both R1 and R2, the RESOURCE role has the following privileges:

• CREATE CLUSTER

• CREATE INDEXTYPE

• CREATE OPERATOR

• CREATE PROCEDURE

• CREATE SEQUENCE

• CREATE TABLE

• CREATE TRIGGER

• CREATE TYPE

This is not meant to be a discussion of security, which is the reason behind the change between the versions, but just an acknowledgment of the change. For the purposes of this application, the one missing privilege in moving from R1 to R2 is CREATE VIEW. Explicitly granting that privilege to the schema owner resulted in a successful import and migration.

Note: This tip was tested using Oracle10g.

Was this tip useful? Did you find any errors? Do you have any suggestions? Do you care? Click here for the tip feedback page. Thank you.