Oracle数据库升级技术方案Oracle Database Upgrade Support目录一说明 (3)二前期准备工作 (4)2.1系统检查 (4)2.2操作系统补丁包检查 (5)2.3系统组件检查 (5)2.4无效对象检查 (5)2.5 temp文件检查 (6)2.6 SYS和SYSTEM重复对象检查 (6)2.7配置闪回操作 (7)2.8前期11.2.0.3软件安装 (7)三创建回退方案 (7)3.1关闭数据库，停监听，停dbconsole 停容灾 (7)3.2 创建闪回点 (7)3.3备份控制文件和redo (8)四数据库升级前检查 (8)五数据库升级 (8)六升级结束后数据库检查 (9)七升级失败的回退措施 (9)7.1重定向10g目录 (9)7.2闪回数据库 (10)7.3 dd还原redo和控制文件 (10)7.4重建控制文件 (10)7.5查看Tempfile (11)一说明数据库为10g单机环境，目标升级为11g环境，操作系统为5，实例名称：zhcj 综合采集IP：130.36.6.8二前期准备工作2.1系统检查针对zhcj数据库进行基本的操作系统检查，包括系统配置，版本，操作系统内存，交换分区，tmp 空间，必要的操作系统包检查，无效对象检查。

包检查：bos.adt.basebos.adt.libbos.adt.libmbos.perf.libperfstat 5.3.9.0 or laterbos.perf.perfstatbos.perf.proctoolsxlC.aix50.rte.10.1.0.0 or latergpfs.base 3.2.1.8 or laterP570A_ZHCJ:/# lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat bos.perf.perfstat bos.perf.proctools xlC.aix50.rte gpfs.base <Fileset Level State Description----------------------------------------------------------------------------Path: /usr/lib/objreposbos.adt.base 5.3.9.0 COMMITTED Base Application DevelopmentToolkitbos.adt.lib 5.3.9.0 COMMITTED Base Application DevelopmentLibrariesbos.adt.libm 5.3.9.0 COMMITTED Base Application DevelopmentMath Librarybos.perf.libperfstat 5.3.9.0 COMMITTED Performance Statistics LibraryInterfacebos.perf.perfstat 5.3.9.0 COMMITTED Performance StatisticsInterfacebos.perf.proctools 5.3.9.0 COMMITTED Proc Filesystem ToolsxlC.aix50.rte 10.1.0.0 COMMITTED XL C/C++ Runtime for AIX 5.3Path: /etc/objreposbos.perf.libperfstat 5.3.9.0 COMMITTED Performance Statistics LibraryInterfacebos.perf.perfstat 5.3.9.0 COMMITTED Performance StatisticsInterfacelslpp: Fileset gpfs.base not installed.2.2操作系统补丁包检查以下补丁包需要安装：•IZ42940•IZ49516•IZ52331•P570A_ZHCJ:/#instfix -i -k "IZ42940 IZ49516 IZ52331"以上补丁包需要安装2.3系统组件检查SQL> select comp_name,version,status from dba_registry;COMP_NAME VERSION STATUS---------------------------------- --------- -----------Oracle interMedia 10.2.0.4.0 VALIDOracle Enterprise Manager 10.2.0.4.0 VALIDOracle XML Database 10.2.0.4.0 VALIDOracle Text 10.2.0.4.0 VALIDOracle Expression Filter 10.2.0.4.0 VALIDOracle Rules Manager 10.2.0.4.0 VALIDOracle Workspace Manager 10.2.0.4.3 VALIDOracle Data Mining 10.2.0.4.0 VALIDOracle Database Catalog Views 10.2.0.4.0 VALIDOracle Database Packages and Types 10.2.0.4.0 INVALIDJServer JAVA Virtual Machine 10.2.0.4.0 VALIDOracle XDK 10.2.0.4.0 VALIDOracle Database Java Packages 10.2.0.4.0 VALID相关组件失效，检查由于sys用户下的包失效造成：SQL> select object_name,status from dba_objects where owner='SYS' and status<>'VALID';OBJECT_NAME STATUS--------------------------- ----------DBMS_SWRF_REPORT_INTERNAL INVALID升级前需要对该包进行重新编译修复该组件2.4无效对象检查sqlplus “/as sysdba”spool invald.lstselect owner,object_name,object_type,status from dba_objects where status<>'VALID';SQL> select count(*),status from dba_objects group by status;COUNT(*) STATUS---------- -------52311 VALID358 INVALIDcreate table invalid_object_20120515 as select * from dba_objects wherestatus<>’VALID’;2.5 temp文件检查select tablespace_name , file_name,bytes/1024/1024 ,status from dba_temp_files order by 1TABLESPACE_NAME FILE_NAME BYTES/1024/1024 STATUS--------------- ------------------------ --------------- ---------TEMP /dev/rzhcj01_4g_003 4095 AVAILABLETEMP /dev/rzhcj02_4g_079 4095 AVAILABLETEMP /dev/rzhcj01_4g_127 4095 AVAILABLETEMP /dev/rzhcj01_4g_191 4095 AVAILABLETEMP /dev/rzhcj02_4g_084 4095 AVAILABLETEMP /dev/rzhcj02_4g_080 4095 AVAILABLETEMP /dev/rzhcj02_4g_081 4095 AVAILABLETEMP /dev/rzhcj02_4g_082 4095 AVAILABLETEMP /dev/rzhcj02_4g_083 4095 AVAILABLETEMP /dev/rzhcj01_4g_192 4095 AVAILABLE2.6 SYS和SYSTEM重复对象检查SQL> select object_name, object_type from dba_objects where (object_name,object_type) in (select object_name,object_type from dba_objects where owner = 'SYS') and owner = 'SYSTEM';OBJECT_NAME OBJECT_TYPE----------------------- -------------------AQ$_SCHEDULES TABLEAQ$_SCHEDULES_PRIMARY INDEXDBMS_REPCAT_AUTH PACKAGEDBMS_REPCAT_AUTH PACKAGE BODY以上为正常并且允许重复的对象。

