Oracle数据库常见问题处理

一、解决Oracle9・2・0・6版本数据库由于ORA-07445宕机问题故障现象：XX网数据库宕机，查看日志发现以下内容：WedJun820:24:172005Errorsinfile/u02/app/oracle/admin/unicom/udump/unicom_ora_661.trc:ORA-07445:\263\366\317\326\322\354\263\243:\272\313\320\304\327\252\264\242[0000000101C3089C][SIGSEGV][Addressnotmappedtoobject][0x000000000][][]WedJun820:24:222005Errorsinfile/u02/app/oracle/admin/unicom/bdump/unicom_pmon_11598.trc:TOC\o"1-5"\h\zORA-07445:exceptionencountered:coredump[0000000101C399A0][SIGSEGV][Addressnotmappedtoobject][0x000000000] [] []WedJun820:24:232005Errorsinfile/u02/app/oracle/admin/unicom/bdump/unicom_pmon_11598.trc:ORA-07445:exceptionencountered:coredump[0000000101C399A0][SIGSEGV][Addressnotmappedtoobject][0x000000000] [] []ORA-00602:internalprogrammingexceptionORA-07445:exceptionencountered:coredump[0000000101C399A0][SIGSEGV][Addressnotmappedtoobject][0x000000000] [] []WedJun820:24:332005CKPT:terminatinginstanceduetoerror472InstanceterminatedbyCKPT,pid=11604

WedJun821:04:472005StartingORACLEinstance(normal)解决办法：Oracle工程师建议安装Oracle补丁p3949307_9206_SOLARIS64,经过测试，安装步骤如下(注意，首先shutdown数据库)：解压补丁文件：unzipp3949307_9206_SOLARIS64.zip解开后的目录是：4060756。修改oraclehomeproperties.xml文件，该文件在$ORACLE_HOME/inventory/ContentsXML目录下：cporaclehomeproperties.xmloraclehomeproperties.xmlb.bakvioraclehomeproperties.xml更改数字453->23,存盘退出。修改PATH路径为：PATH=$ORACLE_HOME/bin:/usr/ccs/bin:${PATH}执行opatchapply命令：cd4060756$ORACLE_HOME/OPatch/opatchapply安装成功后会出现如下结果：Updatinginventory.../oracle92/app/oracle/product/9・2.0.1/OPatch/opatch.plversion:.51Copyright(c)2001-2004OracleCorporation.AllRightsReserved.OPatchsucceeded・

