在oracle10gR2中，oracle开始支持同字节序数据库的跨平台迁移，下面将linux下的数据库迁移到windows。
Oracle 10g同字节序跨平台迁移的测试
在oracle10gR2中，oracle开始支持同字节序数据库的跨平台迁移，
下面将linux下的数据库迁移到windows。
这一技术实现有以下几点注意事项：
1、 源平台和目标平台需要具有相同的字节序
2、 重做日志文件和控制文件不会传输，迁移之后需要重建控制文件使用resetlogs的方式打开数据库
3、 临时文件不会被传输
4、 BFILE、外部表和Directories、口令文件不会被传输
1，确认平台及版本，看到源平台和目标平台具有相同的字节序
SQL> column PLATFORM_NAME format a50
SQL> select PLATFORM_NAME, ENDIAN_FORMAT from v$transportable_platform
2 where platform_name in(’Linux IA (32-bit)’,’Microsoft Windows IA (32-bit)’);
PLATFORM_NAME ENDIAN_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
2 db_ready boolean;
3 begin
4 db_ready:=dbms_tdb.check_db(’Microsoft Windows IA (32-bit)’);
5 end;
6 /
PL/SQL procedure successfully completed.
3，检查外部对象，使用dbms_tdb.check_external来识别外部表、Directories或BFILES等，这些对
象所指向的外部数据不能被RMAN自动转移。
SQL> declare
2 external boolean;
3 begin
4 external:=dbms_tdb.check_external;
5 end;
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" RESETLOGS ARCHIVELOG
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_0 0j64gcv’
SIZE 20971520 AUTOEXTEND ON NEXT 655360 MAXSIZE 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" RESETLOGS ARCHIVELOG
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 Area 167772160 bytes
Fixed Size 1289484 bytes
Variable Size 62915316 bytes
Database Buffers 96468992 bytes
Redo Buffers 7098368 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 50m AUTOEXTEND ON NEXT 655360 MAXSIZE 100M;
Tablespace altered.
至此数据迁移完成！
|
