Featured Database Articles

Changing the Word Size in Oracle

By Steve Callan

Upgrades and migrations are fairly common events in the
lifecycle of an Oracle database. Todays 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 8.1.7.4 and went to 64-bit 9.2.0.1, 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
version level?

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 gotchas to watch out for, plus as you can
expect with respect to lots of Oracles instructions, there are some
inconsistencies you are left to sort out on your own.

For the purposes of this article, Ill 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 youre done. Lets look at the
individual steps in this process.

The very first step of all should be to ensure you have a
good backup. Since youll be shutting down the source database anyway, what an
opportune time to take a cold backup. If using a same-server scenario, youll
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 databases initialization parameter available
to the new ORACLE_HOME (assuming you are not moving files around, which Ill
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
ORACLE_HOME software.

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, youll see:

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 youve read about not committing in loops as
that can contribute to ORA-01555 errors? When it comes to what Oracle does
internally, that rule doesnt 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, youll 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

Rem USAGE

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

Rem not
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 thats 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 dont have a baseline?

As a twist in the new-server scenario, lets suppose youre
moving from one server to another, with new/target server already having the
new bit size in place. In effect, youre 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 youll probably
wind up having to move the files from one path to another (i.e., new) on the
target.

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:

From there, you can shutdown and startup <whatever>
depending on the version of Oracle youre 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. Youll 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 [22635] 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
your environment.

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:

SHARED_POOL_SIZE

SHARED_POOL_RESERVED_SIZE

LARGE_POOL_SIZE

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.

In Closing

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.

Advertiser Disclosure:
Some of the products that appear on this site are from companies from which QuinStreet receives compensation. This compensation may impact how and where products appear on this site including, for example, the order in which they appear. QuinStreet does not include all companies or all types of products available in the marketplace.