Moving data using transportable tablespaces can be faster than performing either a full export/import or unload/load of the same data. This is because the datafiles containing all of the actual data are just copied to the destination location, and you use the datapump export/import utility to transfer only the metadata of the tablespace objects to the new database.
It is important to remember that you cannot import a tablespace if it already exists in the destination database. For example, if you are trying to transport the 'USERS' tablespace from the source, and if 'USERS' already exists in the destination - you will get an error upon import.
We will also need to ensure that the users, who currently own these objects in the source database, are created in the destination database prior to the tablespaces being moved. There is a script provided to do this. An alternative would be to use the 'Remap_Schema' parameter to put the objects into a different schema.

Assumptions:

This examples also assumes you want to transport all user tablespaces to the new database and that there is a destination database already created. If there are tablespaces that you do not wish to transport, then it will be your responsibility to properly edit the 'par' files to exclude the unwanted tablespaces and their associated datafiles.

Metadata residing in the system or sysaux tablespaces:

Database metadata includes views, synonyms, type definitions, database links, PL/SQL packages, roles, Java classes, privileges, sequences, and other objects. Running a full database, metadata-only import creates database metadata that is not automatically created in the target database by the transport process. This will be accomplished with a separate import process.

System-owned objects in the system or sysaux tablespaces:

Some applications create tables and indexes owned by the SYSTEM user that are required for proper application functionality. To properly identify these objects requires application-specific knowledge. You must move these objects to the target database manually with Data Pump or manually re-create the objects after performing the database upgrade.

Databases using text indexes and spatial indexes

Users such as 'CTXSYS, 'ORDSYS', 'MDSYS', 'ORDPLUGINS', 'LBACSYS', 'XDB', 'SI_INFORMTN_SCHEMA', 'DIP', 'DMSYS', 'DBSNMP are treated as objects owned by 'SYS' and not exported.

See Document 228482.1 Schemas CTXSYS MDSYS ORDSYS Are Not Exported

Text:

User-created TEXT indexes will only export/import if the target database has Text installed.

Spatial:

SDO_UTIL.PREPARE_FOR_TTS will need to be run first if exporting from a source database that is 10.2 or 11.1. It is not needed for 11.2.

SDO_UTIL.INITIALIZE_INDEXES_FOR_TTS will be needed if the endianess will be changing. See following note for details:

Note 861911.1 Spatial Indexes And Transportable Tablespaces In The 11gR2 Release

1. Stop materialized view refresh jobs:

Stop all the mview refresh jobs. Once the data is imported to the new database , you should check the refresh jobs. For the mviews that don't refresh, you can drop the mview with 'Preserve Table' and then recreate the mview 'on prebuilt table' to enable the job again.

		drop materialized view  preserve table;
	
		Create materialized view  on prebuilt table;

Note: You must lock the master table or guarantee there are no changes to it from the time you stop the mview refresh jobs until the time you refresh it again in the new db. You should also stop all refresh jobs and make sure the mviews are up to date before the tts process is started.

2. Check endianness:

Determine the endian format of both the source and destination databases with the following queries. If the endianess is different, the RMAN CONVERT DATAFILE must be used to convert the datafiles to the endian format of the destination database.

	SQL> connect / as sysdba

SQL> -- This list taken from 11.2.0.3. Older RDBMS versions may contain fewer platforms.

SQL> -- The list will not contain the platform info for the database from which you are running the query.

SQL> col platform_name for a35

SQL> select * from v$transportable_platform order by platform_id;

PLATFORM_ID PLATFORM_NAME                       ENDIAN_FORMAT

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

          1 Solaris[tm] OE (32-bit)             Big

          2 Solaris[tm] OE (64-bit)             Big

          3 HP-UX (64-bit)                      Big

          4 HP-UX IA (64-bit)                   Big

          5 HP Tru64 UNIX                       Little

          6 AIX-Based Systems (64-bit)          Big

          7 Microsoft Windows IA (32-bit)       Little

          8 Microsoft Windows IA (64-bit)       Little

          9 IBM zSeries Based Linux             Big

         10 Linux IA (32-bit)                   Little

         11 Linux IA (64-bit)                   Little

         12 Microsoft Windows x86 64-bit        Little

         13 Linux x86 64-bit                    Little

         14 Linux x86 32-bit                    Little

         15 HP Open VMS                         Little

         16 Apple Mac OS                        Big

         17 Solaris Operating System (x86)      Little

         18 IBM Power Based Linux               Big

         19 HP IA Open VMS                      Little

         20 Solaris Operating System (x86-64)   Little

         21 Apple Mac OS (x86-64)               Little

SQL> -- You can easily find the platform info for the database running the above query by using the following SQL:

