2022oracle數(shù)據(jù)庫巡檢內(nèi)容

\horacle?檢查\h數(shù)據(jù)庫\hOracleOracleOracle?OracleSQL>selectinstance_name,host_name,startup_time,status,database_statusfromINSTANCE_NAMEHOST_NAMESTARTUP_TIME 2009-5-7其中“STATUS”O(jiān)racle“OPEN”；“DATABASE_STATUS”O(jiān)racleSQL>SQL>selectname,log_mode,open_modefrom ARCHIVELOGREAD其中“LOG_MODE”O(jiān)racle“ARCHIVELOG”表示數(shù)據(jù)庫運行在歸檔模式下，“NOARCHIVELOG”表示?Oracle$ps$ps-ef|grepora_|grep-vgrep&&ps-ef|grepora_|grep-vgrep|wc 10May07 00:01:02 10May07 00:00:22 10May07 00:00:00 10May07 00:03:20 10May07 00:04:29 10May07 00:10:31 10May07 00:03:4510May0700:00:0010May0700:01:2410May0700:06:1710May0700:07:2610May0700:00:0010May0700:00:0010May0700:01:4010Jul2000:00:001005:0900:00:33 10May0700:00:00 10May0700:00:28 10May0700:00:29OracleOracle“ora_lgwr_Oracle“ora_smon_Oracle“ora_pmon_Oracle“ora_arc0_Oracle“ora_ckpt_Oracle\h恢復(fù)的進程，輸出顯示為：“ora_reco_?OracleLSNRCTLforLinux:Version.0-Productionon23-JUL-2009Copyright(c)1991,\h2005,Oracle.AllrightsConnectingto(ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))STATUSoftheLISTENER TNSLSNRforLinux:Version.0-ProductionStartDate 07-MAY-200909:35:52 77days4hr.36min.0Trace ON:LocalOS ListenerParameterFile/data/oracle/product/10.2.0/network/admin/listener.oraListenerLogFile ListeningEndpointsSummary...ServicesService"CKDB"has1Instance"CKDB",statusREADY,has1handler(s)forthisServiceService"CKDBXDB"has1Instance"CKDB",statusREADY,has1handler(s)forthisservice...Service"CKDB_XPT"has1instance(s).Instance"CKDB",statusREADY,has1handler(s)forthisThecommandcompleted“ServicesSummary”O(jiān)racle“CKDB”[oracle@AS14[oracle@AS14~]$ps-ef|greplsn|grep-v 10May0700:01:17/data/oracle/product/10.2.0/bin/tnslsnrLISTENER?oracleOracleOracleRootOracleemail，?##cat/var/log/messages|grepOracle?oracle[oracle@AS14[oracle@AS14~]$cat/data/oracle/admin/CKDB/bdump/alert_CKDB.log|grepora-[oracle@AS14~]$cat/data/oracle/admin/CKDB/bdump/alert_CKDB.log|greperr[oracle@AS14~]$cat/data/oracle/admin/CKDB/bdump/alert_CKDB.log|grepORA-TRCOraclebugListener?Oracle$ls$ORACLE_BASE/admin/CKDB/udump/*.trc|wcOracle?RootOracle#tail#tail–n200#tail–n200Oracle?OracleOracleOracleOracleOracleOracleOracleOracle?OracleSQL>SQL>selectstatus,namefromSTATUS3（3）的記錄，“STATUS”?OracleSQL>SQL>selectgroup#,status,type,memberfromGROUP#STATUS ONLINEONLINEONLINEONLINEONLINEONLINE6rows3（3）記錄，“STATUS”應(yīng)該為非“INVALID”，非“DELETED”。注：“STATUS”顯示為空表?OracleSQL>selecttablespace_name,statusfrom STATUSONLINE?OracleSQL>SQL>selectname,statusfrom 輸出結(jié)果中“STATUS”應(yīng)該都為“ONLINE”SQL>SQL>selectfile_name,statusfrom輸出結(jié)果中“STATUS”應(yīng)該都為“AVAILABLE”?sql>selectsql>selectowner,object_name,object_typefromdba_objectswherestatus!='VALID'andowner!='SYS'andnorowsSELECTSELECTowner,object_name,object_typeFROMdba_objectsWHEREstatus=?SQL>SQL>selectsegment_name,statusfrom11rows輸出結(jié)果中所有回滾段的“STATUS”應(yīng)該為“ONLINE”?OracleOracleOracle磁盤空間，檢查Oracle各個表空間使用情況，檢查一些擴展異常的對象，檢查system表空間內(nèi)的內(nèi)容，檢查對象的下一擴展與表?OracleSQL>selectresource_name,max_utilization,initial_allocation,limit_valuefromv$resource_limit; MAX_UTILIZATIONINITIAL_ALLOCAT 5 MAX_UTILIZATIONINITIAL_ALLOCAT1 22rows若，則表明與相關(guān)的來修改。?SQL>SQL>selectcount(*)fromselectsid,serial#,username,program,machine,statusfrom xz15saledbxz15saledbxz15saledbxz15saledbxz15saledbxz15saledb oracle@xz15saledb xz15saledb oracle@xz15saledb xz15saledb oracle@xz15saledb xz15saledb oracle@xz15saledb xz15saledb ZKAccPrtInv_svr@xz15tuxedo2(TNSV1-V3)xz15tuxedo2INACTIVE1348876 upload@xz15saleap(TNSV1-V3)xz15saleapINACTIVE1720405 AccCreateRpt@xz15tuxedo1(TNSV1-V3)xz15tuxedo1INACTIVE2012895 OweScanSvr@xz15billdb(TNSV1-V3)xz15billdbINACTIVE其中：SID(session)IDSERIALSIDUSERNAMEPROGRAMSTATUSACTIVEINACTIVEaltersystemkillsession\hDBA要手工斷開某個會話，則執(zhí)行：（sessionsidspid,ps–ef|grepspidnospidORACLEaltersystemkillsessionSID110(USERNAMEOracle?檢查系統(tǒng)[oracle@AS14[oracle@AS14~]$df-SizeUsedAvailUse%Mounted9.7G3.9G5.4G42%479M16M438M4%49G19G28G41% 01014M0%?SQL>SQL>selectf.tablespace_name,a.total,f.free,round((f.free/a.total)*100)"%(selecttablespace_name,sum(bytes/(1024*1024))totalfromdba_data_filesgroupbytablespace_name)a,(selecttablespace_name,round(sum(bytes/(1024*1024)))freefromdba_free_spacegroupbytablespace_name)fWHEREa.tablespace_name=f.tablespace_name(+)orderby"% % 3939rows如果空閑率%Free10%以上（10%），則注意要增加數(shù)據(jù)文件來擴展表空間而不要是用數(shù)據(jù)文件的自動擴展功能。請不要對表空間增加過多的數(shù)據(jù)文件，增加數(shù)據(jù)文件的原則是每個數(shù)據(jù)文件大小為2G或者4G，自動擴展的最大限制在8G。?sql>selectsql>selectSegment_Name,Segment_Type,TableSpace_Name,(Extents/Max_extents)*100PercentFromWhereMax_Extents!=0and(Extents/Max_extents)*100>=95orderByPercent;norows?systemselectdistinct(owner)fromdba_tableswhereselectdistinct(owner)fromdba_tableswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM'selectdistinct(owner)fromdba_indexeswheretablespace_name='SYSTEM'andowner!='SYS'andowner!='SYSTEM';norowssystemsystemsys果相關(guān)請把這些對象移到非System表空間，同時應(yīng)該檢查這些對象屬主的缺省表空間值。?sql>selectsql>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,fromfromall_indexes(selecttablespace_name,max(bytes)asbig_chunkfromdba_free_spacegroupbytablespace_name)wheref.tablespace_name=a.tablespace_nameanda.next_extent>f.big_chunk;norows?Oracle數(shù)據(jù)庫\h備份Oraclebackuporacle戶的email，總共三個部分。?假設(shè)：備份的臨時目錄為/backup/hotbakup2009722#cat#cat/backup/hotbackup/hotbackup-09-7-22.log|grep–ihotbackup-月份-日期-年份.log，在備份的臨時目錄下面。如果文件中存在“ERROR:”，則表明備份沒有成?backup#ls#ls–ltbackup?oracle#tail#tail–n300OraclecroncronEmailOracleOracleemailORA-，Error，F(xiàn)ailed?OracleOraclecpu、I/O、內(nèi)存性能，查看是?setsetpagessetlinescoleventforselectselectsid,event,p1,p2,p3,WAIT_TIME,SECONDS_IN_WAITfromv$session_waitwhereeventnotlike'SQL%'andeventlikelatchfree，enqueue，bufferbusywaits，dbfilesequentialread，dbfilescattered?DiskRead\hSQLSQL>SELECTSQL>SELECTSQL_TEXTFROM(SELECT*FROMV$SQLAREAORDERBYWHEREROWNUM<=5?SELECT*FROM(SELECTPARSING_USER_IDSELECT*FROM(SELECTPARSING_USER_IDSQL_TEXTFROMV$SQLAREAORDERBYDISK_READSWHEREWHEREROWNUM<10?5SELECTSELECT*FROM(SELECT*FROMV$SYSTEM_EVENTWHEREEVENTNOTLIKE'SQL%'ORDERBYTOTAL_WAITSWHERE?COLUMNCOLUMNUSERNAMEFORMATA12COLUMNOPNAMEFORMATA16COLUMNPROGRESSFORMATA8SELECTUSERNAME,SID,OPNAME,ROUND(SOFAR*100/TOTALWORK,0)||'%'ASPROGRESS,TIME_REMAINING,SQL_TEXTFROMV$SESSION_LONGOPS,V$SQLWHERETIME_REMAINING<>0ANDSQL_ADDRESS=ADDRESSANDSQL_HASH_VALUE=?CPUSETLINE240SETSETLINE240SETVERIFYCOLUMNSIDFORMAT999COLUMNPIDFORMAT999COLUMNS_#FORMATCOLUMNUSERNAMEFORMATA9HEADING"ORAUSER"COLUMNPROGRAMFORMATA29COLUMNSQLFORMATCOLUMNOSNAMEFORMATA9HEADING"OSSELECTP.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$PROCESSV$PROCESSP,V$SESSIONS,V$SQLAREAAWHEREP.ADDR=S.PADDRANDS.SQL_ADDRESS=A.ADDRESS(+)P.SPIDLIKE?SQL>SQL>SELECTsegment_nametable_name,COUNT(*)extentsFROMdba_segmentsWHEREownerNOTIN('SYS','SYSTEM')GROUPBYsegment_nameHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMdba_segmentsGROUPBY?I/OSQL>SELECTSQL>SELECTDF.TABLESPACE_NAMENAME,DF.FILE_NAME"FILE",F.PHYRDSPYR,F.PHYBLKRDPBR,F.PHYWRTSPYW,F.PHYBLKWRTPBWFROMV$FILESTATF,DBA_DATA_FILESDFWHEREF.FILE#=DF.FILE_IDORDERBY?I/OSQL>SELECTSQL>SELECTSUBSTR(A.FILE#,1,2)"#",SUBSTR(A.NAME,1,30)"NAME",A.STATUS,A.BYTES,B.PHYRDS,B.PHYWRTSV$DATAFILEA,V$FILESTATBWHEREA.FILE#=colsidfor999999colsidfor999999colusernamefora10colschemanamefora10colosuserfora16colmachinefora16colterminalfora20colownerfora10colobject_nameforcolcolobject_typeforselectsid,serial#,username,SCHEMANAME,osuser,MACHINE,fromdba_objectso,v$locked_objectl,v$sessionswhereo.object_id=l.object_idandoraclekillalteraltersystemkillsessionkill#>kill#>kill-9cpu、I/OcpuIOvmstat,iostat,sar,topCPU[root@sale8[root@sale8~]#top-10:29:35up73days,19:54,1user,loadaverage:0.37,0.38,Tasks:353total,2running,351sleeping,0stopped,0Cpu(s):1.2%us,0.1%sy,0.0%ni,98.8%id,0.0%wa,0.0%hi,0.0%siMem:16404472ktotal,12887428kused,3517044kfree, 60796kbuffersSwap:8385920ktotal,665576kused,7720344kfree,10358384kcachedPID PRNIVIRTRESSHRS%CPU TIME+30495 1508329m866m861mR105.47:53.9032501 1508328m1.7g1.7g 210.61:58.383250332503 1508329m1.6g1.6g 210.22:06.62cpu10%CPU##free- -/+I/O##iostat-k1Linux2.6.9-22.ELsmp avg-cpu:%user %sys%iowait 0.36 50.2594483478avg-cpu:%user %sys%iowait 0.00 cpuIO12:08:37up162days,23:33,15users,loadaverage:0.01,0.15,32.5selectselectspidfromv$processwhereaddrnotin(selectpaddrfromSql>selectSql>selecttable_name,num_rows,chain_cntFromdba_tablesWherewner='CTAIS2'Andlongrawchained_rows../rdbms/admin/utlchain.sqlSql>analyze\htabletablenamelistchainedrows;中如:Sql>createtableaaasselecta.*fromsb_zsxxa,chained_rowsbwherea.rowid=b.head_rowidandsql>deletefromsb_zsxxwhererowidin(selecthead_rowidfromchained_rowswheretable_name='SB_ZSXX');sql>insertintosb_zsxxselect*fromchained_rowwheretable_name='SB_ZSXX';OracleCost-Based-Optimizer出正確的explainplan。在以下情況更需要進行統(tǒng)計信息的更新：Sql>Selecttable_name,num_rows,last_analyzedFromuser_tableswheretable_name='DJ_NSRXX'sql>selectcount(*)fromDJ_NSRXX如num_rows和count(*)Sql>execsys.dbms_stats.gather_schema_stats(ownname=>'CTAIS2',cascade=>TRUE,degree=>SQL>SQL>SELECTa.VALUE+b.VALUEc.VALUEc.VALUEround(100*(1-c.value/(a.value+b.value)),4)hit_ratioFROMv$sysstata,v$sysstatb,v$sysstatcWHEREa.NAME='dbblockANDb.NAME='consistentgets'ANDc.NAME='physicalreads'LOGICAL_READSPHYS_READS1273645705 SQL>SQL>selectsum(pinhits)/sum(pins)*100from95%sharedpoolSQL>SQL>selectname,valuefromv$sysstatwherenamelikesortssortssortssortsSQL>SQL>selectname,valuefromv$sysstatwherenamein('redoentries','redobufferallocationredoredobufferallocationredobufferallocationretries/redoentries1%log_buffer?Oracle?[root@rac2~]#[root@rac2~]#grep-iacceptedJan808:44:43rac2sshd[29559]:Acceptedpasswordforrootfrom::ffff:port1119[root@rac2[root@rac2~]#grep-iinval/var/log/secure&&grep-ifailed/var/log/secureJan910:30:44rac2sshd[3071]:Invaliduserydbuserfrom::ffff:Jan910:30:56rac2sshd[3071]:Failedpasswordforinvaliduserydbuserfrom::ffff:port36005JanJan910:30:56rac2sshd[3071]:Failedpasswordforinvaliduserydbuserfrom::ffff:port36005Jan1022:44:38rac2sshd[21611]:Failedpasswordforrootfrom::ffff:port1723?\h\h數(shù)據(jù)庫管理員用戶cat/etc/passwd，在列出來的用戶中查看是否存在已經(jīng)不再使用的或是陌生的帳號。若存在，則記錄為Sql>alterSql>alteruse