Manual Upgrade to Oracle 11gR2

If you are a school that uses Banner, Oracle 11.2 is compatible with Oracle Application Forms Server 10.1.2.3 and Banner 8.3+

These are the steps I personally use to manually upgrade a database from 10.2.x to 11.2.0.1 on Linux RH 5 64-bit.

1. Install 11.2.0.1 Oracle software plus CPUs. I am wary of the new PSU’s…they have bit me before. So I am sticking to the old CPUs for now.

Installed the newer ORACLE software in it’s own Oracle home.  I don’t hard code the ORACLE_SID, ORACLE_HOME in the UNIX profile of the OS account that owns the ORACLE software. That allows me to install additional software without affecting production. I set the ORACLE_SID, ORACLE_HOME with oraenv as needed, those environmental variables are set to nothing when I first logon to the box at the command line. Go ahead and set the new ORACLE_BASE environment, this allows you (vs. Oracle) to control where the trace and log files go.

2. Migrate 10.x databases to 11.2.0.1 listener.

I migrate to the higher level listener (11.2.0.1) for all databases on this box. This includes copying over the listener.ora, tnsnames.ora to the newer ORACLE_HOME.  I reset TNS_ADMIN at this point. Note that this step was done in all of the non-production nodes long before  so any version-specific issues have been resolved and dealt with. Migrating to the new listener will require it to be restarted, you may want to hold this step during offhours in case you need to revert back to the 10.2.x version of the listener.

Here is an example of the way I do the listener.ora file. I don’t use port 1521 which autoregisters all existing databases…sorry but no thanks. I want to maintain more control. Since I use a different home for the listener, the ORACLE_HOME is part of the listener to indicate which is current.

LISTENER_DB11 =
(ADDRESS = (PROTOCOL = TCP)(HOST = FullyQualifiedNODENAME or IP address)(PORT = 1524))
SID_LIST_LISTENER_DB11 =
(SID_LIST =
(SID_DESC =
(SID_NAME = DB11)
(ORACLE_HOME = /u01/app/oracle/product/10.2)
)
)
ADMIN_RESTRICTIONS_LISTENER_DB11=ON
LOG_FILE_LISTENER_DB11=listener_DB11.log

3.  Run the PreUpgrade Tool, easy to copy to a temporary location. Be sure and follow any advice for changing database initialization parameters, editing a copy of your 10.2 initORACLE_SID.ora file (which can be tested and edited easily before migrating to an spfileORACLE_SID.ora) .

This is the time to investiage changes to the Deprecated CONNECT role which now has only the CREATE

SESSION privilege; the other privileges granted to the CONNECT role in earlier releases

are revoked during the upgrade.

To identify which users and roles in your database

are granted the CONNECT role, use the following query:

SELECT grantee FROM dba_role_privs

WHERE granted_role = ‘CONNECT’ and

grantee NOT IN (‘SYS’, ‘OUTLN’, ‘SYSTEM’, ‘CTXSYS’, ‘DBSNMP’,

‘LOGSTDBY_ADMINISTRATOR’, ‘ORDSYS’,

‘ORDPLUGINS’, ‘OEM_MONITOR’, ‘WKSYS’, ‘WKPROXY’,

‘WK_TEST’, ‘WKUSER’, ‘MDSYS’, ‘LBACSYS’, ‘DMSYS’,

‘WMSYS’, ‘EXFSYS’, ‘SYSMAN’, ‘MDDATA’,

‘SI_INFORMTN_SCHEMA’, ‘XDB’, ‘ODM’);

If users or roles require privileges other than CREATE SESSION, then grant the specific

required privileges prior to upgrading.

4.  Backup (document) and then remove any password-protected database links.  You will have a problem altering them after going to 11.2 since there have been extensive changes done to the network components and as well the new security enhancements. I have another blog post on this issue if you want more information.

5. Backup your 10.2 database – consider adding a tag while using RMAN in order to denote this was a preupgrade backup.  The following command neatly wraps up all of the archivelogs and controlfile into a single backup.

