手工升级到oracle 10gR2 的步骤

郑全

In this Document
   Purpose
   Scope and Application
   Complete Checklist for Manual Upgrades to 10gR2
      Steps for Upgrading the Database to 10g Release 2
      Preparing to Upgrade
      Useful Hints
      Appendix A: Initialization Parameters Obsolete in 10g
      Appendix B: Initialization Parameters Deprecated in 10g
      Known issues
      Community Discussions
      Revision History
   References

 

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.4 to 10.2.0.5 - Release: 8.1.7 to 10.2
Oracle Server - Standard Edition - Version: 8.1.7.4 to 10.2.0.5 [Release: 8.1.7 to 10.2]
Information in this document applies to any platform.

 

Purpose

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.

 

 

郑全

Scope and Application

Database administrators

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 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

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 privileges can be restored.
Predefined Roles Evolution From 8i to 10gR2: CONNECT Role Change in 10gR2 (Doc ID 317258.1)

郑全

Step 4:

Create the script for dblink in case 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:

if you are upgrading from 8.1.7.4 skip to Step 6

if you are upgrading from 9i or 10.1 to 10.2.0.4 or 10.2.0.5 then follow first :

Note 553812.1 : Actions for the DSTv4 update in the 10.2.0.4 patchset
Note 1086400.1 : Actions for the DSTv4 update in the 10.2.0.5 patchset

and take note of needed actions listed in those notes.

if you are upgrading from 9i or 10.1 to 10.2.0.1, 10.2.0.2 or 10.2.0.3 we suggest strongly to consider to update to 10.2.0.4 or 10.2.0.5

if you really want to upgrade 9i or 10.1 to 10.2.0.1, 10.2.0.2 or 10.2.0.3 then check the 9i or 10.1 DSt version ( Note 412160.1 Updated Time Zones in Oracle Time Zone File patches )
if this is 2 and you upgrade to 10.2.0.1 or 10.2.0.2 you can skip to Step 6
if this is 3 and you upgrade to 10.2.0.3 you can skip to Step 6

Other wise log an Sr

郑全

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, 10g and 11g"

Check the current NLS_NCHAR_CHARACTERSET

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

If you are upgrading from Oracle 9i or 10.1 and the value is UTF8 or AL16UTF16 , skip to step 7.
If you are upgrading from Oracle 9i or 10.1 and the value is NOT UTF8 or AL16UTF16 log an SR to get help and correct the NLS_NCHAR_CHARACTERSET

If you are upgrading from Oracle 8.1.7 complete the following steps

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 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 the 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
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 10gR2, 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.