tablespace tansport

       今天做了下表空间传输，现将有关信息记录下来

192.168.3.160（源数据库）
数据库信息：
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      10.2.0.1.0

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

建立一个独立自包含的表空间
SQL> create tablespace trans datafile '/opt/oracle/trans.dbf' size 10m;

Tablespace created.
建立用户

SQL> create user trans identified by trans default tablespace trans;

User created.

SQL> grant connect,resource to trans;

Grant succeeded.

SQL> conn trans/trans;
Connected.
SQL> create table test as select * from user_objects;

Table created.

SQL> select count(*) from test;

  COUNT(*)
----------
         1

SQL> cle scr
表空间为只读状态不然会报错
SQL> conn /as sysdba
Connected.
SQL> alter tablespace trans read only;

Tablespace altered.

SQL>
导出表空间
oracle@wxsz001:~> expdp system/qingsheng directory=dpdata dumpfile=trans.dmp transport_tablespaces=trans

Export: Release 10.2.0.4.0 - Production on Tuesday, 18 May, 2010 17:23:55

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=dpdata dumpfile=trans.dmp transport_tablespaces=trans
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:
  /opt/oracle/dpdata/trans.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:24:03
将数据文件及dmp文件复制到目标数据库所在系统
oracle@wxsz001:~> cd dpdata/
oracle@wxsz001:~/dpdata> ls
export.log  trans.dmp
oracle@wxsz001:~/dpdata> scp trans.dmp ../tran
tran/      trans.dbf 
oracle@wxsz001:~/dpdata> scp trans.dmp ../trans.dbf  oracle@192.168.1.77:/opt/oracle/dpdata/.
Password:
scp: /opt/oracle/dpdata/.: No such file or directory
oracle@wxsz001:~/dpdata> scp trans.dmp ../trans.dbf  oracle@192.168.1.77:/opt/oracle/trans/
Password:
trans.dmp                                                                              100%   76KB  76.0KB/s   00:00   
trans.dbf                                                                              100%   10MB  10.0MB/s   00:01   
oracle@wxsz001:~/dpdata> echo $NLS_LANG
american_america.ZHS16GBK

192.168.1.77（目标数据库）
数据库信息：
SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      10.2.0.1.0
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

导入前状态
不存在表空间trans
SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
USERS
SYSAUX
UNDOTBS1
SYSTEM
CMS_DATA
WBSC_DATA

6 rows selected.
用户trans必须存在
SQL> select username from dba_users where username='TRANS';

USERNAME
------------------------------
TRANS
用户trans没有表
SQL> conn trans/trans
Connected.
SQL> select * from tab;

no rows selected

oracle@linux-rpu7:~/trans> ls
trans.dbf  trans.dmp
oracle@linux-rpu7:~/trans> echo $NLS_LANG
american_america.ZHS16GBK
导入数据
oracle@linux-rpu7:~/trans> impdp system/qingsheng directory=trans dumpfile=trans.dmp transport_datafiles='/opt/oracle/trans/trans.dbf'

Import: Release 10.2.0.4.0 - Production on Tuesday, 18 May, 2010 20:39:10

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, 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/******** directory=trans dumpfile=trans.dmp transport_datafiles=/opt/oracle/trans/trans.dbf
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 20:39:14

oracle@linux-rpu7:~/trans>

导入后状态

SQL> select tablespace_name from dba_data_files;

TABLESPACE_NAME
------------------------------
USERS
SYSAUX
UNDOTBS1
SYSTEM
CMS_DATA
WBSC_DATA
TRANS

7 rows selected.

SQL> conn trans/trans
Connected.
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST                           TABLE

SQL> select count(*) from test;

  COUNT(*)
----------
         1

遇到错误：1
oracle@wxsz001:~> expdp wbsc/temobiwbsc directory=tran dumpfile=wbsc.dmp transport_tablespaces=wbsc_data

Export: Release 10.2.0.4.0 - Production on 星期二, 18 5月, 2010 10:09:19

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "WBSC.SYS_EXPORT_TRANSPORTABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 871
ORA-01647: tablespace 'WBSC_DATA' is read only, cannot allocate space in it

原因：因为使用WBSC用户来进行导出WBSC用户的表空间。

2：使用不同版本在没进行转换前不能导入
oracle@wxsz001:~/dpdata> impdp system/qingsheng directory=dpdata dumpfile=trans.dmp transport_datafiles='/opt/oracle/dpdata/trans.dbf'

Import: Release 10.2.0.4.0 - Production on Tuesday, 18 May, 2010 17:11:32

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, 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/******** directory=dpdata dumpfile=trans.dmp transport_datafiles=/opt/oracle/dpdata/trans.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-00721: changes by release 10.2.0.3.0 cannot be used by release 10.2.0.1.0

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 17:11:34

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22779291/viewspace-663034/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22779291/viewspace-663034/