oracle dba手記1chapter2-10rac診斷案例一則

1、RAC環(huán)RAC環(huán)案例一現(xiàn)任云和恩墨 CTO， Oracle 數(shù)據(jù)庫管理版版2004 Oracle數(shù)據(jù)庫性能優(yōu)化一書，年被 Oracle 公司授予 Oracle ACE Oracle 相積累了 1500 多篇 Oracle 相關(guān)的 技術(shù)文章。個(gè)人技術(shù)博客u*RAC W- RAC5*b5-4.alertu*RAC W- RAC5*b5-4.alertM5i/Y+for to-/RAC LRAC . rman rman -4h9*-4 ssh 1 qK alertM*.waiting for to leave M RAC tb5-4 DOWN 4t5 +1a2%G2.alertM5Ko2-BFriJ

2、ul2416:25:09Reconfigurationstarted(oldinc4,newinc6) List of nodes:1GlobalResourceDirectory*deadinstancedetected0invalid=CommunicationchannelsMasterbroadcastedresourcehashvalueNon-localsblockscleanedFriJul2416:25:090:6GCSshadowscancelled,0FriJul2416:25:091:14GCSshadowscancelled,1closed Set master nod

3、e infoSubmittedallremote-enqueuerequests Dwn-cvts replayed, VALBLKs dubious All grantable enqueues grantedtSMONtostart1stpassIR Fri Jul 24 16:25:11 2009Instancerecovery:lookingfordeadthreads Fri Jul 24 16:25:11 2009Beginninginstancerecoveryof1threads Fri Jul 24 16:25:12 20090:378032GCSshadowstravers

4、ed,0FriJul2416:25:121:381728GCSshadowstraversed,0FriJul2416:25:12SubmittedallGCSremote-cacherequests Fix write in gcs resourReconfigurationFriJul2416:25:14parallelrecoverystartedwith7pro Fri Jul 24 16:25:15 2009StartedredoFriJul2416:25:15Completedredo3990redoblocksread,986datablocksneedFriJul2416:25

5、:15MonJul2715:52:14Waiting for instantoleave: u*RAC W-StartedredoapplicationThread1:logseq1678,blockFriJul2416:25:15Recoveryofu*RAC W-StartedredoapplicationThread1:logseq1678,blockFriJul2416:25:15RecoveryofOnlineRedoLog:Thread1Group1Seq1678Readingmem0 Mem# 0: /dev/vx/rdsk/datavg/newtrade_redo1_1_1_1

6、gMem#1:FriJul2416:25:15Completedredoapplication Fri Jul 24 16:25:16 2009CompletedinstancerecoveryThread1:logseq1678,block576679,scn862datablocksread,1014datablockswritten,3990redoblocksread Switch log for thread 1 to sequence 1679MEu 1 tuQ9-4ca(0*Y% Oracle alert YNT?Ma Y:o DOWN ?o* 1 u* 1.W 1 :uRoq%

7、2 alert M5YI/ MonJul2715:20:422009MMNL absent for 1202 secs; Foregrounds takinMon Jul 27 15:37:40 2009MMNLabsentfor1599secs;ForegroundsMonJul2715:48:19IPCSendtimeoutdetected.Sender:Receiver:inst1binc27438MonJul2715:49:24IPCSendtimeoutdetected.Sender:Receiver:inst1binc27438MonJul2715:49:31IPCSendtime

8、outdetected.Sender:Receiver:inst1binc27438MonJul2715:49:58IPCSendtimeoutdetected.Sender:Receiver:inst1binc27438MonJul2715:49:59IPCSendtimeoutto0.2inc12formsgtype32fromo Mon Jul 27 15:49:59 2009Communicationsreconfiguration:instance_number1 Mon Jul 27 15:50:15 2009IPCSendtimeoutto0.2inc12formsgtype32

