Oracle数据库日常管理方案精样本

Oracle数据库寻常管理方案Oracle数据库运维解决，更重要是做好数据库管理工作，做好数据库检查与备份工作，然后需要熟悉有关逻辑构造故障解决办法、物理构造故障解决办法，应对数据库损坏状况，在数据库发生损坏而无法修复时候，能运用数据库管理工作中所做数据备份及时恢复系统、数据，恢复正常生产工作。数据库管理Oracle数据库管理重要是要做好寻常检查管理工作，并检查好数据库备份状况，在发生紧急状况时能及时不但切换到备用系统上进行工作，并要能在数据库数据浮现异常时候，能及时恢复数据。因而，做好寻常检查与管理工作特别重要，重要工作涉及：数据库性能监控检查数据库状态巡检检查数据库备份状况与可恢复性检查数据库性能监控管理通过对数据库性能监控管理工作，可以有效防范风险事件发生，并能从长时间运营数据中，总结出数据库性能状态，为系统性能优化、隐患事件排查提供更好根据。查找前十条性能差sqlSELECT*FROM(selectPARSING_USER_ID,EXECUTIONS,SORTS,COMMAND_TYPE，DISK_READS,sql_textFROMv$sqlareaorderBYdisk_readsDESC)whereROWNUM<10;查询有enqueue等待事件SELECTb.SID，b.serial#，b.username，machine，event，wait_time,CHR(BITAND(p1，-16777216)/16777215)||CHR(BITAND(p1，16711680)/65535)"EnqueueType"FROMv$session_waita，v$sessionbWHEREa.eventNOTLIKE'SQL*N%'ANDa.eventNOTLIKE'rdbms%'ANDa.SID=b.SIDANDb.SID>8ANDa.event='enqueue'ORDERBYusername;如何拟定哪个表空间读写频繁selectname,phyrds,phywrts,readtim,writetimfromv$filestata,v$dbfilebwherea.file#=b.file#orderbyreadtimdesc;SELECTs.sid，kglpnmod“Mode”，kglpnreq“Req”，SPID“OSProcess”FROMv$session_waitw，x$kglpnp，v$sessions,v$processoWHEREp.kglpnuse=s.saddrANDkglpnhdl=w.p1rawandw.eventlike‘%librarycachepin%’ands.paddr=o.addr;查询全表扫描表SQL>colnamefora30SQL>selectname,valuefromv$sysstatwherenamein('tablescans(shorttables)','tablescans(longtables)');NAMEVALUEtablescans(shorttables)18602tablescans(longtables)111SQL>selectcount(target),targetfromv$session_longopswhereopname='TableScan'groupbytarget;COUNT(TARGET) TARGET84 ECM_APPL.RPTLOG159 ECM_DCTM_OTHR.DMR_CONTENT_S9 ECM_DCTM_OTHR.DM_SYSOBJECT_R2 ECM_DCTM_OTHR.DM_SYSOBJECT_S查出全表扫描表Selectsql_textfromv$sqltextt，v$sql_planpWheret.hash_value=p.hash_valueAndp.operation=’TABLEACCESS’Andp.option=’FULL’Orderbyp.hash-value，t.piece;查找FastFullindex扫描Sql语句可以这样：Selectsql_textfromv$sqltextt，v$sql_planpWheret.hash_value=p.hash_valueAndp.operation=’INDEX’Andp.option=’FULLSCAN’Orderbyp.hash-value，t.piece;查询硬语法分析次数SQL>selectname,valuefromv$sysstatwherenamelike'parsecount%';NAMEVALUEparsecount(total)16103parsecount(hard)343parsecount(failures)5该项显示buffercache大小与否适当。公式：1-((physicalreads-physicalreadsdirect-physicalreadsdirect(lob))/sessionlogicalreads)执行：select1-((a.value-b.value-c.value)/d.value)fromv$sysstata,v$sysstatb,v$sysstatc,v$sysstatdwhere=’physicalreads’and=’physicalreadsdirect’and=’physicalreadsdirect(lob)’and=’sessionlogicalreads’;显示buffer命中率公式：1-(physicalreads/(dbblockgets+consistentgets))执行：select1-(sum(decode(name,'physicalreads',value,0)))/(sum(decode(name,'dbblockgets',value,0))+sum(decode(name,'consistentgets',value,0)))"bufferhitratio"fromv$sysstat;Softparseratio：这项将显示系统与否有太多硬解析。该值将会与原始记录数据对比以保证精准。例如，软解析率仅为0.2则表达硬解析率太高。但是，如果总解析量(parsecounttotal)偏低，这项值可以被忽视。公式：1-(parsecount(hard)/parsecount(total))执行：select1-(a.value/b.value)fromv$sysstata,v$sysstatbWhere='parsecount(hard)'and='parsecount(total)';In-memorysortratio：该项显示内存中完毕排序所占比例。最抱负状态下，在OLTP系统中，大某些排序不但小并且可以完全在内存里完毕排序。公式：sorts(memory)/(sorts(memory)+sorts(disk))执行：selecta.value/(b.value+c.value)fromv$sysstata,v$sysstatb,v$sysstatcwhere=’sorts(memory)’and=’sorts(memory)’and=’sorts(disk)’;Parsetoexecuteratio：在生产环境，最抱负状态是一条sql语句一次解析多数运营。公式：1-(parsecount/executecount)执行：select1-(a.value/b.value)fromv$sysstata,v$sysstatbwhere=’parsecount(total)’and=’executecount’;ParseCPUtototalCPUratio：该项显示总CPU耗费在执行及解析上比率。如果这项比率较低，阐明系统执行了太多解析。公式：1-(parsetimecpu/CPUusedbythissession)执行：select1-(a.value/b.value)fromv$sysstata,v$sysstatbwhere=’parsetimecpu’and=’CPUusedbythissession’;ParsetimeCPUtoparsetimeelapsed：普通，该项显示锁竞争比率。这项比率计算与否时间耗费在解析分派给CPU进行周期运算(即生产工作)。解析时间耗费不在CPU周期运算普通表达由于锁竞争导致了时间耗费。公式：parsetimecpu/parsetimeelapsed执行：selecta.value/b.valuefromv$sysstata,v$sysstatbwhere=’parsetimecpu’and=’parsetimeelapsed’;从V$SYSSTAT获取负载间档(LoadProfile)数据负载间档是监控系统吞吐量和负载变化重要某些，该某些提供如下每秒和每个事务记录信息：logonscumulative，parsecount(total)，parsecount(hard)，executes，physicalreads，physicalwrites，blockchanges，andredosize.被格式化数据可检查’rates’与否过高，或用于对比其他基线数据设立为辨认systemprofile在期间如何变化。例如，计算每个事务中blockchanges可用如下公式：dbblockchanges/(usercommits+userrollbacks)执行：selecta.value/(b.value+c.value)fromv$sysstata,v$sysstatb,v$sysstatcwhere=’dbblockchanges’and=’usercommits’and=’userrollbacks’;其他计算记录以衡量负载方式，如下：Blockschangedforeachread：这项显示出blockchanges在blockreads中比例。它将指出与否系统重要用于只读访问或是重要进行诸多数据操作(如：inserts/updates/deletes)公式：dbblockchanges/sessionlogicalreads执行：selecta.value/b.valuefromv$sysstata,v$sysstatbwhere=’dbblockchanges’and=’sessionlogicalreads’;Rowsforeachsort：数据排序状况公式：sorts(rows)/(sorts(memory)+sorts(disk))执行：selecta.value/(b.value+c.value)fromv$sysstata,v$sysstatb,v$sysstatcwhere=’sorts(rows)’and=’sorts(memory)’and=’sorts(disk)’;查看某表约束条件selectconstraint_name，constraint_type,search_condition，r_constraint_namefromuser_constraintswheretable_name=upper('&table_name');selectc.constraint_name,c.constraint_type,cc.column_namefromuser_constraintsc,user_cons_columnsccwherec.owner=upper('&table_owner')andc.table_name=upper('&table_name')andc.owner=cc.ownerandc.constraint_name=cc.constraint_nameorderbycc.position;查看表信息Select*fromuser_tables;查看表空间名称及大小selectt.tablespace_name，round(sum(bytes/(1024*1024)),0)ts_sizefromdba_tablespacest，dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_name;selectt.tablespace_name，round(sum(bytes/(1024*1024)),0)ts_sizefromdba_tablespacest，dba_data_filesdwheret.tablespace_name=d.tablespace_namegroupbyt.tablespace_name;TABLESPACE_NAMETS_SIZECWMLITE20DRSYS20ODM20PERFSTAT99SYSTEM400查看回滚段名称及大小selectsegment_name，tablespace_name，r.status，(initial_extent/1024)InitialExtent，(next_extent/1024)NextExtent，max_extents，v.curextCurExtentFromdba_rollback_segsr，v$rollstatvWherer.segment_id=v.usn(+)orderbysegment_name;移动一种表各种分区BEGINFORxIN(SELECTpartition_nameFROMuser_tab_partitionsWHEREtable_name=‘BIG_TABLE2′)LOOPEXECUTEIMMEDIATE‘altertablebig_table2movepartition‘||x.partition_name;ENDLOOP;END;/查看LOCKSELECT/*+ORDEREDUSE_HASH(H,R)*/H.SIDHOLD_SID,R.SIDWAIT_SID,decode(H.type,”MR”，”MediaRecovery”,”RT”，”RedoThread”,”UN”，”UserName”,”TX”，”Transaction”,”TM”，”DML”,”UL”，”PL/SQLUserLock”,”DX”，”DistributedXaction”,”CF”，”ControlFile”,”IS”，”Instance”FS”，”FileSet”,”IR”，”InstanceRecovery”,”ST”，”DiskSpaceTransaction”,”TS”，”TempSegment”,”IV”，”LibraryCacheInvalidation”,”LS”，”LogStartorSwitch”,”RW”，”RowWait”,”SQ”，”SequenceNumber”,”TE”，”ExtendTable”,”TT”，”TempTable”,H.type)type,decode(H.lmode,0，”None”，1，”Null”,2，”Row-S(SS)”，3，”Row-X(SX)”,4，”Share”，5，”S/Row-X(SSX)”,6，”Exclusive”，to_char(H.lmode))hold,decode(r.request，0，”None”,1，”Null”，2，”Row-S(SS)”,3，”Row-X(SX)”，4，”Share”,5，”S/Row-X(SSX)”,6，”Exclusive”,to_char(R.request))request,R.ID1,R.ID2,R.CTIMEFROMVLOCKH,VLOCKRWHEREH.BLOCK=1ANDR.BLOCK=0andH.TYPE<>”MR”ANDR.TYPE<>”MR”ANDH.ID1=R.ID1ANDH.ID2=R.ID2找ORACLE字符集select*fromps$wherename='NLS_CHARACTERSET'；查看ORACLE运营OS平台SQL>runbegindbms_output.put_line(dbms_utility.port_string);end;/查看空间详细使用状况CREATEORREPLACEPROCEDUREshow_space(p_segnameINVARCHAR2,p_ownerINVARCHAR2DEFAULTUSER,p_typeINVARCHAR2DEFAULT'TABLE',p_partitionINVARCHAR2DEFAULTNULL)ASl_total_blocksNUMBER;l_total_bytesNUMBER;l_unused_blocksNUMBER;l_unused_bytesNUMBER;l_lastusedextfileidNUMBER;l_lastusedextblockidNUMBER;l_last_used_blockNUMBER;PROCEDUREp(p_labelINVARCHAR2，p_numINNUMBER)ISBEGINDBMS_OUTPUT.put_line(RPAD(p_label，40，'.')||p_num);END;BEGINDBMS_SPACE.unused_space(segment_owner=>p_owner,segment_name=>p_segname,segment_type=>p_type,partition_name=>p_partition,total_blocks=>l_total_blocks,total_bytes=>l_total_bytes,unused_blocks=>l_unused_blocks,unused_bytes=>l_unused_bytes,last_used_extent_file_id =>l_lastusedextfileid,last_used_extent_block_id =>l_lastusedextblocki last_used_block=>l_last_used_block);p('TotalBlocks'，l_total_blocks);p('TotalBytes'，l_total_bytes);p('UnusedBlocks'，l_unused_blocks);p('UnusedBytes'，l_unused_bytes);p('LastUsedExtFileId'，l_lastusedextfileid);p('LastUsedExtBlockId'，l_lastusedextblockid);p('LastUsedBlock'，l_last_used_block);END;/显示缓冲区有关SQLSELECTtch，file#，dbablk,CASEWHENobj=THEN'rbs/compatsegment'ELSE(SELECTMAX('('||object_type||')'||owner||'.'||object_name)||DECODE(COUNT(*)，1，''，'maybe!')FROMdba_objectsWHEREdata_object_id=x.obj)ENDwhatFROM(SELECTtch,file#,dbablk,objFROMx$bhWHEREstate<>0ORDERBYtchDESC)xWHEREROWNUM<=5;获取生成依照文献名selectc.value||'/'||d.instance_name||'_ora_'||a.spid||'.trc'tracefromv$processa,v$sessionb,v$parameterc,v$instancedwherea.addr=b.paddrandb.audsid=userenv('sessionid')and='user_dump_dest';在v$session_longops视图中，sofar字段表达已经扫描块数，totalwork表达总得需要扫描块数，因此咱们即可以对正在运营长查询进行监控，例如在索引创立时，查看索引创立进度，也可以查看系统中以往长查询。colopnameformata32coltarget_descformata32colperworkformata12setlines131selectsid,OPNAME,TARGET_DESC,sofar,TOTALWORK,trunc(sofar/totalwork*100,2)||'%'asperworkfromv$session_longopswheresofar!=totalwork;setlines121setpages999colopnameformata29coltargetformata29coltarget_descformata12colperworkformata12colremainformat99colstart_timeformata21colsofarformat99999999coltotalworkformat99999999colsql_textformata101colbufgetsformat99999999selectopname,target,to_char(start_time,'yy-mm-dd:hh24:mi:ss')start_time,elapsed_secondselapsed,executionsexecs,buffer_gets/decode(executions,0,1,executions)bufgets,module,sql_textfromv$session_longopssl,v$sqlareasawheresl.sql_hash_value=sa.hash_valueandupper(substr(module,1,4))<>'RMAN'andsubstr(opname,1,4)<>'RMAN'andmodule<>'SQL*Plus'andsl.start_time>trunc(sysdate)orderbystart_time;IO事件监控1.监控事例等待selectevent,sum(decode(wait_Time,0,0,1))"Prev"，sum(decode(wait_Time,0,1,0))"Curr",count(*)"Tot"fromv$session_Waitgroupbyeventorderby4;2.回滚段争用状况selectname，waits，gets，waits/gets"Ratio"fromv$rollstata，v$rollnamebwherea.usn=b.usn;3.监控表空间I/O比例selectdf.tablespace_namename,df.file_name"file",f.phyrdspyr，f.phyblkrdpbr,f.phywrtspyw，f.phyblkwrtpbwfromv$filestatf，dba_data_filesdfwheref.file#=df.file_idorderbydf.tablespace_name;4.监控文献系统I/O比例selectsubstr(a.file#,1,2)"#"，substr(,1,30)"Name"，a.status，a.bytes，b.phyrds，b.phywrtsfromv$datafilea，v$filestatbwherea.file#=b.file#;在某个顾客下找所有索引selectuser_indexes.table_name，user_indexes.index_name,uniqueness，column_namefromuser_ind_columns，user_indexeswhereuser_ind_columns.index_name=user_indexes.index_nameanduser_ind_columns.table_name=user_indexes.table_nameorderbyuser_indexes.table_type，user_indexes.table_name，user_indexes.index_name，column_position;监控SGA命中率selecta.value+b.value"logical_reads"，c.value"phys_reads"，round(100*((a.value+b.value)-c.value)/(a.value+b.value))"BUFFERHITRATIO"fromv$sysstata，v$sysstatb，v$sysstatcwherea.statistic#=38andb.statistic#=39andc.statistic#=40;监控SGA中字典缓冲区命中率selectparameter，gets,Getmisses，getmisses/(gets+getmisses)*100"missratio"，(1-(sum(getmisses)/(sum(gets)+sum(getmisses))))*100"Hitratio"fromv$rowcachewheregets+getmisses<>0groupbyparameter，gets，getmisses；监控SGA中共享缓存区命中率，应当不大于1%selectsum(pins)"TotalPins"，sum(reloads)"TotalReloads"，sum(reloads)/sum(pins)*100libcachefromv$librarycache；selectsum(pinhits-reloads)/sum(pins)"hitradio",sum(reloads)/sum(pins)"reloadpercent"fromv$librarycache;显示所有数据库对象类别和大小 selectcount(name)num_instances,type,sum(source_size)source_size，sum(parsed_size)parsed_size,sum(code_size)code_size,sum(error_size)error_size，sum(source_size)+sum(parsed_size)+sum(code_size)+sum(error_size)size_requiredfromdba_object_sizegroupbytypeorderby2;监控SGA中重做日记缓存区命中率，应当不大于1%SELECTname,gets,misses,immediate_gets，immediate_misses,Decode(gets,0,0,misses/gets*100)ratio1，Decode(immediate_gets+immediate_misses,0,0，immediate_misses/(immediate_gets+immediate_misses)*100)ratio2FROMv$latchWHEREnameIN('redoallocation'，'redocopy');监控内存和硬盘排序比率，最佳使它不大于.10，增长sort_area_size:SELECTname，valueFROMv$sysstatWHEREnameIN('sorts(memory)'，'sorts(disk)');监控当前数据库谁在运营什么SQL语句SELECTosuser，username，sql_textfromv$sessiona，v$sqltextbwherea.sql_address=b.addressorderbyaddress，piece;监控字典缓冲区select(sum(pins-reloads))/sum(pins)"libcache"fromv$librarycache；select(sum(gets-getmisses-usage-fixed))/sum(gets)"rowcache"fromv$rowcache；selectsum(pins)"executions"，sum(reloads)"cachemisseswhileexecuting"fromv$librarycache;后者除此前者,此比率不大于1%,接近0%为好。selectsum(gets)"dictionarygets",sum(getmisses)"dictionarycachegetmisses"fromv$rowcache;监控MTSselectbusy/(busy+idle)"sharedserversbusy"fromv$dispatcher；此值不不大于0.5时，参数需加大。selectsum(wait)/sum(totalq)"dispatcherwaits"fromv$queuewheretype='dispatcher'；selectcount(*)fromv$dispatcher；selectservers_highwaterfromv$mts；servers_highwater接近mts_max_servers时，参数需加大碎片限度selecttablespace_name,count(tablespace_name)fromdba_free_spacegroupbytablespace_namehavingcount(tablespace_name)>10;altertablespacenamecoalesce；altertablenamedeallocateunused;createorreplaceviewts_blocks_vasselecttablespace_name,block_id,bytes,blocks,'freespace'segment_namefromdba_free_spaceunionallselecttablespace_name,block_id,bytes,blocks,segment_namefromdba_extents;select*fromts_blocks_v;selecttablespace_name,sum(bytes),max(bytes),count(block_id)fromdba_free_spacegroupbytablespace_name;查看碎片限度高表SELECTsegment_nametable_name，COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS'，'SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBYsegment_name);表、索引存储状况检查selectsegment_name,sum(bytes),count(*)ext_quanfromdba_extentswheretablespace_name='&tablespace_name'andsegment_type='TABLE'groupbytablespace_name，segment_name;selectsegment_name,count(*)fromdba_extentswheresegment_type='INDEX'andowner='&owner'groupbysegment_name;找使用CPU多顾客sessioncpuusedbythissession：查找各回话连接CPU使用率selecta.sid,spid,status,substr(gram,1,40)prog,a.terminal,osuser,value/60/100valuefromv$sessiona,v$processb,v$sesstatcwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvaluedesc;寻找CPU使用过量sessionSELECTsql_textFROMv$sqltextaWHERE(a.hash_value，a.address)IN(SELECTdecode(sql_hash_value，0，prev_hash_value，sql_hash_value)，decode(sql_hash_value，0，prev_sql_addr，sql_address)FROMv$sessionbWHEREb.paddr=(SELECTaddrFROMv$processcWHEREc.spid='&pid'))ORDERBYpieceASC;数据库巡检管理寻常检测检测系统卷状态df-k可以看到系统各个卷使用状况；如果文献系统剩余空间不大于20%，需删除不用文献以释放空间。或找系统管理员调节空间大小。检查表空间使用状况SELECTtablespace_name，max_m，count_blocksfree_blk_cnt，sum_free_m，to_char(100*sum_free_m/sum_m，'99.99')||'%'ASpct_freeFROM(SELECTtablespace_name，sum(bytes)/1024/1024ASsum_mFROMdba_data_filesGROUPBYtablespace_name)，(SELECTtablespace_nameASfs_ts_name，max(bytes)/1024/1024ASmax_m，count(blocks)AScount_blocks，sum(bytes/1024/1024)ASsum_free_mFROMdba_free_spaceGROUPBYtablespace_name)WHEREtablespace_name=fs_ts_name:selectsum(bytes)/(1024*1024)asfree_space,tablespace_namefromdba_free_spacegroupbytablespace_name;检测表空间状态DBA_FREE_SPACE是数据库一种视图,可以看到数据库空间使用信息。SELECTtablespace_name,sum(bytes)/1024/1024"MBFree"FROMdba_free_spaceWHEREtablespace_name<>'TEMP'GROUPBYtablespace_name;检测Oracle运营状态ORACLE状态可以通确认所有INSTANCE状态正常登陆到所有数据库或例程，检测ORACLE后台进程：$ps–ef|grepora来查看;检查数据库版本信息sqlplus“/assysdba”select*fromv$version;检查日记文献和trace文献记录alert和trace文献中错误连接到每个需管理系统，使用'telnet'登陆，对每个数据库,cd到bdump目录，普通是$ORACLE_BASE/<SID>/bdump，使用Unix‘tail'命令来查看alert_<SID>.log文献，如果发现任何新ORA-错误，记录并解决。查看会话挂起状况关闭不必要会话命令如下：selectsid,serial#,username,program,machine,statusfromv$session;手工断开某个会话，则执行：altersystemkillsession'SID,SERIAL#';检查数据库当天备份有效性对RMAN备份方式：检查第三方备份工具备份日记以拟定备份与否成功对EXPORT备份方式：检查exp日记文献以拟定备份与否成功。对其她备份方式：检查相应日记文献。检查数据文献状态记录状态不是“online”数据文献需做恢复。Selectfile_namefromdba_data_fileswherestatus='OFFLINE';定期检测系统健康检查检查如下内容：init<sid>.oracontrolfileredologfilearchivingsortareasizetablespace(system,temporary,tablespacefragment)datafiles(autoextend,location)object(numberofextent,nextextent,index)rollbacksegmentlogging&tracing(alert.log,max_dump_file_size,sqlnet)检查无效数据库对象SELECTowner，object_name，object_typeFROMdba_objectsWHEREstatus='INVALID';检查不起作用约束SELECTowner，constraint_name，table_name，constraint_type，statusFROMdba_constraintsWHEREstatus='DISABLED'ANDconstraint_type='P';检查无效triggerselectowner,trigger_name,table_name,statusfromdba_triggerswherestatus='DISABLED';Oracle数据库巡检样本1.ORACLE数据库常规检查1．ORACLE常规检查DatabaseOverviewDBNameORCLGlobalNameORCL.REGRESS.RDBMS.DEV.US.ORACLE.COMHostNameLocalhostInstanceNameOrclInstanceStartTime18-Nov-14:14:01RestrictedModeNOArchiveLogModeARCHIVELOGV$VersionBannerOracleDatabase10gEnterpriseEditionRelease.0-64biPL/SQLRelease.0–ProductionCORE.0ProductionTNSforLinux：Version.0–ProductionNLSRTLVersion.0–Production2．基本参数配备ParameterNameValueModifiedaudit_file_dest/oracle/admin/orcl/adumpFALSEbackground_dump_dest/oracle/admin/orcl/bdumpFALSECompatible.0FALSEcontrol_files/oracle/oradata/orcl/control01.ctl，/oracle/oradata/orcl/control02.ctl，/oracle/oradata/orcl/control03.ctlFALSEcore_dump_dest/oracle/admin/orcl/cdumpFALSEdb_block_size8192FALSEdb_domainNullvalueFALSEdb_file_multiblock_read_count16FALSEdb_nameOrclFALSEdb_recovery_file_dest/oracle/flash_recovery_areaFALSEdb_recovery_file_dest_sizeFALSEDispatchers(PROTOCOL=TCP)(SERVICE=orclXDB)FALSEjob_queue_processes6FALSElog_archive_dest_1location=/oracle/backupArchivelogFALSElog_archive_dest_10location=use_db_recovery_file_destFALSElog_archive_min_succeed_dest2FALSEopen_cursors300FALSEpga_aggregate_targetFALSEProcesses150FALSEremote_login_passwordfileEXCLUSIVEFALSEsga_targetFALSEstatistics_levelTYPICALFALSEtimed_statisticsTRUEFALSEundo_managementAUTOFALSEundo_tablespaceUNDOTBS1FALSEuser_dump_dest/oracle/admin/orcl/udump3．ORACLE顾客配备文献#vi.profile#Getthealiasesandfunctionsif[-f~/.bashrc]；then.~/.bashrcfi#UserspecificenvironmentandstartupprogramsPATH=$PATH:$HOME/binexportPATHunsetUSERNAME#fororacleexportORACLE_SID=orclexportORACLE_BASE=/oracleexportORACLE_HOME=/oracle/10gexportPATH=$ORACLE_HOME/bin:$PATHexportNLS_LANG=AMERICAN_AMERICA.ZHS16GBK2.数据库核心性能检查及分析1数据库性能检查DBBlockBufferHitRatio99.99％OLTP环境下，命中率应当高于90%，否则需要增长DB_BLOCK_BUFFERS以提高性能LibraryCacheHitRatio99.56％该命中率用于提供SharedSQLBuffers性能。应当高于95%SoftParse98.55％软解析率应当高于95％In-memorySort100％此为排序操作，如果指标值过低阐明过多排序操作影响了性能，建议进行SQL优化ExecutetoParse73.10％执行分析比率，不应当低于0否则sharedpoll也许有问题ParseCPUtoParseElapsd94.20％这2项值代表理解析所用cpu时间，ParseCPUtoParseElapsd不到100％阐明有有其她问题影响了，解析时间并不是所有都消耗在cpu上Non-ParseCPU95.34％2数据库响应时间分析响应时间是非常重要数据库性能指标，Top5TimedEventsEventWaitsTime(s)AvgWait(ms)%TotalCallTimeWaitClassCPUtime

