Oracle调优与深入之灌水篇

　　 
　　之一 ：EVENT: CONTROLF
　　
　　EVENT: CONTROLF　 - Obtaining & Interpreting ControlFile
　　Dumps
　　------------------------------------------------------------------------------
　　

　　ALTER SESSION SET EVENTS 'immediate trace name controlf level 10';
　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
　　
　　It is also sometimes useful to: "ALTER DATABASE BACKUP CONTROLFILE TO TRACE".
　　
　　(From Oracle 7.1.x)
　　
　　*DUMP OF CONTROL FILES, Seq # 202417 = 316b1
　　* FILE HEADER:
　　Software vsn=117502976=700f400, Compatibility Vsn=117477376=7009000
　　Db Id=4188500840=f9a77368, Db Name='ECI_RPT'
　　Control Seq=202417=316b1, File size=1238=4d6
　　File Number=0, Blksiz=512, File Type=4
　　==>　　Control Seq　Control file sequence number
　　==>　　File Number　No concept of file no for control files
　　==>　File Size　This files Size (blocks)
　　==>　Blksiz　　This files block size
　　==>　File Type　(See kclearcase/" target="_blank" >cc.h)
　　KCCTYPCF　　1　 * control file *
　　KCCTYPRL　　2　 * redo log file *
　　KCCTYPDF　　3　 * vanilla db data file *
　　KCCTYPBC　　4　 * backup control file *
　　
　　* DATABASE ENTRY:
　　(offset = 0x200, size = 144, max = 1, hi = 1)
　　DF Version: creation=0x700f400 compatable=0x700b000, Date　05/27/95 03:34:38
　　DB Name "ECI_RPT"
　　Database flags = 0x00000147
　　==>　　　 ^^^^^^^^^^^^^^^^^^^(kcc.h)
　　KCCDIMRE 0x0001　* whether media recovery enabled *
　　KCCDICKD 0x0002　* if dictionary must be checked with control file *
　　KCCDIRLR 0x0004　* DB OPEN RESETLOGS required *
　　KCCDIJNK 0x0008　* junk value from beta - do not reuse this flag *
　　KCCDIMRC 0x0010　* was/is last mounted READ_COMPATIBLE *
　　KCCDICNV 0x0020　* control file was just created by convert from v6 *
　　KCCDIIRA 0x0040　* Incomplete Recovery Allowed when resetting logs *
　　KCCDIDBM 0x0080　* K_MLS - on next open DataBase Mac mode *
　　KCCDICCF 0x0100　* Controlfile was created with CREATE CONTROFILE *
　　
　　Incmplt recovery scn: 0.06d3ac0c Resetlogs scn: 0.02811906 count: 0xc99f490
　　==> ^^^ Last Incomplete recovery　　 ^^^ Last Resetlogs
　　Redo Version: creation=0x700f400 compatable=0x700c000
　　#Data files = 204, #Online files = 1
　　==> ^^^ Can derive no of OFFLINE files from these figures.
　　Database checkpoint: Thread=0 scn: 0.00000000
　　Threads: #Enabled=1, #Open=0, Head=0, Tail=0
　　enabled　threads:　01000000 00000000 00000000 00000000 00000000 00000000 ...
　　Max log members = 4, Max data members = 1
　　Log hist = 0, Arch list: Head=0, Tail=0, Force scn: 0.00000000
　　==>　^^^^^^^^^ Size of Log history if being stored
　　
　　* REDO THREAD ENTRIES:
　　(offset = 0x290, size = 92, max = 6, hi = 1)
　　* THREAD #1 - status:0x6 thread links forward:0 back:0
　　==>　　　　　^^^^(kcc.h)
　　KCCRTOPN　0x01　* set if thread is OPeN *
　　KCCRTENB　0x02　* set if thread is ENaBled *
　　KCCRTPUB　0x04　* set if thread is enabled PUBlicly *
　　#logs:3 first:1 last:3 current:3 last used seq#:0x0
　　enabled at scn: 0.00000000 01/01/88 00:00:00
　　Checkpointed at scn: 0.00000000 01/01/88 00:00:00
　　thread:0 rba:(0.0.0)
　　enabled　threads:　00000000 00000000 00000000 00000000 00000000 00000000 ...
　　LOG FILE ENTRIES:
　　(offset = 0x4b8, size = 72, max = 96, hi = 3)
　　* LOG FILE #1:
　　(#　3) /opt/oracle/ECI_RPT/redo_log1.dbf
　　Thread 1 redo log links: forward=2 backward=0
　　siz=0x64000 seq=0x0 hws=0x0 bsz=512 nab=0x0 flg=0x1
　　==> seq=Log Seq no, hws=Hdr write Seq# nab=Next available block (kcc.h)
　　==> flg:　KCCLESPL　 0x0001 * set if log file spooled *
　　KCCLENAL　 0x0002 * set if No more ALocation may be done in log *
　　KCCLEUSM　 0X0004 * if set USe Me as the next log to make current *
　　KCCLECUR　 0x0008 * set if this is the current log *
　　
　　Archive links: fwrd=0 back=0 Prev scn: 0.00000000
　　==>　　　　　　　　　　　　　　　^^^Low SCN of PREVIOUS log
　　Low scn: 0.00000000 01/01/88 00:00:00
　　==> ^^^SCN when log was switched INTO
　　Next scn: 0.00000000 01/01/88 00:00:00
　　==> ^^^SCN After redo in this log (Ie: should == low of NEXT log)
　　...
　　* DATA FILE #1:
　　(#207) /usr/support/oracle/m4/pms505676/system_1_bpk.dbf
　　size=51200 bsize=2048 status=x213 head=207 tail=207 dup=1
　　==>　　　　　　　　　　　^^^^(kcc.h)
　　KCCFESTS 0x0001 * belongs to System TableSpace *
　　KCCFEONL 0x0002 * file is ONLine *
　　KCCFERDE 0x0004 * ReaDing is Enabled *
　　KCCFECGE 0x0008 * ChanGing is Enabled *
　　KCCFEMRR 0x0010 * Media Recovery Required *
　　KCCFEGEM 0x0020 * Generate End hot backup Marker at next open *
　　KCCFECKD 0x0040 * File entry generated by check dictionary *
　　KCCFESOR 0x0080 * Save Offline scn Range at next checkpoint *
　　KCCFERMF 0x0100 * Renamed Missing File *
　　KCCFEGOI 0x0200 * Generate Off-line Immediate marker *
　　
　　Checkpoint cnt:61191 scn: 0.06d3ac0c stop scn: ffff.ffffffff 05/27/95 03:34:39
　　==> Last Checkpoint SCN ^^^^^^^^^^^^^^　^^^ Stop=fff implies OPEN or WARM backup
　　Creation Checkpointed at scn: 0.00000003 12/04/93 06:37:16
　　thread:0 rba:(0.0.0)
　　enabled　threads:　00000000 00000000 00000000 00000000 00000000 00000000
　　00000000 00000000
　　Offline scn: 0.00000000
　　Online Checkpointed at scn: 0.00000000 01/01/88 00:00:00
　　thread:0 rba:(0.0.0)
　　...
　　之二 ：EVENT: FILE_HDRS
　　
　　EVENT: FILE_HDRS　　 - Obtaining & Interpreting File Header
　　Dumps
　　------------------------------------------------------------------------------
　　
　　ALTER SESSION SET EVENTS 'immediate trace name file_hdrs level 10';
　　~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
　　(From Oracle 7.1.x)
　　
　　*DUMP OF DATA FILES: 209 files in database
　　*DATA FILE #1:　------------ NB: Here down is the control file info ---------
　　(#207) /usr/support/oracle/m4/pms505676/system_1_bpk.dbf
　　size=51200 bsize=2048 status=x213 head=207 tail=207 dup=1
　　==>　　　　　　　　　　　^^^^(kcc.h)
　　KCCFESTS 0x0001 * belongs to System TableSpace *
　　KCCFEONL 0x0002 * file is ONLine *
　　KCCFERDE 0x0004 * ReaDing is Enabled *
　　KCCFECGE 0x0008 * ChanGing is Enabled *
　　KCCFEMRR 0x0010 * Media Recovery Required *
　　KCCFEGEM 0x0020 * Generate End hot backup Marker at next open *
　　KCCFECKD 0x0040 * File entry generated by check dictionary *
　　KCCFESOR 0x0080 * Save Offline scn Range at next checkpoint *
　　KCCFERMF 0x0100 * Renamed Missing File *
　　KCCFEGOI 0x0200 * Generate Off-line Immediate marker *
　　
　　Checkpoint cnt:61191 scn: 0.06d3ac0c stop scn: ffff.ffffffff 05/27/95 03:34:39
　　==>　　　　　　　　　　　　　　　　　　^^^Stop=ffff implies OPEN,ABORT or WARM
　　Creation Checkpointed at scn: 0.00000003 12/04/93 06:37:16
　　thread:0 rba:(0.0.0)
　　enabled　threads:　00000000 00000000 00000000 00000000 00000000 00000000 ...
　　Offline scn: 0.00000000
　　Online Checkpointed at scn: 0.00000000 01/01/88 00:00:00
　　thread:0 rba:(0.0.0)
　　enabled　threads:　00000000 00000000 00000000 00000000 00000000 00000000 ...
　　* FILE HEADER:　------------Here down is from the file header------------------
　　Software vsn=117502976=700f400, Compatibility Vsn=117485568=700b000
　　Db Id=4188500840=f9a77368, Db Name='ECI_PD'　 ^^^ Oldest Usable Oracle Vers
　　Control Seq=202416=316b0, File size=51200=c800
　　==>　^^^ Should be <= to Seq# in control file.
　　File Number=1, Blksiz=2048, File Type=3
　　==>　　　　　　　　　　　　　　　^^^^^^^^^^^(kcc.h)
　　KCCTYPCF　　1　 * control file *

原文转自：http://www.ltesting.net