unix下的oracle高级管理技巧

[e-mail]35()PGA1550OracleSGA()(%)(2G)694()PGA3061024OracleSGA()(MB)SGA10.11280(%)OracleSGASGA(1G)81128188800(MB)OracleSGASGAI/OSort_area_sizeOnlineredologDBWRI/Odb_block_buffersI/Odb_block_sizeI/OI/OctaspctfreeI/OpctusedctascreatetableasselectI/OSQLhintI/ObitmapindexindexhintFTS(FullTableScan)db_file_multiblock_read_count16819241638488192164096db_file_multiblock_read_countdb_block_sizecheckpoint0Log_checkpoint_timeout512onlinelogfileLog_checkpoint_intervallogswitch301.2./*+append*/insert3.4.1.tablespace2.Readmostlytablespace3.Readonlytablespace4.inserttablespace5.tablespace6.tablespace7.tablespaceextentUNIXORACLEUNIX1.(sharedmemory)2.(semaphore)3.(swap)4.(mountpoint)5.RAMUNIX()SHMMNISHMSEGSHMMINSHMMAXUNIX()SEMMSLSEMMNISEMMNSNFILEMAXUPRCNPROCOSMAXUSERS•ipcs-ma•ipcs-qa•ipcs-sa•ipcrm•sar-mHP-UX11.0kmtune/usr/sbin/kmtune–l–qsemmns/usr/sbin/kmtune|grep–ishmAIXCPUrootlsdev-CAIXrootlsattr–Elsys0SUN:Solaris2.8()Setfastscan=131072Setmaxpgio=65536Setpriority_paging=1SUN:Swap–lswapsysdefpsrinfoprtdiagprtconfCPUdemsgAIX:I/OsmittyaioI/O()TRUEDISK_ASYNCH_IOOracle8()FALSEUSE_ASYNCH_IOOracle7.3AIX:-aI/OrootpstatlslppswaplspslsdevlsattrrootchdevHP-UX:RAMOracle(dbbuffers)JFSRAMHP-UX:HP10.xbufpages025%dbc_min_pct1550%dbc_max_pct0bufpagesHP-UX:HP-UX,1024M79M1.9GHPORACLE8Mmaxssiz4Mmaxtsiz64Mmaxdsiz:I/OrawveritasQuickI/OI/OSOLARISrawJFSI/OAIXrawHP-UX:SGASGASGAtruefalse8i_use_ismuse_ismSOLARISlock_sgaAIX/Linux/MSw2klock_sgaHP-UXOraclefileblockI/Ops:oracleps-eopid,user,vsz,pcpu,nice|grepora|sort-rn+3|head-20vsz()RAM4du:du–sk*|sort-rndu–sk.find:7:find/home/oracle/admin/ora8i/bdump/*.trc–mtime+7–execrm{}\;14:find.–mtime–14-printfind:(v$process):find.–print|xargsgrepv\$processfind:1MsizeK:find.–size+1024–print|xargs–irm\;CTAS:createtableasselect1.(block)2.3.CTAS4.5.CTAS:createtablenewparallel(degree2)asselect*fromoldorderbyenumber;createtablenewasselect/*+index(old,enumber)*/*fromold;Movetablealtertableempmovetablespaceusers;moveindexrebuildBlock(CTAS)FTSblock/9iblocksize1.FTSblock2.db_recycle_cache_sizeblocksizecache3.4.blocksize5.blocksize6.blocksizeUNIXawksortOWNER:rootls-al|awk'{print$3}'|sort-ucatcut:#!/bin/kshforuserin`cat/etc/passwd|cut-d':'-f1`doecho********************************echoUNIXcommandsecurityauditfor$userecho********************************cat/export/home/$user/.sh_historydonehostnamecutShellSQL*PLUS:$ORACLE_HOME/bin/sqlplus-Ssystem/oracle!Spool`hostname`_`date+%Y_%m_%d_%H_%M_%S`.lstSelectcount(*)fromdba_data_files;Spooloffps-efgrep-iOracle:ps-ef|grep-ioracle|awk'{print$2}'dedicate:ps-ef|grep$ORACLE_SID|grep-vgrep|grep-vora_|wc-lORACLEPS1=[`hostname`*\${ORACLE_SID}-\${PWD}]PS1=[\u@\h\${PWD}]KillORACLEoracleps-ef|grepora_|grep-vgrep|awk'{print$2}'|-execkill-9{}\;5•find/arch/arch_prod*.arc-ctime+5-execrm{}\;•ctime•exec0•{}•okexecSHELLSQL*Plus•#!/bin/ksh•#First,wemustsettheenvironment....•ORACLE_SID=mysid;exportORACLE_SID•ORACLE_HOME=`cat/etc/oratab|grep^$ORACLE_SID:|cut-f2-d':'`•exportORACLE_HOME•PATH=$ORACLE_HOME/bin:$PATH;exportPATH•$ORACLE_HOME/bin/sqlplussystem/passwd!•SELECT*FROMv\$database;•@/home/oracle/sql/longscript.sql•exit•!•nohuprun_sql.kshlogfile.lst2&1&•nohup.•run_sql.kshUnixSHELL.•logfile.lst.•2&1.•2,1.•&(&).•jobs•jobs-l•[1]+Runningnohuptest.shtest.log&•fg•fg%1fg•bg•bg%1•stop(Ctrl+Z)•stop%1export1.mkfifoexp.p2.exp.....file=exp.p&3.compressexp.pexport.dmp.Z•––IO–export–import1.mkfifoimp.p2.uncompressexport.dmp.Zimp.p&3.imp.....file=imp.p•––IO–import–pwdcat(mvinit.ora$ORACLE_HOME/dbs)mv(cpinit.ora$ORACLE_HOME/dbs)cp(mkdirtemp)mkdir(rmcore)rm(tail-falert_test.log)tail(head-n1alert_test.log)head(ls-l)(ls–lt)ls()more(find.-namealert_test.log)findgrep(ps-ef|greporacle)psSHELL(env|grepORACLE_SID)env(viinittest.ora)vi(wc-lalert_test.lo)wc(tarcvfpbackup.tarbackup)tar(ln-stest.shoracle_test.sh)ln(compressbackup.tar)compress(chownoracle:dbatest.sh)chown(chmodu+xtest.sh)chmodtouch(ls|xargs-iwc-l)xargs(fileoracle)file(echohello|cut-c1-3)cut(ps-ef|sort+6)6sort(rmdirtemp)rmdir(aliasrm=rm-i)alias(ulimit-a)ulimit(kill4984)kill(HP-UXbdf)df(du-sa)du(ps-ef|awk'{print$2}')awk(Aixtopas/Solarisprstat)topI/O(iostat15)iostatIPC(ipcrm-s0001)ipcrm(vmstat15)vmstatCPU(sar15)sar(uname-a)uname(uptime)uptimeIPC(ipcs-m)ipcs(rcpalert_test.logctais_stdy:/db/oracle)rcp(rshctais_app_service)rsh(fuseralert_test.log)fuser(date+%Y%m%d)dateUNIXCPUlsdev-C|grepProcessor|wc-lAIXpsrinfo-v|grepStatusofprocessor|wc-lSOLARIScat/proc/cpuinfo|grepprocessor|wc-lLINUXioscan–Cprocessor|grepprocessor|wc-lHP-UXCPURAMlsdev-C|grepmemAIXprtconf|grep-imemSOLARISfreeLINUXswapinfo-tmHP-UXCPUsemmnsrootipcs–sa|greporaclerootipcrm–s384HP-UX:rootgrep–ierror/var/adm/syslog/syslog.log|moreIBMAIX:rooterrpt–a|moreUNIXvmstattop(topas)sariostatglancevmstat:CPUvmstat:I/OCPUwa(IBM)CPUid()CPUsy(CPU)CPUus(CPU)RAMpi()CPUr()CPUCPUrCPU2•CPU••UNIXnice/reniceatbatchcrontoploadaverage:0.07,0.02,0.0015151CPUvmstatORACLEv$processSPID(PID):Selecta.sid,a.serial#,b.spidprocessfromv$sessiona,v$processbwherea.usernameisnotNULLanda.paddr=b