YoucanalsouseRMANtotransportanentiredatabasetoadifferentplatformsolongasthetwoplatformshavethesameendianformat具体过程如下：一convert前提条件1检查两平
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 OR本文来源gao.dai.ma.com搞@代*码(网$A_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” 博客，请务必保留此出处
转载请注明原文链接：oracle下convert a database
