Upgrade previous releases of Oracle 10g

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

Upgrade previous releases of Oracle 10g

Rating:

A database can be upgraded from a previous version to use the new features that it offers by following the upgrade process. It is recommended that first use a test database for planning and testing the database and thoroughly check the various application features. Oracle 10g has the following application features:

1. Oracle provides Database Upgrade Utility (DBUA) to upgrade a database. It is Graphical User Interface (GUI) based. This is Oracle's preferred method.


2. Manual upgrade is done directly by running scripts.


3. It provides export/import utilities to copy data to a new Oracle 10g database.


4. It Copies data to new Oracle 10g database using SQL tools.

Following are the steps involved in database upgradation:

1. Identify the supported upgrade options available for the database.


2. Identify and decide the method to upgrade the database.


3. Check whether the database can be readily upgraded.


4. Upgrade the database.

The following releases of Oracle can be directly upgraded:

1. Oracle 8i Release 8.0.6


2. Oracle 8i Release 8.1.7


3. Oracle 9i Release 1 - 9.0.1


4. Oracle 9i Release 2 - 9.2.0

For upgrading an 8.1.5 or 8.1.6 release to Oracle 10g first the database must be upgraded to Oracle 8i Release 8.1.7. To upgrade 7.3.4 database to Oracle 10g the database must be upgraded to 9.2.0. The upgrade steps and the path depend on the version of the database chosen.

Oracle 10g provides a script utlu101i.sql for performing validation actions on the database that has to be upgraded. It is automatically run by the DBUA for the upgrade process. In case of errors it takes corrective actions. It is located in the directory /rdbms/admin. Before performing a manual upgrade the utlu101.sql script needs to be run as SYSDBA. It is recommended to copy this script to a temporary folder and then run it after spooling the output to a file. It must be run on the database to be upgraded. The script does the following actions:

1. Checks the compatibility of the database.


2. Estimates the upgrade time.


3. Finds the installed components.


4. Finds the estimates of tablespace sizes.


5. Verifies the database compatibility.


6. Finds all the installed components.


7. Identifies the obsolete, special, or renamed parameters.


8. Verifies that the redo log file size is 4 MB at the minimum.


9. It checks for the installed database options.


10. For certain parameters, it upgrades the new values.


11. It looks for the default tablespace for each database component schema.


12. In case the SYSAUX tablespace exists it warns the users about the wrong properties and displays the required properties of SYSAUX tablespace.


13. Verifies that the national character set and database character set are supported by Oracle 10g.


14. Checks the installed database options.

Oracle provides the following ways to upgrade an old version to Oracle 10g:

1. Using DBUA.


2. By running scripts using Command-line SQL*Plus.


3. Manual upgrade.

In the previous releases a user had to run different scripts based on database options whereas in Oracle 10g the component that are to be upgraded are determined automatically and are executed in the right order of dependency. It has only one script upgrade which upgrades all the components automatically.

Using the DBUA: The database can be updated by following the steps given below

1. Choose the database to be upgraded. However, only one database can be upgraded at a time and it should be in running mode.


2. The SYSAUX is created in the database. The size and location of the SYSAUX tablespace should be specified. Although the recommended size is 500MB. The other properties of the SYSAUX tablespace cannot be changed.


3. During the upgrade process many objects become invalid. Choose whether to recompile the invalid objects at the end of the upgrade. If this option is chosen the DBUA runs the utlrp.sql script.


4. Take the backup of the database. In case the backup is performed by the DBUA the backup is taken on a directory on the server. This backup is in uncompressed form. The DBUA also creates a script called <dbname> BACK.BAT to restore the database.


5. Configure the database with the EM by choosing its option.


6. Schedule backups.


7. Provide a location and size for the flash recovery area. This window is displayed if the database backup option has been chosen in the previous window.


8. Provide SYSMAN and DBSNMP as the passwords for the EM administrative accounts.


9. After the upgrade is complete a window appears displaying the result of the upgrade. The option of choosing passwords and restoring the database appears. For security all the user accounts are locked. The user has to click Configure Database Parameters button for unlocking the accounts and allotting new passwords. The DBUA can be used to put the database back to its original settings.

Using the DBUA command line:

By using the DBUA command line several parameters can be specified. On specifying any parameter with the DBUA command the command line is invoked. -h is used for help.

The DBUA Command-Line Parameters are given below:

-dbName	Name of the database to be upgraded.
-backupLocation	The directory name where the database should be backed up before upgrading.
-initParam	Specify comma-separated values to start the database upgrade.
-silent	Enables the silent upgrade of the database.
-disableUpgradeScriptLogging	It disables logging during the upgrade.
-PostUpgradeScripts	Specify the file names, which are run after the upgrade.
-emConfiguration	It specifies the database management mode, i.e. database configuration or grid configuration.

Upgrading the database manually

Manual upgrade is done by running scripts using the SQL*Plus utility. The steps for the manual upgrade are given below:

1. Connect to the database.


2. Run the script utlu10.sql to determine that the pre-upgrade steps are completed.


3. Resize the redo log files to more than 4 MB.


4. Adjust the tablespace sizes.


5. Perform cold backup of the database.


6. Shutdown the database.


7. Copy the parameter and password file from the old Oracle home directory to the Oracle 10g directory.


8. Check that all the environment variables are correctly set to reference the Oracle 10g home.


9. Connect to the database using the SYSDBA privilege by using SQL*Plus.


10. Create a SYSAUX tablespace.


11. Run the upgrade script.


12. Run the utlu101s.sql script with the TEXT option. It determines the upgrade status and provides information about incorrect or incomplete component upgrades by querying the DBA_SERVER_REGISTRY.


13. Shutdown and restart the instance. Perform a normal shutdown. This is important in order to maintain the integrity and consistency of the upgraded database.


14. Run the utlrp.sql script to compile the invalid objects again.


15. Update the listener.ora with the information about the new database.


16. Backup the database.

Rating: