I saw two case recently about losing one of the data files for a tablespace, first case was losing a data file totally, second case is one of the data files has no recent backup, so it's equal to missing a data file. In this case how to recover the data from that tablespace? We can offline drop the missing data file and then get the database open. I create tablespace with three data files for demo.
SQL> SELECT FILE_ID FROM DBA_DATA_FILES
2 WHERE TABLESPACE_NAME='USERS01';
FILE_ID
----------
5
6
7
And I create a table on this tablespace, filled in some data, make sure it has extents on all three data files.
SQL> SELECT EXTENT_ID, FILE_ID FROM DBA_EXTENTS
2 WHERE SEGMENT_NAME='T_OBJECTS' AND OWNER=USER;
EXTENT_ID FILE_ID
---------- ----------
0 7
1 5
2 6
3 7
To avoid recover data file every time I online a data file, I put the tablespace in read only mode.
SQL> alter tablespace users01 read only;
Tablespace altered.
Then I will run the following SQLs with ociuldr utility.
1, select * from sys.t_objects t
2, select /*+ parallel(t,2) */ * from sys.t_objects t
3, select DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
from sys.t_objects t
4, select /*+ parallel(t,2) */
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
from sys.t_objects t
Let's take a look at the result quickly.
offline 7 offline 5 offline 6 Notes Query 1 0 5167 10180 Result constantly Query 2 0 5721 9870 Result dynamically Query 3 N/A 7 7,5 Distinct Values Query 4 N/A 6,7 7,5 Distinct Values
When datafile 7 get offline, we cannot get any row because the segment header is on that file (the extent with id equals 0). For the non-parallel execution, Oracle scan the table according to the sequence of extent allocation, you can check the extent information to know how much rows you can get recovered by a table scan. If table scan cannot get so much rows, you can try to use the parallel execution, the parallel execution will be terminated when any of the parallel slave try to access the data file offlined, so the result is dynamically, you should try multiple times to get maximumly data recovered. For those no extent on the offlined data files, you can get all rows.
If you know the format of ROWID, you can get the data recovered by ROWID range scan. I offline the data file 6 for the rowid demo. Frist step is to get the data object id of the table.
SQL> SELECT DATA_OBJECT_ID
2 FROM USER_OBJECTS WHERE OBJECT_NAME='T_OBJECTS';
DATA_OBJECT_ID
--------------
11366
Then we need to get the minimum ROWID and the maximum ROWID of rows in the missing data file.
AUL> rowid create object 11366 file 6 block 2 slot 0
OBJD = 11366
RDBA = 0x01800002 = 25165826
RFN# = 6
BID# = 2
SLOT = 0
ROWID = AAACxmAAGAAAAACAAA
AUL> rowid create object 11366 file 6 block 4194303 slot 16383
OBJD = 11366
RDBA = 0x01bfffff = 29360127
RFN# = 6
BID# = 4194303
SLOT = 16383
ROWID = AAACxmAAGAAP///D//
Now we get the two ROWIDs, we can write the SQLs get the data on the existing data files recovered.
SQL> SELECT /*+ USE_CONCAT */ * FROM T_OBJECTS
2 WHERE ROWID < 'AAACxmAAGAAAAACAAA'
3 UNION ALL
4 SELECT /*+ USE_CONCAT */ * FROM T_OBJECTS
5 WHERE ROWID > 'AAACxmAAGAAP///D//';
14924 rows selected.
How ever we can get data maximumly recovered with AUL/MyDUL utility for tables with segment header in the missing data file.