利用DBMS_FILE_TRANSFER遷移數(shù)據(jù)庫從AIX至Linux

環(huán)境描述

創(chuàng)新互聯(lián)公司技術(shù)團隊十余年來致力于為客戶提供成都網(wǎng)站設(shè)計、成都做網(wǎng)站、成都品牌網(wǎng)站建設(shè)、成都營銷網(wǎng)站建設(shè)、搜索引擎SEO優(yōu)化等服務(wù)。經(jīng)過多年發(fā)展，公司擁有經(jīng)驗豐富的技術(shù)團隊，先后服務(wù)、推廣了上千余家網(wǎng)站，包括各類中小企業(yè)、企事單位、高校等機構(gòu)單位。

	源端	目標端
操作系統(tǒng)	AIX 6100-03-10-1119	RHEL6.5 x64
集群	Oracle RAC+ASM	Oracle RAC+ASM
數(shù)據(jù)庫版本	11.2.0.3.0	11.2.0.4.0

1、初始化階段

1.1、源端數(shù)據(jù)庫

源端數(shù)據(jù)庫testdb，數(shù)據(jù)庫版本11.2.0.3.0,運行在AIX 64-bit

db1:/oracle/db/bin$echo $ORACLE_SID

testdb1

db1:/oracle/db/bin$echo $ORACLE_HOME

/oracle/db

SYS@testdb1 > select * from v$version where banner like'%Database%';

BANNER

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

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

SYS@testdb1 > select name, platform_id, platform_name fromv$database;

NAME PLATFORM_IDPLATFORM_NAME

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

TESTDB 6 AIX-BasedSystems (64-bit)

需要進行遷移的表空間為XTTSTEST:

select t.name "Tablespace", f.file# "File#",f.name "Filename", f.status "Status"

from v$datafile f, v$tablespace t

where f.ts#=t.ts# and t.name like 'XTTS%'

order by 1, 2;

Tablespace File# Filename Status

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

XTTSTEST 41 +ORADATA/testdb/datafile/xttstest.362.959887907 ONLINE

SYS@testdb1 >

在源端為DBMS_FILE_TRANSFER創(chuàng)建目錄SOURCEDIR，其路徑為遷移表空間數(shù)據(jù)文件路徑

SYS@testdb1 > create directory sourcedir as '+ORADATA/testdb/datafile';

Directory created.

源端數(shù)據(jù)庫必須為歸檔模式

SYS@testdb1 > archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 11771

Next log sequence to archive 11775

Current log sequence 11775

如下為oracle用戶設(shè)置TMPDR環(huán)境變量

export TMPDIR=/home/oracle/tts

上傳rman-xttconvert_2.0.zip至/home/oracle/tts目錄并解壓

db1:/home/oracle/tts$unzip rman-xttconvert_2.0.zip

Archive: rman-xttconvert_2.0.zip

inflating: xttcnvrtbkupdest.sql

inflating: xttdbopen.sql

inflating: xttdriver.pl

inflating: xttprep.tmpl

inflating: xtt.properties

inflating: xttstartupnomount.sql

對xtt.properties文件進行如下配置

db1:/home/oracle/tts$grep ^[a-z] xtt.properties

tablespaces=XTTSTEST

platformid=6

srcdir=SOURCEDIR

dstdir=DESTDIR

srclink=TTSLINK

dfcopydir=/home/oracle/stage_source

backupformat=/home/oracle/stage_source

stageondest=/home/oracle/stage_dest

backupondest=+FRA_SB

asm_home=/testdb/orgrid/oracle/product/112

asm_sid=+ASM1

xtt.properties文件里的大部分參數(shù)都有說明，可以參照說明進行配置。需要注意的是platformid必須設(shè)置成源端數(shù)據(jù)庫platform id，platform id可以從v$database查詢到。

xtt.properties在源端配置完成后，拷貝到目標端：

db1:/home/oracle$scp -r tts x.x.x.81:/home/oracle

oracle@x.x.x.81's password:

rman-xttconvert_2.0.zip 100% 26KB 26.3KB/s 00:00

xtt.properties 100% 7820 7.6KB/s 00:00

xttcnvrtbkupdest.sql 100% 1390 1.4KB/s 00:00

xttdbopen.sql 100% 71 0.1KB/s 00:00

xttdriver.pl 100% 90KB 89.6KB/s 00:00

xttprep.tmpl 100% 11KB 11.3KB/s 00:00

xttstartupnomount.sql 100% 52 0.1KB/s 00:00

在源端和目標端創(chuàng)建xtt.properties文件參數(shù)配置的目錄

1.2、目標端數(shù)據(jù)庫

源端數(shù)據(jù)庫testdb，數(shù)據(jù)庫版本11.2.0.4.0,運行在Linux x86 64-bit

[oracle@testdb1 ~]$ env | egrep "ORACLE_SID|ORACLE_HOME"

ORACLE_SID=testdb1

ORACLE_HOME=/testdb/ordb/oracle/product/112

SQL> select * from v$version where banner like '%Database%';

BANNER

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

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

SQL> select name, platform_id, platform_name from v$database;

NAME PLATFORM_ID PLATFORM_NAME

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

TESTDB 13 Linux x86 64-bit

需要注意的是源端數(shù)據(jù)庫和目標端數(shù)據(jù)庫字符集必須一致。

源端：

SYS@testdb1 > select PARAMETER, VALUE

2 from v$nls_parameters

3 where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER VALUE

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

NLS_CHARACTERSET ZHS16GBK

NLS_NCHAR_CHARACTERSET UTF8

目標端:

SQL> select PARAMETER, VALUE

2 from v$nls_parameters

3 where PARAMETER in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER VALUE

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

NLS_CHARACTERSET ZHS16GBK

NLS_NCHAR_CHARACTERSET UTF8

在目標端創(chuàng)建到源端的database link TTSLINK:

SQL> create public database link ttslink connect to systemidentified by oracle_test using 'source';

Database link created.

SQL> select * from dual@ttslink;

在目標端為DBMS_FILE_TRANSFER創(chuàng)建目錄DESTDIR，其路徑為目標端數(shù)據(jù)庫數(shù)據(jù)文件路徑

SQL> create directory destdir as '+DATA_SB/dest/datafile';

Directory created.

SQL>

如下為oracle用戶設(shè)置TMPDR環(huán)境變量

export TMPDIR=/home/oracle/tts

2、數(shù)據(jù)文件傳輸階段

本階段XTTSTEST表空間的數(shù)據(jù)文件將從源端數(shù)據(jù)庫傳輸?shù)侥繕硕藬?shù)據(jù)庫，字節(jié)轉(zhuǎn)換由DBMS_FILE_TRANSFER包自動完成，本步驟只需要執(zhí)行一次，數(shù)據(jù)文件傳輸過程中不影響源端數(shù)據(jù)庫正常訪問。

在源端以oracle用戶登錄，并設(shè)置數(shù)據(jù)庫和TMPDIR變量。

2.1.源端創(chuàng)建傳輸腳本

運行 'perl xttdriver.pl -S':

db1:/home/oracle$cd $TMPDIR

db1:/home/oracle/tts$$ORACLE_HOME/perl/bin/perl xttdriver.pl -S

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

Parsing properties

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

Done parsing properties

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

Checking properties

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

Done checking properties

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

Starting prepare phase

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