OracleDatabase登陸到數(shù)據(jù) 服務(wù)器端配置 客戶端 檢查Oracle 登陸數(shù)據(jù)庫(kù)的方 數(shù)據(jù)庫(kù)的啟 關(guān)閉數(shù)據(jù) 用戶管 檢察用戶 查看用戶profile參 檢查數(shù)據(jù)庫(kù)基本狀 檢查數(shù)據(jù)庫(kù)創(chuàng)建日 檢查數(shù)據(jù)庫(kù)版本信 檢查實(shí)例狀 查看前臺(tái)進(jìn) 查看數(shù)據(jù)庫(kù)連接的 查看連接到數(shù)據(jù)庫(kù)的模 查看并發(fā)連接 查看最大的連接 監(jiān)控系統(tǒng)后臺(tái)進(jìn) 查看數(shù)據(jù)庫(kù)初始化參 檢查PGA使用情 檢查SGA狀 檢查Oracle服務(wù)進(jìn) 檢查Oracle監(jiān)聽(tīng)狀 檢查監(jiān)聽(tīng)進(jìn)程是否存 檢查操作系統(tǒng)日志文 檢查oracle日志文 檢查Oracle核心轉(zhuǎn)儲(chǔ)目 檢查Root用戶和Oracle用戶的 檢查Oracle對(duì)象狀 檢查Oracle控制文件狀 檢查Oracle在線日志狀 檢查Oracle表空間的狀 檢查Oracle所有數(shù)據(jù)文件狀 檢查無(wú)效對(duì) 檢查所有回滾段狀 檢查用戶下的 檢查用戶默認(rèn)表空 檢查當(dāng)前用戶角色及權(quán) 檢查用戶下的各個(gè)表的大 檢查一個(gè)表的創(chuàng)建時(shí) 檢查某個(gè)表的大 檢查每個(gè)表占用磁盤(pán)空間情 檢查Oracle相關(guān)資源的使用情 檢查Oracle初始化文件中相關(guān)參數(shù) 檢查數(shù)據(jù)庫(kù)連接情 檢查系統(tǒng)磁盤(pán)空 檢查表空間使用情 檢查一些擴(kuò)展異常的對(duì) 檢查表空間碎片情 檢查system表空間內(nèi)的內(nèi) 檢查對(duì)象的下一擴(kuò)展與表空間的最大擴(kuò)展 檢查flashrecoveryarea空 檢查Oracle數(shù)據(jù)庫(kù)性 查詢表空間讀寫(xiě)情 查詢r(jià)edologbuffer的繁忙程 判斷undo表空間的使用情 分析日志組切換頻 查看等待事 檢查數(shù)據(jù)庫(kù)cpu、I/O、內(nèi)存性 內(nèi)存使用情 系統(tǒng)I/O情 系統(tǒng)負(fù)載情 查看是否有僵死進(jìn) 檢查緩沖區(qū)命中 檢查共享池命中 檢查排序 檢查日志緩沖 檢查失效的索 檢查不起作用的約 檢查無(wú)效的 檢查尚未建立索引的 檢查運(yùn)行時(shí)間長(zhǎng)的 檢查性能差的前10條 查看占io較大的正在運(yùn)行的 檢查消耗CPU最高的PID對(duì)應(yīng)的 檢查占用CPU多的 檢查表空間的 檢查臨時(shí)表空間 檢查鎖和等 檢查數(shù)據(jù)庫(kù)安全 檢查系統(tǒng)安全日志信 檢查登錄失敗的日志 檢查用戶修改密 數(shù)據(jù)表空間日常維 查看表空間的一些信 創(chuàng)建表空 表空間擴(kuò) 創(chuàng)建大數(shù)據(jù)文 數(shù)據(jù)表空間文件遷 不停機(jī)移動(dòng)表空間文 存儲(chǔ)過(guò)程管 找出特定用戶的存儲(chǔ)過(guò) 通過(guò)表名找出存儲(chǔ)過(guò) 查看存儲(chǔ)過(guò)程內(nèi) 觸發(fā)器管 找出數(shù)據(jù)庫(kù)中所有觸發(fā) 找出特定用戶的觸發(fā) 找出當(dāng)前用戶定義的觸發(fā) 查看某個(gè)用戶自定義的觸發(fā)器內(nèi) 查看某個(gè)表關(guān)聯(lián)的觸發(fā) 查看當(dāng)前用戶所有觸發(fā)器及存儲(chǔ)過(guò) RedoLog管 離線遷移日志文 在線更改RedoLog文件容 服務(wù)器端配置LISTENER=(DESCRIPTION_LIST=(DESCRIPTION(ADDRESS=(PROTOCOL=IPC)(KEY(ADDRESS=(PROTOCOL=TCP)(HOST=0)(PORT=))ADR_BASE_LISTENER=/u01/app/oracle#BEQUEATH#PRESPAWNCONFIG)))ServiceSIDGLOBAL_DBNAME客戶端根據(jù)tnsname.ora中的SERVICE_NAME和地址(ADDRESS=(PROTOCOL==racdb1)(PORT=1521))，到這個(gè)地址去訪問(wèn)監(jiān)聽(tīng)器。然后監(jiān)聽(tīng)器lisnter.ora文件中的GLOBAL_NAMEGLOBAL_DBNAME和SERVICE_NAME相等則建立客戶端到SID標(biāo)識(shí)的服務(wù)端實(shí)例的連接,在客戶端上我們可以使用tnsping命令來(lái)測(cè)試客戶端racdb1=(description=(address_list(address=(protocol=tcp)(host=0)(port=)(service_name))service_name=racdb1GLOBAL_DBNAME=racdb1[oracle@racdb1~]$tnspingracdb1TNSPingUtilityforLinux:Version.0-Productionon21-JAN-201514:51:49Copyright(c)1997,2009,Oracle. Allrightsreserved.TNS-03502:Insufficientarguments. tnsping<address>[<count>][oracle@racdb1~]$tnspingracdb1TNSPingUtilityforLinux:Version.0-Productionon21-JAN-201514:51:55Copyright(c)1997,2009,Oracle. Allrightsreserved.UsedparameterUsedTNSNAMESadaptertoresolvetheAttemptingtocontact(description=(address_list=(address=(protocol=tcp)(host=racdb1)(port=1521)))(connect_data=(service_name=racdb1)(ur=a)))OK(0Oraclelsnrctlstoplsnrctlstartlsnrctlstatuslsnrctloracleoracle安裝好后是使用操作系統(tǒng)用戶的驗(yàn)證,sys用戶不用密碼就可以登錄,oracle密碼文件驗(yàn)證的話我們就要進(jìn)入下列文件夾cd/u01/app/oracle/product/11.2.0/dbhome_1/network/adminsqlnet.oraSQLNET.AUTHENTICATION_SERVICES=tnsnames登錄數(shù)據(jù)庫(kù)方式[oracle@racdb1~]$sqlplusscott/111111@racdb1sqlplus/nologscottracdb1connsys/111111@racdb1ASSYSDBA;selectinstance_namefromv$instancesys用戶登陸connsys/change_on_installerassysdbaconn/assysconnsys/change_on_installassysdba;conn/assysdba連接數(shù)據(jù)庫(kù)connscott/111111scottstartupstartupstartupstartup openstartup啟動(dòng)數(shù)據(jù)庫(kù)實(shí)例,該步驟只是啟動(dòng)了一個(gè)數(shù)據(jù)庫(kù)實(shí)例.Select*from Select*from Select*from alterdatabaseselect*fromv$database;select*fromv$tablespace;select*fromv$log;alterdatabasestartupaltersystemenablerestrictedsessiongrantrestrictsessiontoscottrestrictselectname,DESCRIPTIONfromv$bgprocesswhereshutdownShutdownshutdown,當(dāng)前所有做工作回滾到一致?tīng)顟B(tài),shutdownselectusername,profilefromselectusername,profilefromdba_userswhereselect*fromdba_profilesselect*fromdba_profileswhereSelectCreated,Log_Mode FromV$Database;SelectCreated,Log_Mode FromV$Database; 2014/8/171SelectversionSelectversionfromProduct_component_versionwhereSQL>SELECTSQL>SELECTinst_id,instance_name,host_name,VERSION,TO_CHAR(startup_time,mm-ddhh24:mi:ss')startup_time,status,archiver,database_statusFROMSELECTinst_id,dbid,NAME,TO_CHAR(created,'yyyy-mm-ddhh24:mi:ss')created,de,TO_CHAR(version_time,'yyyy-mm-ddhh24:mi:ss')version_time,open_modeFROMgv$DBID 942644082014-08-1716:34:32NOARCHIVELOG2014-08-1716:34:32DSQL>selectname,log_mode,open_modefromSQL>selectname,log_mode,open_modefrom READL”eL表示數(shù)據(jù)庫(kù)運(yùn)行在歸檔模式下，E表示數(shù)據(jù)庫(kù)運(yùn)行在非歸檔模式下。在我們的系統(tǒng)中數(shù)據(jù)庫(kù)必須運(yùn)行在歸檔方式下。ps-Cps-Coracle-o'rsz,sid,cmd'|grepfree-m|egrep-v'total|buffers'|awk'{print$1,$3說(shuō)明Oracle的前臺(tái)進(jìn)程是操作系統(tǒng)進(jìn)程，它和oraclesessionoracle=1.5*processSQL>selectcount(*)SQL>selectcount(*)fromselectprogramselectprogramfromv$processorderbyselectd.NAME,s.NAMEfromv$dispatcherd,v$shared_servers,v$circuitcwhered.PADDR=c.DISPATCHERands.PADDR=c.SERVER說(shuō)明selectcount(*)selectcount(*)fromv$sessionwhereSQL>selectvalueSQL>selectvaluefromv$parameterwherename=SQL>SelectSQL>Selectname,DescriptionFromV$BGPROCESSWhere name,valuefrom name,valuefromv$parameterwhereisbasic='TRUE'orderby說(shuō)明selectname,valuefromv$pgastatselectname,valuefromv$pgastatwherenamein('maximumPGAallocated','totalPGASELECTrequest_misses, FROMSELECTrequest_misses, FROMSelectcomponent,current_size,min_size,max_sizefromv$sga_dynamic_components;REQUEST_MISSESREQUEST_FAILURES00期望結(jié)果：request_missesrequest_failures0巡檢說(shuō)明：request_missesLRU列表刷新對(duì)象的次數(shù)；request_failures是未找到滿足請(qǐng)求的內(nèi)存次數(shù)。[oracle@racdb1~]$ps-ef|grep[oracle@racdb1~]$ps-ef|grepora_|grep-vgrep&&ps-ef|grepora_|grep-vgrep|wc1015:5300:00:001015:5300:00:171015:5300:00:001015:5300:00:001015:5300:00:001015:5300:00:001015:5300:00:021015:5300:00:001015:5300:00:001015:5300:00:011015:5300:00:051015:5300:00:001015:5300:00:001015:5300:00:00OracleOracleOracleOracleOracleOracleOracleOracle[oracle@racdb1~]$lsnrctl[oracle@racdb1~]$lsnrctlLSNRCTLforLinux:Version.0-Productionon21-JAN-201517:00:42Copyright(c)1991,2009,Oracle. Allrightsreserved.ConnectingtoSTATUSoftheStartDateTraceLevelTNSLSNRforLinux:Version.0-Production21-JAN-201512:37:480days4hr.22min.53secON:LocalOSAuthenticationListenerLogFile ListeningEndpointsSummary...ServicesService"racdb1"has1Instance"racdb1",statusUNKNOWN,has3handler(s)forthisService""has1Instance"racdb1",statusREADY,has1handler(s)forthisService"racdb1XDB."has1Instance"racdb1",statusREADY,has1handler(s)forthisThecommandcompleted“ServicesSummary”O(jiān)racle的監(jiān)聽(tīng)進(jìn)程正在監(jiān)聽(tīng)哪些數(shù)據(jù)庫(kù)實(shí)例，輸出顯示中至少應(yīng)[oracle@racdb1 ps[oracle@racdb1 ps-ef|greplsn|grep-v 012:37? 00:00:01/u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnrLISTENER-inherit[root@racdb1~]#[root@racdb1~]#cat/var/log/messages|grep說(shuō)明OracleSQL>selectvaluefromSQL>selectvaluefromv$diag_infowherename='DiagTrace';catcat/u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_racdb1.log|grepora-cat/u01/app/oracle/diag/rdbms/racdb1/racdb1/trace/alert_racdb1.log|greperr[oracle@racdb1trace]$cat|grep說(shuō)明e在運(yùn)行過(guò)程中，會(huì)在警告日志文件_.及如果因?yàn)闄z查點(diǎn)pi操作沒(méi)有執(zhí)行完成造成不能切換，會(huì)記錄不能切換的原因；－60)TRCOraclebug selectvalue fromv$parameterwhere selectvalue fromv$parameterwherename='core_dump_dest';[oracle@racdb1cdump]$ls/u01/app/oracle/diag/rdbms/racdb1/racdb1/cdump/*.trc|wc-Oracle進(jìn)程經(jīng)常發(fā)生核心轉(zhuǎn)儲(chǔ)。這說(shuō)明某些用后臺(tái)進(jìn)程的核心轉(zhuǎn)儲(chǔ)會(huì)導(dǎo)致數(shù)據(jù)庫(kù)異常終止。Oracle常用日志文件位置查詢select*fromv$parameterwherename='background_dump_dest';select*fromv$parameterwherename='user_dump_dest';select*fromv$parameterwherename='core_dump_dest';select*fromv$parameterwherename='audit_file_dest';select*fromv$parameterwherename='audit_syslog_level';[oracle@racdb1racdb1]$tail-n200/var/mail/oracle[oracle@racdb1racdb1]$tail-n200/var/mail/root[oracle@racdb1racdb1]$tail-n200/var/mail/oracle[oracle@racdb1racdb1]$tail-n200/var/mail/root說(shuō)明Oracle檢查OracleOracle所有回滾段的狀態(tài)，總共六Oracle select selectstatus,namefromv$controlfile; SQL>selectgroup#,status,type,memberfromSQL>selectgroup#,status,type,memberfromGROUP# 321 3條以上（3條）記錄，“STATUS”應(yīng)該為非“INVALID”，非“DELETED”SQL>selecttablespace_name,statusfromSQL>selecttablespace_name,statusfrom 6rowsSTATUSONLINESQL>selectname,statusfromSQL>selectname,statusfrom selectfile_name,statusfrom selectfile_name,statusfrom owner!='SYS'andowner!='SYSTEM';norows說(shuō)明SELECTowner,SELECTowner,object_name,object_typeFROMdba_objectsWHEREstatus=SQL>selectSQL>selectsegment_name,statusfrom11rowsselect*select*from select*select*fromdba_role_privswhereGRANTEE='SCOTT';select*fromdba_sys_privswhereGRANTEE='SCOTT';select*fromdba_tab_privswhereGRANTEE='SCOTT';SelectSegment_Name,Sum(bytes)/1024/1024SelectSegment_Name,Sum(bytes)/1024/1024MBFromdba_ExtentswhereOWNER='SCOTT'GroupBySegment_Nameorderbysum(bytes)/1024/1024desc;selectobject_name,createdselectobject_name,createdfromdba_objectswhereselectsum(bytes)/(1024*1024)selectsum(bytes)/(1024*1024)as"size(M)"fromdba_segmentswheresegment_name=upper('&table_name');selectb.file_idselectb.file_idfile_ID,b.tablespace_nametablespace_name,b.bytesBytes,(b.bytes-sum(nvl(a.bytes,0)))used,sum(nvl(a.bytes,0))free,sum(nvl(a.bytes,0))/(b.bytes)*100Percentfromdba_free_spacea,dba_data_filesbwherea.file_id=b.file_idgroupbyb.tablespace_name,b.file_id,b.bytesorderbyb.file_id;檢查OracleOracleOracle初始化文件中相關(guān)的參數(shù)Oracle各個(gè)表空間使用情況，檢查一system表空間內(nèi)的內(nèi)容，檢查對(duì)象的下一擴(kuò)展與表空間的最大擴(kuò)SQL>selectresource_name,max_utilization,initial_allocation,limit_valuefromv$resource_limit;SQL>selectresource_name,max_utilization,initial_allocation,limit_valuefromv$resource_limit;000000000000000000000000001101023rowsSQL>selectSQL>selectcount(*)fromselects.osuseros_user_name,decode(sign(48-command),'ActionCode#'||to_char(command))action,gramoracle_process,statussession_status,s.terminalterminal,gramprogram,s.usernameuser_name,s.fixed_table_sequenceactivity_meter,''query,000cpu_usage,s.serial#fromv$session v$process wheres.paddr=p.addrs.type=orderbys.username,SQL>selectsid,serial#,username,program,machine,statusfrom其中：SID會(huì)話(session)IDSERIAL#SID一起用來(lái)唯一標(biāo)識(shí)一個(gè)會(huì)話；USERNAME建立該會(huì)話的用戶名；PROGRAMSTATUS當(dāng)前這個(gè)會(huì)話的狀態(tài)，ACTIVE表示會(huì)話正在執(zhí)行某些任務(wù)，INACTIVE表示當(dāng)前會(huì)DBA要手工斷開(kāi)某個(gè)會(huì)話，則執(zhí)行：（一般不建議使用這種方式去殺掉數(shù)sessionsid查到操作系統(tǒng)的spid,使用ps–ef|grepspidno的方式確認(rèn)spid不是ORACLE的后臺(tái)進(jìn)程killaltersystemkillaltersystemkillsessionSID110(USERNAME列為空)Oracle的后臺(tái)進(jìn)程，不要對(duì)這些[oracle@racdb1 df-[oracle@racdb1 df- UsedAvailUse%Mounted 5% 34% 37%1% 1% 1% 15%2%SELECTdf.file_id,df.file_name,df.size_mb,NVL(free.maxfree,0)ROUND(NVL(free.free_mb,0),2)free_mb,100-ROUND(100.0*NVL(free.free_mb,0)df.size_mb,2)ROUND(100.0*NVL(free.free_mb,0)/df.size_mb,2)FROM(SELECTfile_id,file_name,tablespace_name,BYTES/1048576size_mbFROMdba_data_files)df, file_id,SUM(BYTES)/1048576free_mb,TRUNC(MAX(BYTES/1024/2)FROMdba_free_spaceGROUPBYfile_id)freeWHEREdf.file_id=free.file_id(+)ORDER %6155354SSQL>select(1-(A.total)/B.total)*100used_percent(selecttablespace_name,sum(bytes)totalfromdba_free_spacegroupbytablespace_name)(selecttablespace_name,sum(bytes)totalfromdba_data_filesgroupbytablespace_name)where SELECTD.TABLESPACE_NAME,FILE_NAME"FILE_NAME",SPACE"SUM_SPACE(M)",SPACE-NVL(FREE_SPACE,0)"USED_SPACE(M)",ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2)FROM(SELECTFILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKSFROMGROUPBYTABLESPACE_NAME,FILE_ID,FILE_NAME)(SELECTFILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)FREE_SPACEFROMDBA_FREE_SPACEGROUPBYTABLESPACE_NAME,FILE_ID)(SELECTFILE_ID,AUTOEXTENSIBLEFROMDBA_DATA_FILES)WHERED.TABLESPACE_NAME=E.TABLESPACE_NAME(+)ANDD.FILE_ID=E.FILE_ID(+)ANDD.FILE_ID=F.FILE_ID(+)UNIONALL--ifhaveSELECTD.TABLESPACE_NAME,FILE_NAME"FILE_NAME",SPACE"SUM_SPACE(M)",USED_SPACE"USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2)"USED_RATE(%)",AUTOEXTENSIBLE(SELECTFILE_ID,FILE_NAME,TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2)SPACE,SUM(BLOCKS)BLOCKSFROMGROUPBYTABLESPACE_NAME,FILE_ID,FILE_NAME)(SELECTFILE_ID,TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2)USED_SPACE,ROUND(SUM(BYTES_FREE)/(1024*1024),2)FREE_SPACEFROMGROUPBYTABLESPACE_NAME,FILE_ID)(SELECT(SELECTFILE_ID,AUTOEXTENSIBLEFROMDBA_TEMP_FILES)WHERED.TABLESPACE_NAME=E.TABLESPACE_NAME(+)ANDD.FILE_ID=E.FILE_ID(+)ANDD.FILE_ID=F.FILE_ID(+)ORDERBY10%2G4G8G。selectSegment_Name,selectSegment_Name,Segment_Type,TableSpace_Name,(Extents/Max_extents)*100PercentFromWhereMax_Extents!=0and(Extents/Max_extents)*100>=95orderByPercent;norows說(shuō)明selecttablespace_name,selecttablespace_name,count(*)chunks,max(bytes)/1024/1024max_chunk,sum(bytes)/1024/1024total_spacefromdba_free_spacegroupby說(shuō)明其中，CHUNKS列表示表空間中有多少可用的空閑塊(每個(gè)空閑塊是由一些連續(xù)的Oracle數(shù)據(jù)塊組成)，如果這樣的空閑塊過(guò)多，比如平均到每個(gè)數(shù)據(jù)文件上超過(guò)了100個(gè)，那么該表空間的碎片狀況就比較嚴(yán)重了，可以嘗試用以下的SQL命令進(jìn)行表空間相鄰碎片或重建某些數(shù)據(jù)庫(kù)對(duì)象。碎片接合的方法:altertablespaceuserscoalesce;selectdistinct(owner)fromdba_tableswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM'selectselectdistinct(owner)fromdba_tableswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM'selectdistinct(owner)fromdba_indexeswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM';說(shuō)明System表空間，同時(shí)應(yīng)selecta.table_name,selecta.table_name,a.next_extent,a.tablespace_namefromall_tablesa,(selecttablespace_name,max(bytes)asbig_chunkfromdba_free_spacegroupbytablespace_name)wheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunkselecta.index_name,a.next_extent,a.tablespace_namefromall_indexesa,(selecttablespace_name,max(bytes)asbig_chunkfromdba_free_spacegroupbytablespace_name)wheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunk;norows說(shuō)明SELECTsubstr(name,1,30SELECTsubstr(name,1,30)name,space_limitASquota,space_usedASused,space_reclaimableASreclaimable,number_of_files ASfiles v$recovery_file_dest說(shuō)明檢查OracleSQL>SELECTname,phyrds,phywrtsFROMV$datafileSQL>SELECTname,phyrds,phywrtsFROMV$datafiledf,V$filestatfsWHEREdf.file#=fs.file#; PHYRDS代表已完成的物理讀次數(shù)，PHYWRTSDBWRIOIO，所以一般可以通過(guò)操作系統(tǒng)的一些命令來(lái)確認(rèn)一IOiostat，sar等SQL>SELECTretries.value/entries.value"RedoLogBufferSQL>SELECTretries.value/entries.value"RedoLogBufferRetryFROMV$sysstatretries,V$sysstatW 'redobufferallocationAnd= 'redoentries';RedoLogBufferRetryRatio說(shuō)明redobufferallocationretries01%，否則要redobufferundoSQL>selectSQL>selectsum(bytes),statusfromdba_undo_extentsgroupbystatus;SUM(BYTES)STATUS766771210616832undoextent可以有三種狀態(tài)expired:已結(jié)束的事務(wù)，undoundo_retentionunexpired:已經(jīng)結(jié)束的事務(wù)，undoundo_retentionundoblockundoundospace.activeextentundosegmentundo TO_CHAR(first_time,'yyyy-mm-dd')DAY,COUNT(*)switch_times,SUM(DECODE(TO_CHAR(first_time,'hh24'),'00',1,0))h00,SUM(DECODE(TO_CHAR(first_time,'hh24'),'01',1,0))h01,SUM(DECODE(first_time,'hh24'),'02',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'03',1,0))h03,SUM(DECODE(first_time,'hh24'),'04',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'05',1,0))h05,SUM(DECODE(first_time,'hh24'),'06',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'07',1,0))h07,SUM(DECODE(first_time,'hh24'),'08',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'09',1,0))h09,SUM(DECODE(first_time,'hh24'),'10',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'11',1,0))h11,SUM(DECODE(first_time,'hh24'),'12',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'13',1,0))h13,SUM(DECODE(first_time,'hh24'),'14',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'15',1,0))h15,SUM(DECODE(first_time,'hh24'),'16',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'17',1,0))h17,SUM(DECODE(first_time,'hh24'),'18',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'19',1,0))h19,SUM(DECODE(first_time,'hh24'),'20',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'21',1,0))h21,SUM(DECODE(first_time,'hh24'),'22',1,0))SUM(DECODE(TO_CHAR(first_time,'hh24'),'23',1,0))FROMv$log_historyWHEREfirst_time>TRUNC(SYSDATE-30)GROUPBYROLLUP(TO_CHAR(first_time,'yyyy-mm-dd'));redofromv$sessionfromv$sessions,v$session_eventseWheres.sid=se.sidAndse.eventnotlike'SQl*Net%'Ands.status='ACTIVE'Ands.usernameisnotSQL>select*(selectevent"waitevent",time_waited"timeround(time_waitedround(time_waited/(selectsum(time_waited)fromv$system_event),4)"%timewaited",total_waits"waits",round(total_waits/(selectsum(total_waits)fromv$system_event),4)"%waited"fromv$system_event wherewait_class!='Idle'orderby2desc)whererownum說(shuō)值%waited代表等待數(shù)據(jù)比，此值越小越好,cp檢查數(shù)據(jù)庫(kù)cpu、I/Otop-10:29:35up73days, 1 loadaverage:0.37,0.38,Tasks:353 2running,351 0 0 1.2%us, 0.1%sy, 0.0%ni,98.8%id, 0.0%wa, 0.0%hi, 0.0%si 16404472ktotal,12887428kused, 3517044kfree, 60796kbuffers 8385920ktotal, 665576kused, 7720344kfree,10358384kcachedPID SHRS%CPU 3049508329m866m861m 7:53.903250108328m1.7g1.7g 2 1:58.383250308329m1.6g1.6g 2 2:06.62說(shuō)明CPU使用率異常，需記錄下該數(shù)值，并將狀態(tài)記為異常。[oracle@racdb1racdb1]$[oracle@racdb1racdb1]$free-0-/+ 10%時(shí)視為異常。[oracle@racdb1 iostat-k1[oracle@racdb1 iostat-k1Linux2.6.32-504.1.3.el6.x86_64()_x86_64_(1 %nice%system 0說(shuō)明cpuIOidleiowaitidle值非常小(20%)iowait值非常大(70%)IO出現(xiàn)問(wèn)題。IO問(wèn)題涉及操作系統(tǒng)，存儲(chǔ)系統(tǒng)，cp負(fù)荷及應(yīng)用系統(tǒng)等一些因素，故當(dāng)發(fā)現(xiàn)問(wèn)題請(qǐng)及[oracle@racdb1racdb1]$[oracle@racdb1racdb1]$21:31:08 2 loadaverage:0.00,0.00,說(shuō)明SQL>SQL>selectspidfromv$processwhereaddrnotin(selectpaddrfromv$session);SQL>SELECTSQL>SELECTa.VALUE+b.VALUElogical_reads,c.VALUEphys_reads,round(100*(1-c.value/(a.value+b.value)),4)hit_ratioFROMv$sysstata,v$sysstatb,v$sysstatcWHEREa.NAME='dbblockgets'ANDb.NAME ='consistentgets'AND ='physicalreads'LOGICAL_READS 90%db_cache_sizeSQL>SQL>selectsum(pinhits)/sum(pins)*100fromv$librarycache;SQL>selectSQL>selectname,valuefromv$sysstatwherenamelikesortssorts(disk)sorts(rows)0disk/(memoty+row)或SQL>SQL>selectname,valuefromv$sysstatwherenamein('redoentries','redobufferredoredobufferallocation1redobufferallocationretries/redoentries1%log_buffer owner='CTAIS2'Andstatus<>'VALID';statusN/Arebuild，如：Sql>alterindexINDEX_NAMErebuildtablespaceTABLESPACE_NAME;SELECTSELECTowner,constraint_name,table_name,constraint_type,statusFROMdba_constraintsWHEREstatus='DISABLE'andSql>alterTableTABLE_NAMEEnableConstraintsSELECTowner,SELECTowner,trigger_name,table_name,statusFROMdba_triggersWHEREstatus=Sql>alterTriggerTRIGGER_NAME /*+rule*/owner,segment_name,segment_type,tablespace_name,TRUNC(BYTES/1024/1024,1)size_mb /*+rule*/owner,segment_name,segment_type,tablespace_name,TRUNC(BYTES/1024/1024,1)size_mbFROMdba_segmentsWHERENOTEXISTS(SELECT'x'FROMdba_indexesWHEREt.owner=ANDt.segment_name=ANDt.segment_typeIN('TABLE','TABLEPARTITION')ANDt.ownerNOTIN('SYS','SYSTEM')ORDERBY5說(shuō)明SELECTsql_textSELECTsql_text"SQL",executions運(yùn)行次數(shù)buffer_getsdecode(executions0,1,4000"響應(yīng)時(shí)間FROMv$sqlWHEREbuffer_gets/decode(executions,0,1,executions)/4000>10ANDexecutions>0;說(shuō)明SELECT*FROM(SELECTPARSING_USER_IDSQL_TEXTFROMSELECT*FROM(SELECTPARSING_USER_IDSQL_TEXTFROMV$SQLAREAORDERBYDISK_READSDESC)WHEREROWNUM<10;SELECTse.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,gram,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changesFROMv$sessionse,v$session_waitst,v$sess_iosi,v$processSELECTse.sid,se.serial#,pr.SPID,se.username,se.status,se.terminal,gram,se.MODULE,se.sql_address,st.event,st.p1text,si.physical_reads,si.block_changesFROMv$sessionse,v$session_waitst,v$sess_iosi,v$processprWHEREst.sid=se.sidANDst.sid=si.sidANDse.PADDR=pr.ADDRANDse.sid>6ANDst.wait_time=0ANDst.eventNOTLIKE'%SQL%'ORDERBYphysical_readsDESC;SELECTP.pidpid,S.sidsid,P.spidspid,S.usernameusername,S.osuserosname,P.serial#S_#,P.terminal,P.programprogram,P.background,S.status,RTRIM(SUBSTR(a.sql_text,1,80))SQLFROMv$processP,v$sessionS,v$sqlareaAWHEREP.addr=s.paddrANDS.sql_address=a.address(+)ANDP.spidLIKE'%&1%';CPUPID對(duì)應(yīng)的SELECTP.pidpid,S.sidsid,P.spidspid,S.usernameusername,S.osuserosname,P.serial#S_#,P.terminal,P.programprogram,P.background,S.status,RTRIM(SUBSTR(a.sql_text,1,80))SQLFROMv$processP,v$sessionS,v$sqlareaAWHEREP.addr=s.paddrANDS.sql_address=a.address(+)ANDP.spidLIKE'%&1%';SELECT'++'||S.usernameusername,RTRIM(REPLACE(a.sql_text,chr(10),''))||';'FROMv$processSELECT'++'||S.usernameusername,RTRIM(REPLACE(a.sql_text,chr(10),''))||';'FROMv$processP,v$sessionS,v$sqlareaAWHEREP.addr=s.paddrANDS.sql_address=a.address(+)ANDP.spidLIKE'%&&1%';select*fromv$sqlwheresql_idin(selectsql_idfromgv$sessionwherepaddrin(selectaddrfromgv$processwhereprogram='ORACLE.EXE(SHAD)'andspid=188));說(shuō)明Entervaluefor1:PID（CPUselecta.sid,spid,status,substr(gram,1,40)prog,a.terminal,client_info,osuser,value/60/100selecta.sid,spid,status,substr(gram,1,40)prog,a.terminal,client_info,osuser,value/60/100fromv$sessiona,v$processb,v$sesstatwherec.statistic#=12andc.sid=a.sidanda.paddr=b.addrorderbyvalueselectdf.tablespace_namename,df.file_name"file",f.phyrds"物理讀次數(shù)",f.phyblkrd"selectdf.tablespace_namename,df.file_name"file",f.phyrds"物理讀次數(shù)",f.phyblkrd"物理讀BLOCKS",f.phywrts"物理寫(xiě)次數(shù)",f.phyblkwrt"BLOCKS"fromv$filestatf,dba_data_filesdfwheref.file#=df.file_idorderby說(shuō)明定期檢查，SELECTse.usernameSELECTse.username,se.sid,se.serial#,se.sql_address,se.machine,gram,su.blocks*8192/1024/1024"UsedSpace(M)",su.segtype,su.contentsFROMv$sessionse,v$sort_usagesuWHERE說(shuō)明多次檢查，session FROMv$locked_objectl,dba_objectso,v$sessionsWHEREl.object_id=o.object_idANDl.session_id=s.sidORDERBYo.object_id,xidusnDESC;altersystemkillsession說(shuō)明多次檢查，及早發(fā)現(xiàn)并報(bào)告鎖與等待的對(duì)象,Oracle數(shù)據(jù)庫(kù)的安全性，包含：檢查系統(tǒng)安全信息，定期修改密碼，總共[root@racdb1[root@racdb1~]#grep-iacceptedJan2114:36:00racdb1sshd[3455]:Acceptedpasswordfororaclefromport56246ssh2說(shuō)明系統(tǒng)安全日志文件的目錄在/var/log下，主要檢查登錄成功或失敗的用戶日志信息。[root@racdb1[root@racdb1~]#grep-iinval/var/log/secure&&grep-ifailedselectproperty_name,property_valuefromdatabase_propertieswhereproperty_namelikeselectfile_name,tablespace_name,bytes,autoextensible,maxbytesfromdba_data_filesorderbyselect,fromv$tablespacet1,v$datafilet2whereselecttablespace_name,file_name,bytesfromdba_data_filesorderbySELECTUPPER(F.TABLESPACE_NAME)"表空間名",D.TOT_GROOTTE_MB"表空間大小(M)",D.TOT_GROOTTE_MBF.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)(SELECTROUND(SUM(DD.BYTES)/(1024*1024),2)TOT_GROOTTE_MBFROMSYS.DBA_DATA_FILESDDGROUPBYDD.TABLESPACE_NAME)WHERED.TABLESPACE_NAME=F.TABLESPACE_NAMEORDERBY1;selecttablespace_name,table_namefromuser_tableswhereselect* all_tableswhere TABLESPACE TABLESPACE"TEST"DATAFILE'/u01/app/oracle/oradata/racdb1/TEST01.dbf'100MAUTOEXTENDONNEXT128MMAXSIZE3000MLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTOSQL>showparameter integerK,8K、16K、32K、64K等幾種大小，ORACLE4194304（由操作系統(tǒng)決定），4194304×DB_BLOCK_SIZE/10244k8K16k32K64kALTERALTERTABLESPACETESTADDDATAFILE'/u01/app/oracle/oradata/racdb1/TEST02.dbf'SIZE100MAUTOEXTENDONNEXT128M MAXSIZE300M;ALTERDATABASEDATAFILE'/u01/app/oracle/oradata/racdb1/TEST01.dbf'RESIZE說(shuō)明CREATEBIGFILETABLESPACE"BIGFILE03"DATAFILE'/u01/app/oracle/oradata/racdb1/bigdata01.dbf'SIZE100MAUTOEXTENDONNEXT128MMAXSIZE40GLOGGINGEXTENTMANAGEMENTCREATEBIGFILETABLESPACE"BIGFILE03"DATAFILE'/u01/app/oracle/oradata/racdb1/bigdata01.dbf'SIZE100MAUTOEXTENDONNEXT128MMAXSIZE40GLOGGINGEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTOSELECTtablespace_name,bigfileFROM說(shuō)明bigfiletablespaceOracle10g的新特性。Bigfiletablespaces14Gblocksdatafile8KBdatablocksbigfiletablespace32TB。因?yàn)锽igfileTablespace不能有多個(gè)文件，必須保證在同一個(gè)磁盤(pán)空間有足夠的容量.BFT(bigfiletablespace)還受到操作系統(tǒng)的文件系統(tǒng)的限制。理論上我們可以創(chuàng)建最大32T(4G*8K)的表空間。經(jīng)過(guò)測(cè)試linux系統(tǒng)不建議超過(guò)2T,大文件表空間（bigfile StorageManagement）或其他邏輯卷管理工具（logicalvolumemanager）配合使用，這些工具應(yīng)該能夠支持動(dòng)態(tài)擴(kuò)展邏輯卷，也能支持striping（數(shù)據(jù)跨磁盤(pán)分布）或RAID。文件系統(tǒng)(塊 單個(gè)文件大小限 文件系統(tǒng)大小限ext2/3(4K) ext4(4K) [root@racdb1~]#tune2fs-lBlock SQL>selectSQL>selectfile_name,tablespace_name,bytes,autoextensible,maxbytesfromdba_data_filesorderbytablespace_name;SQL>shutdown[oracle@racdb1~]$mv/u01/app/oracle/oradata/data02.dbf/u01/app/oracle/oradata/racdb[oracle@racdb1~]$mv/u01/app/oracle/oradata/data01.dbf/u01/app/oracle/oradata/racdb[oracle@racdb1~]$mv/u01/app/oracle/oradata/TEST02.dbf/u01/app/oracle/oradata/racdbSQL>startupSQL>alter renamefile'/u01/app/oracle/oradata/data02.dbf'toSQL>alter renamefile'/u01/app/oracle/oradata/data01.dbf'toSQL>alter renamefile'/u01/app/oracle/oradata/TEST02.dbf'toSQL>alterdatabaseopen;selectselectfile_name,tablespace_name,bytes,autoextensible,maxbytesfromdba_data_filesorderbytablespace_name;啟動(dòng)數(shù)據(jù)庫(kù)到mountalt