Monday, October 19, 2009

Upgrade Oracle database from 10.2.0.x to 10.2.0.4

Follow the simple steps to upgrade your database from 10.2.0.x to 10.2.0.4

Here are the Upgrade paths:-
Upgrade 10.2.0.1. to 10.2.0.4
Upgrade 10.2.0.2. to 10.2.0.4
Upgrade 10.2.0.3. to 10.2.0.4

If you have updated your Oracle version then you must upgrade your database using any one of the below methods, otherwise you will get the following error during Database startup.

ORA-01092: ORACLE instance terminated.
ORA-39700: database must be opened with UPGRADE option

1. Database Upgrade assistant
2. Database Upgrade assistant in Noninteractive mode
3. Manual Upgrade Method

1. Upgrade Database using Database Upgrade assistant:
Step 1: Set the ORACLE_HOME and ORACLE_SID and start DBUA
$ ORACLE_SID=testdb; export ORACLE_SID
$ ORACLE_HOME=/oracle/v10204; export ORACLE_HOME
$ dbua

Step 2: Follow the upgrade steps
(i) Select the database that you want to upgrade
(ii) On the Recompile Invalid Objects screen, select the Recompile the invalid objects then click next.
(iii) On the backup screen, select “I would like to take this tool to backup the database”, if you have not taken database backup.
(iv) Click finish

2. Upgrade Database using Database Upgrade assistant in Noninteractive mode:
Run the following command to upgrade Oracle Database in silent mode:
$ dbua -silent -dbname $ORACLE_SID -oracleHome $ORACLE_HOME
-sysDBAUserName UserName -sysDBAPassword SYS_password
-recompile_invalid_objects true

3. Upgrade Database using Manual Upgrade Method:

Step 1: Backup the database.
Backup the databases using RMAN or command line file level backup

Step 2: Set the ORACLE_HOME and ORACLE_SID and run Pre-upgrade checks
$ ORACLE_SID=testdb; export ORACLE_SID
$ ORACLE_HOME=/oracle/v10204; export ORACLE_HOME

SQL> SPOOL Pre_checks.log
SQL> @?/rdbms/admin/utlu102i.sql
SQL> SPOOL OFF

Review the spool file and make the necessary database/parameter changes based on the recommendation.

If you are upgrading a RAC database, then set CLUSTER_DATABASE to “FALSE”.

Step 3: Start the database in UPGRADE mode and run catupgrd.sql
SQL> STARTUP UPGRADE
SQL> SPOOL upgrade_db.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF

Review the log for errors.

Step 4: Restart database and recompile invalid objects
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> @?/rdbms/admin/utlrp.sql

If RAC database, then set CLUSTER_DATABASE to “TRUE” after upgrade.

Step 5: Check components status after upgrade
Run the following command to check the status of all the components
SQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;

The status of all the components should be VALID for a successful upgrade

Step 6: Upgrade RMAN  Catalog
If you are using the Oracle Recovery Manager catalog, enter the following command:
$ rman catalog username/password@alias
RMAN> UPGRADE CATALOG;

Step 7: Startup all other processes and services.

Click here for steps to upgrade oracle version to 10.2.0.4

Regards,
Satishbabu Gunukula
http://www.oracleracexpert.com/

3 comments: