oracle 同字节序跨平台,Oracle 10g同字节序跨平台迁移的测试

在oracle10gR2中,oracle开始支持同字节序数据库的跨平台迁移,下面将linux下的数据库迁移到windows。[@more@]

Oracle 10g同字节序跨平台迁移的测试

在oracle10gR2中,oracle开始支持同字节序数据库的跨平台迁移,下面将linux下的数据库迁移到windows。

这一技术实现有以下几点注意事项:

1、源平台和目标平台需要具有相同的字节序

2、重做日志文件和控制文件不会传输,迁移之后需要重建控制文件使用resetlogs的方式打开数据库

3、临时文件不会被传输

4、BFILE、外部表和Directories、口令文件不会被传输

1,确认平台及版本,看到源平台和目标平台具有相同的字节序

SQL>columnPLATFORM_NAME format a50

SQL>select PLATFORM_NAME, ENDIAN_FORMAT from v$transportable_platform

2where platform_name in('Linux IA (32-bit)','Microsoft Windows IA (32-bit)');

PLATFORM_NAMEENDIAN_FORMAT

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

Microsoft Windows IA (32-bit)Little

Linux IA(32-bit)Little

2,确认迁移是否支持,linux的数据库需处于read only模式打开,使用dbms_tdb.check_db进行检查

SQL> shutdown immediate;

SQL> startup mount

SQL> alter database open read only;

SQL> set serveroutput on

SQL> declare

2db_ready boolean;

3begin

4db_ready:=dbms_tdb.check_db('Microsoft Windows IA (32-bit)');

5end;

6/

PL/SQL procedure successfully completed.

3,检查外部对象,使用dbms_tdb.check_external来识别外部表、Directories或BFILES等,这些对

象所指向的外部数据不能被RMAN自动转移。

SQL> declare

2external boolean;

3begin

4external:=dbms_tdb.check_external;

5end;

6/

The following directories exist in the database:

SYS.ADMIN_DIR, SYS.WORK_DIR, SYS.DATA_PUMP_DIR

PL/SQL procedure successfully completed.

4,使用RMAN进行跨平台文件迁移。执行跨平台迁移首先要通过RMAN对数据文件进行转移:

[oracle@linx130 orcl]$ rman target /

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Jan 14 09:30:46 2008

Copyright (c) 1982, 2005, Oracle.All rights reserved.

connected to target database: ORCL (DBID=1171353837)

RMAN> convert database new database 'LISA'

2> transport script '/oracle/oradata/orcl/transport/transport.sql'

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

4> db_file_name_convert '/oracle/oradata/orcl' '/oracle/oradata/orcl/transport';

Starting convert at 2008-01-14 09:32:10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

Directory SYS.ADMIN_DIR found in the database

Directory SYS.WORK_DIR found in the database

Directory SYS.DATA_PUMP_DIR found in the database

User SYS with SYSDBA and SYSOPER privilege found in password file

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf

converted datafile=/oracle/oradata/orcl/transport/system01.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf

converted datafile=/oracle/oradata/orcl/transport/sysaux01.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00005 name=/oracle/oradata/orcl/eric_data01.dbf

converted datafile=/oracle/oradata/orcl/transport/eric_data01.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf

converted datafile=/oracle/oradata/orcl/transport/undotbs01.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00006 name=/oracle/oradata/orcl/eric_indx01.dbf

converted datafile=/oracle/oradata/orcl/transport/eric_indx01.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

channel ORA_DISK_1: starting datafile conversion

input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf

converted datafile=/oracle/oradata/orcl/transport/users01.dbf

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Run SQL script /oracle/oradata/orcl/transport/transport.sql on the target platform to create database

Edit init.ora file /oracle/product/10.2.0/dbs/init_00j64gcv_1_0.ora.This PFILE will be used to create the database on the target platform

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 backup at 2008-01-14 09:33:29

RMAN的转换语句中指定生成一个转移脚本transport.sql,转移的目标平台是'Microsoft Windows IA (32-bit)',所有数据文件转移后存放在一个新的目录下,还生成了一个参数文件init_00j64gcv_1_0.ora,可以根据需要进行相应的更改,参数文件如下:

[oracle@linx130 orcl]$ cat /oracle/product/10.2.0/dbs/init_00j64gcv_1_0.ora

# Please change the values of the following parameters:

control_files= "/oracle/product/10.2.0/dbs/cf_D-LISA_id-1171353837_00j64gcv"

db_recovery_file_dest= "/oracle/product/10.2.0/dbs/flash_recovery_area"

db_recovery_file_dest_size= 2147483648

background_dump_dest= "/oracle/product/10.2.0/dbs/bdump"

user_dump_dest= "/oracle/product/10.2.0/dbs/udump"

core_dump_dest= "/oracle/product/10.2.0/dbs/cdump"

audit_file_dest= "/oracle/product/10.2.0/dbs/adump"

db_name= "LISA"

# Please review the values of the following parameters:

__shared_pool_size= 100663296

__large_pool_size= 4194304

__java_pool_size= 4194304

__streams_pool_size= 0

