Oracle 10g同位元組序跨平臺遷移的測試
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.
至此資料遷移完成！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/271283/viewspace-997337/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 同位元組序跨平臺資料庫遷移和升級的測試資料庫
- RMAN同位元組序跨平臺跨版本遷移資料庫資料庫
- RMAN同位元組序跨平臺跨版本遷移資料庫（一)資料庫
- RMAN同位元組序跨平臺跨版本遷移資料庫（二）資料庫
- 同位元組序跨平臺表空間傳輸的測試
- ORACLE 跨平臺遷移方法Oracle
- 跨 OS 平臺遷移 Oracle DBOracle
- Oracle跨平臺遷移的簡單總結Oracle
- RMAN跨小版本跨平臺與位元組序傳輸表空間
- 跨平臺遷移oracle資料庫指南Oracle資料庫
- datapump跨平臺升級遷移的對比測試和優化優化
- datapump跨平臺升級遷移的對比測試和最佳化
- 跨平臺遷移支援檢視
- 異構OS平臺的不同點 - 位元組順序
- 利用Oracle Data Guard完成跨平臺的資料庫遷移案例Oracle資料庫
- datapump跨平臺升級遷移的總結
- 使用RMAN完成跨平臺資料遷移
- 利用RMAN跨平臺遷移資料庫資料庫
- rman進行跨平臺資料遷移
- Oracle 10g 跨平臺傳輸 相容平臺列表Oracle 10g
- zt 跨平臺 跨版本 大規模資料遷移
- Oracle rman duplicate遷移測試Oracle
- 大型資料庫跨平臺遷移總結資料庫
- 如何利用 RMAN 可傳輸表空間遷移資料庫到不同位元組序的平臺 (文件 ID 1983639.1)資料庫
- 12c跨平臺完成PDB的備份遷移
- 一個跨平臺資料遷移的方案優化優化
- 【MOS】如何利用RMAN可傳輸表空間遷移資料庫到不同位元組序的平臺(文件 ID 1983639.1)資料庫
- 資料庫中跨平臺遷移方法介紹資料庫
- [zt]跨平臺表空間傳輸 (DB遷移)
- 跨 OS 平臺遷移 Oracle DB (Generic Platform) (文件 ID 1525396.1)OraclePlatform
- ORACLE11G從WINDOWS到LINUX跨平臺遷移並升級OracleWindowsLinux
- 用傳輸表空間跨平臺遷移資料
- RMAN備份恢復典型案例——跨平臺遷移pdb
- 利用CONVERT實現跨平臺表空間遷移
- 跨平臺表空間遷移(傳輸表空間)
- Oracle 不同平臺間表空間遷移Oracle
- expdp 遷移測試
- 行遷移測試