ORA-19721 by Transportable Tablespace Import (TTS)

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4 - Release: 10.1 to 10.2

On 10.1 and above: when attempting to transfer a datafile as a Transportable Tablespace (TTS), the following error occurs, even though the databases are at the same version:

ORA-19721: Cannot find datafile with absolute file number # in tablespace <datafile>

The issue can be reproduced at will with the following steps:

1. Export the data file as a Transportable Tablespace, from a 10.1 (or above) database
2. Import the data file

Cause:

The file header for the data file has a format that is less than v10.0.

When the source tablespace was set to READ-ONLY in preparation for being transported, it is likely that the COMPATIBLE parameter was set to 9.2, at that time. As per unpublished Bug 2905128, this means that the file header is still version 9.2, even though the database is version 10.1 or above.

So, because the target database has COMPATIBLE set to 10.2, the data file being transported has the wrong file header format, and the ORA-19721 error is reported.

Solution

1. Change the tablespace in the SOURCE database back into Read/Write mode:

connect / as sysdba
ALTER TABLESPACE <datafile-name> READ WRITE;


2. If the SOURCE database still has COMPATIBLE set to 9.2, change it 10.2 and bounce the database:

connect / as sysdba
ALTER SYSTEM SET COMPATIBLE=10.2 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

3. Change the tablespace to Read Only mode, again:

ALTER TABLESPACE <datafile-name> READ ONLY;

4. Re-run the Export of the data file using the Transportable Tablespace method.

5. Re-run the Import of the datafile using the Transportable Tablespace method.