__db_cache_size= 54525952

remote_login_passwordfile= "EXCLUSIVE"

db_domain= ""

dispatchers= "(PROTOCOL=TCP) (SERVICE=orclXDB)"

# The values of the following parameters are from source database:

processes= 150

sga_target= 167772160

db_block_size= 8192

compatible= "10.2.0.3.0"

log_archive_format= "%t_%s_%r.dbf"

db_file_multiblock_read_count= 16

undo_management= "AUTO"

undo_tablespace= "UNDOTBS1"

job_queue_processes= 10

open_cursors= 300

pga_aggregate_target= 16777216

参数文件的内容可以在新的平台上重新创建,这个参数文件可以作为参考.

转移脚本transport.sql的内容如下:

[oracle@linx130 transport]$ cat transport.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/product/10.2.0/dbs/init_00j64gcv_1_0.ora'

CREATE CONTROLFILE REUSE SET DATABASE "LISA" RESETLOGSARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/oracle/product/10.2.0/dbs/arch_D-LISA_id-1171353837_S-2_T-1_A-643573105_00j64gcv'SIZE 50M,

GROUP 2 '/oracle/product/10.2.0/dbs/arch_D-LISA_id-1171353837_S-3_T-1_A-643573105_00j64gcv'SIZE 50M,

GROUP 3 '/oracle/product/10.2.0/dbs/arch_D-LISA_id-1171353837_S-4_T-1_A-643573105_00j64gcv'SIZE 50M

DATAFILE

'/oracle/oradata/orcl/transport/system01.dbf',

'/oracle/oradata/orcl/transport/undotbs01.dbf',

'/oracle/oradata/orcl/transport/sysaux01.dbf',

'/oracle/oradata/orcl/transport/users01.dbf',

'/oracle/oradata/orcl/transport/eric_data01.dbf',

'/oracle/oradata/orcl/transport/eric_indx01.dbf'

CHARACTER SET WE8ISO8859P1

;

-- 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/product/10.2.0/dbs/data_D-LISA_I-1171353837_TS-TEMP_FNO-1_00j64gcv'

SIZE 20971520AUTOEXTEND ON NEXT 655360MAXSIZE 32767M;

-- 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/product/10.2.0/dbs/init_00j64gcv_1_0.ora'

@@ ?/rdbms/admin/utlirp.sql

SHUTDOWN IMMEDIATE

STARTUP PFILE='/oracle/product/10.2.0/dbs/init_00j64gcv_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;

5,通过FTP将/oracle/oradata/orcl/transport/下的资料传输到WINDOWS系统

D:oracleproduct10.2.0oradata>mkdir lisa

D:oracleproduct10.2.0oradata>cd lisa

D:oracleproduct10.2.0oradatalisa>ftp 172.17.61.130

Connected to 172.17.61.130.

220 (vsFTPd 2.0.1)

User (172.17.61.130:(none)): oracle

331 Please specify the password.

Password:

230 Login successful.

ftp> cd /oracle/oradata/orcl

250 Directory successfully changed.

ftp> cd transport

250 Directory successfully changed.

ftp> ls

200 PORT command successful. Consider using PASV.

150 Here comes the directory listing.

eric_data01.dbf

eric_indx01.dbf

sysaux01.dbf

system01.dbf

transport.sql

undotbs01.dbf

users01.dbf

226 Directory send OK.

ftp: 105 bytes received in 0.01Seconds 7.00Kbytes/sec.

ftp> mget *

200 Switching to ASCII mode.

mget eric_data01.dbf? y

200 PORT command successful. Consider using PASV.

150 Opening BINARY mode data connection for eric_data01.dbf (52436992 bytes)

226 File send OK.

ftp: 52436992 bytes received in 3.56Seconds 14717.09Kbytes/sec.

mget eric_indx01.dbf? y

200 PORT command successful. Consider using PASV.

150 Opening BINARY mode data connection for eric_indx01.dbf (10493952 bytes)

226 File send OK.

ftp: 10493952 bytes received in 0.50Seconds 20987.90Kbytes/sec.

mget sysaux01.dbf? y

200 PORT command successful. Consider using PASV.

150 Opening BINARY mode data connection for sysaux01.dbf (262152192 bytes).

226 File send OK.

ftp: 262152192 bytes received in 21.08Seconds 12437.24Kbytes/sec.

mget system01.dbf? y

200 PORT command successful. Consider using PASV.

150 Opening BINARY mode data connection for system01.dbf (503324672 bytes).

226 File send OK.

ftp: 503324672 bytes received in 43.28Seconds 11628.96Kbytes/sec.

mget transport.sql? y

200 PORT command successful. Consider using PASV.

150 Opening BINARY mode data connection for transport.sql (2765 bytes).

226 File send OK.

ftp: 2765 bytes received in 0.03Seconds 89.19Kbytes/sec.

mget undotbs01.dbf? y

200 PORT command successful. Consider using PASV.

150 Opening BINARY mode data connection for undotbs01.dbf (26222592 bytes).

226 File send OK.

