实验环境:
源数据库:oracle 10g(Release 10.2.0.1.0)
目标数据库:oracle 10g(Release 10.2.0.1.0)
待传输的表空间:TEST
1.在test(默认表空间是TEST)用户下面创建一张test表。
SQL> select * from tab;
no rows selected
SQL> create table test (id int primary key)
2 ;
Table created.
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(2);
1 row created.
SQL> insert into test values(3);
1 row created.
SQL> show user;
USER is "TEST"
SQL> commit;
Commit complete.
在传输之前:
(1)确认平台是否支持:若是不同平台需要检查平台版本及Endian Format。
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('test',true);
BEGIN DBMS_TTS.TRANSPORT_SET_CHECK('test',true); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_TTS.TRANSPORT_SET_CHECK' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> conn sys/oracle as sysdba
Connected.
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('test',true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
严格方式验证:
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('test',true,true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
3.
(1)
SQL> conn test/oracle
Connected.
SQL> select username,default_tablespace from user_users;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST TEST
SQL> conn sys/oracle as sysdba
Connected.
SQL> alter tablespace test read only;
Tablespace altered.
(2)
[oracle@linux5 dpdump]$ expdp system/oracle dumpfile=test.dmp directory=data_pump_dir transport_tablespaces=test nologfile=y
Export: Release 10.2.0.1.0 - Production on Sunday, 13 April, 2014 20:01:58
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=test.dmp directory=data_pump_dir transport_tablespaces=test nologfile=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
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:
/u01/app/oracle/10.2.0/db_1/rdbms/log/test.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 20:03:05
[oracle@linux5 dpdump]$ cd /u01/app/oracle/10.2.0/db_1/rdbms/log
[oracle@linux5 log]$ ls
dp.log test.dmp
(3)
4.
SQL> select * from dba_directories where directory_name='DUMP_DIR';
OWNER DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
SYS DUMP_DIR
/u01/dmp
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
RMANTBS ONLINE
TEST READ ONLY
8 rows selected.
SQL> alter tablespace test read write;
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
RMANTBS ONLINE
TEST ONLINE
8 rows selected.
5.
SQL> select block_size from dba_tablespaces where tablespace_name='TEST';
BLOCK_SIZE
----------
8192
SQL> show parameter BLOCK_SIZE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
[oracle@localhost orclstd]$ scp oracle@192.168.1.222:/u01/app/oracle/oradata/orcl/test.dbf /u01/dmp
oracle@192.168.1.222's password:
test.dbf 100% 50MB 5.6MB/s 00:09
[oracle@localhost orclstd]$ impdp test/oracle dumpfile=test.dmp directory=dump_dir nologfile=y transport_datafiles=/u01/dmp/test.dbf remap_schema=test:test
Import: Release 10.2.0.1.0 - Production on Sunday, 13 April, 2014 20:36:05
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TRANSPORTABLE_01": test/******** dumpfile=test.dmp directory=dump_dir nologfile=y transport_datafiles=/u01/dmp/test.dbf remap_schema=test:test
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-19721: Cannot find datafile with absolute file number 7 in tablespace TEST
Job "TEST"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 20:38:33
返回查看:
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='TEST';
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
/u01/app/oracle/oradata/orcl/tests.dbf
TEST
看来是拷贝错了数据文件,重头再来:
SQL> alter tablespace test read only;
Tablespace altered.
[oracle@linux5 orcl]$ expdp system/oracle dumpfile=test.dmp directory=dump_file_dir transport_tablespaces=test nologfile=y
Export: Release 10.2.0.1.0 - Production on Monday, 14 April, 2014 1:27:00
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=test.dmp directory=dump_file_dir transport_tablespaces=test nologfile=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
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:
/u01/imp_exp/dmp/test.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 01:27:26
[oracle@localhost dmp]$ scp oracle@192.168.1.222:/u01/imp_exp/dmp/test.dmp /u01/dmp
oracle@192.168.1.222's password:
test.dmp 100% 64KB 64.0KB/s 00:01
[oracle@localhost dmp]$ scp oracle@192.168.1.222:/u01/app/oracle/oradata/orcl/tests.dbf /u01/dmp
oracle@192.168.1.222's password:
tests.dbf 100% 50MB 2.1MB/s 00:24
SQL> alter tablespace test read write;
Tablespace altered.
应该没问题可以……
[oracle@localhost dmp]$ impdp test/oracle dumpfile=test.dmp directory=dump_dir nologfile=y transport_datafiles=/u01/dmp/tests.dbf
Import: Release 10.2.0.1.0 - Production on Monday, 14 April, 2014 1:33:15
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "TEST"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TRANSPORTABLE_01": test/******** dumpfile=test.dmp directory=dump_dir nologfile=y transport_datafiles=/u01/dmp/tests.dbf
Processing object typ