2.7配置闪回操作检查数据库（主备）是否有配置闪回区域，如果没有，配置闪回区域和闪回路径，升级结束后取消设置闪回目录大小和闪回地址：Alter system set db_recovery_file_dest_size=15G scope=bothAlter system set db_recovery_file_dest=’/archlog/backup/flashback’; scope=both 2.8前期11.2.0.3软件安装安装最新的11.2.0.3数据库软件以及最新PSU补丁13696216 和13916709三创建回退方案3.1关闭数据库，停监听，停dbconsole 停容灾$ export ORACLE_SID=zhcj$ sqlplus / as sysdba$ shutdown immediateEmctl stop dbconsole3.2 创建闪回点$ export ORACLE_SID=zhcj$ sqlplus / as sysdbaSQl>startup mountSQL>create restore point upgrade_point guarantee flashback database;3.3备份控制文件和redo2．备份redo和controlfile查找reodo文件：select group#,member from v$logfile;检查对应VG是否具有4K偏移量：$ dbfsize rzhcj01_4g_005Database file: rzhcj01_4g_005Database file type: raw deviceDatabase file size: 524160 8192 byte blocks以上说明lv上有4K偏移量通过dd做备份(前期检查VG发现对应具有4K偏移量)：dd if=/dev/rzhcj01_1g_002 of=/archlog/backup/bak/rzhcj01_1g_002.bak bs=4096 skip=1 dd if=/dev/rzhcj02_1g_002 of=/archlog/backup/bak/rzhcj02_1g_002.bak bs=4096 skip=1 dd if=/dev/rzhcj01_1g_003 of=/archlog/backup/bak/rzhcj01_1g_003.bak bs=4096 skip=1 dd if=/dev/rzhcj02_1g_003 of=/archlog/backup/bak/rzhcj02_1g_003.bak bs=4096 skip=1 dd if=/dev/rzhcj01_1g_004 of=/archlog/backup/bak/rzhcj01_1g_004.bak bs=4096 skip=1 dd if=/dev/rzhcj02_1g_004 of=/archlog/backup/bak/rzhcj02_1g_004.bak bs=4096 skip=1 控制文件：dd if=/dev/rzhcj01_1g_001 of=/archlog/backup/bak/rzhcj01_1g_001.bak bs=4096 skip=1 dd if=/dev/rzhcj02_1g_001 of=/archlog/backup/bak/rzhcj02_1g_001.bak bs=4096 skip=1四数据库升级前检查拷贝11g$ORACLE_HOME/rdbms/admin/utlu112i.sql 至/tmp/upgrade运行该脚本，检测升级环境sqlplus '/as sysdba'SQL> spool Database_Info.logSQL> @ utlu112i.sql。