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 188.8.131.52 on Linux RH 5 64-bit.
1. Install 184.108.40.206 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 220.127.116.11 listener.
I migrate to the higher level listener (18.104.22.168) 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.
(ADDRESS = (PROTOCOL = TCP)(HOST = FullyQualifiedNODENAME or IP address)(PORT = 1524))
(SID_NAME = DB11)
(ORACLE_HOME = /u01/app/oracle/product/10.2)
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’,
‘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 22.214.171.124 $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.
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.
Oracle Database 11.2 Post-Upgrade Status Tool 05-20-2010 14:53:36
Component Status Version HH:MM:SS
Oracle Server VALID 126.96.36.199.0 00:09:20
JServer JAVA Virtual Machine VALID 188.8.131.52.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 184.108.40.206.0 00:00:31
Oracle XDK VALID 220.127.116.11.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 18.104.22.168.0 00:02:41
Oracle Database Java Packages VALID 22.214.171.124.0 00:00:13
Oracle Multimedia VALID 126.96.36.199.0 00:04:11
Oracle Expression Filter VALID 188.8.131.52.0 00:00:06
Oracle Rules Manager VALID 184.108.40.206.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.
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.