Oracle数据库升级技术方案0001

1、Oracle 数据库升级技术方案Oracle Database Upgrade Support目录一 说明 3二 前期准备工作 42.1 系统检查 42.2 操作系统补丁包检查 52.3 系统组件检查 52.4 无效对象检查 52.5 temp 文件检查 62.6 SYS 和 SYSTEM 重复对象检查 62.7 配置闪回操作 72.8 前期 软件安装 7三 创建回退方案 73.1 关闭数据库，停监听，停 dbconsole 停容灾 73.2 创建闪回点 73.3 备份控制文件和 redo 8四 数据库升级前检查 8五 数据库升级 8六 升级结束后数据库检查 9七 升级失败的

2、回退措施 97.1 重定向 10g目录 97.2 闪回数据库 107.3 dd 还原 redo 和控制文件 107.4 重建控制文件 107.5 查看 Tempfile 11说明数据库为 10g 单机环境，目标升级为 11g 环境，操作系统为 5， 实例名称： zhcj 综合采集IP：需升级至 数据库模式单机IP 地址分配32BIT/64BIT64 bit操作系统版本aix 5300-09ORACLE版本ORACLE补丁ORACLE SIDzhcjUNIX用户/ 口令oracleUNIX组dba数据库缺省用

3、户 / 口令统一 :oracle服务器模式专用监听配置文件/opt/oracle/product//network/admin/listener.ora监听进程名称/opt/oracle/product//bin/tnslsnr LISTENER -inherit数据库日志文件/dev/rzhcj01 1g 0021023M/dev/rzhcj02 1g 0021023M/dev/rzhcj01 1g 0031023M/dev/rzhcj01 1g 0041023M/dev/rzhcj02 1g 0041023M数据库控制文件/dev/rzhcj01 1g 00

4、1/dev/rzhcj02 1g 001SGA配置sga target=28G sga max size=32GPGA配置2G二 前期准备工作2.1系统检查针对 zhcj 数据库进行基本的操作系统检查，包括系统配置，版本，操作系统内存，交换分区， 空间，必要的操作系统包检查，无效对象检查。包检查： bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat or later bos.perf.perfstat ctools xlC.aix50.rte. or later gpfs.

5、base or laterP570A_ZHCJ:/# lslpp -l bos.adt.base bos.adt.lib bos.adt.libm bos.perf.libperfstat bos.perf.perfstat ctools xlC.aix50.rte gpfs.base<Fileset Level State DescriptiontmpPath: /usr/lib/objreposbos.adt.basebos.adt.lib COMMITTED Base Application Development Toolki

6、t COMMITTED Base Application Development Librariesbos.adt.libm COMMITTED Base Application DevelopmentMath Librarybos.perf.libperfstat COMMITTED Performance Statistics Library Interfacebos.perf.perfstat COMMITTED Performance StatisticsIctools COM

7、MITTED Proc Filesystem ToolsxlC.aix50.rte COMMITTED XL C/C+ Runtime for AIX 5.3Path: /etc/objreposbos.perf.libperfstat COMMITTED Performance Statistics Library Interfacebos.perf.perfstat COMMITTED Performance Statistics Interfacelslpp: Fileset gpfs.base not installed.2.2操作系统补丁包

8、检查以下补丁包需要安装：? IZ42940? IZ49516? IZ52331?P570A_ZHCJ:/#instfix -i -k "IZ42940 IZ49516 IZ52331"以上补丁包需要安装2.3系统组件检查SQL> select comp_name,version,status from dba_registry;COMP_NAMEVERSIONSTATUSOracle interMediaOracle Enterprise ManagerOracle XML DatabaseOracle TextOracle Expression FilterOrac

9、le Rules ManagerOracle Workspace ManagerOracle Data MiningOracle Database Catalog Views.0 VALID.0 VALID.0 VALID.0 VALID.0 VALID.0 VALID.3VALID.0 VALID.0VALID.0 INVALID.0 VALIDOracle Database Packages and TypesJSe

10、rver JAVA Virtual MachineOracle XDK.0 VALID.0 VALIDOracle Database Java Packages 相关组件失效，检查由于 sys 用户下的包失效造成：SQL> select object_name,status from dba_objects where owner='SYS' and status<>'VALID'OBJECT_NAME STATUSDBMS_SWRF_REPORT_INTERNAL INVALID 升级前需要对该包进行重新编译修

11、复该组件2.4无效对象检查sqlplus “ /as sysdbaspool 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(*) STATUS52311 VALID358 INVALIDcreate table invalid_object_20120515 as select * from

12、dba_objects where status<> ' VALID' ;2.5 temp 文件检查select tablespace_name , file_name,bytes/1024/1024 ,status from dba_temp_files order by 1TABLESPACE_NAME FILE_NAMEBYTES/1024/1024 STATUSTEMP/dev/rzhcj01_4g_0034095AVAILABLETEMP/dev/rzhcj02_4g_0794095AVAILABLETEMP/dev/rzhcj01_4g_1274095A

