本文共 13930 字，大约阅读时间需要 46 分钟。
源端oracle9.2.0.1_x86+windows2003
目标端oracle11.2.0.4_x64+linux7.7将源端9i的users表空间增量传输到11g的数据库中。
和9i同平台字节序的可直接在目标端恢复，无需转换，不同字节序的不支持，报错（ORA-27048: skgfifi: file header information is invalid）。10g以上支持字节序转换，支持异构平台恢复。32位可直接转换到目标64位。字节序相同（如win<-->linux），备份可以压缩，目标端可识别，字节序不同的话(如win,linux<-->aix)，源端备份不能用压缩，目标端不支持压缩后的转换识别。
传输表空间相关1.convert tablespaceconvert tablespace源端库执行:convert tablespace 'TPS_DATA' to platform 'AIX-Based Systems (64-bit)' db_file_name_convert '/u01/app/oracle/oradata/slnngk','/tmp';convert tablespace TPS_DATA TO PLATFORM 'Microsoft Windows IA (32-bit)' FORMAT '/tmp/%U';2.convert datafileconvert datafile必须在目标库执行配合上rman transport在源库生成导入元数据和数据文件,convert datafile在目标库执行,这样可以免去源库表空间read only[oracle@ct6605 ct66]$ rman target /转换为'Linux x86 64-bit'平台使用的数据文件RMAN> convert datafile'/home/oracle/test01.dbf'to platform 'Linux x86 64-bit'from platform 'AIX-Based Systems (64-bit)'db_file_name_convert '/home/oracle','/home/oracle/test';然后再目标端使用impdp的transport_datafiles进行导入,如下impdp TRANSPORT_DATAFILES='/u01/app/oracle/oradata/sun/ttbs1.dbf' directory=dpdir dumpfile=ttbs.dmp remap_tablespace=ttbs1:ttbs2 remap_schema=test1:test2 logfile=ttbs1.log
转换例子参考：
源平台SQL> col PLATFORM_NAME for a40SQL> select PLATFORM_ID,PLATFORM_NAME from v$database;PLATFORM_ID PLATFORM_NAME----------- ----------------------------------------7 Microsoft Windows IA (32-bit) LittleSQL> select * from V$TRANSPORTABLE_PLATFORM;PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- ---------------------------------------- --------------1 Solaris[tm] OE (32-bit) Big2 Solaris[tm] OE (64-bit) Big7 Microsoft Windows IA (32-bit) Little10 Linux IA (32-bit) Little6 AIX-Based Systems (64-bit) Big3 HP-UX (64-bit) Big5 HP Tru64 UNIX Little4 HP-UX IA (64-bit) Big11 Linux IA (64-bit) Little15 HP Open VMS Little8 Microsoft Windows IA (64-bit) LittlePLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- ---------------------------------------- --------------9 IBM zSeries Based Linux Big13 Linux x86 64-bit Little16 Apple Mac OS Big12 Microsoft Windows x86 64-bit Little17 Solaris Operating System (x86) Little18 IBM Power Based Linux Big20 Solaris Operating System (x86-64) Little19 HP IA Open VMS Little目标平台转换恢复oracle@bimsa:/aix6>sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 24 10:53:37 2020Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>conn /as sysdbaset serveroutput on;DECLAREhandle varchar2(512);comment varchar2(80);media varchar2(80);concur boolean;recid number;stamp number; pltfrmfr number;devtype VARCHAR2(512); BEGINBEGINsys.dbms_backup_restore.restoreCancel(TRUE);devtype := sys.dbms_backup_restore.deviceAllocate;sys.dbms_backup_restore.backupBackupPiece(bpname => '/aix6/TEST0_01VIQFOT_1_1',fname => '/aix6/TEST0_01NOCOMPRESS',handle => handle,media=> media,comment=> comment, concur=> concur,recid=> recid,stamp => stamp, check_logical => FALSE,copyno=> 1, deffmt=> 0, copy_recid=> 0,copy_stamp => 0,npieces=> 1,dest=> 0,pltfrmfr=> 7);END;END;/PL/SQL procedure successfully completed.SQL> !ls -l /aix6/TEST0*-rw-r----- 1 oracle oinstall 393216 Dec 24 10:54 /aix6/TEST0_01NOCOMPRESS-rw------- 1 oracle oinstall 393216 Dec 23 16:58 /aix6/TEST0_01VIQFOT_1_1-rw------- 1 oracle oinstall 393216 Dec 23 16:59 /aix6/TEST0_02VIQFRG_1_1SQL> conn /as sysdbaset serveroutput on;DECLAREdevtype varchar2(256);done Boolean;BEGINDevtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');sys.dbms_backup_restore.restoreSetDatafile;sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'/aix6/test01-nocompress.dbf');sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/aix6/TEST0_01NOCOMPRESS', params=>null);sys.dbms_backup_restore.deviceDeallocate;END;/PL/SQL procedure successfully completed.SQL>!ls -l /aix6/test*-rw-r----- 1 oracle oinstall 5251072 Dec 24 10:55 /aix6/test01-nocompress.dbf
--源端压缩0级表空间9i不支持 run { allocate channel t1 type disk; backup as compressed backupset incremental level=0 tablespace USERS format 'C:\oracle\xtts\jyc0_%U'; release channel t1; }
--源端1级增量备份多次操作，最后readonly后做一次。
run { allocate channel t1 type disk; backup incremental level=1 tablespace USERS format 'C:\oracle\xtts\jyc1_%U'; release channel t1; }提取脚本参考：
select 'sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>' || file_id || ',toname=>' ||chr(39)|| file_name ||chr(39) || ');', 'sys.dbms_backup_restore.applySetDatafile(dfnumber=>' || file_id || ',toname=>' ||chr(39)|| file_name ||chr(39) || ');' from dba_data_files where tablespace_name in('USERS'); 备份对应SQL: select b.recid,a.tag,c.file#,b.incremental_level,d.name,a.handle from v$backup_piece a,v$backup_set b,v$backup_datafile c,v$datafile d where a.set_stamp=b.set_stamp and a.set_count=b.set_count and c.set_count=b.set_count and c.set_stamp=b.set_stamp and c.file#=d.file# order by b.recid desc,a.tag,c.file#;目标端恢复0级备份：
set serveroutput on; DECLARE devtype varchar2(256); done Boolean; BEGIN Devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1'); sys.dbms_backup_restore.restoreSetDatafile; sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>9,toname=>'/home/db/oracle/oradata/orcl/users01.dbf'); sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'/backup/JYC0_01VINDGD_1_1', params=>null); sys.dbms_backup_restore.deviceDeallocate;END; /目标端恢复1级增量备份：
conn /as sysdba set serveroutput on; DECLARE outhandle varchar2(512) ; outtag varchar2(30) ; done boolean ; failover boolean ; devtype VARCHAR2(512); BEGIN DBMS_OUTPUT.put_line('Entering RollForward'); -- Now the rolling forward. devtype := sys.dbms_backup_restore.deviceAllocate; sys.dbms_backup_restore.applySetDatafile(check_logical => FALSE, cleanup => FALSE) ; DBMS_OUTPUT.put_line('After applySetDataFile'); sys.dbms_backup_restore.applyDatafileTo(dfnumber=>9,toname=>'/home/db/oracle/oradata/orcl/users01.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0); DBMS_OUTPUT.put_line('Done: applyDataFileTo'); -- Restore Set Piece sys.dbms_backup_restore.restoreSetPiece(handle => '/backup/JYC1_02VINDIB_1_1',tag => null, fromdisk => true, recid => 0, stamp => 0) ; DBMS_OUTPUT.put_line('Done: RestoreSetPiece'); -- Restore Backup Piece sys.dbms_backup_restore.restoreBackupPiece(done => done, params => null, outhandle => outhandle,outtag => outtag, failover => failover); DBMS_OUTPUT.put_line('Done: RestoreBackupPiece'); sys.dbms_backup_restore.restoreCancel(TRUE); sys.dbms_backup_restore.deviceDeallocate; END; /源库只读users表空间后做1级增量备份：
conn /as sysdba set serveroutput on; DECLARE outhandle varchar2(512) ; outtag varchar2(30) ; done boolean ; failover boolean ; devtype VARCHAR2(512); BEGIN DBMS_OUTPUT.put_line('Entering RollForward'); -- Now the rolling forward. devtype := sys.dbms_backup_restore.deviceAllocate; sys.dbms_backup_restore.applySetDatafile(check_logical => FALSE, cleanup => FALSE) ; DBMS_OUTPUT.put_line('After applySetDataFile'); sys.dbms_backup_restore.applyDatafileTo(dfnumber=>9,toname=>'/home/db/oracle/oradata/orcl/users01.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0); DBMS_OUTPUT.put_line('Done: applyDataFileTo'); -- Restore Set Piece sys.dbms_backup_restore.restoreSetPiece(handle => '/backup/JYC1_03VINEEI_1_1',tag => null, fromdisk => true, recid => 0, stamp => 0) ; DBMS_OUTPUT.put_line('Done: RestoreSetPiece'); -- Restore Backup Piece sys.dbms_backup_restore.restoreBackupPiece(done => done, params => null, outhandle => outhandle,outtag => outtag, failover => failover); DBMS_OUTPUT.put_line('Done: RestoreBackupPiece'); sys.dbms_backup_restore.restoreCancel(TRUE); sys.dbms_backup_restore.deviceDeallocate; END; /源库只读users表空间后导出元数据：
C:\Documents and Settings\Administrator>
exp "'sys/oracle as sysdba'" transport_tablespace=y tablespaces=('USERS') STATISTICS=none file=C:\oracle\xtts\users.dmp log=C:\oracle\xtts\users-exp.logExport: Release 9.2.0.1.0 - Production on 星期二 12月 22 13:23:29 2020
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 注: 将不会导出表数据（行） 关于导出可传输的表空间元数据... 用于表空间 USERS... . 正在导出群集定义 . 正在导出表定义 . . 正在导出表 T . . 正在导出表 T1 . 正在导出引用完整性约束条件 . 正在导出触发器 . 结束导出可传输的表空间元数据 在没有警告的情况下成功终止导出。 源库只读users表空间后导出用户其它数据： --用sys用户可以导出public对象。 exp "'sys/oracle as sysdba'" file=C:\oracle\xtts\jyc-meta.dmp log=C:\oracle\xtts\jyc-meta-log.txt owner=jyc rows=n statistics=noneC:\Documents and Settings\Administrator>exp "'sys/oracle as sysdba'" file=C:\oracle\xtts\jyc-meta.dmp log=C:\oracle\xtts\jyc-meta-log.txt owner=jyc rows=n statistics=none
Export: Release 9.2.0.1.0 - Production on 星期二 12月 22 13:26:17 2020
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production 已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集 注: 将不会导出表数据（行）即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作 . 正在导出用户 JYC 的外部函数库名称 . 导出 PUBLIC 类型同义词 . 导出私有类型同义词 . 正在导出用户 JYC 的对象类型定义 即将导出 JYC 的对象 ... . 正在导出数据库链接 . 正在导出序号 . 正在导出群集定义 . 即将导出 JYC 的表通过常规路径 ... . . 正在导出表 T . . 正在导出表 T1 . 正在导出同义词 . 正在导出视图 . 正在导出存储的过程 . 正在导出运算符 . 正在导出引用完整性约束条件 . 正在导出触发器 . 正在导出索引类型 . 正在导出位图, 功能性索引和可扩展索引 . 正在导出后期表活动 . 正在导出实体化视图 . 正在导出快照日志 . 正在导出作业队列 . 正在导出刷新组和子组 . 正在导出维 . 正在导出 post-schema 过程对象和操作 . 正在导出统计 在没有警告的情况下成功终止导出。--目标端最后一次1级增量恢复
conn /as sysdba set serveroutput on; DECLARE outhandle varchar2(512) ; outtag varchar2(30) ; done boolean ; failover boolean ; devtype VARCHAR2(512); BEGIN DBMS_OUTPUT.put_line('Entering RollForward'); -- Now the rolling forward. devtype := sys.dbms_backup_restore.deviceAllocate; sys.dbms_backup_restore.applySetDatafile(check_logical => FALSE, cleanup => FALSE) ; DBMS_OUTPUT.put_line('After applySetDataFile'); sys.dbms_backup_restore.applyDatafileTo(dfnumber=>9,toname=>'/home/db/oracle/oradata/orcl/users01.dbf',fuzziness_hint => 0, max_corrupt => 0, islevel0 => 0,recid => 0, stamp => 0); DBMS_OUTPUT.put_line('Done: applyDataFileTo'); -- Restore Set Piece sys.dbms_backup_restore.restoreSetPiece(handle => '/backup/JYC1_04VINEIR_1_1',tag => null, fromdisk => true, recid => 0, stamp => 0) ; DBMS_OUTPUT.put_line('Done: RestoreSetPiece'); -- Restore Backup Piece sys.dbms_backup_restore.restoreBackupPiece(done => done, params => null, outhandle => outhandle,outtag => outtag, failover => failover); DBMS_OUTPUT.put_line('Done: RestoreBackupPiece'); sys.dbms_backup_restore.restoreCancel(TRUE); sys.dbms_backup_restore.deviceDeallocate; END; / 目标端导入表空间元数据 imp "'sys/oracle as sysdba'" transport_tablespace=y TABLESPACES='USERS' file=users.dmp log=users-imp.log datafiles='/home/db/oracle/oradata/orcl/users01.dbf'恢复默认表空间
alter user jyc default tablespace users; select property_value from database_properties where property_name='DEFAULT_PERMANENT_TABLESPACE'; alter database default tablespace USERS; alter tablespace USERS read write; select TABLESPACE_NAME,STATUS from dba_tablespaces;TABLESPACE_NAME STATUS
------------------------------ --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE目标端导入用户其它对象
imp "'sys/oracle as sysdba'" file=jyc-meta.dmp log=jyc-meta-imp.txt fromuser=jyc touser=jyc--sys导入会包含public对象，fromuser将不导入public。
[oracle@saperp backup]$ imp "'sys/oracle as sysdba'" file=jyc-meta.dmp log=jyc-meta-imp.txt full=y
Import: Release 11.2.0.4.0 - Production on Tue Dec 22 13:38:46 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set . importing SYS's objects into SYS . importing JYC's objects into JYC IMP-00015: following statement failed because the object already exists: "CREATE TABLE "T" ("USERNAME" VARCHAR2(30) NOT NULL ENABLE, "USER_ID" NUMBER" " NOT NULL ENABLE, "PASSWORD" VARCHAR2(30), "ACCOUNT_STATUS" VARCHAR2(32) NO" "T NULL ENABLE, "LOCK_DATE" DATE, "EXPIRY_DATE" DATE, "DEFAULT_TABLESPACE" V" "ARCHAR2(30) NOT NULL ENABLE, "TEMPORARY_TABLESPACE" VARCHAR2(30) NOT NULL E" "NABLE, "CREATED" DATE NOT NULL ENABLE, "PROFILE" VARCHAR2(30) NOT NULL ENAB" "LE, "INITIAL_RSRC_CONSUMER_GROUP" VARCHAR2(30), "EXTERNAL_NAME" VARCHAR2(40" "00)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 F" "REELISTS 1 FREELIST GROUPS 1) LOGGING NOCOMPRESS" IMP-00015: following statement failed because the object already exists: "CREATE TABLE "T1" ("USERNAME" VARCHAR2(30) NOT NULL ENABLE, "USER_ID" NUMBE" "R NOT NULL ENABLE, "PASSWORD" VARCHAR2(30), "ACCOUNT_STATUS" VARCHAR2(32) N" "OT NULL ENABLE, "LOCK_DATE" DATE, "EXPIRY_DATE" DATE, "DEFAULT_TABLESPACE" " "VARCHAR2(30) NOT NULL ENABLE, "TEMPORARY_TABLESPACE" VARCHAR2(30) NOT NULL " "ENABLE, "CREATED" DATE NOT NULL ENABLE, "PROFILE" VARCHAR2(30) NOT NULL ENA" "BLE, "INITIAL_RSRC_CONSUMER_GROUP" VARCHAR2(30), "EXTERNAL_NAME" VARCHAR2(4" "000)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 " "FREELISTS 1 FREELIST GROUPS 1) LOGGING NOCOMPRESS" IMP-00015: following statement failed because the object already exists: "CREATE FORCE VIEW "JYC"."V_T" ("USERNAME") AS " "select username from t" Import terminated successfully with warnings.
相关参考：
转载地址：http://ycsof.baihongyu.com/