ITPub博客

首页 > 数据库 > Oracle > [20160229]探究oracle的启动过程.txt

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

Oracle 作者:lfree 时间:2016-03-01 10:44:42 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2470
  • 访问量
    6277389