9、fromMonJul2716:07:35Waitingfor1toMonJul2716:07:55Waitingfor1toMonJul2716:08:15Waitingfor1tou*RAC W-IPCSendtimeoutdetectedwaitingfortou*RAC W-IPCSendtimeoutdetectedwaitingforto leave YYK n*zi 1 .%? 1 * 2 1 6f4* 2.svrctl a 1.bash-3.00$srvctlstopinst-dnewtrade-i-*1.W 1?a1.?1 alert M*Y(+MonJul2715:05:00

10、GES:Potentialblocker=3815)onresourceCI-0000000A-enqueue info in file /data/oracle/admin/newtrade/bdump/newtrade1_lmd0_3704.trc and DIAG MonJul2715:14:59kkjcre1p:unabletospawnjobqslaveMonJul2715:14:59sErrorsinfile/data/oracle/admin/newtrade/bdump/newtrade1_cjq0_3879.trc: Mon Jul 27 15:15:33 2009WARNI

11、NG:inboundconnectiontimedout(ORA-MonJul2715:24:44GES:Potentialblocker=3815)onresourceCI-0000000A-MonJul2715:25:10GES:Potentialblocker=3815)onresourceCI-00000001-enqueue info in file /data/oracle/admin/newtrade/bdump/newtrade1_lmd0_3704.trc and DIAG MonJul2715:33:07sstartupfailed,errorMonJul2715:33:0

12、7ErrorsinfileORA-27300:OSsystemdependentoperation:forkfailedwithus:ORA-27301:OSfailuremessage:ResourcetemporarilyORA-27302:failureoccurredat:MonJul2715:57:04WARNING:inboundconnectiontimedout(ORA-3136) Mon Jul 27 15:57:32 2009kkjcre1p:unabletospawnjobqslaveMonJul2715:57:32sErrorsinfile/data/oracle/ad

13、min/newtrade/bdump/newtrade1_cjq0_3879.trc: Mon Jul 27 15:57:45 2009WARNING:inboundconnectiontimedout(ORA-3136) Mon Jul 27 15:58:33 2009WARNING:inboundconnectiontimedout(ORA-MonJul2716:05:55WARNING:inboundconnectiontimedout(ORA-3136) Mon Jul 27 16:06:22 2009ErrorsinfileORA-ernalerrorcode,arguments:2

14、103,1,0,1,900,u*RAC W-MonJul2716:06:24ErrorsinfileORA-ernalerrorcode,u*RAC W-MonJul2716:06:24ErrorsinfileORA-ernalerrorcode,arguments:2103,1,0,1,900,MonJul2716:06:54USER:terminatinginstanceduetoerrorMonJul2716:06:54ErrorsinfileORA-00481:LMONsterminatedwithMonJul2716:06:54ErrorsinfileORA-00481:LMONst

15、erminatedwithMonJul2716:06:54Errorsinfile/data/oracle/admin/newtrade/udump/newtrade1_ora_16926.trc: ORA-00481: LMON MonJul2716:06:54SystemedumpismadeforlocalMonJul2716:06:56ErrorsinfileORA-00481:LMONsterminatedwithSystemedumpedtotracefileMonJul2716:07:05DumpsystemeforlocalinstanceMonJul2716:07:05Tra

16、ceisperformingMonJul2716:07:08Shuttingdowninstance(abort) License high water mark = 104 Mon Jul 27 16:07:09 2009InstanceterminatedbyUSER, Mon Jul 27 16:07:13 2009Instanceterminatedby=G5i0RORA-600 *f0iORA-3136 *10.2.0.3 .*Rqi4-BiBuw Q001 $PZ98 $JOB CJQPSPuiORA-27300 LSMoIPCSendtimeoutdetected% oORA-6

17、00(2103)%iORA-00481*LMONi ORA-3136 /d CJQ bash-3.00$moreu*RAC W-OracleDatabase10griseEditionRelease10.2.0.3.0-64bitWiththePartitioning,RealApplicationClusters,OLAPandDataMiningORACLE_HOME=SystemNodename: Generic_127111-:Instancename:Redothreadmountedbythisinstance:OracleUnixsnumber:3879,image:oracle

18、newtrade1*u*RAC W-OracleDatabase10griseEditionRelease10.2.0.3.0-64bitWiththePartitioning,RealApplicationClusters,OLAPandDataMiningORACLE_HOME=SystemNodename: Generic_127111-:Instancename:Redothreadmountedbythisinstance:OracleUnixsnumber:3879,image:oraclenewtrade1*2009-07-27*SERVICENAME:(SYS$BACKGROU

19、ND)2009-07-27*ID:(540.1)2009-07-27WaitedforsJ000tobespawnedfor60*2009-07-27WaitedforsJ000tobespawnedfor111*2009-07-27*2009-07-27WaitedforsJ000tobespawnedfor60*2009-07-27diagnosticinformationfor=loadavg:1.322.43swapinfo:free_mem=162.41Mrsv=alloc=17453.59Mavail=22820.93swap_free=FS PC PRI?STIME15:53:1

20、7TIME0S oracle 6781 1 6781: 0 000000010405b53cskgmidrealm( 000000010405af38 skgmlocate (+0000000104056db4skgmattache4a0,106527230,e4a0,e4c8,3,0)+e4c8,29815c08,0,e4a0,e4c8,29815c08,e4c8)+00000001011012f8ksmlsge(0,106400,0,106527000,106537000,106400)+00000001025153ccopirip10652a720)+f,1056db000,f018,0

