# Upgrading to Oracle Database 11g - Oracle 11g

Up grading to the new Oracle Data base 11g release is similar to the up grade process in 10g,but it contains more sophisticated pre-up grade checks as well as simplified error management.The changes in Oracle 11g simplify the database up grade process while making it faster.Since both the DBUA and the manual up grade processes utilize the same scripts,such as the

utlu111i.sql and utlu111s.sql scripts,the improvements are common to both methods.The pre-upgrade and the post-up grade scripts in Oracle 11g work the same way as they did in the Oracle 10g version.

The default compatibility value for Oracle Database 11g version 11.1 is 11.1.However, you can upgrade to Oracle Data base 11g with a minimum compa tibility value of 10.0.0.Before you perform the data base upgrade,you must set the compatibility level for your new data base by setting a value for the initialization parameter compatible.If you omit this parameter altogether from the new initialization parameter file,it defaults to 11.1.0. Oracle recom mends you use the minimum value for the compatible para meter during the up grade (10.0.0).This way,if your up grade doesn’t go well for some reason and you have to back out of the upgrade process,the upgraded data base won’t become incompatible with your previous (10g.x) release

If you upgrade to 11.1 and keep the compatible parameter at 10.0.0.0, only a small portion of the new features,limited to those that don’t make in compa tible data base structures on disk, will be allowed.The vast majority of the 11g features will make these permanent changes,however,so the lower compati bility level dis ables those new features.Once you confirm that the upgrade did finish successfully, you can change the value of the compatible parameter to 11.1 or greater,depending on the software release you installed.Just re member that once you do this and restart the data base,you can’t downgrade to the previous release.

Before you increase the setting of the compatible initia lization para meter,back up the database,and either edit the initialization parameter (compatible=11.1.0, for example)or, if you’re using the server parameter file, use the following statement to make the change:

Once you change the value of the compatible parameter, shut down the data base(shutdown immediate),and restart it with the startup command.To go back to the old compatibility level after this if something doesn’t work right,you must return to the backup you made of the pre-up grade database.

Whether you can directly upgrade your current Oracle database to Oracle 11g or have to perform an upgrade to an intermediate release first depends on your current Oracle data base release.Oracle supports a direct upgrade to Oracle Database 11g Release1, if you’re migrating from a 9.2.04 or newer release of the Oracle database software.If you want to upgrade to Oracle Clusterware 11g release 1(11.1),then you must upgrade from an Oracle Cluster ware release 10.2.0.3 or newer.Here’s a summary of the upgrade path to Oracle 11g for Oracle database releases older than 9.2.04.

As you can see, some of the older versions have to be migrated to multiple intermediate releases before you can upgrade to Oracle Data base Release 1 (11.1).As in the Oracle Data base 10g release,you can upgrade with the help of Oracle-provided scripts or use the DBUA to simplify matters.For certain data bases,you can also consider using the Data Pump Export and Import utilities as well as the create table as select(CTAS)statement to copy all or part of a data base into a new data base created with the Oracle Data base 11g server software.

You can upgrade an Oracle 8i,Oracle 9i,or Oracle Data base 10g client to the Oracle 11.1 release.You can use the Oracle 11.1 client to access any Oracle 8i,Oracle 9i, Oracle Data base 10g,and Oracle Data base 11g (11.1)data bases.

In the following sections,we’ll review the changes in the manual up grade first and then look at up grading with the DBUA.

A manual upgrade of an Oracle data base involves executing a set of Oracle-provided SQL scripts that are stored in the $ORACLE_ HOME/rdbms/admin directory.The first script you run before star ting the upgrade process is the utlu111i.sql script, also called the Pre-Upgrade Information Tool.In Oracle Data base 11g,this utility provides more information than before to help you during the upgrade process. If you’re upgrading from Oracle Database 10g to Oracle Data base 11g, you must copy three scripts (utlu11i.sql,utilu111s.sql, and utlu111x.sql)from the 11g data base’s file system ($ORACLE_ HOME/rdbms/admin/)to a staging directory on the 10g data base’s file system.

Before you can actually run the up grade script provided by Oracle,you must gather information regarding the up grade requi rements by running the Pre-Up grade Information Tool(invoked by the utlu111i.sqlscript).Once you make sure you’ve taken care of all the warnings and recommendations made by the Pre-Up grade Infor mation Tool,you can up grade data bases to the Oracle Data base 11g release using the same type of scripts as in the previous releases.In this case,the data base up grade scripts are named in the following way:

