前言:数据迁移是DBA日常工作之一,以下文章的实验细节比较全。如果原文作者觉得侵犯权益,请留言。
传输表空间的实质是,抽取表空间元数据传输 + 表空间下数据文件传输
PS:transport_datafiles这个参数不指定,则让oracle数据库自己选择数据文件。
- impdp directory=backup dumpfile=test01.dmp transport_datafiles=/u01/app/oracle/oradata/normal/test01.dbf remap_schema=source_test:target_test logfile =test.log
原文地址:http://blog.csdn.net/ch7543658/article/details/39271135
环境情况
Source 端:
操作系统: OracleLinux 6.2 64位endianness格式: little数据库版本:11.2.0.3
Target 端:
操作系统:OracleLinux 6.2 64位endianness 格式: little数据库版本:11.2.0.3
1、查看操作系统endianness格式
- col platform_name for a40
- 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
- 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
- 20 rows selected.
- --分别查看 source 端 和target端操作系统endianness格式
- --source
- SELECT d.PLATFORM_NAME,
- ENDIAN_FORMAT
- FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
- WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;
- PLATFORM_NAME ENDIAN_FORMAT
- ---------------------------------------- --------------
- Linux x86 64-bit Little
- --target
- SELECT d.PLATFORM_NAME,
- ENDIAN_FORMAT
- FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
- WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;
- PLATFORM_NAME ENDIAN_FORMAT
- ---------------------------------------- --------------
- Linux x86 64-bit Little
- select tablespace_name,
- status
- from dba_tablespaces;
- TABLESPACE_NAME STATUS
- ------------------------------ ---------
- SYSTEM ONLINE
- UNDOTBS1 ONLINE
- SYSAUX ONLINE
- TEMPTS1 ONLINE
- USERS ONLINE
- OUTLN ONLINE
- 6 rows selected.
- select file_name from dba_data_files;
- FILE_NAME
- ------------------------------------------------
- /u01/app/oracle/oradata/normal/system01.dbf
- /u01/app/oracle/oradata/normal/undotbs01.dbf
- /u01/app/oracle/oradata/normal/sysaux01.dbf
- /u01/app/oracle/oradata/normal/users01.dbf
- /u01/app/oracle/oradata/normal/undotbs02.dbf
- /u01/app/oracle/oradata/normal/system02.dbf
- /u01/app/oracle/oradata/normal/outln01.dbf
- 7 rows selected.
- --创建表空间创建表空间 tset
- create tablespace tset datafile '/u01/app/oracle/oradata/normal/test01.dbf' size 50M;
- Tablespace created.
- --创建用户source_test,并指定表空间
- --在source端
- create user source_test
- identified by oracle
- default tablespace tset
- temporary tablespace TEMPTS1;
- User created.
- grant connect,resource to source_test;
- Grant succeeded.
- --在target端(暂时只先创建用户)
- create user target_test
- identified by oracle
- temporary tablespace TEMPTS1;
- User created.
- grant connect,resource to target_test;
- Grant succeeded.
- --创建测试表
- SQL> conn source_test/oracle
- Connected.
- SQL> create table t1(id number, name varchar2(30));
- Table created.
- SQL> insert into t1 values(1, 'AAAAA');
- 1 row created.
- SQL> insert into t1 values(2, 'BBBBB');
- 1 row created.
- SQL> commit;
- Commit complete.
- select * from t1;
- ID NAME
- ---------- ------------------------------
- 1 AAAAA
- 2 BBBBB
- [oracle@normal ~]$ mkdir -p /u01/backup
- [oracle@normal ~]$ ls -l /u01
- total 24
- drwxr-xr-x 3 oracle oinstall 4096 Jul 28 12:31 app
- drwxr-xr-x 2 oracle oinstall 4096 Sep 14 16:21 backup
- SQL> show user
- USER is "SYS"
- SQL> create directory backup as '/u01/backup';
- Directory created.
- SQL> col owner format a5
- SQL> col directory_name format a25
- SQL> col DIRECTORY_PATH format a50
- SQL> select * from dba_directories;
- OWNER DIRECTORY_NAME DIRECTORY_PATH
- ----- ------------------------- --------------------------------------------------
- SYS BACKUP /u01/backup
- SYS OUTLN_DIR /home/oracle
- SYS DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/db_1/rdbms/log/
- SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state
- SQL> GRANT read, write ON DIRECTORY backup TO source_test;
- Grant succeeded.
- --在target端
- [oracle@test ~]$ mkdir -p /u01/backup
- [oracle@test ~]$ ls -l /u01
- total 24
- drwxr-xr-x 3 oracle oinstall 4096 Aug 28 09:09 app
- drwxr-xr-x 2 oracle oinstall 4096 Sep 14 16:40 backup
- SQL> show user
- USER is "SYS"
- SQL> create directory backup as '/u01/backup';
- Directory created.
- SQL> col owner format a5
- SQL> col directory_name format a25
- SQL> col DIRECTORY_PATH format a50
- SQL> select * from dba_directories;
- OWNER DIRECTORY_NAME DIRECTORY_PATH
- ----- ------------------------- --------------------------------------------------
- SYS BACKUP /u01/backup
- SYS OUTLN_DIR /home/oracle
- SYS DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/db_1/rdbms/log/
- SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state
- SQL> GRANT read, write ON DIRECTORY backup TO target_test;
- Grant succeeded.
- SQL> execute dbms_tts.transport_set_check('TSET', TRUE);
- PL/SQL procedure successfully completed.
- --查看自包含验证结果:
- SQL> select * from transport_set_violations;
- no rows selected
- --没有记录说明没有错
- SQL> alter tablespace TSET read only;
- Tablespace altered.
- select tablespace_name,
- status
- from dba_tablespaces;
- TABLESPACE_NAME STATUS
- ------------------------------ ---------
- SYSTEM ONLINE
- UNDOTBS1 ONLINE
- SYSAUX ONLINE
- TEMPTS1 ONLINE
- USERS ONLINE
- OUTLN ONLINE
- TSET READ ONLY
- 7 rows selected.
Transportable Tablespace Set有两部分:
1.expdp 导出的表空间的metadata
2.还有就是表空间对应的数据文件
- --expdp 导出的表空间的metadata
- [oracle@normal normal]$ pwd
- /u01/app/oracle/oradata/normal
- [oracle@normal normal]$ ll
- total 2294664
- -rw-r----- 1 oracle oinstall 9781248 Sep 14 16:46 control01.ctl
- drwx------ 2 oracle oinstall 16384 Aug 22 12:44 lost+found
- -rw-r----- 1 oracle oinstall 20979712 Sep 14 15:52 outln01.dbf
- -rw-r----- 1 oracle oinstall 52429312 Sep 14 16:45 redo01a.log
- -rw-r----- 1 oracle oinstall 52429312 Sep 14 16:45 redo01b.log
- -rw-r----- 1 oracle oinstall 52429312 Sep 14 15:52 redo02a.log
- -rw-r----- 1 oracle oinstall 52429312 Sep 14 15:52 redo02b.log
- -rw-r----- 1 oracle oinstall 52429312 Sep 14 15:52 redo03a.log
- -rw-r----- 1 oracle oinstall 52429312 Sep 14 15:52 redo03b.log
- -rw-r--r-- 1 oracle oinstall 22633 Aug 22 17:00 su.lst
- -rw-r----- 1 oracle oinstall 340795392 Sep 14 16:40 sysaux01.dbf
- -rw-r----- 1 oracle oinstall 340795392 Sep 14 16:43 system01.dbf
- -rw-r----- 1 oracle oinstall 314580992 Sep 14 16:43 system02.dbf
- -rw-r----- 1 oracle oinstall 20979712 Sep 14 15:53 temp01.dbf
- -rw-r----- 1 oracle oinstall 52436992 Sep 14 15:53 temp02.dbf
- -rw-r----- 1 oracle oinstall 52436992 Sep 14 16:31 test01.dbf
- -rw-r----- 1 oracle oinstall 209723392 Sep 14 16:43 undotbs01.dbf
- -rw-r----- 1 oracle oinstall 209723392 Sep 14 16:40 undotbs02.dbf
- -rw-r----- 1 oracle oinstall 524296192 Sep 14 15:52 users01.dbf
- [oracle@normal normal]$ expdp dumpfile=test01.dmp directory=backup transport_tablespaces=TSET transport_full_check=y logfile=TSET.log
- Export: Release 11.2.0.3.0 - Production on Sun Sep 14 16:54:30 2014
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- Username: / as sysdba
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /********/ AS SYSDBA dumpfile=test01.dmp directory=backup transport_tablespaces=TSET transport_full_check=y logfile=TSET.log
- 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/backup/test01.dmp
- ******************************************************************************
- Datafiles required for transportable tablespace TSET:
- /u01/app/oracle/oradata/normal/test01.dbf
- Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:55:13
- [oracle@normal normal]$ ls -l /u01/backup/
- total 80
- -rw-r----- 1 oracle oinstall 77824 Sep 14 16:55 test01.dmp
- -rw-r--r-- 1 oracle oinstall 1160 Sep 14 16:55 TSET.log
1)将表空间test 对应的数据文件copy到Target 对应的ORADATA目录下。
2)将expdp 导出的表空间metadta 数据copy 到Target 端的backup 目录下
- --将表空间test 对应的数据文件copy到Target 对应的ORADATA目录下。
- [oracle@normal normal]$ scp /u01/backup/test01.dmp 192.168.137.12:/u01/backup
- oracle@192.168.137.12 s password:
- test01.dmp 100% 76KB 76.0KB/s 00:00
- --将expdp 导出的表空间metadta 数据copy 到Target 端的backup 目录下
- [oracle@normal normal]$ scp test01.dbf 192.168.137.12:/u01/app/oracle/oradata/normal/test01.dbf
- oracle@192.168.137.12 s password:
- test01.dbf 100% 50MB 16.7MB/s 00:03
- --在target端查看文件是否已经传输
- [oracle@test ~]$ ll /u01/backup/
- total 76
- -rw-r----- 1 oracle oinstall 77824 Sep 14 17:03 test01.dmp
- [oracle@test ~]$ ll $ORACLE_BASE/oradata/normal/test01.dbf
- -rw-r----- 1 oracle oinstall 52436992 Sep 14 17:04 /u01/app/oracle/oradata/normal/test01.dbf
- [oracle@test ~]$ impdp directory=backup dumpfile=test01.dmp transport_datafiles=/u01/app/oracle/oradata/normal/test01.dbf remap_schema=source_test:target_test logfile
- =test.log
- Import: Release 11.2.0.3.0 - Production on Sun Sep 14 17:09:25 2014
- Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
- Username: / as sysdba
- Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
- With the Partitioning, OLAP, Data Mining and Real Application Testing options
- Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
- Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /********/ AS SYSDBA directory=backup dumpfile=test01.dmp transport_datafiles=/u01/app/oracle/oradata/normal/test01.dbf remap_schema=source_test:target_test logfile=test.log
- Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
- Processing object type TRANSPORTABLE_EXPORT/TABLE
- Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
- Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:09:55
- select tablespace_name,
- status
- from dba_tablespaces;
- TABLESPACE_NAME STATUS
- ------------------------------ ---------
- SYSTEM ONLINE
- UNDOTBS1 ONLINE
- SYSAUX ONLINE
- TEMPTS1 ONLINE
- USERS ONLINE
- OUTLN ONLINE
- TSET READ ONLY
- 7 rows selected.
- SQL> alter tablespace TSET read write;
- Tablespace altered.
- SQL> conn target_test/oracle
- Connected.
- SQL> select * from t1;
- ID NAME
- ---------- ------------------------------
- 1 AAAAA
- 2 BBBBB