13、VAILABLETEMP/dev/rzhcj01_4g_1914095AVAILABLETEMP/dev/rzhcj02_4g_0844095AVAILABLETEMP/dev/rzhcj02_4g_0804095AVAILABLETEMP/dev/rzhcj02_4g_0814095AVAILABLETEMP/dev/rzhcj02_4g_0824095AVAILABLETEMP/dev/rzhcj02_4g_0834095AVAILABLETEMP/dev/rzhcj01_4g_1924095AVAILABLE2.6 SYS 和 SYSTEM 重复对象检查SQL> select ob

14、ject_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_TYPEAQ$_SCHEDULES TABLE AQ$_SCHEDULES_PRIMARY INDEX DBMS_REPCAT_AUTH PACKAGE DBMS_REPCAT_AUTHPACKAGE

15、 BODY以上为正常并且允许重复的对象。2.7配置闪回操作检查数据库（主备）是否有配置闪回区域，如果没有，配置闪回区域和闪回路径，升级结束后取 消设置闪回目录大小和闪回地址：Alter system set db_recovery_file_dest_size=15G scope=bothAlter system set db_recovery_file_dest= ' /archlog/backup/flashback' scope=both2.8 前期 软件安装安装最新的 数据库软件以及最新 PSU补丁 13696216 和 1391670

16、9三 创建回退方案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 和 contr

17、olfile查找 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/rzhc

18、j01_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

19、_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

20、 bs=4096 skip=1四 数据库升级前检查拷贝 11g$ORACLE_HOME/rdbms/admin/utlu112i.sql 至 /tmp/upgrade 运行该脚本，检测升级环境sqlplus '/as sysdba'SQL> spool Database_Info.logSQL> utlu112i.sql。Spool off通过 create pfile= ' /tmp/upgrade/initzhcj.ora ' from spfile; 开始通过产生的日志信息进行参数文件 spfile 的修改 *.event='10298

21、 trace name context forever, level 32'五 数据库升级 拷贝原先生产库下 dbs 目录中文件至新的 11g 目录中， 修改部分废弃参数， 用 pfile 启动 startup upgrade升级数据库刷数据字典停 DBRA进程停监听SQL> STARTUP UPGRADESQL> SPOOL patch.logSQL> ?/rdbms/admin/catupgrd.sqlSQL> SPOOL OFFSQL> SHUTDOWN IMMEDIATESQL> STARTUP- 前期 PSU安装后 , catbundle.

22、sql未执行 , 现在执行SQL>shutdown immediateSQL>startupSQL> ?/rdbms/admin/catbundle.sql psu applySQL> ?/rdbms/admin/utlrp.sqlSQL> SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY;SQL> SHUTDOWN IMMEDIATESQL> STARTUPSQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;六 升级结束后数据库检查检查数据库组建

23、状态：SELECT COMP_NAME, VERSION, STATUS FROM SYS.DBA_REGISTRY; 拷贝生产库 tnsnames.ora 文件至新目录如果升级成功，没有错误，取消原先的闪回点操作，并关闭 db_recovery_file_dest 和 db_recovery_file_dest_size 参数七 升级失败的回退措施7.1重定向 10g 目录$sqlplus / as sysdbaSQL>shutdown immediate将 profile 重新指定到对应的原先 10g 目录7.2闪回数据库SQL>startup mountSQL>sele

24、ct flashback_on from v$database;SQL>flashback database to restore point upgrade_point;SQL>shutdown immediate7.3 dd 还原 redo 和控制文件通过 dd 操作将之前备份出来的 redo 和控制文件还原：dd if=/archlog/bak/rzhcj01_1g_002.bak of=/dev/rzhcj01_1g_002 bs=4096 seek=1 dd if=/archlog/bak/rzhcj02_1g_002.bak of=/dev/rzhcj02_1g_002

25、 bs=4096 seek=1 dd if=/archlog/bak/rzhcj01_1g_003.bak of=/dev/rzhcj01_1g_003 bs=4096 seek=1 dd if=/archlog/bak/rzhcj02_1g_003.bak of=/dev/rzhcj02_1g_003 bs=4096 seek=1 dd if=/archlog/bak/rzhcj01_1g_004.bak of=/dev/rzhcj01_1g_004 bs=4096 seek=1 dd if=/archlog/bak/rzhcj02_1g_004.bak of=/dev/rzhcj02_1g

26、_004 bs=4096 seek=1 控制文件： dd if=/archlog/bak/rzhcj01_1g_001.bak of=/dev/rzhcj01_1g_001 bs=4096 seek=1dd if=/archlog/bak/rzhcj02_1g_001.bak of=/dev/rzhcj02_1g_001 bs=4096 seek=17.4重建控制文件SQL>startup mountSQL>drop restore point upgrade_point;SQL> oradebug setmypidStatement processed.SQL> al

27、ter database backup controlfile to trace;Database altered.SQL> oradebug tracefile_nameSQL>SQL>alter database backup controlfile to trace SQL>shutdown immediateSQL>recreatectl.sqlSQL> select file#,to_char(checkpoint_change#) from v$datafile_header; SQL> select file#,to_char(checkpoint_change#) from v$datafile; SQL>alter database open7.5 查看 Tempfilealter tablespace TEMP add tempfile '/dev/rzhcj01_4g_