• utlu111i.sql: This is the previously described pre-upgrade script.
• catupgrd.sql: This is the actual upgrade script and is similar to the script in previous releases.The major change now is that it has been restructured to support parallel upgrades of the database.
• utlu111s.sql: This is the upgrade status utility script, which you invoke after completing the data base up grade.

Here’s a brief summary of the manual direct up grade process from a 10.2 Oracle release data base to the Oracle 11.1 release. Remember that Oracle supports a direct upgrade to the Oracle Data base 11g release from a 9.2.0.4,10.1, or 10.2 release data base. You must log in as the owner of the Oracle 11.1 release Oracle home directory.

1. Log in as the owner of the Oracle 11.1 release Oracle home directory, and copy the utlu111.i sql file from the $ORACLE_HOME/ rdbms/admin directory to another directory such as /tmp. 2. Log in as the owner of the Oracle home directory of the data base you want to up grade,and run the utlu111.i sql script (from the/tmp directory,where you copied it to) to get the pre-up grade information such as the necessary initialization parameter changes,table space space requirements, and so on.Spool the results of the script execution so you can review the output later.for example, the following is a run of the utlu111i.sql script on one of our own systems 3. Note that the utlu111i.sql script(also called the Pre-Up grade Information Tool)will estimate the size requirements for the system and sysaux table spaces. However,these table space size estimates may sometimes be unrealisti cally small.To avoid problems during the up grade,Oracle recommends you set one data file in each of these table spaces to extend auto matically during the up grade(you can do this by using the auto extend on maxsize un limi ted clause).The Pre-Up grade Information Tool shows that in this particular case, there are no changes necessary before the upgrade to the Oracle 11g release.In general,the Pre-Up grade Information Tool will recommend the following: • Removing obsolete initialization parameters • Adjusting initialization parameters • Adding space to key tablespaces such as system and sysaux 4. Shut down the data base cleanly (with the shutdown immediate command).If you’re using a Windows system,stop the Oracle service first using the net stop command,and then delete the service using the oradim utility.Use the oradim utility from the Oracle Data base 11g release to create a new Oracle Data base 11g release instance. 5. Back up the database before starting the upgrade. 6. Make the necessary initialization parameter changes,remove any para meters shown as obsolete by the Pre-Up grade Information Tool from the current init.ora file,and move that file to the new Oracle home under 11g.Ensure that the initialization para meter compatible is set to 11.1.If you are using a pass word file,you must move it to the new Oracle 11g Oracle home. 7. Change the environ ment variables ORACLE_HOME, PATH,and LD_LIBRARY_PATH so they point to the new Oracle Data base 11g Release 1 (11.1) directories. Also, make sure you set the ORACLE_SID variable correctly to point to the data base you’re up grading. 8. Log in to the server as the owner of the Oracle home dire ctory for the new Oracle Data base 11g release.Start SQL*Plus after first changing to the new 11g$ORACLE_HOME/rdbms/admin directory.
9. Start up the data base in up grade mode as shown here after logging in as a user with the sysdba privilege: SQL> startup up grade pfile=$ORACLE_ HOME/ dbs/initorcl.ora We’re assuming you’re upgrading from a 10.x release to the Oracle Data base 11g release. If you’re upgrading from an Oracle Database 9i (Release 2)version, you must first create a sysaux tablespace. 10. Set up spooling so you have a log of the up grade:SQL>spool up grade. log 11. Run the up grade script,catupgrd.sql,located in the$ORACLE_ HOME/rdbms /admin directory,to up grade the database to the Oracle 11g release:SQL> @cat upgd.sql The catupgd.sql script calls various up grade scripts and shuts down the data base after fini shing the up grade.It may up grade or install several data base components.
12. Once the upgrade script finishes successfully,restart the data base in normal mode (not in up grade mode) in order to initi alize the system param eters:SQL>startup Start ing up the data base after the completion of the data base upgrade clears the buf fers and caches and ensures the integrity and consistency of the up graded data base.
13. Run the utl111s.sql script to view the results of the upgrade: The utlu111s.sql script,also called the Post-Up grade Status Tool,shows the status of various data base components following the up grade. In the example shown here,all data base components have migrated successfully,with a VALID status. If you see any errors during the up grade process,you can rerun the catupgrd. sql script as many times as necessary.Most of the time,any errors are because of problems such as an inade quate shared memory or lack of room in a table space.If you see a status of INVALID for any of the data base components,run ning utlrp.sql as shown in the next step will most likely change the status to VALID.
At this point,you must reset any pass words you want (see the following note)and set thresholds for tablespace alerts,because the upgraded database disables all table space alerts (the alert threshold is set to null).

