手工升级到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.

 

 

郑全

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
NOTE:1086400.1 - Actions for the DSTv4 update in the 10.2.0.5 patchset

郑全

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.

郑全

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)

郑全

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

郑全

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 10gR2 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 10gR2 except when upgrading from Release 1 (10.1) to Release 2 (10.2).

 

如果是从以前32位版本中升级到64位版本,是自动升级

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 upgrade process.

郑全

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 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 <db_name> -orahome <pre-10g_Oracle_home>

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

From the 10g home, run the commands:

$ srvctl add database -d <db_name> -o <10g_Oracle_home>
$ srvctl add instance -d <db_name> -i <instance1_name> -n <node1>
$ srvctl add instance -d <db_name> -i <instance2_name> -n <node2>

郑全

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

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 you are upgrading from 8.1.7.4 skip to Step 40

If , while following,

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

utltzpv4.sql found rows then restore the rows you backed up now.

Check if a "select * from dba_scheduler_jobs;" from the sqlplus found in the oracle_home on the server (!!! this is important !!! - do NOT use a remote client) gives "ORA-01882: timezone region %s not found" , if so then you need to run Fix1882.sql mentioned in Note 414590.1. This will not harm the database.

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 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 created 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, particularly 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.