Fixing a Shared Object File 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 node.

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:

srvctl config database ?d test

If the database is missing, just add it back in with 9i srvctl:

  srvctl add 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 to 10g

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.

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

2.      Use racenv to set the environment, then startup the database that is about to be upgraded.

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

4.      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, exit sqlplus.

5.      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 srvctl command.

6.      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 file system.

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

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

9.      Each node has a text file that points to the spfile. This file should be moved from the 9i home to the 10g home.

10.  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 upgrade_test_diagnostic.log previously created to make the necessary changes to the parameters. The database will be opened as a non‑cluster database during the upgrade process.

11.  Change directories to the oracle user's home directory. Use oraenv to set the environment. Startup the database with the UPGRADE keyword.

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

13.  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 activity).

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

Now, shutdown the database.

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

Shutdown the database and exit sqlplus

16.  Run the update_tns.sh script to add the database to the 10g tnsnames.ora file. This step must be competed on each node.

17.  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 previous version.

18.  Connect to the idle instance as sys and create the spfile as shown. Exit sqlplus.

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