关于数据库open的深入探究

biti_rainy

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area  131142648 bytes
Fixed Size                   451576 bytes
Variable Size             104857600 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> alter session set sql_trace = true;

Session altered.

SQL> alter database open;

Database altered.

SQL>

首先我们来参考跟踪文件的前部分(参考附件)
这是第一个对象的创建
create table bootstrap$ ( line#         number not null,   obj#           
  number not null,   sql_text   varchar2(4000) not null)   storage (initial
  50K objno 56 extents (file 1 block 377))

接下来我们来看执行的是
select line#, sql_text
from
bootstrap$ where obj# != :1
(这里实际上是逐步提取内容建立字典表本身的结构,根据后面内容可知)


那么在这里我们在数据库中来看看 bootstap$
SQL> desc bootstrap$
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
LINE#                                     NOT NULL NUMBER
OBJ#                                      NOT NULL NUMBER
SQL_TEXT                                  NOT NULL VARCHAR2(4000)

SQL>

SQL> select count(*) from bootstrap$;
        57

SQL>

SQL> select  obj#,sql_text from bootstrap$ where rownum<11;
        -1
8.0.0.0.0

         0
CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL 112K NEXT 1024K MINEXTENTS 1 M
AXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 9))

         8
CREATE CLUSTER C_FILE#_BLOCK#("TS#" NUMBER,"SEGFILE#" NUMBER,"SEGBLOCK#" NUMBER)
PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 24K NEXT 1024K
MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 8 EXTENTS (FILE 1 BLOCK
73)) SIZE 225

         9
CREATE INDEX I_FILE#_BLOCK# ON CLUSTER C_FILE#_BLOCK# PCTFREE 10 INITRANS 2 MAXT
RANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PC
TINCREASE 0 OBJNO 9 EXTENTS (FILE 1 BLOCK 81))

        14
CREATE TABLE SEG$("FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"TYPE#" NUMBE
R NOT NULL,"TS#" NUMBER NOT NULL,"BLOCKS" NUMBER NOT NULL,"EXTENTS" NUMBER NOT N
ULL,"INIEXTS" NUMBER NOT NULL,"MINEXTS" NUMBER NOT NULL,"MAXEXTS" NUMBER NOT NUL
L,"EXTSIZE" NUMBER NOT NULL,"EXTPCT" NUMBER NOT NULL,"USER#" NUMBER NOT NULL,"LI
STS" NUMBER,"GROUPS" NUMBER,"BITMAPRANGES" NUMBER NOT NULL,"CACHEHINT" NUMBER NO
T NULL,"SCANHINT" NUMBER NOT NULL,"HWMINCR" NUMBER NOT NULL,"SPARE1" NUMBER,"SPA
RE2" NUMBER) STORAGE (  OBJNO 14 TABNO 2) CLUSTER C_FILE#_BLOCK#(TS#,FILE#,BLOCK
#)

         5
CREATE TABLE CLU$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL
,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"COLS" NUMBER NOT NULL,"PCTFRE
E$" NUMBER NOT NULL,"PCTUSED$" NUMBER NOT NULL,"INITRANS" NUMBER NOT NULL,"MAXTR
ANS" NUMBER NOT NULL,"SIZE$" NUMBER,"HASHFUNC" VARCHAR2(30),"HASHKEYS" NUMBER,"F
UNC" NUMBER,"EXTIND" NUMBER,"FLAGS" NUMBER,"DEGREE" NUMBER,"INSTANCES" NUMBER,"A
VGCHN" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" NUMBER,"S
PARE5" VARCHAR2(1000),"SPARE6" VARCHAR2(1000),"SPARE7" DATE) STORAGE (  OBJNO 5
TABNO 2) CLUSTER C_OBJ#(OBJ#)

         6
CREATE CLUSTER C_TS#("TS#" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 2 MAXTRANS 255
STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREAS
E 0 OBJNO 6 EXTENTS (FILE 1 BLOCK 57))

         7
