OracleEBS11i-DBA维护笔记

Author:JarWang(王重东)QQ:[e-mail](R):Jarwang1OracleEBS11i--DBA笔记Author:Jarwang（王重东）CreateDate:February26,2009UpdateDate:ControlNo:CurrentEdition:1.0声明：本文可以任意免费转载、复制、传播。但您务必保持其完整性！Ifyoucopythisdocument,yououghttokeepthedocumentcompletely.Author:JarWang(王重东)QQ:[e-mail](R):Jarwang2DocumentControlModifyRecord日期作者版本更改参考February26,2009jarwang1.0Approved姓名职位签字Distribute编码姓名地点和位置1234Author:JarWang(王重东)QQ:[e-mail](R):Jarwang3IndexDocumentControl2ModifyRecord2Approved2Index3Preface5FoundationChapter6EBSArchitecture6EBSInstallation6EBSStartup6Startup&Shutdown6StartupEnterprise7MaintenanceChapter8ChangeAPPSpwd8EBSClone8ApplyEBSpatch8ApplyDBpatch9OracleDBMaitenance9RMANBackup9ORA-0002010ORA-0981710Movetablespacedatafile11Movesystemtablespacedatafile11RMANBackup12OracleApplicationMaitenance12PurgeApplicationlogfilesandreports12UpgradeChapter14DBUpgrade10g14Preparation14DownloadDatabase10g14InstalltheRelease10.2OracleSoftware15InstallOracleDatabase10gProductsfromthe10gCompanionCD20InstalltheLatestAvailablePatchSetReleaseandAnyRequiredPatches23Createnls/data/9idatadirectory23ShutdownApplicationsserverprocessesanddatabaselistener23RunthePre-UpgradeInformationTool23CopyinitSID.oratonewORACLE_HOME26ModifynewORACLE_HOMEinitSID.ora26Additionaldatabaseconfiguration27UpgradetheDatatabase27InstallOracleDataMiningandOLAP29Startthenewdatabaselistener29Runadgrants.sql30GrantcreateprocedureprivilegeonCTXSYS30ApplyAutoConfigPatchset31MigratingtoAutoConfigontheDatabaseTier33MigratingtoAutoConfigontheApplicationTier34GatherstatisticsforSYSschema35Re-createcustomdatabaselinks36Re-creategrantsandsynonyms36RestartApplicationServer36UpgradedTroublesome37Config10gEM37OptimizationChapter40ApplicationOptimization40DatabaseOptimization40Author:JarWang(王重东)QQ:[e-mail](R):Jarwang4MoveControlfile40SQLOptimization40QueryTablespaceDatafiles40OtherNotes41Author:JarWang(王重东)QQ:[e-mail](R):Jarwang5PrefaceOracleEBSDBA是一个很泛的概念，基本上DB与APP两者要负责。另外有的公司EBSDBA，还得负责EBS功能、EBS开发等。有点全能啦？Author:JarWang(王重东)QQ:[e-mail](R):Jarwang6FoundationChapterEBSArchitectureOracleEBS11i系统架构图：EBSInstallationEBS安装包虽然多达30G之多，但安装过程却相对简单。至少比安装Oracle数据库本身要来的简单。具体请参考：http://www.itpub.net/thread-1228782-1-1.htmlEBSStartupStartup&Shutdown对于EBSDBA来说，EBS启动脚本命令肯定是要熟悉的。这一点Oracle已经为我们考虑到啦，在EBS主页就可以查询的到。Author:JarWang(王重东)QQ:[e-mail](R):Jarwang7StartupEnterprise启动Oracle9i数据库的企业管理器oemctlstartoms#emappconsole报错：Xconnectiontolocalhost:10.0broken(explicitkillorservershutdown).解决：Author:JarWang(王重东)QQ:[e-mail](R):Jarwang8MaintenanceChapterChangeAPPSpwdApps用户在EBS中地位，如同sys、system在DB一样重要，所以第一件事情就是修改apps的密码。Oracle有专门提供一个工具来修改apps及其他应用模块的用户密码：FNDCPASS1、使用appltier用户登陆应用服务器。2、执行FNDCPASS[appltest@node2~]$FNDCPASSapps/apps0Ysystem/managerSYSTEMAPPLSYSapps654321注：如果是密码不能是纯数字或以数字开头。3、对11.5.10以后的版本，只要使用FNDCPASS即可，无须其他设置。EBSCloneEBSClone应该算得上是DBA最经常干的活之一啦。EBSClone可分为五个步骤：n在DB层执行预clone：perladpreclone.pldbTiern在APP层执行预clone：perladpreclone.plappsTiern关闭APP层、DB层及复制APP层、DB层目录n在DB层执行clone：perl./adcfgclone.pldbTiern在APP层执行clone：perl./adcfgclone.plappsTier具体请参考http://www.itpub.net/thread-1228782-1-1.htmlApplyEBSpatch在打EBS补丁前，应做好系统备份。EBS补丁是无法撤销的，另外就是打补丁前，仔细阅读readme。打EBS补丁的步骤：n关闭应用层，启动EBS数据库n切换应用到维护模式n解压补丁，运行adpatch命令，应用*****.drv补丁驱动n切换到正常模式，启动应用层Author:JarWang(王重东)QQ:[e-mail](R):Jarwang9ApplyDBpatchOracle数据库打补丁步骤：n关闭数据库n运行补丁程序，一般在补丁包中都有runInstaller文件。n升级数据库字典n启动数据库OracleDBMaitenanceRMANBackupEBS之RMAN备份脚本。#!/bin/shcurr_date=`date+%Y%m%d`prior_date=`date+%Y%m%d--date=-2day`source/home/oracle/.bash_profilecd~echo----------------beginbackup:`date+%Y%m%d%H:%M:%S`----------------------rmanEOFconnecttarget;run{configurecontrolfileautobackupoff;configureretentionpolicytoredundancy2;sql'altersystemarchivelogcurrent';allocatechannelc1devicetypedisk;backupspfiletag'ebs_spfile'format'/backup/rman_backup/spfile_ebs_%T';backupcurrentcontrolfiletag'ebs_controlfile'format'/backup/rman_backup/controlfile_ebs_%T';backupfulldatabasetag'ebs_database'format'/backup/rman_backup/db_ebs_%T';releasechannelc1;}deletenopromptobsolete;EOFcd/backup/rman_backupsmbclient//ip/ebs_backup-Uuser%passwordEOFputcontrolfile_ebs_$curr_dateputdb_ebs_$curr_datedelcontrolfile_ebs_$prior_datedeldb_ebs_$prior_dateAuthor:JarWang(王重东)QQ:[e-mail](R):Jarwang10EOFecho----------------endbackup:`date+%Y%m%d%H:%M:%S`-------------------------echoechoechoecho功能说明：将上述脚本写入conrtab，每天零点开始备份。同时将备份文件复制到远程Window机器上。备份策略为保留冗余数2。ORA-00020登陆EBS出现ORA-00020错误，查看联机文档得知是PROCESSES参数设置太低。切到后台用sqlplus‘/assysdba’也登陆不了，提示ORA-01012:Notloggedin。解决：查看进程信息：ps–ef|greporaSID，发现大量的服务进程。用KILL-9杀掉部分进程，结果更惨。用sqlplus‘/assysdba’登陆，一直处于等待状态。重启机器，然后启动Oracle实例，查看PROCESS参数。SQLshowparameterprocessesProcesses为200，参数值已经够大了。ORA-09817无法登陆EBS，切到EBS数据库，登陆不了数据库。[oratest@node1~]$sqlplus'/assysdba'SQL*Plus:Release10.2.0.1.0-Productionon星期五7月914:15:062010Copyright(c)1982,2005,Oracle.Allrightsreserved.ERROR:ORA-09817:Writetoauditfilefailed.LinuxEr