oracle 11g bootstrap$系列一
背景
oracle從nomount到mount，內部機制到底是怎麼樣的，要經歷哪些過程，瞭解這些，對於理解ORACLE，會大有幫助，拓寬分析解決問題能力。結論
1,測試環境為oracle 11.2.0.12,bootstrap$為底層表，共計儲存60個物件
3,這60個物件，有表，有索引，也有cluster table
4,obj#為-1及0的物件不在obj$，它的含義如下：
LINE# OBJ# SQL_TEXT
---------- ---------- ----------------------------------------------------------------------------------------------------
-1 -1 8.0.0.0.0
0 0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO
0 EXTENTS (FILE 1 BLOCK 128))
5，這60個物件全儲存在1號檔案即system.dbf中
6，透過10046分析，從NOMOUNT到MOUNT,依次是dbwr,ckpt,dbwr,lgwr,ckpt幾個後臺程式參考了從nomount至mount的工作，
也就是如果這些後臺程式出現故障，資料庫無法從nomount到mount狀態
我是源於10046 TRACE檔案中的等待事件rdbms ipc reply
7, 要理解相關幾個等待事件的含義及其引數含義
rdbms ipc reply
Disk file operations I/O
control file sequential read
control file heartbeat
control file parallel write
ADR block file read
8，關於這些等待事件，還正在研究中，將於下文進行繼續測試
9，關於上述這些等待事件的obj#=-1,其含義還沒有搞懂
以及file=0和file=1 或file=2
測試
1,資料庫版本
SQL> select * from v$version where rownum=1;
BANNER
--------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.
2.0.1.0 - 64bit Production
2,bootstrap$含義
SQL> desc bootstrap$;
Name Null? Type
----------------------- -------- ----------------
LINE# NOT NULL NUMBER
OBJ# NOT NULL NUMBER
SQL_TEXT NOT NULL VARCHAR2(4000)
3,可見bootstrap$共計儲存60個物件
SQL> select distinct obj# from bootstrap$ order by 1;
OBJ#
----------
-1
0
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60 rows selected.
4，我們看看這60個物件都是什麼呢？
SQL> select obj#,name,type# from obj$ where obj# in (select obj# from bootstrap$) order by 1;
OBJ# NAME TYPE#
---------- -------------------------------------------------- ----------
2 C_OBJ# 3
3 I_OBJ# 1
4 TAB$ 2
5 CLU$ 2
6 C_TS# 3
7 I_TS# 1
8 C_FILE#_BLOCK# 3
9 I_FILE#_BLOCK# 1
10 C_USER# 3
11 I_USER# 1
12 FET$ 2
13 UET$ 2
14 SEG$ 2
15 UNDO$ 2
16 TS$ 2
17 FILE$ 2
18 OBJ$ 2
19 IND$ 2
20 ICOL$ 2
21 COL$ 2
22 USER$ 2
23 PROXY_DATA$ 2
24 I_PROXY_DATA$ 1
25 PROXY_ROLE_DATA$ 2
26 I_PROXY_ROLE_DATA$_1 1
27 I_PROXY_ROLE_DATA$_2 1
28 CON$ 2
29 C_COBJ# 3
30 I_COBJ# 1
31 CDEF$ 2
32 CCOL$ 2
33 I_TAB1 1
34 I_UNDO1 1
35 I_UNDO2 1
36 I_OBJ1 1
37 I_OBJ2 1
38 I_OBJ3 1
39 I_OBJ4 1
40 I_OBJ5 1
41 I_IND1 1
42 I_ICOL1 1
43 I_FILE1 1
44 I_FILE2 1
45 I_TS1 1
46 I_USER1 1
47 I_USER2 1
48 I_COL1 1
49 I_COL2 1
50 I_COL3 1
51 I_CON1 1
52 I_CON2 1
53 I_CDEF1 1
54 I_CDEF2 1
55 I_CDEF3 1
56 I_CDEF4 1
57 I_CCOL1 1
58 I_CCOL2 1
59 BOOTSTRAP$ 2
58 rows selected.
5,看下上述58個物件屬於什麼型別的物件
摘自dcore.sql檔案，可知type#=1為索引，2為表，3為集或叫cluster table(這是一種特殊型別的表)
type# number not null, /* object type (see KQD.H): */
/* 1 = INDEX, 2 = TABLE, 3 = CLUSTER, 4 = VIEW, 5 = SYNONYM, 6 = SEQUENCE, */
/* 7 = PROCEDURE, 8 = FUNCTION, 9 = PACKAGE, 10 = NON-EXISTENT, */
/* 11 = PACKAGE BODY, 12 = TRIGGER, 13 = TYPE, 14 = TYPE BODY, */
/* 19 = TABLE PARTITION, 20 = INDEX PARTITION, 21 = LOB, 22 = LIBRARY, */
/* 23 = DIRECTORY , 24 = QUEUE, */
/* 25 = IOT, 26 = REPLICATION OBJECT GROUP, 27 = REPLICATION PROPAGATOR, */
/* 28 = JAVA SOURCE, 29 = JAVA CLASS, 30 = JAVA RESOURCE, 31 = JAVA JAR, */
/* 32 = INDEXTYPE, 33 = OPERATOR , 34 = TABLE SUBPARTITION, */
/* 35 = INDEX SUBPARTITION */
/* 82 = (Data Mining) MODEL */
/* 92 = OLAP CUBE DIMENSION, 93 = OLAP CUBE */
/* 94 = OLAP MEASURE FOLDER, 95 = OLAP CUBE BUILD PROCESS */
所以可見上述的58個物件有表，也有索引，也有cluster table
6,大家不知注意到沒有，出現一個問題，bootstrap$儲存了60個物件，但我們上述只說了58個物件，還有2個物件，這2個物件又是什麼呢？
可見這2個物件肯定不儲存在obj$中
SQL> select obj#,name from obj$ where obj# in (-1,0);
no rows selected
從查詢出來的結果可知,-1儲存物件為8.0.0.0.0,這是個什麼東西呢，0物件為建立一個system rollback segment的ddl語句
SQL> col sql_text for a100
SQL> select line#,obj#,sql_text from bootstrap$ where obj# in (-1,0);
LINE# OBJ# SQL_TEXT
---------- ---------- ----------------------------------------------------------------------------------------------------
-1 -1 8.0.0.0.0
0 0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO
0 EXTENTS (FILE 1 BLOCK 128))
7，檢視上述60個物件所處的檔案及資料塊
我們僅列舉部分，可知60個物件全儲存在檔案1號中，也就是system表空間的資料檔案
SQL> select distinct DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) file_no from tab$;
FILE_NO
----------
1
SQL> select count(distinct DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) block_count from tab$;
BLOCK_COUNT
-----------
539
SQL> select distinct DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) file_no from seq$;
FILE_NO
----------
1
SQL> select count(distinct DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) block_count from seq$;
BLOCK_COUNT
-----------
3
SQL> select distinct DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) file_no from col$;
FILE_NO
----------
1
SQL> select count(distinct DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) block_count from col$;
BLOCK_COUNT
-----------
1157
8，現在重啟資料庫到nomount,用10046跟蹤，看啟動是以何種方式及次序讀取這些60個物件的？
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2215064 bytes
Variable Size 1409287016 bytes
Database Buffers 721420288 bytes
Redo Buffers 4964352 bytes
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> alter database mount;
Database altered.
SQL> oradebug event 10046 trace name context off
Statement processed.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_8301.trc
9,檢視上述的trace檔案
*** 2015-10-31 09:04:49.823
Oradebug command 'event 10046 trace name context forever,level 12' console output: <none>
WAIT #0: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1446296689823453
*** 2015-10-31 09:06:12.614
WAIT #0: nam='SQL*Net message from client' ela= 82780553 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1446296772614555
XCTEND rlbk=0, rd_only=1, tim=1446296772631455
=====================
PARSING IN CURSOR #3 len=21 dep=0 uid=0 oct=35 lid=0 tim=1446296772652864 hv=4108919762 ad='de9f8750' sqlid='5fk0qrbufk8yk'
alter database mount
END OF STMT
PARSE #3:c=1000,e=37098,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1446296772652863
WAIT #3: nam='rdbms ipc reply' ela= 61923 from_process=10 timeout=60 p3=0 obj#=-1 tim=1446296772825546
WAIT #3: nam='reliable message' ela= 21968 channel context=3684420096 channel handle=3683986800 broadcast message=3685422768 obj#=-1 tim=1446296772861121
*** 2015-10-31 09:06:12.983
WAIT #3: nam='rdbms ipc reply' ela= 118283 from_process=12 timeout=900 p3=0 obj#=-1 tim=1446296772983943
WAIT #3: nam='Disk file operations I/O' ela= 138 FileOperation=2 fileno=0 filetype=1 obj#=-1 tim=1446296772993312
WAIT #3: nam='Disk file operations I/O' ela= 113 FileOperation=2 fileno=1 filetype=1 obj#=-1 tim=1446296773013153
WAIT #3: nam='control file sequential read' ela= 44 file#=0 block#=1 blocks=1 obj#=-1 tim=1446296773013223
WAIT #3: nam='control file sequential read' ela= 69 file#=1 block#=1 blocks=1 obj#=-1 tim=1446296773039515
WAIT #3: nam='control file sequential read' ela= 144975 file#=0 block#=3 blocks=8 obj#=-1 tim=1446296773184571
WAIT #3: nam='control file sequential read' ela= 63738 file#=1 block#=3 blocks=8 obj#=-1 tim=1446296773248392
*** 2015-10-31 09:06:17.249
WAIT #3: nam='control file heartbeat' ela= 4000995 p1=0 p2=0 p3=0 obj#=-1 tim=1446296777249544
WAIT #3: nam='control file sequential read' ela= 60 file#=0 block#=3 blocks=8 obj#=-1 tim=1446296777250003
WAIT #3: nam='control file sequential read' ela= 36 file#=1 block#=3 blocks=8 obj#=-1 tim=1446296777250084
WAIT #3: nam='control file sequential read' ela= 7 file#=0 block#=1 blocks=1 obj#=-1 tim=1446296777250109
WAIT #3: nam='control file parallel write' ela= 443 files=1 block#=1 requests=1 obj#=-1 tim=1446296777250578
WAIT #3: nam='control file sequential read' ela= 5 file#=0 block#=1 blocks=1 obj#=-1 tim=1446296777250612
WAIT #3: nam='control file sequential read' ela= 6 file#=1 block#=1 blocks=1 obj#=-1 tim=1446296777250634
WAIT #3: nam='control file parallel write' ela= 215 files=1 block#=1 requests=1 obj#=-1 tim=1446296777250866
WAIT #3: nam='control file sequential read' ela= 5 file#=0 block#=1 blocks=1 obj#=-1 tim=1446296777250905
WAIT #3: nam='control file sequential read' ela= 11096 file#=0 block#=16 blocks=1 obj#=-1 tim=1446296777262041
WAIT #3: nam='control file sequential read' ela= 828 file#=0 block#=18 blocks=1 obj#=-1 tim=1446296777263032
WAIT #3: nam='control file parallel write' ela= 338 files=2 block#=17 requests=2 obj#=-1 tim=1446296777263447
WAIT #3: nam='control file parallel write' ela= 398 files=2 block#=15 requests=2 obj#=-1 tim=1446296777263874
WAIT #3: nam='control file parallel write' ela= 260 files=2 block#=1 requests=2 obj#=-1 tim=1446296777264159
WAIT #3: nam='control file sequential read' ela= 6 file#=0 block#=1 blocks=1 obj#=-1 tim=1446296777264181
WAIT #3: nam='control file sequential read' ela= 30 file#=0 block#=3 blocks=8 obj#=-1 tim=1446296777264234
WAIT #3: nam='control file parallel write' ela= 343 files=1 block#=3 requests=1 obj#=-1 tim=1446296777264600
WAIT #3: nam='control file sequential read' ela= 31 file#=0 block#=3 blocks=1 obj#=-1 tim=1446296777264743
WAIT #3: nam='control file parallel write' ela= 161 files=1 block#=4 requests=1 obj#=-1 tim=1446296777265035
WAIT #3: nam='control file sequential read' ela= 5 file#=0 block#=4 blocks=1 obj#=-1 tim=1446296777265058
WAIT #3: nam='control file parallel write' ela= 146 files=1 block#=5 requests=1 obj#=-1 tim=1446296777265219
WAIT #3: nam='control file sequential read' ela= 7 file#=0 block#=5 blocks=1 obj#=-1 tim=1446296777265248
WAIT #3: nam='control file parallel write' ela= 211 files=1 block#=6 requests=1 obj#=-1 tim=1446296777265475
WAIT #3: nam='control file sequential read' ela= 134 file#=0 block#=6 blocks=1 obj#=-1 tim=1446296777265625
WAIT #3: nam='control file parallel write' ela= 8503 files=1 block#=7 requests=1 obj#=-1 tim=1446296777274158
WAIT #3: nam='control file sequential read' ela= 9 file#=0 block#=7 blocks=1 obj#=-1 tim=1446296777274229
WAIT #3: nam='control file parallel write' ela= 197 files=1 block#=8 requests=1 obj#=-1 tim=1446296777274449
WAIT #3: nam='control file sequential read' ela= 4 file#=0 block#=8 blocks=1 obj#=-1 tim=1446296777274467
WAIT #3: nam='control file parallel write' ela= 129 files=1 block#=9 requests=1 obj#=-1 tim=1446296777274607
WAIT #3: nam='control file sequential read' ela= 3 file#=0 block#=9 blocks=1 obj#=-1 tim=1446296777274624
WAIT #3: nam='control file parallel write' ela= 277 files=1 block#=10 requests=1 obj#=-1 tim=1446296777274912
WAIT #3: nam='control file sequential read' ela= 347 file#=0 block#=10 blocks=1 obj#=-1 tim=1446296777275274
WAIT #3: nam='control file parallel write' ela= 403 files=1 block#=11 requests=1 obj#=-1 tim=1446296777275717
WAIT #3: nam='control file sequential read' ela= 4 file#=0 block#=11 blocks=1 obj#=-1 tim=1446296777275736
WAIT #3: nam='control file parallel write' ela= 130 files=1 block#=12 requests=1 obj#=-1 tim=1446296777275877
WAIT #3: nam='control file sequential read' ela= 4 file#=0 block#=12 blocks=1 obj#=-1 tim=1446296777275894
WAIT #3: nam='control file parallel write' ela= 106 files=1 block#=13 requests=1 obj#=-1 tim=1446296777276010
WAIT #3: nam='control file sequential read' ela= 3 file#=0 block#=13 blocks=1 obj#=-1 tim=1446296777276026
WAIT #3: nam='control file sequential read' ela= 63871 file#=0 block#=282 blocks=1 obj#=-1 tim=1446296777339913
WAIT #3: nam='control file parallel write' ela= 8327 files=2 block#=281 requests=2 obj#=-1 tim=1446296777348493
WAIT #3: nam='control file parallel write' ela= 340 files=2 block#=18 requests=2 obj#=-1 tim=1446296777348897
WAIT #3: nam='control file parallel write' ela= 293 files=2 block#=16 requests=2 obj#=-1 tim=1446296777349217
WAIT #3: nam='control file parallel write' ela= 289 files=2 block#=1 requests=2 obj#=-1 tim=1446296777349531
WAIT #3: nam='control file sequential read' ela= 7 file#=0 block#=1 blocks=1 obj#=-1 tim=1446296777349554
WAIT #3: nam='control file sequential read' ela= 77 file#=0 block#=23 blocks=1 obj#=-1 tim=1446296777365937
WAIT #3: nam='control file sequential read' ela= 12930 file#=0 block#=181 blocks=1 obj#=-1 tim=1446296777378929
WAIT #3: nam='control file sequential read' ela= 19939 file#=0 block#=309 blocks=1 obj#=-1 tim=1446296777422063
WAIT #3: nam='control file sequential read' ela= 18 file#=0 block#=281 blocks=1 obj#=-1 tim=1446296777446057
WAIT #3: nam='control file parallel write' ela= 341 files=2 block#=310 requests=2 obj#=-1 tim=1446296777446470
WAIT #3: nam='control file sequential read' ela= 520 file#=0 block#=283 blocks=1 obj#=-1 tim=1446296777447014
WAIT #3: nam='control file sequential read' ela= 11495 file#=0 block#=519 blocks=1 obj#=-1 tim=1446296777459545
WAIT #3: nam='control file sequential read' ela= 17 file#=0 block#=281 blocks=1 obj#=-1 tim=1446296777459856
WAIT #3: nam='control file parallel write' ela= 12696 files=2 block#=520 requests=2 obj#=-1 tim=1446296777472579
WAIT #3: nam='control file parallel write' ela= 293 files=2 block#=17 requests=2 obj#=-1 tim=1446296777473101
WAIT #3: nam='control file parallel write' ela= 336 files=2 block#=15 requests=2 obj#=-1 tim=1446296777473462
WAIT #3: nam='control file parallel write' ela= 416 files=2 block#=1 requests=2 obj#=-1 tim=1446296777473902
WAIT #3: nam='control file sequential read' ela= 7 file#=0 block#=1 blocks=1 obj#=-1 tim=1446296777473929
WAIT #3: nam='rdbms ipc reply' ela= 700 from_process=10 timeout=910 p3=0 obj#=-1 tim=1446296777492487
WAIT #3: nam='rdbms ipc reply' ela= 8099 from_process=11 timeout=1800 p3=0 obj#=-1 tim=1446296777501110
WAIT #3: nam='control file sequential read' ela= 6 file#=0 block#=1 blocks=1 obj#=-1 tim=1446296777501170
WAIT #3: nam='control file sequential read' ela= 4 file#=0 block#=16 blocks=1 obj#=-1 tim=1446296777501191
WAIT #3: nam='control file sequential read' ela= 4 file#=0 block#=18 blocks=1 obj#=-1 tim=1446296777501206
WAIT #3: nam='rdbms ipc reply' ela= 521 from_process=12 timeout=2147483647 p3=0 obj#=-1 tim=1446296777501843
WAIT #3: nam='control file sequential read' ela= 6 file#=0 block#=1 blocks=1 obj#=-1 tim=1446296777502027
WAIT #3: nam='control file sequential read' ela= 5 file#=1 block#=1 blocks=1 obj#=-1 tim=1446296777502047
WAIT #3: nam='control file sequential read' ela= 4 file#=0 block#=16 blocks=1 obj#=-1 tim=1446296777502062
WAIT #3: nam='control file sequential read' ela= 4 file#=0 block#=18 blocks=1 obj#=-1 tim=1446296777502076
WAIT #3: nam='control file sequential read' ela= 6 file#=0 block#=281 blocks=1 obj#=-1 tim=1446296777502096
WAIT #3: nam='ADR block file read' ela= 37247 =0 =0 =0 obj#=-1 tim=1446296777675074
WAIT #3: nam='ADR block file read' ela= 7722 =0 =0 =0 obj#=-1 tim=1446296777683459
WAIT #3: nam='ADR block file read' ela= 25901 =0 =0 =0 obj#=-1 tim=1446296777709757
WAIT #3: nam='ADR block file read' ela= 1663 =0 =0 =0 obj#=-1 tim=1446296777711874
WAIT #3: nam='ADR block file read' ela= 35072 =0 =0 =0 obj#=-1 tim=1446296777747572
WAIT #3: nam='ADR block file read' ela= 14872 =0 =0 =0 obj#=-1 tim=1446296777762855
WAIT #3: nam='ADR block file read' ela= 17409 =0 =0 =0 obj#=-1 tim=1446296777780799
WAIT #3: nam='ADR block file read' ela= 29847 =0 =0 =0 obj#=-1 tim=1446296777850437
WAIT #3: nam='ADR block file read' ela= 13570 =0 =0 =0 obj#=-1 tim=1446296777864497
WAIT #3: nam='ADR block file read' ela= 20071 =0 =0 =0 obj#=-1 tim=1446296777885196
WAIT #3: nam='ADR block file read' ela= 880 =0 =0 =0 obj#=-1 tim=1446296777886715
*** 2015-10-31 09:06:17.944
WAIT #3: nam='ADR block file read' ela= 27761 =0 =0 =0 obj#=-1 tim=1446296777944148
WAIT #3: nam='ADR block file read' ela= 10647 =0 =0 =0 obj#=-1 tim=1446296777982848
WAIT #3: nam='ADR block file read' ela= 14849 =0 =0 =0 obj#=-1 tim=1446296777998699
WAIT #3: nam='ADR block file read' ela= 35131 =0 =0 =0 obj#=-1 tim=1446296778034599
WAIT #3: nam='ADR block file read' ela= 14513 =0 =0 =0 obj#=-1 tim=1446296778050044
WAIT #3: nam='ADR block file read' ela= 1261 =0 =0 =0 obj#=-1 tim=1446296778063093
WAIT #3: nam='ADR block file read' ela= 23532 =0 =0 =0 obj#=-1 tim=1446296778105361
WAIT #3: nam='ADR block file read' ela= 15768 =0 =0 =0 obj#=-1 tim=1446296778121825
WAIT #3: nam='ADR block file read' ela= 929 =0 =0 =0 obj#=-1 tim=1446296778123475
WAIT #3: nam='ADR block file read' ela= 490 =0 =0 =0 obj#=-1 tim=1446296778124444
WAIT #3: nam='ADR block file read' ela= 752 =0 =0 =0 obj#=-1 tim=1446296778126288
WAIT #3: nam='ADR block file read' ela= 454 =0 =0 =0 obj#=-1 tim=1446296778127213
WAIT #3: nam='ADR block file read' ela= 646 =0 =0 =0 obj#=-1 tim=1446296778128271
WAIT #3: nam='ADR block file read' ela= 231 =0 =0 =0 obj#=-1 tim=1446296778128965
WAIT #3: nam='ADR block file read' ela= 1045 =0 =0 =0 obj#=-1 tim=1446296778130391
WAIT #3: nam='ADR block file read' ela= 593 =0 =0 =0 obj#=-1 tim=1446296778131413
WAIT #3: nam='ADR block file read' ela= 391 =0 =0 =0 obj#=-1 tim=1446296778132208
WAIT #3: nam='ADR block file read' ela= 15954 =0 =0 =0 obj#=-1 tim=1446296778163400
WAIT #3: nam='ADR block file read' ela= 1006 =0 =0 =0 obj#=-1 tim=1446296778165076
WAIT #3: nam='ADR block file read' ela= 767 =0 =0 =0 obj#=-1 tim=1446296778166364
WAIT #3: nam='ADR block file read' ela= 227 =0 =0 =0 obj#=-1 tim=1446296778168120
WAIT #3: nam='ADR block file read' ela= 392 =0 =0 =0 obj#=-1 tim=1446296778168904
WAIT #3: nam='ADR block file read' ela= 702 =0 =0 =0 obj#=-1 tim=1446296778185168
WAIT #3: nam='ADR block file read' ela= 15825 =0 =0 =0 obj#=-1 tim=1446296778201438
WAIT #3: nam='ADR block file read' ela= 4704 =0 =0 =0 obj#=-1 tim=1446296778206743
WAIT #3: nam='ADR block file read' ela= 14386 =0 =0 =0 obj#=-1 tim=1446296778244721
WAIT #3: nam='ADR block file read' ela= 1116 =0 =0 =0 obj#=-1 tim=1446296778247177
WAIT #3: nam='ADR block file read' ela= 575 =0 =0 =0 obj#=-1 tim=1446296778265971
WAIT #3: nam='ADR block file read' ela= 523 =0 =0 =0 obj#=-1 tim=1446296778266884
10,要分析TRACE檔案的內容，先要理解下上述幾個等待事件各個引數的含義
rdbms ipc reply
Disk file operations I/O
control file sequential read
control file heartbeat
control file parallel write
ADR block file read
我們依次來看
SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name='rdbms ipc reply';
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ ------------------------------ ------------------------------ ------------------------------
rdbms ipc reply from_process timeout
SQL> select addr,pid,spid,pname from v$process where pid in (10,11,12);
ADDR PID SPID PNAME
---------------- ---------- ------------------------------------------------ ----------
00000000DD5A7E40 10 8281 DBW0
00000000DD5A8E80 11 8283 LGWR
00000000DD5A9EC0 12 8285 CKPT
我們把與rdbms ipc reply相關的等待事件記錄摘錄出來，
WAIT #3: nam='rdbms ipc reply' ela= 61923 from_process=10 timeout=60 p3=0 obj#=-1 tim=1446296772825546
WAIT #3: nam='rdbms ipc reply' ela= 118283 from_process=12 timeout=900 p3=0 obj#=-1 tim=1446296772983943
WAIT #3: nam='rdbms ipc reply' ela= 700 from_process=10 timeout=910 p3=0 obj#=-1 tim=1446296777492487
WAIT #3: nam='rdbms ipc reply' ela= 8099 from_process=11 timeout=1800 p3=0 obj#=-1 tim=1446296777501110
WAIT #3: nam='rdbms ipc reply' ela= 521 from_process=12 timeout=2147483647 p3=0 obj#=-1 tim=1446296777501843
由上可見，從nomount到mount,依次是dbwr,ckpt,dbwr,lgwr,ckpt幾個後臺程式參考了從nomount至mount的工作，
也就是如果這些後臺程式出現故障，資料庫無法從nomount到mount狀態
然後再來看等待事件Disk file operations I/O
SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name='Disk file operations I/O';
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ ------------------------------ ------------------------------ ------------------------------
Disk file operations I/O FileOperation fileno filetype
摘錄下與此等待事件相關的TRACE檔案內容
WAIT #3: nam='Disk file operations I/O' ela= 138 FileOperation=2 fileno=0 filetype=1 obj#=-1 tim=1446296772993312
WAIT #3: nam='Disk file operations I/O' ela= 113 FileOperation=2 fileno=1 filetype=1 obj#=-1 tim=1446296773013153
從上面fileno的值來看，有0和1，這對應哪些檔案呢？當然還有fileoperation=2，又是什麼含義呢？obj#=-1對應哪個物件呢？下面我們來分析下。
SQL> select file#,name from v$datafile;
FILE# NAME
---------- ------------------------------------------------------------
1 /oracle/oradata/guowang/system01.dbf
2 /oracle/oradata/guowang/sysaux01.dbf
3 /oracle/oradata/guowang/uniform1.dbf
4 /oracle/oradata/guowang/users01.dbf
5 /oracle/oradata/guowang/new_undo1.dbf
6 /oracle/oradata/guowang/t_err1.dbf
7 /oracle/oradata/guowang/tbs_undo_nb.dbf
8 /oracle/oradata/guowang/new_add1.dbf
8 rows selected.
SQL> select name,file_size_blks,block_size from v$controlfile;
NAME FILE_SIZE_BLKS BLOCK_SIZE
------------------------------------------------------------ -------------- ----------
/oracle/oradata/guowang/control01.ctl 666 16384
/oracle/oradata/guowang/control02.ctl 666 16384
可見每個控制檔案的資料塊大小為16K
SQL> select 16384/1024 from dual;
16384/1024
----------
16
大家注意觀察，TRACE裡面等待事件涉及的物件全是obj#=-1,我推測是如下的8.0.0.0.0,從這裡可以看出來，nomount到mount只是用到了bootstrap$中儲存物件的obj#=-1的物件而已，其後的物件沒有用到
SQL> select line#,obj#,sql_text from bootstrap$ where obj# in (-1,0);
LINE# OBJ# SQL_TEXT
---------- ---------- ----------------------------------------------------------------------------------------------------
-1 -1 8.0.0.0.0
0 0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE ( INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO
0 EXTENTS (FILE 1 BLOCK 128))
個人簡介：
8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。
服務過的客戶：
中國電信
中國移動
中國聯通
中國電通
國家電網
四川達州商業銀行
湖南老百姓大藥房
山西省公安廳
中國郵政
北京302醫院
河北廊坊新奧集團公司
專案經驗:
中國電信3G專案AAA系統資料庫部署及最佳化
中國聯通CRM資料庫效能最佳化
中國移動10086電商平臺資料庫部署及最佳化
湖南老百姓大藥房ERR資料庫sql最佳化專案
四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化
四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化
北京高鐵訊號監控系統RAC資料庫部署及最佳化
河南宇通客車資料庫效能最佳化
中國電信電商平臺核心採購模組表模型設計及最佳化
中國郵政儲蓄系統資料庫效能最佳化及sql最佳化
北京302醫院資料庫遷移實施
河北廊坊新奧data guard部署及最佳化
山西公安廳身份證審計資料庫系統故障評估
聯絡方式：
手機：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30496894/viewspace-1825222/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle 11g bootstrap$系列二Oracleboot
- oracle 11g bootstrap$系列三Oracleboot
- oracle 11g latch之系列一Oracle
- oracle 11g pmon工作內容系列一Oracle
- Oracle 11g系列：檢視Oracle
- Oracle 11g系列：約束Oracle
- Bootstrap系列 -- 1. 如何使用Bootstrapboot
- Oracle 11g系列：資料庫Oracle資料庫
- Oracle 11g系列：資料表物件Oracle物件
- oracle 11g如果應用表依賴的bootstrap$某些某表如obj$_col$資料損壞系列四OraclebootOBJ
- Bootstrap系列 -- 3. 段落boot
- Bootstrap系列 -- 6. 列表boot
- Bootstrap系列 -- 9. 表格boot
- Oracle 11g系列：SQL Plus與PL/SQLOracleSQL
- oracle 11g latch之v$latch系列二Oracle
- oracle 11g latch之v$latch系列三Oracle
- oracle 11g pmon工作內容系列二Oracle
- oracle 11g pmon工作內容系列三Oracle
- Bootstrap3外掛系列：bootstrap-select2boot
- Bootstrap3系列：導航boot
- Bootstrap系列 -- 2. 標題boot
- Bootstrap系列 -- 22. 按鈕boot
- Bootstrap系列 -- 23. 圖片boot
- oracle 11g rman configure retention policy to recovery window of days系列一Oracle
- Oracle bootstrap$ 詳解Oracleboot
- ORACLE 11g dataguard系列，手工切換測試Oracle
- Oracle 11g AWR 系列六：使用 AWR 檢視Oracle
- oracle lock系列一Oracle
- Bootstrap3系列：下拉選單boot
- Bootstrap3系列：按鈕組boot
- Bootstrap系列 -- 7. 列表排版方式boot
- Bootstrap系列 -- 12. 水平表單boot
- Bootstrap系列 -- 16. 文字域textareaboot
- Bootstrap系列 -- 20. 禁用狀態boot
- Bootstrap系列 -- 29. 按鈕組boot
- Bootstrap系列 -- 33. 等分按鈕boot
- oracle11g基於bootstrap$中的ind$表損壞系列五Oracleboot
- Oracle 11g系列：函式與儲存過程Oracle函式儲存過程