ftp: 26222592 bytes received in 1.44Seconds 18248.15Kbytes/sec.

mget users01.dbf? y

200 PORT command successful. Consider using PASV.

150 Opening BINARY mode data connection for users01.dbf (5251072 bytes).

226 File send OK.

ftp: 5251072 bytes received in 0.27Seconds 19815.37Kbytes/sec.

ftp>

D:oracleproduct10.2.0oradatalisa>mkdir datafile

D:oracleproduct10.2.0oradatalisa>mkdir controlfile

D:oracleproduct10.2.0oradatalisa>mkdir onlinelog

D:oracleproduct10.2.0oradatalisa>mv *.dbf datafile/

6,创建基础环境

D:oracleproduct10.2.0admin>mkdir lisa

D:oracleproduct10.2.0admin>cd lisa

D:oracleproduct10.2.0adminlisa>mkdir adump

D:oracleproduct10.2.0adminlisa>mkdir bdump

D:oracleproduct10.2.0adminlisa>mkdir cdump

D:oracleproduct10.2.0adminlisa>mkdir dpdump

D:oracleproduct10.2.0adminlisa>mkdir pfile

D:oracleproduct10.2.0adminlisa>mkdir udump

创建WINDOWS服务

D:oracleproduct10.2.0admin>oradim -new -sid lisa

修改参数文件,参数文件可以从前面自动生成的参数文件中修改得到,传输过程略,放在D:oracleproduct10.2.0adminlisapfile下面,文件名改成init.ora

先将控制文件的内容给注释掉,待建好控制文件后再改成正确的。

修改的参数文件如下 :

# Please change the values of the following parameters:

# control_files= "/oracle/product/10.2.0/dbs/cf_D-LISA_id-1171353837_00j64gcv"

db_recovery_file_dest= "D:oracleproduct10.2.0adminlisaflash_recovery_area"

db_recovery_file_dest_size= 2147483648

background_dump_dest= "D:oracleproduct10.2.0adminlisabdump"

user_dump_dest= "D:oracleproduct10.2.0adminlisaudump"

core_dump_dest= "D:oracleproduct10.2.0adminlisacdump"

audit_file_dest= "D:oracleproduct10.2.0adminlisaadump"

db_name= "LISA"

7,迁移步骤。准备工作完成后,现进行新平台的数据库加载等工作,可以参考在源平台生成的transport.sql脚本。

STARTUP NOMOUNT PFILE='D:oracleproduct10.2.0adminlisapfileinit.ora';

CREATE CONTROLFILE REUSE SET DATABASE "LISA" RESETLOGSARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 'D:oracleproduct10.2.0oradatalisaonlinelogredo01'SIZE 50M,

GROUP 2 'D:oracleproduct10.2.0oradatalisaonlinelogredo02'SIZE 50M,

GROUP 3 'D:oracleproduct10.2.0oradatalisaonlinelogredo03'SIZE 50M

DATAFILE

'D:oracleproduct10.2.0oradatalisadatafilesystem01.dbf',

'D:oracleproduct10.2.0oradatalisadatafileundotbs01.dbf',

'D:oracleproduct10.2.0oradatalisadatafilesysaux01.dbf',

'D:oracleproduct10.2.0oradatalisadatafileusers01.dbf',

'D:oracleproduct10.2.0oradatalisadatafileeric_data01.dbf',

'D:oracleproduct10.2.0oradatalisadatafileeric_indx01.dbf'

CHARACTER SET WE8ISO8859P1;

查看所建立控制文件的位置:

SQL> select value from v$parameter where name='control_files';

VALUE

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

D:ORACLEPRODUCT10.2.0ADMINLISAFLASH_RECOVERY_AREALISACONTROLFILEO1_MF_3RRBT2LK_.CTL

Shutdown数据库,更改控制的位置,并添加到参数文件夹中

SQL> shutdown immediate;

在参数文件中增加控制文件的信息:

control_files= "D:oracleproduct10.2.0oradatalisacontrolfileO1_MF_3RRBT2LK_.CTL"

现在可以启动到mount状态

SQL>startup mount pfile='D:oracleproduct10.2.0adminlisapfileinit.ora'

ORACLE例程已启动

Total System Global Area167772160 bytes

Fixed Size1289484 bytes

Variable Size62915316 bytes

Database Buffers96468992 bytes

Redo Buffers7098368 bytes

数据库装载完毕

8,参照transport.sql脚本需对数据进行恢复操作

SQL>startup mount pfile='D:oracleproduct10.2.0adminlisapfileinit.ora'

ORACLE instance started.

SQL>alter database open resetlogs;

Database altered.

由于源平台和目标平台的oracle版本相同,打开比较顺利,没有出现需要升级的提示

建立spfile

SQL> create spfile from pfile='D:oracleproduct10.2.0adminlisapfileinit.ora';

File created.

最后不要忘记增加临时文件

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:oracleproduct10.2.0oradatalisad

atafiletmp01.dbf' SIZE 50mAUTOEXTEND ON NEXT 655360MAXSIZE 100M;

Tablespace altered.

至此数据迁移完成!

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值