利用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会报错
  (提示导入表空间因字符集不匹配失败--处理:
    变更oracle的字符集
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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-621040/