二、査看Oracle表空间大小及利用率的SQL语句一非常实用的语句setlinesize300col表空间名fora30SELECTUPPER(F.TABLESPACE_NAME)"表空间名"，D.TOT_GROOTTE_MB"表空间大小(M)",D.TOT_GROOTTE_MB-F.TOTAL_BYTES"已使用空间(M)",TO_CHAR(ROUND((D.TOT_GROOTTE_MB-F.TOTAL_BYTES)/D.TOT_GROOTTE_MB*100,2),'990.99')"使用比",F.TOTAL_BYTES"空闲空间(M)",F.MAX_BYTES"最大块(M)"FROM(SELECTTABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)TOTAL_BYTES,ROUND(MAX(BYTES)/(1024*1024),2)MAX_BYTESFROMSYS.DBA_FREE_SPACEGROUPBYTABLESPACE_NAME)F,(SELECTDD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)DWHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY4DESC;三、XX网Oracle数据库SYSTEM表空间文件坏块的解决办法故障现象：NetBackup备份出错，日志如下：RMAN-00571:RMAN-00569:===============ERRORMESSAGESTACKFOLLOWSRMAN-00571:RMAN-03009:failureofbackupcommandonch01channelat06/15/200502:28:44ORA-19566:超出损坏块限制0(文件/u01/app/oradata/unicom/system01.dbf)故障分析：由于事前发生过别的表空间(cookdbs：/uOl/app/oradata/unicom/cokdbs.dbf)也有坏块的情况,根据李智他们的建议，使用迁移数据后删除表空间的办法解决(仅适用于普通表空间)。这是查询该表空间上的表和索引对象的一个脚本：GXdb%morequery.sqlconn/assysdbacolownerfora25colsegment_namefora40spooltable.logselectdistinctOWNER,SEGMENT_NAMEfromdba_extentswhereTABLESPACE_NAME='COOKDB'andSEGMENT_TYPE='TABLE';spooloffspoolindex.logselectdistinctOWNER,SEGMENT_NAMEfromdba_extentswhereTABLESPACE_NAME='COOKDB'andSEGMENT_TYPE='INDEX';spooloffexit查询完毕后，使用一个迁移的脚本迁移到新的表空间：GXdb%moremove.sh#!/bin/kshsqlplus/NEWTBS="cookdbs1"echo"conn/assysdba">move.sqlforTNAMEin'cattable.log|awk'/"COOKDB/{print$2}'、doecho"altertablecookdb.$TNAMEmovetablespace$NEWTBS;"〉〉move.sqldoneforINAMEin'catindex.log|awk'/ACOOKDB/{print$2}'、doecho"ALTERINDEXcookdb.$INAMEREBUILDTABLESPACE$NEWTBS;"〉〉move.sqldoneecho"exit"〉〉move.sqlsqlplus/本次经过检查，发现包括有System在内的四个表空间的四个数据文件有坏块。为了能使用NetBackup备份，对NetBackup脚本做以下修改（对相应的数据文件设置maxcorrupt）:run{allocatechannelch00type'sbt_tape';setmaxcorruptfordatafile1,27,28,44to10;backup。。。。。。releasechannelchOO;}如果有归档日志没有丢失，都备份过，可以使用rman的blockrecover来修复文件的坏块，例如：run{allocatechannelch00type'sbt_tape';blockrecoverdatafile27block302089,302090,332823,332824,332825,332826;blockrecoverdatafile28block340846,340847,340848,340849;blockrecoverdatafile44block380381,380382,380383,380384,380405,380406,380407,380408;blockrecoverdatafile1block1703064,1703065,1703066,1703067,1703088,1703089,1703090,1703091;releasechannelch00;}

