某政府部门ORACLE数据库系统数据迁移实施报告

某政府部门ORACLE数据库系统数据迁移实施报告-PAGE42-XXXX局资源库系统数据库迁移项目实施报告2010-11-27目录解决方案目前已采购新的HP服务器和存储设备。cup：6颗双核，每核1.6G。内存48G，操作系统：hpunix11iv3，磁盘阵列为28个146G。计划在新系统上安装Oracle10gRAC数据库软件。实施计划数据库迁移的流程本次数据库迁移需要从oracle9i（）迁移到oracle10g（）。迁移流程如下：将31数据库的数据通过RMAN迁移到2服务器上的临时数据库中。将2服务器上的临时数据库从升级到。将2服务器上的临时数据库从的数据库升级到将2服务器上的临时数据库从文件系统方式转变为ASM工作方式。将2服务器上的临时数据库转换为RAC工作模式，随即转变为正式环境。时间进度完成情况20项目协调会，确认实施计划及实施内容在新的机器上安装10gRAC()20准备9i测试环境准备迁移方案备份目前资源库的数据，并ftp到新的环境中。20测试整个迁移方案。预估迁移时间编写实施报告2010-12-0应用测试2010-12-0正式迁移下表为关键步骤实测耗时经修正后的列表XXXX局资源数据库迁移关键步骤耗时一览（不包含软件安装时间）步骤耗时131数据库备份3小时131备份数据ftp到622.5小时在服务器62上恢复数据库25分钟数据库升级到30分钟备份数据库30分钟数据库升级至1小时数据库备份30分钟数据文件迁移至ASM35分钟单实例迁移至RAC40分钟测试30分钟其它1小时总共：大约11小时实施结果62上测试用数据库已升级到，运行状态正常数据库已迁移到Itanuim服务器上，运行于RAC模式，运行状态正常实施过程服务器62和63的安装与配置操作系统环境准备修改内核参数KSI_ALLOC_MAX(NPROC*8)

EXECUTABLE_STACK=0

MAX_THREAD_PROC1024

MAXDSIZ1073741824bytes

MAXDSIZ_64BIT2147483648bytes

MAXSSIZ134217728bytes

MAXSSIZ_64BIT1073741824

MAXSWAPCHUNKS16384*

MAXUPRC((NPROC*9)/10)+1

MSGMAP(MSGTQL+2)

MSGMNI(NPROC)

MSGSEG32767

MSGTQL(NPROC)(B25293-01)

NCSIZE(NINODE+1024)*

NFILE(15*NPROC+2048)

NFLOCKS(NPROC)(B25293-01)

NINODE(8*NPROC+2048)

NKTHREAD(((NPROC*7)/4)+16)

NPROC4096

SEMMAP(SEMMNI+2)*

SEMMNI(NPROC)(B25293-01)

SEMMNS(SEMMNI*2)

SEMMNU(NPROC-4)

SEMVMX32767

SHMMAXAvailMem

SHMMNI512

SHMSEG120

VPS_CEILING64

