Roads ? - where we're going, we don't need roads ...
Month of September, 2010 | ORA600
ORA600 content Content RSS
Oracle ORA600 News RRSS Oracle News RSS
ORA600 blog Blog RSS
ORA600 blog Blog Atom

User login

Month of September, 2010

Unloading history - old Oracle7 dictionary

When I saw Jonathan's post it reminded me of the work I did last weekend.
Friday evening I got 2 datafiles - one SYSTEM and one DATA datafile.

I only knew the platform but I had send out a request for more information. You can quite easily find out endianness and blocksizes... but the Oracle version is a bit different.
With this one, I was quite certain they were Oracle 7 datafiles. For one thing, when DUDE scanned the fileheaders it decoded the filenumbers as being a multiple of 2. SYSTEM turned out to be file#=16. I'll explain later.
Secondly, setting DUDE's VERSION parameter to '7', allowed me to unload the dictionary without too much trouble.

However, when I wanted to unload data from the actual datafile, no data objectid's were found in obj$ or tab$ !!! How could that be !
So I started to investigate the dictionary - looking at the SYSTEM tablespace with an hex editor.
Blocks were fine, however, there was huge gap in objectid's being used, jumping from 3000 to 41000.
Hmm... a serious part of the dictionary was missing - maybe completely overwritten.

I then got word back that this was an Oracle 8.0.x database and even more surprising, the database was up and running except for one datafile that had been offlined. (the one they needed data from).
So, the dictionary had to be good ! 

I was too puzzled at the time - it was only when I took a step back and thought about it for a while everything came together.
This was an Oracle 8.0.x, which was once an Oracle 7.3.4 migrated to 8.0.x using Oracle's 'mig' utility. 'mig' doesn't exist anymore - it's functionality has basically taken over by 'startup migrate' and the upgrade scripts.
Back in the day you had to run the mig utility on your database, which would upgrade your dictionary, using 'migrate.bsq' script. No startup migrate there!
To migrate the dictionary, new dictionary tables were created. For example, if you had obj$, tab$, col$, then mig utility would create obj_mig$, tab_mig$ and col_mig$ and then it would migrate the data from the old dictionary to the new dictionary and basically switch names. That would mean that the objectid's (and of course data objectid's) of the base dictionary tables are totally different than a normal 8.0 database.
In this case, they would be in the 41000 range, because that was the range of objectid's of the most recent created object/segment. 
The old dictionary would then be dropped ... but orphaned extents might sit untouched for years in your system tablespace.
And that's exactly happened when I unloaded the dictionary with VERSION='7'.
DUDE found the left-overs of the old dictionary. However, DUDE should have unloaded the new dictionary !

