10.2.0.1升级到11g R2    

转自；http://www.dbaxiaoyu.com/archives/1157

关于数据库升级做的并不多，一般小版本的升级还算处理的比较多，而大版本的升级相对搞得较少。接下来需要将数据库升级到 11.2.0.1版本（一般11g R2会选用11.2.0.3版本，这里由于测试就只选用了11.2.0.1版本，升级步骤区别不大，11.2.0.2和11.2.0.3本来就是一个单独 的发行版本，不需要先安装升级到11.2.0.1再升级11.2.0.3）

11g的升级线路如下：
Minimum version of the database that can be directly upgraded to Oracle 11g Release 2 (11.2)
Source Database Target Database
9.2.0.8 or higher 11.2.x
10.1.0.5 or higher 11.2.x
10.2.0.2 or higher 11.2.x
11.1.0.6 or higher 11.2.x

The following database versions will require an indirect upgrade path:
Source Database Upgrade Path for Target Database
Target Database
7.3.3 (or lower) ----> 7.3.4 -> 9.2.0.8 ----> 11.2.x
8.0.5 (or lower) ----> 8.0.6 -> 9.2.0.8 ----> 11.2.x
8.1.7 (or lower) ----> 8.1.7.4 -> 10.2.0.2(or any higher 10GR2 version) ----> 11.2.x
9.0.1.3 (or lower) ----> 9.0.1.4 -> 10.2.0.2 (or any higher 10GR2 version) ----> 11.2.x
9.2.0.7(or lower) ----> 9.2.0.8 ----> 11.2.x

For example:

If source database is 8.1.7.0.0, the upgrade path to be followed is as below:
8.1.7.0.0 --> 8.1.7.4 --> 10.2.0.2(or any higher 10GR2 version)--> 11.2.x.

关于升级的版本线路说明可以参考maclean的blog，图形比较直观。

http://www.oracledatabase12g.com/archives/oracle-database-9i-10g-11g-r2-upgrade-roadmap.html

就我们这个升级而言，我们至少需要采用oracle 9.2.0.8,oracle 10.2.0.2,oracle 10.1.0.5,oracle 11.1.0.6这几个版本或者更高来直接升级到11gR2，不然我们需要先升级到中间的版本然后再次升级到oracle 11gR2，这里由于源库是10.2.0.1版本，所以这里小鱼选择先升级到10.2.0.5然后升级到11.2.0.1.
升级到10.2.0.5版本：

1 做好备份策略，避免升级失败
[oracle@dg01 ~]$ rman target /
RMAN> run{
2> backup database include current controlfile format '/home/oracle/backup_full_%U'
3> plus archivelog format '/home/oracle/backup_arch_%U delete input;
4> }

关闭应用程序避免产生新的数据，然后归档最后一组在线日志。
SQL> alter system archive log current;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

找到最后一组归档日志，如果升级失败，这个日志将是我们恢复的终点。
[oracle@dg01 ~]$ ls -lt /arch/
total 2589808
-rw-r----- 1 oracle oinstall 232960 Jul 1 04:34 1_175_809171947.dbf

Rman的备份集如下：
[oracle@dg01 ~]$ ls -l /home/oracle/backup_*
-rw-r----- 1 oracle oinstall 553015296 Jul 1 04:21 /home/oracle/backup_arch_0vodkcgf_1_1
-rw-r----- 1 oracle oinstall 518277120 Jul 1 04:21 /home/oracle/backup_arch_10odkchs_1_1
-rw-r----- 1 oracle oinstall 515874304 Jul 1 04:22 /home/oracle/backup_arch_11odkcja_1_1
-rw-r----- 1 oracle oinstall 324550656 Jul 1 04:22 /home/oracle/backup_arch_12odkckd_1_1
-rw-r----- 1 oracle oinstall 148986880 Jul 1 04:23 /home/oracle/backup_arch_13odkcl7_1_1
-rw-r----- 1 oracle oinstall 223232 Jul 1 04:23 /home/oracle/backup_arch_14odkcln_1_1
-rw-r----- 1 oracle oinstall 86016 Jul 1 04:25 /home/oracle/backup_arch_17odkcpq_1_1
-rw-r----- 1 oracle oinstall 1160749056 Jul 1 04:25 /home/oracle/backup_full_15odkclo_1_1
-rw-r----- 1 oracle oinstall 15794176 Jul 1 04:25 /home/oracle/backup_full_16odkcpl_1_1

2 备份已经完成后，接下来我们就可以放心的升级了，其实这里小鱼觉得如果升级最好还是新建一个oracle目录安装oracle软件然后打补丁到需要升级的 版本，然后用上述rman的备份集去做升级，这样可以最大速度保障升级失败后尽快保证应用可用，当然这里由于是虚拟机测试机就不那么讲究了。

升级数据库软件，注意这里需要关掉所有ORACLE_HOME下面的程序，例如sqlplus窗口、监听程序、em等。

软件升级完成后进行数据库的升级：
启动源库到nomount状态，这里修改两个数据
SQL> startup nomount;
SQL> alter system set java_pool_size=100M;
SQL> alter system set shared_pool_size=150M;
SQL> shutdown immediate;

