Research on Bootstrap $, data dictionary structure information and open correlation

Environment:

00:24:16 sys@ORCL (^ω^) select * from v$version where rownum=1;BANNER--------------------------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

To query a table in our system, we first need to find the structural information of the table in the data dictionary. These structural information is stored in the data dictionary table, but where can we obtain the structural information of the data dictionary table itself? Although we can query the structural information of the dictionary table from the table, how do we first determine the structure when the database is open? Is it written in a program? Or how? How Does Oracle find physical data in the system tablespace?

23:48:02 sys @ orcl (^ ω ^) shutdown the immediate database has been disabled. The database has been detached. The Oracle routine has been disabled. 23:48:54 sys @ orcl (^ ω ^) Startup mountoracle routine has been started. Total system global area 612368384 bytesfixed size 1250428 bytesvariable size 192940932 bytesdatabase buffers 411041792 bytesredo buffers 7135232 bytes database loaded. 23:49:20 sys @ orcl (^ ω ^) alter session set SQL _trace = true; the session has been changed. 23:49:44 sys @ orcl (^ ω ^) alter database open; the database has been changed.

The TRC file is as follows:

=====================PARSING IN CURSOR #1 len=19 dep=0 uid=0 oct=35 lid=0 tim=18716803687 hv=1907384048 ad='33ecf028'alter database openEND OF STMTPARSE #1:c=0,e=1749,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=18716803683=====================

Create the first object:

PARSING IN CURSOR #2 len=188 dep=1 uid=0 oct=1 lid=0 tim=18717747631 hv=1365064427 ad='33ec080c'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))END OF STMTPARSE #2:c=0,e=549,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=18717747628EXEC #2:c=0,e=195,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=18717750171=====================

Gradually extract content to build the structure of the data dictionary table

PARSING IN CURSOR #2 len=55 dep=1 uid=0 oct=3 lid=0 tim=18717751282 hv=2111436465 ad='33ec0098'select line#, sql_text from bootstrap$ where obj# != :1END OF STMTPARSE #2:c=0,e=538,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=18717751278EXEC #2:c=0,e=35732,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=18717789730FETCH #2:c=0,e=1528,p=4,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=18717792281FETCH #2:c=0,e=43,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=18717793026FETCH #2:c=0,e=34,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=18717793574..................................

First, observe Bootstrap $:

00:05:00 sys @ orcl (^ ω ^) DESC Bootstrap $ is the name blank? Type -------- ------------------------------ line # not null number OBJ # not null number SQL _text not null varchar2 (4000) 00:05:42 sys @ orcl (^ ω ^) Select count (1) From Bootstrap $; count (1) ---------- 5700: 05: 55 sys @ orcl (^ ω ^) Select OBJ #, SQL _text from Bootstrap $ where rownum <11; OBJ # ---------- SQL _text limit ---------------------------------------------------------------- -----------------18.0.0.0.0 0 create rollback segment system storage (initial 112 K next 1024 K minextents 1 maxextents 32765 objno 0 extents (File 1 block 9 )) 20 create table icol $ ("OBJ #" number not null, "bo #" number not null, "Col #" number no OBJ # ---------- SQL _TEXT--------------------------------------------------------------------------------T null, "POS #" number not null, "segcol #" number not null, "Segcollength" number not null, "offset" number not null, "intcol #" number not null, "spare1" number, "spare2" number, "spare3" number, "spare4" varchar2 (1000), "spare5" varchar2 (1000), "spare6" date) Storage (objno 20 tabno 4) Cluster c_obj # (Bo #) 40 create index I _icol1 on icol $ (OBJ #) pctfree 10 initrans 2 maxtrans 255 storage (initial 64 K next 1024 K minextents 1 maxextents 2147483645 pctincrease 0 objn O40 extents (File 1 block 249) OBJ # ---------- SQL _text limit 28 CREATE TABLE con $ ("Owner #" number not null, "name" varchar2 (30) not null, "con #" number not null, "spare1" number, "spare2" number, "spare3" number, "spare4" varchar2 (1000), "spare5" varchar2 (1000 ), "spare6" date) pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (I Nitial 64 K next 1024 K minextents 1 maxextents 2147483645 pctincrease 0 objno 28 extents (File 1 block 169) 48 OBJ # ---------- SQL _TEXT--------------------------------------------------------------------------------CREATE unique index I _con1 on Con $ (owner #, name) pctfree 10 initrans 2 maxtrans 255 storage (initial 64 K next 1024 K minextents 1 maxextents 2147483645 pctincrease 0 objno 48 extents (File 1 block 313) 49 create unique index I _con2 on Con $ (con #) pctfree 10 initrans 2 maxtrans 255 storage (initial 64 K next 1024 K minextents 1 maxextents 2147483645 pctincrease 0 objno 49 extents (File 1 block 321 )) OBJ # ---------- SQL _text limit 15 CREATE TABLE undo $ ("us #" number not null, "name" varchar2 (30) not null, "user #" numbe R not null, "file #" number not null, "block #" number not null, "scnbas" number, "scnwrp" number, "xactsqn" number, "undosqn" number, "inst #" number, "Status $" number not null, "ts #" number, "ugrp #" number, "keep" number, "Optimal" number, "Flags" number, "spare1" number, "spare2" number, "spare3" number, "spare4" varchar2 (1000), "spare5" varchar2 (1000 ), "spare6" date) pctfree 10 pctused 40 initrans 1 maxtrans 255 St Orage (initial 64 K next 1024 K minextents 1 maxextents 2147483645 pctincrease 0 objno 15 extents (File 1 Block 105 )) OBJ # ---------- SQL _text -------------------------------------------------------------------------------- 34 create unique index I _undo1 on undo $ (US #) pctfree 10 initrans 2 maxtrans 255 storage (initial 64 K next 1024 K minextents 1 maxextents 2147483645 pctincrease 0 objno 34 extents (Fi Le 1 block 201) 35 create index I _undo2 on undo $ (name) pctfree 10 initrans 2 maxtrans 255 storage (initial 64 K next 1024 K minextents 1 maxextents 2147483645 pctincrease 0 objno OBJ # ---------- SQL _TEXT--------------------------------------------------------------------------------35 extents (File 1 block 209) has selected 10 rows.

