oracle下convert a database

永不落伍

　　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_NAME          ENDIAN_FORMAT
　　-------------------------------------------------- --------------
　　Linux x86 64-bit         Little
　　windows 平台如下：
　　SQL> select platform_name,endian_format from v$transportable_platform where plat
　　form_id=(select platform_id from v$database);
　　PLATFORM_NAME          ENDIAN_FORMAT
　　-------------------------------------------------- --------------
　　Microsoft Windows x86 64-bit      Little
　　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>
　　PL/SQL>　　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>
　　PL/SQL>　　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:>　　Copyright (c) 1982, 2010, Oracle. All rights reserved.

　　Connected to an>　　2 以只读方式打开数据库
　　SQL> startup mount
　　ORACLE instance started.
　　Total System Global Area 1252663296 bytes

　　Fixed>
　　Variable>　　Database Buffers   268435456 bytes
　　Redo Buffers    8921088 bytes
　　Database mounted.

　　SQL>>
　　Database>　　SQL> select open_mode from v$database;
　　OPEN_MODE
　　--------------------
　　READ ONLY
　　3 用rman连接到源数据库作为target
　　QL> host;
　　[oracle@source ~]$ rman target /

　　Recovery Manager:>　　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
　　converteddatafile=/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>　　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:

　　-->　　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'>
　　GROUP 2 '/oracle/app/db1/dbs/arch_D-NEWDB_id-3599153036_S-34_T-1_A-823810820_04op1uot'>
　　GROUP 3 '/oracle/app/db1/dbs/arch_D-NEWDB_id-3599153036_S-35_T-1_A-823810820_05op1uot'>
　　GROUP 4 '/oracle/app/db1/dbs/arch_D-NEWDB_id-3599153036_S-32_T-1_A-823810820_06op1uot'>　　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'

　　>　　-- 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>　　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:

　　-->　　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'>
　　GROUP 2 'e:\crm\redo02.log'>
　　GROUP 3 'e:\crm\redo03.log'>
　　GROUP 4 'e:\crm\redo04.log'>　　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'

　　>　　-- 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>　　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:>　　Copyright (c) 1982, 2010, Oracle. All rights reserved.
　　已连接到空闲例程。
　　SQL> @e:\crm\transportscript.sql
　　ORACLE 例程已经启动。
　　Total System Global Area 1252663296 bytes

　　Fixed>
　　Variable>　　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;
　　.......................................完.......................................
　　总结：数据库迁移时，如果源平台和目标平台字节序相同的话，可以考虑用此方法迁移整个库