21、00000010033f774rv(106537460,0,32,106534198,32,0)+0000000100339c10sou2of4a8,32,f4d0,105e0f000,105e0f)+00000001002fbfccopimai_real00000001002fbdf8mainff79d00140)+f5a8,0,0,247d6fc,14800)+f6b8,00000001002fbd1c_start(0,0,0,0,0,0)+*2009-07-27*2009-07-27Killings6781): (reason=x4.andthesisstillaliveafter*20

22、09-07-27*2009-07-27u*RAC W-KCL:caughterror481duringcrlock*2009-07-27ORA-00604:erroroccurredatrecursiveSQLlevelORA-00481:LMONsterminatedwithYju*RAC W-KCL:caughterror481duringcrlock*2009-07-27ORA-00604:erroroccurredatrecursiveSQLlevelORA-00481:LMONsterminatedwithYj spawned i: ORA-bash-3.00$more*SERVIC

23、ENAME:(SYS$BACKGROUND)2009-07-27*RedoID:(533.1)2009-07-27cntperformingstandby*2009-07-2711:03:13.46865194LoggedontostandbyCntlogonandsecuritynegotiation*2009-07-27TIMEOUTONCONTROLFILEmode=S,type=0,wait=1,*2009-07-27ORA-ernalorfatalernalerrorcode,arguments:2103,1,0,1,900,-CallStackTrace-argumentvalue

24、sin(?meansdubious000000840000000000?B44C7F406CA876A8 ORA-600 TIMEOUT ON CONTROL FILE ENQUEUE M?bHpMk5k5900 ORA-%4:07*-4 d psp bash-3.00$more*2009-07-27Dumpdiagnosticsfors11183whichdidnotstartafter120(spawn_time:xD42371now:xD5FF06*2009-07-27diagnosticinformationfor=loadavg:0.981.52swapinfo:free_mem=2

25、50.75Mrsv=alloc=17360.75Mavail=22774.20swap_free=FS 0S P11183 C0PRINI ADDR TIME39 ?1590514:48:2311183: u*RAC W-0000000000000000?(0,0,0,0,0,*2009-07-27Killings11183):didnotstartafter120.andthesisstillaliveafterKilleds11183isstillaliveafter62*u*RAC W-0000000000000000?(0,0,0,0,0,*2009-07-27Killings1118

26、3):didnotstartafter120.andthesisstillaliveafterKilleds11183isstillaliveafter62*2009-07-27Killings17027):requestercancelled.andthesisstillaliveafter*2009-07-27Dumpdiagnosticsfors20623whichdidnotstartafter120(spawn_time:xDAA953now:xDC83AF*2009-07-27diagnosticinformationfor=loadavg:1.321.07swapinfo:fre

27、e_mem=197.38Mrsv=alloc=17448.21Mavail=22713.61swap_free=FS 0S P20623 C0PRINI ADDR TIME40 20 1584914:55:34pstack:cannotexamine20623:sis*2009-07-27Killings20623):didnotstartafter120.andthesisstillaliveafterKilleds20623isstillaliveafter168*2009-07-27Dumpdiagnosticsfors6781whichdidnotstartafter120(spawn_time:x11009ADnow:x111E13E*2009-07-27diagnosticinformationfor=loadavg:1.021.95swapinfo:free_mem=154.73Mrsv=alloc=17466.89Mavail=22809.92swap