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

Seede AI

AI 驱动的设计工具

586 查看详情

# __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:crmcontrol01.ctl”

audit_file_dest = “c:appadminCRMadump”

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:crminitCRM.ora’

CREATE CONTROLFILE REUSE SET DATABASE “CRM” RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ‘e:crmredo01.log’ SIZE 200M BLOCKSIZE 512,

GROUP 2 ‘e:crmredo02.log’ SIZE 200M BLOCKSIZE 512,

GROUP 3 ‘e:crmredo03.log’ SIZE 200M BLOCKSIZE 512,

GROUP 4 ‘e:crmredo04.log’ SIZE 200M BLOCKSIZE 512

DATAFILE

‘e:crmsystem01.dbf’,

‘e:crmsysaux01.dbf’,

‘e:crmzx.dbf’,

‘e:crmusers01.dbf’,

‘e:crmpos.dbf’,

‘e:crmerp.dbf’,

‘e:crmuser01.dbf’,

‘e:crmundotbs03.dbf’,

‘e:crmjxc.dbf’,

‘e:crmcrm.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:crmtemp01.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:crminitCRM.ora’

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE=’e:crminitCRM.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:UsersAdministrator>set oracle_sid=CRM

C:UsersAdministrator>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:crmtransportscript.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” 博客，请务必保留此出处