DBA Tools

    A few databases got corrupted recently. Such as, disk holding the system tablespace data files got crashed, tables got dropped by mistakes, schema got dropped by mistakes, tablespace got dropped by mistakes (data files still exist). These are all data recovery without system, not system tablespace, it's the relative system dictionary rows of the dropped tables.

    Why it's complex? It's due to the Oracle data file format. We can get the following dictionary information from the system tablespace.

1, Object ID

2, Data Object ID

3, Table Name

4, Column information including order, types etc.

    While from the user data files, we can get the following information.

1, Data Object ID

2, Reocrd (column values, ......)

    You can see that we need the system dictionary data to map the table to relative data object id. If we lost the system tablespace or get the tables dropped, we lost the relation map. By scanning the user data files, we can get the data and save it to files, but we don't know which table it is for each file, we need somebody who know the application well to do tell us the relation map. And without the system dictionary data, we have to verify the column order and column types manually if the recovery utility cannot identify them correctly, so it will take longer time than you expected.

    When you create a table and drop it later, Oracle will leave a shadow data segment in the data files. When you move a table, Oracle will create a new data segment in different location, and leave another shadow data segment. When we scan whole the data files, all these will be identified out. In a really recovery case, customer said that there are maximum 400 tables in the tablespace, but finally we got 1500 data segments in the data files, it took my customer two weeks and three engineers to get the most of the data recovered and restored.

    Now you should know, even you have the system tablespace, but there is no relative system dictionary data of the dropped objects, so for dropping recovery, it's a complex recovery, a recovery without system.