升级数据库，更新数据字典
SQL> startup upgrade;
SQL> spool '/home/oracle/upgrade.log'
SQL> start '/db/oracle/product/10.2.0/db_1/rdbms/admin/catupgrd.sql'
SQL> spool off;

重启数据库，编译失效对象
SQL> shutdown immediate；
SQL> startup；
SQL> start '/db/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql';

创建temp表空间
SQL> alter talespace temp add tempfile ‘/db/oracle/product/oradata/xiaoyu/temp01.dbf’size 500m reuse autoextend on next 500m maxsize unlimited;

升级完成后发现该日志中有Oracle Real Application Clusters状态是invalid，检验视图确实real application clusters不可用了
SQL> col comp_name for a30
SQL> col version for a20
SQL> col status for a20

SQL> select comp_name,status,version from dba_registry;

COMP_NAME STATUS VERSION
------------------------------ -------------------- --------------------
Oracle Enterprise Manager VALID 10.2.0.5.0
Spatial VALID 10.2.0.5.0
Oracle interMedia VALID 10.2.0.5.0
OLAP Catalog VALID 10.2.0.5.0
Oracle XML Database VALID 10.2.0.5.0
Oracle Text VALID 10.2.0.5.0
Oracle Expression Filter VALID 10.2.0.5.0
Oracle Rule Manager VALID 10.2.0.5.0
Oracle Workspace Manager VALID 10.2.0.5.0
Oracle Data Mining VALID 10.2.0.5.0
Oracle Database Catalog Views VALID 10.2.0.5.0

COMP_NAME STATUS VERSION
------------------------------ -------------------- --------------------
Oracle Database Packages and T VALID 10.2.0.5.0
ypes

JServer JAVA Virtual Machine VALID 10.2.0.5.0
Oracle XDK VALID 10.2.0.5.0
Oracle Database Java Packages VALID 10.2.0.5.0
OLAP Analytic Workspace VALID 10.2.0.5.0
Oracle OLAP API VALID 10.2.0.5.0
Oracle Real Application Cluste INVALID 10.2.0.5.0
Rs

在mos上面找到一篇关于如何解决升级后dba_registry中status无效的文章：
How To Diagnose Components With NON VALID Status In DBA_REGISTRY After an Upgrade [ID 753041.1]

首先最主要的还是先查看其中的upgared.log升级日志，upgrade.log日志主要查如下几类：
grep "PL/SQL" upgrade.log
grep -i "ora-" upgrade.log
grep -i "mgr-" upgrade.log
grep -i "oci-" upgrade.log
grep -i "sp2-" upgrade.log
grep -i "sp1-" upgrade.log
grep -i "sp3-" upgrade.log
grep -i "pls-" upgrade.log
grep -i error upgrade.log
grep -i severe upgrade.log
grep -i fatal upgrade.log
grep -i fail upgrade.log
grep -i stop upgrade.log

[oracle@dg01 ~]$ grep -i "ora-" upgrade.log
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> The following statements will cause an "ORA-01722: invalid number"
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> The following PL/SQL block will cause an ORA-20000 error and
DOC> The following PL/SQL block will cause an ORA-20000 error and
ORA-29558: JAccelerator (NCOMP) not installed. Refer to Install Guide for
ORA-06512: at "SYS.DBMS_JAVA", line 236
执行上面的grep后发现主要是上面的ora错误，查找mos，由于没有使用Native PL/SQL compilation，这个错误可以忽略掉，

在upgrade.log日志中没有找到问题的根源，接下来检验对lib库进行关联：
Run the following commands to confirm the component has been linked/enabled at the binary level:
(Only applicable to UNIX/LINUX and the items listed below are the only items that require this check)

A result of 1 means enabled.
A result of 0 means disabled.

$ cd $ORACLE_HOME/rdbms/lib

$ ar -t libknlopt.a | grep -c kcsm.o (Real Application Clusters or RAC)

$ ar -t libknlopt.a | grep -c kzlilbac.o (Oracle Label Security or OLS)

$ ar -t libknlopt.a | grep -c kzvidv.o (Oracle Data Vault or DV)

$ ar -t libknlopt.a | grep -c kciwcx.o (Oracle Text or CTX)

$ ar -t libknlopt.a | grep -c xsyeolap.o (Oracle OLAP)

$ ar -t libknlopt.a | grep -c dmwdm.o (Oracle Data Mining or DM)

编译组件的库文件：
Real Application Clusters or RAC
--------------------------------
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk rac_on ioracle

Oracle Text or CTX
------------------
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk ctx_on ioracle

Oracle Label Security or OLS
----------------------------
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk lbac_on ioracle

or in 11.2 (can be used with UNIX/LINUX or WINDOWs)

$ chopt enable lbac

Oracle Data Vault or DV
-----------------------
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dv_on ioracle

or in 11.2 (can be used with UNIX/LINUX or WINDOWs)

$ chopt enable dv

Oracle Data Mining or DM
------------------------
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk dm_on ioracle

