DUL The tool is Oracle Database is the last resort to save data , You use it DUL When , In most cases , The database can no longer be started , Even some
data The file is corrupted . that DUL And how to export the data in these extreme cases ？ Let's analyze its working principle step by step .
Such as If you want to develop a similar tool yourself , This article will also tell you what work you have to do , How to do .
Oracle A database is actually a collection of data , The data is stored in tables , Manage the data through some software , Reading data is just these functions
A small part of . The most important of these data is user data , They are usually stored in data files , Store in a certain format . How to interpret these data
As we see it , This needs the help of metadata , Usually we call metadata a data dictionary . Now let's take a look at what a data dictionary looks like .
The data dictionary
Oracle The data dictionary is also made up of tables . The main ones are obj$,tab$,col$ These three tables ,obj$ The name of the object is specified in the table , Yes
like ID, Object's data ID etc. , Of course, it also specifies the owner of the object ID.tab$ The table specifies some properties of the table , Most importantly, it specifies where the table starts , stay
Which data file , Which block to start with .col$ The table specifies the column properties of the table , Include the name of the column , Column ID, Listed in the paragraph ID, The type of the column , Length, etc. ,
With col$ Information in ,Oracle The format of the table stored in the data block can be explained .
The location of the table in the data file
As we said above ,tab$ There are two fields in the table that specify where the table starts , One is called FILE#, In which data file is the indicator , Another call BLOCK#,
Which block does the indicator start from . This starting block is called the segment head block , It contains one by one extent Address range , be called extent map,extent It's a continuous number
It's made up of blocks . With this extent map, You can read data from these blocks , These blocks are the data blocks of the table . If a watch is very large , The head of the segment and
It can't contain all extent, Then what shall I do? ？Oracle The next one will be specified in this block extent map The block address of , Until all extent map Dulie
It's over .
With the above knowledge , We can read the table data from the data file . Before we start , There seems to be another problem , How to read from a data file
Data dictionary table ？ The data dictionary is also a table , The position of the segment head of the table is from tab$ Obtained in , We haven't read about it yet tab$ The data of , It's like a dead cycle .
Don't worry. ,Oracle We'll have the same problems when we start up , How can it be solved ？ original Oracle Startup time , First, create one in memory called
bootstrap$ Table of , This table stores some table building statements , Including the above mentioned obj$,tab$ and col$, Interestingly , In each table creation statement
Behind , It also indicates the position of the segment header block of this table , So it's convenient , Go straight to this location and find extent map, Traverse all extent map find
The data blocks that belong to this table , Parse the contents of the data block , You can get the information of the data dictionary .
See here , You're still a little confused , So it shows that you think deeply , Yes ,bootstrap$ Where does the table start ？ It's kept in 1 Data file No
1 In block , This block contains header information , There's a man in it called root dba Field of , The included address is bootstrap$ The segment header address of the table .
The data block contains the data in the table , It also has a certain structure , First, it's big information , Transaction information , Here is ITL,ITL The size is fixed , It's called something
Service slot , The block contains several transaction slots , Specify... In the transaction information . And then there's the header information , Next comes the table of contents （table directory） Information , And then
Line directory （row directory）, The row directory specifies the location of each row of data . And then there's row data , Row data is stored from the bottom of the block up , therefore
There may be some free space between the row directory and the real data . The structure of data block is complex , Fortunately Oracle There's a tool called bbed, Can play
Open a data block , It defines these data structures in detail , Contains the fields of the data structure , You can easily see the details of data storage .
LONG data type
LONG The type of data is generally long , It's easy to create row connections , Of course, if a table is created with too many fields , It can also cause row connections , That is, a row of data
Distributed between two or more data blocks , What to do at this time ？Oracle At the beginning of each line of data, there is one called fb Field of , Indicates whether the data is connected to the next
Block , If we get to the next block , Then there will be a Website monitoring be called nrid Field of , Used to indicate where the following data is connected , This is an address , Represents in
Which offset of which block . If the next block has not yet fully contained this row of data , Then there will be the next nrid, Keep connecting , Until the end of the data line .
LOB data type
LOB It's a big object data type , It's to replace LONG Type introduces , When the amount of data is small , It's stored in blocks of the table , If the data is large , Save it
Stored in a segment outside the table , This passage is called LOB paragraph .LOB The data is in LOB The position in the segment , Specified by a field called a locator , The English name is Lob
Locator, This locator is stored in the data block of the table , Read like this LOB A field , You can find it through the locator LOB data .
Actually ,LOB It's quite complicated to store , By default , For easy storage ,LOB Column in the data block of the table , Not only does it store locators , Also stores the
some LOB The address of the data block , Through these addresses LOB Read out the data . But there is a limit to the number of addresses stored , It depends on... In the table block LOB Letter
The length of the breath , By default, at most 12 individual , If it goes beyond , We're going to use the locator , The locator can't be found directly LOB The block position of the segment , In fact, he is LOB
index A key value of , Through this key , stay LOB A series of LOB The address of the block , Through these addresses LOB Read out the data .
As mentioned above LOB The storage format is called BasicFile LOB, from 11g Start ,Oracle Introduced a new LOB Storage format , be called SecureFile LOB.
It almost puts LOB index To cancel the , But the LOB The block address of is put directly in LOB In the head of the section , It can be read directly from the address in the header block LOB data . When
But if LOB There's a lot of data , The head block can't hold so many addresses , Then what shall I do? ？Oracle Four addresses are set in the header block , named dba0,
dba1,dba2,dba3. This is a four level internal tree structure ,dba0 It is equivalent to a leaf node , Managed a lot of LOB Block address , When dba0 Full of ,
Will appear dba1, yes dba0 The superior node of , It manages a lot of things like dba0 Leaves of , Each leaf node block contains many nodes LOB The address of the data block ,
dba1 Full of , Will appear dba2 node , By analogy , here we are dba3 when , The amount of data that can be managed is far more than LOB The maximum amount of data , That all
Of LOB Data can be read through this structure .
If you delete the table , from 11g At first, it's not really deleted by default , Instead, I changed the name of the table , The original table name is stored in a table called recycle bin ,
If you change your mind , It can also be restored by command , It's good news to delete the table by mistake . Because it is no different from the ordinary table , So through the knowledge above ,
We can come back .
If a table is truncated , Maybe you can't access the original data , If I regret it now , I have to hit the wall . Use what we described earlier
Can the method retrieve the data ？ Find the segment header of the table ,dump Come out and see , You'll find that in the header extent map It's been cleared , It's not going to pass
extent map Traversing the data . There is always a way , Isn't all data stored in data files ？ Let's scan all the blocks in the data file ,
Take the one with this watch ID All the consistent pieces are found out , And then analyze the data from these blocks , No, it's OK ？ It just takes more time , also
Make sure you don't miss the data file , Practice has proved that the data can be read out .
With the experience of truncated table above , The deleted table is easy to handle . The change of segment head block is almost the same as truncation table . Unlike truncated tables , You have to put
The deleted records in the data dictionary are recovered ,obj$,tab$,col$ All records in the table about this table have been deleted , So how to recover ？ Remember that we mentioned
I've been to the fact that in front of every line of data there's one called fb Field of , Actually Oracle It doesn't clear this data , It's just fb There is a mark on the field , Get rid of this
A sign , These records are restored . Now scan the data file again , Find the block that belongs to this table , You can recover the data .
The data dictionary is damaged
The most serious case is that part of the data file has been damaged , Then there is no guarantee that the data will be fully recovered . So first try to read the data dictionary ,
Oracle For the basic data dictionary table, the position of the segment header block is fixed , Find a database of the same version , from bootstrap$ Look up the data dictionary in the table
Section head position , Or from tab$ Find the position of the segment head in , Then try to export the data dictionary from these places , If you can export a data dictionary , The rest of the work is the same as before
It's ready .
The worst case is that the data files in the system table space are lost or seriously damaged , Unable to export data dictionary , So what to do at this time ？ So only through
Data to reconstruct the data dictionary , Or scan all the data files , Record the position of the segment header , Each segment header corresponds to a table or partition , It's like this
The position of the section head is found , The next step is to rebuild col$ In the field , Mainly data types , Length etc. . Some data types are fixed in length , Like Japan
Period type , Timestamp type , It's good to guess . The number type also has its own characteristics, which is easy to determine , The rest is character types , Most of the things you can't guess can be done first
Treat it as a character type . And then export part of the data according to the reconstructed data dictionary , At this time, it is necessary to make manual comparison , Make sure the field type is clear , And then you can lead
There's data .