From this we can see that Bootstrap $ actually records the creation statements of some basic objects in the database system. For our database, we can also regard it as creating a table structure. Through this structure, we can use relational databases to obtain data in files.

 

Continue TRC File Content

================================== Create a system rollback segment in the header of the database system tablespace (Block 9 starts, 1---8 is a data file header) parsing in cursor #2 Len = 129 Dep = 1 uid = 0 Oct = 36 lid = 0 Tim = 18717824579 HV = 0 ad = '3e5398 'create rollback segment system storage (initial 112 K next 1024 K minextents 1 maxextents 32765 objno 0 extents (File 1 block 9 )) end of stmtparse #2: C = 0, E = 347, P = 0, Cr = 0, Cu = 0, MIS = 1, r = 0, DEP = 1, OG = 4, Tim = 18717824575 exec #2: C = 0, E = 111, P = 0, Cr = 0, Cu = 0, MIS = 0, r = 0, dep = 1, OG = 4, Tim = 18717826689 ======================

Here, Block 25 is directly specified for the segment header of the cluster.

Parsing in cursor #2 Len = 209 Dep = 1 uid = 0 Oct = 4 lid = 0 Tim = 18717827549 HV = 0 ad = '3e5398' create cluster c_obj # ("OBJ #" number) pctfree 5 pctused 40 initrans 2 maxtrans 255 storage (initial 136 K next 1024 K minextents 1 maxextents 2147483645 pctincrease 0 objno 2 extents (File 1 Block 25) size 800end of stmtparse #2: C = 0, E = 336, P = 0, Cr = 0, Cu = 0, MIS = 1, r = 0, DEP = 1, OG = 4, tim = 18717827546 exec #2: C = 0, E = 125, P = 0, Cr = 0, c U = 0, MIS = 0, r = 0, DEP = 1, OG = 4, Tim = 1871782973600: 19: 30 sys @ orcl (^ ω ^) Select file_id, block_id from dba_extents where segment_name = 'C _ OBJ #'; file_id block_id ---------- 1 25 1 33 1 41 1 3665 1 4929 1 5841 1 6105 1 6129 1 6153 1 6169 1 6193 1 6217 1 6249 1 6265 1 6281 1 6313 1 6409 1 7817 1 1 1 1 14601 1 27145 1 48905 1 56585 22 rows selected.

