oracle下convert a database
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_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 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 Size 2226072 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 Size 2252768 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 服务启动，关闭脚本(windows系统下)
-
浅谈Windows下 PHP4.0与oracle 8的连接设置
-
Windows下Oracle的下载与安装
-
在代码生成工具Database2Sharp中使用ODP.NET（Oracle.ManagedDataAccess.dll）访问Oracle数据库，实现免安装Oracle客户端，兼容32位64位Oracle驱动
-
在Linux下安装Oracle
-
Windows下ORACLE 10g完全卸载的方法分析
-
oracle数据库下统计专营店的男女数量的语句
-
Oracle VM VirtualBox 在linux系统下安装增强插件实现访问主机的共享文档方法
-
Linux下重启oracle服务及监听器和实例详解
-
Linux下安装Oracle（CentOS-Oracle 12c）的方法