CNDBA社区
步骤一：
SQL> l
1* select * from v$transportable_platform order by 3
SQL> /
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- ----------------------------
3 HP-UX (64-bit) Big
6 AIX-Based Systems (64-bit) Big
18 IBM Power Based Linux Big
2 Solaris[tm] OE (64-bit) Big
4 HP-UX IA (64-bit) Big
16 Apple Mac OS Big
1 Solaris[tm] OE (32-bit) Big
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- ----------------------------
9 IBM zSeries Based Linux Big
17 Solaris Operating System (x86) Little
19 HP IA Open VMS Little
20 Solaris Operating System (x86-64) Little
12 Microsoft Windows x86 64-bit Little
13 Linux x86 64-bit Little
8 Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- ----------------------------
21 Apple Mac OS (x86-64) Little
11 Linux IA (64-bit) Little
5 HP Tru64 UNIX Little
10 Linux IA (32-bit) Little
7 Microsoft Windows IA (32-bit) Little
15 HP Open VMS Little
20 rows selected.
2）需要迁移的表空间是否自包含
SQL> exec sys.dbms_tts.transport_set_check('SCOTT1,SCOTT9',true);
PL/SQL procedure successfully completed.
SQL> select * from sys.transport_set_violations;
no rows selected
3）需要将迁移的表空间打到readonly状态：
SQL> alter tablespace scott1 read only;
Tablespace altered.
SQL> alter tablespace scott9 read only;
Tablespace altered.
4）在源端导出数据（元数据）
exp userid=/'sys/oracle as sysdba/' file=tablespace.dmp log=exp.log transport_tablespace=y tablespaces=scott1,scott9
==============
Export: Release 11.2.0.4.0 - Production on Mon Sep 18 18:52:09 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 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 SCOTT1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table PAR2_TABLE
EXP-00091: Exporting questionable statistics.
. . exporting table PAR3_TABLE
EXP-00091: Exporting questionable statistics.
. . exporting table PAR2_TABLE
EXP-00091: Exporting questionable statistics.
. . exporting table PAR3_TABLE
EXP-00091: Exporting questionable statistics.
. . exporting table T1
EXP-00091: Exporting questionable statistics.
. . exporting table T2
EXP-00091: Exporting questionable statistics.
. . exporting table T3
EXP-00091: Exporting questionable statistics.
. . exporting table EMP
EXP-00091: Exporting questionable statistics.
For tablespace SCOTT9 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table ALL_OBJECT
EXP-00091: Exporting questionable statistics.
. . exporting table EMP
EXP-00091: Exporting questionable statistics.
. . exporting table DEPT
EXP-00091: Exporting questionable statistics.
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully with warnings.
5）将源数据拷贝到目标端：
-rw-r--r-- 1 nfsnobody oinstall 57344 Sep 18 2017 tablespace.dmp
-rw-r--r-- 1 nfsnobody oinstall 1835 Sep 18 2017 exp.log
[oracle@hwjrac1 bak]$
[oracle@hwjrac1 bak]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-root
50G 13G 34G 28% /
tmpfs 3.7G 148M 3.6G 4% /dev/shm
/dev/sda1 477M 41M 411M 9% /boot
/dev/mapper/VolGroup-home
50G 11G 36G 24% /home
/dev/mapper/VolGroup-opt
99G 34G 61G 36% /opt
/dev/mapper/VolGroup-oswbb
50G 58M 47G 1% /oswbb
/root/rhel-server-6.7-x86_64-dvd.iso
3.6G 3.6G 0 100% /mnt/rhel
10.10.50.130:/u01/bak
99G 22G 72G 24% /u01/bak
[oracle@hwjrac1 bak]$ pwd
/u01/bak
当然了、我在这里采用的NFS挂载方式，所以基本上不用scp到目标端，只用修改一下权限即可。
SQL> l
1* select file_id,file_name,tablespace_name from dba_data_files
SQL> /
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ---------------------------------------- ------------------------------------------------------------
4 /opt/oracle/oradata/qhdb/users01.dbf USERS
3 /opt/oracle/oradata/qhdb/undotbs01.dbf UNDOTBS1
2 /opt/oracle/oradata/qhdb/sysaux01.dbf SYSAUX
1 /opt/oracle/oradata/qhdb/system01.dbf SYSTEM
7 /opt/oracle/oradata/qhdb/scott.dbf SCOTT
5 /opt/oracle/oradata/qhdb/example01.dbf EXAMPLE
6 /opt/oracle/oradata/qhdb/test02.dbf TEST02
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ---------------------------------------- ------------------------------------------------------------
8 /opt/oracle/oradata/qhdb/scott1.dbf SCOTT1
9 /opt/oracle/oradata/qhdb/scott2.dbf SCOTT2
10 /opt/oracle/oradata/qhdb/scott3.dbf SCOTT3
11 /opt/oracle/oradata/qhdb/scott4.dbf SCOTT4
12 /opt/oracle/oradata/qhdb/scott5.dbf SCOTT5
13 /opt/oracle/oradata/qhdb/scott6.dbf SCOTT6
14 /opt/oracle/oradata/qhdb/scott7.dbf SCOTT7
FILE_ID FILE_NAME TABLESPACE_NAME
---------- ---------------------------------------- ------------------------------------------------------------
15 /opt/oracle/oradata/qhdb/scott8.dbf SCOTT8
16 /opt/oracle/oradata/qhdb/scott9_2.dbf SCOTT9
16 rows selected.
由于我这里是Scott1 scott9表空间：
所以：
RMAN> backup as copy datafile 8,16 format '/u01/bak/%U.dbf';
Starting backup at 2017-09-18 19:18:23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/opt/oracle/oradata/qhdb/scott1.dbf
output file name=/u01/bak/data_D-QHDB_I-4043740841_TS-SCOTT1_FNO-8_5pseponv.dbf tag=TAG20170918T191823 RECID=7 STAMP=955048705
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00016 name=/opt/oracle/oradata/qhdb/scott9_2.dbf
output file name=/u01/bak/data_D-QHDB_I-4043740841_TS-SCOTT9_FNO-16_5qsepoo2.dbf tag=TAG20170918T191823 RECID=8 STAMP=955048708
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 2017-09-18 19:18:29
[root@qhdb2 bak]# l s -l rt
total 204876
-rw-r--r-- 1 nfsnobody oinstall 57344 Sep 18 22:44 tablespace.dmp
-rw-r--r-- 1 nfsnobody oinstall 1835 Sep 18 22:44 exp.log
-rw-r----- 1 nfsnobody oinstall 104865792 Sep 18 23:10 data_D-QHDB_I-4043740841_TS-SCOTT1_FNO-8_5pseponv.dbf
-rw-r----- 1 nfsnobody oinstall 104865792 Sep 18 23:10 data_D-QHDB_I-4043740841_TS-SCOTT9_FNO-16_5qsepoo2.dbf
[root@qhdb2 bak]# chown -R oracle:oinstall *
[root@qhdb2 bak]# chmod -R 775 *
[root@qhdb2 bak]# l s -l r t
total 204876
-rwxrwxr-x 1 oracle oinstall 57344 Sep 18 22:44 tablespace.dmp
-rwxrwxr-x 1 oracle oinstall 1835 Sep 18 22:44 exp.log
-rwxrwxr-x 1 oracle oinstall 104865792 Sep 18 23:10 data_D-QHDB_I-4043740841_TS-SCOTT1_FNO-8_5pseponv.dbf
-rwxrwxr-x 1 oracle oinstall 104865792 Sep 18 23:10 data_D-QHDB_I-4043740841_TS-SCOTT9_FNO-16_5qsepoo2.dbf
如果源库与目标库所在平台不一样需要进程字节序转换：
[oracle@qhdb2 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 18 23:13:09 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: QHDB2 (DBID=3208837156)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name QHDB2 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/products/11.2.0/db_home1/dbs/snapcf_qhdb2.f'; # default
RMAN> report schema;
Report of database schema for database with db_unique_name QHDB2
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 750 SYSTEM *** /u01/app/oracle/qhdb2/system01.dbf
2 600 SYSAUX *** /u01/app/oracle/qhdb2/sysaux01.dbf
3 825 UNDOTBS1 *** /u01/app/oracle/qhdb2/undotbs01.dbf
4 5 USERS_BAK *** /u01/app/oracle/qhdb2/users01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 59 TEMP 32767 /u01/app/oracle/qhdb2/temp01.dbf
进行字节转换：
RMAN> convert datafile '/u01/bak/data_D-QHDB_I-4043740841_TS-SCOTT1_FNO-8_5pseponv.dbf' to platform="Linux x86 64-bit" from platform="Linux x86 64-bit"
2> format '/tmp/%U.dbf';
Starting conversion at target at 2017-09-18 23:30:58
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/u01/bak/data_D-QHDB_I-4043740841_TS-SCOTT1_FNO-8_5pseponv.dbf
converted datafile=/tmp/data_D-QHDB_I-4043740841_TS-SCOTT1_FNO-8_03seq7hi.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2017-09-18 23:30:59
RMAN> convert datafile '/u01/bak/data_D-QHDB_I-4043740841_TS-SCOTT9_FNO-16_5qsepoo2.dbf' to platform="Linux x86 64-bit" from platform="Linux x86 64-bit"
2> format '/tmp/%U.dbf';
Starting conversion at target at 2017-09-18 23:31:26
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/u01/bak/data_D-QHDB_I-4043740841_TS-SCOTT9_FNO-16_5qsepoo2.dbf
converted datafile=/tmp/data_D-QHDB_I-4043740841_TS-SCOTT9_FNO-16_04seq7ie.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 2017-09-18 23:31:27
查看转换结果：
-rw-r----- 1 oracle oinstall 104865792 Sep 18 23:30 data_D-QHDB_I-4043740841_TS-SCOTT1_FNO-8_03seq7hi.dbf
-rw-r----- 1 oracle oinstall 104865792 Sep 18 23:31 data_D-QHDB_I-4043740841_TS-SCOTT9_FNO-16_04seq7ie.dbf
-rw-r--r-- 1 root root 34 Sep 18 23:32 osw.hb
[oracle@qhdb2 tmp]$ pwd
/tmp
[oracle@qhdb2 bak]$ cat imp.par
file=tablespace.dmp
log=imp_tablespace.log
transport_tablespace=y
datafiles='/tmp/data_D-QHDB_I-4043740841_TS-SCOTT1_FNO-8_03seq7hi.dbf','/tmp/data_D-QHDB_I-4043740841_TS-SCOTT9_FNO-16_04seq7ie.dbf'
接下来就是导入表空间：
[oracle@qhdb2 bak]$ imp userid=/'sys/oracle as sysdba/' parfile=imp.par statistics=none
Import: Release 11.2.0.4.0 - Production on Mon Sep 18 23:45:00 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing SCOTT's objects into SCOTT
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "PAR2_TABLE" ("ID" NUMBER(*,0), "RETAIL_DATE" DATE, "WARE_NAME"
"" VARCHAR2(40)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE ""
"USERS" PARTITION BY RANGE ("ID" , "RETAIL_DATE" ) (PARTITION "PAR_01" VAL"
"UES LESS THAN (10000, TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:"
"SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS "
"255 TABLESPACE "SCOTT1" LOGGING NOCOMPRESS, PARTITION "PAR_02" VALUES LESS "
"THAN (20000, TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS"
"_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "SCOTT2" LOGGING NOCOMPRESS, PARTITION "PAR_03" VALUES LESS THAN (MAX"
"VALUE, MAXVALUE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "
""SCOTT3" LOGGING NOCOMPRESS )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USERS' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "PAR3_TABLE" ("ID" NUMBER(*,0), "REAL_DATE" DATE, "WARE_NAME" "
"VARCHAR2(50)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "US"
"ERS" PARTITION BY HASH ("ID" ) (PARTITION "PAR_01" TABLESPACE "SCOTT1", "
"PARTITION "PAR_02" TABLESPACE "SCOTT2" STORAGE(SEG_FILE 9 SEG_BLOCK 145 OB"
"JNO_REUSE 90076 INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREEL"
"IST GROUPS 1 BUFFER_POOL DEFAULT) )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USERS' does not exist
. importing HUBERT's objects into HUBERT
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "PAR2_TABLE" ("ID" NUMBER(*,0), "RETAIL_DATE" DATE, "WARE_NAME"
"" VARCHAR2(40)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE ""
"USERS" PARTITION BY RANGE ("ID" , "RETAIL_DATE" ) (PARTITION "PAR_01" VAL"
"UES LESS THAN (10000, TO_DATE(' 2011-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:"
"SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS "
"255 TABLESPACE "SCOTT1" LOGGING NOCOMPRESS, PARTITION "PAR_02" VALUES LESS "
"THAN (20000, TO_DATE(' 2012-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS"
"_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLE"
"SPACE "SCOTT2" LOGGING NOCOMPRESS, PARTITION "PAR_03" VALUES LESS THAN (MAX"
"VALUE, MAXVALUE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "
""SCOTT3" LOGGING NOCOMPRESS )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USERS' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "PAR3_TABLE" ("ID" NUMBER(*,0), "REAL_DATE" DATE, "WARE_NAME" "
"VARCHAR2(50)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "US"
"ERS" PARTITION BY HASH ("ID" ) (PARTITION "PAR_01" TABLESPACE "SCOTT1", "
"PARTITION "PAR_02" TABLESPACE "SCOTT2" STORAGE(SEG_FILE 9 SEG_BLOCK 1169 O"
"BJNO_REUSE 90401 INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREE"
"LIST GROUPS 1 BUFFER_POOL DEFAULT) )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'USERS' does not exist
. importing SCOTT1's objects into SCOTT1
. . importing table "T1"
. . importing table "T2"
. . importing table "T3"
. . importing table "EMP"
. importing SCOTT9's objects into SCOTT9
. . importing table "ALL_OBJECT"
. . importing table "EMP"
. . importing table "DEPT"
. importing SYS's objects into SYS
Import terminated successfully with warnings.
报错1：用户不存在：手工建立用户，当然了这是测试。生产环境中需要手工导入full=y 的元数据。
[oracle@qhdb2 bak]$ imp userid=/'sys/oracle as sysdba/' parfile=imp.par statistics=none
Import: Release 11.2.0.4.0 - Production on Mon Sep 18 23:44:39 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export client uses ZHS16GBK 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 29342:
"BEGIN sys.dbms_plugts.checkUser('SCOTT1'); END;"
IMP-00003: ORACLE error 29342 encountered
ORA-29342: user SCOTT1 does not exist in the database
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_PLUGTS", line 2056
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
sys@QHDB2>select distinct(tablespace_name) from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1
TEMP
SYSTEM
USERS_BAK
sys@QHDB2>
sys@QHDB2>create user scott9 account unlock identified by oracle;
User created.
sys@QHDB2>
sys@QHDB2>create user scott1 account unlock identified by oracle;
User created.
sys@QHDB2>
sys@QHDB2>select distinct(tablespace_name) from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSAUX
UNDOTBS1
TEMP
SCOTT9
SYSTEM
USERS_BAK
SCOTT1
7 rows selected.
sys@QHDB2>alter tablespace SCOTT1 read write;
Tablespace altered.
sys@QHDB2>alter tablespace SCOTT9 read write;
Tablespace altered.
版权声明：本文为博主原创文章，未经博主允许不得转载。
使用（X）TTS方式进行迁移
