利用DBMS_FILE_TRANSFER遷移數據庫從AIX至Linux

發布時間：2020-07-08 16:33:27 來源：網絡閱讀：1775 作者：lizhhui001 欄目：關系型數據庫

環境描述

	源端	目標端
操作系統	AIX 6100-03-10-1119	RHEL6.5 x64
集群	Oracle RAC+ASM	Oracle RAC+ASM
數據庫版本	11.2.0.3.0	11.2.0.4.0

1、初始化階段

1.1、源端數據庫

源端數據庫testdb，數據庫版本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創建目錄SOURCEDIR，其路徑為遷移表空間數據文件路徑

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

Directory created.

源端數據庫必須為歸檔模式

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用戶設置TMPDR環境變量

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文件里的大部分參數都有說明，可以參照說明進行配置。需要注意的是platformid必須設置成源端數據庫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

在源端和目標端創建xtt.properties文件參數配置的目錄

1.2、目標端數據庫

源端數據庫testdb，數據庫版本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

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

源端：

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

在目標端創建到源端的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創建目錄DESTDIR，其路徑為目標端數據庫數據文件路徑

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

Directory created.

SQL>

如下為oracle用戶設置TMPDR環境變量

export TMPDIR=/home/oracle/tts

2、數據文件傳輸階段

本階段XTTSTEST表空間的數據文件將從源端數據庫傳輸到目標端數據庫，字節轉換由DBMS_FILE_TRANSFER包自動完成，本步驟只需要執行一次，數據文件傳輸過程中不影響源端數據庫正常訪問。

在源端以oracle用戶登錄，并設置數據庫和TMPDIR變量。

2.1. 源端創建傳輸腳本

運行 '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

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