[20160229]探究oracle的启动过程.txt

[20160229]探究oracle的启动过程.txt

--昨天自己研究了sys.bootstrap$，链接http://blog.itpub.net/267265/viewspace-2016219/
--今天换一种方式探究，当加载某个对象时，会调用内部函数kqlobjlod，通过gdb设置断点，可以实现了解启动的过程。
--还是通过测试来讲解：

1.建立测试环境:
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create table t (id number ,name varchar2(20));
SCOTT@book> insert into t values (1,'aaaa');
SCOTT@book> insert into t values (2,'bbbb');
SCOTT@book> commit ;

SCOTT@book> select rowid,t.* from t;
ROWID                      ID NAME
------------------ ---------- --------------------
AAAWRFAAEAAAAIdAAA          1 aaaa
AAAWRFAAEAAAAIdAAB          2 bbbb

SCOTT@book> @ &r/rowid AAAWRFAAEAAAAIdAAA;
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
     91205          4        541          0 4,541                alter system dump datafile 4 block 541 ;

SCOTT@book> update t set name='BBBB' where id=2;
1 row updated.

SCOTT@book> @ &r/xid ;
XIDUSN_XIDSLOT_XIDSQN
------------------------------
6.22.3261

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBAREC     UBASQN STATUS            USED_UBLK  USED_UREC XID              ADDR             START_DATE          C70
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ---------------- ------------------- ----------------------------------------------------------------------
         6         22       3261          3       5137         26        896 ACTIVE                    1          1 06001600BD0C0000 00000000814455D0 2016-03-01 09:19:08 ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU6_1263032392$' XID 6 22 3261;
                                                                                                                                                                          ALTER SYSTEM DUMP UNDO HEADER '_SYSSMU6_1263032392$';
--注意不要提交。

SYS@book> alter system checkpoint;
System altered.

SYS@book> shutdown abort;
ORACLE instance shut down.

2.开始探究启动过程：
--因为我没有正常关闭数据库，oracle启动后发现scn不一致，先启动日志应用也就是前滚，读取日志并应用日志，然后启动加载
--bootstrap$完成自举，接着才是通过smon进程将没有提交的事务回滚，打开数据库，当然smon恢复与打开应该可以并行完成。

--测试要打开5个窗口，便于观察：
--2个sqlplus 窗口，1个gdb，1个tail -f 检查alert文件，1个检查跟踪文件,通过tmux很容易完成。

--窗口1(sqlplus)：
SYS@book> startup mount
ORACLE instance started.
Total System Global Area  634679296 bytes
Fixed Size                  2255912 bytes
Variable Size             264242136 bytes
Database Buffers          360710144 bytes
Redo Buffers                7471104 bytes
Database mounted.

--确定进程号
SYS@book> host ps -ef | grep oracleboo[k]
oracle   61709 56257  0 15:24 ?        00:00:00 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

--窗口2(sqlplus)：
$ rlsql

--窗口3(gdb)：
$ rlwrap gdb -p 61709
...
(gdb) break kqlobjlod
Breakpoint 1 at 0x1c49932

--窗口4(tail -f):

$ tail -f alertbook.log

--回到窗口1:
SYS@book> @ &r/10046on 12
old   1: alter session set events '10046 trace name context forever, level &1'
new   1: alter session set events '10046 trace name context forever, level 12'
Session altered.
SYS@book> alter database open ;

--窗口5:(通过进程号很容易确定是生成的跟踪文件名)
$ tail -f book_ora_61709.trc

3.开始跟踪：

--检查alertbook.log:
Completed: ALTER DATABASE   MOUNT
Mon Feb 29 15:35:06 2016
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 23 processes
Started redo scan
Completed redo scan
read 841 KB redo, 384 data blocks need recovery
Started redo application at
Thread 1: logseq 420, block 15995
Recovery of Online Redo Log: Thread 1 Group 3 Seq 420 Reading mem 0
  Mem# 0: /mnt/ramdisk/book/redo03.log
