\ncreate table tab$ \/* table table *\/\n( obj# number not null, \/* object number *\/\n dataobj# number, \/* data layer object number *\/\n ts# number not null, \/* tablespace number *\/\n file# number not null, \/* segment header file number *\/\n block# number not null, \/* segment header block number *\/\n<\/code><\/pre>\nTAB$ has all table definition and the TS#, FILE# and BLOCK# identifies uniquely the block where the segment header is. TS# identifies the tablespace. FILE# identifies the datafile within the tablespace. And BLOCK# is the offset within that file (there is only one block size for a tablespace). This is the physical identifier of a segment.<\/p>\n
Let’s take SCOTT.EMP as an example<\/p>\n
\nSQL> select object_id,object_type,data_object_id from dba_objects where owner='SCOTT' and object_name='EMP';\n \n OBJECT_ID OBJECT_TYPE DATA_OBJECT_ID\n---------- ----------------------- --------------\n 121515 TABLE 121533\n<\/code><\/pre>\nI get segment header from TAB$<\/p>\n
\nSQL> select ts#,file#,block# from sys.tab$ where obj#=121515;\n \n TS# FILE# BLOCK#\n---------- ---------- ----------\n 4 6 458\n<\/code><\/pre>\nThe extents listed in the tablespace header can be displayed from X$KTFBUE fixed table (if it were a Dictionary Managed Tablespace this information would be in the dictionary table UET$)<\/p>\n
\nSQL> select ktfbuefno,ktfbuebno,ktfbueblks from x$ktfbue where ktfbuesegtsn=4 and ktfbuesegfno=6 and ktfbuesegbno=458;\n \n KTFBUEFNO KTFBUEBNO KTFBUEBLKS\n---------- ---------- ----------\n 6 456 8\n<\/code><\/pre>\nFrom there we know that SCOTT.EMP data is stored in 8 blocks starting from block number 456 in file 6. This file number is relative to the tablespace, which is the tablespace number 4 because all segment extents are in the same tablespace as the segment header.<\/p>\n
BOOTSTRAP$<\/h3>\n
Ok, so you can get everything from the dictionary, starting from TAB$. But it’s a table. So if you want to read it you need to read blocks from it’s extents. Where are those extents?<\/p>\n
Easy, TAB$ itself has information in TAB$<\/p>\n
\nSQL> select obj# from obj$ where name='TAB$';\n \n OBJ#\n----------\n 4\n \nSQL> select ts#,file#,block# from sys.tab$ where obj#=4;\n \n TS# FILE# BLOCK#\n---------- ---------- ----------\n 0 1 144\n<\/code><\/pre>\nInformation is there: segment header is in tablespace 0 (which is SYSTEM) file 1 and block 144. And we can get extents from x$ktfbue where ktfbuesegtsn=0 and ktfbuesegfno=1 and ktfbuesegbno=144<\/p>\n
But wait a minute… Am I saying that in order to get TAB$ data you need first to get TAB$ data? This is not possible. It’s a Catch 22 here. TAB$ metadata about itself must be available before being able to read the table. This core dictionary table information must be hardcoded and this is done by special bootstrapping code. The dcore.bsq above is not a normal SQL script, but a Bootstrap SQL which has a special syntax. When those core dictionary objects are created, by CREATE DATABASE, their extents go into an hardcoded location in the SYSTEM datafile.<\/p>\n
And when an instance opens a database, the metadata about them is hardcoded in order to have the minimal information to be able to find the other dictionary information. This is done by pre-filling the dictionary cache with some bootstrap code. And this code is visible:<\/p>\n
\nSQL> select * from bootstrap$ where sql_text like 'CREATE TABLE TAB$%';\n \n LINE# OBJ# SQL_TEXT \n---------- ---------- ---------------------------------------------------------------------------------------------------------------------------------\n 4 4 CREATE TABLE TAB$(\"OBJ#\" NUMBER NOT NULL,\"DATAOBJ#\" NUMBER,\"TS#\" NUMBER NOT NULL,\"FILE#\" NUMBER NOT NULL,\"BLOCK#\" NUMBER NOT NULL\n ,\"BOBJ#\" NUMBER,\"TAB#\" NUMBER,\"COLS\" NUMBER NOT NULL,\"CLUCOLS\" NUMBER,\"PCTFREE$\" NUMBER NOT NULL,\"PCTUSED$\" NUMBER NOT NULL,\"INIT\n RANS\" NUMBER NOT NULL,\"MAXTRANS\" NUMBER NOT NULL,\"FLAGS\" NUMBER NOT NULL,\"AUDIT$\" VARCHAR2(38) NOT NULL,\"ROWCNT\" NUMBER,\"BLKCNT\" \n NUMBER,\"EMPCNT\" NUMBER,\"AVGSPC\" NUMBER,\"CHNCNT\" NUMBER,\"AVGRLN\" NUMBER,\"AVGSPC_FLB\" NUMBER,\"FLBCNT\" NUMBER,\"ANALYZETIME\" DATE,\"SA\n MPLESIZE\" NUMBER,\"DEGREE\" NUMBER,\"INSTANCES\" NUMBER,\"INTCOLS\" NUMBER NOT NULL,\"KERNELCOLS\" NUMBER NOT NULL,\"PROPERTY\" NUMBER NOT \n NULL,\"TRIGFLAG\" NUMBER,\"SPARE1\" NUMBER,\"SPARE2\" NUMBER,\"SPARE3\" NUMBER,\"SPARE4\" VARCHAR2(1000),\"SPARE5\" VARCHAR2(1000),\"SPARE6\" D\n ATE) STORAGE ( OBJNO 4 TABNO 1) CLUSTER C_OBJ#(OBJ#) \n<\/code><\/pre>\nThe code to create TAB$ is there with additional bootstrapping SQL syntax to hardcoded the OBJECT_ID. This table is actually stored in a CLUSTER segment, and its definition is also harcoded in bootstrapping code:<\/p>\n
\nSQL> select * from bootstrap$ where sql_text like 'CREATE CLUSTER C_OBJ#%';\n \n LINE# OBJ# SQL_TEXT \n---------- ---------- --------------------------------------------------------------------------------\n 2 2 CREATE CLUSTER C_OBJ#(\"OBJ#\" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS 25\n 5 STORAGE ( INITIAL 136K NEXT 200K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREA\n SE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 144)) SIZE 800 \n<\/code><\/pre>\nFile 1 Block 144 this is exactly what we have seen when querying TAB$ but actually it’s an hardcoded value. You will find exactly the same in all Oracle Database since 8.0<\/p>\n
It seems that it was a different value in Oracle 7:
\n![\"CaptureBOOTSTRAP\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureBOOTSTRAP.png\")
<\/a>
\nI don’t remember that upgrade to 8.0 had to update the first blocks of SYSTEM datafiles, but it was probably the case. If anyone has a database that has been upgraded since Oracle 7 then please tell me, but that should be rare nowadays. But for sure the code that warms up the dictionary cache must be consistent with how those segments are stored in the SYSTEM tablespace.<\/p>\nControlfile<\/h3>\n
Let’s continue. We know how to read data as soon as the database is opened because all required information is in the SYSTEM datafile. <\/p>\n
But how do we know where is this datafile? The controlfile knows the location of all datafiles and this information is available since the database mount stage<\/p>\n
Here is an example after alter database backup controlfile to trace;<\/p>\n
\nCREATE CONTROLFILE REUSE DATABASE \"RACDB\" NORESETLOGS NOARCHIVELOG\n MAXLOGFILES 192\n MAXLOGMEMBERS 3\n MAXDATAFILES 1024\n MAXINSTANCES 32\n MAXLOGHISTORY 292\nLOGFILE\n GROUP 1 '+DATA\/RACDB\/ONLINELOG\/group_1.262.906247575' SIZE 50M BLOCKSIZE 512,\n GROUP 2 '+DATA\/RACDB\/ONLINELOG\/group_2.263.906247575' SIZE 50M BLOCKSIZE 512,\n GROUP 3 '+DATA\/RACDB\/ONLINELOG\/group_3.264.906247575' SIZE 50M BLOCKSIZE 512,\n GROUP 4 '+DATA\/RACDB\/ONLINELOG\/group_4.274.906299789' SIZE 50M BLOCKSIZE 512,\n GROUP 5 '+DATA\/RACDB\/ONLINELOG\/group_5.275.906299789' SIZE 50M BLOCKSIZE 512,\n GROUP 6 '+DATA\/RACDB\/ONLINELOG\/group_6.276.906299789' SIZE 50M BLOCKSIZE 512\n-- STANDBY LOGFILE\nDATAFILE\n '+DATA\/RACDB\/DATAFILE\/system.258.906247493',\n '+DATA\/RACDB\/DATAFILE\/sysaux.257.906247463',\n '+DATA\/RACDB\/DATAFILE\/undotbs1.260.906247529',\n<\/code><\/pre>\nNow the question is:
\nWhere is the controlfile? This information is known from the instance parameter:<\/p>\n
\nSQL> show parameter control_files\n \nNAME TYPE VALUE\n------------------------------------ ----------- ---------------------------------------------\ncontrol_files string +DATA\/RACDB\/CONTROLFILE\/current.261.906247573\n<\/code><\/pre>\nOh good. I’m in ASM so I can continue my path, with question such as: how do we find the controlfile?<\/p>\n
But before that, where this instance parameter comes from? SPFILE is read at instance startup.<\/p>\n
Where is the SPFILE?<\/p>\n
Database SPFILE<\/h3>\n\nSQL> show parameter spfile\n \nNAME TYPE VALUE\n------------------------------------ ----------- ----------------------------------------------\nspfile string +DATA\/RACDB\/PARAMETERFILE\/spfile.269.906247759\n<\/code><\/pre>\nIn single instance, the SPFILE is found in $ORACLE_HOME\/dbs but here I’m in RAC and the database resource has this information:<\/p>\n
\n[oracle@racp1vm1 ~]$ srvctl config database -db RACDB\nDatabase unique name: RACDB\nDatabase name: RACDB\nOracle home: \/u01\/app\/oracle\/product\/12.1.0\/dbhome_1\nOracle user: oracle\nSpfile: +DATA\/RACDB\/PARAMETERFILE\/spfile.269.906247759\n<\/code><\/pre>\nASM<\/h3>\n
So in order to find the datafiles or the SPFILE you must have access to the +DATA diskgroup. The information about it is available from the ASM instance and this is easy to find because you have only one asm instance on the server.<\/p>\n
But now, let’s go to the ASM instance. It stores all metadata in the disks that are accessible once the instance has started. What does it need to start and to find those disks?
\nThe ASM instance has a SPFILE and information from the SPFILE is mandatory to access the diskgroups. So where is the SPFILE of the ASM instance?<\/p>\n
\n[grid@racp1vm1 ~]$ asmcmd spget\n+CRS_DG\/ws-dbi-scan1\/ASMPARAMETERFILE\/registry.253.905527691\n<\/code><\/pre>\nOkay… another Catch 22 here. the ASM SPFILE is stored in ASM and you need an ASM instance to access to it… but starting an ASM instance needs SPFILE information…<\/p>\n
GPnP profile<\/h3>\n