利用oracle10g_rman_convert_transportable tablespace遷移表空間
前言:
利用rman cross-platform. transfortable databases and tablespaces 跨os平臺遷移資料庫或表空間
不同是指:位元組序不同(可在源庫rman convert tablespace 或者在目標庫convert datafile)
1,在源庫
convert tablespace ts_1,..ts_n to platform. 'platform_name' ---v$transportable_platform
convert相關選項:
parallelism n --n不能>資料檔案數,不然沒有意義
db_file_name_convert ---用於為轉換的資料檔案生成新的檔名字
format ---提供一個模組用於產生新的獨立的檔名(為轉換檔案)
示例:
1,select platform_id,platform_name,.endian_format from v$transportable_platform
where upper(platform_name) like 'LINUX';
2,rman target /
convert tablespace newly to platform. ''
format='/tmp/transport_linux/%U';
3,利用exp生成dump file
4,把第2步生成的轉換檔案和第3步生成的dump file cp到目標主機上(對應目錄)
5,利用imp把表空間匯入目標庫
6,源：solaris 10 for sparc
目標:rhel5 for x86
實施如下;
前提條件:
a,在源和目標庫構建directory(以sysdba使用者)
b,以system使用者在源和目標庫進行expdp/impdp
c,在源庫進行cross os表空間匯出時,先須offline(alter tablespace crosstbs read only;)
d,在源庫和目標庫要構建cross os表空間的對應使用者
e,源庫和目標庫儲存dmp檔案的目錄(directory),可一致也可不一樣,這個
1,在源庫建立要cross的表空間及對應的使用者
-bash-3.00$ export ORACLE_SID=target
-bash-3.00$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 30 16:56:51 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/orainstall/oradata/target/system01.dbf
/orainstall/oradata/target/undotbs01.dbf
/orainstall/oradata/target/sysaux01.dbf
/orainstall/oradata/target/users01.dbf
/orainstall/oradata/target/test01.dbf
SQL> create tablespace crosstbs datafile '/orainstall/oradata/target/crosstbs.dbf' size 20m;
Tablespace created.
SQL> create user crosstbs identified by system default tablespace crosstbs;
User created.
SQL> grant resource,connect to crosstbs;
Grant succeeded.
SQL> conn crosstbs/system
Connected.
SQL> create table liusha(a int);---建立測試表
Table created.
SQL> insert into liusha values(1);
1 row created.
SQL> commit;
Commit complete.
SQL>
2,在源庫匯出要cross os的表空間
bash-3.00$ expdp system/system directory=cross_dir dumpfile=exp_crosstbs.dmp transport_tablespaces=crosstbs
Export: Release 10.2.0.1.0 - 64bit Production on Monday, 30 November, 2009 19:33:45
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=cross_dir dumpfile=exp_crosstbs.dmp transport_tablespaces=crosstbs
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:
/orainstall/cross/exp_crosstbs.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 19:34:19
3,在源庫對cross os的表空間資料檔案進行轉換
bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Nov 30 19:34:54 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TARGET (DBID=3106171664)
RMAN> convert tablespace crosstbs
2> to platform. 'Linux IA (32-bit)'
3> format='/orainstall/cross/%U';
Starting backup at 30-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/orainstall/oradata/target/crosstbs.dbf
converted datafile=/orainstall/cross/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 30-NOV-09
RMAN> exit
4,在目標庫利用ftp把源庫產生的dmp檔案及轉換檔案(rman加工生產),下載到目標庫
[oracle@rhel5 sk]$ ftp 172.16.31.72
Connected to 172.16.31.72.
220 capitek1 FTP server ready.
334 Using AUTH type GSSAPI; ADAT must follow
GSSAPI accepted as authentication type
GSSAPI error major: An invalid name was supplied
GSSAPI error minor: Cannot determine realm for numeric host address
GSSAPI error: initializing context
GSSAPI authentication failed
504 AUTH KERBEROS_V4 not supported.
KERBEROS_V4 rejected as an authentication type
Name (172.16.31.72:oracle):
331 Password required for oracle.
Password:
230 User oracle logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> pwd
257 "/orainstall" is current directory.
ftp> ls
227 Entering Passive Mode (172,16,31,72,181,172)
150 Opening ASCII mode data connection for /bin/ls.
總數 8305376
-rw------- 1 oracle oinstall 7832 11月 30日 17:52 .bash_history
-rw-r--r-- 1 oracle oinstall 509 11月 23日 14:43 .profile
-rw-r--r-- 1 oracle oinstall 398958592 11月 23日 14:30 10gr2_cluster_sol.cpio
drwxr-x--- 5 oracle oinstall 512 11月 30日 13:49 admin
-rw-r--r-- 1 oracle oinstall 119 11月 30日 17:12 afiedt.buf
drwxr-xr-x 2 oracle oinstall 512 2005 8月 18 cluvfy
drwxr-xr-x 2 oracle oinstall 512 11月 30日 19:36 cross
drwxr-xr-x 6 oracle oinstall 512 2005 8月 18 doc
drwxr-x--- 4 oracle oinstall 512 11月 30日 14:58 flash_recovery_area
drwxr-xr-x 4 oracle oinstall 512 2005 8月 18 install
drwxr-xr-x 2 oracle oinstall 512 11月 30日 17:34 newarch
drwxr-xr-x 2 oracle oinstall 512 11月 23日 23:31 ocrbak
drwxr-xr-x 6 oracle oinstall 512 11月 29日 16:26 oraInventory
drwxrwx--- 9 oracle oinstall 512 2008 9月 16 oracle
-rw-r--r-- 1 oracle oinstall 3850654208 11月 23日 14:40 oracle10g.tar
drwxr-x--- 4 oracle oinstall 512 11月 30日 13:51 oradata
drwxr-xr-x 2 oracle oinstall 512 2005 8月 18 racpatch
drwxr-xr-x 2 oracle oinstall 512 2005 8月 18 response
drwxr-xr-x 2 oracle oinstall 512 11月 30日 13:57 rmanbak
-rwxr-xr-x 1 oracle oinstall 1331 2005 8月 18 runInstaller
-rw-r--r-- 1 oracle oinstall 1049 11月 30日 14:00 same.ora
-rw-r--r-- 1 oracle oinstall 1992 11月 29日 21:26 sqlnet.log
drwxr-xr-x 9 oracle oinstall 2560 2005 8月 18 stage
-rw-r--r-- 1 oracle oinstall 1091 11月 29日 20:11 target.ora
-rw-r--r-- 1 oracle oinstall 0 11月 29日 18:04 tt.txt
drwxr-xr-x 2 oracle oinstall 512 2005 8月 18 upgrade
-rw-r--r-- 1 oracle oinstall 3529 2005 8月 6 welcome.html
-rw-r--r-- 1 oracle oinstall 586240 11月 29日 23:20 x.tar
226 Transfer complete.
ftp> bin
200 Type set to I.
ftp> prompt
Interactive mode off.
ftp> cd cross
250 CWD command successful.
ftp> pwd
257 "/orainstall/cross" is current directory.
ftp> ls
227 Entering Passive Mode (172,16,31,72,244,242)
150 Opening ASCII mode data connection for /bin/ls.
總數 41162
-rw-r----- 1 oracle oinstall 20979712 11月 30日 19:36 data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
-rw-r----- 1 oracle oinstall 69632 11月 30日 19:34 exp_crosstbs.dmp
-rw-r----- 1 oracle oinstall 944 11月 30日 19:34 export.log
226 Transfer complete.
ftp> mget *
local: data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig remote: data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
227 Entering Passive Mode (172,16,31,72,85,166)
150 Opening BINARY mode data connection for data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig (20979712 bytes).
226 Transfer complete.
20979712 bytes received in 2.8 seconds (7.2e+03 Kbytes/s)
local: exp_crosstbs.dmp remote: exp_crosstbs.dmp
227 Entering Passive Mode (172,16,31,72,54,8)
150 Opening BINARY mode data connection for exp_crosstbs.dmp (69632 bytes).
226 Transfer complete.
69632 bytes received in 0.014 seconds (4.9e+03 Kbytes/s)
local: export.log remote: export.log
227 Entering Passive Mode (172,16,31,72,242,137)
150 Opening BINARY mode data connection for export.log (944 bytes).
226 Transfer complete.
944 bytes received in 4.9e-05 seconds (1.9e+04 Kbytes/s)
ftp> bye
221-You have transferred 21050288 bytes in 3 files.
221-Total traffic for this session was 21054054 bytes in 6 transfers.
221-Thank you for using the FTP service on capitek1.
221 Goodbye.
5,在目標庫實施impdp匯入表空間
[oracle@rhel5 sk]$ impdp system/system dumpfile=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/sk/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
Import: Release 10.2.0.1.0 - Production on Monday, 30 November, 2009 11:04:02
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=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/sk/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
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 11:04:05
[oracle@rhel5 sk]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 30 11:04:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
6,在目標庫對匯入的cross os表空間進行read write操作
sqlplus '/as sysdba'
alter tablespace crosstbs read write;
小結及排錯:
1,源與目標庫的字符集要一樣,不然impdp會報錯
(提示匯入表空間因字符集不匹配失敗--處理:
2,修改目標庫字符集後,再次進行impdp報如下錯誤:
[oracle@rhel5 ~]$ impdp system/system dumpfile=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_12kvlp9b
Import: Release 10.2.0.1.0 - Production on Monday, 30 November, 2009 10:59:08
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=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_12kvlp9b
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-19722: datafile /home/oracle/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_12kvlp9b is an incorrect version
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:59:10
處理:再行從源庫進行expdp及rman 的convert轉換檔案,最後在目標庫impdp就可以了
3,中途相關錯誤,摘錄如下:
[oracle@rhel5 ~]$ impdp system/system dumpfile=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/crosstbs.dbf
Import: Release 10.2.0.1.0 - Production on Monday, 30 November, 2009 10:57:48
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
ORA-39006: internal error
ORA-39213: Metadata processing is not available
[oracle@rhel5 ~]$ oerr ora 39006
39006, 00000, "internal error"
// *Cause: An unexpected error occurred while processing a Data Pump job.
// Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
// will further describe the error.
// *Action: Contact Oracle Customer Support.
[oracle@rhel5 ~]$ oerr ora 39213
39213, 00000, "Metadata processing is not available"
// *Cause: The Data Pump could not use the Metadata API. Typically,
// this is caused by the XSL stylesheets not being set up properly.
// *Action: Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
// to reload the stylesheets.
[oracle@rhel5 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 30 10:58:27 2009
Copyright (c) 1982, 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
SQL> exec dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.
SQL> exit
利用rman cross-platform. transfortable databases and tablespaces 跨os平臺遷移資料庫或表空間
不同是指:位元組序不同(可在源庫rman convert tablespace 或者在目標庫convert datafile)
1,在源庫
convert tablespace ts_1,..ts_n to platform. 'platform_name' ---v$transportable_platform
convert相關選項:
parallelism n --n不能>資料檔案數,不然沒有意義
db_file_name_convert ---用於為轉換的資料檔案生成新的檔名字
format ---提供一個模組用於產生新的獨立的檔名(為轉換檔案)
示例:
1,select platform_id,platform_name,.endian_format from v$transportable_platform
where upper(platform_name) like 'LINUX';
2,rman target /
convert tablespace newly to platform. ''
format='/tmp/transport_linux/%U';
3,利用exp生成dump file
4,把第2步生成的轉換檔案和第3步生成的dump file cp到目標主機上(對應目錄)
5,利用imp把表空間匯入目標庫
6,源：solaris 10 for sparc
目標:rhel5 for x86
實施如下;
前提條件:
a,在源和目標庫構建directory(以sysdba使用者)
b,以system使用者在源和目標庫進行expdp/impdp
c,在源庫進行cross os表空間匯出時,先須offline(alter tablespace crosstbs read only;)
d,在源庫和目標庫要構建cross os表空間的對應使用者
e,源庫和目標庫儲存dmp檔案的目錄(directory),可一致也可不一樣,這個
1,在源庫建立要cross的表空間及對應的使用者
-bash-3.00$ export ORACLE_SID=target
-bash-3.00$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 30 16:56:51 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/orainstall/oradata/target/system01.dbf
/orainstall/oradata/target/undotbs01.dbf
/orainstall/oradata/target/sysaux01.dbf
/orainstall/oradata/target/users01.dbf
/orainstall/oradata/target/test01.dbf
SQL> create tablespace crosstbs datafile '/orainstall/oradata/target/crosstbs.dbf' size 20m;
Tablespace created.
SQL> create user crosstbs identified by system default tablespace crosstbs;
User created.
SQL> grant resource,connect to crosstbs;
Grant succeeded.
SQL> conn crosstbs/system
Connected.
SQL> create table liusha(a int);---建立測試表
Table created.
SQL> insert into liusha values(1);
1 row created.
SQL> commit;
Commit complete.
SQL>
2,在源庫匯出要cross os的表空間
bash-3.00$ expdp system/system directory=cross_dir dumpfile=exp_crosstbs.dmp transport_tablespaces=crosstbs
Export: Release 10.2.0.1.0 - 64bit Production on Monday, 30 November, 2009 19:33:45
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** directory=cross_dir dumpfile=exp_crosstbs.dmp transport_tablespaces=crosstbs
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:
/orainstall/cross/exp_crosstbs.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 19:34:19
3,在源庫對cross os的表空間資料檔案進行轉換
bash-3.00$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Nov 30 19:34:54 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TARGET (DBID=3106171664)
RMAN> convert tablespace crosstbs
2> to platform. 'Linux IA (32-bit)'
3> format='/orainstall/cross/%U';
Starting backup at 30-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/orainstall/oradata/target/crosstbs.dbf
converted datafile=/orainstall/cross/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 30-NOV-09
RMAN> exit
4,在目標庫利用ftp把源庫產生的dmp檔案及轉換檔案(rman加工生產),下載到目標庫
[oracle@rhel5 sk]$ ftp 172.16.31.72
Connected to 172.16.31.72.
220 capitek1 FTP server ready.
334 Using AUTH type GSSAPI; ADAT must follow
GSSAPI accepted as authentication type
GSSAPI error major: An invalid name was supplied
GSSAPI error minor: Cannot determine realm for numeric host address
GSSAPI error: initializing context
GSSAPI authentication failed
504 AUTH KERBEROS_V4 not supported.
KERBEROS_V4 rejected as an authentication type
Name (172.16.31.72:oracle):
331 Password required for oracle.
Password:
230 User oracle logged in.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> pwd
257 "/orainstall" is current directory.
ftp> ls
227 Entering Passive Mode (172,16,31,72,181,172)
150 Opening ASCII mode data connection for /bin/ls.
總數 8305376
-rw------- 1 oracle oinstall 7832 11月 30日 17:52 .bash_history
-rw-r--r-- 1 oracle oinstall 509 11月 23日 14:43 .profile
-rw-r--r-- 1 oracle oinstall 398958592 11月 23日 14:30 10gr2_cluster_sol.cpio
drwxr-x--- 5 oracle oinstall 512 11月 30日 13:49 admin
-rw-r--r-- 1 oracle oinstall 119 11月 30日 17:12 afiedt.buf
drwxr-xr-x 2 oracle oinstall 512 2005 8月 18 cluvfy
drwxr-xr-x 2 oracle oinstall 512 11月 30日 19:36 cross
drwxr-xr-x 6 oracle oinstall 512 2005 8月 18 doc
drwxr-x--- 4 oracle oinstall 512 11月 30日 14:58 flash_recovery_area
drwxr-xr-x 4 oracle oinstall 512 2005 8月 18 install
drwxr-xr-x 2 oracle oinstall 512 11月 30日 17:34 newarch
drwxr-xr-x 2 oracle oinstall 512 11月 23日 23:31 ocrbak
drwxr-xr-x 6 oracle oinstall 512 11月 29日 16:26 oraInventory
drwxrwx--- 9 oracle oinstall 512 2008 9月 16 oracle
-rw-r--r-- 1 oracle oinstall 3850654208 11月 23日 14:40 oracle10g.tar
drwxr-x--- 4 oracle oinstall 512 11月 30日 13:51 oradata
drwxr-xr-x 2 oracle oinstall 512 2005 8月 18 racpatch
drwxr-xr-x 2 oracle oinstall 512 2005 8月 18 response
drwxr-xr-x 2 oracle oinstall 512 11月 30日 13:57 rmanbak
-rwxr-xr-x 1 oracle oinstall 1331 2005 8月 18 runInstaller
-rw-r--r-- 1 oracle oinstall 1049 11月 30日 14:00 same.ora
-rw-r--r-- 1 oracle oinstall 1992 11月 29日 21:26 sqlnet.log
drwxr-xr-x 9 oracle oinstall 2560 2005 8月 18 stage
-rw-r--r-- 1 oracle oinstall 1091 11月 29日 20:11 target.ora
-rw-r--r-- 1 oracle oinstall 0 11月 29日 18:04 tt.txt
drwxr-xr-x 2 oracle oinstall 512 2005 8月 18 upgrade
-rw-r--r-- 1 oracle oinstall 3529 2005 8月 6 welcome.html
-rw-r--r-- 1 oracle oinstall 586240 11月 29日 23:20 x.tar
226 Transfer complete.
ftp> bin
200 Type set to I.
ftp> prompt
Interactive mode off.
ftp> cd cross
250 CWD command successful.
ftp> pwd
257 "/orainstall/cross" is current directory.
ftp> ls
227 Entering Passive Mode (172,16,31,72,244,242)
150 Opening ASCII mode data connection for /bin/ls.
總數 41162
-rw-r----- 1 oracle oinstall 20979712 11月 30日 19:36 data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
-rw-r----- 1 oracle oinstall 69632 11月 30日 19:34 exp_crosstbs.dmp
-rw-r----- 1 oracle oinstall 944 11月 30日 19:34 export.log
226 Transfer complete.
ftp> mget *
local: data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig remote: data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
227 Entering Passive Mode (172,16,31,72,85,166)
150 Opening BINARY mode data connection for data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig (20979712 bytes).
226 Transfer complete.
20979712 bytes received in 2.8 seconds (7.2e+03 Kbytes/s)
local: exp_crosstbs.dmp remote: exp_crosstbs.dmp
227 Entering Passive Mode (172,16,31,72,54,8)
150 Opening BINARY mode data connection for exp_crosstbs.dmp (69632 bytes).
226 Transfer complete.
69632 bytes received in 0.014 seconds (4.9e+03 Kbytes/s)
local: export.log remote: export.log
227 Entering Passive Mode (172,16,31,72,242,137)
150 Opening BINARY mode data connection for export.log (944 bytes).
226 Transfer complete.
944 bytes received in 4.9e-05 seconds (1.9e+04 Kbytes/s)
ftp> bye
221-You have transferred 21050288 bytes in 3 files.
221-Total traffic for this session was 21054054 bytes in 6 transfers.
221-Thank you for using the FTP service on capitek1.
221 Goodbye.
5,在目標庫實施impdp匯入表空間
[oracle@rhel5 sk]$ impdp system/system dumpfile=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/sk/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
Import: Release 10.2.0.1.0 - Production on Monday, 30 November, 2009 11:04:02
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=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/sk/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_13kvm1ig
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 11:04:05
[oracle@rhel5 sk]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 30 11:04:10 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
6,在目標庫對匯入的cross os表空間進行read write操作
sqlplus '/as sysdba'
alter tablespace crosstbs read write;
小結及排錯:
1,源與目標庫的字符集要一樣,不然impdp會報錯
(提示匯入表空間因字符集不匹配失敗--處理:
2,修改目標庫字符集後,再次進行impdp報如下錯誤:
[oracle@rhel5 ~]$ impdp system/system dumpfile=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_12kvlp9b
Import: Release 10.2.0.1.0 - Production on Monday, 30 November, 2009 10:59:08
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=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_12kvlp9b
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39123: Data Pump transportable tablespace job aborted
ORA-19722: datafile /home/oracle/data_D-TARGET_I-3106171664_TS-CROSSTBS_FNO-6_12kvlp9b is an incorrect version
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:59:10
處理:再行從源庫進行expdp及rman 的convert轉換檔案,最後在目標庫impdp就可以了
3,中途相關錯誤,摘錄如下:
[oracle@rhel5 ~]$ impdp system/system dumpfile=exp_crosstbs.dmp directory=sk transport_datafiles=/home/oracle/crosstbs.dbf
Import: Release 10.2.0.1.0 - Production on Monday, 30 November, 2009 10:57:48
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
ORA-39006: internal error
ORA-39213: Metadata processing is not available
[oracle@rhel5 ~]$ oerr ora 39006
39006, 00000, "internal error"
// *Cause: An unexpected error occurred while processing a Data Pump job.
// Subsequent messages supplied by DBMS_DATAPUMP.GET_STATUS
// will further describe the error.
// *Action: Contact Oracle Customer Support.
[oracle@rhel5 ~]$ oerr ora 39213
39213, 00000, "Metadata processing is not available"
// *Cause: The Data Pump could not use the Metadata API. Typically,
// this is caused by the XSL stylesheets not being set up properly.
// *Action: Connect AS SYSDBA and execute dbms_metadata_util.load_stylesheets
// to reload the stylesheets.
[oracle@rhel5 ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 30 10:58:27 2009
Copyright (c) 1982, 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
SQL> exec dbms_metadata_util.load_stylesheets;
PL/SQL procedure successfully completed.
SQL> exit
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-621040/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 表空間遷移
- 遷移表空間
- 利用PLSQL實現表空間的遷移（一）SQL
- 利用PLSQL實現表空間的遷移（二）SQL
- 利用PLSQL實現表空間的遷移（四）SQL
- 利用PLSQL實現表空間的遷移（三）SQL
- 利用PLSQL實現表空間的遷移（五）SQL
- 用dbms_streams_tablespace_adm表空間的遷移(4)
- 用dbms_streams_tablespace_adm表空間的遷移(3)
- 【遷移】表空間transport
- RMAN遷移表空間
- 用dbms_streams_tablespace_adm來表空間的遷移(2)
- 用dbms_streams_tablespace_adm來表空間的遷移(1)
- 利用RMAN遷移表空間碰到的問題（五）
- 利用RMAN遷移表空間碰到的問題（四）
- 利用RMAN遷移表空間碰到的問題（三）
- 利用RMAN遷移表空間碰到的問題（二）
- 利用RMAN遷移表空間碰到的問題（一）
- ORACLE表批量遷移表空間Oracle
- 遷移SYSTEM表空間為本地管理表空間
- expdp/impdp 遷移表空間
- Oracle10g新特性——利用RMAN遷移表空間Oracle
- 利用CONVERT實現跨平臺表空間遷移
- 跨平臺表空間遷移(傳輸表空間)
- 遷移表到新的表空間
- table/index/LOBINDEX遷移表空間Index
- lob欄位表空間遷移
- Oracle10g新特性——利用RMAN遷移表空間（二）Oracle
- Oracle10g新特性——利用RMAN遷移表空間（一）Oracle
- 基於可傳輸表空間的表空間遷移
- Oracle 不同平臺間表空間遷移Oracle
- MySQL 遷移表空間，備份單表MySql
- 表、索引遷移表空間alter table move索引
- 空間遷移
- 利用可傳輸表空間跨平臺遷移 -RMAN CONVERT
- Oracle中表空間、表、索引的遷移Oracle索引
- 表空間遷移辦法補充
- 使用RMAN簡單遷移表空間