oracle 数据库convert,oracle下convert a database

You can also use RMAN to transport an entire database to a different platformso 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

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

.......................................完.......................................

总结：数据库迁移时，如果源平台和目标平台字节序相同的话，可以考虑用此方法迁移整个库