Completed redo application of 0.66MB
Completed crash recovery at
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Thread 1: logseq 420, block 17677, scn 13225677436
384 data blocks read, 384 data blocks written, 841 redo k-bytes read
Mon Feb 29 15:35:07 2016
LGWR: STARTING ARCH PROCESSES
Mon Feb 29 15:35:07 2016
ARC0 started with pid=46, OS id=61837
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thread 1 advanced to log sequence 421 (thread open)
Thread 1 opened at log sequence 421
  Current log# 1 seq# 421 mem# 0: /mnt/ramdisk/book/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Feb 29 15:35:08 2016
SMON: enabling cache recovery
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Mon Feb 29 15:35:08 2016
ARC1 started with pid=47, OS id=61839
Mon Feb 29 15:35:08 2016
ARC2 started with pid=48, OS id=61841
Mon Feb 29 15:35:08 2016
ARC3 started with pid=49, OS id=61843
ARC1: Archival started
ARC2: Archival started
ARC2: Becoming the 'no FAL' ARCH
ARC2: Becoming the 'no SRL' ARCH
ARC1: Becoming the heartbeat ARCH
Archived Log entry 405 added for thread 1 sequence 420 ID 0x4fb7d86e dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE

--注意看~,Completed crash recovery at之前就是应用日志的过程，接着启动SMON: enabling cache recovery.(这一步是本次测试的重点)。
--回到窗口5，可以发现目前没有到读取dba=1,520那一步。
--另外这时看看数据块dba=4,541的情况：
BBED> set dba 4,541
        DBA             0x0100021d (16777757 4,541)

BBED> x /2rnc rowdata
rowdata[0]                                  @8166
----------
flag@8166: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8167: 0x02
cols@8168:    2

col    0[2] @8169: 2
col    1[4] @8172: BBBB

rowdata[11]                                 @8177
-----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179:    2

col    0[2] @8180: 1
col    1[4] @8183: aaaa

--窗口2(sqlplus)：
SYS@book> SELECT  KGLOBTYD, KGLNAOBJ FROM x$kglob WHERE KGLOBTYD IN ('CLUSTER', 'TABLE', 'INDEX')  AND KGLNAOBJ NOT LIKE 'X%' ORDER BY 1;
no rows selected

SYS@book> select * from v$rowcache where count>0;
no rows selected

--查询以上条件，可以发现没有结果。

--窗口3(gdb)，按c继续：
(gdb) c
Continuing.
Breakpoint 1, 0x0000000001c49932 in kqlobjlod ()

--窗口2(sqlplus)：
SYS@book> column KGLOBTYD format a20
SYS@book> SELECT  KGLOBTYD, KGLNAOBJ FROM x$kglob WHERE KGLOBTYD IN ('CLUSTER', 'TABLE', 'INDEX')  AND KGLNAOBJ NOT LIKE 'X%' ORDER BY 1;
KGLOBTYD             KGLNAOBJ
-------------------- ------------------------------
TABLE                BOOTSTRAP$

SYS@book> select parameter,count,gets from v$rowcache where count>0;
PARAMETER                             COUNT       GETS
-------------------------------- ---------- ----------
dc_rollback_segments                      1          1
dc_objects                                2          9

--可以发现首先加载对象BOOTSTRAP$。另外你可以发现PARAMETER=dc_rollback_segments,也存在一个记数。如果你看BOOTSTRAP$或者跟踪文件，可以发现：
=====================
PARSING IN CURSOR #140157476704072 len=129 dep=1 uid=0 oct=36 lid=0 tim=1456732450496714 hv=1119914026 ad='7f78f490c778' sqlid='864bmh11c121a'
CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 128))
END OF STMT
PARSE #140157476704072:c=1000,e=422,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456732450496713
EXEC #140157476704072:c=0,e=79,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1456732450496870
CLOSE #140157476704072:c=0,e=5,dep=1,type=0,tim=1456732450496936
=====================
--实际上就是系统回滚段。

--窗口3(gdb)，按c继续：
--窗口2(sqlplus)：