CREATE INDEX I_TS# ON CLUSTER C_TS# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (
  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO
7 EXTENTS (FILE 1 BLOCK 65))

        18
CREATE TABLE OBJ$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"OWNER#" NUMBER NOT N
ULL,"NAME" VARCHAR2(30) NOT NULL,"NAMESPACE" NUMBER NOT NULL,"SUBNAME" VARCHAR2(
30),"TYPE#" NUMBER NOT NULL,"CTIME" DATE NOT NULL,"MTIME" DATE NOT NULL,"STIME"
DATE NOT NULL,"STATUS" NUMBER NOT NULL,"REMOTEOWNER" VARCHAR2(30),"LINKNAME" VAR
CHAR2(128),"FLAGS" NUMBER,"OID$" RAW(16),"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3
" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE
10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 16K NEXT 1024K MINEXTEN
TS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 18 EXTENTS (FILE 1 BLOCK 121))

        36
CREATE UNIQUE INDEX I_OBJ1 ON OBJ$(OBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STOR
AGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 O
BJNO 36 EXTENTS (FILE 1 BLOCK 217))


10 rows selected.

SQL>

由这里我们可以看出，bootstrap$ 中实际上是记录了一些数据库系统基本对象的创建语句
那对于我们数据库来说，也可以看做建立一个表结构，通过这个结构可以通过关系型数据库的方式去获取文件中数据

ok,接下来我们看看trace文件中的内容，发现

CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL 112K NEXT 1024K MINEXTENTS
  1 MAXEXTENTS 32765 OBJNO 0 EXTENTS (FILE 1 BLOCK 9))   

在数据库系统表空间的头部创建了系统回滚段（block 9 开始，1---8  属于数据文件头）

再往下
CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS
  255 STORAGE (  INITIAL 136K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 25)) SIZE 800
这里同样是直接指定了该cluster 的段头位置  block 25


SQL> select file_id,block_id from dba_extents where segment_name = 'C_OBJ#';
         1         25
         1         33
         1         41
         1       3241
         1       4441
         1       4473
         1       4489
         1       4513
         1       4529
         1       4561
         1       4585
         1       4609
         1       4641
         1       4681
         1       4753
         1       4833
         1       4873
         1      12681
         1      23689
         1      26249

20 rows selected.




CREATE INDEX I_OBJ# ON CLUSTER C_OBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 OBJNO 3 EXTENTS (FILE 1 BLOCK 49))


CREATE TABLE TAB$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT
  NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"BOBJ#" NUMBER,"TAB#"
  NUMBER,"COLS" NUMBER NOT NULL,"CLUCOLS" NUMBER,"PCTFREE$" NUMBER NOT NULL,
  "PCTUSED$" NUMBER NOT NULL,"INITRANS" NUMBER NOT NULL,"MAXTRANS" NUMBER NOT
  NULL,"FLAGS" NUMBER NOT NULL,"AUDIT$" VARCHAR2(38) NOT NULL,"ROWCNT" NUMBER,
  "BLKCNT" NUMBER,"EMPCNT" NUMBER,"AVGSPC" NUMBER,"CHNCNT" NUMBER,"AVGRLN"
  NUMBER,"AVGSPC_FLB" NUMBER,"FLBCNT" NUMBER,"ANALYZETIME" DATE,"SAMPLESIZE"
  NUMBER,"DEGREE" NUMBER,"INSTANCES" NUMBER,"INTCOLS" NUMBER NOT NULL,
  "KERNELCOLS" NUMBER NOT NULL,"PROPERTY" NUMBER NOT NULL,"TRIGFLAG" NUMBER,
  "SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),
  "SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE (  OBJNO 4 TABNO 1) CLUSTER
  C_OBJ#(OBJ#)

