1. check endian format and character set.
SYS@DG> desc V$TRANSPORTABLE_PLATFORM
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
PLATFORM_ID NUMBER
PLATFORM_NAME VARCHAR2(101)
ENDIAN_FORMAT VARCHAR2(14)
SYS@DG> column PLATFORM_NAME format a30
SYS@DG> select * from V$TRANSPORTABLE_PLATFORM ;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux x86 64-bit Little
16 Apple Mac OS Big
12 Microsoft Windows x86 64-bit Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
19 HP IA Open VMS Little
20 Solaris Operating System (x86- Little
64)
21 Apple Mac OS (x86-64) Little
20 rows selected.
SYS@DG> select PLATFORM_NAME from v$database;
PLATFORM_NAME
------------------------------
Linux x86 64-bit
select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
(select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
(select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
-------------------------------------------------------------------------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
2. Create test tablespace TTS1 having two data files, id, for transport.
SYS@DG> select file_name from dba_data_files;
FILE_NAME
-------------------------------------------------------------------------------------------------------------------
/u01/oradata/DG/system01.dbf
/u01/oradata/DG/sysaux01.dbf
/u01/oradata/DG/undotbs01.dbf
/u01/oradata/DG/users01.dbf
SYS@DG> create tablespace tts1 datafile '/u01/oradata/DG/tts1_01.dbf' size 20m;
Tablespace created.
SYS@DG> alter tablespace tts1 add datafile '/u01/oradata/DG/tts1_02.dbf' size 20m;
Tablespace altered.
SYS@DG> select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
SYSTEM
/u01/oradata/DG/system01.dbf
SYSAUX
/u01/oradata/DG/sysaux01.dbf
UNDOTBS1
/u01/oradata/DG/undotbs01.dbf
USERS
/u01/oradata/DG/users01.dbf
TTS1
/u01/oradata/DG/tts1_01.dbf
TTS1
/u01/oradata/DG/tts1_02.dbf
6 rows selected.
SYS@DG> create user tranp identified by oracle123 default tablespace tts1;
User created.
SYS@DG> grant create session, resource to tranp;
Grant succeeded.
SYS@DG> create table tranp.t01 as select * from sys.dba_objects;
Table created.
3.TRANSPORT_SET_CHECK Procedure
This procedure checks if a set of tablespaces (to be transported) is self-contained. After calling this procedure, the user may select from a view to see a list of violations, if there are any.
If the view does not return any rows, then the set of tablespaces is self-contained. For example,
SQLPLUS> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('foo,bar', TRUE);
SQLPLUS> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
in my case:
SYS@DG> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTS1',TRUE);
PL/SQL procedure successfully completed.
SYS@DG> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
@> conn system
Connected.
4. export the tablespace metadata.
SYS@DG> create directory tranp_dir as '/home/oracle/ops/exp';
Directory created.
SYS@DG> grant read, write on directory tranp_dir to system;
Grant succeeded.
SYS@DG> alter tablespace tts1 read only;
Tablespace altered.
orarac1poc:DG:/home/oracle/ops/exp> cat expdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
TRANSPORT_TABLESPACES=tts1
orarac1poc:DG:/home/oracle/ops/exp> expdp system parfile=expdp_trans_df.par
Export: Release 11.2.0.2.0 - Production on Wed Mar 20 10:34:14 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** parfile=expdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/ops/exp/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS1:
/u01/oradata/DG/tts1_01.dbf
/u01/oradata/DG/tts1_02.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:35:40
--now switch to target database which using ASM
5. copy the data file to ASM disk group
SYS@ORCL1> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
6 rows selected.
--copy to ASM
orarac1poc:ORCL1:/home/oracle/ops/exp> su - grid
Password:
[grid@orarac1poc ~]$ ls -l /u01/oradata/DG/tts*
-rw-r----- 1 oracle asmadmin 20979712 Mar 20 10:27 /u01/oradata/DG/tts1_01.dbf
-rw-r----- 1 oracle asmadmin 20979712 Mar 20 10:27 /u01/oradata/DG/tts1_02.dbf
[grid@orarac1poc ~]$ asmcmd
ASMCMD> cp /u01/oradata/DG/tts1_01.dbf +DGDATA/orcl/
copying /u01/oradata/DG/tts1_01.dbf -> +DGDATA/orcl/tts1_01.dbf
ASMCMD> cp /u01/oradata/DG/tts1_02.dbf +DGDATA/orcl/
copying /u01/oradata/DG/tts1_02.dbf -> +DGDATA/orcl/tts1_02.dbf
ASMCMD> ls -l +DGDATA/orcl/
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N control01.ctl => +DGDATA/ORCL/CONTROLFILE/Current.260.803128289
N control02.ctl => +DGDATA/ORCL/CONTROLFILE/Current.261.803128289
N example01.dbf => +DGDATA/ORCL/DATAFILE/UNKNOWN.265.803128321
N redo01.log => +DGDATA/ORCL/ONLINELOG/group_1.262.803128313
N redo02.log => +DGDATA/ORCL/ONLINELOG/group_2.263.803128313
N redo03.log => +DGDATA/ORCL/ONLINELOG/group_3.267.803128495
N redo04.log => +DGDATA/ORCL/ONLINELOG/group_4.268.803128495
N spfileORCL.ora => +DGDATA/ORCL/PARAMETERFILE/spfile.269.803128497
N sysaux01.dbf => +DGDATA/ORCL/DATAFILE/SYSAUX.257.803128215
N system01.dbf => +DGDATA/ORCL/DATAFILE/SYSTEM.256.803128215
N temp01.dbf => +DGDATA/ORCL/TEMPFILE/TEMP.264.803128317
N tts1_01.dbf => +DGDATA/ASM/DATAFILE/tts1_01.dbf.270.810557179
N tts1_02.dbf => +DGDATA/ASM/DATAFILE/tts1_02.dbf.271.810557191
N undotbs01.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS1.258.803128215
N undotbs02.dbf => +DGDATA/ORCL/DATAFILE/UNDOTBS2.266.803128451
N users01.dbf => +DGDATA/ORCL/DATAFILE/USERS.259.803128215
ASMCMD> exit
[grid@orarac1poc ~]$ exit
logout
6. create id, data pump working directory, and import the metadata.
orarac1poc:ORCL1:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 10:52:14 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL1> create directory tranp_dir as '/home/oracle/ops/exp';
Directory created.
SYS@ORCL1> grant read, write on directory tranp_dir to system;
Grant succeeded.
SYS@ORCL1> create user tranp identified by oracle123 default tablespace tts1;
create user tranp identified by oracle123 default tablespace tts1
*
ERROR at line 1:
ORA-00959: tablespace 'TTS1' does not exist
SYS@ORCL1> create user tranp identified by oracle123;
User created.
SYS@ORCL1> grant create session, resource to tranp;
Grant succeeded.
orarac1poc:ORCL1:/home/oracle/ops/exp> cat impdp_trans_df.par
DIRECTORY=tranp_dir
DUMPFILE=tts.dmp
#TRANSPORT_TABLESPACES=tts1
TRANSPORT_DATAFILES='+DGDATA/orcl/tts1_01.dbf' ,'+DGDATA/orcl/tts1_02.dbf'
orarac1poc:ORCL1:/home/oracle/ops/exp> impdp system parfile=impdp_trans_df.par
Import: Release 11.2.0.2.0 - Production on Wed Mar 20 10:58:29 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** parfile=impdp_trans_df.par
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 10:59:00
8. verification
orarac1poc:ORCL1:/home/oracle/ops/exp> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Wed Mar 20 10:59:28 2013
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SYS@ORCL1> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
USERS
+DGDATA/orcl/users01.dbf
UNDOTBS1
+DGDATA/orcl/undotbs01.dbf
SYSAUX
+DGDATA/orcl/sysaux01.dbf
SYSTEM
+DGDATA/orcl/system01.dbf
EXAMPLE
+DGDATA/orcl/example01.dbf
UNDOTBS2
+DGDATA/orcl/undotbs02.dbf
TTS1
+DGDATA/orcl/tts1_02.dbf
TTS1
+DGDATA/orcl/tts1_01.dbf
8 rows selected.
SYS@ORCL1> select count(*) from tranp.t01;
COUNT(*)
----------
60708
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 READ ONLY
8 rows selected.
SYS@ORCL1> alter tablespace tts1 read write;
Tablespace altered.
SYS@ORCL1> alter user tranp default tablespace tts1;
User altered.
SYS@ORCL1> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TTS1 ONLINE
8 rows selected.
@> conn tranp/oracle123
Connected.
TRANP@ORCL1> select count(*) from t01;
COUNT(*)
----------
60708
References:
Oracle传输表空间总结