创建Oracle软件安装所需文件系统文件系统lv名称/dev/vg00/lvora文件系统lvsize30GB文件系统mount点/oracle创建数据库迁移用文件系统在62主机上创建vgbak卷组，并创建lvora-bak，并在lvora-bak上创建文件系统mount到/database2路径。安装Oracle10gforItanuim软件安装准备修改核心参数已由HP完成存储准备数据库安装可用磁盘列表如下：disk-namedisk-sizevdisk-namehostname-1hostname-2mount-pointdisk421GOCR-1Ggabzyk01gabzyk02disk431GVote-1Ggabzyk01gabzyk02disk441GASMspfile-1Ggabzyk01gabzyk02disk451GASM-OTHER-1-1Ggabzyk01gabzyk02disk54100GASM-OTHER-2-1Ggabzyk01gabzyk02disk63100GASM-DATA-100G-1gabzyk01gabzyk02disk72100GASM-DATA-100G-2gabzyk01gabzyk02disk81100GASM-DATA-100G-3gabzyk01gabzyk02disk90100GASM-DATA-100G-4gabzyk01gabzyk02disk99100GASM-DATA-100G-5gabzyk01gabzyk02disk108100GASM-DATA-100G-6gabzyk01gabzyk02disk117100GASM-DATA-100G-7gabzyk01gabzyk02disk126100GASM-DATA-100G-8gabzyk01gabzyk02disk135100GASM-DATA-100G-9gabzyk01gabzyk02disk144100GASM-DATA-100G-10gabzyk01gabzyk02disk1531000Goracle-bakgabzyk01/backup网络准备host-info节点1名称GABZYK01节点2名称GABZYK02节点1LAN信息lan名称IP地址子网掩码网关lan0(心跳)lan250lan2lan3节点2LAN信息lan名称IP地址子网掩码网关lan0(心跳)无lan350lan2lan3创建oracle用户root用户分别登录alice01、alice02主机执行sam命令创建oracle用户和组，信息如下：用户名oracle口令oracleShellksh用户id500组信息组名：oinstall：500id命令输出uid=500(oracle)gid=500(oinstall)修改oracle用户.profile，在.profile文件中增加如下内容，并登录oracle用户exportORACLE_BASE=/oracleexportORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1exportORA_CRS_HOME=$ORACLE_BASE/product/10.2.0/crsexportLD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/local/libexportORACLE_SID=orcl12exportPATH=$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$PATHumask022unsetUSERNAME创建oracle安装目录mkdir–pORACLE_BASEmkdir–pORACLE_HOMEmkdir–pORA_CRS_HOME配置alice01、alice02两台主机的互信：root用户登录主机，编辑.rhosts文件，在文件中输入+，保存退出安装clusterware软件由于Itanuim服务器的操作系统版本高于Oracle软件需求版本，启动安装程序是会校验失败，导致不能启动安装程序，需要早启动安装程序时添加-ignoreSysPrereqs参数调高操作系统版本检查。具体命令是：runInstaller-ignoreSysPrereqs将oracleclusterware软件复制到alice01服务上，路径为：/oracle/product/10.2.0/crsoracle用户登录alice01主机exportTMP=/u01/tmpexportDISPLAY=客户端ip:0.0启动xmanagerpassivexclock测试图形应用能正常启动/runInstaller-ignoreSysPrereqs启动oracleClusterware安装程序根据提示安装软件根据安装程序提示完成安装后步骤执行crs_stat-t检查clusterware运行状态，输出应为：crs_stat-t名称类型目标状态主机orak01.gsdapplicationONLINEONLINEgabzyk01orak01.onsapplicationONLINEONLINEgabzyk01orak01.vipapplicationONLINEONLINEgabzyk01orak02.gsdapplicationONLINEONLINEgabzyk02orak02.onsapplicationONLINEONLINEgabzyk02orak02.vipapplicationONLINEONLINEgabzyk02安装database软件将oracledatabase软件复制到62服务上，路径为：/oracle/product/10.2.0/db_1oracle用户登录alice01主机exportTMP=/u01/tmpexportDISPLAY=客户端ip:0.0启动xmanagerpassivexclock测试图形应用能正常启动/runInstaller-ignoreSysPrereqs启动oracledatabase安装程序根据提示安装软件，安装过程中不创建数据库根据安装程序提示完成安装后步骤安装clusterware软件补丁将oracle补丁软件复制到62服务上.oracle用户登录62主机exportTMP=/u01/tmpexportDISPLAY=客户端ip:0.0启动xmanagerpassivexclock测试图形应用能正常启动/runInstaller-ignoreSysPrereqs启动oracle安装程序根据提示安装软件，应选择CRS_HOME作为安装路径根据安装程序提示完成安装后步骤安装database软件补丁将oracle补丁软件复制到62服务上.oracle用户登录alice01主机exportTMP=/u01/tmpexportDISPLAY=客户端ip:0.0启动xmanagerpassivexclock测试图形应用能正常启动/runInstaller-ignoreSysPrereqs启动oracle安装程序根据提示安装软件，应选择ORACLE_HOME作为安装路径根据安装程序提示完成安装后步骤配置Listeneroracle用户登录alice01服务器exportDISPLAY=客户端ip:0.0启动xmanagerpassivexclock测试图形应用能正常启动netca启动oracle网络配置助手选择cluster配置根据提示完成listener配置检查集群状态应为:2008-09-1914:43:39alice01/u01/10203patch$crs_stat-t名称类型目标状态主机ora01.lsnrapplicationONLINEONLINEgabzyk01orak01.gsdapplicationONLINEONLINEgabzyk01orak01.onsapplicationONLINEONLINEgabzyk01orak01.vipapplicationONLINEONLINEgabzyk01ora02.lsnrapplicationONLINEONLINEgabzyk02orak02.gsdapplicationONLINEONLINEgabzyk02orak02.onsapplicationONLINEONLINEgabzyk02orak02.vipapplicationONLINEONLINEgabzyk02输出显示两台服务器上的listener已自动添加到集群中，并且已经online配置ASM磁盘组oracle用户登录alice01服务器exportDISPLAY=客户端ip:0.0启动xmanagerpassivexclock测试图形应用能正常启动dbca启动数据库配置助手选择集群配置选择配置ASM，ASM实例sys用户口令为oracle创建ASM磁盘组，采用外部冗余方式，创建一个磁盘组：ORADATA数据库迁移过程备份生产数据库(31)由于目前的生产数据库是非归档方式，所以只能进行冷备。备份脚本如下：lsnrctstopshutdownimmediate;startupmount;rmantarget/run{ allocatechannelc1typediskmaxpiecesize1800m; allocatechannelc2typediskmaxpiecesize1800m; allocatechannelc3typediskmaxpiecesize1800m; allocatechannelc4typediskmaxpiecesize1800m; allocatechannelc5typediskmaxpiecesize1800m; allocatechannelc6typediskmaxpiecesize1800m; allocatechannelc7typediskmaxpiecesize1800m; allocatechannelc8typediskmaxpiecesize1800m; backup incrementallevel=0 format='/database2/backup/%U.bak' tag='cold_full_bak' database includecurrentcontrolfile; releasechannelc1; releasechannelc2; releasechannelc3; releasechannelc4; releasechannelc5; releasechannelc6; releasechannelc7; releasechannelc8;}run{ allocatechannelc1typedisk; backupformat='/database2/backup/%U.ctl'CURRENTCONTROLFILE; releasechannelc1;}shutdownimmediate备份控制文件、参数文件、密码文件：cp/database1/redo*/database2/backupcp/database1/control*/database2/backupcpinitorcl.ora/database2/backupcp密码文件/database2/backup备份过程中可通过如下的语句查询备份进度SELECTSID,SERIAL#,CONTEXT,SOFAR,TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2)"%COMPLETE"FROMV$SESSION_LONGOPSWHEREOPNAMELIKE'RMAN%'ANDOPNAMENOTLIKE'%aggregate%'ANDTOTALWORK!=0ANDSOFAR<>TOTALWORK;复制131备份数据至62服务器登录131服务器将/database2/backup目录的内容ftp到62服务器的/database2/backup下。ftp2binpromptoffmput*ftp完成后，比对两边服务器文件大小。恢复62上的数据库修改参数文件su-oraclecd/database2/backup/cpcontro*/database1/cpredo*/database1/cpinitorcl.oraorapworcl1/database2/9i/db_1/dbs/mvinitorcl.orainitorcl1.oramkdir-p/database2/9i/admin/orcl1/bdumpmkdir-p/database2/9i/admin/orcl1/cdumpmkdir-p/database2/9i/admin/orcl1/udumpvi/database2/9i/db_1/dbs/initorcl1.ora注释如下参数：sort_area_size=524288hash_join_enabled=TRUE修改如下参数：background_dump_dest=/database2/9i/admin/orcl1/bdumpcore_dump_dest=/database2/9i/admin/orcl1/cdumpuser_dump_dest=/database2/9i/admin/orcl1/udumpinstance_name=orcl1启动数据库exportORACLE_BASE=/database2/9iexportORACLE_HOME=$ORACLE_BASE/db_1exportPATH=$ORACLE_HOME/bin:$PATHexportORACLE_SID=orcl1startupnomountpfile='?/dbs/initorcl1.ora';createspfilefrompfile='?/dbs/initorcl1.ora';shutdownabortstartupnomountaltersystemsetsga_max_size=4gscope=spfile;altersystemsetpga_aggregate_target=2gscope=spfile;ALTERSYSTEMSETSHARED_POOL_SIZE='1024m'SCOPE=spfile;ALTERSYSTEMSETJAVA_POOL_SIZE='1024m'SCOPE=spfile;shutdownabort;startupmount;恢复数据库run{ allocatechannelc1typedisk; allocatechannelc2typedisk; allocatechannelc3typedisk; allocatechannelc4typedisk; allocatechannelc5typedisk; allocatechannelc6typedisk; allocatechannelc7typedisk; allocatechannelc8typedisk;setnewnamefordatafile1to'/database2/9i/oradata/orcl1/system01.dbf';setnewnamefordatafile2to'/database2/9i/oradata/orcl1/undotbs01.dbf';setnewnamefordatafile3to'/database2/9i/oradata/orcl1/example01.dbf';setnewnamefordatafile4to'/database2/9i/oradata/orcl1/indx01.dbf';setnewnamefordatafile5to'/database2/9i/oradata/orcl1/tools01.dbf';setnewnamefordatafile6to'/database2/9i/oradata/orcl1/users01.dbf';setnewnamefordatafile7to'/database2/9i/oradata/orcl1/ACCIDENT.dbf';setnewnamefordatafile8to'/database2/9i/oradata/orcl1/ACCi_DATA.dbf';setnewnamefordatafile9to'/database2/9i/oradata/orcl1/IMPORTVEH1.dbf';setnewnamefordatafile10to'/database2/9i/oradata/orcl1/ntiis_26.dbf';setnewnamefordatafile11to'/database2/9i/oradata/orcl1/undotbs03.dbf';setnewnamefordatafile12to'/database2/9i/oradata/orcl1/MANAGE1.dbf';setnewnamefordatafile13to'/database2/9i/oradata/orcl1/undotbs04.dbf';setnewnamefordatafile14to'/database2/9i/oradata/orcl1/WZZD_ZD1.dbf';setnewnamefordatafile15to'/database2/9i/oradata/orcl1/UNDOTBS11.dbf';setnewnamefordatafile16to'/database2/9i/oradata/orcl1/undotbs05.dbf';setnewnamefordatafile17to'/database2/9i/oradata/orcl1/undotbs02.dbf';setnewnamefordatafile18to'/database2/9i/oradata/orcl1/database2dacr.ora';setnewnamefordatafile19to'/database2/9i/oradata/orcl1/P_NEW_DATA1.ora';setnewnamefordatafile20to'/database2/9i/oradata/orcl1/P_INDEX.ora';setnewnamefordatafile21to'/database2/9i/oradata/orcl1/P_TEMP.ora';setnewnamefordatafile22to'/database2/9i/oradata/orcl1/ntiis.ora';setnewnamefordatafile23to'/database2/9i/oradata/orcl1/ntiis_2.ora';setnewnamefordatafile24to'/database2/9i/oradata/orcl1/ntiis_index.ora';setnewnamefordatafile25to'/database2/9i/oradata/orcl1/ntiis_12_1.ora';setnewnamefordatafile26to'/database2/9i/oradata/orcl1/ntiis_12_2.ora';setnewnamefordatafile27to'/database2/9i/oradata/orcl1/ntiis_index_12_1.ora';setnewnamefordatafile28to'/database2/9i/oradata/orcl1/ntiis_index_12_2.ora';setnewnamefordatafile29to'/database2/9i/oradata/orcl1/ntiis_3.dbf';setnewnamefordatafile30to'/database2/9i/oradata/orcl1/ntiis_4.dbf';setnewnamefordatafile31to'/database2/9i/oradata/orcl1/ntiis_5.dbf';setnewnamefordatafile32to'/database2/9i/oradata/orcl1/ntiis_6.dbf';setnewnamefordatafile33to'/database2/9i/oradata/orcl1/ntiis_7.dbf';setnewnamefordatafile34to'/database2/9i/oradata/orcl1/ntiis_8.dbf';setnewnamefordatafile35to'/database2/9i/oradata/orcl1/ntiis_9.dbf';setnewnamefordatafile36to'/database2/9i/oradata/orcl1/ntiis_10.dbf';setnewnamefordatafile37to'/database2/9i/oradata/orcl1/ntiis_11.dbf';setnewnamefordatafile38to'/database2/9i/oradata/orcl1/ntiis_12.dbf';setnewnamefordatafile39to'/database2/9i/oradata/orcl1/ntiis_13.dbf';setnewnamefordatafile40to'/database2/9i/oradata/orcl1/ntiis_14.dbf';setnewnamefordatafile41to'/database2/9i/oradata/orcl1/ntiis_index_1.ora';setnewnamefordatafile42to'/database2/9i/oradata/orcl1/ntiis_index_2.ora';setnewnamefordatafile43to'/database2/9i/oradata/orcl1/ntiis_15.dbf';setnewnamefordatafile44to'/database2/9i/oradata/orcl1/P_NEW_DATA2.ora';setnewnamefordatafile45to'/database2/9i/oradata/orcl1/ntiis_index_3.ora';setnewnamefordatafile46to'/database2/9i/oradata/orcl1/ntiis_12_3.ora';setnewnamefordatafile47to'/database2/9i/oradata/orcl1/IV_DATA1.ora';setnewnamefordatafile48to'/database2/9i/oradata/orcl1/IV_DATA2.ora';setnewnamefordatafile49to'/database2/9i/oradata/orcl1/IV_INDEX1.ora';setnewnamefordatafile50to'/database2/9i/oradata/orcl1/IV_INDEX2.ora';setnewnamefordatafile51to'/database2/9i/oradata/orcl1/IV_USER1.ora';setnewnamefordatafile52to'/database2/9i/oradata/orcl1/ntiis_index_4.ora';setnewnamefordatafile53to'/database2/9i/oradata/orcl1/ntiis_index_5.ora';setnewnamefordatafile54to'/database2/9i/oradata/orcl1/ntiis_16.dbf';setnewnamefordatafile55to'/database2/9i/oradata/orcl1/ntiis_17.dbf';setnewnamefordatafile56to'/database2/9i/oradata/orcl1/ntiis_18.dbf';setnewnamefordatafile57to'/database2/9i/oradata/orcl1/ntiis_19.dbf';setnewnamefordatafile58to'/database2/9i/oradata/orcl1/ntiis_20.dbf';setnewnamefordatafile59to'/database2/9i/oradata/orcl1/ntiis_12_4.ora';setnewnamefordatafile60to'/database2/9i/oradata/orcl1/ntiis_12_5.ora';setnewnamefordatafile61to'/database2/9i/oradata/orcl1/ntiis_12_6.ora';setnewnamefordatafile62to'/database2/9i/oradata/orcl1/ntiis_21.dbf';setnewnamefordatafile63to'/database2/9i/oradata/orcl1/ntiis_22.dbf';setnewnamefordatafile64to'/database2/9i/oradata/orcl1/ntiis_23.dbf';setnewnamefordatafile65to'/database2/9i/oradata/orcl1/ntiis_24.dbf';setnewnamefordatafile66to'/database2/9i/oradata/orcl1/ntiis_25.dbf';setnewnamefordatafile67to'/database2/9i/oradata/orcl1/ntiis_index_6.ora';setnewnamefordatafile68to'/database2/9i/oradata/orcl1/ntiis_index_7.ora';setnewnamefordatafile69to'/database2/9i/oradata/orcl1/undotbs06.dbf';setnewnamefordatafile70to'/database2/9i/oradata/orcl1/undotbs07.dbf';setnewnamefordatafile71to'/database2/9i/oradata/orcl1/undotbs08.dbf';setnewnamefordatafile72to'/database2/9i/oradata/orcl1/undotbs09.dbf';setnewnamefordatafile73to'/database2/9i/oradata/orcl1/ACCi_DATA_1.dbf';setnewnamefordatafile74to'/database2/9i/oradata/orcl1/ACCi_DATA_2.dbf';setnewnamefordatafile75to'/database2/9i/oradata/orcl1/ACCi_DATA_3.dbf';setnewnamefordatafile76to'/database2/9i/oradata/orcl1/ACCi_DATA_4.dbf';setnewnamefordatafile77to'/database2/9i/oradata/orcl1/ACCi_DATA_5.dbf';setnewnamefordatafile78to'/database2/9i/oradata/orcl1/ntiis_index_8.ora';setnewnamefordatafile79to'/database2/9i/oradata/orcl1/ntiis_index_9.ora';setnewnamefordatafile80to'/database2/9i/oradata/orcl1/P_NEW_DATA3.ora';setnewnamefordatafile81to'/database2/9i/oradata/orcl1/ntiis_27.dbf';setnewnamefordatafile82to'/database2/9i/oradata/orcl1/ntiis_28.dbf';setnewnamefordatafile83to'/database2/9i/oradata/orcl1/ntiis_12_7.ora';setnewnamefordatafile84to'/database2/9i/oradata/orcl1/ntiis_index_12_3.ora';setnewnamefordatafile85to'/database2/9i/oradata/orcl1/P_NEW_DATA4.ora';setnewnamefordatafile86to'/database2/9i/oradata/orcl1/ntiis_12_8.ora';restoredatabase;switchdatafileall; releasechannelc1; releasechannelc2; releasechannelc3; releasechannelc4; releasechannelc5; releasechannelc6; releasechannelc7; releasechannelc8;}alterdatabaseopen;服务器应该能够正常启动。创建临时表空间alterdatabasetempfile'/database1/temp01.dbf'drop;alterdatabasetempfile'/database1/temp02.dbf'drop;alterdatabasetempfile'/database1/orcl/ntiis_temp.ora'drop;alterdatabasetempfile'/database1/orcl/ntiis_temp_12_1.ora'drop;alterdatabasetempfile'/database1/orcl/IV_TEMP.ora'drop;ALTERTABLESPACETEMPADDTEMPFILE'/database2/9i/oradata/orcl1/temp01.dbf'size2047m;ALTERTABLESPACETEMPADDTEMPFILE'/database2/9i/oradata/orcl1/temp02.dbf'size1000m;ALTERTABLESPACENTIIS_TEMPADDTEMPFILE'/database2/9i/oradata/orcl1/ntiis_temp.ora'size1020m;ALTERTABLESPACENTIIS_TEMP_12ADDTEMPFILE'/database2/9i/oradata/orcl1/ntiis_temp_12_1.ora'size100m;ALTERTABLESPACEIV_TEMPADDTEMPFILE'/database2/9i/oradata/orcl1/IV_TEMP.ora'size500m;alterdatabasedatafile'/database2/9i/oradata/orcl1/system01.dbf'resize2048m;升级数据库至升级数据库cd/home/oracle/startupnomountaltersystemset"_kghdsidx_count"=1scope=spfile;shutdownabortSTARTUPMIGRATESPOOLpatch-9208.log@?/rdbms/admin/catpatch.sqlSPOOLOFF检查升级是否成功查看/home/oracle/patch-9208.log是否有错误。ORA-selectcomp_name,version,statusfromdba_registry;@?/rdbms/admin/utlrp.sql升级成功后进行备份mkdir-p/database2/before_upgrade10gstartupmount;rmantarget/run{ allocatechannelc1typedisk; allocatechannelc2typedisk; allocatechannelc3typedisk; allocatechannelc4typedisk; allocatechannelc5typedisk; allocatechannelc6typedisk; allocatechannelc7typedisk; allocatechannelc8typedisk; backup incrementallevel=0 format='/database2/before_upgrade10g/%U.bak' tag='before_upgrade10g' database includecurrentcontrolfile; releasechannelc1; releasechannelc2; releasechannelc3; releasechannelc4; releasechannelc5; releasechannelc6; releasechannelc7; releasechannelc8;}shutdownimmediatecp/database1/*/database2/before_upgrade10g升级数据库至升级前的检查su-oraclecp/oracle/product/10.2.0/db_1/rdbms/admin/utlu102i.sql/database2设置环境变量exportORACLE_BASE=/database2/9iexportORACLE_HOME=$ORACLE_BASE/db_1exportPATH=$ORACLE_HOME/bin:$PATHexportORACLE_SID=orcl1@/database2/utlu102i.sql创建sysaux表空间CREATETABLESPACEsysauxDATAFILE'/database2/9i/oradata/orcl1/sysaux.dbf'SIZE500MREUSEEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTOONLINE;修改相关参数altersystemsetsession_max_open_files=20scope=spfile;###alterdatabasedatafile'/database2/9i/oradata/orcl1/system01.dbf'resize2048m;查看connect权限SELECTgranteeFROMdba_role_privsWHEREgranted_role='CONNECT'andgranteeNOTIN('SYS','OUTLN','SYSTEM','CTXSYS','DBSNMP','LOGSTDBY_ADMINISTRATOR','ORDSYS','ORDPLUGINS','OEM_MONITOR','WKSYS','WKPROXY','WK_TEST','WKUSER','MDSYS','LBACSYS','DMSYS','WMSYS','OLAPDBA','OLAPSVR','OLAP_USER','OLAPSYS','EXFSYS','SYSMAN','MDDATA','SI_INFORMTN_SCHEMA','XDB','ODM');SELECTGRANTEE,PRIVILEGEFROMDBA_SYS_PRIVSWHEREGRANTEE='CONNECT'查看dblinkSELECT'create'||DECODE(U.NAME,'PUBLIC','public')||'databaselink'||CHR(10)||DECODE(U.NAME,'PUBLIC',Null,U.NAME||'.')||L.NAME||chr(10)||'connectto'||L.USERID||'identifiedby'''||L.PASSWORD||'''using'''||L.host||''''||chr(10)||';'TEXTFROMsys.link$L,sys.user$UWHEREL.OWNER#=U.USER#;收集系统统计信息execdbms_stats.create_stat_table('SYS','dictstattab');execdbms_stats.export_schema_stats('WMSYS','dictstattab',statown=>'SYS');execdbms_stats.export_schema_stats('MDSYS','dictstattab',statown=>'SYS');execdbms_stats.export_schema_stats('ORDPLUGINS','dictstattab',statown=>'SYS');execdbms_stats.export_schema_stats('OUTLN','dictstattab',statown=>'SYS');execdbms_stats.export_schema_stats('DBSNMP','dictstattab',statown=>'SYS');execdbms_stats.export_schema_stats('SYSTEM','dictstattab',statown=>'SYS');execdbms_stats.export_schema_stats('SYS','dictstattab',statown=>'SYS');execdbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO',cascade=>TRUE);execdbms_stats.gather_schema_stats('WMSYS',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO',cascade=>TRUE);execdbms_stats.gather_schema_stats('MDSYS',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO',cascade=>TRUE);execdbms_stats.gather_schema_stats('ORDPLUGINS',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO',cascade=>TRUE);execdbms_stats.gather_schema_stats('OUTLN',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO',cascade=>TRUE);execdbms_stats.gather_schema_stats('DBSNMP',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO',cascade=>TRUE);execdbms_stats.gather_schema_stats('SYSTEM',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO',cascade=>TRUE);execdbms_stats.gather_schema_stats('SYS',options=>'GATHER',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>'FORALLCOLUMNSSIZEAUTO',cascade=>TRUE);@?/rdbms/admin/utlrp.sqlcheckforcorruptioninthedictionarySetverifyoffSetspace0Setline120SetheadingoffSetfeedbackoffSetpages1000Spoolanalyze.sqlSelect'Analyzecluster"'||cluster_name||'"validatestructurecascade;'fromdba_clusterswhereowner='SYS'unionSelect'Analyzetable"'||table_name||'"validatestructurecascade;'fromdba_tableswhereowner='SYS'andpartitioned='NO'and(iot_type='IOT'oriot_typeisNULL)unionSelect'Analyzetable"'||table_name||'"validatestructurecascadeintoinvalid_rows;'fromdba_tableswhereowner='SYS'andpartitioned='YES';spooloff$sqlplus'/assysdba'SQL>@$ORACLE_HOME/rdbms/admin/utlvalid.sqlSQL>@analyze.sql确认物化视图selectdistinctowner,namemview,master_ownermaster_owner,last_refreshfromdba_mview_refresh_times;solveanyoutstandingunresolveddistributedtransactionselect*fromdba_2pc_pending;

