1.Oracle數(shù)據(jù)庫平常管理方案

Oracle數(shù)據(jù)庫的運維解決，更重要的是做好數(shù)據(jù)庫的管理工作，做好數(shù)據(jù)庫的檢者與備

份工作，然后需要熟悉相關(guān)的邏輯結(jié)構(gòu)故障解決方法、物理結(jié)構(gòu)故障解決方法，應對數(shù)據(jù)庫

損壞的情況，在數(shù)據(jù)庫發(fā)生損壞而無法修復的時候，能運用數(shù)據(jù)庫管理工作中所做的數(shù)據(jù)備

份及時恢復系統(tǒng)、數(shù)據(jù)，恢復正常生產(chǎn)工作。

.數(shù)據(jù)庫管理

Oracle數(shù)據(jù)庫的管理重要是要做好平常的檢查管理工作，并檢查好數(shù)據(jù)庫的備份情況,

在發(fā)生緊急情況時能及時不僅切換到備用系統(tǒng)上進行工作，并要能在數(shù)據(jù)庫數(shù)據(jù)出現(xiàn)異常的

時候，能及時恢任數(shù)據(jù)。因此，做好平常的檢查與管理工作特別重要，重要工作涉及：

?數(shù)據(jù)庫的性能監(jiān)控檢查

?數(shù)據(jù)庫狀態(tài)巡檢檢查

?數(shù)據(jù)庫備份情況與可恢復性檢查

1.LL數(shù)據(jù)庫性能監(jiān)控管理

通過對數(shù)據(jù)庫性能監(jiān)控的管理工作，可以有效的防范風險事件的發(fā)生，并能從長時間的

運營數(shù)據(jù)中，總結(jié)出數(shù)據(jù)庫性能的狀態(tài)，為系統(tǒng)性能優(yōu)化、隱患事件排查提供更好的依據(jù)。

查找前十條性能差的sql

SELECT*FROM(selectPARS1NG_USER」D.EXECUTIONS,SORTS,COMMAND_TYPE,

DISK_READS.sql_textFROMv$sqlareaorderBYdisk_readsDESC)whereROWNUM<10;

1.1.1.2.查詢有enqueue等待的事件

SELECTb.SID,b.serial#,b.username,machine,event,wait_time,CHR(BITAND(pl,

-16777216)/16777215)11CHR(BITAND(pl,16711680)/65535)''EnqueueType"FROM

v$session_waita,v$sessionbWHEREa.eventNOTLIKE'SQL*N%'ANDa.eventNOT

LIKE'rdbms%'ANDa.SID=b.SIDANDb.SID>8ANDa.event='enqueue'ORDERBY

username;

1.1.L3.如何擬定哪個表空間讀寫頻繁

selectname,phyrds,phywi-ts.readtini,writetimfromv$filestata,v$dbfilebwherea.file#=b.file#

orderbyreadtimdesc;

SELECTs.sid,kglpnmod“Mode”,kglpnreq"Req”,SPID**OSProcess''FROMv$sessionwaitw,

x$kglpnp,v$sessions,v$processoWHEREp.kglpnuse=s.saddrANDkglpnhdl=w.plrawand

w.eventlike4%librarycachepin%'ands.paddr=o.addr;

Ll.1.4.查詢?nèi)頀呙璧谋?/p>

SQL>colnamefora30

