用途:数据迁移
演示场景:
原库:linux x86_64 oracle 11.2.0.4
目标端:
-- 导出元数据
将这两个文件'/u01/bak/metaexpdat.dmp','/u01/app/oracle/oradata/PROD/tbs1.dbf',传输到目标端指定路径,如果大的话可以压缩下
这里也可以使用exp导出元数据,比如:
exp userid=\’/ as sysdba\’ tablespaces=tbs1 file=/tmp/tbs1.dmp transport_tablespace=y
相应导入imp userid=\’/ as sysdba\’ tablespaces=tbs1 file=/tmp/tbs1.dmp transport_tablespace=y datafiles=XXX/tbs01.dbf, xxx/tbs02.dbf fromuser=u1 touser=u2 4.1创建待导入的用户
演示场景:
原库:linux x86_64 oracle 11.2.0.4
目标:linux x86_64 oracle 12.2.0.1
过程
0, 创建试验环境
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/PROD/tbs1.dbf' size 10m autoextend on maxsize 100M;
Tablespace created.
SQL> create user u1 identified by u1 default tablespace tbs1;
User created.
SQL> grant connect,resource to u1;
Grant succeeded.
SQL> conn u1/u1;
Connected.
SQL> create table t1(id int,name varchar2(20));
Table created.
SQL> insert into t1 (id,name) values(1,'china');
1 row created.
SQL> commit;
Commit complete.
1,检查原库,目标库支持的平台、本地平台类型和端序
SQL> 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-64) Little
21 Apple Mac OS (x86-64) Little
20 rows selected.
SQL> SELECT t2.PLATFORM_NAME,ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM t1,V$DATABASE t2 WHERE t1.PLATFORM_NAME = t2.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
---------------------------------------- --------------
Linux x86 64-bit Little
目标端:
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT CON_ID
----------- ------------------------------ -------------- ----------
1 Solaris[tm] OE (32-bit) Big 0
2 Solaris[tm] OE (64-bit) Big 0
7 Microsoft Windows IA (32-bit) Little 0
10 Linux IA (32-bit) Little 0
6 AIX-Based Systems (64-bit) Big 0
3 HP-UX (64-bit) Big 0
5 HP Tru64 UNIX Little 0
4 HP-UX IA (64-bit) Big 0
11 Linux IA (64-bit) Little 0
15 HP Open VMS Little 0
8 Microsoft Windows IA (64-bit) Little 0
9 IBM zSeries Based Linux Big 0
13 Linux x86 64-bit Little 0
16 Apple Mac OS Big 0
12 Microsoft Windows x86 64-bit Little 0
17 Solaris Operating System (x86) Little 0
18 IBM Power Based Linux Big 0
19 HP IA Open VMS Little 0
20 Solaris Operating System (x86- Little 0
64)
21 Apple Mac OS (x86-64) Little 0
20 rows selected.
SQL> SELECT t2.PLATFORM_NAME,ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM t1,V$DATABASE t2 WHERE t1.PLATFORM_NAME = t2.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------ --------------
Linux x86 64-bit Little
2,检查是否满足条件,如果满足导出元数据
另外需要确定的是,字符集
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('tbs1', TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
SQL> alter tablespace tbs1 read only;
Tablespace altered.
-- 导出元数据
[oracle@prod ~]$ expdp \'/ as sysdba \' directory=DMP_DIR dumpfile=metaexpdat.dmp transport_tablespaces=tbs1
Export: Release 11.2.0.4.0 - Production on Mon Nov 27 16:54:34 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
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" directory=DMP_DIR dumpfile=metaexpdat.dmp transport_tablespaces=tbs1
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/bak/metaexpdat.dmp
******************************************************************************
Datafiles required for transportable tablespace TBS1:
/u01/app/oracle/oradata/PROD/tbs1.dbf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Nov 27 16:55:10 2017 elapsed 0 00:00:35
将这两个文件'/u01/bak/metaexpdat.dmp','/u01/app/oracle/oradata/PROD/tbs1.dbf',传输到目标端指定路径,如果大的话可以压缩下
这里也可以使用exp导出元数据,比如:
exp userid=\’/ as sysdba\’ tablespaces=tbs1 file=/tmp/tbs1.dmp transport_tablespace=y
相应导入imp userid=\’/ as sysdba\’ tablespaces=tbs1 file=/tmp/tbs1.dmp transport_tablespace=y datafiles=XXX/tbs01.dbf, xxx/tbs02.dbf fromuser=u1 touser=u2
3,转换源端数据文件格式匹配目标端
(此步骤不用,因为第一步骤中检查源目标一致)
如不一致,比如源端是'Microsoft Windows x86 64-bit',在目标端进入RMAN,进行转换
RMAN> convert datafile '/home/oracle/transport_tbs/tbs1.dbf' FROM PLATFORM 'Microsoft Windows x86 64-bit' FORMAT '/u01/app/oracle/oradata/cdb1/pdb1/bak/tbs1.dbf';
4,导入元数据和数据文件
4.1创建待导入的用户
SQL> create user u2 identified by u2 ;
User created.
SQL> grant connect,resource to u2;
Grant succeeded.
4.2,导入元数据和数据文件
如果多个文件逗号隔开即可
[oracle@centos7 ~]$ impdp system/oracle@pdb2 directory=dp_dir dumpfile=metaexpdat.dmp datafiles='/u01/app/oracle/oradata/cdb1/tbs1.dbf' remap_schema=u1:u2
Import: Release 12.2.0.1.0 - Production on Mon Nov 27 17:53:53 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Legacy Mode Active due to the following parameters:
Legacy Mode Parameter: "datafiles=/u01/app/oracle/oradata/cdb1/tbs1.dbf" Location: Command Line, Replaced with: "transport_datafiles=/u01/app/oracle/oradata/cdb1/tbs1.dbf"
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@pdb2 directory=dp_dir dumpfile=metaexpdat.dmp transport_datafiles=/u01/app/oracle/oradata/cdb1/tbs1.dbf remap_schema=u1:u2
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 Mon Nov 27 17:54:05 2017 elapsed 0 00:00:09
5,查看结果
SQL> conn u2/u2@pdb2
Connected.
SQL> select * from t1;
ID NAME
---------- --------------------
1 china
SQL> insert into t1 values(2,'USA');
insert into t1 values(2,'USA')
*
ERROR at line 1:
ORA-00372: file 17 cannot be modified at this time
ORA-01110: data file 17: '/u01/app/oracle/oradata/cdb1/tbs1.dbf'
SQL> conn system/oracle@pdb2
Connected.
SQL> alter tablespace tbs1 read write;
Tablespace altered.
SQL> conn u2/u2@pdb2
Connected.
SQL> insert into t1 values(2,'USA');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1;
ID NAME
---------- --------------------
1 china
2 USA