确保sys用户和system用户使用各自的表空间

selectusername,default_tablespacefromdba_users

whereusernamein('SYS','SYSTEM');

确定和审计相关的设置selecttablespace_namefromdba_tableswheretable_name='AUD$';修改参数altersystemsetSHARED_POOL_SIZE=300mscope=spfile;altersystemsetLARGE_POOL_SIZE=300mscope=spfile;altersystemsetJAVA_POOL_SIZE=200mscope=spfile;shutdownimmediatesu-oraclecp/database2/9i/db_1/dbs/initorcl1.ora$ORACLE_HOME/dbs/startupnomountpfile='?/dbs/initorcl1.ora';createspfilefrompfile='?/dbs/initorcl1.ora';shutdownabort;startupnomount;altersystemsetstreams_pool_size=50331648scope=spfile;altersystemsetcompatible='.0altersystemsetbackground_dump_dest='/oracle/admin/orcl1/bdump'scope=spfile;altersystemsetcore_dump_dest='/oracle/admin/orcl1/cdump'scope=spfile;altersystemsetuser_dump_dest='/oracle/admin/orcl1/udump'scope=spfile;mkdir-p/oracle/admin/orcl1/bdumpmkdir-p/oracle/admin/orcl1/cdumpmkdir-p/oracle/admin/orcl1/udumpshutdownimmediate运行升级脚本cd/home/oraclestartupupgradespoolupgrade_10205.log@?/rdbms/admin/catupgrd.sqlSPOOLOFF验证升级是否成功@?/rdbms/admin/utlu102s.sqlselectcomp_name,status,versionfromdba_registry;编译错误对象shutdownimmediatestartuprestrict@?/rdbms/admin/utlrp.sql升级成功后的备份mkdir-p/database2/after_upgrade10g/startupmount；rmantarget/run{ allocatechannelc1typedisk; allocatechannelc2typedisk; allocatechannelc3typedisk; allocatechannelc4typedisk; allocatechannelc5typedisk; allocatechannelc6typedisk; allocatechannelc7typedisk; allocatechannelc8typedisk; backup incrementallevel=0 format='/database2/after_upgrade10g/%U.bak' tag='cold_full_bak_10g' database includecurrentcontrolfile; releasechannelc1; releasechannelc2; releasechannelc3; releasechannelc4; releasechannelc5; releasechannelc6; releasechannelc7; releasechannelc8;}run{ allocatechannelc1typedisk; backupformat='/database2/after_upgrade10g/%U.ctl'CURRENTCONTROLFILE; releasechannelc1;}shutdownimmediatecp/database1/*/database2/after_upgrade10g将数据库的文件系统转换为ASM以oracle用户登录createpfile='?/dbs/initorcl1.ora'fromspfile;修改参数*.db_create_file_dest='+ORADATA'*.db_create_online_log_dest_1='+ORADATA'*.db_create_online_log_dest_2='+ORADATA'去掉control_files启动数据库startupnomountpfile='?/dbs/initorcl1.ora';将数据文件迁移到ASMrestorecontrolfilefrom'/database1/control01.ctl';sql'alterdatabasemount';run{ allocatechannelc1typedisk; allocatechannelc2typedisk; allocatechannelc3typedisk; allocatechannelc4typedisk; allocatechannelc5typedisk; allocatechannelc6typedisk; allocatechannelc7typedisk; allocatechannelc8typedisk; allocatechannelc9typedisk; allocatechannelc10typedisk; allocatechannelc11typedisk; allocatechannelc12typedisk; allocatechannelc13typedisk; allocatechannelc14typedisk; allocatechannelc15typedisk; allocatechannelc16typedisk;backupascopydatabaseformat'+oradata';}switchdatabasetocopy;alterdatabaseopen;selectname,TOTAL_MB,FREE_MBfromv$asm_diskgroup;将在线日志文件迁移到ASMalterdatabaseaddlogfilegroup4size100m;alterdatabaseaddlogfilegroup5size100m;alterdatabaseaddlogfilegroup1size100m;altersystemswitchlogfile;alterdatabasedroplogfilegroup2;alterdatabasedroplogfilegroup3;alterdatabasedroplogfilegroup1;将tempfile迁移到ASMalterdatabasetempfile'/database2/9i/oradata/orcl1/temp01.dbf'drop;alterdatabasetempfile'/database2/9i/oradata/orcl1/temp02.dbf'drop;alterdatabasetempfile'/database2/9i/oradata/orcl1/ntiis_temp.ora'drop;alterdatabasetempfile'/database2/9i/oradata/orcl1/ntiis_temp_12_1.ora'drop;alterdatabasetempfile'/database2/9i/oradata/orcl1/IV_TEMP.ora'drop;altertablespaceTEMPaddtempfile'+oradata'size4000m;altertablespaceTEMPaddtempfile'+oradata'size1024m;altertablespaceNTIIS_TEMPaddtempfile'+oradata'size1024m;altertablespaceNTIIS_TEMP_12addtempfile'+oradata'size1024m;altertablespaceIV_TEMPaddtempfile'+oradata'size1024m;验证所有文件是否都迁移到ASMreportschema;setlines400colNAMEfora70selectname,statusfromv$