convert database异构同字节迁移

最近有项目需要异构迁移，源平台是windows 64bit的，需要迁移到linux 64bit的，数据库平台是oracle 10.2.0.4 64bit的，这里主要想讨论的迁移方式是oracle rman的convert database。 官档中有一个简要的说明： Restrictions on CONVERT DATABASE The primary

最近有项目需要异构迁移，源平台是windows 64bit的，需要迁移到linux 64bit的，数据库平台是oracle 10.2.0.4 64bit的，这里主要想讨论的迁移方式是oracle rman的convert database。

官档中有一个简要的说明：
Restrictions on CONVERT DATABASE

The primary additional restriction on transporting entire databases is that the source and target platform must share the same endian format. For example, while you can transport a database from Microsoft Windows to Linux for x86 (both little-endian), or from HP-UX to AIX (both big-endian), you cannot transport a whole database from Solaris to Linux to x86 using this feature. You can, however, create a new database on a target platform manually, and transport individual tablespaces from the source database using the RMAN CONVERT TABLESPACE or CONVERT DATAFILE commands.

Oracle 10g 已经有了convert database，可以转换迁移同字节的oracle database，但是如果是不同的字节，那么convert database无法完成，此时只能使用tts来迁移个别的tablespace

Convert database有以下需要注意的地方：
Also note that some parts of the database are not transported directly:
? Redo log files and control files from the source database are not transported. New control files and redo log files are created for the target database during the transport process, and an OPEN RESETLOGS is performed once the new database is created.

? the output for the CONVERT DATABASE command, but users must copy the BFILEs themselves and fix their locations on the target platform.

? Datafiles for locally managed temporary tablespaces are not transported. The temporary tablespaces are re-created at the target platform by running the transport script.

? External tables and directories are not transported. RMAN provides a list of affected objects as part of the output of the CONVERT DATABASE command, but users must redefine these on the target platform. See Oracle Database Administrator's Guide for more information on managing external tables and directories.

? Password files are not transported. If a password file was used with the source database, the output of CONVERT DATABASE includes a list of all usernames and their associated privileges. Create a new password file on the target database using this information. See Oracle Database Security Guide for more information on managing password files.
下面小鱼根据官方的资料来实际的模拟一次迁移

1 查看平台字节顺序
SQL> select * from v$transportable_platform order by 3;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- -------------------------
---
6 AIX-Based Systems (64-bit) Big
18 IBM Power Based Linux Big
2 Solaris[tm] OE (64-bit) Big
4 HP-UX IA (64-bit) Big
16 Apple Mac OS Big
1 Solaris[tm] OE (32-bit) Big
9 IBM zSeries Based Linux Big
3 HP-UX (64-bit) Big
17 Solaris Operating System (x86) Little
20 Solaris Operating System (x86-64) Little
12 Microsoft Windows x86 64-bit Little
13 Linux x86 64-bit Little
8 Microsoft Windows IA (64-bit) Little
19 HP IA Open VMS Little
11 Linux IA (64-bit) Little
5 HP Tru64 UNIX Little
10 Linux IA (32-bit) Little
7 Microsoft Windows IA (32-bit) Little
15 HP Open VMS Little

19 rows selected.

2 关闭数据库启动到read only状态
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2066080 bytes
Variable Size 385878368 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14708736 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

3检验当前环境是否能够convert database
SQL> set serveroutput on;
SQL> declare
2 db_ready boolean;
3 begin
4 db_ready:=
5 dbms_tdb.check_db('Linux x86 64-bit',dbms_tdb.skip_readonly);
6 end;
7 /

PL/SQL procedure successfully completed.

如果dbms_tdb.check_db没有任何警告出现，则支持迁移

