FaceDBA'S (Mahesh's Oracle DBA Blog): Complete Checklist for Manual Upgrades to 10gR2

This document is created for use as a guideline and checklist when manually upgrading Oracle 8i, Oracle 9i or Oracle 10gR1 to Oracle 10gR2 on a single server.

If the database instance is being moved from one server to another as part of the upgrade process, additional steps for that move may need to be performed which are not included in this checklist.

This document is divided into three major sections.
-- Preparing to Upgrade
-- Upgrading to the New Oracle Database 10g Release 2
-- After Upgrading a Database

Please read the whole article before starting to perform an upgrade

Additional Note:

These instructions are incomplete for taking an 8i OPS cluster into a 10gR2 RAC cluster due to the differences in OPS vs RAC.

Complete Checklist for Manual Upgrades to 10gR2

Prerequisites and recommendations

Install Oracle 10g Release 2 in a new Oracle Home
Install JAccelerator (NCOMP) into the home from the Companion media, to avoid the issue in

Note:293658.1 "10.1 or 10.2 Patchset Install Getting ORA-29558 JAccelerator (NCOMP) And ORA-06512"

Download and install the latest available 10.2.0.x patchset. Review the following note for a list of available patchsets and details of any well known issues:

Note:316900.1 "ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts"

Install the latest available Critical Patch Update:

Note 290738.1 "Oracle Critical Patch Update Program General FAQ"

If you are upgrading to 10.2.0.3, review the following alerts before performing the upgrade and apply any required patches:

Note 406472.1 "Mandatory Patch 5752399 for 10.2.0.3 on Solaris 64-bit and Filesystems Managed By Veritas or Solstice Disk Suite software"

Note 412271.1 "ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading or Patching Databases to 10.2.0.3"

NOTE: If your database was originally created as 32-bit, even if it is 64-bit now, apply the patches recommended in Note 412271.1

If you are upgrading to 10.2.0.4, review the following notes and alerts before performing the upgrade and apply any required patches:

Note 553812.1 Actions for the DSTv4 update in the 10.2.0.4 patchset

Note 414590.1 Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade

Note 565600.1 ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA

If you are upgrading to any 10.2.0.x version, review the following alert before performing the upgrade and apply any required patch:

Note:471479.1 "IOT Corruptions After Upgrade from COMPATIBLE <= 9.2 to COMPATIBLE >= 10.1"

If you are upgrading to any 10.2.0.x version on AIX5L, review the following note before upgrading:

Note:557242.1 "Upgrade Gives Ora-29558 Error Despite of JAccelerator Has Been Installed"

If you are upgrading to 10.2.0.4, review the following notes before upgrading:

Note:565600.1 "Error in Catupgrd: ORA-00904 In DBMS_SQLPA"
Note:603714.1 "10.2.0.4 Catupgrd.sql Fails With ORA-03113 Creating SYS.KU$_XMLSCHEMA_VIEW"
If you are upgrading a 32-bit database to 10.2.0.x 64-bit, review the following note and remove the "use_indirect_data_buffers=TRUE" parameter setting before performing the upgrade:

Note:465951.1 "ORA-600 [kcbvmap_1] or Ora-600 [Kcliarq_2] On Startup Upgrade Moving From a 32-Bit To 64-Bit Release"

Either take a cold or hot backup for your database.
Make sure to take a backup of Oracle Home and Central Inventory. Central inventory can be located by the contents of oraInst.loc files. "oraInst.loc" is available in the following locations on various platforms:

/var/opt/oracle/oraInst.loc -- Solaris
/etc/oraInst.loc -- other operating systems
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\inst_loc -- On windows Platform.

Verify kernel parameters are set according to the 10gR2 Installation Guide.
Verify that all O/S packages and patches are installed as per the Installation Guide.

Please also note that Oracle have made an "Oracle10g Upgrade Companion" available. For further information, please review:

Note:466181.1 "10g Upgrade Companion"

The above document is continually updated as new information becomes available.

Compatibility Matrix

Minimum Version of the database that can be directly upgraded to Oracle 10g Release 2

8.1.7.4 -> 10.2.X.X.X
9.0.1.4 or 9.0.1.5 -> 10.2.X.X.X
9.2.0.4 or higher -> 10.2.X.X.X
10.1.0.2 or higher -> 10.2.X.X.X

The following database version will require an indirect upgrade path.

7.3.3 (or lower) -> 7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
7.3.4 -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.0.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X
8.1.n -> 8.1.7 -> 8.1.7.4 -> 10.2.X.X.X

Steps for Upgrading the Database to 10g Release 2

Preparing to Upgrade

In this section all the steps need to be performed to the previous version of Oracle. Please note that the database must be running in normal mode in the old release.

Step 1:

Log in to the system as the owner of the new 10gR2 ORACLE_HOME and copy the following files from the 10gR2 ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the /tmp directory on your system:

ORACLE_HOME/rdbms/admin/utlu102i.sql
ORACLE_HOME/rdbms/admin/utltzuv2.sql

Make a note of the new location of these files.

Step 2:

Change to the temporary directory that you copied files to in Step 1.

Start SQL*Plus and connect to the database instance you are intending to upgrade (running from the original ORACLE_HOME) as a user with SYSDBA privileges. Then run and spool the utlu102i.sql file.

sqlplus '/as sysdba'

SQL> spool Database_Info.log
SQL> @utlu102i.sql
SQL> spool off

Please note: The instance can be running in normal mode and does not need to be running in a restricted (migrate / upgrade) mode to run the script. Also, the instance must not be running in read-only mode. A few registry$ tables may be created, if they do not already exist, and some rows may be inserted into existing Upgrade tables.

Then, check the spool file and examine the output of the upgrade information tool. The sections which follow, describe the output of the Upgrade Information Tool (utlu102i.sql).

NOTE: If you are upgrading from 8.1.7.4, the utlu102i.sql script will fail with an ORA-1403 error. Please follow the workaround in Note:5640527.8 (or Note:407031.1) to enable utlu102i.sql to run.

Database:

This section displays global database information about the current database such as the database name, release number, and compatibility level. A warning is displayed if the COMPATIBLE initialization parameter needs to be adjusted before the database is upgraded.

Logfiles:

This section displays a list of redo log files in the current database whose size is less than 4 MB. For each log file, the file name, group number, and recommended size is displayed. New files of at least 4 MB (preferably 10 MB) need to be created in the current database. Any redo log files less than 4 MB must be dropped before the database is upgraded.

Tablespaces:

This section displays a list of tablespaces in the current database. For each tablespace, the tablespace name and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade. If the tablespace does not have enough free space, then space must be added to the tablespace in the current database. Tablespace adjustments need to be made before the database is upgraded.

Update Parameters:

This section displays a list of initialization parameters in the parameter file of the current database that must be adjusted before the database is upgraded. The adjustments need to be made to the parameter file after it is copied to the new Oracle Database 10g release.

Deprecated Parameters:

This section displays a list of initialization parameters in the parameter file of the current database that are deprecated in the new Oracle Database 10g release.

Obsolete Parameters:

This section displays a list of initialization parameters in the parameter file of the current database that are obsolete in the new Oracle Database 10g release. Obsolete initialization parameters need to be removed from the parameter file before the database is upgraded.

Components:

This section displays a list of database components in the new Oracle Database 10g release that will be upgraded or installed when the current database is upgraded.

Miscellaneous Warnings:

This section provides warnings about specific situations that may require attention before and/or after the upgrade.

SYSAUX Tablespace:

This section displays the minimum required size for the SYSAUX tablespace, which is required in Oracle Database 10g. The SYSAUX tablespace must be created after the new Oracle Database 10g release is started and BEFORE the upgrade scripts are invoked.

Step 3:

Check for the deprecated CONNECT Role

After upgrading to 10gR2, the CONNECT role will only have the CREATE SESSION privilege; the other privileges granted to the CONNECT role in earlier releases will be 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', 'OLAPDBA', 'OLAPSVR', 'OLAP_USER',
'OLAPSYS', '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. The upgrade scripts adjust the privileges for the Oracle-supplied users.

In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:

SELECT GRANTEE,PRIVILEGE FROM DBA_SYS_PRIVS
WHERE GRANTEE='CONNECT'

GRANTEE PRIVILEGE
------------------------------ ---------------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK

In Oracle 10.2 the CONNECT role only includes CREATE SESSION privilege.

If needed by some applications the previous privleges can be restored.
Predefined Roles Evolution From 8i to 10g R2: CONNECT Role Change in 10gR2 (Doc ID 317258.1)

Step 4:

Create the script for dblink incase of downgrade of the database.

During the upgrade to 10gR2, any passwords in database links will be encrypted. To downgrade back to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links will not exist in the downgraded database. If you anticipate a requirement to be able to downgrade back to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can recreate the database links after the downgrade.

Following script can be used to construct the dblink.

SELECT
'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '''
||L.PASSWORD||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L,
sys.user$ U
WHERE L.OWNER# = U.USER# ;

Step 5:

Check for the TIMESTAMP WITH TIMEZONE Datatype.

Please this step is only required for the 10gR1 as versions prior to 10.1 did not have this data type.
Please note that you must verify the time zone patch installed on the source home and instance is the same as installed on the 10.2 home as identified in the pre-upgrade check (utlu102i.sql)
Please review the Prerequisites and recommendations section at the beginning of this note for additional information.

This may affect existing data of TIMESTAMP WITH TIME ZONE datatype.
For example, if users enter TIMESTAMP '2003-02-17 09:00:00 America/Sao_Paulo', we convert the data to UTC based on the transition rules in the time zone file and store them on the disk. So '2003-02-17 11:00:00' along with the time zone id for 'America/Sao_Paulo' is stored because the offset for this particular time is '-02:00'. Now the transition rules are modified and the offset for this particular time is changed to '-03:00'. when users retrieve the data, they will get '2003-02-17 08:00:00 America/Sao_Paulo'. There is one hour difference compared to the original value.

Change to the temporary directory that you copied files to in Step 1.

Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utltzuv2.sql file.

$ sqlplus '/as sysdba'

SQL> spool TimeZone_Info.log
SQL> @utltzuv2.sql
SQL> spool off

If the utltzuv2.sql script identifies columns with time zone data affected by a database upgrade, then use the solution to solve this problem.

