在oracle10gR2中，oracle開始支援同位元組序資料庫的跨平臺遷移，下面將linux下的資料庫遷移到windows。[@more@]

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

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.

至此資料遷移完成！

Oracle 10g同位元組序跨平臺遷移的測試

相關文章