Fixing a Shared
Oracle Database Tips by Donald Burleson
If a 9i database will be run on a 10g cluster for
an extended period of time, Oracle suggests, in its release notes for
10g (Part number B10817-04), replacing the 10g version of libcmdll.so
with the 9i version.
Figure 10.1 demonstrates backing up the 10g
version of this file and replacing it with the 9i version. This should
be done as root on both nodes. After this is completed, use the 10g
srvctl to stop nodeapps on each node; then start nodeapps on each
Oracle does not explain the reason for this
change, nor does it explain what will happen if it is not done. I have
run 9i databases on a 10g CRS both with and without this step and no
noticeable difference was detected.
If a 9i database is upgraded and no other 9i
databases are to be used, restore the original 10g version of this
file and restart nodeapps on each node.
Registering a 9i
Database using srvctl if necessary
If the CRS has been wiped out and rebuilt by using
the instructions in Appendix G, the previously created 9i instances
will not be included any longer. Use the 9i or 10g srvctl to find out
if the instances are registered:
database ?d test
If the database is missing, just add it back in
with 9i srvctl:
database -d test -o $ORACLE_HOME
srvctl add instance -d test -i west1 -n oracle1
srvctl add instance -d test -i west2 -n oracle2
Upgrading a 9i Database
Oracle provides a graphical program called dbua,
or Database Upgrade Assistant, that can be used to upgrade a database.
If you choose to use dbua, make sure you are not using the 9i version
because its purpose is to upgrade older databases to version 9i. The
9i version and 10g versions look nearly identical unless you carefully
read the welcome screen.
This book will not show a demonstration of the
dbua because it does not teach much about the upgrade process. Also,
the 10g dbua will not work unless a modification is made to the script
that launches the program. The solution for this problem is documented
in Appendix C.
The following table walks through the steps taken
to manually upgrade a 9i database named TEST to a 10g database. Before
running through these steps, it is a good idea to shutdown any other
instances that may be running, including any +ASM instances.
For an upgrade there are times in which you will want to be
certain you are accessing a particular oracle home. Therefore, it is a
good idea to have two variables that are hard coded to these
directories. Add these variables to the end of the oracle user's .bash_profile
as shown. Log out of the operating system and log back in as oracle
Use racenv to set the environment, then startup the database
that is about to be upgraded.
Using sqlplus, login to the database as the sys user. Spool the
results of the upgrade diagnostic script into a file. Even though the
current $ORACLE_HOME is for the 9i directory, you are accessing a
script in the 10g home.
The script will take a minute or so to
produce the results. When it is finished, issue the command spool off
to stop the spooling. At this point, open the spooled file in a text
editor and read its recommendations for upgrading the database. Use
these recommendations as a basis for editing the pfile to support the
Query the database to find the size of each file. This
information will be necessary, especially when making a cold backup of
a datafile on raw. It is possible that one or more datafiles on raw is
now larger than it was when it was created. When you are finished,
Use srvctl to
shutdown the database on both nodes, and remove the database
configuration from the CRS repository. This will make the test
database unavailable to the 9i
Choose the method you prefer for backing up the database. My
preference for a database in noarchivelog mode is a cold backup, as
shown. For the partitions on raw, the
dd copy method should be
used. Copy more bytes of data than the size of the file. In this case,
64000 8k blocks is equal to 500 megabytes. 64 extra blocks are copied
to ensure the file is complete. You will find copying with
dd from raw is much faster
than copying with cp from a
Use the sucommand to switch to the root user. Open the file
/etc/oratab in geditand change the oracle home for the database being
upgraded to the 10g oracle home. Complete this step on each node.
On oracle1, exit the root login. Run the racenv script to set
the environment, which will change the $ORACLE_HOME and $PATH to the
new home. Change directories to the directory for the test database
files. Verify that the orapwd about to be used is the 10g version.
Create a password file as shown. Change to the dbs directory and
create two links to the password file. The second link will be used
when the database is run as a single instance during the upgrade.
From oracle2, exit the root login and
create a link to the password file as shown.
Each node has a text file that points to the spfile. This file
should be moved from the 9i home to the 10g home.
When a database is created with dbca, a text parameter file is
created with a path and name like $ORACLE_BASE/admin/<sid>/pfile/init.ora.nnnnnnnnnnn.
This file will work perfectly for the database to be upgraded.
Copy the file to a new file as shown and
export an environmental variable $PFILE to match the path and filename
of the new file. Then, edit the file with and consult the
previously created to make the necessary changes to the parameters.
The database will be opened as a non‑cluster database during the
Change directories to the oracle user's home directory. Use
oraenv to set the environment. Startup the database with the UPGRADE
Note: you should be using the 10g version of SQLPLUS.
Create the sysaux tablespace as shown. This tablespace is
required for a 10g database. It can be sized from 70 megabytes to 5
gigabytes. For a small test database, 300 megabytes is more than
There are four upgrade scripts available with names that match
the version of the database to be upgraded: u0800060.sql,
u0801070.sql, u0900010.sql, u0902000.sql.
Spool a log of
the database upgrade and run the u0902000.sql as shown. It will stream
by quickly for about an hour. When it reaches the end, it will appear
to hang, but it is still working (use the system monitor to check CPU
When the upgrade has finished, use the
spool off command and scroll
up a short distance to view the results. The STATUS column as shown
should be marked valid with a version 10.1.x as shown.
Startup the database as shown. The
utlrp.sql script is used to
compile all database objects. Before the script is run, the query
reveals that 308 objects are not compiled. After the
utlrp.sql script completes,
rerun the query to determine the count of invalid objects.
the database and exit sqlplus
Run the update_tns.sh
script to add the database to the 10g tnsnames.ora file. This step
must be competed on each node.
Open the parameter file with the command gedit $PFILE
and make the changes as shown. The compatible parameter should be
changed to reflect the upgrade. Once the database is started with the
compatible parameter for this version, it cannot be rolled back to the
Connect to the idle instance as sys and create the spfile as
shown. Exit sqlplus.
Use srvctl to add the database back in as a 10g database.
At this point, it is a very good idea to use
sqlplus to startup and shutdown each instance, one node at a time. No
pfile should be indicated in the startup command. This will ensure all
steps were completed and each node is ready.
If you want to learn RAC at home, get the bestselling book "Personal
Oracle Real Application Clusters" by Edward Stoever.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts.