实战：Oracle10.2.0.4异机迁移并升级到Oracle 11.2.0.4

实战：Oracle10.2.0.4异机迁移并升级到Oracle 11.2.0.4

环境:
源库：192.168.8.132  Oracle10.2.0.4
目标库：192.168.8.133 oracle11.2.0.4     
OS:linux 5.4-64位

将原10G的库迁移到新主机133上面，数据库版本同时升级为11.2.0.4

主要步骤：
一、环境检查(源库)
1、将11g中的utlu112i.sql，cp到源主机上，进行升级检查；
2、通过dbupgdiag.sql脚本来检查源库的一致性，如果有无效对象，@utlrp.sql可以对其进行重编译
3、对源库进行备份

二、迁移到新主机
1、修改相对应的pfile文件，因为10g里面的参数在11g中有些是废弃;
2、启动数据库到nomount下，恢复控制文件，恢复全库。
3、recover database;
4、进行升级
a、alter database open resetlogs upgrade;
b、执行如下：
SQL> spool upgrade.log
SQL> @catupgrd.sql

----这个执行时间比较长，大概1个多小时。
SQL> STARTUP
## run the below utlu112s.sql file for post upgrade checking and utlrp.sql file to compile the invalid objects.
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

执行以上脚本，再次检查数据库状态。

5、如果升级完成后，如果DST version有问题，可以对其进行修改，具体可以通过脚本自动执行，也可以手工执行，具体参考：
Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST (Doc ID 977512.1)
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.4 Patchset (Doc ID 1579838.1)

下面是相当记录

 

源库环境检查：

 

[oracle@ora1 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 12 15:54:36 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool upgrade_info.log                 
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 12-12-2014 15:55:42
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORCL
--> version:      10.2.0.4.0
--> compatible:    10.2.0.3.0
--> blocksize:    8192
--> platform:      Linux x86 64-bit
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 981 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 722 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 69 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
WARNING: --> "sga_target" needs to be increased to at least 596 MB
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
--> background_dump_dest        11.1      DEPRECATED  replaced by  "diagnostic_dest"
--> user_dump_dest              11.1      DEPRECATED  replaced by  "diagnostic_dest"
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views        [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Oracle Workspace Manager    [upgrade]  VALID
--> OLAP Analytic Workspace      [upgrade]  VALID
--> OLAP Catalog                [upgrade]  VALID
--> EM Repository                [upgrade]  VALID
--> Oracle Text                  [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages        [upgrade]  VALID
--> Oracle interMedia            [upgrade]  VALID
--> Spatial                      [upgrade]  VALID
--> Data Mining                  [upgrade]  VALID
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                [upgrade]  VALID
--> Oracle OLAP API              [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file older than version 14.
.... After the release migration, it is recommended that DBMS_DST package
.... be used to upgrade the 10.2.0.4.0 database timezone version
.... to the latest version which comes with the new release.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... Upgrade Guide for instructions to save the EM data prior to upgrade.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
        PURGE DBA_RECYCLEBIN
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
SQL> spool off;
SQL> create pfile='/home/oracle/pfile.ora' from spfile;
SQL> alter system set sga_target=600M scope=spfile;
SQL>  EXECUTE dbms_stats.gather_dictionary_stats;

===根据以上检查结果，，对源库进行调整；