SQL> SELECT tp.platform_id,substr(d.PLATFORM_NAME,1,30), ENDIAN_FORMAT

     FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d

     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

The output of the query can change with version. So please use the query above to find the current support platforms. In previous releases, the transportable tablespace feature allowed the transfer between platforms of the same architecture only.

At the source database:

3. Create list of tablespaces and par files

A. Run this script to list all of the tablespaces that are available to be transported and their block sizes:

		select tablespace_name, block_size

from dba_tablespaces

where tablespace_name not in ('SYSTEM','SYSAUX')

and contents = 'PERMANENT';

NOTE: You will need to review the results of this query and verify the block size for all tablespaces that you are going to Transport. If there are any using a non-standard block size, you will need to add to the target database the init parameter, db_nK_cache_size (where 'n' is the desired block size), to support the transported tablespace. This must be done prior to the TTS import.

B. Ensure you have a valid directory for datapump:

Run the following query to determine the location of DATA_PUMP_DIR:

		SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';
	
		OWNER      DIRECTORY_NAME   DIRECTORY_PATH

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

SYS        DATA_PUMP_DIR    C:\app\orauser\admin\orawin\dpdump\

You can also create a directory to a different location if you like and grant rights to the directory to the user running the exports/imports using the following syntax:

		Create directory DATA_PUMP_DIR as  ‘/usr/apps/datafiles’;
	
		GRANT read, write on directory DATA_PUMP_DIR TO scott;

Reference: Oracle DataPump Quick Start (Doc ID 413965.1)

C. Run the following script to create TTS export, and TTS import Data Pump parameter files.

		connect / as sysdba
	
		REM

REM Create TTS Data Pump export and import PAR files

REM

set feedback off trimspool on

set serveroutput on size 1000000

REM

REM Data Pump parameter file for TTS export

REM

spool dp_ttsexp.par

declare

tsname varchar(30);

i number := 0;

begin

dbms_output.put_line('directory=DATA_PUMP_DIR');

dbms_output.put_line('dumpfile=dp_tts.dmp');

dbms_output.put_line('logfile=dp_ttsexp.log');

dbms_output.put_line('transport_full_check=no');

dbms_output.put('transport_tablespaces=');

for ts in

(select tablespace_name from dba_tablespaces

where tablespace_name not in ('SYSTEM','SYSAUX')

and contents = 'PERMANENT'

order by tablespace_name)

loop

if (i!=0) then

dbms_output.put_line(tsname||',');

end if;

i := 1;

tsname := ts.tablespace_name;

end loop;

dbms_output.put_line(tsname);

dbms_output.put_line('');

end;

/

spool off

REM

REM Data Pump parameter file for TTS import

REM

spool dp_ttsimp.par

declare

fname varchar(513);

i number := 0;

begin

dbms_output.put_line('directory=DATA_PUMP_DIR');

dbms_output.put_line('dumpfile=dp_tts.dmp');

dbms_output.put_line('logfile=dp_ttsimp.log');

dbms_output.put('transport_datafiles=');

for df in

(select file_name from dba_tablespaces a, dba_data_files b

where a.tablespace_name = b.tablespace_name

and a.tablespace_name not in ('SYSTEM','SYSAUX')

and contents = 'PERMANENT'

order by a.tablespace_name)

loop

if (i!=0) then