or in 11.2 (can be used with UNIX/LINUX or WINDOWs)

$ chopt enable dm

Oracle OLAP
-----------
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk olap_on ioracle

or in 11.2 (can be used with UNIX/LINUX or WINDOWs)

$ chopt enable olap

按照上面的办法重新链接库文件后，针对其中的cluster real application再次执行upgrade脚本。
SQL> startup upgrade;
SQL> spool '/home/oracle/cluster.log'
SQL> start '/db/oracle/product/10.2.0/db_1/rdbms/admin/catclust.sql'
SQL> spool off;

再次查看组件是否可用，发现Oracle Real Application Clusters的status已经是valid的。
SQL> select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS
------------------------------ -------------------- --------------------
Oracle Enterprise Manager 10.2.0.5.0 VALID
Spatial 10.2.0.5.0 VALID
Oracle interMedia 10.2.0.5.0 VALID
OLAP Catalog 10.2.0.5.0 VALID
Oracle XML Database 10.2.0.5.0 VALID
Oracle Text 10.2.0.5.0 VALID
Oracle Expression Filter 10.2.0.5.0 VALID
Oracle Rule Manager 10.2.0.5.0 VALID
Oracle Workspace Manager 10.2.0.5.0 VALID
Oracle Data Mining 10.2.0.5.0 VALID
Oracle Database Catalog Views 10.2.0.5.0 VALID

COMP_NAME VERSION STATUS
------------------------------ -------------------- --------------------
Oracle Database Packages and T 10.2.0.5.0 VALID
ypes

JServer JAVA Virtual Machine 10.2.0.5.0 VALID
Oracle XDK 10.2.0.5.0 VALID
Oracle Database Java Packages 10.2.0.5.0 VALID
OLAP Analytic Workspace 10.2.0.5.0 VALID
Oracle OLAP API 10.2.0.5.0 VALID
Oracle Real Application Cluste 10.2.0.5.0 VALID
Rs
再次编译无效对象
SQL> start '/db/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql';

一般小版本升级小鱼还是很少会碰见问题的，这次出现这个问题也告诉我们最好一定要备份，因为upgrade本来就是有风险的。

此时数据库已经升级到10.2.0.5了，接下来我们将这个数据库升级到11.2.0.1：

1 在新库10.2.0.5上面做好备份。
RMAN> run{
2> backup incremental level 0 database include current controlfile format '/u01/full_backup_%U'
3> plus archivelog format '/u01/full_arch_%U' delete input;
4> }

由于应用已经关闭了，不会产生新的生产环境数据，这里也就不归档最后一组日志了，此时备份集中的最后一组归档日志就是我们恢复的终点。

拷贝11g oracle软件中utlu112i.sql出来到源库端，这里都是同一台主机，直接cp到oracle家目录下
[oracle@dg01 app]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/
[oracle@dg01 admin]$ cp utlu112i.sql /home/oracle

在oracle 10g下面执行这个sql文件，这个脚本可以检验升级到11g R2的一些先决条件是否满足,注意需要在备份之前运行这个脚本，不然后面在11g catupgrd.sql时会出现下列错误
DOC> - "ORA-00942: table or view does not exist" ; or
DOC> - "ORA-00904: "TZ_VERSION": invalid identifier"" ; or
DOC> - "ORA-01722: invalid number"
。。。

SQL> spool '/home/oracle/upgrade_info.log';
SQL> start '/home/oracle/utlu112i.sql';
SQL> spool off；

检验升级条件后这里还是选择用10.2.0.5的备份集去恢复升级数据库到11G R2.

编辑参数文件：
[oracle@dg01 admin]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@dg01 admin]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@dg01 ~]$ cp /db/oracle/product/10.2.0/db_1/dbs/spfilexiaoyu.ora /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

[oracle@dg01 dbs]$ sqlplus / as sysdba
SQL> create pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initxiaoyu.ora' from spfile;

编辑参数文件后用该参数文件启动到nomount
SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initxiaoyu.ora'

恢复controlfile并启动mount
RMAN> restore controlfile from '/u01/full_backup_1aodmsr2_1_1';
RMAN> alter database mount;

先修改controlfile中的datafile和logfile的存储位置，然后restore database。
RMAN> restore database;

尝试恢复后最后一组日志
RMAN> run{
2> set until sequence 213;
3> recover database;
4> }

Upgrade打开数据库开始升级
SQL> alter database open resetlogs upgrade;
Database altered.

新建tempfile
SQL> alter tablespace temp add tempfile '/u01app/oracle/oradata/xiaoyu/temp01.dbf' size 500m reuse autoextend on next 500 maxsize unlimited;
SQL> alter tablespace temp drop tempfile '/db/oracle/product/oradata/xiaoyu/temp.277.805001779';

运行升级脚本catupgrd.sql：
SQL> spool '/home/oracle/upgrade.log'
SQL> start '/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catupgrd.sql'
SQL> spool off;

关闭数据库进行升级后一些维护

迁移10g的baseline到11g
SQL> start ‘/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catuppst.sql’
编译无效对象
SQL> start ‘/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlrp.sql’