So how does Oracle find the dictionary ?
Well, in the tablespace header block of the SYSTEM tablespace, there's a pointer pointing to the bootstrap$ table. The bootstrap$ table contains ddl for the base dictionary tables (and indexes) including data objectid's (and table numbers for clustered tables).
(you'll also find the ddl for the base dictionary in the sql.bsq script which is used when the database is created)
Here's an example from bootstrap $: 
 17","2407","CREATE TABLE OBJ$ ( OBJ# NUMBER NOT NULL, DATAOBJ# NUMBER, OWNER# NUMBER NOT NULL, 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 VARCHAR2(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 10240 next 126976 minextents 1 maxextents 121 pctincrease 50 objno 2407 extents ( file 40 block 247))"

You'll notice that it contains some key pointers for the described table, like file# (40), offset (247) and dataobjectid (2407). For tables part of a clustered table, it will also contain the table number within the cluster.

So once we have the bootstrap$ segment, we'll know the location of dictionary !

DUDE actually has the ability to actually 'search' for the bootstrap$ segment based on it's characteristics.
It does not however parse the bootstrap ddl for objectid's and table numbers - but once you have the output for bootstrap$, it's quite straightforward to punch in DUDE's bootstrap parameters like :
  • BOOTSTRAP_FILE_OID
  • BOOTSTRAP_OBJ_OID
  • BOOTSTRAP_COBJ_OID
  • BOOTSTRAP_CFILEBLOCK_OID
  • BOOTSTRAP_CTS_OID
  • BOOTSTRAP_CUSER_OID
  • BOOTSTRAP_TAB_TABNO
  • BOOTSTRAP_IND_TABNO
  • BOOTSTRAP_ICOL_TABNO
  • BOOTSTRAP_COL_TABNO
  • BOOTSTRAP_LOB_TABNO
  • BOOTSTRAP_SEG_TABNO
  • BOOTSTRAP_TS_TABNO
  • BOOTSTRAP_USER_TABNO 

These parameters are explained in the DUDE primer here.

Anyway, once I set these parameters, I was home free, unloading a complete Oracle8 dictionary !!!


So what about the SYSTEM datafile having a filenumber larger than 1 ?

In Oracle 6 there were only 5 to 6 bits used for the file number. So only a maximum of 2^5-1 (31) or 2^6-1 (63) datafiles could be used (database wide).
In Oracle 7 this changed to 10bits or 2^10-1 (1023) datafiles (database wide). However, because of backward compatibility with Oracle 6 an encoding scheme was introduced splitting up the 10bits for file number into 6 and 4 bits and wrapping them around. It really depends on the platform. On intel windows and IBM AIX for example, I’ve seen an 8/2 split.
SVRMGR> select dump(chartorowid('00000000.0000.0001')) from dual ;
DUMP(CHARTOROWID('0000000
-------------------------
Typ=69 Len=6: 1,0,0,0,0,0
1 row selected.
SVRMGR> select dump(chartorowid('00000000.0000.ffff')) from dual ;
DUMP(CHARTOROWID('00000000.00
-----------------------------
Typ=69 Len=6: 255,192,0,0,0,0
1 row selected.
This means that the first file# is :
00000001 00000000 00000000 00000000 -> file# 1
And the maximum file# is :
11111111 11000000 00000000 00000000 -> file# 1023
So the 10bits encoding scheme is like this :
LLLL LLLL HH
Where L is the low order bits
And H the high order bits
Now let’s open DUDE on a series of these datafiles :
DUDE> Initialising ...
DUDE> Init : creating filenumber map ...
DUDE> Scanning tablespace SYSTEM : BLOCKSIZE = 2048
DUDE> File : G:\sys1orcl.ora resolves to number : 4
DUDE> File : G:\sys2.ora resolves to number : 40
You’ll notice that sys1orcl.ora which is basically the first file of the database has file# equal to 4. And we know that sys2.ora had file# equal to 10. How’s that possible ?
File# 1 = 0000 0001 00 (LLLL LLLL HH) EQUALS 4 in Oracle 8 DBA format
File# 10 = 0000 1010 00 (LLLL LLLL HH) EQUALS 40 in Oracle 8 DBA format
It’s clear that using the Oracle 8 DBA format encoding on the Oracle 7 wrapped DBA format, results in different file numbers. Basically, the file number shifted 2 bits to the left (or x2x2). This is of course platform specific, but if the first file of SYSTEM has a file number that is a multiple of 2, you probably have a migrated database.
So what happened when Oracle 8.0 came along and introduced 2^10-1 or 1023 datafiles per tablespace !
Well – the DBA format stayed the same. However, the file numbers became relative to the tablespace. So 2 datafiles of the same database could have potentially the same file number, but belong to 2 different tablespaces!
What happened to the Oracle 7 (absolute) file numbers when it was migrated to Oracle8. Surely, the mig utility didn’t update the DBA for all blocks ?
Let’s check out an Oracle 7 database :
SVRMGR> desc file$
Column Name Null? Type
------------------------------ -------- ----
FILE# NOT NULL NUMBER
STATUS$ NOT NULL NUMBER
BLOCKS NOT NULL NUMBER
TS# NOT NULL NUMBER
SVRMGR> select file#, ts# from file$ ;
FILE# TS#
---------- ----------
1 0
2 1
3 2
4 3
5 8
6 9
7 10
8 7
9 11
10 0
10 rows selected.
Ok – looks logical – we see that tablespace TS#=0 or SYSTEM has 2 datafiles with file#=1 and file#=10.
Let’s do the same after a migration to 8.0 :
SVRMGR> desc file$
Column Name Null? Type
------------------------------ -------- ----
FILE# NOT NULL NUMBER
STATUS$ NOT NULL NUMBER
BLOCKS NOT NULL NUMBER
TS# NUMBER
RELFILE# NUMBER
MAXEXTEND NUMBER
INC NUMBER
CRSCNWRP NUMBER
CRSCNBAS NUMBER
OWNERINSTANCE VARCHAR2(30)
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 VARCHAR2(1000)
SPARE4 DATE
SVRMGR> select file#,ts# from file$ ;
FILE# TS#
---------- ----------
1 0
2 1
3 2
4 3
5 8
6 9
7 10
8 7
9 11
10 0
10 rows selected.
So – the file# for the datafiles stayed the same. But we can see an add column in file$ - relfile# :
SVRMGR> select file#,relfile#,ts# from file$ ;
FILE# RELFILE# TS#
---------- ---------- ----------
1 4 0
2 8 1
3 12 2
4 16 3
5 20 8
6 24 9
7 28 10
8 32 7
9 36 11
10 40 0
10 rows selected.
Here we can clearly see the 2bit shift to the left – the Oracle 7 absolute filenumber became an Oracle 8 relative filenumber.
So the mig utility did not have to :
  • update the DBA in a block
  • row addresses in chained and migrated rows

More info on the topic can be found on Metalink - see note 122926.1 ...
I think I'll have to lay down now Wink 




Howto's
See DUDE primer for info

Get Support

Europe

Belgium :
Kurt Van Meerbeeck
ORA600 bvba
E-mail
dude@ora600.be
Cell : +32 495 580714

Denmark :
Michael Môller
Service & Support Manager
Miracle AS
E-mail :
hra@miracleas.dk
Cell: +45 53 74 71 27


North America

USA :
Tim Gorman
Evdbt Inc
E-mail
tim@evdbt.com
Cell : +1 303 885 4526

Canada :
Pythian
E-mail
dude@pythian.com
Contact


Latin America

Brazil :
HBtec
E-mail
dude@hbtec.com.br
Cell : +55 47 88497639
Contact


Africa

South Africa :
Kugendran Naidoo
NRG Consulting
E-mail
k@nrgc.co.za
Cell : +27 82 7799275


East Asia Pacific

Australia
Andre Araujo
Pythian Australia
E-mail
dude@pythian.com
Cell : +61 2 9191 7427 ext. 1270