You can use the DBUA to effortlessly up grade a pre-Oracle Data base 11g data base to the 11g release.The DBUA now uses the new pre-up grade script for estimating disk space,providing warnings, and so on.The DBUA works pretty similarly to the way it did in the Oracle Data base 10g release.You just have an additional screen now that asks you to specify the diagnostic directory location.If you specify that you’re performing a data base up grade after installing the Oracle Data base 11g software,the DBUA starts up automatically after you install Oracle Data base 11g.Otherwise, you can manually invoke DBUA when you want to up grade a data base to the Oracle Data base 11g release.

For the past several major releases,Oracle has committed a huge amount of time and resources to the fine-tuning of the DBUA.The DBUA has become a reli able option and the “one-stop shop” for simplified up grades,espe cially if you have lot of special data base options installed in the data base such as Oracle Text,Java, Advanced Replication,and Streams.The DBUA lets you up grade both the data base instance and the ASM instance simultaneously, whereas with a manual method,you must up grade the ASM separately from the data base.

Whether you use the manual method or the DBUA to up grade,you can use several new Oracle Data base change management features to test the performance of the upgrade to the new release:

SQL Performance Analyzer (SPA):You can use the SQL Perfor mance Analyzer to predict the impact of any change,such as an up grade to a new release,on the SQL workload as captured by a SQL tuning set. We discuss the SPA later.
• Data base Replay: You can use the Data base Replay feature to test the impact of the data bas up grade on the production work load by capturing and replaying the production workload on a test system before you actually perform the up grade on your production data base.We discuss the Database Replay feature later.
• SQL Plan Management:The SQL Plan Management feature relies on the use of SQL plan base lines,which represent efficient execution plans.When you adopt SQL Plan Management pursuant to a data base up grade,only those SQL plans are used that don’t result in a performance regression.

Of course,besides the new change management tools described here,you can also use traditional volume and load testing to pre dict the impact of the data base upgrade under reallife conditions.

You can down grade to the major release from which you upgraded to 11g.For example,if you up graded from 10.1 to 11.1,you can down grade only to 10.1,but not to the 10.2 release.Once you up grade to Oracle Data base 11g Release 1(11.1),you can down grade back to release 10.2 or 10.1,whichever you’ve up graded from.If your original Oracle data base release number is lower than 10.2. 0.4, however,you must install the latest patch set for release 10.1.

Here are the steps for down grading from the Oracle Data base 11g Release1 (11.1)(you must be in the 11.1 environment before starting the down grade process):

