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

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.

檢查是否出現警告資訊，如果沒有出現警告資訊則表示可以轉換整個資料庫。

如下則表示不能轉換整個資料庫

5 DBMS_TDB.CHECK_DB('HP-UX (64-bit)',DBMS_TDB.SKIP_READONLY);

The specified target platform name 'HP-UX (64-bit)' is invalid or the target

platform is not transportable.

3 确定兩平台有相同的資料庫版本

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平台如下：

------------------------------------------------------------------------------

TNS for 64-bit Windows: 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';

過程如下：

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

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

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

input datafile file number=00005 name=/oracle/CRM/pos.dbf

converted datafile=/backup/convertdbpos.dbf

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

input datafile file number=00008 name=/oracle/CRM/undotbs03.dbf

converted datafile=/backup/convertdbundotbs03.dbf

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

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

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

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内容如下：

control_files = "e:\crm\control01.ctl"

audit_file_dest = "c:\app\admin\CRM\adump"

db_name = "CRM"

# __oracle_base = "c:\app"

__large_pool_size = 1677721

# log_archive_dest_1 = "LOCATION=f:\archive"

# 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).

SHUTDOWN IMMEDIATE

STARTUP UPGRADE PFILE='/oracle/app/db1/dbs/init_00op1uot_1_0.ora'

@@ ?/rdbms/admin/utlirp.sql

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的内容

STARTUP NOMOUNT PFILE='e:\crm\initCRM.ora'

CREATE CONTROLFILE REUSE SET DATABASE "CRM" RESETLOGS ARCHIVELOG

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

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

ALTER TABLESPACE TEMP ADD TEMPFILE 'e:\crm\temp01.dbf'

STARTUP UPGRADE PFILE='e:\crm\initCRM.ora'

STARTUP PFILE='e:\crm\initCRM.ora'

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

已連接配接到空閑例程。

SQL> @e:\crm\transportscript.sql

ORACLE 例程已經啟動。

Fixed Size 2252768 bytes

Variable Size 973078560 bytes

Redo Buffers 8896512 bytes

控制檔案已建立。

....................省略

SQL> DROP function local_enquote_name;

函數已删除。

SQL>

SQL> Rem =====================================================================

SQL> Rem Run component validation procedure

SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL 過程已成功完成。

SQL> SET serveroutput off

SQL> Rem =======================================================================

====

SQL> Rem END utlrp.sql

SQL> set feedback 6;

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

總結：資料庫遷移時，如果源平台和目标平台位元組序相同的話，可以考慮用此方法遷移整個庫

本文轉自 zhangxuwl 51CTO部落格，原文連結：http://blog.51cto.com/jiujian/1325292，如需轉載請自行聯系原作者