SQL> DECLARE
2 external BOOLEAN;
3 BEGIN
4 /* value of external is ignored, but with SERVEROUTPUT set to ON
5 * dbms_tdb.check_external displays report of external objects
6 * on console */
7 external := DBMS_TDB.CHECK_EXTERNAL;
8 END;
9 /
The following directories exist in the database:
SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR, SYS.ADMIN_DIR, SYS.WORK_DIR

PL/SQL procedure successfully completed.

检验是否有外部表、directory和bfile的存在，这里只存在系统的directory，后面可以再建。

4 convert database，这个脚本即可在源端执行，也可以在目标端执行。
C:\Users\Administrator>rman target /
RMAN> convert database
2> new database '10gnew'
3> transport script 'd:\transport\transportscript.sql'
4> to platform 'Linux x86 64-bit'
5> db_file_name_convert 'F:\oracle\product\10.2.0\oradata\ora10g' 'd:\transport'
;

Starting convert at 09-MAY-14
using channel ORA_DISK_1

Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.ADMIN_DIR found in the database
Directory SYS.WORK_DIR found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSTEM01.D
BF
converted datafile=D:\TRANSPORT\SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\SYSAUX01.D
BF
converted datafile=D:\TRANSPORT\SYSAUX01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\UNDOTBS01.
DBF
converted datafile=D:\TRANSPORT\UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORA10G\USERS01.DB
F
converted datafile=D:\TRANSPORT\USERS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Run SQL script D:\TRANSPORT\TRANSPORTSCRIPT.SQL on the target platform to create
database
Edit init.ora file F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00P7S8VO_1_0.ORA.
This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target plat
form
To change the internal database identifier, use DBNEWID Utility
Finished backup at 09-MAY-14

这个脚本的大体含义如下：
New database 指定新的数据库名称
Transport script 是生成的数据库脚本，其中包括创建控制文件，指定pfile文件，open resetlogs打开，创建temp datafile，运行脚本编译无效的对象等
To_platform指定目标端的平台
Db_file_name_convert指定转换前和转换后数据文件位置

5 移动convert database生成的datafiles到目标库

6 修改d:\transport\transportscript.sql脚本，根据目标库实际文件的存储位置进行修改

来看下生成的这个脚本文件：
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.

-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT PFILE='F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00P7S8VO_1_0.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "10GNEW" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-10GNEW_ID-4165993973_S-1_T-1_A-847121848_00P7S8VO' SIZE 50M,
GROUP 2 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-10GNEW_ID-4165993973_S-2_T-1_A-847121848_00P7S8VO' SIZE 50M,
GROUP 3 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-10GNEW_ID-4165993973_S-3_T-1_A-847121848_00P7S8VO' SIZE 50M
DATAFILE
'D:\TRANSPORT\SYSTEM01.DBF',
'D:\TRANSPORT\UNDOTBS01.DBF',
'D:\TRANSPORT\SYSAUX01.DBF',
'D:\TRANSPORT\USERS01.DBF'
CHARACTER SET ZHS16GBK
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-10GNEW_I-4165993973_TS-TEMP_FNO-1_00P7S8VO'
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--

set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00P7S8VO_1_0.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='F:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00P7S8VO_1_0.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;

7 编写pfile文件，这个也需要注意根据目录来编写指定的参数，这里就不再列出了

8 pfile文件和datafile文件存储到指定位置后，就可以直接运行上面编辑好的transportscript.sql脚本了

SQL> CONNECT / AS SYSDBA
SQL> @transportscript

刚开始报出了一个创建控制文件时：
ORA-01130: database file version 10.2.0.3.0 incompatible with ORACLE version 10.2.0.0.0

这个是由于参数文件中没有指定compatible，而此时oracle读取的是默认的10.2.0，重新指定compatible=10.2.0.3.0参数后，再次执行transportscript.sql脚本一切正常

这里需要注意的oracle的convert database只能用于迁移同字节的平台，而如果字节顺序不同，此时只能用tts、逻辑复制gg等工具来实现迁移。

原文地址：convert database异构同字节迁移, 感谢原作者分享。