1,454

92.0

RMANbackup&recoveryI/O25,0064611829.1SystemI/Oosthreadstartup8,6724595329.0Concurrencylogfileparallelwrite148,211245215.5SystemI/Ocontrolfileparallelwrite192,426223114.1SystemI/O从以上信息可以看出，RMAN备份占用大量时间3.备份恢复方略检查和维护1．RMAN配备状况RMANconfigurationparametersare:CONFIGURERETENTIONPOLICYTORECOVERYWINDOWOF7DAYS;CONFIGUREBACKUPOPTIMIZATIONON;CONFIGUREDEFAULTDEVICETYPETODISK;CONFIGURECONTROLFILEAUTOBACKUPON;CONFIGURECONTROLFILEAUTOBACKUPFORMATFORDEVICETYPEDISKTO'orclcongrol_%F';CONFIGUREDEVICETYPEDISKPARALLELISM2BACKUPTYPETOCOMPRESSEDBACKUPSET;CONFIGUREDATAFILEBACKUPCOPIESFORDEVICETYPEDISKTO1；#defaultCONFIGUREARCHIVELOGBACKUPCOPIESFORDEVICETYPEDISKTO1；#defaultCONFIGURECHANNEL1DEVICETYPEDISKMAXPIECESIZE100MMAXOPENFILES8RATE40M;CONFIGURECHANNEL2DEVICETYPEDISKMAXPIECESIZE100MMAXOPENFILES8RATE40M;CONFIGUREMAXSETSIZETOUNLIMITED；#defaultCONFIGUREENCRYPTIONFORDATABASEOFF；#defaultCONFIGUREENCRYPTIONALGORITHM'AES128'；#defaultCONFIGUREARCHIVELOGDELETIONPOLICYTONONE；#defaultCONFIGURESNAPSHOTCONTROLFILENAMETO'/oracle/10g/dbs/snapcf_orcl.f'；#default2．备份方略描述选用RMAN多级备份方略,以每个星期作为一种周期星期1数据库全备0级星期2增量备份1级星期3增量备份1级星期4累积增量备份1c级星期5数据库全备0级星期6增量备份1级星期天增量备份1级基于以上方略,任何时间点数据恢复只需要做最多1次0级恢复和2次1级恢复.加上当天归档日记可以实现迅速完全恢复3．备份方略实行Cat/etc/crontabSHELL=/bin/bashPATH=/sbin:/bin:/usr/sbin:/usr/binMAILTO=rootHOME=/#run-parts01****rootrun-parts/etc/cron.hourly024***rootrun-parts/etc/cron.daily224**0rootrun-parts/etc/cron.weekly4241**rootrun-parts/etc/cron.monthly004**1oracle/oracle/scripts/rman/backupweek1.cmd&004**2oracle/oracle/scripts/rman/backupweek2.cmd&004**3oracle/oracle/scripts/rman/backupweek3.cmd&004**4oracle/oracle/scripts/rman/backupweek4.cmd&004**5oracle/oracle/scripts/rman/backupweek5.cmd&004**6oracle/oracle/scripts/rman/backupweek6.cmd&004**7oracle/oracle/scripts/rman/backupweek7.cmd&4．备份检查和每日备份脚本日记检查使用crosscheckbackupset检查。所有备份集备份片都为AVAILABLE每日脚本日记输出正常，使用reportobsolete检查并无过期备份信息。5．对当前方略总结当前脚本能正常准时完毕每日制定备份方略，总体运营状况良好。备份集信息完整，并且包括了归档日记，逻辑检查完好，可以正常执行恢复。4.核心性SQL语句定位及分析SQL>selectrownum,d.*from(selecthash_value,disk_reads,executions,buffer_gets,ROWS_PROCESSEDfromv$sqlareaborderbyb.disk_readsdesc)dwhererownum<11;ROWNUMHASH_VALUEDISK_READSEXECUTIONSBUFFER_GETSROWS_PROCESSED14140112108721189232716113530636921227543227645051682405361232774891423619980387642924673718811901280121190816880385864921285913419393554681609010133232792372504SQL>selectsql_textfromv$sqltextwherehash_value=&1orderbypiece;5.近期警告日记及有关分析1．orcl_ora_8727.trc-rw-r1oracledba944Dec620:00Dec620:00orcl_ora_8727.trc/oracle/admin/orcl/udump/orcl_ora_8727.trcOracleDatabase10gEnterpriseEditionRelease.0-64bitProductionWiththePartitioning，OracleLabelSecurity，OLAPandDataMiningScoringEngineoptionsORACLE_HOME=/oracle/10gSystemname: LinuxNodename: localhostRelease: 2.6.9-34.ELsmpVersion: #1SMPFriFeb2416:56:28ESTMachine: x86_64Instancename：orclRedothreadmountedbythisinstance：1Oracleprocessnumber：21Unixprocesspid：8727，image：oracle@localhost(TNSV1-V3)***-12-0704:00:28.002***ACTIONNAME:(0000221STARTED111)-12-0704:00:28.001***MODULENAME:(backuparchivelog)-12-0704:00:28.001***SERVICENAME:(SYS$USERS)-12-0704:00:28.001***SESSIONID:(157.7513)-12-0704:00:28.001***ACTIONNAME:(0000244STARTED16)-12-0704:00:32.421***MODULENAME:(backupfulldatafile)-12-0704:00:32.421***ACTIONNAME:(0000248STARTED111)-12-0704:00:33.7032orcl_ora_8727.trc-rw-r1oracledba944Dec620:00Dec620:00orcl_ora_8727.trc/oracle/admin/orcl/udump/orcl_ora_8727.trcOracleDatabase10gEnterpriseEditionRelease.0-64bitProductionWiththePartitioning，OracleLabelSecurity，OLAPandDataMiningScoringEngineoptionsORACLE_HOME=/oracle/10gSystemname: LinuxNodename: localhostRelease: 2.6.9-34.ELsmpVersion: #1SMPFriFeb2416:56:28ESTMachine: x86_64Instancename：orclRedothreadmountedbythisinstance：1Oracleprocessnumber：21Unixprocesspid：8727，image：oracle@localhost(TNSV1-V3)***-12-0704:00:28.002***ACTIONNAME:(0000221STARTED111)-12-0704:00:28.001***MODULENAME:(backuparchivelog)-12-0704:00:28.001***SERVICENAME:(SYS$USERS)-12-0704:00:28.001***SESSIONID:(157.7513)-12-0704:00:28.001***ACTIONNAME:(0000244STARTED16)-12-0704:00:32.421***MODULENAME:(backupfulldatafile)-12-0704:00:32.421***ACTIONNAME:(0000248STARTED111)-12-0704:00:33.703总结，可以发现，2个警告日记都是对数据库进行全备份产生，并无其她警告日记。6.小结整个数据库运营状况良好，响应时间较为稳定，备份方略能较好执行并且运营正常，各备份集信息完整。整个数据库得到有效保护。数据库备份与恢复数据库备份恢复管理原则：经常同步恢复目录确认删除不需要归档日记备份方略简朴原则Oracle备份与恢复有三种原则模式，大体分为两大类:备份恢复(物理上)以及导入导出(逻辑上)，而备份恢复又可以依照数据库工作模式分为非归档模式(Nonarchivelog-style)和归档模式(Archivelog-style),普通，咱们把非归档模式称为冷备份，而相应把归档模式称为热备份，她们关系如下所示：三种方式各有长处，如下表：比较逻辑备份物理备份导入和导出冷备份热备份长处可以针对对象进行备份，可以跨平台进行数据备份工作，可以不需要关闭数据库。备份和恢复速度快，执行效率高，具备高安全、低维护长处。可以依照日记恢复到上一秒操作，不需要关闭数据库。缺陷导出方式并不能防止介质上失效，它只是一种逻辑上备份。单独使用时，只能恢复某一时间点数据，不能按照表和按顾客来恢复。并且必要关闭数据库。过程教其她办法复杂，需要不小空间存储归档文献，操作不容许失误，否则恢复不能进行。使用时机普通用于有规律寻常备份。数据库可以暂时关闭或者需要配合热备份使用。数据访问量小或需要更精准备份以及需要备份表空间和库文献逻辑备份恢复方式运用Export可将数据从数据库中提取出来，运用Import则可将提取出来数据送回到Oracle数据库中去。理论基本：Oracle提供Export和Import具备三种不同操作方式(就是备份数据输出(入)类型)：表方式(T)可以将指定表导出备份；全库方式(Full)将数据库中所有对象导出；顾客方式(U)可以将指定顾客相应所有数据对象导出；在导入导出备份方式中，提供了很强大一种办法，就是增量导出/导入，但是它必要作为System来完毕增量导入导出，并且只能是对整个数据库进行实行。增量导出又可以分为三种类别：完全增量导出(CompleteExport):这种方式将把整个数据库文献导出备份；expsystem/managerinctype=completefile=1125.dmp（为了以便检索和事后查询，普通咱们将备份文献以日期或者其她有明确含义字符命名）增量型增量导出(IncrementalExport):这种方式将只会备份上一次备份后变化成果。expsystem/managerinctype=incrementa