环境
目前环境10.2.0.4
OS版本 linux5.5
新环境数据库版本：11.2.0.4.17
OS 版本： linx6.5
一.安装
在192.0.2.12机器上安装软件
11.2.0.4 并打上11.2.0.4.170418
注意：在11.2.0.4上打上最新的PSU,
这样就不用跑两次catbundle.sql，减少停机时间。
二 检查
1. 在升级之前，确保所有的组件和对象都是valid：
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
COMP_NAME STATUS VERSION
-------------------------------------------------------------------------------- ---------------------- --------------------
JServer JAVA Virtual Machine VALID 10.2.0.4.0
OLAP Analytic Workspace VALID 10.2.0.4.0
OLAP Catalog VALID 10.2.0.4.0
Oracle Data Mining VALID 10.2.0.4.0
Oracle Database Catalog Views VALID 10.2.0.4.0
Oracle Database Java Packages VALID 10.2.0.4.0
Oracle Database Packages and Types VALID 10.2.0.4.0
Oracle Enterprise Manager VALID 10.2.0.4.0
Oracle Expression Filter VALID 10.2.0.4.0
Oracle interMedia VALID 10.2.0.4.0
Spatial VALID 10.2.0.4.0
SQL> 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;
no rows selected --针对对象
如果有invalid的对象，运行utlrp.sql重新编译对象。
三、升级前工作
1.
从11gR2的Oracle Home下拷贝以下文件至一个临时文件夹：
$ORACLE_HOME/rdbms/admin/utlu112i.sql
2.
SQL> ! pwd
/home/oracle10g
SQL> spool upgrade_info.log
SQL> set time on
18:34:03 SQL> set timing on
18:34:09 SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/utlu112i.sql
**********************************************************************
Elapsed: 00:00:04.40
18:34:41 SQL> spool off ;
保存此文件upgrade_info.log
3.检查脚本
set timing on
SQL> set time on
19:35:54 SQL> @/u03/arch/dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /u03/arch/
Upload db_upg_diag_erpdb_11_Jul_2017_0736.log from "/u03/arch/" directory
Elapsed: 00:00:00.00
编译失效对象
SQL> @utlrp.sql
四：异机恢复
1.备份数据库
脚本如下：
run{
crosscheck archivelog all;
crosscheck backup;
delete noprompt obsolete;
delete noprompt expired backup;
allocate channel d1 type disk ;
allocate channel d2 type disk;
backup format '/u03/bk/%T_%d_%s_%p.bk' tag 'full_data' database ;
backup format '/u03/bk/%T_%d_%s_%p.arc' tag 'full_arc' archivelog all delete all input ;
backup format '/u03/bk/%T_%d_%s_%p.ctl' tag 'full_ctl' current controlfile;
release channel d1;
release channel d2;
}
2. 把pfile initerpdb.ora 和密码文件orapwerpdb scp到192.0.2.12 上
3.把备份文件mount到192.0.2.12上，省去copy耗时间的麻烦。
4.修改pfile文件添加
_optim_dict_stats_at_db_cr_upg = false-此参数为了在运行catupgrd.sql慢的问题
5.在192.0.2.102
启数据库
startup nomount
rman target /
startup nomount
restore controlfile from '/u03/bk/20170712_ERPDB_12_1.ctl'
run
{
set newname for datafile 1 to '/u01/app/oracle/oradata/erpdb/sys01.bdf';--- 如果是asm可以改为磁盘组'+dgdata'
set newname for datafile 2 to '/u01/app/oracle/oradata/erpdb/syu02.bdf';
set newname for datafile 3 to '/u01/app/oracle/oradata/erpdb/sy03.bdf';
set newname for datafile 4 to '/u01/app/oracle/oradata/erpdb/sy04.bdf';
restore database;
switch datafile all;
recover database;
}
6.在生产库手动切3-4归档， （一下操作需要停生产库的业务）
如果数据库量大的话，备份归档日志
backup format '/u03/bk/%T_%d_%s_%p.arc' tag 'full_arc' archivelog all delete all input ; --
并应用日志。
7. catalog backuppiece '/u03/bk/20170712_ERPDB_17_1.arc'; --注册备份集
restore archivelog from sequence 48 until sequence 52; 还原
recover database；
8.临时表空间
Select 'alter database rename file '''||NAME||''' to'''||'/目录/tempfile/'||''';' from v$tempfile;
alter database rename file '/u03/app/oracle/oradata/erpdb/temp01.dbf' to'/u01/app/oracle/oradata/erpdb/temp01.dbf';
9.redo日志
select 'alter database rename file '''||MEMBER||''' to'''||'目录/redofile/'||''';'from v$logfile;
alter database rename file '/u03/app/oracle/oradata/erpdb/redo03.log' to'/u01/app/oracle/oradata/erpdb/redo03.log';
alter database rename file '/u03/app/oracle/oradata/erpdb/redo02.log' to'/u01/app/oracle/oradata/erpdb/redo02.log';
alter database rename file '/u03/app/oracle/oradata/erpdb/redo01.log' to'/u01/app/oracle/oradata/erpdb/redo01.log';
10. alter database open resetlogs upgrade;
spool upgrade.log
@?/rdbms/admin/catupgrd.sql --此步骤时间比较长（本测试在95分钟）
spool off ;
创建spfile文件
create spfile from pfile ;
startup
ALTER SYSTEM SET compatible='11.2.0.0.0' SCOPE=SPFILE;
exec dbms_stats.gather_dictionary_stats;
编译对象
@?/rdbms/admin/utlrp.sql
数据库层面应用PSU
SQL> @?/rdbms/admin/catbundle.sql psu apply
说明：
至此10gR2升级到11.2.0.4完成。其实在生产数据库升级时，升级性能测试比较重要也会和业务配合，需要考虑很多问题如：
1.应用中间件jdk
2.应用在10g-11R2的特性的用法。
3.压力测试
4.sql语句的性能（SPA测试）
参考文档
-
手动升级到 Oracle Database 11gR2 (11.2)的完整核对清单 (文档 ID 1674333.1)
-
数据库 升级/降级 兼容性矩阵 (文档 ID 1577660.1)
-
收集数据库升级/迁移诊断信息的脚本 (dbupgdiag.sql) (文档 ID 1577288.1)
-
如何下载并运行Oracle数据库预升级实用程序 (文档 ID 1577379.1)
-
Script. to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) [ID 556610.1]
-
During A Manual Database Upgrade To 11.2, Gathering DictionaryStatistics Takes Too Long (catupgrd.sql, cmpupend.sql) [ID 1425763.1
