If an archive log is deleted, the offline datafile cannot be accessed.

If an archive log is deleted, the offline datafile cannot be accessed.

An archive log is deleted and the offline datafile cannot be accessed.

In fact, it is more appropriate to change the title to "rebuid online index encounters the problem of control file sequential read wait event ".

Version: 10.2.0.5, 2 nodes in asm mode rac, rhel5.5

In fact, the solution should be to rescue the data.
This datafile is not the first data file in the index tablespace. App developers say that the tablespace contains only indexes and does not contain tables.

Use the following statement to query the objects contained in the datafile:

select distinct owner, segment_name, segment_type, partition_name  from dba_extents where relative_fno IN (select relative_fno                          from dba_data_files                         where tablespace_name = 'XXX'                           and file_name = 'YYYY');

-- Note: This statement is not well written. Please give your valuable comments.

From the preceding query results, we can see that table is not included, but the partition of index and index.

Based on the actual situation, the engineer decided to use the rebuild online method to reconstruct the index. The specific statement is:

alter index index_owner.index_name rebuild partition partition_name online tablespace XXX parallel 20;

-- Note: in other words, the index is still placed in the original XXX tablespace.

If the execution time of the preceding statement exceeds six hours, the query of the v $ session is continuously executed to determine that the waiting events of 20 parallel sessions in the v $ session are (v $ session. event column ):
The vast majority of parallel sessions are the control file sequential read wait events among the vast majority of executions, and sporadic enq: RO-fast object reuse wait events

This wait is extremely abnormal. Search by "control file sequential read" on mos and no valuable information is found.

After the event is intercepted for 6 hours, the awr of one hour is intercepted, and the control file sequential read wait event is quite prominent:

 

Later, I went to query the usage of the XXX tablespace and found that the output of the script for the tablespace usage (see below) did not have the XXX tablespace:

-- This generally means that all dba_data_files.bytes in the tablespace is used. (This sentence means that the tablespace is full without considering the automatic expansion of datafile .)

SQL> select f.tablespace_name tablespace_name,round((d.sumbytes/1024/1024/1024),2) total_g,  2  round(f.sumbytes/1024/1024/1024,2) free_g,  3  round((d.sumbytes-f.sumbytes)/1024/1024/1024,2) used_g,  4  round((d.sumbytes-f.sumbytes)*100/d.sumbytes,2) used_percent  5  from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,  6  (select tablespace_name,sum(bytes) sumbytes from dba_data_files group by tablespace_name) d  7  where f.tablespace_name= d.tablespace_name  8  order by  used_percent desc;  

Later, I continued to check the datafile information and found that the XXX tablespace has a total of four datafiles, except for the offline datafile, there are three remaining, and the bytes of each datafile is about 6 GB, the maxbytes of these datafiles is 32 GB
The engineer decided to resize datafile:

Alter database datafile 'datafile full path \ idx_tool3.dbf' resize 20000 M; -- This datafile is an online datafile, not an offline datafile

The following statement after the datafile is resize:

alter index index_owner.index_name rebuild partition partition_name online tablespace XXX parallel 20;

The execution is completed immediately.

 

Therefore, we can infer one thing:
During the index rebuild online process, only the dba_data_files.bytes capacity of each datafile in the tablespace is used, and the capacity (dba_data_files.maxbytes-dba_data_files.bytes) is not used.
If the tablespace is used up before the rebuild starts ("ignore the automatic expansion attribute of datafile, only use of dba_data_files.bytes"), after the rebuild starts, in the process of indexing rebuild online, the ora-error is not reported. The waiting event is mostly the control file sequential read-which is confusing.