create tables with the time zone information in character format (for example, TO_CHAR(column, 'YYYY-MM-DD HH24.MI.SSXFF TZR'), and recreate the TIMESTAMP data from these tables after the upgrade.

For example, user scott has a table tztab:
create table tztab(x number primary key, y timestamp with time zone);
insert into tztab values(1, timestamp '');

Before upgrade, you can create a table tztab_back, note column y here is defined as VARCHAR2 to preserve the original value.

create table tztab_back(x number primary key, y varchar2(256));
insert into tztab_back select x,
to_char(y, 'YYYY-MM-DD HH24.MI.SSXFF TZR') from tztab;

After upgrade, you need update the data in the table tztab using the value in tztab_back.
update tztab t set t.y = (select to_timestamp_tz(t1.y,
'YYYY-MM-DD HH24.MI.SSXFF TZR') from tztab_back t1 where t.x=t1.x);

Step 6:

Starting in Oracle 9i the National Characterset (NLS_NCHAR_CHARACTERSET) will be limited to UTF8 and AL16UTF16. Any other NLS_NCHAR_CHARACTERSET will no longer be supported.

For more details, refer to Note:276914.1 "The National Character Set in Oracle 9i and 10g"

NOTE: This step only applies to version 8i upgrade to version 9i or higher. If you are not upgrading from Oracle8i, skip to step 7.

When upgrading from Oracle8i to 10g the value of NLS_NCHAR_CHARACTERSET is based on value currently used in the Oracle8i version.

If the NLS_NCHAR_CHARACTERSET is UTF8 then new it will stay UTF8. In all other cases the NLS_NCHAR_CHARACTERSET is changed to AL16UTF16 and -if used- N-type data (= data in columns using NCHAR, NVARCHAR2 or NCLOB ) may need to be converted.

The change itself is done in step 38 by running the upgrade script.

To check whether there are any N-type objects in a database, run the following query:

    select distinct OWNER, TABLE_NAME
    from DBA_TAB_COLUMNS
    where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB')
    and OWNER not in ('SYS','SYSTEM','XDB');

If no rows are returned it should mean that the database is not using N-type columns for user data, so simply go to the next step.

If you have N-type columns for user data then run the following query:

SQL> select * from nls_database_parameters where parameter ='NLS_NCHAR_CHARACTERSET';

If you are using N-type columns AND your National Characterset is UTF8 or is in the following list:

JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED

then also simply go to point next step. The conversion of the user data itself will then be done in step 38

If you are using N-type columns AND your National Characterset is NOT UTF8 or NOT in the following list:

JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED

(your current NLS_NCHAR_CHARACTERSET is for example US7ASCII, WE8ISO8859P1, CL8MSWIN1251 ...)

then you have to:

change the tables to use CHAR, VARCHAR2 or CLOB instead the N-type
or
use export/import the table(s) containing N-type column and truncate those tables before migrating to 10g

The recommended NLS_LANG during export is simply the NLS_CHARACTERSET, not the NLS_NCHAR_CHARACTERSET

Step 7:

When upgrading to Oracle Database 10g, optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade.

As of Oracle Database 10g Release 10.1, Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. You can enter the following:

$ sqlplus '/as sysdba'

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

For Oracle8i and Oracle9i, use the DBMS_STATS.GATHER_SCHEMA_STATS procedure to gather statistics.

Current Statistics if desired, can be backed up prior to gathering current statistics and is useful if you want to revert back the statistics post upgrade.
Process to backup the existing statistics as follows:

$ sqlplus '/as sysdba'
SQL>spool sdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.create_stat_table('SYS','dictstattab');

SQL>exec dbms_stats.export_schema_stats('WMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('MDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('CTXSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('XDB','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('WKSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('LBACSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OLAPSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DMSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ODM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDSYS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SI_INFORMTN_SCHEMA','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('OUTLN','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('DBSNMP','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYSTEM','dictstattab',statown => 'SYS');
SQL>exec dbms_stats.export_schema_stats('SYS','dictstattab',statown => 'SYS');

SQL>spool off

This data is useful if you want to revert back the statistics
For example, the following PL/SQL subprograms import the statistics for the SYS schema after deleting the existing statistics:

exec dbms_stats.delete_schema_stats('SYS');
exec dbms_stats.import_schema_stats('SYS','dictstattab');

To gather statistics run this script, connect to the database AS SYSDBA using SQL*Plus.

$ sqlplus '/as sysdba'

SQL>spool gdict

SQL>grant analyze any to sys;

SQL>exec dbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('MDSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('CTXSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('XDB',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('WKSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('LBACSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('OLAPSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('DMSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ODM',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ORDSYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SI_INFORMTN_SCHEMA',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);
SQL>exec dbms_stats.gather_schema_stats('SYS',options=>'GATHER',
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

SQL>spool off

Step 8:

Check for invalid objects in the database:

spool invalid_pre.lst
select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status from
dba_objects where status <> 'VALID';
spool off

Run the following script as a user with SYSDBA privs using SQL*Plus and then requery invalid objects:

% sqlplus '/as sysdba'
SQL> @?/rdbms/admin/utlrp.sql

This last query will return a list of all objects that cannot be recompiled before the upgrade in the file 'invalid_pre.lst'

If you are upgrading from Oracle9iR2 (9.2), verify that the view dba_registry contains data. If the view is empty, run the following scripts from the 9.2 home:

% sqlplus '/as sysdba'
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sql

and verify that the dba_registry view now contains data.

Step 9:

Check for corruption in the dictionary, use the following commands in sqlplus connected as sys:

Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

Select 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
from dba_clusters
where owner='SYS'
union
Select 'Analyze table "'||table_name||'" validate structure cascade;'
from dba_tables
where owner='SYS' and partitioned='NO' and (iot_type='IOT' or iot_type is NULL)
union
Select 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
from dba_tables
where owner='SYS' and partitioned='YES';

spool off

This creates a script called analyze.sql.
Now execute the following steps.

$ sqlplus '/as sysdba'
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

This script (analyze.sql) should not return any errors.

Step 10:

Ensure that all Materialized views / Snapshot refreshes are successfully completed, and that replication must be stopped (ie: quiesced).

$ sqlplus '/ as sysdba'
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;

You can also use:

SQL> select distinct owner, name mview, master_owner master_owner, last_refresh from dba_mview_refresh_times;

Step 11:

Stop the listener for the database:

$ lsnrctl
LSNRCTL> stop

Ensure no files need media recovery:

$ sqlplus '/ as sysdba'
SQL> select * from v$recover_file;

This should return no rows.

Step 12:

Ensure no files are in backup mode:

SQL> select * from v$backup where status!='NOT ACTIVE';

This should return no rows.

Step 13:

Resolve any outstanding unresolved distributed transaction:

SQL> select * from dba_2pc_pending;

If this returns rows you should do the following:

SQL> select local_tran_id from dba_2pc_pending;
SQL> execute dbms_transaction.purge_lost_db_entry('');
SQL> commit;

Step 14:

Disable all batch and cron jobs.

Step 15:

Ensure the users sys and system have 'system' as their default tablespace.

SQL> select username, default_tablespace from dba_users
where username in ('SYS','SYSTEM');

To modify use:

SQL> alter user sys default tablespace SYSTEM;
SQL> alter user system default tablespace SYSTEM;

Step 16:

Ensure that the aud$ is in the system tablespace when auditing is enabled.

SQL> select tablespace_name from dba_tables where table_name='AUD$';

If the AUD$ table exists, and is in use, upgrade performance can be effected depending on the number of records in the table.

Please refer to the following note for information on exporting and truncating the AUD$ table:

Note.979942.1 Ext/Pub Database upgrade appears to have halted at SYS.AUD$ Table

Step 17:

Note down where all control files are located.

SQL> select * from v$controlfile;

Step 18:

If table XDB.MIGR9202STATUS exists in the database, drop it before upgrading the database (to avoid the issue described in Note:356082.1)

Step 19:

Shutdown the database

$ sqlplus '/as sysdba'
SQL> shutdown immediate;

Step 20:

Perform a full cold backup (or an online backup using RMAN)

You can either do this by manually copying the files or sign on to RMAN:

$ rman "target / nocatalog"

And issue the following RMAN commands:

RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO 'save_controlfile_location';
}

Upgrading to the New Oracle Database 10g Release 2

Step 21:

Update the init.ora file:

- Make a backup of the old init.ora file

- Copy it from the old (pre-10.2) ORACLE_HOME to the new (10.2) ORACLE_HOME

On Unix/Linux, the default location of the file is the $ORACLE_HOME/dbs directory

- Comment out any obsoleted parameters (listed in appendix A).

- Change all deprecated parameters (listed in appendix B).

- Set the COMPATIBLE initialization parameter to an appropriate value. If you are
upgrading from 8.1.7.4 then set the COMPATIBLE parameter to 9.2.0 until after the
upgrade has been completed successfully. If you are upgrading from 9.2.0 or 10.1.0
then leave the COMPATIBLE parameter set to it's current value until the upgrade
has been completed successfully. This will avoid any unnecessary ORA-942 errors
from being reported in SMON trace files during the upgrade (because the upgrade
is looking for 10.2 objects that have not yet been created)

- If you have the parameter NLS_LENGTH_SEMANTICS currently set to CHAR, change the value
to BYTE during the upgrade (to avoid the issue described in Note:4638550.8)

- Verify that the parameter DB_DOMAIN is set properly.

- Make sure the PGA_AGGREGATE_TARGET initialization parameter is set to
at least 24 MB.

- Ensure that the SHARED_POOL_SIZE and the LARGE_POOL_SIZE are at least 150Mb.
Please also the check the "KNOWN ISSUES" section

- Make sure the JAVA_POOL_SIZE initialization parameter is set to at least 150 MB.

- Make sure the STREAMS_POOL_SIZE is set to at least 50 MB (200 MB is ideal)

- Ensure there is a value for DB_BLOCK_SIZE

- On Windows operating systems, change the BACKGROUND_DUMP_DEST and USER_DUMP_DEST initialization parameters that point to RDBMS80 or any other environment variable
to point to the following directories instead:

BACKGROUND_DUMP_DEST to ORACLE_BASE\oradata\DB_NAME
and
USER_DUMP_DEST to ORACLE_BASE\oradata\DB_NAME\archive

- Comment out any existing AQ_TM_PROCESSES and JOB_QUEUE_PROCESSES parameter settings, and add new lines in the init.ora/spfile.ora that explicitly set AQ_TM_PROCESSES=0 and JOB_QUEUE_PROCESSES=0 for the duration of the upgrade. The "startup upgrade" command (see step 30) should ensure that these settings are used, but it's worth making sure.

- If you have defined an UNDO tablespace, set the parameter UNDO_MANAGEMENT=AUTO (otherwise, either unset the parameter or explicitly set it to MANUAL). See Note:135090.1 for further information about the Automatic Undo Management feature.

- Make sure all path names in the parameter file are fully specified. You should not have relative path names in the parameter file.

- If you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE during the upgrade.

- If you are upgrading a cluster database, then modify the initdb_name.ora file in the same way that you modified the parameter file.

- Verify the parameter FIXED_DATE is not set. If it set during the upgrade catalog and catproc will report as invalid even if there are no invalid sys or system objects. See Note: 745183.1 for further information.

Step 22 :

Check for adequate freespace on archive log destination file systems.

Step 23 :

If needed in your environment, ensure the environment variable NLS_LANG variable is set correctly:
To verify the current setting:
$ env | grep $NLS_LANG

For additional information on NLS_LANG settings, please refer to the following notes:

Note 158577.1 NLS_LANG Explained (How does Client-Server Character Conversion Work?)
Note 179133.1 The correct NLS_LANG in a Windows Environment
Note 264157.1 The correct NLS_LANG setting in Unix Environments

Step 24:

If needed copy the SQL*Net files like (listener.ora,tnsnames.ora etc) to the new location (when no TNS_ADMIN env. Parameter is used)

$ cp $OLD_ORACLE_HOME/network/admin/*.ora /network/admin

Step 25:

If your Operating system is Windows (NT, 2000, XP or 2003) delete your services With the ORADIM of your old oracle version.

Stop the OracleServiceSID Oracle service of the database you are upgrading, where SID is the instance name. For example, if your SID is ORCL, then enter the following at a command prompt:

C:\> NET STOP OracleServiceORCL

For Oracle 8.0 this is:
C:\ORADIM80 -DELETE -SID

For Oracle 8i or higher this is:
C:\ORADIM -DELETE -SID

Also create the new Oracle Database 10gR2 service at a command prompt using the ORADIM command of the new Oracle Database release:

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

Step 26:

Copy configuration files from the ORACLE_HOME of the database being upgraded to the new Oracle Database 10g ORACLE_HOME:

If your parameter file resides within the old environment's ORACLE_HOME, then copy it to the new ORACLE_HOME. By default, Oracle looks for the parameter file in ORACLE_HOME/dbs on UNIX platforms and in ORACLE_HOME\database on Windows operating systems. The parameter file can reside anywhere you wish, but it should not reside in the old environment's ORACLE_HOME after you upgrade to Oracle Database 10g.

If your parameter file is a text-based initialization parameter file with either an IFILE (include file) or a SPFILE (server parameter file) entry, and the file specified in the IFILE or SPFILE entry resides within the old environment's ORACLE_HOME, then copy the file specified by the IFILE or SPFILE entry to the new ORACLE_HOME. The file specified in the IFILE or SPFILE entry contains additional initialization parameters.

If you have a password file that resides within the old environments ORACLE_HOME, then move or copy the password file to the new Oracle Database 10g ORACLE_HOME.

The name and location of the password file are operating system-specific.
On UNIX platforms, the default password file is ORACLE_HOME/dbs/orapwsid.
On Windows operating systems, the default password file is
ORACLE_HOME\database\pwdsid.ora. In both cases, sid is your Oracle instance ID.

If you are upgrading a cluster database and your initdb_name.ora file resides within the old environment's ORACLE_HOME, then move or copy the initdb_name.ora file to the new ORACLE_HOME.

Note:
If you are upgrading a cluster database, then perform this step on all nodes
in which this cluster database has instances configured.

Step 27:

Update the oratab entry, to set the new ORACLE_HOME and disable automatic startup:

SID:ORACLE_HOME:N

Step 28:

Update the environment variables like ORACLE_HOME and PATH

$. oraenv

Step 29:

Make sure the following environment variables point to the new release (10g) directories:

- ORACLE_HOME
- PATH
- ORA_NLS10
- ORACLE_BASE
- LD_LIBRARY_PATH
- LD_LIBRARY_PATH_64 (Solaris only)
- LIBPATH (AIX only)
- SHLIB_PATH (HPUX only)
- ORACLE_PATH

$ env | grep ORACLE_HOME
$ env | grep PATH
$ env | grep ORA_NLS10
$ env | grep ORACLE_BASE
$ env | grep LD_LIBRARY_PATH
$ env | grep ORACLE_PATH

AIX:
$ env | grep LIBPATH

HP-UX:
$ env | grep SHLIB_PATH

Note that the ORA_NLS10 environment variable replaces the ORA_NLS33 environment variable, so you should unset ORA_NLS33 and set ORA_NLS10.

As per Note:77442.1, you should set ORA_NLS10 to point to $ORACLE_HOME/nls/data

Step 30:

Startup upgrade the database:

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus / as sysdba
Use Startup with the UPGRADE option:
SQL> startup upgrade

Step 31:

Create a SYSAUX tablespace. In Oracle Database 10g, the SYSAUX tablespace is used to consolidate data from a number of tablespaces that were separate in previous releases.

The SYSAUX tablespace must be created with the following mandatory attributes:

- ONLINE
- PERMANENT
- READ WRITE
- EXTENT MANAGEMENT LOCAL
- SEGMENT SPACE MANAGEMENT AUTO

The Upgrade Information Tool(utlu102i.sql in step 4) provides an estimate of the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace section.

The following SQL statement would create a 500 MB SYSAUX tablespace for the database:

SQL> CREATE TABLESPACE sysaux DATAFILE 'sysaux01.dbf'
SIZE 500M REUSE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO
ONLINE;

Step 32:

NOTE: Before performing the next action, disable any third party procedures that check the complexity of schema passwords. During the upgrade, new schemas may be created and these may initially have an insecure password (but only for a very short period of time, because the SQL script that creates the new schema will then immediately expire the password and lock the schema). If procedures are in place to enforce password complexity, the "create user" statement may fail and cause configuration of a component to fail.

Run the catupgrd.sql script, spooling the output so you can check whether any errors occured and investigate them:

SQL> spool upgrade.log
SQL> @catupgrd.sql

The catupgrd.sql script determines which upgrade scripts need to be run and then runs each necessary script. You must run the script in the new release 10.2 environment.

The upgrade script creates and alters certain data dictionary tables. It also upgrades and configures the following database components in the new release 10.2 database (if the components were installed in the database before the upgrade).

Oracle Database Catalog Views
Oracle Database Packages and Types
JServer JAVA Virtual Machine
Oracle Database Java Packages
Oracle XDK
Oracle Real Application Clusters
Oracle Workspace Manager
Oracle interMedia
Oracle XML Database
OLAP Analytic Workspace
Oracle OLAP API
OLAP Catalog
Oracle Text
Spatial
Oracle Data Mining
Oracle Label Security
Messaging Gateway
Expression Filter
Oracle Enterprise Manager Repository

Turn off the spooling of script results to the log file:

SQL> SPOOL OFF

Then, check the spool file and verify that the packages and procedures compiled successfully. You named the spool file earlier in this step; the suggested name was upgrade.log. Correct any problems you find in this file and rerun the appropriate upgrade script if necessary. You can rerun any of the scripts described in this note as many times as necessary.

Step 33:

Run utlu102s.sql, specifying the TEXT option:

SQL> @utlu102s.sql TEXT

This is the Post-upgrade Status Tool displays the status of the database components in the upgraded database. The Upgrade Status Tool displays output similar to the following:

Oracle Database 10.2 Upgrade Status Utility 04-20-2005 05:18:40

Component Status Version HH:MM:SS
Oracle Database Server VALID 10.2.0.1.0 00:11:37
JServer JAVA Virtual Machine VALID 10.2.0.1.0 00:02:47
Oracle XDK VALID 10.2.0.1.0 00:02:15
Oracle Database Java Packages VALID 10.2.0.1.0 00:00:48
Oracle Text VALID 10.2.0.1.0 00:00:28
Oracle XML Database VALID 10.2.0.1.0 00:01:27
Oracle Workspace Manager VALID 10.2.0.1.0 00:00:35
Oracle Data Mining VALID 10.2.0.1.0 00:15:56
Messaging Gateway VALID 10.2.0.1.0 00:00:11
OLAP Analytic Workspace VALID 10.2.0.1.0 00:00:28
OLAP Catalog VALID 10.2.0.1.0 00:00:59
Oracle OLAP API VALID 10.2.0.1.0 00:00:53
Oracle interMedia VALID 10.2.0.1.0 00:08:03
Spatial VALID 10.2.0.1.0 00:05:37
Oracle Ultra Search VALID 10.2.0.1.0 00:00:46
Oracle Label Security VALID 10.2.0.1.0 00:00:14
Oracle Expression Filter VALID 10.2.0.1.0 00:00:16
Oracle Enterprise Manager VALID 10.2.0.1.0 00:00:58
Note - in RAC environments, this script may suggest that the status of the RAC component is INVALID when in actual fact it is VALID (as shown in the output from DBA_REGISTRY)

NOTE: As per Note:456845.1, the output from the utlu102s.sql script may differ from the output from DBA_REGISTRY. To check the current status of each component, run the following SQL statement:

SQL> select comp_name, status, version from dba_registry;

Step 34:

Restart the database:
SQL> shutdown immediate (DO NOT use "shutdown abort" !!!)
SQL> startup restrict

Executing this clean shutdown flushes all caches, clears buffers and performs other database housekeeping tasks. Which is needed if you want to upgrade specific components.

Step 35:

Run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies.
This step is only necessary if Oracle Label Security is in your database.
(Check from Step 33).

SQL> @olstrig.sql

Step 36:

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:

If there are still objects which are not valid after running the script run the following:

spool invalid_post.lst
Select substr(owner,1,12) owner,
substr(object_name,1,30) object,
substr(object_type,1,30) type, status
from
dba_objects where status <>'VALID';
spool off

Now compare the invalid objects in the file 'invalid_post.lst' with the invalid objects in the file 'invalid_pre.lst' you create in step 9.

NOTE: If you have upgraded from version 9.2 to version 10.2 and find that the following views are invalid, the views can be safely ignored (or dropped):

SYS.V_$KQRPD
SYS.V_$KQRSD
SYS.GV_$KQRPD
SYS.GV_$KQRSD

You can also ignore or drop the following public synonyms
V$KQRPD
V$KQRSD
GV$KQRPD
GV$KQRSD

As they are based on

SYS.V_$KQRPD
SYS.V_$KQRSD
SYS.GV_$KQRPD
SYS.GV_$KQRSD

NOTE: If you have used OPatch to apply a CPU patch to the 10.2.0.x home, you now need to review and follow the post-installation steps in the README file of the CPU patch to apply the CPU patch to the upgraded database. This may require running the catcpu.sql and other scripts and will vary depending on the version of the CPU installed.

NOTE: After the upgrade there may be invalid views with the prefix x_$. These views are created by third party applications and are pointing to non-existent or modified x$ tables. Third parties should not create SYS owned views, particular not SYS owned views based on internal X$ tables. Since these are not Oracle created objects, they should be dropped before upgrade, since they cannot be validated or dropped after upgrade using normal methods.

Additional information can be found in...
Note 361757.1 Invalid x_$ Objects After Upgrade.

After Upgrading a Database

Step 37:

Shutdown the database and startup the database.

    % sqlplus '/as sysdba'
    SQL> shutdown
    SQL> startup restrict

Step 38:

Complete the Step 38 only if you upgraded your database from release 8.1.7
Otherwise skip to Step 40.

A) If you are not using N-type columns for user data, ie. the query

    select distinct OWNER, TABLE_NAME
    from DBA_TAB_COLUMNS
    where DATA_TYPE in ('NCHAR','NVARCHAR2', 'NCLOB')
    and OWNER not in ('SYS','SYSTEM','XDB');

did not return rows in Step 6 of this note then:

% sqlplus '/as sysdba'
SQL> shutdown immediate

and go to step 40.

B) IF your version 8 NLS_NCHAR_CHARACTERSET was UTF8:

You can look up your previous NLS_NCHAR_CHARACTERSET using this select:

    select * from nls_database_parameters where parameter ='NLS_SAVED_NCHAR_CS';

then:

    % sqlplus '/as sysdba'
    SQL> shutdown immediate

and go to step 40.

C) IF you are using N-type columns for *user* data *AND* your previous NLS_NCHAR_CHARACTERSET was in the following list:

JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED

then the N-type columns *data* need to be converted to AL16UTF16:

To upgrade user tables with N-type columns to AL16UTF16 run the script utlnchar.sql:

    % sqlplus '/as sysdba'
    SQL> @utlnchar.sql
    SQL> shutdown immediate;

go to step 40.

D) IF you are using N-type columns for *user* data *AND * your previous NLS_NCHAR_CHARACTERSET was *NOT* in the following list:

JA16SJISFIXED , JA16EUCFIXED , JA16DBCSFIXED , ZHT32TRISFIXED
KO16KSC5601FIXED , KO16DBCSFIXED , US16TSTFIXED , ZHS16CGB231280FIXED
ZHS16GBKFIXED , ZHS16DBCSFIXED , ZHT16DBCSFIXED , ZHT16BIG5FIXED
ZHT32EUCFIXED

then import the data exported in point 8 of this note. The recommended NLS_LANG during import is simply the NLS_CHARACTERSET, not the NLS_NCHAR_CHARACTERSET

After the import:

% sqlplus '/as sysdba'
SQL> shutdown immediate;

go to step 40.

Step 39:

If your database has TIMESTAMP WITH TIMEZONE data, you must update the data so that it is converted and stored based on the new time zone rules that come with the upgrade. (Step 6).

If you used the export utility to export a copy of the affected tables, you should now use the import utility to import your data from these tables back into your database. The import utility will update the timestamp data as it imports.

If you used the manual script method, you will need to update the affected timestamp data based on your backed up table. For example, if you previously backed up your table, you need to run an update statement similar to the one below to update your timestamp data.

UPDATE tztab t SET t.y =
(SELECT to_timestamp_tz(t1.y,'YYYY-MM-DD HH24.MI.SSXFF TZR')
FROM tztab_back t1
WHERE t.x=t1.x);

Step 40:

Now edit the init.ora:

- If you changed the value for NLS_LENGTH_SEMANTICS from CHAR to BYTE prior to the upgrade (see step 21), set it back to CHAR. Otherwise, do not change the value of the parameter to CHAR without careful evaluation and testing. Switching to CHAR semantics can break application code. See Note:144808.1 for further information about the usage of this parameter.

- If you changed the value for CLUSTER_DATABASE from TRUE to FALSE prior to the upgrade, set it back to TRUE

Step 41:

Startup the database:
SQL> startup

Create a server parameter file with a initialization parameter file
SQL> create spfile from pfile;

This will create a spfile as a copy of the init.ora file located in the
$ORACLE_HOME/dbs directory.

Step 42:

Modify the listener.ora file:
For the upgraded intstance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME.

Step 43:

Start the listener
$ lsnrctl
LSNRCTL> start

Step 44:

Enable cron and batch jobs

Step 45:

Change oratab entry to use automatic startup
SID:ORACLE_HOME:Y

Step 46:

Upgrade the Oracle Cluster Registry (OCR) Configuration.
If you are using Oracle Cluster Services, then you must upgrade the Oracle Cluster Registry (OCR)keys for the database.

* Use srvconfig from the 10g ORACLE_HOME. For example:

% srvconfig -upgrade -dbname -orahome

If the output from the $ORACLE_HOME/bin/ocrdump command references the pre-10g home, it may be necessary to do the following:

From the pre-10g home, run the command:

% svrctl remove database -d

From the 10g home, run the commands:

% srvctl add database -d -o <10g_oracle_home>
% srvctl add instance -d -i -n
% srvctl add instance -d -i -n

Step 47:

Use the DBMS_STATS package to gather new statistics for your user objects. Using statistics collected from a previous Oracle version may lead CBO to generate less optimal execution plans.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ;

References:

Note:114671.1 "Gathering Statistics for the Cost Based Optimizer"
Note:262592.1 "How to tune your Database after Migration/Upgrade"

Step 48:

Enterprise Manager Grid Control (EMGC) will show that the upgraded database does not have an inventory. To re-discover the database, do the following:

1. Go to EMGC => Targets => Databases

2. Select the upgraded database and remove it

3. Click "Add", enter the name of the host and click "Continue" to allow EMGC to re-discover
the database in the correct home with the correct inventory

Useful Hints

** Upgrading With Read-Only and Offline Tablespaces (Database must be in archive log mode)

The Oracle database can read file headers created prior to Oracle 10g, so you do not need to do anything to them during the upgrade. The only exception to this is if you want to transport tablespaces created prior to Oracle 10g, to another platform. In this case, the file headers must be made read-write at some point before the transport. However, there are no special actions required on them during the upgrade.

The file headers of offline datafiles are updated later when they are brought online, and the file headers of read-only tablespaces are updated if and when they are made read-write sometime after the upgrade. In any other circumstance, read-only tablespaces never have to be made read-write.

To decrease the time needed for a recovery in case of issues with the migration, users can OFFLINE NORMAL all user or application created tablespaces prior to migration. This way if migration fails only the Oracle created tablespaces and rollback datafiles need to be restored rather than the entire database.

You can not OFFLINE any Oracle created tablespaces including those containing rollback/UNDO tablespace prior to migration.

Note: You must OFFLINE the TABLESPACE as migrate does not allow OFFLINE files in an ONLINE tablespace.

Note: If you are upgrading from Oracle9i, the CWMLITE tablespace (which contains OLAP objects) will need to be ONLINE during the upgrade (so that the OLAP objects can be upgraded to 10g and moved to the SYSAUX tablespace)

** Converting Databases to 64-bit Oracle Database Software

If you are installing 64-bit Oracle Database 10g software but were previously using a 32-bit Oracle Database installation, then the databases will automatically be converted to 64-bit during the upgrade to Oracle Database 10g except when upgrading from Release 1 (10.1) to Release 2 (10.2).

The process is not automatic for the release 1 to release 2 upgrade, but is automatic for all other upgrades. This is because the utlip.sql script is not run during the release 1 to release 2 upgrade to invalid all PL/SQL objects. You must run the utlip.sql script as the last step in the release 10.1 environment, before upgrading to release 10.2.

** If error occurs while executing the catupgrd.sql

If an error occurs during the running of the catupgrd.sql script, once the problem is fixed you can simply rerun the catupgrd.sql script to finish the upgrade process and complete the
the upgrade process.

Appendix A: Initialization Parameters Obsolete in 10g

ENQUEUE_RESOURCES
DBLINK_ENCRYPT_LOGIN
HASH_JOIN_ENABLED
LOG_PARALLELISM
MAX_ROLLBACK_SEGMENTS
MTS_CIRCUITS
MTS_DISPATCHERS
MTS_LISTENER_ADDRESS
MTS_MAX_DISPATCHERS
MTS_MAX_SERVERS
MTS_MULTIPLE_LISTENERS
MTS_SERVERS
MTS_SERVICE
MTS_SESSIONS
OPTIMIZER_MAX_PERMUTATIONS
ORACLE_TRACE_COLLECTION_NAME
ORACLE_TRACE_COLLECTION_PATH
ORACLE_TRACE_COLLECTION_SIZE
ORACLE_TRACE_ENABLE
ORACLE_TRACE_FACILITY_NAME
ORACLE_TRACE_FACILITY_PATH
PARTITION_VIEW_ENABLED
PLSQL_NATIVE_C_COMPILER
PLSQL_NATIVE_LINKER
PLSQL_NATIVE_MAKE_FILE_NAME
PLSQL_NATIVE_MAKE_UTILITY
ROW_LOCKING
SERIALIZABLE
TRANSACTION_AUDITING
UNDO_SUPPRESS_ERRORS

Appendix B: Initialization Parameters Deprecated in 10g

LOGMNR_MAX_PERSISTENT_SESSIONS
MAX_COMMIT_PROPAGATION_DELAY
REMOTE_ARCHIVE_ENABLE
SERIAL_REUSE
SQL_TRACE
BUFFER_POOL_KEEP (replaced by DB_KEEP_CACHE_SIZE)
BUFFER_POOL_RECYCLE (replaced by DB_RECYCLE_CACHE_SIZE)
GLOBAL_CONTEXT_POOL_SIZE
LOCK_NAME_SPACE
LOG_ARCHIVE_START
MAX_ENABLED_ROLES
PARALLEL_AUTOMATIC_TUNING
PLSQL_COMPILER_FLAGS (replaced by PLSQL_CODE_TYPE and PLSQL_DEBUG)

Known issues

1) While doing a upgrade from 9iR2 to 10.2.0.X.X, on running the utlu102i.sql script as directed in step 2
Its output informs to add streams_pool_size=50331648 to the init.ora file. While adding the parameter Oracle gives streams_pool_size as invalid parameter.

STREAMS_POOL_SIZE, was introduced in release 10gR1. This message may be ignored for database version 9iR2 or less

2) One of the customer has reported on keeping the shared_pool_size at 150 MB, catmeta.sql fails with insuffient shared memory during the processing of view KU$_PHFTABLE_VI.

Please set the shared_pool_size at 200M.

3) While upgrade following error was encountered.
create or replace
*
ERROR at line 1:
ORA-06553: PLS-213: package STANDARD not accessible.
ORA-00955: name is already used by an existing object

Please make sure to set the following init parameters as below in the spfile/init file or comment them out to their default values, at the time of upgrading the database.

PLSQL_V2_COMPATIBILITY = FALSE
PLSQL_CODE_TYPE = INTERPRETED # Only applicable to 10gR1
PLSQL_NATIVE_LIBRARY_DIR = ""
PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT = 0

Refer to Note 170282.1 PLSQL_V2_COMPATIBLITY=TRUE causes STANDARD and DBMS_STANDARD to Error at Compile

@

Always disconnect from the session which issues the STARTUP and connect as a fresh session before doing any further SQL. eg: On upgrade to 10.2 startup the instance with the upgrade option, exit sqlplus , reconnect a fresh SQLPLUS session as SYSDBA and then run the upgrade scripts.

If this is not a RAC instance, but DBA_REGISTRY shows the RAC component and it is invalid the following bite can be used to remove the reference from DBA_REGISTRY.

NOTE:312071.1 - RAC Option Invalid After Migration

5) Upgrade log shows process errors with ORA-28031 "maximum of 148 enabled roles" when creating queue table.

The number of DEFINED roles (enabled and disabled) that SYS has must not exceed the maximum number of roles defined for the database as defined by the instance parameter max_enabled_roles.

To correct, reduce the number of DEFINED roles to less then 148 then re-run the catupgrd.sql script.

Revision History

Support have been asked to include this new section in the note. It is not possible to provide a completely accurate revision history because many changes have been made since the note was first created in 2005 but, now that this section exists, Support will keep it up-to-date.

18-JUL-2005

Article created

31-JUL-2005

Article published

24-JAN-2007

- Explicitly set AQ_TM_PROCESSES=0 in init.ora (step 21)

29-JAN-2007

- V_$ and GV_$ views can be dropped (step 36)

03-DEC-2007

- Drop table XDB.MIGR9202STATUS from the OLD home (step 18)
- Full cold backup OR an online backup using RMAN (step 20)

05-FEB-2008

- Added reference to Note:406472.1 in the list of prerequisites
- N-type columns in tables owned by XDB can be ignored (step 6)
- Add workaround to ORA-1403 from utlu102i.sql (step 2)
- Added reference to Note:471479.1 in the list of prerequisites

27-FEB-2008

- Added some further commands to step 46
- Added a step about gathering new statistics (step 47)
- Added a reference to Note:407031.1 in step 2
- Added advice regarding ORA_NLS10 (step 29)
- Skip step 6 if upgrading from 9.x to 10.2
- Keep CWMLITE tablespace online (useful hints)
- Check that DBA_REGISTRY contains data (step 8)
- Added reference to Note:465951.1 in the list of prerequisites
- Use GATHER_SCHEMA_STATS in 8i and 9i (step 7)

18-APR-2008

- Added this “Revision History” section to the note
- Clarified when to set UNDO_MANAGEMENT=AUTO in step 21
- Added reference to Note:135090.1 in step 21
- Added reference to Note:293658.1 in the list of prerequisites
- Added reference to Note:316900.1 in the list of prerequisites
- Added reference to Note:466181.1 in the list of prerequisites
- Added reference to Note:557242.1 in the list of prerequisites
- Added some info to step 36 about running catcpu.sql if a CPU patch is applied to the home
- Explicitly set JOB_QUEUE_PROCESSES=0 in init.ora (step 21)
- Added a step about discovering the upgraded database in EMGC (step 48)

21-APR-2008

- Added a note suggesting that password complexity checking procedures are disabled (step 32)
- Added a warning about using NLS_LENGTH_SEMANTICS=CHAR (step 40)

29-SEP-2008

- Added reference to Note:565600.1 in the list of prerequisites
- Added reference to Note:603714.1 in the list of prerequisites
- Added reference to Note:456845.1 in step 33
- Clarified step 21

29-APR-2009
- Added reference to Note:312071.1 into known issues
- Added disclamer for movement of database instance form one system to another
- Processed remarks and corrections for changes due to new patch versions.

18-AUG-2009
- Expanded information about invalid x_$ views, and removed comments about Note 361757.1 being restricted since it has been changed to published external

References

NOTE:135090.1 - Managing Rollback/Undo Segments in AUM (Automatic Undo Management)
NOTE:158577.1 - NLS_LANG Explained (How does Client-Server Character Conversion Work?)
NOTE:159657.1 - Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9iR2 (9.2.0)
NOTE:170282.1 - PLSQL_V2_COMPATIBLITY=TRUE causes STANDARD and DBMS_STANDARD to Error at Compile
NOTE:179133.1 - The correct NLS_LANG in a Windows Environment
NOTE:263809.1 - Complete checklist for manual upgrades to 10gR1 (10.1.0.x)
NOTE:264157.1 - The correct NLS_LANG setting in Unix Environments
NOTE:293658.1 - 10.1 or 10.2 Patchset Install Getting ORA-29558 JAccelerator (NCOMP) And ORA-06512 [VIDEO]
NOTE:312071.1 - RAC Option Invalid After Migration
NOTE:316900.1 - ALERT: Oracle 10g Release 2 (10.2) Support Status and Alerts
NOTE:317258.1 - Predefined Roles Evolution From 8i to 10g R2: CONNECT Role Change in 10gR2
NOTE:356082.1 - ORA-7445 [qmeLoadMetadata()+452] During 10.1 to 10.2 Upgrade
NOTE:361757.1 - Invalid x_$ Objects After Upgrade
NOTE:406472.1 - Mandatory Patch 5752399 for 10.2.0.3 on Solaris 64-bit and Filesystems Managed By Veritas or Solstice Disk Suite software
NOTE:407031.1 - ORA-01403 no data found while running utlu102i.sql/utlu102x.sql on 8174 database
NOTE:412271.1 - ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While Upgrading Or Patching Databases To 10.2.0.3
NOTE:414590.1 - Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade
NOTE:443706.1 - After Migrate From 9.2.0.7 To 10.2.0.3 We Get Ora-04065 On Plitblm
NOTE:456845.1 - UTLU102S.SQL, UTLU111S.SQL and UTLU112S.SQL May Show Different Results Than Select From DBA_REGISTRY
NOTE:465951.1 - ORA-600 [kcbvmap_1] or ORA-600 [kcliarq_2] On Startup Upgrade Moving From a 32-Bit To 64-Bit Release
NOTE:466181.1 - 10g Upgrade Companion
NOTE:471479.1 - IOT Corruptions After Upgrade from COMPATIBLE <= 9.2 to COMPATIBLE >= 10.1
NOTE:553812.1 - Actions for the DSTv4 update in the 10.2.0.4 patchset
NOTE:557242.1 - Upgrade Gives Ora-29558 Error Despite of JAccelerator Has Been Installed
NOTE:565600.1 - ERROR IN CATUPGRD: ORA-00904 IN DBMS_SQLPA
NOTE:603714.1 - 10.2.0.4 Catupgrd.sql Fails With ORA-03113 Creating SYS.KU$_XMLSCHEMA_VIEW
NOTE:745183.1 - After upgrade CATPROC and CATALOG comps are INVALID even if only user invalid objects are found
NOTE:979942.1 - Database upgrade appears to have halted at SYS.AUD$ Table

Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]

	Modified 24-FEB-2011 Type BULLETIN Status PUBLISHED

In this Document
  Purpose
  Scope and Application
  Complete Checklist for Manual Upgrades to 11gR2
  References

Applies to:

Oracle Server - Standard Edition - Version: 9.2.0.8 to 11.2.0.2.0 - Release: 9.2 to 11.2
Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.2.0.2.0 [Release: 9.2 to 11.2]
Information in this document applies to any platform.

Purpose

This document is created for use as a guideline and checklist when manually upgrading from Oracle 9iR2, Oracle 10gR1 (10.1), Oracle 10gR2 (10.2) and Oracle 11g R1 (11.1) to Oracle 11gR2 (11.2).

Scope and Application

Database Administrators, Support

Complete Checklist for Manual Upgrades to 11gR2

Recommendations for Source database

1) Ensure that all database components / objects provided by Oracle are VALID in the source database prior to starting the upgrade

2) Ensure that you do not have the duplicate objects in the SYS and SYSTEM schema

The following objects are permissible duplicates objects

OBJECT_NAME OBJECT_TYPE

------------------------------ -------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY

Please refer the following article for complete instructions to remove any other duplicates.
NOTE.1030426.6 HOW TO CLEAN UP DUPLICATE OBJECTS OWNED BY SYS AND SYSTEM

Note :All these checks are taken care when you execute the step 3 (dbupgdiag.sql)

Requirements and recommendations for target database

Check the certification of the Oracle 11g R2 with your Platform /Operating system before downloading and installing the Oracle 11g R2 .Please check the certification information on the My Oracle Support.
Download and Install Oracle 11g Release 2 in a new Oracle Home and make sure there are no relinking errors.
Install the latest available Patchset from Metalink. (If available).
Install the latest opatch available for your platform and database version ( If available)
Install the latest available Critical Patch Update. (If available).
Either take a Cold or Hot backup of your source database (advisable to have cold backup).
If you have XDB installed then please install the PSE for 10368698 to the 11.2.0.2 Home before doing the upgrade . If there is not an existing One off for your platform please open an SR to request the one off patch .This defect can cause certain databases that are XDB enabled to take too much time to upgrade as opposed to hours.

Compatibility Matrix
Minimum Version of the database that can be directly upgraded to Oracle 11g Release 2

Source Database	Target Database
9.2.0.8 or higher	11.2.x
10.1.0.5 or higher	11.2.x
10.2.0.2 or higher	11.2.x
11.1.0.6 or higher	11.2.x

The following database version will require an indirect upgrade path.

Source Database		Upgrade Path for Target Database		Target Database
7.3.3 (or lower)	---->	7.3.4 -> 9.2.0.8	---->	11.2.x
8.0.5 (or lower)	---->	8.0.6 -> 9.2.0.8	---->	11.2.x
8.1.7 (or lower)	---->	8.1.7.4 -> 10.2.0.4	---->	11.2.x
9.0.1.3 (or lower)	---->	9.0.1.4 -> 10.2.0.4	---->	11.2.x
9.2.0.7(or lower)	---->	9.2.0.8	---->	11.2.x

For example:-

If source database is 8.1.7.0.0, the upgrade path to be followed is as below
8.1.7.0.0 --> 8.1.7.4 --> 10.2.0.4--> 11.2.x.

Pre-Upgrade Steps

In this section all the steps need to be performed after having set the environment of the previous version of the Oracle Database. Note that the database must be running in normal mode in the old release.

To download the latest Pre-Upgrade Information Tool check script:See Note 884522.1

Run the Pre-Upgrade Information Tool for Collecting Pre-Upgrade Information

Step1

Log in to the system as the owner of the Oracle Database 11g Release 2 (11.2)Oracle home directory.
Copy the Pre-Upgrade Information Tool (utlu112i.sql) and utltzuv2.sql from the Oracle Database 11g Release 2 (11.2) ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.

$ORACLE_HOME/rdbms/admin/utlu112i.sql

Step 2

Should be change to the directory where utlu112i.sql had been copied in the previous step.
Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the utlu112i.sql file. Please note that the database should be started using the Source Oracle Home .

$ sqlplus '/ as sysdba'
SQL> spool upgrade_info.log
SQL> @utlu112i.sql
SQL> spool off
SQL>

Check the spool file and examine the output of the upgrade information tool.
The sections which follow, describe the output of the Upgrade Information Tool.
For Sample output, Click here

Database
This section displays global database information about the current database, such as the database name, release number, and compatibility level. A warning is displayed if you must adjust the COMPATIBLE initialization parameter before the database is upgraded.

Logfiles
This section displays a list of redo log files in the current database whose size is less than 4 MB. For each log file, the file name, group number, and recommended size is displayed.
In a manual upgrade using SQL scripts and utilities, new files of at least 4 MB (preferably 10 MB) must be created in the current database, and any redo log files less than 4 MB must be dropped before the database is upgraded. These tasks are performed automatically by the Database Upgrade Assistant.

Tablespaces
This section displays a list of tablespaces in the current database. For each tablespace, the tablespace name and minimum required size is displayed. In addition, a message is displayed if the tablespace is adequate for the upgrade.In a manual upgrade using SQL scripts and utilities, space must be added to tablespaces that do not have enough free space in the current database. These tablespace adjustments must be made before the database is upgraded. This task is performed automatically by the Database Upgrade Assistant.

Update Parameters
This section displays a list of initialization parameters in the parameter file of the current database that must be adjusted before the database is upgraded. The adjustments must be made to the parameter file after it is copied to the new Oracle Database 11g release.

Deprecated Parameters
This section displays a list of initialization parameters in the parameter file of the current database that are deprecated in the new Oracle Database 11g release.Obsolete Parameters This section displays a list of initialization parameters in the parameter file of the current database that are obsolete in the new Oracle Database 11g release. Obsolete initialization parameters must be removed from the parameter file before the database is upgraded.

Appendix A: "Deprecated Initialization Parameters" for a list of initialization parameters that are deprecated in Oracle Database 11g release 2 (11.2).

Obsolete Parameters:
This section displays a list of initialization parameters in the parameter file of the current database that are obsolete in the new Oracle Database 11g release 2 (11.2). Obsolete Parameters need to be removed from the parameter file before the database is upgraded. Obsolete Parameters means, which are no longer valid (or) in use.
Appendix B: "Obsolete Initialization Parameters" for a list of initialization parameters that are obsolete in Oracle Database 11g release 2 (11.2)

Components
This section displays a list of database components in the new Oracle Database 11g release that are upgraded or installed when the current database is upgraded.

Miscellaneous Warnings
This section provides warnings about specific situations that might require attention before or after the upgrade.

SYSAUX Tablespace
This section displays the minimum required size for the SYSAUX tablespace, which is required in the new Oracle Database 11g release. The SYSAUX tablespace must be created if does not exist ( in Oracle 9i )after the new release is started and before the upgrade scripts are invoked.

Note : If sysaux was created in 9i then it must be dropped and re-created after starting in the new release. If created in 10G or later then it can be left there and used.

Preparing Database for Upgrade

Step3

Check for the integrity of the source database

Check for the integrity of the source database prior to starting the upgrade by downloading and running dbupgdiag.sql script from below My Oracle Support article

Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql

After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.

Step 4

Deprecated CONNECT Role

After upgrading to Oracle Database 11g Release 2 (11.2) from Oracle Database9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), the CONNECT role 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 upgrade.
The upgrade scripts adjust the privileges for the Oracle-supplied users.

In Oracle 9.2.x and 10.1.x CONNECT role includes the following privileges:

SELECT GRANTEE,PRIVILEGE
FROM DBA_SYS_PRIVS
WHERE GRANTEE ='CONNECT'

GRANTEE PRIVILEGE
------- ----------------------
CONNECT CREATE VIEW
CONNECT CREATE TABLE
CONNECT ALTER SESSION
CONNECT CREATE CLUSTER
CONNECT CREATE SESSION
CONNECT CREATE SYNONYM
CONNECT CREATE SEQUENCE
CONNECT CREATE DATABASE LINK

From Oracle 10.2, 'CONNECT' role only includes 'CREATE SESSION' privilege.

Step 5

Create Script for DBLINK (In case the database has to be downgraded again)

During the upgrade to Oracle Database 11g Release 2 (11.2) from Oracle Database 9i Release 2 (9.2) or Oracle Database 10g Release 1 (10.1), any passwords in database links are encrypted. To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade. Consequently, the database links do not exist in the downgraded database. If you anticipate a requirement to be able to downgrade to your original release, then save the information about affected database links from the SYS.LINK$ table, so that you can re-create the database links after the downgrade.

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

Step 6
Check for TIMESTAMP WITH TIMEZONE Datatype

The time zone files that are supplied with Oracle Database 11g Release 2 (11.2) have been updated to version 11 to reflect changes in transition rules for some time-zone regions. The changes might affect existing data of the TIMESTAMP WITH TIME ZONE data type.

Case1
=====

If the source database is using a timezone file older than version 11 then the utlu112i.sql (Pre-Upgrade Information Tool) will generate the following warning

Example :
WARNING: -->Database is using a timezone file older than version 11.
.... After the release migration, it is suggested that DBMS_DST package
.... be used to upgrade the 11.1.0.6.0 database timezone version
.... to the latest version which comes with the new release.

You can upgrade the time zone version after the release migration ,it is suggested that use DBMS_DST package to upgrade the Source database timezone version to the latest version which comes with the new release.

Below action plan the complete action required for all the releases .

Note 815679.1 : Actions For DST Updates When Upgrading To 11.2.0.1 Base

Please refer the following article if you are upgrading to 11.2.0.2.

Note 1201253.1 : Actions For DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset

If you are still patching the timezone files to the required Timezone file version,running the Pre- upgrade script does not always update the TZ version information in the registry .So please execute the following sql query in the source database to find the Version of existing timezone files:

SQL> select TZ_VERSION from registry$database;

Case 2
======
If the source database is using a timezone file greater than version 11 then utlu112i.sql (Pre-Upgrade Information Tool) will generate the following warning .

Example :
WARNING: --> Database is using a timezone file greater than version 11.
.... BEFORE upgrading the database, patch the 11gR2
.... $ORACLE_HOME/oracore/zoneinfo/ with a timezone data file of the
.... same version as the one used in the 11.1.0.6.0 release database.

After patching the timezone files to the required Timezone file version,running the Pre- upgrade script does not always update the TZ version information in the registry .
Please execute the following sql query in the source database to find the Version of existing timezone files:

SQL> select TZ_VERSION from registry$database;

BEFORE upgrading the database, you MUST patch the 11gR2 $ORACLE_HOME/oracore/zoneinfo/ with a timezone data file of the same version as the one used in the source release database. Apply the patch for each database you will be upgrading. Otherwise, the upgrade script will terminate without upgrading the database.

if the source database is using timezone files higher than version 11 and the Oracle 11gR2 Oracle home is NOT patched with the same version timezone before upgrade then you will get the following error while trying to upgrade the database .

ERROR at line 1:
ORA-01722: invalid number

For a detailed description of time zone upgrade, Please refer the following My Oracle Support (formerly OracleMetalink) notes:

Note 815679.1 Actions For DST Updates When Upgrading To 11.2.0.1 Base Release
Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST
Note 412160.1 "Updated DST transitions and new Time Zones in Oracle Time Zone File patches"

Please refer the following article if you are upgrading to 11.2.0.2.

Note 1201253.1 : Actions For DST Updates When Upgrading To Or Applying The 11.2.0.2 Patchset

Note : The TIMESTAMP WITH TIME ZONE data stored in the database can become corrupted during the upgrade if there is a time zone file version mismatch.

Step 7

Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.

select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

If this is UTF8 or AL16UTF16 then no action is needed.
If is not UTF8 or AL16UTF16 then refer the following article

Note 276914.1 The National Character Set in Oracle 9i and 10g.

Step 8
Optimizer Statistics

When upgrading to Oracle Database 11g Release 2 (11.2), optimizer statistics are collected for dictionary tables that lack statistics. This statistics collection can be time consuming for databases with a large number of dictionary tables, but statistics gathering only occurs for those tables that lack statistics or are significantly changed during the upgrade.

To determine the schemas which lacks statistics,, either review the output of the utlu112i.sql script or download and run the script from below Metalink article

Note 560336.1 Script to Check Schemas with Stale Statistics

To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. As of Oracle Database 10g Release 1 (10.1), Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, you can enter the following:

$ sqlplus "/as sysdba"

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

If you are using Oracle Database 9i Release 2 (9.2), then you should use the DBMS_STATS.GATHER_SCHEMA_STATS procedure to gather statistics. To do this, you can run the scripts provided in Appendix D.

Appendix D has sample script, which creates the table, dictstattab, and exports the statistics for the RDBMS component schema into it. The statistics collection might give errors if a particular component schema does not exist in the database, or if a component is not installed or invalid.

Backup the existing statistics to revert / import back the statistics, once the upgrade is successful.

For example, the following PL/SQL subprograms import the statistics for the SYS schema after deleting the existing statistics:

SQL> EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
SQL> EXEC DBMS_STATS.IMPORT_SCHEMA_STATS('SYS','dictstattab');

Step 9
Disable Oracle Database Vault

When upgrading from Oracle Database release 10.2,if you have enabled Oracle Database Vault option in your current Oracle home , then you must disable Oracle Database Vault in the target Oracle home where the new release 11.2 software is installed before upgrading the database, and enable it again when the upgrade is finished.If Database Vault is enabled, then DBUA will return an error asking you to disable Database Vault prior to upgrade

You must do this before upgrading the database. Enable Oracle Database Vault again once the upgrade is complete.

Please refer the following Documentation /Articles for complete information to Disable/Enable Oracle Database Vault

Disabling and Enabling Oracle Database Vault

OR

You can also refer to the following Metalink Documents for Disabling Oracle Database Vault before the upgrade and enabling it after the upgrade.

Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
Note 453902.1 - Enabling and Disabling Oracle Database Vault in WINDOWS

Step 10
Backing up Enterprise Manager Database Control Data

After upgrading to Oracle Database 11g release 2(11.2), if you want to downgrade Oracle Enterprise Manager Database Control you must save your Database Control files and data before upgrading your database. The emdwgrd utility can be used to keep a copy of your database control files and data before upgrading your database. The emdwgrd utility resides in the ORACLE_HOME/bin directory in the Oracle Database 11g release 2 (11.2) home.

1. Set ORACLE_HOME to your old Oracle home
2. Set ORACLE_SID to the SID of the database being upgraded.
3. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database is being upgraded.
4. Change directory to Oracle Database 11g release 2 (11.2) home.
5.
a- Run the following command for single instance database

$ emdwgrd -save -sid old_SID -path save_directory

where old_SID is the SID of the database being upgraded and save_directory is the path to the storage place you have chosen for your Database Control files and data:

@Note 870877.1 How To Save Oracle Enterprise Manager Database Control Data Before Upgrading The Single Instance Database To Other Release ?

b- For RAC database, remote copy is required across the cluster nodes. Define an environment variable to indicate which remote copy is configured. For example: setenv EM_REMCP /usr/bin/scp

$ emdwgrd -save -cluster -sid old_SID -path save_directory

Note: If 10g Oracle home is on a shared device, add -shared to the previous command line.

The above command(s) may core dump in HP-UX Itanium platform, which is a known issue. For more information, refer to following Metalink article

Note 562980.1 - emdwgrd core dumps : emdwgrd[228]: 10366 Memory fault(coredump)

6. Enter the SYS password for the database to be upgraded.
Note : On RAC databases you will be prompted to run '/tmp/racdwgrd_dbctl.sh' on each of the nodes.

Step 11

Configuring Network ACL's

Oracle Database 11g Release 2 (11.2) includes fine-grained access control to the UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP, or UTL_INADDR packages using Oracle XMLDB. If you have applications that use one of these packages, you must install OracleXML DB if it is not already installed. You must also configure network access control lists (ACLs) in the database before these packages can work as they did in prior releases. Actions are discussed in Post Upgrade tasks (Step 36), as the DBMS_NETWORK_ACL_ADMIN package is introduced after upgrading the database and not available in prior releases.

Step 12
This optional check is introduced to spot any logical corruption in underlying object and its dependent.
This proactive check is introduced to avoid any failure in database upgrade at a later stage due to such corruption.If there is corruption the upgrade will most likely fail

Check for corruption in the dictionary, use the following commands in SQL*Plus (connected as sys):

Set verify off
Set space 0
Set line 120
Set heading off
Set feedback off
Set pages 1000
Spool analyze.sql

SELECT 'Analyze cluster "'||cluster_name||'" validate structure cascade;'
FROM dba_clusters
WHERE owner='SYS'
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='NO'
AND (iot_type='IOT' OR iot_type is NULL)
UNION
SELECT 'Analyze table "'||table_name||'" validate structure cascade into invalid_rows;'
FROM dba_tables
WHERE owner='SYS'
AND partitioned='YES';

spool off

This creates a script called analyze.sql.
Now execute the following steps.

$ sqlplus "/ as sysdba"
SQL> @$ORACLE_HOME/rdbms/admin/utlvalid.sql
SQL> @analyze.sql

This script (analyze.sql) should not return any errors.

Note:
1. ORA-30657 might occur, if there is any external table is validated, which can be safely ignored as per Note 209355.1 ORA-30657: Using ANALYZE TABLE for an External Table
2. Errors shown below when executing analyze.sql can be ignored:
SP2-0734: unknown command beginning "SQL> SELEC..." - rest of line ignored.
SP2-0042: unknown command "SQL>" - rest of line ignored.
SP2-0734: unknown command beginning "SQL> spool..." - rest of line ignored.

Step 13
Ensure that all snapshot refreshes are successfully completed, and that replication is stopped.

SELECT DISTINCT(TRUNC(last_refresh))
FROM dba_snapshot_refresh_times;

Step 14
Ensure that no files need media recovery

SELECT * FROM v$recover_file;

This should return no rows.

Step 15
Ensure that no files are in backup mode

SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

This should return no rows.

Step 16
Resolve outstanding distributed transactions prior to the upgrade.

SQL> select * from dba_2pc_pending;

If this returns rows you should do the following:

SQL> SELECT local_tran_id
FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

Step 17
To check if a standby database exists, issue the following query:

SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%';

If this query returns a row, then sync the standby database with the Primary database.
1. Make sure all the logs are transported to the standby server after a final log switch
in the primary.
2. Start the recovery of the standby database with the NODELAY option.

Step 18
Disable all batch and cron jobs.

Step 19
Ensure the users sys and system have 'system' as their default tablespace.
You must have sufficient space in the tablespace or be set to extents unlimited.

SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM');

If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace to SYSTEM by using the below command.

SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;

Step 20
Ensure that if the aud$ table exists that it is in the sys schema and in the system tablespace.

SQL> SELECT owner,tablespace_name
FROM dba_tables
WHERE table_name='AUD$';

If the AUD$ table is not in SYSTEM tablespace and not owned by the SYS user then before doing the upgrade put it back to the SYSTEM tablespace and it should be owned by SYS .

Note :If the AUD$ table exists, and is in use, upgrade performance can be effected depending on the number of records in the table.

Please refer to the following note for information on exporting and truncating the AUD$ table:

Note 979942.1 Database upgrade appears to have halted at SYS.AUD$ Table

NOTE:73408.1 How to Truncate, Delete, or Purge Rows from the Audit Trail Table SYS.AUD$

Step 21
Check whether database has any externally authenticated SSL users.

SQL> SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';

If any SSL users are found then Step 34 has to be followed after the upgrade

Step 22
Note down the location of datafiles, redo logs, control files. Also take a backup of all configuration files like listener.ora,tnsnames.ora,etc., from $ORACLE_HOME

SQL> SELECT name FROM v$controlfile;
SQL> SELECT file_name FROM dba_data_files;
SQL> SELECT group#, member FROM v$logfile;.

Step 23
a) Stop the listener for the database:

$ lsnrctl stop

Previous versions of the listener are not supported for use with an Oracle Database 11g Release 2 (11.2) database. However, it is possible to use the new version of the listener with previous versions of Oracle Database.
If you are upgrading from 9i or upgrading manually without using DBUA, run Oracle Net Configuration Assistant before upgrading the Oracle RAC database.

This is a two-step option.
You must first run Oracle Net Configuration Assistant from the old Oracle home to remove the old listener.
- Invoke the Netca
- Choose the configuration you want to do ==> Choose Listener Configuration
-Select what you want to do ====> Delete
-Select the listener you want to delete .

Then you must run Oracle Net Configuration Assistant again from the new Oracle Database 11g Release 2 (11.2) home to create a new listener.

- Invoke the Netca
- Choose the configuration you want to do ==> Choose Listener Configuration
-Select what you want to do ====> Add
-Provide the detail that is required to configure the listener.

You must remove the old listener before creating a new one. If you attempt to create a new listener from the new Oracle home first, and use the same name and port as the old listener, then Oracle Net Configuration Assistant returns an error.

Note:This is your only option if you want to upgrade your Oracle RAC database manually.

b) Stop other executable such as dbconsole, isqlplus

$ emctl stop dbconsole
$ isqlplusctl stop

Step 24
Shutdown the database.

$ sqlplus "/as sysdba"
SQL> shutdown immediate;

Back Up the Database

1- Perform Cold Backup
(or)
2- Take a backup using RMAN

Connect to RMAN:

rman "target / nocatalog"

RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT '%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE TO '';
}

--> backup_directory >> Location of the Database backup.
--> controlfile_backup_directory >> Location of the Controlfile backup.

Step 25
Make a backup of the init.ora file.
Comment out obsoleted parameters( Appendix A ) and change all deprecated parameters( Appendix B ).

* The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST.

According to Bug 8937877, CORE_DUMP_DEST is not deprecated.

Refer the below article for understanding directory structure in 11g and DIAGNOSTIC_DEST.

Note 454442.1 11g Install : Understanding about Oracle Base, Oracle Home and Oracle Inventory locations

* If you are upgrading from 9.2.0.x,the COMPATIBLE initialization parameter must be set to at least 10.0.0, which is the lowest possible setting for Oracle Database11g Release 2 (11.2) prior to the upgrade This value must remain throughout the upgrade and can be changed to the higher value after the upgrade has been completed successfully .(Please note, once you set the COMPATIBLE to 10.1 there is no way to downgrade to 9iR2 because of symptoms described in Note 388604.1 : ORA-00201 while downgrading from 10gR2 to 10gR1 or 9iR2 ).

Oracle recommends increasing the COMPATIBLE parameter only after complete testing of the upgraded database has been performed.

If you are upgrading from 10.1.0.x or 10.2.0.x then you can leave the COMPATIBLE parameter set to it's current value until the upgrade has been completed successfully. This will avoid any unnecessary ORA-942 errors from being reported in SMON trace files during the upgrade (because the upgrade is looking for 10.2 objects that have not yet been created)
* Adjust the values of the initialization parameters to at least the minimum value indicated by the Pre-Upgrade Information Tool. Make sure all path names in the parameter file are fully specified. You should not have relative path names in the parameter file.
* If you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE during the upgrade and set it back to true after the upgrade. If you are upgrading a cluster database, then modify the init.ora file in the same way that you modified the parameter file.

Note: Once the Parameter file is modified as per your requirement, copy the file to $ORACLE_HOME/dbs (database directory on Windows) of 11gR2 Oracle Home.

Step 26
Check for adequate freespace on archive log destination file systems. Note that if your database is in archivelog mode, then it is always desirable and advisable to upgrade the database in noarchivelog mode as that will reduce the time taken to upgrade the database. After the upgrade you can again put the database in the archivelog mode.

Step 27
If your operating system is Windows then complete the actions in this Step, else skip to next Step.

Stop the OracleServiceSID Oracle service of the database you are upgrading, where SID is the instance name. For example, if your SID is ORCL, then enter the following at a command prompt:

Set the environment to Source / Previous version (9.2 / 10.1 / 10.2 /11.1)

1- Stop the Oracle database service

C:\> NET STOP OracleServiceORCL

2- Delete Oracle service using ORADIM binary from which the database is upgraded to 11.2.

C:\> ORADIM -DELETE -SID ORCL

3- Create the Oracle Database 11g Release 2 (112) service at a command prompt using the ORADIM command of the new Oracle Database release:

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INIT.ORA

For Instance,

C:\> ORADIM -NEW -SID ORCL -INTPWD -STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INIT.ORA

Step 28
If your operating system is UNIX then complete this step, else skip to next Step.

1. Make sure the following environment variables point to the Oracle 11g Release directories:

- ORACLE_BASE
- ORACLE_HOME
- PATH

$ export ORACLE_HOME=
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=

Note : If ORACLE_BASE is not known, after setting Path towards 11g Oracle Home, execute 'orabase', which will point the location of base.

$ orabase
/uo1/app/oracle

2. Update the oratab entry, to set the new ORACLE_HOME pointing towards ORCL and disable automatic startup

Sample /etc/oratab

#orcl:/opt/oracle/product/10.2/db_1:N
orcl:/opt/oracle/product/11.2/db_1:N

Note : After /etc/oratab is updated to have sid and Oracle Home (11.2), you can execute oraenv (/usr/local/bin/oraenv) and set the environment. The input has to be the sid which is entered in /etc/oratab against 11g home.

for Instance,

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/opt/oracle/product/11.2/db_1 is /u01/app/oracle
[oracle@localhost ~]$

Upgrading Database to 11gR2

Step 29
At the operating system prompt, change to the $ORACLE_HOME/rdbms/admin directory of 11gR2 Oracle Home.

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE

Note :If you are upgrading from 9.2 and the sysaux table already exists then drop the existing sysaux tablespace.
Create the SYSAUX tablespace only if you are upgrading from Oracle Database9i Release 2 (9.2) with the following mandatory attributes:

ONLINE
PERMANENT
READ WRITE
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO

The Pre-Upgrade Information Tool provides an estimate of the minimum required size for the SYSAUX tablespace in the SYSAUX Tablespace section. Refer the output generated by utlu112i.sql script in Step 1. The following SQL statement would create a 500 MB SYSAUX tablespace for the database:

SQL> CREATE TABLESPACE SYSAUX
     DATAFILE '/sysaux01.dbf'
     SIZE 500M REUSE
     EXTENT MANAGEMENT LOCAL
     SEGMENT SPACE MANAGEMENT AUTO
     ONLINE;

Set the system to spool results to a log file for later verification after the upgrade is completed and start the upgrade script.

SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> spool off

These measures are an important final step to ensure the integrity and consistency of the newly upgraded Oracle Database software.Also, if you encountered a message listing obsolete initialization parameters when you started the database for upgrade, then remove the obsolete initialization parameters from the parameter file before restarting. If necessary, convert the SPFILE to a PFILE so you can edit the file to delete parameters.

Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql which provides a summary of the upgrade at the end of the spool log. It displays the status of the database components in the upgraded database and the time required to complete each component upgrade. Any errors that occur during the upgrade are listed with each component and must be addressed

$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu112s.sql

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> @catuppst.sql

This script can be run concurrently with utlrp.sql. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

SQL> @utlrp.sql

Check for the integrity of the upgraded database

Check for the integrity of the upgraded database by running dbupgdiag.sql script from below Metalink article

Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.

After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.

Post Upgrade Steps

Step 30

Modify the listener.ora file:

For the upgraded instance(s) modify the ORACLE_HOME parameter to point to the new ORACLE_HOME. Start the listener :

lsnrctl start

Step 31
Environment Variables

1. Make sure the following environment variables point to the Oracle 11g Release directories:

- ORACLE_BASE
- ORACLE_HOME
- PATH

Also check that your oratab file and any client scripts that set the value of ORACLE_HOME point to the Oracle Database 11g Release 2 (11.2) home.

Note : If you are upgrading a cluster database, then perform these checks on all nodes in which this cluster database has instances configured.

2. Modify /etc/oratab entry to use automatic startup

SID:ORACLE_HOME:Y

For Instance,
orcl:/opt/oracle/product/11.2/db_1:Y

Step 32

Please refer the following article for complete steps to upgrade the TimeZone file to 11 after upgrading the database ( Post upgrade steps )

Note 977512.1 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST

NOTE : If you have run DBMS_DST package to upgrade the timezone version to a newer one, e.g., version 11 during post-upgrade of Oracle Database Release 11.2 , before you downgrade to your previous Oracle database release, you must install version 11 timezone files into the directory
of $ORACLE_HOME/ORACORE/ZONEINFO on your old Oracle database release.

For patches of new timezone version files on Oracle Database Release 11.1 or older, please refer to metalink Note 412160.1 Updated DST transitions and new Time Zones in Oracle Time Zone File patches.

Step 33
Upgrade Statistics Tables Created by the DBMS_STATS Package

If you created statistics tables using the DBMS_STATS.CREATE_STAT_TABLE procedure, then upgrade these tables by executing the following procedure:

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab');

In the example, 'SYS' is the owner of the statistics table and 'dictstattab' is the name of the statistics table. Execute this procedure for each statistics table.

Step 34
Upgrade Externally Authenticated SSL Users

If you have upgraded from Oracle 9.2.0.x or 10.1.0.x, and you are using externally authenticated SSL users, then you must run the following command to upgrade those users:

ORACLE_HOME/rdbms/bin/extusrupgrade --dbconnectstring
--dbuser --dbuserpassword
-a

If you are upgrading from 10.2.0.x (or higher), then you are not required to run this command.

Step 35
Enable Database Vault

Refer to the following Metalink Documents for enabling Oracle Database Vault

Note 453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
Note 453902.1 - Enabling and Disabling Oracle Database Vault in WINDOWS

Step 36

Configure Fine-Grained Access to External Network Services

To avoid "ORA-24247: network access denied by access control list (ACL)" when executing UTL packages (Network related Packages), access has to be granted to user using these packages.

The following example first looks for any ACL currently assigned to host_name. If one is found, then the example grants user_name the CONNECT privilege in the ACL only if that user does not already have it. If no ACL exists for host_name, then the example creates a new ACL called ACL_name, grants the CONNECT privilege to user_name, and assigns the ACL to host_name.

DECLARE
acl_path VARCHAR2(4000);
BEGIN
SELECT acl INTO acl_path FROM dba_network_acls
WHERE host = 'host_name' AND lower_port IS NULL AND upper_port IS NULL;
IF DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE(acl_path,'principal','privilege') IS NULL THEN
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl_path,'principal', is_grant, 'privilege');
END IF;
EXCEPTION
WHEN no_data_found THEN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL('ACL_name.xml','ACL description', 'principal', is_grant, 'privilege');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL('ACL_name.xml','host_name');
END;

COMMIT;

acl_name.xml => Enter a name for the access control list XML file.
ACL description => 'file description',
principal => 'user_or_role',
is_grant => TRUE|FALSE,
privilege => 'connect|resolve',
host_name => host name

Refer the below note on how to use DBMS_NETWORK_ACL_ADMIN Package and also to avoid ORA-24247 : network access denied by access control list (ACL)

Note 453786.1 ORA-24247 When Executing UTL_HTTP UTL_INADDR Packages

Step 37
Edit init.ora:

- If you changed the CLUSTER_DATABASE parameter prior the upgrade set it back to TRUE
- Migrate your initialization parameter file to a server parameter file.

Create a server parameter file with a initialization parameter file

SQL> create spfile from pfile;

This will create a spfile as a copy of the init.ora file located in $ORACLE_HOME/dbs (UNIX) & %ORACLE_HOME%\database (Windows).

Step 38
Change Passwords for Oracle-Supplied Accounts

Depending on the release from which you upgraded, there might be new Oracle supplied accounts. Oracle recommends that you lock all Oracle supplied accounts except for SYS and SYSTEM, and expire their passwords, thus requiring new passwords to be specified when the accounts are unlocked.

You can view the status of all accounts by issuing the following SQL statement:

SQL> SELECT username, account_status FROM dba_users ORDER BY username;

To lock and expire passwords, issue the following SQL statement:

SQL> ALTER USER username PASSWORD EXPIRE ACCOUNT LOCK;

Step 39

Upgrading Oracle Text

This is only needed if Oracle text is in use .

Copy the following files from the previous Oracle home to the new Oracle home:

* Stemming user-dictionary files
* User-modified KOREAN_MORPH_LEXER dictionary files
* USER_FILTER executables

To obtain a list of the above files, use:

$ORACLE_HOME/ctx/admin/ctxf.txt
$ORACLE_HOME/ctx/admin/ctxf.sql

where version is 920,101,102

For instance, if upgrading from 10.2.0

1. For User Extended Knowledge Base files check
$ORACLE_HOME/ctx/admin/ctxf102.txt
2. Execute the script as database user SYS,SYSTEM, or CTXSYS
$ORACLE_HOME/ctx/admin/ctxf102.sql

If your Oracle Text index uses KOREAN_LEXER which was deprecated in Oracle 9i and desupported in Oracle 10g Release 2, see below Note for further information on manual migration from KOREAN_LEXER to KOREAN_MORPH_LEXER.

Note 300172.1 Obsolescence of KOREAN_LEXER Lexer Type

Step 40
Upgrade the Oracle Cluster Registry (OCR) Configuration

If you are using Oracle Cluster Services, then you must upgrade the Oracle Cluster Registry (OCR) keys for the database.

Use one of the following options to upgrade the OCR configuration to 11g:

1. Use srvconfig from the Oracle Database 11g Release 1 (11.1) Oracle home.

For example:

srvconfig -upgrade -dbname db_name -orahome pre-11g_Oracle_home

2. Run srvctl.

For example:

From old Oracle_Home:

% $ORACLE_HOME/bin/srvctl remove database -d db_name

From 11g Oracle_Home:

% $ORACLE_HOME/bin/srvctl add database -d db_name -o
% $ORACLE_HOME/bin/srvctl add instance -d db_name -i instance -n node

Step 41

Configure Enterprise Manager

If your database is being managed by Oracle Enterprise Manager Database Control or Oracle Enterprise Manager Grid Control, then use the following command to update the configuration:

emca -upgrade (db | asm | db_asm) [-cluster] [-silent] [parameters]

You need to run this from the new Oracle Database 11g Oracle Home. When prompted, provide the Oracle home from which the configuration is being upgraded.

Appendix A: Initialization parameters deprecated in Oracle Database 11g release 2 (11.2)

PARALLEL_IO_CAP_ENABLED

To get a list of all deprecated initialization parameters, issue the following SQL statement:

SQL> SELECT name FROM v$parameter WHERE isdeprecated = 'TRUE';

A warning message is displayed at instance startup if a deprecated parameter is specified in the parameter file. In addition, all deprecated parameters are logged to the alert log at instance startup.

Appendix B :Initialization Parameters Obsolete in Oracle Database 11g Release 2 (11.2)

DRS_START
SQL_VERSION

Known Issues

Revision History
03-Sep-2009 Article Created

27-Oct-2009 Changed Indirect upgrade table value from 9.2.0.3(or lower) to 9.2.0.7(or lower)

27-Oct-2009 Check the certification of the 11g R2 with you platform before installing the software

29-OCt-2009 Note in step 32

References

NOTE:276914.1 - The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g and 11g
NOTE:300172.1 - Obsolescence of KOREAN_LEXER Lexer Type
NOTE:359145.1 - Impact of 2007 USA daylight saving changes on the Oracle database
NOTE:412160.1 - Updated DST transitions and new Time Zones in Oracle Time Zone File patches
NOTE:414590.1 - Time Zone IDs for 7 Time Zones Changed in Time Zone Files Version 3 and Higher, Possible ORA-1882 After Upgrade
NOTE:453786.1 - ORA-24247 When Executing UTL_HTTP UTL_INADDR Packages
NOTE:453902.1 - How To Enable And/Or Disable Oracle Database Vault
NOTE:453903.1 - Enabling and Disabling Oracle Database Vault in UNIX
NOTE:454442.1 - 11g Install : Understanding about Oracle Base, Oracle Home and Oracle Central/Global Inventory locations
NOTE:556610.1 - Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
NOTE:560336.1 - Script to Check Schemas with Stale Statistics
NOTE:560980.1 - Unable to Display Ultra Search Administration Page on 11gR1
NOTE:870877.1 - How To Save Oracle Enterprise Manager Database Control Data Before Upgrading The Single Instance Database To Other Release ?
NOTE:944122.1 - How To Upgrade The Timezone File Older Than Version 11 Using DBMS_DST Package

FaceDBA'S (Mahesh's Oracle DBA Blog)

Friday, December 20, 2013

Complete Checklist for Manual Upgrades to 10gR2

No comments:

Post a Comment

Contributors