ORA-39123 + ORA-29341: The transportable set is not self-contained

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29341: The transportable set is not self-contained

Error: 

Export: Release 10.2.0.4.0 - 64bit Production on Monday, 05 August, 2013 11:30:55

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  '/******** AS SYSDBA' directory=Dir_1 dumpfile=.dmp logfile=.log TRANSPORT_TABLESPACES=TBS_1 TRANSPORT_FULL_CHECK=Y

ORA-39123: Data Pump transportable tablespace job aborted

ORA-29341: The transportable set is not self-contained

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 11:30:58

Cause: Here, The transportable set is not self-contained for your tablespace.

some of objects(table, index, constraints ... ) which is owned by sys is pointing to other tablespace.. 

your output may be like this....

EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'tbs_1', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

VIOLATIONS

--------------------------------------------------------------------------------

Index SYS.SYS_C00261262 in tablespace SYSTEM enforces primary constriants  of table   SYS.QUERY_PARSING_DATA in tablespace tbs_1

Sys owned object   in tablespace tbs_1 not allowed in pluggable set

Sys owned object   in tablespace tbs_1 not allowed in pluggable set

Sys owned object   in tablespace tbs_1 not allowed in pluggable set

Sys owned object   in tablespace tbs_1 not allowed in pluggable set

Table in tablespace tbs_3 points to lob segment owner.SUPPLIER in tablespace tbs_1

Table  in tablespace tbs_2 points to lob segment owner.SUBSTANCE in tablespace tbs_1

Table in tablespace tbs_3 points to lob segment owner.MSDX in tablespace tbs_1

8 rows selected.

Solutions:

if possible drop the objects after taking backups, and then try to take expdp backups.

(or)

first check your schema objects are pointing to the same tablespace or other tablespace also.

SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE STATUS from dba_objects where OBJECT_NAME='objectnam';

OWNER                                  OBJECT_NAME                                        STATUS

------------------------------ -------------------------------------------------- -------------------

SYS                                          objectnam                                                            INDEX

SCHEMA1                              objectnam                                                            INDEX

SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE STATUS from dba_objects where OBJECT_NAME='obj2';

OWNER                          OBJECT_NAME                                        STATUS

------------------------------ -------------------------------------------------- -------------------

SYS                                           obj2                                                     TABLE

SCHEMA1                               obj2                                                     TABLE

SQL> select OWNER, OBJECT_NAME, OBJECT_TYPE STATUS from dba_objects where OBJECT_NAME='QUERY_PARSING_DATA';

OWNER                          OBJECT_NAME                                        STATUS

------------------------------ -------------------------------------------------- -------------------

SYS                                         QUERY_PARSING_DATA                                 TABLE

SCHEMA1                             QUERY_PARSING_DATA                                 TABLE

select OWNER, OBJECT_NAME, OBJECT_TYPE STATUS from dba_objects where OBJECT_NAME like 'SYS_C00261262';

OWNER                          OBJECT_NAME                                        STATUS

------------------------------ -------------------------------------------------- -------------------

SYS                                   SYS_C00261262                                           INDEX

find the nos tablespace used by the schema1 user.

SQL> select TABLESPACE_NAME,sum(bytes/1024/1024/1024) from dba_segments where OWNER='SCHEMA1' group by TABLESPACE_NAME;

TABLESPACE_NAME                SUM(BYTES/1024/1024/1024)

------------------------------ -------------------------

tbs_2                                            3.9921875

tbs_1                                            1.6640625

tbs_3                                             .959472656

tbs_4                                            1.40625

now, change all the tablespaces as read only mode like this,

alter tablespace tbs_1 read only:

alter tablespace tbs_2 read only:

alter tablespace tbs_3 read only:

alter tablespace tbs_4 read only:

SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'tbs_1,tbs_2,tbs_3,tbs_4', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

No row selected

now connect to Recovery Manager

rman target /

Recovery Manager Release 10.2.0.4.0 64-Bit

connected to target database : orcl2 (dbid =3287908659)

RMAN> convert tablespace 'tbs_1'

to platform="Linux IA (32-bit)"

db_file_name_convert='datafiile01.DBF', '/tmp/datafile01.DBF','datafiel02.DBF', '/tmp/datafile02.DBF';

Starting backup at 07-AUG-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00074 name=datafile01.DBF
converted datafile=/tmp/datafile01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00193 name=datafile02.DBF
converted datafile=/tmp/datafile02.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Finished backup at 07-AUG-12

RMAN> convert tablespace 'tbs_2'

to platform="Linux IA (32-bit)"

db_file_name_convert='datafile001.DBF', '/tmp/datafile001.DBF';

Starting backup at 07-AUG-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00072 name=datafiile001.DBF
converted datafile=/tmp/datafile001.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:55
Finished backup at 07-AUG-12

RMAN> convert tablespace 'tbs_3'

    ....  .....

RMAN> convert tablespace 'tbs_4'

    ..... .....

now try expdp;

your backup will be completed successfully.

expdp \'/ as sysdba\' directory=DIR_1 dumpfile=name.dmp logfile=name.log 

TRANSPORT_TABLESPACES=tbs_1,tbs_2,tbs_3, tbs_4 TRANSPORT_FULL_CHECK=TRUE

dont forget to change the tbs mode to read write;

alter tablespace tbs_1 read write;

alter tablespace tbs_2 read write;

alter tablespace tbs_3 read write;

alter tablespace tbs_4 read write;