ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Cross-Platform Transportable database:RMAN CONVERT DATABASE

Cross-Platform Transportable database:RMAN CONVERT DATABASE

原创 Linux操作系统 作者:g644516804 时间:2012-01-11 16:01:09 0 删除 编辑

利用rman COVNERT DATABASE实现跨平台迁移数据库,条件必须是相同的尾数格式!

RMAN COVERT DATABASE 命令是用于自动的将整个库从一个平台(原始平台)转移到另一个平台(目标平台)。要求是原始平台和目标平台的尾数格式是一致的,RMAN会自动的在目标平台上创建一个同原始数据库一模一样的目标数据库。

CONVER DATABASE的特性:

1.       数据文件是永久性的tablespace,不能转移temp tablespace

2.       如果pfile正在使用,则会传输pfile;如果spfile在使用,则pfile会根据spfile产生并传输,新的spfile也会传输到目标数据库中

3.       控制文件和日志文件不会被传输,在目标数据库中open restlogs的过程会自动创建

4.       密码文件将不会被传输,需要自己手动创建

5.       数据库处于open read only状态

6.       利用dbms_tdb包检测数据库能否被传输以及列出无法传输的对象

  linux 迁移至window为例

SQL>shutdown immediate;

SQL>startup mount;

SQL>ALTER database open read only;

使用dbms_tdb.check_db检测数据库能否被传输

SQL>set serveroutput on;

SQL>declear

 Db_ready Boolean;

Begin

   Db_ready:=dbms_tdb.check_db(‘Microsoft Windows IA (32-bit)’,dbms_tdb.skip_readonly);

End;

注意:数据库必须处于read only状态下

如果显示PL/SQL procedure successfully completed.并且没用其他信息显示,则表明数据库可以被传输

使用dbms_tdb.check_external定义外部对象

SQL>set serveroutput on

SQL>declear

     External Boolean;

     Begin;

   begin

    /* value of external is ignored, but with SERVEROUTPUT set to ON

     * dbms_tdb.check_external displays report of external objects

     * on console */

    external := dbms_tdb.check_external;

  end;

如果没有外部对象,则该存储过程执行结束之后没有信息输出;如果有外部表,则会输出如下信息:

The following directories exist in the database:

SYS.ORACLE_OCM_CONFIG_DIR, SYS.DATA_PUMP_DIR, SYS.XMLDIR

PL/SQL procedure successfully completed.

7.       利用rman convert database 在源数据库进行转换(也可以在目标数据库中转换)

RMAN>CONVERT DATABASE NEW DATABASE 'newdb'  --产生新的db name
  transport script. '/tmp/convertdb/transportscript.sql'  --产生创建数据库的脚本位置
 to platform. 'Microsoft Windows IA (32-bit)'      --转移的平台
db_file_name_convert '/disk1/oracle/dbs' '/tmp/convertdb';  --数据文件的转换
 
如:
RMAN>  convert database new database 'oratest'
2> transport script. '/tmp/convert/transportscript'   
3>  to platform. 'Microsoft Windows IA (32-bit)'
4>  db_file_name_convert '/oradata/ora12' '/tmp/convert';
 
Starting conversion at source at 07-JAN-12
using channel ORA_DISK_1
 
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
 
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/oradata/ora12/system01.dbf
converted datafile=/tmp/convert/system01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:34
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/oradata/ora12/sysaux01.dbf
converted datafile=/tmp/convert/sysaux01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=/oradata/ora12/lerry01.dbf
converted datafile=/tmp/convert/lerry01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/oradata/ora12/undotbs01.dbf
converted datafile=/tmp/convert/undotbs01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00007 name=/oradata/ora12/rman_tb01.dbf
converted datafile=/tmp/convert/rman_tb01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/oradata/ora12/test01.dbf
converted datafile=/tmp/convert/test01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/oradata/ora12/users01.dbf
converted datafile=/tmp/convert/users01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file /ora101/11.2/dbs/init_00n05kuh_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script. /tmp/convert/transportscript. 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 07-JAN-12
 

8.       在目标数据库中执行@transportscript.sql

脚本具体信息如下:

STARTUP NOMOUNT PFILE='/ora101/11.2/dbs/init_00n05kuh_1_0.ora'

CREATE CONTROLFILE REUSE SET DATABASE "ORATEST" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/ora101/11.2/dbs/arch_D-ORATEST_id-1383533651_S-10_T-1_A-771757905_03n05kuh'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/ora101/11.2/dbs/arch_D-ORATEST_id-1383533651_S-11_T-1_A-771757905_04n05kuh'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/ora101/11.2/dbs/arch_D-ORATEST_id-1383533651_S-9_T-1_A-771757905_05n05kuh'  SIZE 50M BLOCKSIZE 512

DATAFILE

  '/tmp/convert/system01.dbf',

  '/tmp/convert/sysaux01.dbf',

  '/tmp/convert/undotbs01.dbf',

  '/tmp/convert/users01.dbf',

  '/tmp/convert/test01.dbf',

  '/tmp/convert/lerry01.dbf',

  '/tmp/convert/rman_tb01.dbf'

CHARACTER SET UTF8

;

-- 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 TEMP2 ADD TEMPFILE '/ora101/11.2/dbs/data_D-ORATEST_I-1383533651_TS-TEMP2_FNO-1_06n05kuh'

     SIZE 209715200  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='/ora101/11.2/dbs/init_00n05kuh_1_0.ora'

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE='/ora101/11.2/dbs/init_00n05kuh_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;

 

-- Database can now be opened zeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

 

在目标数据库中新创建一个ORACLE服务,

然后根据目标db的需要更改脚本和pfile(目录更改),并将装换之后的数据文件及脚本、pfile copy到目标数据库的相应目录中,运行建库脚本即完成跨平台数据库的转换

 

C:\Documents and  Settings\lerry_wu\>oradim –new –sid newdb –startmode –m

C:\Documents and settings \lerry_wu>set oracle_sid=newdb

SQL>@transportscript.sql

    执行结束即完成

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24849178/viewspace-714752/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-04

  • 博文量
    104
  • 访问量
    231589