SYS@book> SELECT  KGLOBTYD, KGLNAOBJ FROM x$kglob WHERE KGLOBTYD IN ('CLUSTER', 'TABLE', 'INDEX')  AND KGLNAOBJ NOT LIKE 'X%' ORDER BY 1;
KGLOBTYD             KGLNAOBJ
-------------------- ------------------------------
CLUSTER              C_OBJ#
TABLE                BOOTSTRAP$

SYS@book> select parameter,count,gets from v$rowcache where count>0;
PARAMETER                             COUNT       GETS
-------------------------------- ---------- ----------
dc_rollback_segments                      1          1
dc_objects                                4         15

--又加载一个对象。如果仔细观察可以发现与这个看到的顺序一致：select rowid,a.* from SYS.BOOTSTRAP$ a order by line#;
--好了现在不在单步跟踪，检查select rowid,a.* from SYS.BOOTSTRAP$ a order by line#;可以发现LINE#＝15，执行CREATE TABLE UNDO$...

--窗口3(gdb)，按c 13继续：(注意这里13表示继续13次)
(gdb) c 13
Will ignore next 12 crossings of breakpoint 1.  Continuing.
Breakpoint 1, 0x0000000001c49932 in kqlobjlod ()

--窗口2(sqlplus)：
SYS@book> SELECT  KGLOBTYD, KGLNAOBJ FROM x$kglob WHERE KGLOBTYD IN ('CLUSTER', 'TABLE', 'INDEX')  AND KGLNAOBJ NOT LIKE 'X%' ORDER BY 1;
KGLOBTYD             KGLNAOBJ
-------------------- ------------------------------
CLUSTER              C_USER#
CLUSTER              C_TS#
CLUSTER              C_FILE#_BLOCK#
CLUSTER              C_OBJ#
INDEX                I_OBJ#
INDEX                I_FILE#_BLOCK#
INDEX                I_TS#
INDEX                I_USER#
TABLE                UNDO$
TABLE                UET$
TABLE                FET$
TABLE                BOOTSTRAP$
TABLE                SEG$
TABLE                TAB$
TABLE                CLU$

15 rows selected.

SYS@book> select parameter,count,gets from v$rowcache where count>0;
PARAMETER                             COUNT       GETS
-------------------------------- ---------- ----------
dc_rollback_segments                      1          1
dc_objects                               30        101

--可以发现undo$在视图x$kglob已经可以看到。是否意味着我们这时可以执行select * from undo$;呢？测试看看。

SYS@book> select * from undo$;
select * from undo$
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

--依旧无法访问。把剩下的全部加载完成 58-15=43.
--窗口3(gdb)，按c 43继续：(注意这里43表示继续43次)
(gdb) c 43
Will ignore next 42 crossings of breakpoint 1.  Continuing.
Breakpoint 1, 0x0000000001c49932 in kqlobjlod ()

--窗口2(sqlplus)：

--这个时候查询会话会死掉(我的测试2次都出现这种情况)，必须在gdb下按c在执行1步。

SYS@book> select parameter,count,gets from v$rowcache where count>0;
PARAMETER                             COUNT       GETS
-------------------------------- ---------- ----------
dc_rollback_segments                      1          1
dc_objects                              120        671
global database name                      1          4

--已经加载到PARAMETER=global database name。这时可以执行select * from undo$;呢？测试看看。

SYS@book> select us#,name,user#,file#,block# from undo$ where rownum=1;
       US# NAME                      USER#      FILE#     BLOCK#
---------- -------------------- ---------- ---------- ----------
         0 SYSTEM                        0          1        128

--到这里实际上应该可以查询一些用户的表测试看看：
SYS@book> select * from scott.dept ;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
--ok没有问题。
SYS@book> select parameter,count,gets from v$rowcache where count>0;
PARAMETER                             COUNT       GETS
-------------------------------- ---------- ----------
dc_rollback_segments                      1          1
dc_segments                               3          5
dc_tablespaces                            1         14
dc_users                                  2         63
dc_objects                              126        833
global database name                      1          4
6 rows selected.

