Oracle数据库升级技术方案文档格式.docx
《Oracle数据库升级技术方案文档格式.docx》由会员分享，可在线阅读，更多相关《Oracle数据库升级技术方案文档格式.docx（11页珍藏版）》请在冰豆网上搜索。
数据库缺省用户/口令
统一:
服务器模式
专用
监听配置文件
/opt/oracle/product/10.2.0.4/network/admin/listener.ora
监听进程名称
/opt/oracle/product/10.2.0.4/bin/tnslsnrLISTENER-inherit
数据库日志文件
/dev/rzhcj01_1g_002
1023M
/dev/rzhcj02_1g_002
/dev/rzhcj01_1g_003
/dev/rzhcj01_1g_004
/dev/rzhcj02_1g_004
数据库控制文件
/dev/rzhcj01_1g_001
/dev/rzhcj02_1g_001
SGA配置
sga_target=28Gsga_max_size=32G
PGA配置
2G
二前期准备工作
2.1系统检查
针对zhcj数据库进行基本的操作系统检查，包括系统配置，版本，操作系统内存，交换分区，tmp空间，必要的操作系统包检查，无效对象检查。
包检查：
bos.adt.base
bos.adt.lib
bos.adt.libm
bos.perf.libperfstat5.3.9.0orlater
bos.perf.perfstat
bos.perf.proctools
xlC.aix50.rte.10.1.0.0orlater
gpfs.base3.2.1.8orlater
P570A_ZHCJ:
/#lslpp-lbos.adt.basebos.adt.libbos.adt.libmbos.perf.libperfstatbos.perf.perfstatbos.perf.proctoolsxlC.aix50.rtegpfs.base<
FilesetLevelStateDescription
----------------------------------------------------------------------------
Path:
/usr/lib/objrepos
bos.adt.base5.3.9.0COMMITTEDBaseApplicationDevelopment
Toolkit
bos.adt.lib5.3.9.0COMMITTEDBaseApplicationDevelopment
Libraries
bos.adt.libm5.3.9.0COMMITTEDBaseApplicationDevelopment
MathLibrary
bos.perf.libperfstat5.3.9.0COMMITTEDPerformanceStatisticsLibrary
Interface
bos.perf.perfstat5.3.9.0COMMITTEDPerformanceStatistics
bos.perf.proctools5.3.9.0COMMITTEDProcFilesystemTools
xlC.aix50.rte10.1.0.0COMMITTEDXLC/C++RuntimeforAIX5.3
/etc/objrepos
lslpp:
Filesetgpfs.basenotinstalled.
2.2操作系统补丁包检查
以下补丁包需要安装：
∙IZ42940
∙IZ49516
∙IZ52331
∙
/#instfix-i-k"
IZ42940IZ49516IZ52331"
以上补丁包需要安装
2.3系统组件检查
SQL>
selectcomp_name,version,statusfromdba_registry;
COMP_NAMEVERSIONSTATUS
------------------------------------------------------
OracleinterMedia10.2.0.4.0VALID
OracleEnterpriseManager10.2.0.4.0VALID
OracleXMLDatabase10.2.0.4.0VALID
OracleText10.2.0.4.0VALID
OracleExpressionFilter10.2.0.4.0VALID
OracleRulesManager10.2.0.4.0VALID
OracleWorkspaceManager10.2.0.4.3VALID
OracleDataMining10.2.0.4.0VALID
OracleDatabaseCatalogViews10.2.0.4.0VALID
OracleDatabasePackagesandTypes10.2.0.4.0INVALID
JServerJAVAVirtualMachine10.2.0.4.0VALID
OracleXDK10.2.0.4.0VALID
OracleDatabaseJavaPackages10.2.0.4.0VALID
相关组件失效，检查由于sys用户下的包失效造成：
selectobject_name,statusfromdba_objectswhereowner='
SYS'
andstatus<
>
'
VALID'
;
OBJECT_NAMESTATUS
-------------------------------------
DBMS_SWRF_REPORT_INTERNALINVALID
升级前需要对该包进行重新编译修复该组件
2.4无效对象检查
sqlplus“/assysdba”
spoolinvald.lst
selectowner,object_name,object_type,statusfromdba_objectswherestatus<
selectcount（*）,statusfromdba_objectsgroupbystatus;
COUNT（*）STATUS
-----------------
52311VALID
358INVALID
createtableinvalid_object_20120515asselect*fromdba_objectswherestatus<
’VALID’;
2.5temp文件检查
selecttablespace_name,file_name,bytes/1024/1024,statusfromdba_temp_filesorderby1
TABLESPACE_NAMEFILE_NAMEBYTES/1024/1024STATUS
---------------------------------------------------------------
TEMP/dev/rzhcj01_4g_0034095AVAILABLE
TEMP/dev/rzhcj02_4g_0794095AVAILABLE
TEMP/dev/rzhcj01_4g_1274095AVAILABLE
TEMP/dev/rzhcj01_4g_1914095AVAILABLE
TEMP/dev/rzhcj02_4g_0844095AVAILABLE
TEMP/dev/rzhcj02_4g_0804095AVAILABLE
TEMP/dev/rzhcj02_4g_0814095AVAILABLE
TEMP/dev/rzhcj02_4g_0824095AVAILABLE
TEMP/dev/rzhcj02_4g_0834095AVAILABLE
TEMP/dev/rzhcj01_4g_1924095AVAILABLE
2.6SYS和SYSTEM重复对象检查
selectobject_name,object_typefromdba_objectswhere（object_name,object_type）in（selectobject_name,object_typefromdba_objectswhereowner='
）andowner='
SYSTEM'
OBJECT_NAMEOBJECT_TYPE
------------------------------------------
AQ$_SCHEDULESTABLE
AQ$_SCHEDULES_PRIMARYINDEX
DBMS_REPCAT_AUTHPACKAGE
DBMS_REPCAT_AUTHPACKAGEBODY
以上为正常并且允许重复的对象。
2.7配置闪回操作
检查数据库（主备）是否有配置闪回区域，如果没有，配置闪回区域和闪回路径，升级结束后取消
设置闪回目录大小和闪回地址：
Altersystemsetdb_recovery_file_dest_size=15Gscope=both
Altersystemsetdb_recovery_file_dest=’/archlog/backup/flashback’;
scope=both
2.8前期11.2.0.3软件安装
安装最新的11.2.0.3数据库软件以及最新PSU补丁13696216和13916709
三创建回退方案
3.1关闭数据库，停监听，停dbconsole停容灾
$exportORACLE_SID=zhcj
$sqlplus/assysdba
$shutdownimmediate
Emctlstopdbconsole
3.2创建闪回点
SQl>
startupmount
createrestorepointupgrade_pointguaranteeflashbackdatabase;
3.3备份控制文件和redo
2．备份redo和controlfile
查找reodo文件：
selectgroup#,memberfromv$logfile;
检查对应VG是否具有4K偏移量：
$dbfsizerzhcj01_4g_005
Databasefile:
rzhcj01_4g_005
Databasefiletype:
rawdevice
Databasefilesize:
5241608192byteblocks
以上说明lv上有4K偏移量
通过dd做备份（前期检查VG发现对应具有4K偏移量）：
ddif=/dev/rzhcj01_1g_002of=/archlog/backup/bak/rzhcj01_1g_002.bakbs=4096skip=1
ddif=/dev/rzhcj02_1g_002of=/archlog/backup/bak/rzhcj02_1g_002.bakbs=4096skip=1
ddif=/dev/rzhcj01_1g_003of=/archlog/backup/bak/rzhcj01_1g_003.bakbs=4096skip=1
ddif=/dev/rzhcj02_1g_003of=/archlog/backup/bak/rzhcj02_1g_003.bakbs=4096skip=1
ddif=/dev/rzhcj01_1g_004of=/archlog/backup/bak/rzhcj01_1g_004.bakbs=4096skip=1
ddif=/dev/rzhcj02_1g_004of=/archlog/backup/bak/rzhcj02_1g_004.bakbs=4096skip=1
控制文件：
ddif=/dev/rzhcj01_1g_001of=/archlog/backup/bak/rzhcj01_1g_001.bakbs=4096skip=1
ddif=/dev/rzhcj02_1g_001of=/archlog/backup/bak/rzhcj02_1g_001.bakbs=4096skip=1
四数据库升级前检查
拷贝11g$ORACLE_HOME/rdbms/admin/utlu112i.sql至/tmp/upgrade
运行该脚本，检测升级环境
sqlplus'
/assysdba'
spoolDatabase_Info.log
@utlu112i.sql
。
Spooloff
通过createpfile=’/tmp/upgrade/initzhcj.ora’fromspfile;
开始通过产生的日志信息进行参数文件spfile的修改
*.event='
10298tracenamecontextforever,level32'
五数据库升级
拷贝原先生产库下dbs目录中文件至新的11g目录中，修改部分废弃参数，用pfile启动startupupgrade
升级数据库刷数据字典
停DBRA进程
停监听
STARTUPUPGRADE
SPOOLpatch.log
@?
/rdbms/admin/catupgrd.sql
SPOOLOFF
SHUTDOWNIMMEDIATE
STARTUP
--前期PSU安装后,catbundle.sql未执行,现在执行
shutdownimmediate
startup
/rdbms/admin/catbundle.sqlpsuapply
@?
/rdbms/admin/utlrp.sql
SELECTCOMP_NAME,VERSION,STATUSFROMSYS.DBA_REGISTRY;
EXECDBMS_STATS.GATHER_DICTIONARY_STATS;
六升级结束后数据库检查
检查数据库组建状态：
SELECTCOMP_NAME,VERSION,STATUSFROMSYS.DBA_REGISTRY;
拷贝生产库tnsnames.ora文件至新目录
如果升级成功，没有错误，取消原先的闪回点操作，并关闭db_recovery_file_dest和db_recovery_file_dest_size参数
七升级失败的回退措施
7.1重定向10g目录
$sqlplus/assysdba
将profile重新指定到对应的原先10g目录
7.2闪回数据库
selectflashback_onfromv$database;
flashbackdatabasetorestorepointupgrade_point;
7.3dd还原redo和控制文件
通过dd操作将之前备份出来的redo和控制文件还原：
ddif=/archlog/bak/rzhcj01_1g_002.bakof=/dev/rzhcj01_1g_002bs=4096seek=1
ddif=/archlog/bak/rzhcj02_1g_002.bakof=/dev/rzhcj02_1g_002bs=4096seek=1
ddif=/archlog/bak/rzhcj01_1g_003.bakof=/dev/rzhcj01_1g_003bs=4096seek=1
ddif=/archlog/bak/rzhcj02_1g_003.bakof=/dev/rzhcj02_1g_003bs=4096seek=1
ddif=/archlog/bak/rzhcj01_1g_004.bakof=/dev/rzhcj01_1g_004bs=4096seek=1
ddif=/archlog/bak/rzhcj02_1g_004.bakof=/dev/rzhcj02_1g_004bs=4096seek=1
ddif=/archlog/bak/rzhcj01_1g_001.bakof=/dev/rzhcj01_1g_001bs=4096seek=1
ddif=/archlog/bak/rzhcj02_1g_001.bakof=/dev/rzhcj02_1g_001bs=4096seek=1
7.4重建控制文件
droprestorepointupgrade_point;
oradebugsetmypid
Statementprocessed.
alterdatabasebackupcontrolfiletotrace;
Databasealtered.
oradebugtracefile_name
alterdatabasebackupcontrolfiletotrace
shutdownimmediate
@recreatectl.sql
selectfile#,to_char（checkpoint_change#）fromv$datafile_header;
selectfile#,to_char（checkpoint_change#）fromv$datafile;
alterdatabaseopen
7.5查看Tempfile
altertablespaceTEMPaddtempfile'
/dev/rzhcj01_4g_003'
size4095mreuseautoextendoff;
/dev/rzhcj02_4g_079'
/dev/rzhcj01_4g_127'
/dev/rzhcj01_4g_191'
/dev/rzhcj02_4g_084'
/dev/rzhcj02_4g_080'
/dev/rzhcj02_4g_081'
/dev/rzhcj02_4g_082'
/dev/rzhcj02_4g_083'
/dev/rzhcj01_4g_192'
升级会员 