ORACLE 10.2.0.5升级11.2.0.4

说明：Oracle 10.2.0.5.0版本需要先安装10.2.0.1.0版本，然后再升级到10.2.0.5.0.因为所有的10g高版本都必须从低版本升级而来，而不能直接安装。10.2.0.1.0的安装和10.2.0.5.0的升级这篇文章不做介绍。请参考我之前的文章！

升级前准备

每一次做变更前的首要任务就是做备份，做实验，生产文档，养成良好的实施习惯。（数据库备份请参考我的备份文章！）
–操作系统备份
–数据库软件的备份
–数据库的备份
–上传软件安装包

全新安装oracle软件

–安装11.2.0.4.0数据库软件，利用10g的安装环境进行解压安装
安装11g软件之前，先安装11数据库所需要的依赖包

yum -y install binutils-* compat-libstdc++-* compat-libstdc++-*.i686 elfutils-libelf* elfutils-libelf-devel* gcc-* gcc-c++-* glibc-* glibc-*.i686 glibc-common* glibc-devel* glibc-devel*.i686 glibc-headers* ksh* libaio* libaio*.i686 libaio* libaio*.i686 libgcc* libgcc*.i686 libstdc++* libstdc++*.i686 libstdc++-devel* make* sysstat* unixODBC* unixODBC*.i686 unixODBC-devel* unixODBC-devel*  compat* 

–将11g软件安装到与10g不同的安装目录下，比如：（图形化安装时，更改安装目录）
10g /u01/app/oracle/product/10.2/db_1
11g /u01/app/oracle/product/11.2/db_1

–安装完成后，在11g的安装目录下会生成一个预升级脚本
/u01/app/oracle/product/11.2/db_1/rdbms/admin/utlu112i.sql

运行预升级脚本，修改参数

SQL>spool upgrade_info.sql
SQL>@/u01/app/oracle/product/11.2/db_1/rdbms/admin/utlu112i.sql
SQL>spool off
将脚本运行产生的内容输出到upgrade_info.sql文件中

upgrade_info.sql内容如下：

@/u01/app/oracle/product/11.2/db_1/rdbms/admin/utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 10-29-2019 13:21:14
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          ORCL                                                                             
--> version:       10.2.0.5.0                                                                       
--> compatible:    10.2.0.1.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: 999 MB
--> SYSAUX tablespace is adequate for the upgrade.                                                  
.... minimum required size: 773 MB
--> TEMP tablespace is adequate for the upgrade.                                                    
.... minimum required size: 60 MB
WARNING: --> UNDOTBS2 tablespace is not large enough for the upgrade.                               
.... currently allocated size: 100 MB
.... minimum required size: 400 MB
.... increase current size by: 300 MB
.... tablespace is NOT AUTOEXTEND ENABLED.
.
**********************************************************************
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                                                   
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                                                   
--> 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.5.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

根据上面的提示做参数的变更
1.database:No changes are required
2.tablespace:(resize 或者 add 的方式都可以，调整大小只目标大小就好，以下分别以两种方式体现)

--系统表空间
SQL>select FILE_NAME,FILE_ID,BYTES/1024/1024 from dba_data_files;
SQL>alter database datafile '/oradata/orcl/system01.dbf' resize 1000M;
SQL>alter database datafile '/oradata/orcl/sysaux01.dbf' resize 800M;
SQL>alter database datafile '/oradata/orcl/UNDOTBS02.dbf' resize 500M;

--temp表空间
SQL>select file_name,file_id,bytes/1024/1024 from dba_temp_files;
SQL>alter tablespace temp add tempfile '/oradata/orcl/temp02.dbf' size 100M;

3.Flashback:No changes are required
4.Update Parameters:（可以直接生产pfile后修改文件内容）

SQL>alter system set sga_max_size=600M scope=spfie;(静态参数改完需要重启实例)
SQL>alter system set sga_target=600M;

5.Renamed Parameters:No changes are required
6.Obsolete/Deprecated Parameters:
在pfile文件中
–注释或者删除两个参数（这两个参数被diagnostic_dest参数替代）
background_dump_dest
user_dump_dest
–新增参数
diagnostic_dest=’/u01/app/oracle’ -----注意这里填写ORACLE_BASE的路径即可
–修改参数
compatible=‘11.2.0.0.0’ -----兼容性填11g的兼容性版本
7.Components:查看数据库组件

SQL>select comp_id,comp_name,version,status from dba_registry;

8.Miscellaneous Warnings：清空回收站

SQL>PURGE DBA_RECYCLEBIN；

9.Recommendations:
–9.1收集数据字典统计信息，以sysdba的角色执行以下语句

SQL>EXECUTE dbms_stats.gather_dictionary_stats；

–9.2 Oracle建议在升级之前检查正在执行的事件，以sysdba的角色执行以下语句

SQL>SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'；
SQL>SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'；

最后将所有的变革写入PFILE文件，然后将生产的pfile文件提供给11g数据库使用

更改11g环境变量

–安装完成之后，修改.bash_profile
ORACLE_HOME=/u01/app/oracle/product/11.2/db_1

–修改/etc/oratab
orcl:/u01/app/oracle/product/11.2/db_1:N

关闭10g数据库

SQL>shutdown immediate

高版本软件以upgrade方式打开低版本数据库，运行升级脚本，升级数据库组件

–连接数据库时一定要确定是11g环境

SQL>startup upgrade pfile='/home/oracle/pfile.ora';
SQL>@?/rdbms/admin/catupgrd.sql

重启实例

–升级完成后会自动关闭实例，用spfile重启实例.

SQL>startup upgrade pfile='/home/oracle/pfile.ora';
SQL>shutdown immediate
SQL>startup

–查看失效对象并重新编译

SQL>SELECT owner,object_name,object_type,status FROM dba_objects WHERE status = 'INVALID';
SQL>@?/rdbms/admin/utlrp.sql

升级完成后查看数据库的组件状态

SQL>select comp_id,comp_name,version,status from dba_registry;

如果组件全是valid状态，代表升级成功

另外：
将监听文件，别名文件，密码文件从10g安装目录传输到11g安装目录

cp /u01/app/oracle/product/10.2/db_1/network/admin/listener.ora /u01/app/oracle/product/11.2/db_1/network/admin/listener.ora
cp /u01/app/oracle/product/10.2/db_1/network/admin/tnsnames.ora  /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
cp /u01/app/oracle/product/10.2/db_1/dbs/orapworcl /u01/app/oracle/product/11.2/db_1/dbs/orapworcl

升级完成后与开发确认数据库是否能正常为应用提供服务
比如
job ,dblink …等是否可以用。

select * from dba_jobs;
select * from dba_db_links;

在10g升级到11g时，如果程序中用到UTL_TCP, UTL_HTTP, UTL_SMTP And UTL_MAIL会提示ORA-24247的错误。解决方案如下

begin
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(acl         => 'www.xml',
                                    description => 'www acl',
                                    principal   => 'APPS',
                                    is_grant    => true,
                                    privilege   => 'connect');
end;

begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl       => 'www.xml',
                                       principal => 'APPS',
                                       is_grant  => true,
                                       privilege => 'resolve');
end;

begin                               
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl  => 'www.xml',
                                    host => '*');
end;