手工升级到oracle 10gR2 的步骤

郑全

Step 31:

Create a SYSAUX tablespace. In Oracle Database 10gR2, 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 unsecure 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 that 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 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.

郑全

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

郑全

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