[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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2018607/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Android中Activity啟動過程探究Android
- Oracle的啟動過程Oracle
- Oracle啟動的三個過程Oracle
- oracle的內部啟動過程Oracle
- ORACLE啟動過程淺析Oracle
- ORACLE啟動過程簡析Oracle
- Oracle-解析啟動的全過程Oracle
- 深入理解ORACLE啟動過程Oracle
- Oracle資料庫啟動過程Oracle資料庫
- Angular的啟動過程Angular
- main的啟動過程AI
- Nginx的啟動過程Nginx
- 使用10046 來了解oracle的啟動過程Oracle
- Windows 啟動過程Windows
- app的啟動過程（三）APP
- App 啟動過程（含 Activity 啟動過程） | 安卓 offer 收割基APP安卓
- oracle資料庫啟動過程大揭祕Oracle資料庫
- Service啟動過程分析
- Activity啟動過程分析
- linux啟動過程Linux
- 【LINUX】啟動過程Linux
- 根Activity元件的啟動過程元件
- Android Service的啟動過程Android
- Android Activity的啟動過程Android
- iOS App 的完整啟動過程iOSAPP
- Linux的啟動過程(轉)Linux
- Windows啟動過程(MBR引導過程分析)Windows
- oracle資料庫的啟動與關閉過程詳解Oracle資料庫
- Oracle 資料庫例項啟動關閉過程Oracle資料庫
- js函式執行過程的探究JS函式
- 一張圖弄清Activity的啟動過程
- 走近原始碼：Redis的啟動過程原始碼Redis
- Cypress 本身啟動過程的除錯除錯
- Slackware的啟動(init)過程(轉)
- Linux的啟動過程介紹Linux
- Android App啟動過程AndroidAPP
- 計算機啟動過程計算機
- Liferay 啟動過程分析