As we can see, from tab $, the storage has changed: storage (objno 4 tabno 1). What does objno 4 tabno 1 mean?

Parsing in cursor #2 Len = 827 Dep = 1 uid = 0 Oct = 1 lid = 0 Tim = 18717834472 HV = 4071397944 ad = '33ebc484 '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 #) End of stmtparse #2: C = 0, E = 633, P = 0, cr = 0, Cu = 0, MIS = 1, r = 0, DEP = 1, OG = 4, Tim = 18717834468 exec #2: C = 0, E = 169, P = 0, Cr = 0, Cu = 0, MIS = 0, r = 0, DEP = 1, OG = 4, Tim = 1871783626200: 19: 42 sys @ orcl (^ ω ^) Select * From tab $ where OBJ # = 4 and Tab # = 1; OBJ # dataobj # ts # file # block # bobj # tab # ---------- ------------ ---------- hour ------------ hour Cols hour $ hour flags ---------- ------------ audit $ hour ---------------------------------------------------------------------------- rowcnt too many threads ---------- ------------ analyze analyzetime samplesize too many threads ---------- too ------------ too -------- ------------ property has spare1 spare2 spare3 too many other companies SPARE4--------------------------------------------------------------------------------SPARE5--------------------------------------------------------------------------------SPARE6 -------------- 4 2 0 1 25 2 1 37 1 0 0 0 529 ------------------------------------------ 1637 862 135 0 0 1637 0 15 -9 month-12 1024 37 0 030-8 month-05

From here, we can see that the storage parameters are retrieved from the records of fixed tables in the data files from here.

Then, we can look at the trace file and find that the database can extract the structure information and data information from the system tablespace file through the existing information.

We can see from the above content that the program only needs to provide the bootstrap $ creation script to determine the structure of bootstap $ and specify the position of the segment header, then we can get the content in Bootstrap $ through the field header, and the content is to create SQL _text for some dictionary tables. This is because Bootstrap $ is a natural egg, when c_obj #, Tab $, and so on are incubated, the chickens continue to generate eggs, and the database dictionary table structure is established. Therefore, bootstrap $ is the first table in Oracle! Each time the database is started, create Bootstrap $ to create the structure of the dictionary table, and then obtain the data file information based on Bootstrap $. Create
Bootstrap $ is performed in the memory. It is not a real physical object. The object created in the memory exactly corresponds to the object on the physical disk, which is fixed.

 

That is to say, Oracle uses Relational Tables to establish its own structure.

 

System tablespace, sysaux tablespace, and other physical objects already exist. The "CREATE" we do is to create such a corresponding structure in SGA, then we can read the data according to the characteristics of the table. To generate a tab $ structure, the SQL statement of this structure comes from Bootstrap $. At the beginning, Oracle only needs to have the structure information of Bootstrap $, if a structure is created in SGA, you can get the bootstrap $ content from system tablespace immediately, and then gradually create the dictionary table structure in SGA. Go to System
The bootstrap $ content read by the tablespace. These SQL statements do not need to be reused or related data dictionary information (or related data dictionary information ). The header of any data structure is always relatively fixed. The database is in a fixed position in the system.

 

The boot process is as follows:

1. When the system starts, obtain the location of each data file based on the control file information.
2. Obtain the root DBA address (file_id = 1, block_id = 417) based on the file header of file # = 1 (the first data file in system tablespace ).
3. Based on the Root DBA information, get the storage location of the segment corresponding to bootstrap $ (file_id = 1, block_id = 377) to get the hen started by the system.
4. Based on the information in the bootstrap $ table, obtain the system table creation information in the system to complete the process of giving birth to the main chicken ..
5. process the further processing after the main table is generated, generate the main data dictionary information, and start the related undo segment (rollback segments ).
6. The database has been started.

 

A database is a logical concept, but it is implemented through the physical storage of disks. How can we connect the logic with the physical storage? A hook is required to link the two together. In fact, the system dictionary exists after the database is created. The reason why the database is logical is to understand and use it well. During the database startup process, you need to hook up the logical database and physical storage through the hook, Which is Bootstrap $. As for its starting address, it is determined when the database is created, I think it may be written somewhere on the disk. Different databases may be different. only some dictionary tables were created before open, because those tables do not need to read the information in the tablespace or data file, but are obtained from other places, such as initialization parameters or control files, the entire startup process is to abstract physical storage into a logical database.