backup database plus archivelog include current controlfile tag=’Preupgrade_to_11′;

6.  Shutdown 10.2 database, then change /etc/oratab to reflect the new 11.2 ORACLE_HOME, adjust any local modifications done to /usr/local/bin/oraenv file.  EXIT your console completely.

7. Adjust Environment Settings for 11.2 – Logon with a new console command line, set your ORACLE_SID, ORACLE_HOME to the newer 11.2. Create a password file in the 11.2.0.1 $ORACLE_HOME/dbs location, double check the initORACLE_SID.ora file.

8. Startup the database in upgrade mode– after setting the correct ORACLE_SID, double check all enviormental variables.

startup upgrade;

9. Upgrade by running $ORACLE_HOME/rdbms/admin/catupgrd.sql. Be sure and Create a spool file for later review.

10. Post Upgrade Scripts – Run both $ORACLE_HOME/rdbms/admin/catuppst.sql and utlrp.sql files. These can be run concurrently. Also run the $ORACLE_HOME/rdbms/admin/utlu112s.sql (Post-Upgrade Tool).  See the couple of errors I received and the reasons why.

SQL> @?/rdbms/admin/utlu112s

Oracle Database 11.2 Post-Upgrade Status Tool 05-20-2010 14:53:36

Component Status Version HH:MM:SS

Oracle Server VALID 11.2.0.1.0 00:09:20

JServer JAVA Virtual Machine  VALID 11.2.0.1.0 00:05:13

Oracle Workspace Manager ORA-00942: table or view does not exist

. ORA-06512: at “WMSYS.OWM_MIG_PKG”, line 1575

. ORA-06512: at “WMSYS.OWM_MIG_PKG”, line 1592

. ORA-06512: at line 1

. VALID 11.2.0.1.0 00:00:31

Oracle XDK VALID 11.2.0.1.0 00:01:39

Oracle XML Database ORA-04043: object XDB_DATASTORE_PROC does not exist

. ORA-04043: object XDB_DATASTORE_PROC does not exist  VALID 11.2.0.1.0 00:02:41

Oracle Database Java Packages VALID 11.2.0.1.0 00:00:13

Oracle Multimedia VALID 11.2.0.1.0 00:04:11

Oracle Expression Filter VALID 11.2.0.1.0 00:00:06

Oracle Rules Manager VALID 11.2.0.1.0 00:00:06

Gathering Statistics. 00:02:50

Total Upgrade Time: 00:26:54

ORA-04043: object XDB_DATASTORE_PROC does not exist- The package does not exist because Oracle Text was not installed when XML database (XDB) was installed.

Oracle Workspace Manager ORA-00942: table or view does not exist -see the MOS document. DBUA RAISES ORA-942 WHILE UPGRADING EXPRESSION FILTER COMPONENT [ID 1066116.1]

11. Check for newly invalid objects – Double check any objects that were made invalid from the upgrade – there is a script for that.

$ORACLE_HOME/rdbms/admin/utluiobj.sql to display only those objects which are newly invalid as a result of the upgrade process.

12. Post-Upgrade SunGard tasks – If you run SunGardHE Banner, here are the specific vendor recommendations –

alter role ban_default_connect not identified;  –this is due to everyone having this previously password-protected role as default, in 11g password-protected roles CANNOT be default. Workaround is to remove the password.

alter system set SEC_CASE_SENSITIVE_LOGON = FALSE;  –to make the transition to case sensitive passwords a long term project out of the scope of this particular upgrade.

13. Upgrading the Optimizer- check out the following MOS URL for Oracle-supplied scripts and suggestions for this part of the upgrade.

https://support.oracle.com/CSP/main/article?cmd=show&id=465787.1&type=NOT

In theory you should use the in-place statistics from 10g…for at least a week before gathering the 11.2 version of schema statistics.

3 Responses to Manual Upgrade to Oracle 11gR2

  1. Pingback: 2010 in review « Oracle High Availability

  2. David says:

    I am hoping that you also documented installing/configuring Weblogic for Banner and wouldn’t mind sharing that with me please?
    Thank you,
    David

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