我们可以看到，从tab$ 开始，存储发生了变化，
STORAGE (  OBJNO 4 TABNO 1) CLUSTER   C_OBJ#(OBJ#)
那这里的 objno 4 tabno 1 表示什么意思呢?(通过 cluster   C_OBJ#(OBJ#)我们就可以找到表tab$自身)


SQL> select * from tab$ where obj# = 4 and tab# = 1;

      OBJ#   DATAOBJ#        TS#      FILE#     BLOCK#      BOBJ#       TAB#
---------- ---------- ---------- ---------- ---------- ---------- ----------
      COLS    CLUCOLS   PCTFREE$   PCTUSED$   INITRANS   MAXTRANS      FLAGS
---------- ---------- ---------- ---------- ---------- ---------- ----------
AUDIT$                                     ROWCNT     BLKCNT     EMPCNT
-------------------------------------- ---------- ---------- ----------
    AVGSPC     CHNCNT     AVGRLN AVGSPC_FLB     FLBCNT ANALYZETI SAMPLESIZE
---------- ---------- ---------- ---------- ---------- --------- ----------
    DEGREE  INSTANCES    INTCOLS KERNELCOLS   PROPERTY   TRIGFLAG     SPARE1
---------- ---------- ---------- ---------- ---------- ---------- ----------
    SPARE2     SPARE3
---------- ----------
SPARE4
--------------------------------------------------------------------------------
SPARE5
--------------------------------------------------------------------------------
SPARE6
---------
         4          2          0          1         25          2          1
        37          1          0          0          0          0         17
--------------------------------              921        428         83
      2381          0        139       6368          7 11-AUG-03        921
                              37         37       1024          0          0



06-AUG-03


SQL>

由这里可以看出，存储参数从这里开始，就从数据文件中固定表的记录中取出来
由此再往trace文件看下去，就会发现这个时候数据库已经可以通过已有信息从系统表空间文件中提取结构信息和数据信息
我们看上面关于存储的定义
CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS
  255 STORAGE (  INITIAL 136K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 25)) SIZE 800
这里的extents 指明了是 block 25,我们假设在初始化参数文件中db_block_size设置不当，则必然导致文件位置定位错误


之所以要来研究这个问题（以前研究了今天写出来而已），是因为最早思考这样一个问题

我们要查询自己系统的一个表，则首先是去数据字典中找到该表的结构性信息
这些结构性信息存储在数据字典表中，但我们又从哪里获得数据字典表本身的结构性信息呢？

虽然从表中我们可以查询到字典表本身的结构性信息，那在数据库open的时候最初是如何确定结构的？
是写死在程序中呢？还是怎样处理的

由上面内容我们可以看出，程序中只要提供 bootstrap$ 的创建脚本，确定好bootstap$的结构同时指定了段头的位置
然后就能通过段头去获取bootstrap$中的内容，而这些内容就是为一些字典表创建结构的sql_text
这样就因了bootstrap$这个天生的蛋，孵化出了c_obj#,tab$等等鸡，这些鸡又继续生蛋，则完成了数据库字典表结构的建立

也就是说，oracle用关系型表来实现了自身结构的建立


trace 结果参考附件

eygle

这个东西是很有意思的，有助于深入理解数据库的本质

开天辟地，这就是那个1啊!

dodola

向這種求真，求實的人致敬﹗

ocp8i

在家用小猫看完这贴！

Fenng

和操作系统的 init  很神似

这帖子不错！

Yong Huang

最初由 biti_rainy 发布
[B]...
CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS
  255 STORAGE (  INITIAL 136K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 25)) SIZE 800
这里的extents 指明了是 block 25,我们假设在初始化参数文件中db_block_size设置不当，则必然导致文件位置定位错误
[/B]

Very impressive research!

By "我们假设在初始化参数文件中db_block_size设置不当，则必然导致文件位置定位错误", I think you mean if db_block_size is 4k, you can't say 8k in spfile (or pfile), otherwise you would get database corruption. Of course nobody would be so insane to do so. But I just tried it on my test database (9.2.0.1.0 on XP). I was not able to startup. I got ORA-00209: control file blocksize mismatch, check alert log for more info. So looks like even if you want to change db_block_size, you cannot.