SQL>selectname,valuefremvSsysstatwherenamein('tabicscans(shorttables)','tabicscans

(longtables)');

NAMEVALUE

(ablescans(shorttables)18602

tablescans(longtables)111

SQL>selectcount(targct),targetfromv$session_longopswhereopnamc='TableScan'groupby

target;

COUNT(TARGET)TARGET

84ECM.APPL.RPTLOG

159ECM_DCTM_OTHR.DMR^CONTENT_S

9ECM_DCTM_OTHR.DM_SYSOBJECT_R

2ECM_DCTM_OTHR.DM_SYSOBJECT_S

1.1.L5,查出全表掃描的表

Selectsql_tcxtfromv$sqltcxtt,v$sql_planpWheret.hash_valuc=p.hash_valueAnd

p.operation=,TABLEACCESS'Andp.opt沁n='FULL'Orderbyp.hash-vahie,t.piece;

1.1.1.6.查找FastFullindex掃描的Sql語句可以這樣:

Selectsql_tcxtfromv$sqltcxtt,v$sql_planpWheret.hash_valuc=p.hash_vaiucAnd

p.operation=,INDEX,Andp.option=,FULLSCAN'Orderbyp.hash-value,t.piece;

L1.L7.查詢硬語法分析的次數(shù)

SQL>selectname,vahiefremvSsysstatwherenamelike'parsecount%';

NAMEVALUE

parsecount(total)16103

parsecount(hard)343

parsecount(failures)5

該項一顯示buffercache大小是否合適。

公式：1-((physicalreads-physicalreadsdirect-physicalreadsdirect(lob))/sessionlogicalreads)

執(zhí)行：

select1-((a.value-b.value-c.value)Zd.value)fromv$sysstata,v$sysstatb,vSsysstatc,vSsysstatd

wherea.namc=,physicalreads'and=,physicalreadsdirect'and

=,physicalreadsdirect(lob)'and='sessionlogicalreads';

1.1.1.8.顯示buffer命中率

公式：1-(physicalreads/(dbblockgcts+consistcntgets))

執(zhí)行:

select1-(suin(decode(name.'physicalreads',value,0)))/(sum(decode(name,'dbblock

gets',value,0))+sum(decode(name,Consistentgets',value,0)))"bufferhitratio"fromv$sysstat;

Softparseratio：

這項將顯示系統(tǒng)是否有太多硬解析。該值將會與原始記錄數(shù)據(jù)對比以保證精確。例如，軟解

析率僅為0.2則表達硬解析率太高。但是，假如總解析量(parsecounttotal)偏低，這項值可以

被忽略。

公式：1-(parsecount(hard)/parsecount(total))

執(zhí)行:

selectl-(a.value/b.value)fromv$sysstata,v$sysstatbWhere='parsecount(hard)'and

='parsecount(total),;

In-mcmorysortratio：該項顯示內(nèi)存中完畢的排序所占比例。最抱負狀態(tài)卜*在OLTP系統(tǒng)

中，大部分排序不僅小并且可以完全在內(nèi)存里完畢排序。

公式：sorts(memory)/(sorts(memory)+sorts(disk))

執(zhí)行：

selecta.value/(b.value+c.viilue)fromvSsysstata,v$sysstatb,v$sysstatcwhere=,sorts

(memory)'and=,sorts(memory)'and=,sorts(disk)';

Parsetoexecuteratio：在生產(chǎn)環(huán)境，最抱負狀態(tài)是?條sql語句一次解析多數(shù)運營。

公式：1-(parsecount/executecount)

執(zhí)行：

select1-(a.valuc/b.valuc)fromvSsysstata,v$sysstatbwherea.natnc=,parsccount(total)5and

=,executecount,;

ParseCPUtototalCPUratio：該項顯示總的CPU花費在執(zhí)行及解析上的比率。假如這項比

率較低，說明系統(tǒng)執(zhí)行了太多的解析。

公式:I-(parsetimeepu/CPUusedbythissession)

執(zhí)行：

selectl~(a.value/b.value)fiomv$sysstata,vSsysstatbwhere=,parsetimeepu'and

=,CPUusedbythissession,;

ParsetimeCPUtoparsetimeelapsed：通常，該項顯示鎖競爭比率。這項比率計算是否時間花

費在解析分派給CPU進行周期運算（即生產(chǎn)工作）。解析時間花費不在CPU周期運算通常表

達由于鎖競爭導致了時間花費。

公式：parsetimecpu/parsetimeelapsed

執(zhí)行：

selecta.value/b.valuefromv$sysstata,v$sysstatbwhere='parsetimecpu'and

b.namc=,parsctimeelapsed';

從V$SYSSTAT獲取負載間檔(LoadProfile)數(shù)據(jù)負載間檔是監(jiān)控系統(tǒng)吞吐量和負載變化

的重要部分，該部分提供如卜每秒和每個事務的記錄信息：logonscumulative,parsecount

(total),parsecount(hard),executes,physicalreads,physicalwrites,blockchanges,andredosize.

被格式化的數(shù)據(jù)可檢查'rales'是否過高，或用于對比其它基線數(shù)據(jù)設(shè)立為辨認syslemprofile

在期間如何變化。例如，計算每個事務中blockchanges可用如下公式：

dbblockchanges/(usercommits+userrollbacks)

執(zhí)行：

selecta.value/(b.valuc+c.value)fromvSsysstata,v$sysstath,v$sysstatcwherea.narnc=,db

blockchangcs,andb.namc=,uscrcommits5andc.namc='uscrrollbacks,;

其它計算記錄以衡量負載方式，如下：

Blockschangedforeachread：這項顯示出blockchanges在blockreads中的比例。它將指出

是否系統(tǒng)重要用于只讀訪問或是重要進行諸多數(shù)據(jù)操作(如：inserts/updales/deletes)

公式：dbblockchanges/sessionlogicalreads

執(zhí)行：

selecta.value/b.valuefromvSsysstata,vSsysstatbwhere=,dbblockchanges'and

=,sessionlogicalreads';

Rowsforeachsort：數(shù)據(jù)排序情況

公式：sorts(rows)/(sorts(memory)+sorts(disk))

執(zhí)行：

selecta.value/(b.value+c.value)fromvSsysstata,v$sysstatb,v$sysstatcwhere=,sorts

(rows)'and=,sors(memory)'and=,sorts(disk)';

1.1.L9,查看某表的約束條件

selectconslraint_name,constraint_type,search_condition.r_constraint_namefrom

user_constraintswheretable_name=upper('&table_name');

selectc.conslraint_naine,c.constraint_lype,cc.column_namefromuser_constraints

c,uscr_cons_columnsccv/hcrcc.owner=uppcr('&table_owncr')andc.table_namc=

uppcr('&table_namc')andc.owncr=cc.owncrandc.constraint_namc=cc.constraint_name

orderbycc.position;

0.查看表的信息

Select*fromuser_tables;

1.查看表空間的名稱及大小

selectt.tablespace_name,round(sum(byles/(1024*1024)),0)ls_sizefromdba_tablespacest,

dba_data_filcsdwheret.tablespacc_namc=d.tablcspacc_namcgroupbyt.tablespacc_namc;

selectt.tablespace_name,round(sum(bytes/(1024*1024)),0)ts_sizefromdba_tablespacest.

dba_data_filesdwheret.tablcspacc_namc=d.tablespacc_namegroupbyt.tablcspacc_name;

TABLESPACE.NAMETS.SIZE

CWML1TE20

DRSYS20

ODM20

PERFSTAT99

SYSTEM400

2.查看回滾段名稱及大小

selectsegment_nanie,tablespace_name,r.status,(initial_extent/1024)InitialExtent,

(next_extent/1024)NextExtcnt,max_extents,v.curextCurExtcntFromdba_rollback_segsr,

v$rolIstatvWherer.segment_id=v.usn(+)orderbysegrient_name;

3.移動一個表的多個分區(qū)

BEGIN

FORxIN(SELECTpartition_name

FROMuser_tab_partitions

WHERE(='BIG_TABLE2')

LOOP

EXECUTEIMMEDIATE'altertabicbig_table2mDvepartition€||x.partitionname;

ENDLOOP;

END;

4.查看LOCK

SELECT/*+ORDEREDUSE_HASH(H,R)*/

H.SIDHOLD_SID,

R.SIDWAIT_SID,

dccodc(H.typc,

”MR","MediaRecovery”,

“RT"，"RedoThread”，

“UN"，"UserName”，

“TX，，，'Transaction”，

叮M",“DML”，

"UL","PL/SQLUserLock”，

"DX"，"DistributedXaction”，

“CF”,"ControlFile”,

“IS",“InstanceState”，

"FS","FileSet”，

“1R"，“InstanceRecovery”，

“ST","DiskSpaceTransaction'\

“TS”,'HempSegmenf；

"IV","LibraryCacheInvalidation^,

"LS"，"LogStartorSwitch”，

“RW"，“RowWait”,

“SQ"，"SequenceNumber”,

"TE","ExtendTable”,

"TT","TempTable”,

H.typc)type,

dccodc(II.lmodc,

0,"None",「Null”,

2,“Row-s(ssy;3,“Row-X(SXr,

4,“Share”,5,"S/Row-X(SSXf,

6,“Exclusive",tochar(H.lniode))hold,

decode(匚request,0,"None",

:'Null”,2,“Row-S(SS)”,

3,“Row-X(SX)”,4,'Share'；

5,MS/Row-X(SSX)”,6,"Exclusive”,

to_char(R.reqjest))request.

R.ID1,R.ID2,R.CTIME

FROMVLOCKH,VLOCKRWHEREH.BLOCK=1ANDR.BLOCK=0andH.TYPE<>”MR"

ANDR.TYPE<>"MR"ANDH.IDI=R.ID1ANDH.ID2=R.ID2

5.找ORACLE字符集

select*fromps$wherename='NLS_CHARACTERSET';

6.查看ORACLE運營的OS平臺

SQL>run

begin

dbms_output.put_line(dbms_utility.port_string);

end;

/

7.查看空間具體使用情況

CREATEORREPLACEPROCEDUREshow.space(

p_segnameINVARCHAR2,

p_ownerINVARCHAR2DEFAULTUSER,

p_typeINVARCHAR2DEFAULT'TABLE,

p_partitionINVARCHAR2DEFAULTNULL)

AS

l_tolal_blocksNUMBER;

l_tolal_bytesNUMBER;

l_unuscd_blocksNUMBER;

l_unused_bytesNUMBER;

l_lastusedextfileidNUMBER;

l」astusedextblockidNUMBER;

l_last_uscd_blockNUMBER;

PROCEDUREp(pjabelINVARCHAR2,p」iumINNUMBER)

IS

BEGIN

DBMS_OUTPUT.put_line(RPAD(pjabel,40,||p_num);

END:

BEGIN

DBMS_SPACE.unused_space

(segment_owner=>p_owner,

scgmcnt_namc=>p_scgnamc,

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_uscd_cxtcnt_fiic_id=>IJastuscdcxtfileid,

last_used_extent_block_id=>Uastusedextblocki

last_used_block=>l_Iast_used_block

)；

p('TotalBlocks',l_total_blocks);

p('TotalBytes',l_total_bytes);

p('UnusedBlocks',l_unused_blocks);

p('UnusedBytes\l_unused_bytes);

p('LastUsedExtFileld,IJastusedextfileid);

p('LastUsedExtBlockld',IJastusedextblockid);

p('LastUsedBlock',l_last_used_block);

END;

1.1.1.18.顯示緩沖區(qū)的相關(guān)SQL

SELECTtch,file#,dbablk,

CASE

WHENobj=

THEN'rbs/compatsegment'

ELSE(SELECTMAX(*(11object_type||')'||owner||7||)||DECODE

(COUNT(*),1,‘maybe!')FROMdba.objeccsWHEREdata_object_id=x.obj)

ENDwhat

FROM(SELECTtch,file#,dbablk,objFROMx$bhWHEREstate<>0ORDERBYtchDESC)x

WHEREROWNUM<=5;

Ll.1.19.獲取生成的根據(jù)文獻名

selectc.value||7'||d.instance_name||LoraJ||a.spid||'.trc'tracefromv$processa,v$session

b,v$paramctcrc,vSinstanccdwherea.addr=b.paddrandb.audsid=uscrcnv('scssionid')and

=,user_dump_dest';

在v$scssion_longops視圖中，sofar字段表達已經(jīng)掃描的塊數(shù)，totalwork表達總得需要

掃描的塊數(shù)，所以我們即可以對正在運營的長查詢進行監(jiān)控，比如在索引創(chuàng)建時，查看索引

創(chuàng)建的進度，也可以查看系統(tǒng)中以往的長查詢。

colopnameformata32

coltargct_dcscformata32

colperworkformata12

setlines131

selectsid,OPNAME.TARGET_DESC,sofar,TOTALWORK,!runc(sofar/totalwork*100,2)||'%'as

perworkfromv$session_longopswheresofar!=totalvvork;

setlines121

setpages999

colopnameformata29

coltargetformata29

col(arge(_descformata12

colperworkformata12

colremainformat99

colstart_timeformata21

colsofarformat99999999

coltotalworkformat99999999

colsql_textformata101

colbufgetsformal99999999

selectopname,target,to_char(start_time,'yy-mm-dd:hh24:mi:ss,)start_time,elapsed_seconds

elapsed,executionsexecs,buffer_gets/decode(executions,0,1executions)bufgets,module,sqljext

fromvSsession_longopssl,v$sqlarcasawheresl.sql_hash_valuc=sa.hash_valueand

upper(substr(module,1,4))<>'RMAN'andsubstr(opname,l,4)<>'RMAN'andmodule<>

'SQL*Plus'andsl.start_lime>trunc(sysdate)orderbys(art_time;

0.IO事件監(jiān)控

i.監(jiān)控事例的等待

selectevent,sum(dccodc(w￡it_Timc,0,0,1))"Prev",sumidccodc(\vait_Timc,0,1,0))

"Curr",count(*)"Tot"fromv$session_Waitgroupbyeventorderby4;

2.回滾段的爭用情況

selectname,waits,gets,waits/gets"Ratio"fromv$rollstala,v$rollnamebwherea.usn

=b.usn;

3.監(jiān)控表空間的I/O比例

selectdf.tablespace_namename,df.file_name"file'\fphyrdspyr,f.phyblkrdpbr,f.phywrts

pyw,f.phyblkwrtpbwfromv$filestatf,dba_data_filescfwheref.file#=df.file_id

orderbydf.tablcspacc_name;

4.監(jiān)控文獻系統(tǒng)的I/O匕例

selectsubstr(a.file#,1,2)substr(,l,30)"Name",a.status,a.byles,b.phyrds,

b.phywrtsfromv$datafilea,v$filestatbwherea.file#=b.file#;

1.在某個用戶下找所有的索引

selectuser_indexes.table_name,user_indexes.index_name,uniqueness,column_namefrom

user_ind_columns,uscr_incexcswhereuser_ind_columns.indcx_naine=user_indexes.index_namc

anduser_ind_columns.table_name=user_indexes.table_nameorderbyuser_indexes.table_type,

user_indexes.lable_name,user_indexes.in(.lex_name,column_posi(ion;

2.監(jiān)控SGA的命中率

selecta.value+b.value"logical_reads",c.value"phys_reads",round(100*

((a.value+b.value)-c.value)/(a.value+b.valuc))"BUFFERHITRATIO"fromv$sysstata,

v$sysstatb.v$syssta(cwherea.statistic#=38andb.statistic#=39andc.statistic#=40;

3.監(jiān)控SGA中字典緩沖區(qū)的命中率

selectparameter,gcts,Gctmisscs,gctmisscs/(gcts?gctmisscs)*100"missratio",

(I-(sum(getmisses)/(sum(g3ts)+sum(getmisses))))*100"Hitratio"fromv$rowcache

wheregets+getmisses<>0groupbyparameter,gets,getmisses;

監(jiān)控SGA中共享緩存區(qū)的命中率，應當小于1%

selectsum(pins)"TotalPins",siim(rclcads)"TotalReloads",snm(reloads)/si]m(pins)*100

libcachefromv$librarycache;selectsum(pinhits-reloads)/sum(pins)"hit

radio',,sum(reloads)/sum(pins)"reloadpercent"fromvSlibrarycache;

顯示所有數(shù)據(jù)庫對象的類別和大小

selectcount(name)nuin_ins(ances,lype,sum(source_size)source_size,sum(parsed_size)

parsed_size,surn(code_size)code_size,sum(error_size)error_size,sum(source_size)

+sum(parsed_size)+sum(code_size)+sum(eiTor_size)size_requiredfromdba_object_size

groupbytypeorderby2;

監(jiān)控SGA中重做日記緩存區(qū)的命中率，應當小于1%

SELECTname,gets,misses,immediate_gets,immediale」nis$es,Decode(gets,0,0,misses/gets*100)

ratio1,Decode(iminedia(e_?ets+iminedia(e_misses,(),0,imniediate_misses/(immediate_gets+

innncdiate_misses)*I()0)ratio2FROMv$latchWHEREnameIN('redoallocation','redocopy');

監(jiān)控內(nèi)存和硬盤的排序比率，最佳使它小于.10,增長sort_area_size:

SELECTname,valueFROMv$sysstatWHEREnameIN('sorts(memory)','sorts(disk),);

監(jiān)控當前數(shù)據(jù)庫誰在運營什么SQL語句

SELECTosuser,username,sql_textfromv$sessiona,v$sqltextbwherea.sql_address=b.address

orderbyaddress,piece;

監(jiān)控字典緩沖區(qū)

select(sum(pins-reloads))/sum(pins)"libcache"fromv$librarycache;select(sum(gets-

gctmisses-usage-fixed))/sum(gcts)"rowcache"fromv$rowcachc;selectsum(pins)

"executions",sum(reloads)"cachemisseswhileexecuting"fromv$librarycache;

后者除以前者，此比率小于1%,接近0%為好。

selectsum(gets)"dictionarygets",sum(getmisses)"dictionarycachegetmisses"from

v$rowcache;

4.監(jiān)控MTS

selectbusy/(busy+idle)"sharedserversbusy"fromvSdispatcher;

此值大于0.5時，參數(shù)需加大。

selectsuin(wait)/sum(totalq)"dispatcherwaits"fromv$qucucwheretypc='dispatchcr';

selectcount(*)fromv$dispatcher;

selectservers_highwaterfromvSmts;

servers_highwater接近mts_max_servers時，參數(shù)需加大

5.碎片限度

selecttablespace_name,count(tablespace_name)fromdba_lree_spacegroupbytablespace_name

havingcount(tablespace_name)>10;

altertablespacenamecoalesce;

altertablenamedeallocateunused;

createorreplaceviewts_blocks_vas

selecttablcspacc_namc,block_id,bytes,blocks,'freespace15cgmcnt_namcfromdba_frcc_spacc

unionall

selecttablespace_name.block_id,bytes,blocks,segment_rainefromdba_extents;

select*fromts_blocks_v;

selecttablespace_namc,suin(bytcs),max(bytes),count(biock_id)fromdba_frcc_spaccgroupby

tablespace_name;

6.查看碎片限度高的表

SELECTscgmcnt_namctablc_namc,COUNT(*)extentsFROMdba_scgmcntsWHEREowner

NOTIN('SYS','SYSTEM1；GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECT

MAX(COUNT(*))FROMdba_segmenlsGROUPBYsegnent_name);

7.表、索引的存儲情況檢查

selectsegment_name,sum(bytes),count(*)ext_quanfromdba_extentswheretablespace_n￡me

='&tablespace_name'andsegment_type='TABLE'groupbytablespace_name,segment_name;

selectsegment_name,count(*)fromdba_extentswheresegment_type='INDEX'and

owner='&owner'groupbysegment_name;

8.找使用CPU多的用戶session

cpuusedbythissession：查找各回話連接的CPU使用率

selecta.sid,spid,status,substr(grani.1,40)prog,a.tcrminal,osuscr,value/60/100valuefrom

v$sessiona,v$processb,v$sesstatcwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addr

orderbyvaluedesc;

9.尋找CPU使用過量的session

SELECTsql_textFROMv$sqltextaWHERE(a.hash_value,a.address)IN(SELECT

deccde(sql_hash_vahie,0.prev_hash_value,sql_hash_valne),decode(sql_hash_valiie,0,

prev_sql_addr,sql_address)FROMv$sessionbWHEREh.paddr=(SELECTaddrFROM

v$processcWHEREc.spid='&pid'))ORDERBYpieceASC;

1.1.2.數(shù)據(jù)庫巡檢管理

1.1.2.L平常檢測

LL2.L1.檢測系統(tǒng)卷狀態(tài)

df-k可以看到系統(tǒng)各個卷的使用情況；假如文獻系統(tǒng)的剩余空間小于20%，需刪除不

用的文獻以釋放空間?；蛘蚁到y(tǒng)管理員調(diào)整空間大小。

*df-k

Filesystem1024-blocksFreezUsedIusedzIusedMountedon

/deu/hd4104857627237675z15403z/

/dev/hd23145728163693248z3714810ZZusr

Zdev/hd9uar104857610144764z532lxZuar

/deu/hd33145728189711240z439927.Ztmp

Zdeu/lidl655366516017.7/home

/proc——一——/proc

Zdev/hdl0opt52428843905217z169527./opt

Zdeu/lu0210485760202132081z115215Zwps

Zdev/lv01524288003035012843”3217lxZo>?adata

Zdeu/lu0010485760562436847z733923zZoracle

L.檢查表空間的使用情況

SELECTtablespace_name,max_m,counl_blocksfree_blk_cnt,sum_free_m.

to_char(100*suin_free_ni/sum_m,'99.99')||'%'ASpct_frccFROM(SELECTtablcspace_nanie,

sum(bytcs)/1024/1024ASsum_mFROMdba_data_filcsGROUPBYtablcspacc_namc),

(SELECTtablespace_nameASfs_ts_name,max(bytes)/1024/1024ASmax_m,count(blocks)

AScount_blocks,sum(by(es/1024/1024)ASsum_free_mFROMdba_free_spaceGROUP

BYtablespacc_namc)WHEREtablcspacc_namc=fs_ts_name:

rftBLESPACE_NfiMEFREE_BLK_CNTSUM_FREE_MPCT_FRE

CITICIWCM199.93751199.937566.65z

CWMLITE8518585.00Z

DRSVS90.3125190.312590.31Z

EXAMPLE97.6875197.687541.76Z

ICMLFQ3229?.251297.2599.08Z

ICMLNF3224.5124.598.00Z

ICMLSNDX.43751.43754.38Z

ICMSFQ04142.56251142.562595.04Z

ICMUFQ0421.9375121.937587.75Z

INDX24.9375124.937599.75%

IWCM99.9375199.937599.94Z

rABLESPACE_NAMEMAX_MFREE_BLK_CNTSUM_FREE_MPCTJFRE

ODM10.5625110.562552.81Z

SYSTEM150.81251150.812530.167

FOOLS9.937519.937599.38/

UND0TBS11902?409.37581.88Z

USERS.937521.1251.13%

XDB15.3125115.312525.52%

selectsum(bytes)/(1024*1024)asfree_space,tablespace_namefromdba_free_space

groupbytablespace_namc;

1?121.3.檢測表空間狀態(tài)

DBA_FREE_SPACE是數(shù)據(jù)庫的?個視圖,可以看到數(shù)據(jù)庫的空間使用信息。

SELECTtablcspacc_namc,sum(bytcs)/1024/1024'MBFree"FROMdba_frcc_spacc

WHERE(ablespace_nameoTEMP'GROUPBYtablespac3_name;

TABLESPACE_NAMEMBFree

CITICIWCM199.9375

CWMLITE85

DRSVS90.3125

EXAMPLE97.6875

INDX24.9375

IWCM99.9375

ODM10.5625

SVSTEM153.875

TOOLS9.9375

UNDOTBS1373

USERS49.9375

TABLESPACE_NAMEMBFree

XDB15.3125

1.1.2.L4.檢測Oracle運營狀態(tài)

ORACLE的狀態(tài)可以通確認所有的INSTANCE狀態(tài)正常

登陸到所有數(shù)據(jù)庫或例程,檢測ORACLE后臺進程：

$ps-efjgrcpora來查看;

wps-ef:greporacle

oracle57601019:33:49-0:23ora_ckpt_testwps

oracle162041019:33:49—0:06ora_snon_testwps

oracle280181019:22:07一0:00/oracle/app/product/9.2.0/bin/t

LslsnvLISTENER-inherit

oracle290121019:33:48一0:08ora_lgwr_testwps

oracle314661019:33:48—0:08ora_dbw0_testwps

oracle317161019:33:44-0:17ora_pnon_testwps

oracle461961019:33:49—0:00ora_s000_testwps

oracle492621019:33:49一0:00ora_reco_testwps

oracle509161021:23:11—0:00oracletestwps<LOCAL=NO>

oracle1318721019:33:50—0:00ora_d000_testwps

oracle1343981021：24：440：00orncIctestwps<LOCAL-NO>

root13656838362116:00:24pts/10:00greporacle

LL2.L5.檢查數(shù)據(jù)庫版本信息

sqlplus47assysdba”

select*fromv$version;

SQL>select*fromu$uersion；

BANNER

Oracle9iEnterpriseEditionRelease9.2.0.6.0-64bitProduction

PL/SQLRelease9.2.0.6.0-Production

CORE9.2.0.6.0Production

TNSforIBM/AIXRISCSystem/6000：Uersion9.2.0.6.0-Production

NLSRTLUersion9.2.0.6.0-Production

L1.2.L6.檢查日記文獻和trace文獻記錄alert和trace

文獻中的錯誤

連接到每個需管理的系統(tǒng)，使用‘lehieL登陸,對每個數(shù)據(jù)庫，cd到bdump目錄，通

常是$ORACLE_BASE/<SID>/bduinp,使用Unix“ail，命令來查看alen_<SID>.log文獻,

假如發(fā)現(xiàn)任何新的ORA-錯誤,記錄并解決。

$tailalert_testv/ps.log

Conpleted：createtablespaceICMSFQ04datafile,Zoracle/app/o

UedDec314：19：582008

createtablespaceICMLSNDXdatafile*Zoracle/app/oradata/testwps/index/testwps_

CMLSNDX_01.dbf,size10Mreuseautoextendonnext10MnaxsizeUNLIMITEDextenti

anagenentlocalautoallocate

ORA-1543signalledduring：createtablespaceICMLSNDXdatafile*Zoracle/app/o..

l/edDec315：17：402008

Thread1advancedtologsequence18

Currentlogit3seqtt18memtt0：Zoracle/app/oradata/testwps/redo03.log

UedDec315：27：262008

Thread1advancedtologsequence19

Currentlogtt4seqtt19nenttA:Zo>?acle/app/oradata/testwps/redoA4.log

.7.查看會話掛起情況

關(guān)閉不必要的會話

命令如下：

selectsid,serial#,username,program,machine,statusfromvSsession;

手工斷開某個會話，則執(zhí)行：

altersystemkillsession'SID,SERIAL#';

SIDSERIAL"USERNAME

PROGRAM

1ACHINESTATUS

86043ICMADMIN

testportalftCTIUE

96651SVS

pqlplusPtestportal<TNSU1-U3)

testportalACTIVE

SIDSERIAL。USERNAME

PROGRAM

卜ACHINESTATUS

1.1.2.L8.檢查數(shù)據(jù)庫當天備份的有效性

對RMAN備份方式：

檢查第三方備份工具妁備份日記以擬定備份是否成引

對EXPORT備份方式：

檢查exp日記文獻以擬定備份是否成功。

對其他備份方式：

檢查相應的日記文獻。

1.L2.1.9.檢查數(shù)據(jù)文獻的狀態(tài)記錄狀態(tài)

不是“onl