dbms_output.put_line(''''||fname||''',');

end if;

i := 1;

fname := df.file_name;

end loop;

dbms_output.put_line(''''||fname||'''');

dbms_output.put_line('');

end;

/

4. Check for user-created objects in the system and sysaux tablespaces:

Since objects in the system and sysaux tablespaces are not transported, you will want to check for user-created objects that may be in these two tablespaces with this script:

		select owner, segment_name, segment_type from dba_segments where tablespace_name in ('SYSTEM', 'SYSAUX') and owner not in (select name

from system.logstdby$skip_support

where action=0);

You must move the identified objects to a user tablespace prior to beginning the transport process so the objects can be transported by TTS. Alternatively, you can move the objects separately with Data Pump or you can manually re-create them after performing the database upgrade.

5. Create the 'Create user script':

Run the following commands at the source database to generate a 'create user' script to be used at the destination database to create the users prior to the TTS import. A subsequent metadata import, run after the TTS, will reset these accounts to their original passwords and default tablespaces.

		connect / as sysdba
	
		spool def_Tbs.sql

select 'CREATE USER '||username||' IDENTIFIED BY test DEFAULT TABLESPACE SYSTEM;' from dba_users where username not in ('SYS', 'SYSTEM', 'DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC');

spool off

6. Check containment:

Run this script for the DBMS_TTS.TRANSPORT_SET_CHECK function to perform the self-containment check for the list of tablespaces to be transported.

		connect / as sysdba
	
		declare

checklist varchar2(4000);

i number := 0;

begin

for ts in

(select tablespace_name

from dba_tablespaces

where tablespace_name not in ('SYSTEM','SYSAUX') and contents = 'PERMANENT')

loop

if (i=0) then

checklist := ts.tablespace_name;

else

checklist := checklist||','||ts.tablespace_name;

end if;

i := 1; 

end loop;

dbms_tts.transport_set_check(checklist,TRUE,TRUE);

end;

/
	
		select * from transport_set_violations;

Any violations listed must be resolved before the tablespaces are transportable. See note

Note.114915.1 Ext/Pub Using Dbms_tts.transport_set_check Results in Entry in Transport_set_violations:

7. Export source metadata:

Export metadata from the source database. After the tablespaces are transported, the metadata will be imported into the target database to create the remaining metadata that was not transported. Perform no DDL at the source database after this step. This command will create a dump file for the metadata.

-- example

		$ expdp system/<password> DIRECTORY=DATA_PUMP_DIR LOGFILE=dp_fullexp_meta.log DUMPFILE=dp_full.dmp FULL=y CONTENT=METADATA_ONLY
	

Copy the resulting dump file; dp_full.dmp to the target database.

8. Check for tablespaces using a non-default block size. These tablespaces will need a db_nK_cache_size parameter at the target database to support the tablespace.

-- Example

	show parameter cache_size;

NAME                 TYPE    VALUE

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

client_result_cache_size       big integer 0

db_16k_cache_size          big integer 0

db_2k_cache_size           big integer 0

db_32k_cache_size          big integer 1024M

db_4k_cache_size           big integer 0

db_8k_cache_size           big integer 0

db_cache_size            big integer 16G

db_keep_cache_size          big integer 16G

db_recycle_cache_size        big integer 0

select tablespace_name, block_size from dba_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE

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

SYSTEM                               8192

SYSAUX                               8192

UNDOTBS1                             8192

TEMP                                 8192

USERS                                8192

USER_DATA                            8192

BIG_DATA                            32768

If the tablespace (in this case BIG_DATA )with a 32k block size is being transported, you will need to create a 32k cache in the target database. We will do this in Step 13.

9. Create tablespace scripts:

A. This script creates the tts_tsro.sql script from the source database. Use this script to set all tablespaces to be transported to READ ONLY mode.

		connect / as sysdba
	
		set heading off

feedback off

trimspool on

linesize 500

spool tts_tsro.sql

prompt /* =================================== */

prompt /* Make all user tablespaces READ ONLY */

prompt /* =================================== */

select 'ALTER TABLESPACE ' || tablespace_name || ' READ ONLY;' from dba_tablespaces

where tablespace_name not in ('SYSTEM','SYSAUX')

and contents = 'PERMANENT';

spool off

B. Run script tts_tsro.sql to set the tablespaces to be transported to read only mode.

		SQL> @tts_tsro.sql;
	

C. This script creates the tts_tsrw.sql script. Use script tts_tsrw.sql later in step 13 to set all tablespaces to READ WRITE mode after the transport tablespace import process.

		connect / as sysdba
	
		set heading off

feedback off

trimspool on

linesize 500

spool tts_tsrw.sql

prompt /* ==================================== */

prompt /* Make all user tablespaces READ WRITE */

prompt /* ==================================== */

select 'ALTER TABLESPACE ' || tablespace_name || ' READ WRITE;' from dba_tablespaces

where tablespace_name not in ('SYSTEM','SYSAUX')

and contents = 'PERMANENT';

spool off

D. Save script tts_tsrw.sql to be run on the destination database later in step 13.

10. Export the tablespaces:

Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.

		SQL>HOST
	
		$ EXPDP system/<password> parfile=dp_ttsexp.par

11. Endianness conversion:

If the tablespaces are being transported to a different platform, and the endianness of the platforms is different, then convert the datafiles composing the tablespaces.
You can find the list of tablespaces in the parfile that was created. For a simple example, we used the 'sales_1 and sales_2 tablespaces below:

From SQL*Plus, return to the host system:

		SQL> HOST
	

Start RMAN and connect to the source database:

		$ RMAN TARGET /
	

Use the RMAN CONVERT TABLESPACE command to convert the datafiles into a temporary location on the source platform. In this example, assume that the temporary location, directory /tmp, has already been created. The converted datafiles are assigned names by the system. If you have a large amount of tablespaces to convert, you can issue multiple 'CONVERT TABLESPACE' commands.

--Example

		RMAN> CONVERT TABLESPACE sales_1,sales_2 

2> TO PLATFORM 'Microsoft Windows IA (32-bit)'

3> FORMAT '/tmp/%U';
	
RMAN> exit

Recovery Manager complete.

See Also:
Oracle Database Backup and Recovery Reference for a description of the RMAN CONVERT command

12. Copy the following files to a place that is accessible to the destination database.

dp_ttsimp.par import parameter file

def_Tbs.sql create user script

dp_full.dmp metadata dump file

tts_tsrw.sql script to make tablespaces read write

If your datafiles are on ASM, you will need to use Dbms_file_transfer to move them to the destination database as seen in note:

How to Create Transportable Tablespaces Where the Source and Destination are ASM-Based (Doc ID 394798.1)

Copy the datafiles of the tablespaces to a place that is accessible to the destination database. To accomplish this, do one of the following:

If no endianness conversion of the tablespace set is needed, or if you already converted the tablespace set:

Copy the dump file to the directory pointed to by the DATA_PUMP_DIR directory object, or to any other directory object of your choosing. Run the following query to determine the location of DATA_PUMP_DIR:

		SELECT * FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'DATA_PUMP_DIR';
	
		OWNER      DIRECTORY_NAME   DIRECTORY_PATH

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

SYS        DATA_PUMP_DIR    C:\app\orauser\admin\orawin\dpdump\

On the UNIX and Linux platforms, the datafile location is typically /u01/app/oracle/oradata/SID/ or +DISKGROUP/SID/datafile/.

Note:
If you converted the datafiles, obtain the new names and locations of the datafiles from the CONVERT TABLESPACE command output.

See Also:

DBMS_FILE_TRANSFER Feature in Oracle 10g (Doc ID 283991.1)

AT the Destination database:

13. Add the db_32k_cache_size parameter to the target database to support the BIG_DATA tablespace being transported from step 8.

	SQL> ALTER SYSTEM SET db_32k_cache_size='1024M' SCOPE=BOTH;

14. Create users:

Create the users on the destination database for the objects being imported.

		SQL>@def_Tbs.sql    (previously created in step 5)
	

15. Import the tablespaces:

Run Data Pump on the target system to import the tablespaces. Keep in mind that you will get an error on the import if any tablespaces already exist in the destination database. Import the tablespaces using the Data Pump Import utility, impdp:

		SQL> host
	
		IMPDP system/<password> parfile=dp_ttsimp.par

16. Make tablespace read write:

Run the tts_tsrw.sql script created in step 7C to make the new tablespaces read write.

		SQL> @tts_tsrw.sql;
	

17. Import source metadata:

Import Source Database Metadata into Target Database

	$ impdp system/<password> DIRECTORY=DATA_PUMP_DIR LOGFILE=dp_fullexp_meta.log DUMPFILE=dp_full.dmp FULL=y CONTENT=METADATA_ONLY

18. Verification:

A. At the source database:

We assume TEMP and UNDO are the names used for your temp and undo tablespaces, if they are different you will need to edit the script.

	set trim on trims on pagesize 150 linesize 999

col owner for a15

col tablespace_name for A30

col segment_name for a30

col segment_type for a16

Create table t1 as

select tablespace_name, owner, segment_name, segment_type, (bytes/1048576) Size_Mbytes from dba_segments where tablespace_name not in ('SYSTEM','SYSAUX','UNDO','TEMP') and owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC') order by tablespace_name, owner, segment_name, segment_type;

B. Export table t1 to destination database.

	expdp system/<password> directory=data_pump_dir logfile=dp_exp_t1.log dumpfile=dp_t1.dmp tables=T1;

C. Import table t1 into destination database.

	

impdp system/<password> directory=data_pump_dir logfile=dp_imp_t1.log dumpfile=dp_t1.dmp tables=T1;

D. At destination database:

	set trim on trims on pagesize 150 linesize 999

col owner for a15

col tablespace_name for A30

col segment_name for a30

col segment_type for a16

Create table t2 as

select tablespace_name, owner, segment_name, segment_type, (bytes/1048576) Size_Mbytes from dba_segments where tablespace_name not in ('SYSTEM','SYSAUX','UNDO','TEMP') and owner not in ('SYS','SYSTEM','DBSNMP','SYSMAN','OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS','ANONYMOUS','XDB','ORDPLUGINS','OLAPSYS','PUBLIC') order by tablespace_name, owner, segment_name, segment_type;

select * from t1 where (segment_name) NOT IN ( select segment_name from t2);

References:
        Document 1454872.1 Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable
        Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues (Doc ID 1166564.1)
        Note.114915.1 Ext/Pub Using Dbms_tts.transport_set_check Results in Entry in Transport_set_violations
        Note 228482.1 Schemas CTXSYS MDSYS ORDSYS Are Not Exported

How to Move a Database Using Transportable Tablespaces (文件 ID 1493809.1)

APPLIES TO:

GOAL

FIX