How to upgrade Oracle from 10g to 11g

Oracle upgrades from 10g to 11g detailed steps database old version: 10.2.0.4

Database new version: 11.2.0.2

OS Version: Solaris 10

Reference Documentation: Complete checklist for Manual upgrades to 11GR2 [ID 837570.1]

Part I-Installing 11GR2 software

The installation of the software here is not explained in detail, you can refer to the appropriate documentation.

Here is to note: If you want to 11gr2 on the latest PSU or CPU, you can first in the software level of PSU or CPU, so you do not have to run two times catbundle.sql, reduce downtime

Between

Part II-Preliminary examination

1. Before upgrading, make sure all components and objects are valid:

Select substr (comp_name,1,40) comp_name, status, substr (version,1,10) version from Dba_registry Order by Comp_name; --For

Component

Select substr (object_name,1,40) object_name,substr (owner,1,15) Owner,object_type from dba_objects where status= ' INVALID '

Order BY Owner,object_type; --For objects

If there are invalid objects, run Utlrp.sql recompile the object.

2. Ensure that there are no duplicate objects under SYS and system:

Select Object_name, object_type from dba_objects where object_name| | Object_type in (select Object_name| | Object_type from

dba_objects where Wner = ' SYS ') and Wner = ' SYSTEM ';

The above statement can only return the following 4 records:

object_name object_type

---------------------------------------- ---------------

Dbms_repcat_auth PACKAGE Body

Dbms_repcat_auth PACKAGE

Aq$_schedules_primary INDEX

Aq$_schedules TABLE

If there are other records to return, you must delete the duplicate records according to the following document:

Duplicate Objects owned by SYS and SYSTEM Schema [ID 1030426.6]

Part III-Pre-upgrade work

(1) Copy the following files from 11gr2 Oracle home to a temporary folder:

$ORACLE _home/rdbms/admin/utlu112i.sql

(2) Log in to the database and run:

$ Sqlplus '/as Sysdba '

Sql> Spool Upgrade_info.log

sql> @utlu112i. sql

Sql> Spool Off

Sql>

The content in the generated Upgrade_info.log is important, and subsequent steps will be modified according to the contents of the file, so be sure to keep it.

(3) from the following document can be downloaded to the script dbupgdiag.sql:

Script. To Collect DB upgrade/migrate diagnostic Information (dbupgdiag.sql) [ID 556610.1]

Run this script:

CD <location of the Script>

$ sqlplus/as SYSDBA

Sql> alter session set nls_language= ' American ';

sql> @dbupgdiag. sql

Sql> exit

If the script reports a invalid object, run the following command to recompile the invalid object:

$ cd $ORACLE _home/rdbms/admin

$ sqlplus "/as SYSDBA"

sql> @utlrp. sql

(4) Since 10.2, the Connect role has become less privileged, so if you upgraded from 10.2 to 11g, you will need to grant the missing permissions after the upgrade, but if you are starting from

10.2 and then upgrade to 11g, you do not need to reassign permissions, this example is upgraded from 10.2.0.4 to 11g, so do not need this step.

(5) Generate a script to reconstruct the Dblink in case the database needs to be degraded. As with step 4, this example is upgraded from 10.2.0.4 to 11g, so it is not required.

(6) Check the timezone version, the main reference:

Actions for DST Updates when upgrading to Or applying the 11.2.0.2 patchset [ID 1201253.1]

Note: The 11g software has its own version 1-14 timezone.

First check the current timezone version:

Sql> Conn/as SYSDBA

Connected.

Sql>select version from V$timezone_file;

According to the current timezone version, it is divided into three different cases:

1 equals 14: This is already the version required for 11g, so there is no need to do anything before and after the upgrade, which is very rare.

2 above 14: The timezone version of the DST patch must be made available to 11g software before upgrading, which is also rare.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/