Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Feb 11, 2009

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:

utlirp.sql - UTiLity script to Invalidate Pl/sql modules

At the top of utlip.sql, you’ll see:

utlip.sql - UTiLity script to Invalidate & Recompile Pl/sql modules

(Note to Oracle: you got the names backwards, plus neither recompiles.)

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:

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 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.

» See All Articles by Columnist Steve Callan



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM