you can also use rman to transport an entire database to a different platform so long as the two platforms have the same endian format
具体过程如下：
一 convert前提条件
1 检查两平台是否有相同的字节顺序
linux 平台如下：
sql> select platform_name,endian_format from v$transportable_platform where platform_id=(select platform_id from v$database);
platform_nameendian_format
-------------------------------------------------- --------------
linux x86 64-bitlittle
windows 平台如下：
sql> select platform_name,endian_format from v$transportable_platform where plat
form_id=(select platform_id from v$database);
platform_nameendian_format
-------------------------------------------------- --------------
microsoft windows x86 64-bitlittle
2 检查数据库是否支持整体convert （需要在sqlplus下开启serveroutput)
由于本次convert a database是从‘linux x86 64-bit’ --->‘microsoft windows x86 64-bit’所以有如下：
sql> set serveroutput on
sql> declare
2 db_ready boolean;
3 begin
4 db_ready :=
5 dbms_tdb.check_db('microsoft windows x86 64-bit',dbms_tdb.skip_none);
6 end;
7 /
注意skip_none (or 0), 表示检测所有表空间
pl/sql procedure successfully completed.
检查是否出现警告信息，，如果没有出现警告信息则表示可以转换整个数据库。
如下则表示不能转换整个数据库
sql> declare
2 db_ready boolean;
3 begin
4 db_ready :=
5 dbms_tdb.check_db('hp-ux (64-bit)',dbms_tdb.skip_readonly);
6 end;
7 /
the specified target platform name 'hp-ux (64-bit)' is invalid or the target
platform is not transportable.
pl/sql procedure successfully completed.
3 确定两平台有相同的数据库版本
linux 平台如下：
sql> select * from v$version;
banner
--------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.2.0 - 64bit production
pl/sql release 11.2.0.2.0 - production
core 11.2.0.2.0 production
tns for linux: version 11.2.0.2.0 - production
nlsrtl version 11.2.0.2.0 - production
windows平台如下：
sql> select * from v$version;
banner
------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.2.0 - 64bit production
pl/sql release 11.2.0.2.0 - production
core 11.2.0.2.0 production
tns for 64-bit windows: version 11.2.0.2.0 - production
nlsrtl version 11.2.0.2.0 - production
二 操作步骤
1 以sysdba连接数据库
[oracle@source ~]$ sqlplus / as sysdba
sql*plus: release 11.2.0.2.0 production on fri nov 15 19:28:45 2013
copyright (c) 1982, 2010, oracle. all rights reserved.
connected to an idle instance.
2 以只读方式打开数据库
sql> startup mount
oracle instance started.
total system global area 1252663296 bytes
fixed size2226072 bytes
variable size 973080680 bytes
database buffers 268435456 bytes
redo buffers 8921088 bytes
database mounted.
sql> alter database open read only;
database altered.
sql> select open_mode from v$database;
open_mode
--------------------
read only
3 用rman连接到源数据库作为target
ql> host;
[oracle@source ~]$ rman target /
recovery manager: release 11.2.0.2.0 - production on fri nov 15 19:46:16 2013
copyright (c) 1982, 2009, oracle and/or its affiliates. all rights reserved.
connected to target database: crm (dbid=3599153036)
4 转换数据库
rman> convert database new database 'newdb'
transport script '/backup/convertdb/transportscript.sql'
to platform 'microsoft windows x86 64-bit'
db_file_name_convert '/oracle/crm/' '/backup/convertdb';
过程如下：
rman> convert database new database 'newdb'
2> transport script '/backup/convertdb/transportscript.sql'
3> to platform 'microsoft windows x86 64-bit'
4> db_file_name_convert '/oracle/crm/' '/backup/convertdb';
starting conversion at source at 15-nov-13
using target database control file instead of recovery catalog
allocated channel: ora_disk_1
channel ora_disk_1: sid=131 device type=disk
directory sys.oracle_ocm_config_dir found in the database
directory sys.data_pump_dir found in the database
directory sys.xmldir found in the database
directory sys.dump found in the database
directory sys.tspitr_dirobj_dpdir found in the database
user sys with sysdba and sysoper privilege found in password file
channel ora_disk_1: starting datafile conversion
input datafile file number=00004 name=/oracle/crm/users01.dbf
converted datafile=/backup/convertdbusers01.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:03:07
channel ora_disk_1: starting datafile conversion
input datafile file number=00001 name=/oracle/crm/system01.dbf
converted datafile=/backup/convertdbsystem01.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:01:05
channel ora_disk_1: starting datafile conversion
input datafile file number=00002 name=/oracle/crm/sysaux01.dbf
converted datafile=/backup/convertdbsysaux01.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:00:55
channel ora_disk_1: starting datafile conversion
input datafile file number=00005 name=/oracle/crm/pos.dbf
converted datafile=/backup/convertdbpos.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:00:55
channel ora_disk_1: starting datafile conversion
input datafile file number=00006 name=/oracle/crm/erp.dbf
converted datafile=/backup/convertdberp.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:00:15
channel ora_disk_1: starting datafile conversion
input datafile file number=00008 name=/oracle/crm/undotbs03.dbf
converted datafile=/backup/convertdbundotbs03.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:00:15
channel ora_disk_1: starting datafile conversion
input datafile file number=00009 name=/oracle/crm/jxc.dbf
converted datafile=/backup/convertdbjxc.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:01:15
channel ora_disk_1: starting datafile conversion
input datafile file number=00010 name=/oracle/crm/crm.dbf
converted datafile=/backup/convertdbcrm.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:00:07
channel ora_disk_1: starting datafile conversion
input datafile file number=00003 name=/oracle/crm/zx.dbf
converted datafile=/backup/convertdbzx.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:00:03
channel ora_disk_1: starting datafile conversion
input datafile file number=00007 name=/oracle/crm/user01.dbf
converted datafile=/backup/convertdbuser01.dbf
channel ora_disk_1: datafile conversion complete, elapsed time: 00:00:01
edit init.ora file /oracle/app/db1/dbs/init_00op1uot_1_0.ora. this pfile will be used to create the database on the target platform
run sql script /backup/convertdb/transportscript.sql on the target platform to create database
to recompile all pl/sql modules, run utlirp.sql and utlrp.sql on the target platform
to change the internal database identifier, use dbnewid utility
finished conversion at source at 15-nov-13
注意：红色部分为要拷贝到目标平台的文件
6 拷贝转换后的数据文件,参数文件，transportscript.sql 到目标平台
7 更改参数文件和transportscript.sql的内容
更改前参数文件内容
[oracle@source ~]$ vi /oracle/app/db1/dbs/init_00op1uot_1_0.ora
# please change the values of the following parameters:
control_files = /oracle/app/db1/dbs/cf_d-newdb_id-3599153036_01op1uot
audit_file_dest = /oracle/app/db1/dbs/adump
db_name= newdb
# please review the values of the following parameters:
# __oracle_base = /oracle/app
__shared_pool_size = 402653184
__large_pool_size = 16777216
__java_pool_size = 16777216
__streams_pool_size = 33554432
__sga_target = 754974720
__db_cache_size = 268435456
__shared_io_pool_size = 0
remote_login_passwordfile= exclusive
db_domain =
dispatchers = (protocol=tcp) (service=crmxdb)
__pga_aggregate_target = 503316480
# the values of the following parameters are from source database:
processes = 150
memory_target = 1258291200
db_block_size = 8192
db_cache_size = 218103808
compatible = 11.2.0.0.0
# log_archive_dest_1 = location=/oracle/archive
log_archive_dest_2 =
log_archive_format = %t_%s_%r.dbf
undo_tablespace = undotbs3
undo_retention = 1200
audit_trail = os
open_cursors = 300
# diagnostic_dest = /oracle/app
更改后参数文件initcrm.ora内容如下：
# please change the values of the following parameters:
control_files = e:\crm\control01.ctl
audit_file_dest = c:\app\admin\crm\adump
db_name= crm
# please review the values of the following parameters:
# __oracle_base = c:\app
__shared_pool_size = 402653184
__large_pool_size = 1677721
__java_pool_size = 16777216
__streams_pool_size = 33554432
__sga_target = 754974720
__db_cache_size = 268435456
__shared_io_pool_size = 0
remote_login_passwordfile= exclusive
db_domain =
dispatchers = (protocol=tcp) (service=crmxdb)
__pga_aggregate_target = 503316480
# the values of the following parameters are from source database:
processes = 150
memory_target = 1258291200
db_block_size = 8192
db_cache_size = 218103808
compatible = 11.2.0.0.0
# log_archive_dest_1 = location=f:\archive
log_archive_dest_2 =
log_archive_format = %t_%s_%r.dbf
undo_tablespace = undotbs3
undo_retention = 1200
audit_trail = os
open_cursors = 300
# diagnostic_dest = c:\app
更改前transportscript.sql的内容
[oracle@source ~]$ cat /backup/convertdb/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='/oracle/app/db1/dbs/init_00op1uot_1_0.ora'
create controlfile reuse set database newdb resetlogs archivelog
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 8
maxloghistory 292
logfile
group 1 '/oracle/app/db1/dbs/arch_d-newdb_id-3599153036_s-33_t-1_a-823810820_03op1uot' size 200m blocksize 512,
group 2 '/oracle/app/db1/dbs/arch_d-newdb_id-3599153036_s-34_t-1_a-823810820_04op1uot' size 200m blocksize 512,
group 3 '/oracle/app/db1/dbs/arch_d-newdb_id-3599153036_s-35_t-1_a-823810820_05op1uot' size 200m blocksize 512,
group 4 '/oracle/app/db1/dbs/arch_d-newdb_id-3599153036_s-32_t-1_a-823810820_06op1uot' size 200m blocksize 512
datafile
'/backup/convertdbsystem01.dbf',
'/backup/convertdbsysaux01.dbf',
'/backup/convertdbzx.dbf',
'/backup/convertdbusers01.dbf',
'/backup/convertdbpos.dbf',
'/backup/convertdberp.dbf',
'/backup/convertdbuser01.dbf',
'/backup/convertdbundotbs03.dbf',
'/backup/convertdbjxc.dbf',
'/backup/convertdbcrm.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 '/oracle/app/db1/dbs/data_d-newdb_i-3599153036_ts-temp_fno-1_07op1uot'
size 20971520 autoextend off;
-- 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='/oracle/app/db1/dbs/init_00op1uot_1_0.ora'
@@ ?/rdbms/admin/utlirp.sql
shutdown immediate
startup pfile='/oracle/app/db1/dbs/init_00op1uot_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;
更改后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='e:\crm\initcrm.ora'
create controlfile reuse set database crm resetlogs archivelog
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 8
maxloghistory 292
logfile
group 1 'e:\crm\redo01.log' size 200m blocksize 512,
group 2 'e:\crm\redo02.log' size 200m blocksize 512,
group 3 'e:\crm\redo03.log' size 200m blocksize 512,
group 4 'e:\crm\redo04.log' size 200m blocksize 512
datafile
'e:\crm\system01.dbf',
'e:\crm\sysaux01.dbf',
'e:\crm\zx.dbf',
'e:\crm\users01.dbf',
'e:\crm\pos.dbf',
'e:\crm\erp.dbf',
'e:\crm\user01.dbf',
'e:\crm\undotbs03.dbf',
'e:\crm\jxc.dbf',
'e:\crm\crm.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 'e:\crm\temp01.dbf'
size 20971520 autoextend off;
-- 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='e:\crm\initcrm.ora'
@@ ?/rdbms/admin/utlirp.sql
shutdown immediate
startup pfile='e:\crm\initcrm.ora'
-- the following step will recompile all pl/sql modules.
-- it may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
8 在目标平台上执行transportscript.sql
创建一个oracle服务
c:\>oradim -new -sid crm -startmode manual
实例已创建。
执行transportscript.sql
c:\users\administrator>set oracle_sid=crm
c:\users\administrator>sqlplus / as sysdba
sql*plus: release 11.2.0.2.0 production on 星期五 11月 15 14:38:03 2013
copyright (c) 1982, 2010, oracle. all rights reserved.
已连接到空闲例程。
sql> @e:\crm\transportscript.sql
oracle 例程已经启动。
total system global area 1252663296 bytes
fixed size2252768 bytes
variable size 973078560 bytes
database buffers 268435456 bytes
redo buffers 8896512 bytes
控制文件已创建。
....................省略
....................省略
....................省略
....................省略
sql> drop function local_enquote_name;
函数已删除。
sql>
sql> rem =====================================================================
sql> rem run component validation procedure
sql> rem =====================================================================
sql>
sql> execute dbms_registry_sys.validate_components;
pl/sql 过程已成功完成。
sql> set serveroutput off
sql>
sql>
sql> rem =======================================================================
====
sql> rem end utlrp.sql
sql> rem =======================================================================
====
sql> set feedback 6;
.......................................完.......................................
总结：数据库迁移时，如果源平台和目标平台字节序相同的话，可以考虑用此方法迁移整个库
本文出自 “myblog” 博客，请务必保留此出处