ITPub博客

首页 > 数据库 > Oracle > oracle 11g bootstrap$系列一

oracle 11g bootstrap$系列一

原创 Oracle 作者:wisdomone1 时间:2015-11-03 00:26:32 0 删除 编辑

背景

  oracle从nomount到mount,内部机制到底是怎么样的,要经历哪些过程,了解这些,对于理解ORACLE,会大有帮助,拓宽分析解决问题能力。

结论

1,测试环境为oracle 11.2.0.1
2,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/9240380/viewspace-1821176/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb 工作经历: 中国普天 北京科蓝 北京云和恩墨 北京神州新桥

注册时间:2008-04-04

  • 博文量
    2188
  • 访问量
    12254989