Home > Oracle > Upgrading a database manually from 11.2.0.2 to 11.2.0.3 linux

Upgrading a database manually from 11.2.0.2 to 11.2.0.3 linux

September 28, 2011 Leave a comment Go to comments

Steps for upgrading a database manually

backup up the database

rman "target / nocatalog"
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name';
}

Prepare the new oracle home

Copy the spfile and password file from $OLD_ORACLE_HOME/dbs to $NEW_ORACLE_HOME

If you are upgrading a cluster database , then set the CLUSTER_DATABASE initialization parameter to false

Upgrade the database

1. Shutdown the instance

SQL> SHUTDOWN IMMEDIATE ;

2. Amend the entry in /etc/oratab for the database you are upgrading to point to the new home
RMANCATPR:/u01/app/oracle/product/11.2.0.3/db_1:N

3. The following environment variables point to the Oracle Database 11g Release 2 (11.2.0.3) directories:
ORACLE_HOME
PATH

4. Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2.0.3) Oracle home directory

5. At a system prompt, change to the ORACLE_HOME/rdbms/admin directory

6. Start SQL*Plus.

7. Connect to the database instance as a user with SYSDBA privileges.

8. Start the instance by issuing the following command:

SQL> STARTUP UPGRADE

9. Set the system to spool results to a log file for later verification of success:
SQL> spool /tmp/upgrade.log

10. Run the Pre-Upgrade Information Tool by executing the utlu112i.sql script:
SQL> @utlu112i.sql

11. Run the catupgrd.sql script:
SQL> @catupgrd.sql

12. Restart the instance to reinitialize the system parameters for normal operation.
SQL> STARTUP

13. Run utlu112s.sql, the Post-Upgrade Status Tool, which provides a summary of the upgrade at the end of the spool log.
Run utlu112s.sql to display the results of the upgrade as follows:
SQL> @utlu112s.sql

14. Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require
the database to be in UPGRADE mode:
SQL> @rdbms/admin/catuppst.sql

15. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code.
SQL> @utlrp.sql

Verify that all expected packages and classes are valid:
SQL> SELECT count(*) FROM dba_invalid_objects;
SQL> SELECT distinct object_name FROM dba_invalid_objects;

16. Exit SQL*Plus.

17. If you are upgrading a cluster database from releases 10.2, 11.1, or 11.2.0.1, then upgrade the database configuration in Oracle Clusterware using the following command:

$ srvctl upgrade database -d db-unique-name -o oraclehome
where db-unique-name is the database name assigned to it (not the instance name), and oraclehome is the Oracle home location in which the database is being upgraded.

Rerunning the Upgrade

You can rerun the upgrade with the catupgrd.sql script as described in the following steps.

To rerun the upgrade

1.Shut down the database as follows:

SQL> SHUTDOWN IMMEDIATE
2.Restart the database in UPGRADE mode:

SQL> STARTUP UPGRADE
3.Set the system to spool results to a log file for later verification of success:

SQL> SPOOL upgrade.log
4.Rerun catupgrd.sql:

SQL> @catupgrd.sql
Note:

You can rerun the catupgrd.sql script as many times as necessary. The first time you run the script, there should be no error messages returned. If you rerun the script, then the ORA-00001 message is displayed. You can safely ignore this message.
5.Rerun utlu112s.sql:

SQL> @utlu112s.sql

 

 
Cancelling the Upgrade

If you completed the steps in “Backing Up the Database” to back up your database,
then the easiest way to cancel the upgrade is to restore that backup as described in the following procedure.

To cancel the upgrade by restoring the previous backup

1.Log in to the system as the owner of the Oracle home directory of the previous release.

2.Sign on to RMAN:

rman “target / nocatalog”
3.Issue the following RMAN commands:

STARTUP NOMOUNT
RUN
{
    RESTORE CONTROLFILE FROM ‘save_controlfile_location’;
    ALTER DATABASE MOUNT;
    RESTORE DATABASE FROM TAG before_upgrade
    ALTER DATABASE OPEN RESETLOGS;
}

Advertisements
Categories: Oracle
  1. December 21, 2013 at 1:54 pm

    Exclusive step by step Oracle 11g upgrade guide. I got every steps. Thanks for sharing. I impressed to see RMAN commands for backing up and restoring database before and after upgrade.

  2. Ralph
    January 11, 2014 at 12:58 am

    Thanks, Really helped.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: