0、準備工作
--創建被傳輸的表空間
SQL> create tablespace tbs_single
datafile 'c:\oracle\oradata\ora9i\tbs_single01.dbf' size 100M
extent management local;
--創建用戶,并將表空間作為默認表空間
SQL> create user tranp identified by oracle default tablespace tbs_single;
SQL> grant connect,resource to tranp;
--在該表空間創建表,用于測試
SQL> create table tranp.t01 as select * from sys.dba_objects;
1、檢查源、目標平臺Endianness
在源 數據庫 平臺上:
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
2 FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d
3 WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
PLATFORM_NAME ENDIAN_FORMAT
------------------------------------ --------------
Microsoft Windows IA (32-bit) Little
在目標數據庫平臺上:
SQL> 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 IA (32-bit) Little
由于源和目標平臺的Endianness一致,可以省去convert這一步。
2、檢查要表空間是否自包含
SQL> EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TBS_SINGLE',true);
PL/SQL過程已成功完成。
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
未選定行
說明表空間是自包含的。
3、產生傳輸表空間集
創建目錄對象
SQL> create DIRECTORY tranp_dir as 'c:\software';
目錄已創建。
SQL> grant read,write on DIRECTORY tranp_dir to public;
授權成功。
將表空間置為只讀。
SQL> alter tablespace tbs_single read only;
表空間已更改。
使用數據泵導出傳輸表空間的元數據
注:如果Endianness不一致,還需要使用RMAN進行轉換表空間的數據文件。
4、傳送表空間集
將表空間的數據文件和導出的DMP文件,傳送到目標數據庫平臺上。
5、導入表空間
在目標數據庫中,創建相應的目錄對象和用戶。
SQL> create directory tranp_dir as '/home/oracle';
Directory created.
SQL> grant read,write on directory tranp_dir to public;
Grant succeeded.
SQL> create user tranp identified by oracle;
User created.
SQL> grant connect,resource to tranp;
Grant succeeded.
使用數據庫泵,導入到目標數據庫中。
[oracle@ocmu ~]$ impdp system/oracle dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp
Import: Release 10.2.0.1.0 - Production on Thursday, 30 August, 2012 23:40:25
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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp
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 23:40:29
將被導入的表空間置為read write。
SQL> alter tablespace TBS_SINGLE read write;
Tablespace altered.
6、測試
目標庫中,進行測試。
SQL> select name from v$tablespace;
NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
TBS_SINGLE
。。。。。。。。。。。。。。。。。
13 rows selected.
SQL> conn tranp/oracle
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T01 TABLE
SQL> select count(*) from t01;
COUNT(*)
----------
49795
SQL> conn / as sysdba
Connected.
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
TBS_SINGLE ONLINE
。。。。。。。。。。。。。。。。。。。。。。。。
13 rows selected.
7、問題
問題描述:
oracle@ocmu ~]$ impdp system/oracle dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp
Import: Release 10.2.0.1.0 - Production on Thursday, 30 August, 2012 23:25:47
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 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=TBSINGLE.DMP directory=tranp_dir transport_datafiles=/u01/app/oracle/oradata/PROD/TBS_SINGLE01.DBF remap_schema=tranp:tranp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-29345: cannot plug a tablespace into a database using an incompatible character set
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 23:25:53
a元數據庫:
SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
2 (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
3 (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
----------------------------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
b目標數據庫:
SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
2 (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
3 (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
----------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8
原因:由于源和目標數據庫的字符集不一致或不是子集和超集的關系,所以造成不能導入表空間的字符集。
解決方法:將源數據庫和目標數據庫的字符集調整為一致的字符集;或者源數據庫的字符集是目標數據庫的子集。
源數據庫的修改:
SQL> alter database character set internal_use utf8;
alter database character set internal_use utf8
*
第1行出現錯誤:
ORA-12719:操作要求數據庫處于RESTRICTED模式下
SQL> shutdown immediate;
數據庫已經關閉。
已經卸載數據庫。
ORACLE例程已經關閉。
SQL> startup mount;
ORACLE例程已啟動
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 138412736 bytes
Database Buffers 301989888 bytes
Redo Buffers 2973696 bytes
數據庫已裝載
SQL> alter system enable restricted session;
系統已更改
SQL> alter database open;
數據庫已更改
SQL> alter database character set internal_use utf8;
數據庫已更改
SQL> shutdown immediate;
數據庫已經關閉。
已經卸載數據庫。
ORACLE例程已經關閉。
SQL> startup mount;
ORACLE例程已啟動
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 138412736 bytes
Database Buffers 301989888 bytes
Redo Buffers 2973696 bytes
數據庫已裝載
SQL> alter system disable restricted session;
系統已更改
SQL> alter database open;
數據庫已更改
SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
2 (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
3 (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
----------------------------------------------------------------------
AMERICAN_AMERICA.UTF8
目標數據庫修改:
SQL> alter database character set internal_use utf8;
alter database character set internal_use utf8
*
ERROR at line 1:
ORA-12719: operation requires database is in RESTRICTED mode
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 138412736 bytes
Database Buffers 301989888 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter system enable restricted session;
System altered.
SQL> alter database open;
Database altered.
SQL> alter database character set internal_use utf8;
Database altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size 1219904 bytes
Variable Size 138412736 bytes
Database Buffers 301989888 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> alter system disable restricted session;
System altered.
SQL> alter database open;
Database altered.
SQL> select tab1.aa||'_'||tab2.bb||'.'||tab3.cc from
2 (select VALUE$ aa from sys.props$ where name='NLS_LANGUAGE')tab1,
3 (select VALUE$ bb from sys.props$ where name='NLS_ISO_CURRENCY')tab2,
4 (select VALUE$ cc from sys.props$ where name='NLS_CHARACTERSET')tab3;
TAB1.AA||'_'||TAB2.BB||'.'||TAB3.CC
----------------------------------------------------------------------
AMERICAN_AMERICA.UTF8
修改完成,再重新導出/導入一遍,即可成功。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