1. Start the instance in down grade mode:SQL>start up down grade pfile= spfileorcl11.ora
3. Execute the downgrade script,catdwgrd.sql,located in the $ORACLE_HOME/ rdbms/admin directory. SQL>@catdwgrd.sql Once all components are successfully downgraded, the data base down grade will be completed. 4. Turn off spooling once down grading of the data base is completed: SQL> spool off If you find any errors in the spool file down grade log,you can rerun the down grade script catdwgrd. sql as many times as necessary. 5. Shut down the instance: SQL>shut down immediate; 6. Restart the data base after changing all environment varia bles to point to the correct directories for the release to which you’re down grading. Rolling Upgrade Enhancements A rolling up grade lets you up grade a data base to a new server release or apply patches, all while the data base is on line.Obviously, a rolling up grade lets you avoid down time, because it means you can apply patches to a running,“live” data base.In Oracle Database 10g, Oracle offered rolling up grades to Oracle Data Guard (and logical standby data bases) and Oracle Streams.In addition,you could also apply individual patches online for ORAC data bases.In Oracle Data base 11g, Oracle takes the rolling upgrade capability further by extending it to ASM-based data bases.You can now perform a live up grade of any ASM software from the Oracle Data base 11.1 release to a newer release(11.2 and so on). The rolling up grade of ASM data bases means you can continue to use all the features of a clustered ASM environment,even while you are performing a rolling upgrade of one of the nodes in the cluster. Please see Chapter 9 for more on rolling up grades in Oracle Data base 11g. You can now also perform an Oracle Cluster ware rolling up grade, but you ’re limited to applying patch set up grades.During the patch set up grade, all instances of the Oracle RAC install ation except the instance being up graded continue to be available to users, thus reducing the total down time during the up grade to the new patch set release.We discuss the Oracle Cluster ware up grade in the next section. Oracle Clusterware Upgrade Oracle Cluster ware is the heartbeat of the RAC ecosystem. Oracle continues to make the up grade procedures for Cluster ware simpler with every release.The up grade procedures for 10g to 11g is seamlessly straight for ward.If you are familiar with the up grade process from 10g Release 2 to 10g Release 2 patch set 3,you’ ll find they are identical to the upgrade procedures here. The Cluster ware up grade is done in place.Un like the ASM or data base up grade process where you install the binaries into a separate directory and run the DBUA utility, the Clusterware up grade occurs in the source Cluster ware’s ORACLE_HOME.Since you are running RAC for high availability considerations, you should perform rolling up grades of the Cluster ware stack.The following sections provide detailed instructions with screen shots of the Cluster ware up grade proce dures from 10g Release 2 to 11g.Further more,the up grade is performed in a rolling up grade fashion. Preparation for Upgrade Before you can run the installation and up grade your cluster ware,you must first prepare your environment.First,the cluster ware should be shut down completely.Next, you must run the supp lied preup date.sh script to reset the file permissions.This script attempts to shut down Clusterware also, but the recommendation is to perform a clean shutdown manually prior to this step. You can comment out the last three lines of /etc/inittab so that the CRS daemons will not try to restart: Now, you need to manually stop all the CRS background processes: Once you receive confirmation that the shut down sequence has begun, you can validat that the processes are down using this script:[root@rac1 upgrade]# ps-ef|grep-v grep| egrep-i "crs| css"There should be nothing returned from this script. Execute the Pre-update Script In the up grade directory from the CD software,run the pre up date.sh script as root.The up grade directory is below the cluster ware directory. You will notice that the CRS shut down attempt is made.Also, behind the scenes,it is changing the file and directory per missions to Oracle so that you can perform an up grade.If this script is not executed prior to the up grade,you will receive an error that resembles this: Install and Upgrade Using runInstaller Now let’s proceed with the actual up grade.The up grade process starts by executing the runInstaller executable from the CD software:$./runInstaller

When you click Next,it will take you to the Specify Home Details page where you can confirm the source Clusterware home that needs to be up graded to Oracle 11g Clusterware,as shown in Figure.

Clusterware upgrade, Specify Home Details page

When you click Next, you will be directed to the screen shown in Figure to select th nodes you want to upgrade.For rolling up grades,you should select only the node you are up grading.

Clusterware upgrade, Specify Hardware Cluster Installation Mode page

Once you pick the node(s),you can continue the upgrade with the system prerequisite checks,as shown in Figure.In this section, it is critical you make sure the overall status is Succeeded!Once all the patches,swap space,memory, networks,CSS,OCR,voting disk, and so on are validated,you can continue.The upgrade process will take you to the Summary page,as shown in Figure.

Once the installation is complete, you will be directed to the dialog box shown in Figure to run the rootupgrade script from the \$ORA_CRS_HOME/install directory.

As prompted,execute the root up grade script.The script will yield the following output:

[root@rac1 install]#./rootupgrade
Checking to see if Oracle CRS stack is already up...
copying ONS config file to 11.1 CRS home
/bin/cp:/apps/oracle/product/CRS/opmn/conf/ons.config'and
/apps/oracle/p
/apps/oracle/product/CRS/opmn/conf/ons.config was copied successfully to /ap
WARNING: directory'/apps/oracle/product' is not owned by root
WARNING: directory'/apps/oracle' is not owned by root
WARNING: directory'/apps' is not owned by root
Oracle Cluster Registry configuration up graded successfully
`

Attempting to start Oracle Cluster ware stack

Your cluster ware may not autostart and may fail with the cluster verify utility (cluvfy).This is an optional requirement, and you can ignore the failure.You should confirm that there is nothing wrong if you get this error. Figure shows the Configuration Assistants page,with the Failed status for the Oracle Cluster Verification Utility.

Clusterware up grade, Configuration Assistants page

A simple bounce of the server can resolve this issue.The Next button will take you to the final screen of the up grade,as shown in Figure.

Clusterware upgrade, End of Installation page

Once the successful up grade is complete and the CRS stack is back up and running,you can validate your rolling cluster ware up grade:

Upgrade Remaining Nodes of the RAC Environment

Repeat the same steps we showed here,starting from the “Prepare for Up grade” section for every node in the cluster.once the Clusterware up grade is complete across all RAC nodes inthe cluster, you can confirm that the Oracle Cluster ware active version is the same as the soft ware version:

The output shows the Oracle Clusterware version for each RAC node.