在oracle10gR2中，oracle開始支持同字節(jié)序數(shù)據(jù)庫(kù)的跨平臺(tái)遷移，下面將linux下的數(shù)據(jù)庫(kù)遷移到windows。
Oracle 10g同字節(jié)序跨平臺(tái)遷移的測(cè)試
在oracle10gR2中，oracle開始支持同字節(jié)序數(shù)據(jù)庫(kù)的跨平臺(tái)遷移，
下面將linux下的數(shù)據(jù)庫(kù)遷移到windows。
這一技術(shù)實(shí)現(xiàn)有以下幾點(diǎn)注意事項(xiàng)：
1、 源平臺(tái)和目標(biāo)平臺(tái)需要具有相同的字節(jié)序
2、 重做日志文件和控制文件不會(huì)傳輸，遷移之后需要重建控制文件使用resetlogs的方式打開數(shù)據(jù)庫(kù)
3、 臨時(shí)文件不會(huì)被傳輸
4、 BFILE、外部表和Directories、口令文件不會(huì)被傳輸
1，確認(rèn)平臺(tái)及版本，看到源平臺(tái)和目標(biāo)平臺(tái)具有相同的字節(jié)序
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，確認(rèn)遷移是否支持,linux的數(shù)據(jù)庫(kù)需處于read only模式打開，使用dbms_tdb.check_db進(jìn)行檢查
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，檢查外部對(duì)象，使用dbms_tdb.check_external來(lái)識(shí)別外部表、Directories或BFILES等，這些對(duì)
象所指向的外部數(shù)據(jù)不能被RMAN自動(dòng)轉(zhuǎn)移。
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進(jìn)行跨平臺(tái)文件遷移。執(zhí)行跨平臺(tái)遷移首先要通過(guò)RMAN對(duì)數(shù)據(jù)文件進(jìn)行轉(zhuǎn)移：
[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的轉(zhuǎn)換語(yǔ)句中指定生成一個(gè)轉(zhuǎn)移腳本transport.sql，轉(zhuǎn)移的目標(biāo)平臺(tái)是’Microsoft Windows IA (32-bit)’，所有數(shù)據(jù)文件轉(zhuǎn)移后存放在一個(gè)新的目錄下，還生成了一個(gè)參數(shù)文件init_00j64gcv_1_0.ora，可以根據(jù)需要進(jìn)行相應(yīng)的更改，參數(shù)文件如下：
[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
參數(shù)文件的內(nèi)容可以在新的平臺(tái)上重新創(chuàng)建，這個(gè)參數(shù)文件可以作為參考.
轉(zhuǎn)移腳本transport.sql的內(nèi)容如下：
[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，通過(guò)FTP將/oracle/oradata/orcl/transport/下的資料傳輸?shù)絎INDOWS系統(tǒng)
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，創(chuàng)建基礎(chǔ)環(huán)境
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
創(chuàng)建WINDOWS服務(wù)
D:oracleproduct10.2.0admin>oradim -new -sid lisa
修改參數(shù)文件，參數(shù)文件可以從前面自動(dòng)生成的參數(shù)文件中修改得到，傳輸過(guò)程略，放在D:oracleproduct10.2.0adminlisapfile下面，文件名改成init.ora
先將控制文件的內(nèi)容給注釋掉，待建好控制文件后再改成正確的。
修改的參數(shù)文件如下 ：
# 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，遷移步驟。準(zhǔn)備工作完成后，現(xiàn)進(jìn)行新平臺(tái)的數(shù)據(jù)庫(kù)加載等工作，可以參考在源平臺(tái)生成的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數(shù)據(jù)庫(kù)，更改控制的位置，并添加到參數(shù)文件夾中
SQL> shutdown immediate;
在參數(shù)文件中增加控制文件的信息：
control_files = "D:oracleproduct10.2.0oradatalisacontrolfileO1_MF_3RRBT2LK_.CTL"
現(xiàn)在可以啟動(dòng)到mount狀態(tài)
SQL> startup mount pfile=’D:oracleproduct10.2.0adminlisapfileinit.ora’
ORACLE 例程已啟動(dòng)
Total System Global Area 167772160 bytes
Fixed Size 1289484 bytes
Variable Size 62915316 bytes
Database Buffers 96468992 bytes
Redo Buffers 7098368 bytes
數(shù)據(jù)庫(kù)裝載完畢
8，參照transport.sql腳本需對(duì)數(shù)據(jù)進(jìn)行恢復(fù)操作
SQL> startup mount pfile=’D:oracleproduct10.2.0adminlisapfileinit.ora’
ORACLE instance started.
SQL> alter database open resetlogs;
Database altered.
由于源平臺(tái)和目標(biāo)平臺(tái)的oracle版本相同，打開比較順利，沒有出現(xiàn)需要升級(jí)的提示
建立spfile
SQL> create spfile from pfile=’D:oracleproduct10.2.0adminlisapfileinit.ora’;
File created.
最后不要忘記增加臨時(shí)文件
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ’D:oracleproduct10.2.0oradatalisad
atafiletmp01.dbf’ SIZE 50m AUTOEXTEND ON NEXT 655360 MAXSIZE 100M;
Tablespace altered.
至此數(shù)據(jù)遷移完成！
|