--可以发现dc_users增加1个，dc_tablespaces增加1个。而查询scott.t是否可以呢？

SYS@book> alter system checkpoint;
System altered.

SYS@book> select * from scott.t;
        ID NAME
---------- --------------------
         1 aaaa
         2 bbbb
--可以发现读取的结果是正确的。

SYS@book> select parameter,count,gets from v$rowcache where count>0;
PARAMETER                             COUNT       GETS
-------------------------------- ---------- ----------
dc_rollback_segments                      2          3
dc_segments                               4          8
dc_tablespaces                            1         14
dc_users                                  1         63
dc_objects                              127        806
global database name                      1          1
6 rows selected.
--dc_rollback_segments增加一个回滚段记数。也就是这个时候即使回滚段没有加载，读取信息时也可以使用使用回滚段构造数据。
--做一个ALTER SESSION SET EVENTS 'immediate trace name row_cache level 10';也可以确定：

BUCKET 33959:
  row cache parent object: address=0x7cb98920 cid=3(dc_rollback_segments)
  hash=f27284a6 typ=9 transaction=(nil) flags=00000002
  own=0x7cb989e8[0x7cb989e8,0x7cb989e8] wat=0x7cb989f8[0x7cb989f8,0x7cb989f8] mode=N
  status=VALID/-/-/-/-/-/-/-/-
  data=
  00000006 00000002 00000003 000000d0 535f0014 4d535359 315f3655 30333632
  39333233 00002432 00000000 00000000 00000003 00000001 00000378 00000cb8
  144fd8d0 00000003 00000000 00000002 00000000 f27284a6 7cb98920 00000000
  80958e28 00000000 80958e28 00000000
  BUCKET 33959 total object count=1

--转换5f 53 59 53 53 4d 55 36 5f 31 32 36 33 30 33 32 33 39 32 24
SYS@test> @conv_c 5f535953534d55365f3132363330333233393224
old   1: select utl_raw.cast_to_varchar2(lower('&1')) c60 from dual
new   1: select utl_raw.cast_to_varchar2(lower('5f535953534d55365f3132363330333233393224')) c60 from dual
C60
------------------------------------------------------------
_SYSSMU6_1263032392$

SYS@book> select us#,name,user#,file#,block# from undo$ where name ='_SYSSMU6_1263032392$';
       US# NAME                        USER#      FILE#     BLOCK#
---------- ---------------------- ---------- ---------- ----------
         6 _SYSSMU6_1263032392$            1          3        208
--正好对上。并且与前面的查询一致。
--看看是否这个时候可以实现dml操作。
SYS@book> insert into scott.t values (3,'cccc');
insert into scott.t values (3,'cccc')
                  *
ERROR at line 1:
ORA-01109: database not open

--窗口3(gdb)，按c不断继续：
--窗口2(sqlplus)：
SYS@book> select parameter,count,gets from v$rowcache where count>0;
PARAMETER                             COUNT       GETS
-------------------------------- ---------- ----------
dc_rollback_segments                     22         36
dc_segments                               6         14
dc_tablespaces                            3         19
dc_users                                  2         66
dc_objects                              152       1142
global database name                      1          5
6 rows selected.
--可以回滚段加载。

=====================
PARSING IN CURSOR #140528559513912 len=83 dep=1 uid=0 oct=3 lid=0 tim=1456796851670729 hv=855561815 ad='7cb64500' sqlid='a7pj5gstgxpkr'
select us#, status$, user#, ts#, spare1 from undo$ where ts# = :1 order by us# desc
END OF STMT
PARSE #140528559513912:c=0,e=343,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1456796851670728
BINDS #140528559513912:
Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7fcf5ab34270  bln=22  avl=02  flg=05
  value=2