By the way, the create cluster SQL with objno and extents (file block) in the storage clause looks interesting. I tried to use those keywords but always get ORA-900 invalid SQL statement. There're some operations Oracle internally can do but we cannot in SQL*Plus. This is one example. Others are like alter database dismount or alter database close (the latter may work with anomaly), which you see in alert.log, and select piece from sys.idl_sb4$ (sometimes seen in v$sql or SQL trace).

Yong Huang

eygle

最初由 Yong Huang 发布
[B]

There're some operations Oracle internally can do but we cannot in SQL*Plus. This is one example. Others are like alter database dismount or alter database close (the latter may work with anomaly), which you see in alert.log, and select piece from sys.idl_sb4$ (sometimes seen in v$sql or SQL trace).

Yong Huang [/B]

From Oracle9i,Following command can be use in sql*plus under some condition:
alter database close;
alter database dismount;

For example:



SQL> startup open;
ORACLE instance started.

Total System Global Area  131142648 bytes
Fixed Size                   451576 bytes
Variable Size             104857600 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
PL/SQL Release 9.2.0.3.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.3.0 - Production
NLSRTL Version 9.2.0.3.0 - Production

SQL>
SQL> alter database close;

Database altered.

SQL> alter database dismount;

Database altered.

SQL> alter database mount;

Database altered.

SQL> shutdown         
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

Yong Huang

最初由 eygle 发布
[B]
From Oracle9i,Following command can be use in sql*plus under some condition:
alter database close;
alter database dismount;
...
SQL> alter database close;

Database altered.

SQL> alter database dismount;

Database altered.

SQL> alter database mount;

Database altered.

SQL> shutdown         
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down. [/B]

Actually, I really should say "alter database close" or "dismount" works without complaint from Oracle. But they don't seem to be the exact reverse of alter database open and mount, respectively. For instance, (tested in 8.1.7.0 and 9i both releases), after these commands, you can't alter database open (if dismounted, you have to mount first of course). Alter database open would return ORA-1531 ("a database already open by the instance". I wish it was just me that didn't find the trick yet. It would be nice to have exactly the reverse of nomount, mount and open for startup command.

By the way, info in sys.idl_sb4$ seems to be available only by dumping data blocks. Pete Finnigan (petefinnigan.com) told me UPI programming may reveal the content. He's an Oracle security guy.

Yong Huang

Yong Huang

最初由 biti_rainy 发布
[B]首先我们来参考跟踪文件的前部分(参考附件)
这是第一个对象的创建
create table bootstrap$ ( line#         number not null,   obj#           
  number not null,   sql_text   varchar2(4000) not null)   storage (initial
  50K objno 56 extents (file 1 block 377))
...
[/B]

biti_rainy's research worked up my appetite. In case biti knows, (what's biti's real name anyway!), do you know what Oracle really does to the physical database? By that I mean, Oracle apparently did NOT really create the bootstrap$, tab$ and others every time the database is opened. Otherwise, a trace of database shutdown should show corresponding drop of those tables and clusters. I traced it and didn't find those drop statements. It's also hard to believe a potentially huge table tab$ is always recreated whenever the database is opened. So those tables bootstrap$, tab$ etc. "created" at the database open time are already in SYSTEM tablespace. But SGA needs information for the instance to work properly. However, even that is a puzzle to me, because you won't find those create table bootstrap$ statement in library cache (v$sql) and there's no alter system flush shared pool in there either.

Yong Huang

Fenng

[php]
create table bootstrap$
( line#         number not null,                       /* statement order id */
  obj#          number not null,                            /* object number */
  sql_text      varchar2("M_VCSZ" not null)                    /* statement */
  storage (initial 50K)            /* to avoid space management during IOR I */
//                                            /* "//" required for bootstrap */


[/php]