但是中间由于备份失败，本地维护删除过归档日志，此方法不适用。本次由于有SYSTEM表空间，不同于普通用户表空间，它有自己的特殊性。所以有些问题的解决方法也不尽相同。经多方探讨协商(OracleIndiaengineer、项目经理、本地维护、开发部门和系统工程部经理)，总结了以下处理办法：解决SYSTEM表空间文件坏块的方法是导出全库数据，删除数据库，重建数据库，导入全库数据。具体的操纵步骤，经过讨论，见如下：主要是考虑到优化可以在线进行，也需要在线进行，还考虑到优化的万一出错性，既便出错至少还可以恢复到原始状态，如果优化没有成功，再做导出导入也不能恢复到初始状态。所以就先做导出导入工作再做优化处理。步骤是：导出整个数据库(数据库在线)，参考命令：expsystem/managerfile=/oraclebak/unicomdbfull.dmpfull=Ylog=/oraclebak/unicomfull.log备份配置文件cp$ORACLE_HOME/dbs/*/Oraclebak，记录数据库名及实例名，参考字典视图V$DATABASE,V$INSTANCE；删除数据库DBCA(/u02/app/oracle/product//bin/dbca)图形化配置工具。注意：建议不要删除数据文件、重做日志文件和控制文件以及归档日志文件，处理方法是：就数据文件、重做日志文件和控制文件分别改名为xxx.bak.xxx文件，例如mvdatafile001.dbfdatafile001.bak.dbf；就归档日志文件，只改名归档日志文件所在的目录名即可，例如mvarchivedirarchivedirbak。要参考的数据字典视图有V$DATAFILE、V$LOGFILE、V$CONTROLFILE和archiveloglist参数。重建数据库DBCA(/u02/app/oracle/product//bin/dbca)图形化配置工具；导入整个数据库，impsystem/unicom@unicomfull=yignore=yfile=xxx.dmplog=yyy.log；启动数据库，严格测试dbvfile=/u02/app/oracle/oradata/unicom/system01.dbfblocksize=8192检查还有没有坏块；7.使用3.2SQL脚本检查表空间情况。

四、一个使用Oracle归档日志恢复数据库的案例案例环境：XX网Oracle816数据库需求描述：备份主机（装有oracle）上备份了7月13日的数据，但是主数据库服务器当前日期是7月15日。为了把备份主机上的库跟主数据库服务器同步，使用归档日志把7月13日的库前滚到7月15日。处理步骤：1•备份备份主机上的控制文件，然后把主用数据库的最新控制文件备份并拷贝到备份主机相应目录；备份控制文件的方法：sqlplus/nologVVEOFconnectinternalalterdatabasebackupcontrolfileto'${BACKUP_DIR}/controlfile.bkp';alterdatabasebackupcontrolfiletotrace;EOF2•拷贝主库的归档日志到备份数据库的归档日志目录（这个前滚操作需要从13号到15号的归档日志完整，若备份数据库的归档日志目录不知道，可以从参数文件中看；3.启动数据库到mount状态，使用recoverdatabase前滚数据库；SQL〉startuprestrictmountpfile="${ORACLE_BASE}/admin/sdh21/pfile/init${service_names}.ora"SQL>recoverdatabaseusingbackupcontrolfile;选择AUTO模式4•如果到了最后一个日志它还需要前滚，此时可以在主库上把当前redolog切换，然后把产生的归档继续拷贝到备份主机，继续前滚；5.前滚完成之后，它可能还继续要求下一个归档日志，此时可以CANCEL；6.使用reselogs选项打开数据库。SQL〉alterdatabaseopenresetlogs;（注意：如果打开时报错可能是还需要把日志继续前滚，可重复使用recoverdatabase（注意：如果打开时报错可能是还需要把日志继续前滚，可重复使用recoverdatabase五、Oracle五、Oracle补丁p3948480_9206_SOLARIS64.zip的安装步骤升级说明：是从Oracle920.1.0升级到920.6.0,解决一系列bug问题。安装步骤：解压文件包%unzipp3948480_9206_SOLARIS64.zip%cdDisk12•停掉数据库sql>shutdownimmediate3•启动安装程序%./runInstaller4•执行catpatch.sql脚本%sqlplus/nologSQL>conn/assysdbaSQL>SPOOLpatch.logSQL>startupmigrate;SQL〉@?/rdbms/admin/catpatch.sql//执行catpatch.sql脚本SQL>SPOOLOFF5•执行utlrp.sql脚本SQL〉shutdownSQL〉startupSQL〉@?/rdbms/admin/utlrp.sql//执行utlrp.sql脚本6.验证selectcomp_name,versionfromdba_registry;注意事项：若不是在本地安装，指定X应用程序的所在主机。♦Bourne,Bash,orKornshell:$DISPLAY=local_host:0.0;exportDISPLAY♦Cshell:%setenvDISPLAYlocal_host:0.0六、在VM维护的裸设备上添加表空间的过程创建裸设备，大小是2G#/usr/sbin/vxassist-gdatadgmakevol1832G修改指定裸设备的权限#vxedit-gdatadgsetuser=oraclegroup=oinstallmode=600vol1823•创建连接#ln-s/dev/vx/rdsk/datadg/vol182pre_ln2_182.dbf4.添加到指定的表空间，例如UNDOTBS1SQL〉altertablespaceUNDOTBS12adddatafile'/usr5/linkdata/pre/pre_ln2_182・dbf'size2000M;

七、在SUNDiskSuit维护的裸设备上添加表空间的过程创建裸设备metainit-scqcdgd6049-p/dev/did/rdsk/d4s02g创建连接文件ln-s/dev/md/cqcdg/rdsk/d6049pre_ln2_63・dbf3•修改权限chownoracle:dbapre_ln2_63.dbf添加表空间altertablespacePERFDBSadddatafile'/usr5/linkdata/pre/pre_ln2_63.dbf'SIZE2000M;八、在Oracle数据库中从文件系统迁移到裸设备的处理过程故障现象：C网数据库/文件系统空间使用率突然达到99%，检查发现原来是因为本地维护给数据库添加了数据文件，但是该数据文件添加到文件系统上导致根文件系统使用率突然增大。将文件系统上的数据文件迁移到裸设备，裸设备在文件系统上只存在一个链接，几乎不占空间。处理步骤：1•检查发现C网已没有已建好的可用裸设备，有一个建好的30G的裸设备没有使用，首先需要添加适合适用的裸设备。2.删除这个没有使用的30G的softpartitionmetaclear-scqcdg-rd6042在删除的空间上建立一个2G的裸设备metainit-scqcdgd6044-p/dev/did/rdsk/d4s02g：

4•在文件系统上建立到裸设备的链接ln-s/dev/md/cqcdg/rdsk/d6044pre_ln2_57.dbf5•更改链接文件的所有者chownoracle:dbapre_ln2_57.dbf6•以sysdba身份登录并关闭数据库sqlplus“/assysdba”SQL>shutdownimmediate7•将文件系统文件上的数据导到裸设备文件上ddif=/usr5/linkdata/sys/pre_ln2_61.dbfof=/usr5/linkdata/pre/pre_ln2_57.dbfbs=134217728if文件系统文件名of新建裸设备链接名bsdd时的blocksize，单位为byte，如果不设此项，dd的速度非常慢8.动数据库到mount状态SQL〉startupmount9•改数据文件名SQL>ALTERDATABASERENAMEFILE'/usr5/linkdata/sys/pre_ln2_61・dbf'TO'/usr5/linkdata/pre/pre_ln2_57.dbf';开数据库SQL>alterdatabaseopen;证更改

九、解决在OracleOpen状态下shutdownimmediate不能关闭数据库问题故障现象：XX网数据库使用shutdownimmediate不能关闭，日志中不断输出以下内容:Shuttingdowninstance:furtherlogonsdisabledShuttingdowninstance(immediate)Licensehighwatermark=215AlldispatchersandsharedserversshutdownFriAug1221:52:342005ALTERDATABASECLOSENORMALFriAug1221:52:382005Thread1advancedtologsequence69953Currentlog#2seq#69953mem#0:/ora_sys/cqgdb/redo02.logThread1advancedtologsequence69954Currentlog#4seq#69954mem#0:/ora_sys/cqgdb/redo04.logCurrentlog#3seq#69966mem#0:/ora_sys/cqgdb/redo03.logThread1advancedtologsequence69967Currentlog#1seq#69967mem#0:/ora_sys/cqgdb/redo01.log解决办法：Oracle工程师首先怀疑是临时表空间空间不足导致,经检查临时表空间没有空间不足的情况，仔细观察日志发现重做日志文件不断切换，分析应该是有较多的事务没有完成提交或者有较多没有提交的事务完成回滚。现在面临的问题是我们没有很多时间去等待所有的事务去完成回滚或提交。解决问题的思路就是如何尽快结束这些事务的回滚或提交。

1.查看spfile文件中是否有fast_start_parallel_rollback参数的设置，检查结果G网数据库没有设置该参数。如果没有显式设置，则该参数的默认值为low。修改该参数值为false；将数据库启动到nomount状态：startupnomount；修改改参数值：altersystemsetfast_start_parallel_rollback=FALSEscope=spfile；shutdownimmediate关闭数据库；startup启动；查看该参数是否生效：showparameterfast_start_parallel_rollback；7•等待一段时间；8.shutdownimmediate数据库可以关闭。分析：FAST_START_PARALLEL_ROLLBACK是用来控制事务并行回滚最大进程数的参数。该参数有三个可设值，low,high，false。当设置为false时并行回滚被禁止，由于禁止了并行回滚，在数据库关闭时，需要回滚的事务将被取消。十、数据库服务器字符集更改步骤问题描述：在客户端插入字符“咪咪”，从数据库中查询显示时出现乱码。处理步骤：1.据库做全库导出，备份全库数据，以防故障发生首先设定客户端的字符集，必须以ZHS16GBK的字符集导出，然后才能在更改失败后顺利倒入新建的库。#setenvNLS_LANG"SIMPLIFIEDCHINESE_CHINA.ZHS16GBK";#stty-istrip-paritycs8;#setenvLANGzh拟在/sybdata（磁盘阵列）下建立一个目录orabak，用于存放dmp文件。#mkdir/sybdata/orabak#chownoracle:oinstall/sybdata/orabak#su—oracle#cd/sybdata/orabak%expsystem/manager@hnsdhfile=hnsdh_2005-8-17log=hnsdh_exp_2005-8-17full=y（此处命名为示例，以实施当日日期为准）察看日志结尾，以判定导出是否成功。#cathnsdh2005-8-17.dmpod-xhead看第二和第三个字节组成的十六进制数是多少可判断导出文件的字符集。示例如下：#catexample.dmp|od-x|head00000000303544558504f52543a5630392e3032

0000220646d7000000000000000000000000000卜六进制的0354化为十进制为852，参造下表：NLS_CHARSET_IDNLS_CHARSET_NAMEHEX_IDUS7ASCII1WE8DEC2WE8HP3US8PC4374WE8EBCDIC375WE8EBCDIC5006WE8EBCDIC11407WE8EBCDIC2858850ZHS16CGB231280352851ZHS16MACCGB231280353852ZHS16GBK354853ZHS16DBCS355860ZHT32EUC35c861ZHT32SOPS35d862ZHT16DBT35e863ZHT32TRIS35f864ZHT16DBCS360865ZHT16BIG5361866ZHT16CCDC362

867ZHT16MSWIN950363868ZHT16HKSCS364870AL24UTFFSS366871UTF8367872UTFE368即可得出这个dmp文件的字符集为ZHS16GBK。2•据库中直接更改字符集参数操作步骤如下：SQL〉shutdownimmediateSQL〉startupmountSQL>ALTERSYSTEMENABLERESTRICTEDSESSION;SQL>ALTERSYSTEMSETJOB_QUEUE_PROCESSES=0;SQL>ALTERSYSTEMSETAQ_TM_PROCESSES=0;SQL>ALTERDATABASEOPEN;SQL>altersessionsetevents'10046tracenamecontextforever,level12';SQL>alterdatabasecharactersetINTERNAL_USEZHS16GBK;SQL>shutdownimmediateSQL>startup察看系统字符集SQL>SELECT*FROMNLS_DATABASE_PARAMETERS;看NLS_CHARACTERSET的值为多少，如果为ZHS16GBK则说明改动成功。如果执行正常，则按照下一节进行测试操作。成功后的测试测试1,在数据库服务器端下测试

%setenvNLS_LANG"SIMPLIFIEDCHINESE_CHINA.ZHS16GBK";%stty-istrip-paritycs8;%setenvLANGzh%sqlplus/nologSQL〉conn/assysdbaSQL〉createtabletest_tq(achar(20));SQL〉insertintotest_tq1〉(a)2〉values('洣洣');SQL〉select*fromtest_tq;如显示为A洣洣则成功。测试2,Windows客户端环境下测试运行REGEDIT，第一步选HKEY_LOCAL_MACHINE第二步选择SOFTWARE,第三步选择ORACLE,第四步选择NLS_LANG,键入与服务器端相同的字符集（本例为：AMERICAN_AMERICAN.US7ASCII）。右击我的电脑，然后点击属性，“高级”页面下，点击“环境变量”，在系统变量中添加：变量名：NLS_LANG变量值:SIMPLIFIEDCHINESE_CHINA.ZHS16GBK运行cmd，输入echo%NLS_LANG%，查看系统变量设置时否成功然后运行:$sqlplussystem/manager@hnsdh

SQL〉createSQL〉SQL〉createtabletest_tq(achar(20));SQL〉insertintotest_tq1>(a)2〉values('洣洣');SQL〉select*fromtest_tq;如显示为A洣洣则成功。4•不成功时的措施新建数据库，设定字符集为ZHS16GBK，其他参数先照搬原来的，并倒入数据。建库时所需的具体参数在重建之前要搜集。注意在配置控制文件时设定最大数据文件数。建好数据库以后，执行以下命令即可恢复数据库：%cd/sybdata/orabak%impsystem/manager@hnsdhfull=yignore=yfile=hnsdh_2005-8-17log=hnsdh_imp_2005-8-17十^一、Oracle数据库归档目录usr5满的解决办法故障现象：C网数据库的逻辑日志增长很快，有时候每分钟就产生150M的日志文件，导致归档目录不到一天的时间就满了。我们的备份策略是每天的晚上0点执行，也就是说还没来得及备份归档目录就满了。导致的结果：数据库挂起不能工作问题分析及解决办法：解决办法有3种：1•增加归档目录的空间2•增加备份频度3•删除归档日志文件每一种办法都会存在一些问题或产生一些负面影响：1•增加归档目录的空间，这个已经不可行，因为已经没有可用空间2•增加备份频度，会影响部分系统性能，后来观察影响不大，远远排在了oracle进程后面。3•删除归档日志文件，这只是权宜之计，会带来控制文件和日志文件的不同步从而影响下一次的数据库备份失败，以及万一数据文件损坏从而因影响恢复的问题。经过分析和权衡,初步采用了每天办法2次的办法。除了原来夜里零点备份的1次之外，又安排在白天2点备份一次，至于为什么定到2点，主要是想均衡一下业务量，考虑到凌晨业务量较小可能产生较少的日志（相对白天而言）。结果很见效。后来讨论，又发现了一下新的问题。问题是：万一备份失败或者在12小时之内usr5空间满怎么办？于是又添加了一个执行脚本fs_monitor.sh，每小时执行一次，若发现usr5空间达到80%就自动删除归档日志文件然后自动数据库同步。以下是自动自动清楚自动同步的脚本（由左亮撰写）：#!/bin/sh#PleasechangetheARCHIVE_FStoyouractualfilesystemthatyourarchivelogstoraged

ARCHIVE_FS=/usr5#PleasechangetheARCHIVE_DIRtoyouractualdirectorythatyourarchivelogstoragedARCHIVE_DIR=/usr5/oracle/bjdb/arch_2#DefinedthelocationoflogfileLOG=/usr5/oracle/bjdb/${0}・'date+%m%d'・log#ObtaintheusageoffilesystematthattimeDFK=、df—klgrep$ARCHIVE_FS|awk'{USAGE=substr($5,1,length($5)—1)printUSAGE}''START_RMAN="setenvORACLE_SIDrmansqlplus/nologVVEOFconnect/assysdbastartupexitEOF"STOP_RMAN="setenvORACLE_SIDrmansqlplus/nologVVEOFconnect/assysdbashutdownimmediateexitEOF#DefinedthecommandofarchivelogcrosscheckCMD_STR="setenvORACLE_SIDbjdbrmantargetsys/syscatalogrman/rman@rmanVVEOFcrosscheckarchivelogall;exitEOF#ChecktheusageofARCHIVE_FSif[$DFK-gt80]thenTIME='date'echo"Atthetime:"$TIME",Usageof"$ARCHIVE_FS"filesystemisbeyond80%Theusedrateis:"$DFK"%now">>$LOGcd$ARCHIVE_DIR#ObtainfilelistthatneedtoberemoveFILE_LIST='ls-ltltail-600|awk'{print$9}'、forFILEin$FILE_LISTdorm$FILE2>>$LOGdone#Startrmandatabasesu-oracle-c"$START_RMAN">>$LO