EXEC #140528559513912:c=1000,e=837,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=1412949702,tim=1456796851671693
FETCH #140528559513912:c=0,e=34,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851671756
FETCH #140528559513912:c=0,e=17,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851671846
FETCH #140528559513912:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851671900
FETCH #140528559513912:c=999,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851671947
FETCH #140528559513912:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851671993
FETCH #140528559513912:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851672038
FETCH #140528559513912:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851672083
FETCH #140528559513912:c=0,e=11,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851672128
FETCH #140528559513912:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851672174
FETCH #140528559513912:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851672235
FETCH #140528559513912:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851672282
FETCH #140528559513912:c=0,e=12,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=1412949702,tim=1456796851672327
FETCH #140528559513912:c=0,e=10,p=0,cr=2,cu=0,mis=0,r=0,dep=1,og=4,plh=1412949702,tim=1456796851672370
STAT #140528559513912 id=1 cnt=12 pid=0 pos=1 obj=15 op='TABLE ACCESS BY INDEX ROWID UNDO$ (cr=26 pr=0 pw=0 time=33 us)'
STAT #140528559513912 id=2 cnt=21 pid=1 pos=1 obj=34 op='INDEX FULL SCAN DESCENDING I_UNDO1 (cr=13 pr=0 pw=0 time=35 us)'
--查询获取undo$信息。

PARSING IN CURSOR #140157475824208 len=142 dep=1 uid=0 oct=3 lid=0 tim=1456735293011966 hv=361892850 ad='7cb118b0' sqlid='7bd391hat42zk'
select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
END OF STMT
PARSE #140157475824208:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=906473769,tim=1456735293011965
BINDS #140157475824208:
Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f78f494bbd0  bln=22  avl=02  flg=05
  value=2
EXEC #140157475824208:c=0,e=160,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=906473769,tim=1456735293012285
FETCH #140157475824208:c=0,e=22,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=3,plh=906473769,tim=1456735293012338
STAT #140157475824208 id=1 cnt=1 pid=0 pos=1 obj=15 op='TABLE ACCESS BY INDEX ROWID UNDO$ (cr=2 pr=0 pw=0 time=15 us)'
STAT #140157475824208 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=0 pw=0 time=8 us)'
CLOSE #140157475824208:c=0,e=7,dep=1,type=0,tim=1456735293012434
=====================
PARSING IN CURSOR #140157475824208 len=160 dep=1 uid=0 oct=6 lid=0 tim=1456735293012530 hv=1292341136 ad='7cb10da0' sqlid='8vyjutx6hg3wh'
update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
END OF STMT
PARSE #140157475824208:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,plh=3078630091,tim=1456735293012529
BINDS #140157475824208:
Bind#0
  oacdty=01 mxl=32(20) mxlc=00 mal=00 scl=00 pre=00
  oacflg=18 fl2=0001 frm=01 csi=852 siz=32 off=0
  kxsbbbfp=7cb0c612  bln=32  avl=20  flg=09
  value="_SYSSMU2_2996391332$"
Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f78f494b788  bln=24  avl=02  flg=05
  value=3
...

Bind#12
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f78f494b7b8  bln=22  avl=02  flg=05
  value=2
EXEC #140157475824208:c=1000,e=818,p=0,cr=1,cu=3,mis=0,r=1,dep=1,og=3,plh=3078630091,tim=1456735293013506
STAT #140157475824208 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE  UNDO$ (cr=1 pr=0 pw=0 time=127 us)'
STAT #140157475824208 id=2 cnt=1 pid=1 pos=1 obj=34 op='INDEX UNIQUE SCAN I_UNDO1 (cr=1 pr=0 pw=0 time=8 us)'
CLOSE #140157475824208:c=0,e=8,dep=1,type=0,tim=1456735293013610
=====================

--内容比较多，过滤看看。

