Upgrades and migrations are fairly common events in the
lifecycle of an Oracle database. Todays 11g database may have started off
under version 7.3.4, and one of the intermediate steps in this path probably
included a change from 32-bit to 64-bit Oracle. If following one of several
options as far as the upgrade method is concerned, the step to turn a 32-bit
database into a 64-bit was done for you, directly or otherwise. For example, if
you were using 32-bit 22.214.171.124 and went to 64-bit 126.96.36.199, you may have used
export/import or the upgrade assistant. Either way, the change in word size was
handled for you. In a probably less frequent scenario, although entirely
possible, what has to be done to upgrade to 64-bit while staying at the same
In one case, you may not be changing servers, so the
installed software (32-bit) is being replaced with the 64-bit version of
Oracle. In another case, you could be moving a database from a 32-bit server to
a 64-bit server. Without any other changes in the version of Oracle, you (the
person doing the migration) are responsible for invoking the internal change
process or script that takes care of turning your 32-bit database into a 64-bit
version. At a high level, this is actually a pretty simple procedure. At a
detailed level, there are some gotchas to watch out for, plus as you can
expect with respect to lots of Oracles instructions, there are some
inconsistencies you are left to sort out on your own.
For the purposes of this article, Ill assume the
change/upgrade path is going from 32-bit to 64-bit, although the reverse is
just as true.
At a high level, all you need to do is take a backup, install
the 64-bit version, point your database and environment to the new version, startup,
run a script (actually two, maybe three), and youre done. Lets look at the
individual steps in this process.
The very first step of all should be to ensure you have a
good backup. Since youll be shutting down the source database anyway, what an
opportune time to take a cold backup. If using a same-server scenario, youll
want to copy the files to a separate location (or do whatever you do via RMAN).
If using a new-server scenario, copy the files to the new server. This may be
an opportunity to brush up the UNIX rcp command and configure an .rhosts
file. The original files will be on the old server, so that is your safe backup
location for free in case something goes wrong (you can always revert back to
what you had to begin with).
Probably the hardest part is installing the new(er) version
of Oracle in the desired word size. In a same-server scenario, that means
having at least two ORACLE_HOMEs installed, so that means your environment needs
to be configurable for both versions, and more precisely, having the paths
related to the version you want to work with be accurate. As a tip for UNIX
users, create a second .profile or .cshrc file (or whatever shell you use)
based on the paths for the new 64-bit ORACLE_HOME, and upon logon as oracle,
source the resource file of interest. Using this approach preserves your
original (and known to be good) environment setup, and it should be trivial for
the most part to create a similar resource file for the new environment.
The pointing step is nothing more than using the new
environment setup and having the databases initialization parameter available
to the new ORACLE_HOME (assuming you are not moving files around, which Ill
cover in a bit).
Startup is almost the same as normal you need to ensure
nothing else (i.e., no one else) is going on in the database. Depending on what
you read where, startup restrict works, or it may require the use of migrate or
upgrade. The main thing is that no one or nothing (other than you and what you
will be doing) has the ability to execute any DDL.
The driver script which facilitates the upgrade process is
named utlirp.sql (in the $ORACLE_HOME/rdbms/admin directory). This script, like
many others in the admin directory, calls another script closely or similarly
named. In this case, utlirp.sql calls utlip.sql. The main function is to
invalidate PL/SQL so that it can be momentarily later recompiled using the new
The names of the scripts are meant to be suggestive as to
their purpose, but in typical Oracle manner, the notes or remarks within the
scripts leave something to be desired with respect to accuracy and
proofreading. At the top of utlirp.sql, youll see:
utlirp.sql - UTiLity script to Invalidate Pl/sql modules
At the top of utlip.sql, youll see:
utlip.sql - UTiLity script to Invalidate & Recompile Pl/sql modules
(Note to Oracle: you got the names backwards, plus neither
The utlip.sql is easy to understand in terms of what does
it take to invalidate PL/SQL modules? As you can see most of the work is based
on updating the SYS-owned obj$ table and setting a status flag. A series of
four deletes takes place, and the approach Oracle takes to deleting is
interesting in of itself.
All that stuff youve read about not committing in loops as
that can contribute to ORA-01555 errors? When it comes to what Oracle does
internally, that rule doesnt apply. The reasoning (as shown in a 10g version
of the utlip.sql script) is that the DELETEs are coded in chunks using a
PL/SQL loop to avoid running into rollback segment limits. Interesting.
In several other documentation sources, youll see
references to utlirp.sql also taking care of compiling invalid objects. But,
within the script notes, it clearly states that YOU must run utlrp.sql.
Rem Please note that
this script does not recompile invalid objects
Rem automatically. You
must restart the database and explicitly invoke
Rem utlrp.sql to
recompile invalid objects.
Rem To use this script,
execute the following sequence of actions:
Rem 1. Shut down the
database and restart in UPGRADE mode
Rem (using STARTUP
UPGRADE or ALTER DATABASE OPEN UPGRADE)
Rem 2. Run this script
Rem 3. Shut down the
database and restart in normal mode
Rem 4. Run utlrp.sql to
recompile invalid objects. This script does
automatically recompile invalid objects.
So, as another tip, do this: before running the utlirp.sql
script, obtain a listing of what is currently invalid (and by owner and type)
within your database. After the script and utlrp.sql are run, do a comparison.
Not that you would want to normally keep invalid objects hanging around in your
database, but what if you inherited a legacy database, and thats just how things
are? The maintenance is a separate issue, the here and now issue is to ensure
that no valid object is left behind, so to speak. One of the steps in a support
note lists running an after check, but nothing about a beforehand check. What
are you supposed to compare the end result to if you dont have a baseline?
As a twist in the new-server scenario, lets suppose youre
moving from one server to another, with new/target server already having the
new bit size in place. In effect, youre moving the database from a 32-bit
server to a 64-bit server where an Oracle database (instance) is already
running, with the end result being that the target server will now have
multiple instances running on it. How do the files map to the new server? If
the same paths exist AND there is enough space on the target paths, then no
problem. If the paths exist BUT there is not enough space, then youll probably
wind up having to move the files from one path to another (i.e., new) on the
According to Oracle support, issuing the command to rename a
file can be done in the new ORACLE_HOME environment prior to running the
utlirp.sql script. This means that after having copied the files to their new
path/location on the target server, that you STARTUP MOUNT and issue:
alter database rename file original path and name to new path and name;
From there, you can shutdown and startup <whatever>
depending on the version of Oracle youre using.
Where to find more information
Aside from what is shown in the upgrade or migration
documentation, do a search on My Oracle Support for two phrases. One is for
word size and the other is for word-size. Youll get a different set of
results because of the hyphenated versus non-hyphenated usage. The main note of
interest is Doc ID 62290.1, Changing between 32-bit and 64-bit Word Sizes.
Depending upon your destination version, Doc ID 412271.1, "ORA-600  and
ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases to 10.2.0.3"
may be of huge interest to you as well, but is never mentioned in the first
note which was (as of this writing) last updated on 03-FEB-2009.
The problem here is that if your original version started as
32-bit, going to 64-bit 10.2.0.3 can fail (64-bit to 32-bit can also encounter
the bug). Note 412271.1 lists an action plan that may be applicable to you, but
not all platforms are covered (most are, one notable exception being Windows).
Patches are available, but be sure to fully understand which patch applies in
Other bugs are listed in the 62290.1 note, but not all of
them are visible to the public (Hello, Oracle, then what is the point of
mentioning the bug in the first place?). Some modifications to a couple of
initialization parameters are also recommended in this note (you may want to
create a PFILE just prior if using an SPFILE). Additionally, if using later
versions of Oracle, you may need to increase (double is the suggestion) the
sizes of the following items:
See Doc ID 209766.1, Memory Requirements of Databases
Migrated from 32-bit to 64-bit. Then again, you may not have to deal with
these settings at all given the use of SGA_TARGET. This note makes no reference
to this 10g and later parameter, even though it was last updated 04-JUL-2008.
Overall, changing the word size appears to be pretty simple,
but this is one of those situations where doing some additional digging into
support notes can alleviate to outright prevent some problems down the road. It
is definitely one of those scenarios where you are left to figure out things on
your own as there is no one definitive source or complete guide.
See All Articles by Columnist Steve Callan