利用RMAN Convert database特性进行跨平台迁移数据

最新推荐文章于 2022-12-30 12:11:15 发布

cuibai1991

最新推荐文章于 2022-12-30 12:11:15 发布

阅读量189

点赞数

文章标签：数据库操作系统网络

转载学习

在Oracle10g中，不仅可以利用跨平台传输表空间的新特性在异构平台数据库之间迁移数据，在特定条件下，还可以利用Convert database特性进行整个数据库跨平台的迁移。

如果要在10G中进行全库跨平台的迁移，那么只能在低位和低位或者高位和高位平台之间进行迁移，无法进行高位与低位平台的迁移。大家可以参考V$TRANSPORTABLE_PLATFORM中的信息以决定是否能够迁移。

SQL> select * from V$TRANSPORTABLE_PLATFORM. order by 3;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

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

4 HP-UX IA (64-bit) Big

1 Solaris[tm] OE (32-bit) Big

16 Apple Mac OS Big

3 HP-UX (64-bit) Big

9 IBM zSeries Based Linux Big

6 AIX-Based Systems (64-bit) Big

2 Solaris[tm] OE (64-bit) Big

18 IBM Power Based Linux Big

17 Solaris Operating System (x86) Little

12 Microsoft Windows 64-bit for AMD Little

13 Linux 64-bit for AMD Little

8 Microsoft Windows IA (64-bit) Little

15 HP Open VMS Little

5 HP Tru64 UNIX Little

10 Linux IA (32-bit) Little

7 Microsoft Windows IA (32-bit) Little

11 Linux IA (64-bit) Little、

Convert database具体如下特性：

1、只能传输数据表空间的数据文件。临时表空间将不会被传输，如果临时表空间是本地管理的话，那么传输数据库完成后将会自动在目标数据库创建临时表空间。

2、假如源主数据库使用PFILE，那么PFILE也会被传输。如果源数据库使用的是SPFILE，那么将会自动产生一个PFILE进行传输，并在目标数据库生成SPFILE。

3、控制文件和联机日志文件将不会被传输。在目标数据库open resetlogs的过程中，将会自动创建控制文件和联机日志文件。

4、 BFILE、外部表和directory将不会被传输。RMAN检查的时候将会把这些对象列出，用户可以手工创建这些对象。

5、密码文件将不会被传输，用户必须在目标数据库手工创建密码文件。

传输数据库的第一步是将源数据库置于READ ONLY模式：

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1610612736 bytes

Fixed Size 1262044 bytes

Variable Size 385879588 bytes

Database Buffers 1207959552 bytes

Redo Buffers 15511552 bytes

Database mounted.

SQL> alter database open read only;

Database altered.

第二步利用dbms_tdb包检查数据库能否被传输以及列出外部表和DIRECTORY等无法传输的对象信息（测试案例采用Linux迁移至Windows平台）：

SQL> set serveroutput on

SQL> declare

2 db_ready boolean;

3 begin

4 /* db_ready is ignored, but with SERVEROUTPUT set to ON any

5 * conditions preventing transport will be output to console */

6 db_ready := dbms_tdb.check_db('Microsoft Windows IA (32-bit)',

7 dbms_tdb.skip_none);

8 end;

9 /

PL/SQL procedure successfully completed.

SQL> declare

2 external boolean;

3 begin

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

5 * dbms_tdb.check_external displays report of external objects

6 * on console */

7 external := dbms_tdb.check_external;

8 end;

10 /

The following external tables exist in the database:

SH.SALES_TRANSACTIONS_EXT

The following directories exist in the database:

SYS.DATA_PUMP_DIR, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FILE_DIR,

SYS.DATA_FILE_DIR, SYS.WORK_DIR, SYS.ADMIN_DIR

The following BFILEs exist in the database:

PM.PRINT_MEDIA

PL/SQL procedure successfully completed.

环境检查完毕后就可以利用RMAN的convert database命令在源数据库进行转换（convert database也可以在目标数据库进行，具体可以参考文档）：

SQL> host rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Thu Jun 28 15:23:16 2007

connected to target database: DBTEST (DBID=1018019231)

RMAN> CONVERT DATABASE NEW DATABASE 'newdb'

2> transport script. '/home/oracle/liuyun/transportdb.sql'

3> to platform. 'Microsoft Windows IA (32-bit)'

4> db_file_name_convert '/opt/oracle/oradata/dbtest/' '/home/oracle/liuyun/';

Starting convert at 28-JUN-07

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=153 devtype=DISK

External table SH.SALES_TRANSACTIONS_EXT found in the database

Directory SYS.DATA_PUMP_DIR found in the database

Directory SYS.SUBDIR found in the database

Directory SYS.XMLDIR found in the database

Directory SYS.MEDIA_DIR found in the database

Directory SYS.LOG_FILE_DIR found in the database

Directory SYS.DATA_FILE_DIR found in the database

Directory SYS.WORK_DIR found in the database

Directory SYS.ADMIN_DIR found in the database

BFILE PM.PRINT_MEDIA found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file