$  grep -i  -n 'undo\$'  book_ora_1278.trc | grep -v ":STAT"
677:CREATE TABLE UNDO$("US#"
827:CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320))
837:CREATE INDEX I_UNDO2 ON UNDO$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 35 EXTENTS (FILE 1 BLOCK 328))
4132:select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
4285:update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
4361:select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
4378:update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
4453:select /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1 from undo$ where us#=:1
4469:update /*+ rule */ undo$ set name=:2,file#=:3,block#=:4,status$=:5,user#=:6,undosqn=:7,xactsqn=:8,scnbas=:9,scnwrp=:10,inst#=:11,ts#=:12,spare1=:13 where us#=:1
6120:select us#, status$, user#, ts#, spare1 from undo$ where ts# = :1 order by us# desc
==

Mon Feb 29 16:52:24 2016
[61709] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:2730494188 end:2731145638 diff:651450 (6514 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Mon Feb 29 16:52:24 2016
SMON: enabling tx recovery
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Database Characterset is ZHS16GBK
--已经出现事件回滚恢复。实际上完成上面的加载以后，就是加载undo，启动回滚操作。

--这个时候做一些dml ，已经没有问题。

SYS@book> insert into scott.t values (3,'cccc');
1 row created.

SYS@book> commit ;
Commit complete.

SYS@book> alter system checkpoint;
System altered.
SYS@book> select rowid,t.* from scott.t ;
ROWID                      ID NAME
------------------ ---------- ---------------
AAAWRFAAEAAAAIcAAA          3 cccc
AAAWRFAAEAAAAIdAAA          1 aaaa
AAAWRFAAEAAAAIdAAB          2 bbbb

--在通过bbed观察：
BBED> set dba 4,541
        DBA             0x0100021d (16777757 4,541)

BBED> x /2rnc rowdata
rowdata[0]                                  @8166
----------
flag@8166: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8167: 0x00
cols@8168:    2

col    0[2] @8169: 2
col    1[4] @8172: bbbb

rowdata[11]                                 @8177
-----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179:    2

col    0[2] @8180: 1
col    1[4] @8183: aaaa

--记录已经回滚。

BBED> set dba 4,540
        DBA             0x0100021c (16777756 4,540)

BBED> x /1rnc rowdata
rowdata[0]                                  @8177
----------
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x01
cols@8179:    2
col    0[2] @8180: 3
col    1[4] @8183: cccc

--再按c继续在gdb界面下 ，直到窗口1 open命令完成。

--总结：
1.这个测试并不是一气呵成的，我自己做了几次。
2.还是有点乱。

--总结：
1.这个测试并不是一气呵成的，我自己做了几次。
2.还是有点乱。

3.补充：
--如果undo加载到row cache，也无法插入。
SYS@book> select parameter,count,gets from v$rowcache where count>0;
PARAMETER                             COUNT       GETS
-------------------------------- ---------- ----------
dc_rollback_segments                     22         26
dc_tablespaces                            2         16
dc_users                                  1         65
dc_objects                              150        947
global database name                      1          1

SYS@book> insert into scott.t values (5,'eeee');
insert into scott.t values (5,'eeee')
                  *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'

SYS@book> select * from v$rollname;
       USN NAME
---------- --------------------
         0 SYSTEM

--必须等到Undo 初始化完成。

Undo initialization finished serial:0 start:2797578388 end:2797889698 diff:311310 (3113 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
Tue Mar 01 11:24:58 2016
SMON: enabling tx recovery
Database Characterset is ZHS16GBK

 

SYS@book> select open_mode from v$database ;
OPEN_MODE
--------------------
READ WRITE
--这个时候查询视图v$database 数据库已经打开读写，虽然alter database open还没有执行完成。

SYS@book> select * from v$rollname;
       USN NAME
---------- ----------------------
         0 SYSTEM
         1 _SYSSMU1_3724004606$
         2 _SYSSMU2_2996391332$
         3 _SYSSMU3_1723003836$
         4 _SYSSMU4_1254879796$
         5 _SYSSMU5_898567397$
         6 _SYSSMU6_1263032392$
         7 _SYSSMU7_2070203016$
         8 _SYSSMU8_517538920$
         9 _SYSSMU9_1650507775$
        10 _SYSSMU10_1197734989$

11 rows selected.
SYS@book> insert into scott.t values (5,'eeee');
1 row created.

SYS@book> commit ;
Commit complete.

时间： 2024-10-06 19:44:24

[20160229]探究oracle的启动过程.txt的相关文章

oracle的启动过程

Oracle  的启动需要经历四个状态,SHUTDOWN .NOMOUNT .MOUNT .OPEN.       SHUTDOWN状态                                          第一状态没什么好解释的,oracle的所有文件都静静的躺在磁盘里,一切都还未开始,属于关机状态     NOMOUNT状态                             Starting the instance (nomount) *  Reading the ini

[20170315]简单探究dg的mrp进程.txt

[20170315]简单探究dg的mrp进程.txt --//昨天上午在做测试做在线日志与备用日志大小不一样时遇到一个令我困惑的问题,链接: http://blog.itpub.net/267265/viewspace-2135377/ --//发现mrp进程并没有打开数据文件的句柄.这样MRP进程如何应用日志的呢?简单探究看看: 1.环境: SYS@192.168.31.8/xxxxxx> @ &r/ver1 PORT_STRING                    VERSION  

典型的Liferay Server的启动过程

以下是一个典型的Liferay Server的启动过程: 01.May 29, 2012 3:09:32 AM org.apache.catalina.core.AprLifecycleListener init 02.INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.libra

Android启动过程深入解析

当按下Android设备电源键时究竟发生了什么? Android的启动过程是怎么样的? 什么是Linux内核? 桌面系统linux内核与Android系统linux内核有什么区别? 什么是引导装载程序? 什么是Zygote? 什么是X86以及ARM linux? 什么是init.rc? 什么是系统服务? 当我们想到Android启动过程时,脑海中总是冒出很多疑问.本文将介绍Android的启动过程,希望能帮助你找到上面这些问题的答案. Android是一个基于Linux的开源操作系统.x86(x

Oracle数据库启动ORA-08103错误提示解决办法

数据库在open过程报ORA-08103错误导致数据库无法正确启动  代码如下 复制代码 Fri Jul 18 22:02:51 2014 SMON: enabling tx recovery Fri Jul 18 22:02:51 2014 Errors in file d:＼oracle＼product＼10.2.0＼admin＼kemu3＼udump＼kemu3_ora_29788.trc: ORA-00604: ?? SQL ?? 1 ???? ORA-08103: ??????   F

[20170703]SQL语句分析执行过程.txt

[20170703]SQL语句分析执行过程.txt --//正常sql select语句执行需要这些过程,create cursor,parse,execute and fetch. --//dml估计缺少fetch步骤.参考vage的书写的例子,原书的例子存在问题,理解如下脚本对于sql语句如何执行很有益处. --//当然正常的编程很少有人这样写代码的. DECLARE    mcur     NUMBER;    mstat    NUMBER;    v_name   VARCHAR2 (

[20151110]Oracle Direct NFS Client.txt

[20151110]Oracle Direct NFS Client.txt --摘要链接:http://www.askmaclean.com/archives/setup-oracle-direct-nfs-client.html 在Oracle 11g中引入了Direct Network File System(Oracle Direct NFS)的新特性,通过一个打包在Oracle内核中的NFS客户机以改善实 例使用NFS时的性能,同时进一步完善了通过NFS实现RAC的解决方案.常规的NF

[20150228]启动问题.txt

[20150228]启动问题.txt --生产系统出现一个奇怪的问题,在服务器端看: $ rlsql SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 28 09:46:12 2015 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to an idle instance. SYS@test> quit Disconnected --奇怪服务端使用直接连接看不到

[20121021]探究表的rowdependencies属性.txt

[20121021]探究表的rowdependencies属性.txt 使用bbed来探索表的rowdependencies属性. 实际上默认这个功能是不打开的,也就是采用norowdependencies方式,这样block的所有行会共享同一个SCN,如果执行DML操作, 这个SCN都是一样的在同一块中.而使用rowdependencies.每行都会有对应事务的scn.使用ora_rowscn可以查询到块中记录的SCN. SQL> select * from v$version; BANNER