Transportable Tablespace Import Fails With ORA-19721

This blog post is linked with post before : Cross-platform migration - large Oracle 9i db from Solaris 64 to Linux 64

SOURCE: Solaris 64bit, Oracle 10.2.0.4
TARGET: Linux 32bit, Oracle 10.2.0.4

I was playing a little with transportable tablespace imports and experienced transportable tablespace import failure with ORA-19721 error.

After dropping contents of DATA1 tablespace I've decided to import same metadata I've successfully imported before - but import failed.

SQL> alter tablespace DATA1 offline normal;

Tablespace altered.

SQL> drop tablespace DATA1 including contents;

Tablespace dropped.



$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y 
datafiles='/oracle/oradata/rman_convert/data01.dbf' 
file=/oracle/oradata/rman_convert/data1.dmp

...
IMP-00017: following statement failed with ORACLE error 19721:
"BEGIN   sys.dbms_plugts.checkDatafile(NULL,1096888558,12,6400,13,12,0,0,104"
"6791,1047073,162043,50331650,NULL,NULL,NULL); END;"
IMP-00003: ORACLE error 19721 encountered
ORA-19721: Cannot find datafile with absolute file number 12 in tablespace DATA1
ORA-06512: at "SYS.DBMS_PLUGTS", line 2065
...

To investigate problem little more I've created new tablespace DATA2 on source database and moved segments from DATA1 tablespace to DATA2.

SQL> set lines 200
SQL> col segment_name for a25
SQL> col segment_type for a20
SQL> col tablespace_name for a20
SQL>  select segment_name, segment_type, tablespace_name 
from dba_segments where tablespace_name='DATA1';

SEGMENT_NAME              SEGMENT_TYPE         TABLESPACE_NAME
------------------------- -------------------- --------------------
TEST_TABLE                TABLE                DATA1


SQL> create tablespace DATA2 datafile '/oradata1/oradata/data02.dbf' size 50M;

Tablespace created.


SQL> alter table MSUTIC.TEST_TABLE move tablespace DATA2;

Table altered.

Now to convert new tablespace and export metadata:

SQL> alter tablespace DATA2 read only;

Tablespace altered.


RMAN> convert tablespace 'DATA2'
2> to platform="Linux IA (32-bit)"
3> db_file_name_convert='/oradata1/oradata/data02.dbf','/oradata1/rman_convert/data02.dbf';

Starting backup at 28-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=135 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00013 name=/oradata1/oradata/data02.dbf
converted datafile=/oradata1/rman_convert/data02.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 28-NOV-09


$ exp userid=\'/ as sysdba\' transport_tablespace=y tablespaces=DATA2 file=data2.dmp statistics=none

Export: Release 10.2.0.4.0 - Production on Sat Nov 28 19:39:55 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in UTF8 character set and UTF8 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace DATA2 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                     TEST_TABLE
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.

Copy converted datafile and tablespace metadata to target machine:

$ scp rman_convert/* oracle@10.10.10.18:/oracle/oradata/rman_convert
oracle@10.2.10.18's password:
data02.dbf           100% |******************************************| 51208 KB    00:04
data2.dmp            100% |******************************************|  3072       00:00

First tablespace import to target database:

$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y 
datafiles='/oracle/oradata/rman_convert/data02.dbf' 
file=/oracle/oradata/rman_convert/data2.dmp

Import: Release 10.2.0.4.0 - Production on Sat Nov 28 19:44:42 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in UTF8 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing MSUTIC's objects into MSUTIC
. . importing table                   "TEST_TABLE"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

Import execution was successful.


I will drop contents of DATA2 tablespace and try second import:

SQL> drop tablespace DATA2 including contents;

Tablespace dropped.

$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y 
datafiles='/oracle/oradata/rman_convert/data02.dbf' 
file=/oracle/oradata/rman_convert/data2.dmp

Import: Release 10.2.0.4.0 - Production on Sat Nov 28 19:46:23 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in UTF8 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing MSUTIC's objects into MSUTIC
. . importing table                   "TEST_TABLE"
. importing SYS's objects into SYS
Import terminated successfully without warnings.

Again successful import.


This time I will change tablespace mode to read/write, drop contents and try import for third time:

SQL> alter tablespace DATA2 read write;

Tablespace altered.

SQL> alter tablespace DATA2 offline normal;

Tablespace altered.

SQL> drop tablespace DATA2 including contents;

Tablespace dropped.


$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y 
datafiles='/oracle/oradata/rman_convert/data02.dbf' 
file=/oracle/oradata/rman_convert/data2.dmp

Import: Release 10.2.0.4.0 - Production on Sat Nov 28 19:48:11 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in UTF8 character set and UTF8 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 19721:
"BEGIN   sys.dbms_plugts.checkDatafile(NULL,1096888558,13,6400,14,13,0,0,110"
"4430,1104611,162043,54525954,NULL,NULL,NULL); END;"
IMP-00003: ORACLE error 19721 encountered
ORA-19721: Cannot find datafile with absolute file number 13 in tablespace DATA2
ORA-06512: at "SYS.DBMS_PLUGTS", line 2065
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully

This time import fails with error:
ORA-19721: Cannot find datafile with absolute file number 13 in tablespace DATA2

Error probably occurs because "alter tablespace DATA2 read write" statement modified data file header so we can't use exported metadata to plug in modified data file.
Changing mode to read/write also updates control file entry for datafile.

When tablespace is in read only mode, then datafile headers are not updated and import works perfectly with exported metadata.