2020年Oracle数据库维护手册

(Oracle管S)Oracle数据库维护手册20XX年XX月去年的企业咨询顾问经验」经讨实战验证可以客地执行的卓避管理75案，直得您下载拥有!数据库维护手册目录1前言42ORACLE维护方法42.1数据库启动42.2数据库停止52.3监听器的启停52.4用户管理62.5数据库参数文件62.6数据库概况的查询72.7常用对象的创建和使用82.8失效数据库对象的检测和编译92.9数据备份的技巧102.10数据库命中率的监测112.11最消耗资源进程的检测112.12锁的监测及处理122.13SQL语句执行技巧132.14表空间的管理142.15数据文件I/O的统计优化152.16错误号的跟踪出理153日常问题处理流程163.1ORACLE数据库日常检查163.2会话连接日志的清理163.3数据库hang住时的停止和方法163.4扩表空间方法173.5死锁会话的杀死173.6未决两阶段事务的回滚184相关通用工具介绍184.1EM工具181前言本文主要是简单介绍和总结日常Oracle数据在运行维护方面的一些方法和技巧。通过该手册以便于相关技术人员的日常运维工作。2ORACLEDB维护方法2.1数据库启动1、以oracle用户用户登录，sqlplus‘/assysdba’sqlplus>startup2、以oracle用户或ora816用户登录sqlplus‘/assysdba’sqlplus>startupnomuntsqlplus>alterdatabasemount；sqlplus>alterdatabaseopen；3、第一种启动方式是最常用的手工启动方式，第二种启动方式是数据库有故障时常用的逐步启动方式，可以观察故障点。因为startupnomount语句只读取spfile（serverparameterfile数据库参数文件），启动instance，启动SGA和后台进程；alterdatabasemount语句打开控制文件，确认数据文件和联机日志文件的位置，但此时不对数据文件和日志文件进行校验检查；alterdatabaseopen打开包括Redolog文件在内的所有数据库文件，这种方式下可访问数据库中的数据。Startup完成功能是上述三条语句的之和。2.2数据库停止1、sqlplus‘/assysdba’sqlplus>shutdownnormal正常方式关闭数据库。2、shutdownimmediate立即方式关闭数据库，在SQLPLUS中执行shutdownimmediate，数据库并不立即关闭，而是在所有事务执行完毕并提交工作后才关闭，所以可能会等待，因此在关闭数据前要停掉连接到数据库的所有应用程序。建议日常维护工作中的关闭数据库，采用此方式。3、shutdownabort直接关闭数据库，正在访问数据库的会话会被突然终止。如果数据库中有大量操作正在执行，这时执行shutdownabort后，因日志回滚、前滚(RollBack/RollForward)，下次重新启动数据库需要教长时间。当用shutdownimmediate不能关闭数据库时，shutdownabort可以立即完成数据库的关闭操作。2.3监听器的启停1、监听器的启动，以oracle用户用户登录lsnrctlstart[$ORACLE_SID]2、监听器的停止，以oracle用户用户登录lsnrctlstop[$ORACLE_SID]3、监听器状态的查看，以oracle用户用户登录lsnrctlstatus[$ORACLE_SID]4、检测服务名是否有效，在操作系统下运行tnspingSERVICE_NAMENUMBERSSERVICE_NAME为你建立的服务名，NUMBERS为你要试PING数据库服务的次数2.4用户管理1、用SQL语句查看系统中已有用户情况select*fromdba_users;2、增加新的用户，并授予连接和资源权限，只有授予连接权限才可登录，授予资源权限才可建表和修改createuserUSERNAMEprofiledefaultidentifiedbyPASSWORDdefaulttablespaceDEFALUT_TABLESPACE_NAMEtemporarytablespaceTEMP_TABLESPACE_NAME;grantconnecttoUSERNAME;grantresourcetoUSERNAME;3、修改用户口令alteruserUSERNAMEidentifiedbyNEWPASSWORD;4、删除用户dropuserUSERNAME;5、限制某个已有用户会话的连接数（通过创建profile的方式）createprofilePROFILENAMElimitsessions_per_userCONNECT_NUMBERS;alteruserUSERNAMEprofilePROFILENAME;2.5数据库参数文件尸哪萍)4弃KFMSORACLE—HOMEAIb^spfioINSTANCE—NAME.Ora^^H+ffl2，啸罔哪萍sKfffa-iersysiemseipARAMETERINAMEHVA-UESCOPeHboiMsysienvspfi-e)3，Jl/bIMffls哪萍>db—bock—Qze萍甫府沽汁三、sswiffl、陛wanT瞟啸区。Fdblbocklbuffers萍甫醐前滞)-&冈汁、」/甘兵园^dblbocklbuffes3^|站、短冈盈汁盈®。pshare—POOLSize®^醐厕滞)-&冈奇szfflNB滞)-&冈3汁、=、H+fflfflHH4»^君苛assq一1、滞醐卸带厕阀。短冈成盈汁盈®、醐厕滞)-&冈君Hr®可醐前滞)-&冈苫萍甫出»滞)-&冈3汁、」二玛SGA)—露T磷a测落普ffiA4»s5。次。ysorilarealsize©T^ffifflHH*薄15布昏吕令汁、」/、w/甘wwBfsa^四1MN画。E，process、s^s$.*沛ffl、I53。。徉。F，db」o—s_aves1T。(wFr哪萍W9i甘哪咪、ffllog)尸淅rt}画sasselecttablespace_name,stautsfromdba_tablespaces;2、数据文件的情况SELECTFILE_NAME,FILE_ID,TABLESPACE_NAMEFROMDBA_DATA_FILES;3、段的情况SELECTSEGMENT_NAME,,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUSFROMDBA_ROLLBACK_SEGS;4、表、存储过程的拥有者名字和创建日期selectowner,object_name,object_type,createdfromall_objectswhereobject_type=’TABLE’OROBJECT_TYPE=’PROCEDURE'5、其他常用的视图和表v$lock锁的情况v$session会话的情况v$process进程的情况v$parameter数据库参数v$rollname回滚段的情况v$sqlarea共享池中的SQL语句v$waitstat等待的会话dba_jobs数据里定义的作业dba_jobs_running数据库里正在运行的作业dba_views数据库中所有视图的文本dba_indexs数据库中所有索引的描述dba_free_space数据库中所有表空间自由分区dba_db_links数据库中所有数据库链接2.7常用对象的创建和使用1、表的创建(在表空间zy上创建表dhhm为例)createtabledhhm(hmvarchar2(11),yhmcvarchar2(60))tablespacezy;2、索弓I的创建(将DHHM表上hm字段建立索引hmsy存储在表空间zy上为例)createindexhmsyondhhm(hm)tablespacezy;3、已有存储过程、函数、包源代码的查找selectname,textfromuser_sourcewherename=NAME;4、数据库链接的创建和使用createpublicdatabaselinkDBLINKNAME.worldconnecttoUSERNAMEidentdiedbyPASWORDusingSERVICENAME;select*fromTABLE_NAME@DBLINKNAME;5、同义词的创建和使用似dyh用户可以查询并修改yyxt用户的dhhm表为例)cratesynonyndyh.dhhmforyyxt.dhhm;grantselectonyyxt.dhhmtodyh;grantupdateonyyxt.dhhmtodyh;2.8失效数据库对象的检测和编译1、失效存储过程的查找和编译select'alterprocedure'||object_name||'compile;'fromuser_objectswherestatus='INVALID'andobject_type='PROCEDURE’然后复制粘贴并执行第一条语句的输出结果2、失效的包的查找和编译select'alterpackage'||object_name||'compile;'fromuser_objectswherestatus='INVALID'andobject_type='PACKAGE,然后复制粘贴并执行第一条语句的输出结果2.9数据备份的技巧一、EXP/IMP1、利用管道将数据库备份压缩到磁带中去,以备份dhhm,yhzl表为例(磁带机名IBM为/dev/rmt0，HP为/dev/rtm/m0)/usr/sbin/mknodpipe_ofp/usr/sbin/mknodpipe_ifpddof=/dev/rmt0if=pipe_ifbs=1024&compress<pipe_of>pipe_if&expUSERNAME/PASSWORDtables=(dhhm,yhzl)file=pipe_ofrmpipe_ofrmpipe_if2、利用管道将备份磁带解压后倒入到数据库中去/usr/sbin/mknodpipe_ofp/usr/sbin/mknodpipe_ifpddif=/dev/rmt0of=pipe_ofbs=1024&uncompress<pipe_of>pipe_if&impUSERNAME/PASSWORDtables=(dhhm,yhzl)file=pipe_ifrmpipe_ofrmpipe_if3、数据库备份的分割,解决有时文件系统限制文件大小不能超过2G的问题(此处只分割为三个文件，当然可以根据实际情况继续增加)expUSERNAME/PASSWORDfile=(FILE1.dmp,FILE2.dmp,FILE3.dmp)log=dump.logfilesize=1gtables=(dhhm,yhzl)1、为了提高备份速度，可将DIRECT参数设为(但当该参数设置为Y时，备份时不做一些有效性检查，应慎用)，并将buffer开大，如expUSERNAME/PASSWORDdirect=ybuffer=8192000tables=(dhhm,yhzl)file=1.dmp2、只备份建表和建索引语句expUSERNAME/PASSWORDrows=ntables=(dhhm,yhzl)file=1.dmp5、有时为了提高倒入速度，应该将数据和索引的倒入倒出分开，而不是一起进行二、RMAN备份恢复1、非归档模式下数据库必须在mount状态下进行备份。$rmantarget/进入rman全库备份RMAN>shutdownimmediate；关闭数据库RMAN>startupmount；RMAN>back叩databaseformat‘/路径/DAT_%d_%T_%s,;RMAN>alterdatabaseopen;恢复数据库为OPEN状态RMAN>listbackupsetofdatabase;备份记录RMAN>listbackup;恢复RMAN>shutdownimmediate；RMAN>startupnomount；RMAN>restorecontrolfilefrom‘控制文件备份’;RMAN>alterdatabasemount;RMAN>restoredatabase;RMAN>recoverdatabase;RMAN>alterdatabaseopenresetlogs;打开数据库并重做日志2、归档模式切换归档模式sql>altersystemsetlog_archive_dest_1='location=归档路径'scope=both;首先需要指定归档路径SQL>startupmount;SQL>alterdatabasearchivelog;非归档模式noarchivelogSQL>alterdatabaseopen;SQL>altersystemswitchlogfile;/*切换日志*/RMAN>back叩databaseformat'/数据备份路径份路径/full_%d_%T_%s'plusarchivelogformat'/日志/arch_%d_%T_%s'deleteallinput;2.10数据库命中率的监测1、监控SGA中数据高速缓冲区的命中率selecta.value+b.value"logical_reads”,c.value"phys_reads”,(100*((a.value+b.value)-c.value)/(a.value+b.value))"SGADATABUFFERHITRATIO"fromv$sysstata,v$sysstatb,v$sysstatcwherea.statistic#=38andb.statistic#=39andc.statistic#=40;2、监控SGA中字典缓冲区的命中率select100*(sum(pins-reloads))/sum(pins)"DICTIONARYBUFFERHITRATIO"fromv$librarycache;3、监控SGA中共享缓存区的命中率，select100*((sum(pins-reloads))/sum(pins))"SQLSHAREAREAHITRATIO"fromv$librarycache;4、整个SGA使用率的检查selectname,sgasize/1024/1024"totalsgaM",bytes/1024/1024"freesgaM”,round(bytes/sgasize*100,2)"freeratio%”from(selectsum(bytes)sgasizefromsys.v_$sgastat)s,sys.v_$='freememory';2.11最消耗资源进程的检测1、查看某个会话正在执行的SQL语句及该会话是哪台台机器发出selectb.username,mand,b.termainal,b.osuserb.machinea.sql_textfromv$sqltexta,v$sessionbwherea.address=b.sql_addressandb.sid=SESSION_ID2、SQLPLUS下找出最消耗资源的10个进程的SQL指令集!ps-eaf|grepora|grep-v\/sh|grep-vora_|awk'{print$4,$1,$2}'|sort-r|head-10（HP平台）!ps-eaf-opcpu,pid,user,tty,comm|grepora|grep-v\/sh|grep-vora_|sort-r|head-10（IBM平台）promptEnterTheUNIXPID:份路径acceptPIDSELECTa.username,a.terminal,gram,b.sql_textFROMv$sessiona,v$sqlareab,v$processcWHERE（c.spid='&PID'ORcess='&PID'）ANDa.paddr=c.addrANDa.sql_address=b.address;3、利用数据自带统计工具检测其性能和瓶颈A、启动统计程序：svrmgrl>svrmgrl>@$ORACLE_HOME/admin/rdbms/utlbstat.sqlB、停止统计程序svrmgrl>svrmgrl>@$ORACLE_HOME/admin/rdbms/utlestat.sqlC、一般在系统忙时开始统计程序，半小时后停止统计程序，不能执行过长，否则会影响系统性能；停止统计程序后，会在当前目录产生名为report.txt的文本文件，应对其做分析。2.12锁的监测及处理1、锁的监测及锁住哪些表(注意绝大部分锁都不是死锁)selectid1,count(*)fromv$lockgroupbyid2havingcount(*)>1;selectobject_name,object_typefromdba_objectswhereobject_id=id1;这里v$lock中的id1和all_objects中的object_id是相对应的。2、查看哪些会话被锁并杀死会话selectid1,count(*)fromv$lockgroupbyid2havingcount(*)>1;selectsid,serial#fromv$sessionwhereid1=被锁的表ID；altersystemkillsession'sid,serial#';(注意sid,serial#都为数字)3、死数据库僵死进程(假设此进程的数据库会话号为X)selectspidfromv$processa,v$sessionbwherea.addr=b.paddrandb.sid=x;找出此数据库会话的操作系统进程spid,然后在操作系统提示符号下杀死此进程killspid2.13SQL语句执行技巧1、打开和关闭显示SQL语句的执行方案(如使用哪个索引等)setautotraceon;setautotroaceoff;2、打开和关闭显示SQL语句的执行时间sesmeon;sesmeoff;sesmingon;sesmingoff;3，浏迎Mffl11303=3^宙丑union囱甘union®奋宜ffl*薄*蕊浒嘟洒苗灿、3unga=4,sfflSQL™宙丑PSQLssffl、iMS5，wi西宜漏^宙丑-ike、％s5.ng、、w^脸淅mm、3-ike、s5.ng%、匡宙丑6,-、滞醐wi西厕网a-iez.ndexINDEXINAMErebu=d;6，iSQL甘查)seiserverouionspoo-s.-txl:SQLM酒spoooff。尸w邮渊4淅rt}画>{-、」夺口宙丑«se-ecar.arb-espacelnampsumpbyiesMloz^loz^ioar-sizaM)--、round(10000*(sum(a.byies)、1024、1024—sum(b.byies)、1024、1024))、1000=usedsize(M)Lround(10000*(sum(a.byies)、1024、1024—sum(b.byies)、1024、1024)、(sum(a.bytes)/1024/1024))/100"percent%”fromdba_data_filesa,(selecttablespace_name,file_id,sum(bytes)bytesfromdba_free_spacegroupbytablespace_name,file_id)bwherea.tablespace_name=b.tablespace_nameanda.file_id=b.file_idgroupbya.tablespace_name;2、使用文件系统的表空间扩展alterdatabaseTABLESPACE_NAMEadddatafileFILE_NAMEsizeSIZE;3、用裸设备表空间的扩展alterdatabaseTABLESPACE_NAMEadddatafileRAW_DEVICE_NAMEsizeSIZE;这里需要注意的是裸设备要提前建立后，且这里使用的是裸设备的字符文件(类型为C),SIZE值要比其实际大小要稍微小一些。4、集群中，在某一台主机上共享卷组上增加文件系统和裸设备后，一定要同步到集群中另外的主机，否则当数据库在另外的主机中启动时，会找不着相应的设备号，导致数据库不能够被打开。5、查找表空间的剩余空间块数以及最大空闲块的大小，如果碎片过多或最大空闲块过小，需要手工合并表空间碎片selecttablespace_name,count(*)"freeblocknumber",trunc(sum(bytes)/1024/1024)totalfreesize(M)",trunc(max(bytes)/1024/1024)"maxblock(M)"fromdba_free_spacegroupbytablespace_name;6、手工合并某表空间碎片altertablespaceTABLESPACE_NAMEcoalesce;2.15数据文件I/O的统计优化col文件名formata35；文件名,fs.phyrds读次数,fs.phywrts写次数，(fs.readtim/decode(fs.phyrds,0,-1,fs.phyrds))读时间，(fs.writetim/decode(fs.phywrts,0,-1,fs.phywrts))写时间fromv$datafiledf,v$filestatfswheredf.file#=fs.file#；2.16错误号的跟踪出理1、在操作系统级显示其错误详细信息和常用解决办法以报ora_4031错误为例)$>oerrora40312、有时为了进一步跟踪其错误，让其产生更详细的告警日志在用户报警文件里，需要在数据库的初始化文件里加下面一句配置，并重新启动数据库event="4031tracenameerrorstacklevel3”这里注意的是，跟踪完毕后，应将此句话屏蔽掉，以免影响数据库性能。3日常问题处理流程3.1ORACLE数据库日常检查1、登录到主机上利用sqlplus检查是否可用；2、数据库告警日志的检查E_NAME.log此文件中记载数据库的启动、停止、系统级修改、告警信息、日志切换，是日常维护中的重要依据。3.2会话连接日志的清理会话连接日志$ORACLE_HOME/network/log/listener.log,此文件中记载着每个连接登录时的IP地址和时间等情况，可作为监测的依据，因为此文件不断增大，需要定期清空，清空方式为在$ORACLE_HOME/network/log目录下运行操作系统命令>listener.log。3.3数据库hang住