当前位置:Gxlcms > 数据库问题 > 利用DBMS_FILE_TRANSFER迁移数据库从AIX至Linux

利用DBMS_FILE_TRANSFER迁移数据库从AIX至Linux

时间:2021-07-01 10:21:17 帮助过:26人阅读


源端

目标端

操作系统

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;

D

-

X

 

在目标端为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

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

Prepare source for Tablespaces:

                  ‘XTTSTEST‘  /home/oracle/stage_dest

xttpreparesrc.sql for ‘XTTSTEST‘ started atMon Nov 13 15:00:39 2017

xttpreparesrc.sql for  ended at Mon Nov 13 15:00:39 2017

Prepare source for Tablespaces:

                  ‘‘  /home/oracle/stage_dest

xttpreparesrc.sql for ‘‘ started at Mon Nov13 15:00:39 2017

xttpreparesrc.sql for  ended at Mon Nov 13 15:00:39 2017

Prepare source for Tablespaces:

                  ‘‘  /home/oracle/stage_dest

xttpreparesrc.sql for ‘‘ started at Mon Nov13 15:00:39 2017

xttpreparesrc.sql for  ended at Mon Nov 13 15:00:39 2017

Prepare source for Tablespaces:

                  ‘‘  /home/oracle/stage_dest

xttpreparesrc.sql for ‘‘ started at Mon Nov13 15:00:39 2017

xttpreparesrc.sql for  ended at Mon Nov 13 15:00:39 2017

Prepare source for Tablespaces:

                  ‘‘  /home/oracle/stage_dest

xttpreparesrc.sql for ‘‘ started at Mon Nov13 15:00:39 2017

xttpreparesrc.sql for  ended at Mon Nov 13 15:00:40 2017

Prepare source for Tablespaces:

                  ‘‘  /home/oracle/stage_dest

xttpreparesrc.sql for ‘‘ started at Mon Nov13 15:00:40 2017

xttpreparesrc.sql for  ended at Mon Nov 13 15:00:40 2017

Prepare source for Tablespaces:

                  ‘‘  /home/oracle/stage_dest

xttpreparesrc.sql for ‘‘ started at Mon Nov13 15:00:40 2017

xttpreparesrc.sql for  ended at Mon Nov 13 15:00:40 2017

Prepare source for Tablespaces:

                  ‘‘  /home/oracle/stage_dest

xttpreparesrc.sql for ‘‘ started at Mon Nov13 15:00:40 2017

xttpreparesrc.sql for  ended at Mon Nov 13 15:00:40 2017

 

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

Done with prepare phase

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

本步骤将在当前目录(TMPDIR)生成如下文件

1. xttplan.txt - containing the tablespacenames, their current SCNs and their datafile numbers:

db1:/home/oracle/tts$cat xttplan.txt

XTTSTEST::::15718791765461

41

 

2. xttnewdatafiles.txt - containing thetablespace names, datafile numbers, the destination directory object name and thesource filenames:

db1:/home/oracle/tts$catxttnewdatafiles.txt

::XTTSTEST

41,DESTDIR:/xttstest.362.959887907

 

3. getfile.sql - The PL/SQL script(formatting ours) that will be used at the destination to get the datafilesfrom the source:

db1:/home/oracle/tts$cat getfile.sql

0,SOURCEDIR,xttstest.362.959887907,DESTDIR,xttstest_362_959887907

 

4. xttpreparesrc.sql - the PL/SQL scriptused to create the files in this step.

 

5. xttprepareNaNd - the command script(empty at this step).

 

2.2. 传输数据文件到目标端

在目标端以oracle用户登录,并设置环境变量。

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

ORACLE_SID=dest1

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

 

从源端拷贝xttnewdatafiles.txt 和 getfile.sql到目标端

[oracle@testdb1 tts]$ scp x.x.x.3:/home/oracle/tts/xttnewdatafiles.txt.

The authenticity of host ‘x.x.x.3 (x.x.x.3)‘can‘t be established.

RSA key fingerprint is33:f6:00:32:8c:5d:3e:44:5f:1b:e1:61:40:c4:2d:c1.

Are you sure you want to continueconnecting (yes/no)? yes

Warning: Permanently added ‘x.x.x.3‘ (RSA)to the list of known hosts.

oracle@x.x.x.3‘s password:

xttnewdatafiles.txt                                                                                         100%   46     0.0KB/s  00:00   

[oracle@testdb1 tts]$ ls

rman-xttconvert_2.0.zip  xttdbopen.sql xttnewdatafiles.txt xtt.properties

xttcnvrtbkupdest.sql     xttdriver.pl   xttprep.tmpl         xttstartupnomount.sql

[oracle@testdb1 tts]$ ll

total 156

-rw-r----- 1 oracle oinstall 26975 Nov 1315:03 rman-xttconvert_2.0.zip

-rwxr-xr-x 1 oracle oinstall  1390 Nov 13 15:03 xttcnvrtbkupdest.sql

-rwxr-xr-x 1 oracle oinstall    71 Nov 13 15:03 xttdbopen.sql

-rwxr-xr-x 1 oracle oinstall 91722 Nov 1315:03 xttdriver.pl

-rw-r--r-- 1 oracle oinstall    46 Nov 13 15:21 xttnewdatafiles.txt

-rwxr-xr-x 1 oracle oinstall 11549 Nov 1315:03 xttprep.tmpl

-rwxr-xr-x 1 oracle oinstall  7820 Nov 13 15:03 xtt.properties

-rwxr-xr-x 1 oracle oinstall    52 Nov 13 15:03 xttstartupnomount.sql

[oracle@testdb1 tts]$ scp x.x.x.3:/home/oracle/tts/getfile.sql .

oracle@x.x.x.3‘s password:

getfile.sql                                                                                                 100%   66     0.1KB/s  00:01

 

在目标端通过执行‘xttdriver.pl -G‘进行数据文件传输

[oracle@testdb1 tts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -G

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

Parsing properties

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

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

Done parsing properties

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

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

Checking properties

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

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

Done checking properties

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

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

Getting datafiles from source

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

sh: line 5: warning: here-document at line0 delimited by end-of-file (wanted `EOF‘)

sh: line 6: warning: here-document at line0 delimited by end-of-file (wanted `EOF‘)

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

Executing getfile forgetfile_sourcedir_xttstest.362.959887907_0.sql

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

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

Completed getting datafiles from source

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

本步骤不产生任何输出。本步骤将花费数据库迁移的大部分时间,因为本步骤会传输源端的数据文件到目标端。

本步骤执行完成,可以在目标端数据库数据文件存储目录发现从源端传输过来的数据文件。

ASMCMD> ls

FILE_TRANSFER.339.959959381

SYSAUX.259.959957695

SYSTEM.303.959957695

UNDOTBS1.264.959957697

UNDOTBS2.335.959957845

USERS.256.959957697

xttstest_362_959887907

3. 增量更新备份阶段

 

增量更新备份是通过RMAN增量备份来实现的。不过该阶段增量备份是通过DBMS_FILE_TRANSFER.GET_FILE来实现的,而不是RMANBACKUP AS COPY。本阶段增量备份在源端数据库创建,然后传输到目标端数据库,最后与第二阶段传输过来的数据文件进行合并。

这个阶段中的步骤可以运行多次,以使目标中的datafiles更接近源文件的时间/ SCN。在此阶段,源数据库完全可访问。

3.1. 源端数据库创建增量备份

在源端数据库以oracle用户登录,并设置环境变量,执行‘xttdriver.pl -i‘:

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

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

Parsing properties

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

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

Done parsing properties

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

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

Checking properties

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

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

Done checking properties

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

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

Backup incremental

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

Prepare newscn for Tablespaces: ‘XTTSTEST‘

Prepare newscn for Tablespaces: ‘‘

Prepare newscn for Tablespaces: ‘‘

Prepare newscn for Tablespaces: ‘‘

Prepare newscn for Tablespaces: ‘‘

Prepare newscn for Tablespaces: ‘‘

Prepare newscn for Tablespaces: ‘‘

Prepare newscn for Tablespaces: ‘‘

rman target /  cmdfile /home/oracle/tts/rmanincrNaNd

Recovery Manager: Release 11.2.0.3.0 -Production on Mon Nov 13 15:14:31 2017

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

connected to target database: TESTDB (DBID=2896936214)

RMAN> set nocfau;

2> host ‘echo ts::XTTSTEST‘;

3> backup incremental from scn15718791765461

4>  tag tts_incr_update tablespace ‘XTTSTEST‘  format

5> ‘/home/oracle/stage_source/%U‘;

6>

executing command: SET NOCFAU

using target database control file insteadof recovery catalog

ts::XTTSTEST

host command complete

Starting backup at 2017-11-13 15:14:33

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1443 instance=testdb1device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=1490 instance=testdb1device type=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: SID=2 instance=testdb1device type=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: SID=98 instance=testdb1device type=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: SID=148 instance=testdb1device type=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: SID=196 instance=testdb1device type=DISK

allocated channel: ORA_DISK_7

channel ORA_DISK_7: SID=244 instance=testdb1device type=DISK

allocated channel: ORA_DISK_8

channel ORA_DISK_8: SID=292 instance=testdb1device type=DISK

backup will be obsolete on date 2017-11-2015:14:36

archived logs will not be kept or backed up

channel ORA_DISK_1: starting compressedfull datafile backup set

channel ORA_DISK_1: specifying datafile(s)in backup set

input datafile file number=00041name=+ORADATA/testdb/datafile/xttstest.362.959887907

channel ORA_DISK_1: starting piece 1 at2017-11-13 15:14:36

channel ORA_DISK_1: finished piece 1 at2017-11-13 15:14:43

piece handle=/home/oracle/stage_source/k8sjfjqs_1_1tag=TTS_INCR_UPDATE comment=NONE

channel ORA_DISK_1: backup set complete,elapsed time: 00:00:07

 

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

using channel ORA_DISK_7

using channel ORA_DISK_8

backup will be obsolete on date 2017-11-2015:14:43

archived logs will not be kept or backed up

channel ORA_DISK_1: starting compressedfull datafile backup set

channel ORA_DISK_1: specifying datafile(s)in backup set

including current control file in backupset

channel ORA_DISK_1: starting piece 1 at2017-11-13 15:14:44

channel ORA_DISK_1: finished piece 1 at2017-11-13 15:14:45

piece handle=/home/oracle/stage_source/k9sjfjr3_1_1tag=TTS_INCR_UPDATE comment=NONE

channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01

Finished backup at 2017-11-13 15:14:45

 

Recovery Manager complete.

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

Done backing up incrementals

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

db1:/home/oracle/tts$

 

本步骤在当前目录(TMPDIR)创建如下文件:

1. tsbkupmap.txt - containing thetablespace names, datafile numbers and the incremental backup pieces:

 

db1:/home/oracle/tts$cat tsbkupmap.txt

XTTSTEST::41:::1=k8sjfjqs_1_1

 

2. incrbackups.txt - containing the actuallocation of the incremental backup pieces:

db1:/home/oracle/tts$cat incrbackups.txt

/home/oracle/stage_source/k8sjfjqs_1_1

 

3. rmanincrNaNd - containing the RMANscripts used to create the incremental backups.

 

4. xttdetnewfromscnsrc.sql - the PL/SQLscript used to create the files in this step

 

5. xttplan.txt.new- after the first run of‘xttdriver.pl -i‘ this is just a copy of the xttplan.txt

 

3.2. 传输增量备份至目标端

传输增量备份至目标端的stageondest目录

db1:/home/oracle/tts$scp `cat incrbackups.txt` x.x.x.81:/home/oracle/stage_dest

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

k8sjfjqs_1_1                                                                                                100%   88KB  88.0KB/s  00:00   

db1:/home/oracle/tts$

 

3.3. 在目标端应用增量备份

在目标端以oracle用户登录,并设置环境变量。从源端拷贝xttplan.txt 和 tsbkupmap.txt文件。

[oracle@testdb1 ~]$ cd $TMPDIR

[oracle@testdb1 tts]$ scp x.x.x.3:/home/oracle/tts/xttplan.txt .

oracle@x.x.x.3‘s password:

xttplan.txt                                                                                                 100%   30     0.0KB/s  00:00   

[oracle@testdb1 tts]$ scp x.x.x.3:/home/oracle/tts/tsbkupmap.txt .

oracle@x.x.x.3‘s password:

tsbkupmap.txt                                                                                               100%   30     0.0KB/s  00:00

 

运行 ‘xttdriver.pl -r‘,把增量备份与第二阶段传输过来的数据文件进行合并

 

[oracle@testdb1 tts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r

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

Parsing properties

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

 

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

Done parsing properties

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

 

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

Checking properties

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

 

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

Done checking properties

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

 

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

Start rollforward

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

 

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Error:

------

/tmp/xxttconv_k3sjf6tk_1_1_41.sql executionfailed

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

直接运行sql脚本:

[oracle@testdb1 staging]$ sqlplus -L -s"/ as sysdba" @/tmp/xxttconv_k3sjf6tk_1_1_41.sql/home/oracle/tts/staging/k3sjf6tk_1_1 /home/oracle/tts/backupondest 6

ERROR IN CONVERSION ORA-19994: Message19994 not found;  product=RDBMS;

facility=ORA

ORA-19600: input file is backup piece

(/home/oracle/tts/staging/k3sjf6tk_1_1)

ORA-19601: output file is backup piece

(/home/oracle/tts/backupondest/xib_k3sjf6tk_1_1_41)

CONVERTED BACKUPPIECE/home/oracle/tts/backupondest/xib_k3sjf6tk_1_1_41

 

PL/SQL procedure successfully completed.

还是出错,参考如下文件,源端数据库rmant备份不能启动compress。

ORA-19837: INVALID BLOCKSIZE 0 IN BACKUPPIECE HEADER USING RMAN TTS BACKUPS DOING RECOVER (文档ID 1486655.1)

[oracle@testdb1 tts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r

============================================================

trace file is/home/oracle/tts/rollforward_Nov13_Mon_17_14_36_931//Nov13_Mon_17_14_36_931_.log

=============================================================

 

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

Parsing properties

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

 

 

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

Done parsing properties

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

 

 

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

Checking properties

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

 

 

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

Done checking properties

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

 

 

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

Start rollforward

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

 

Can‘t locate DBI.pm in @INC (@INC contains: /testdb/orgrid/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/5.10.0 /testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0 /testdb/orgrid/oracle/product/112/lib/testdb/orgrid/oracle/product/112/lib/asmcmd /testdb/orgrid/oracle/product/112/rdbms/lib/asmcmd/testdb/ordb/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi /testdb/ordb/oracle/product/112/perl/lib/5.10.0/testdb/ordb/oracle/product/112/perl/lib /testdb/orgrid/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/5.10.0 /testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0 /testdb/orgrid/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi /testdb/orgrid/oracle/product/112/perl/lib/5.10.0/testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0 /testdb/orgrid/oracle/product/112/perl/lib/site_perl.) at /testdb/orgrid/oracle/product/112/lib/asmcmdshare.pm line 205.

BEGIN failed--compilation aborted at /testdb/orgrid/oracle/product/112/lib/asmcmdshare.pmline 205.

Compilation failed in require at /testdb/orgrid/oracle/product/112/bin/asmcmdcoreline 147.

BEGIN failed--compilation aborted at /testdb/orgrid/oracle/product/112/bin/asmcmdcoreline 147.

 

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

End of rollforward phase

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

 “Can‘t locate DBI.pm in @INC错误是可以忽略的,这是由于Perl pragmaasmcmdcore中使用“严格模式的结果。

本步骤增量备份会被放在备份目标目录并和数据文件副本进行合并,本操作会重启数据库,在RAC环境中只启动目标端一个数据库实例。

可以在备份目标目录查看备份数据:

grep ^backupondest xtt.properties

backupondest=+fra_sb

 

ASMCMD> ls

nnndn0_0.256.959966091

nnndn0_0.257.959966945

ASMCMD> pwd

+fra_sb/dest/backupset/2017_11_13

 

3.4. 确定下次增量备份的起始SCN

源端以oracle用户登录,并设置环境变量,执行‘xttdriver.pl -s‘

$ORACLE_HOME/perl/bin/perl xttdriver.pl -s

Prepare newscn for Tablespaces: ‘XTTSTEST‘

New /home/oracle/tts/xttplan.txt with FROMSCN‘s generated

 

本步骤原来的xttplan.txt会被备份为xttplan.txt<timestamp>,起始SCN会被记录在xttplan.txt,如有需要执行‘xttdriver.pl -i‘进行下一次增量备份。

第三阶段可以多次执行,每次执行需要运行‘xttdriver.pl -i‘,拷贝增量备份文件、xttplan.txt和tsbkupmap.txt到目标端,在目标端运行‘xttdriver.pl -r‘,最后在源端运行‘xttdriver.pl -s‘。

 

本次操作不重复执行阶段3,直接执行阶段4.

 

4. 完成表空间传输阶段

本阶段源端数据文件为只读模式,目标端数据文件通地创建合并最终增量备份与源端数据文件保持一致。最后传输表空间元数据并导入到目标端数据库。源端数据库表空间置为只读模式直到本阶段完成。

4.1、设置表空间为READ ONLY

在源端以oracle用户登录,并设置环境变量,把XTTSTEST表空间设置为READ ONLY模式。

SYS@testdb1 > alter tablespace xttstest read only;

 

Tablespace altered.

 

4.2. 最后增量备份

源端:

$ORACLE_HOME/perl/bin/perl xttdriver.pl -i

$scp `cat incrbackups.txt` x.x.x.81:/home/oracle/stage_dest

目标端:

 [oracle@testdb1 tts]$ scp x.x.x.3:/home/oracle/tts/xttplan.txt.

oracle@x.x.x.3‘s password:

xttplan.txt                                                                           100%   32    0.0KB/s   00:00   

[oracle@testdb1 tts]$ scp x.x.x.3:/home/oracle/tts/tsbkupmap.txt .

oracle@x.x.x.3‘s password:

tsbkupmap.txt                                                                        100%   30    0.0KB/s   00:00   

[oracle@testdb1 tts]$

 

4.3. 最后增量备份合并

[oracle@testdb1 tts]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r

============================================================

trace file is /home/oracle/tts/rollforward_Nov13_Mon_17_28_50_404//Nov13_Mon_17_28_50_404_.log

===========================================================

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

Parsing properties

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

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

Done parsing properties

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

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

Checking properties

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

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

Done checking properties

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

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

Start rollforward

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

Can‘t locate DBI.pm in @INC (@INC contains:/testdb/orgrid/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi /testdb/orgrid/oracle/product/112/perl/lib/5.10.0/testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0 /testdb/orgrid/oracle/product/112/lib/testdb/orgrid/oracle/product/112/lib/asmcmd /testdb/orgrid/oracle/product/112/rdbms/lib/asmcmd/testdb/ordb/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi /testdb/ordb/oracle/product/112/perl/lib/5.10.0/testdb/ordb/oracle/product/112/perl/lib /testdb/orgrid/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/5.10.0 /testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0 /testdb/orgrid/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/5.10.0/x86_64-linux-thread-multi /testdb/orgrid/oracle/product/112/perl/lib/5.10.0/testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0/x86_64-linux-thread-multi/testdb/orgrid/oracle/product/112/perl/lib/site_perl/5.10.0 /testdb/orgrid/oracle/product/112/perl/lib/site_perl.) at /testdb/orgrid/oracle/product/112/lib/asmcmdshare.pm line 205.

BEGIN failed--compilation aborted at /testdb/orgrid/oracle/product/112/lib/asmcmdshare.pmline 205.

Compilation failed in require at /testdb/orgrid/oracle/product/112/bin/asmcmdcoreline 147.

BEGIN failed--compilation aborted at /testdb/orgrid/oracle/product/112/bin/asmcmdcoreline 147.

 

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

End of rollforward phase

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

 

[oracle@testdb1 tts]$

 

 

4.4.导入传输表空间元数据

 [oracle@testdb1 tts]$$ORACLE_HOME/perl/bin/perl xttdriver.pl -e

===========================================================

trace file is/home/oracle/tts/generate_Nov13_Mon_17_29_45_222//Nov13_Mon_17_29_45_222_.log

===========================================================

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

Parsing properties

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

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

Done parsing properties

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

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

Checking properties

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

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

Done checking properties

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

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

Generating plugin

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

 

 

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

Done generating plugin file/home/oracle/tts/xttplugin.txt

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

 

[oracle@testdb1 tts]$ more /home/oracle/tts/xttplugin.txt

impdp directory=<DATA_PUMP_DIR>logfile=<tts_imp.log> \

network_link=<ttslink>transport_full_check=no \

transport_tablespaces=XTTSTEST \

transport_datafiles=‘+DATA_SB/dest/datafile/xttstest_362_959887907‘

 

impdp "‘/ as sysdba‘" directory=dump_dirlogfile=tts_imp.log network_link=ttslink transport_full_check=notransport_tablespaces=XTTSTESTtransport_datafiles=‘+DATA_SB/dest/datafile/xttstest_362_959887907‘

 

SQL> create user xttstest identified by xttstest;

 

User created.

 

SQL> grant connect,resource to xttstest;

 

Grant succeeded.

 

SQL> exit

Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real ApplicationClusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testingoptions

[oracle@testdb1 tts]$ impdp "‘/ as sysdba‘"directory=dump_dir logfile=tts_imp.log network_link=ttslinktransport_full_check=no transport_tablespaces=XTTSTESTtransport_datafiles=‘+DATA_SB/dest/datafile/xttstest_362_959887907‘

 

Import: Release 11.2.0.4.0 - Production onMon Nov 13 17:32:25 2017

 

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

 

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real ApplicationClusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testingoptions

Starting"SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA"directory=dump_dir logfile=tts_imp.log network_link=ttslinktransport_full_check=no transport_tablespaces=XTTSTESTtransport_datafiles=+DATA_SB/dest/datafile/xttstest_362_959887907

Source time zone is +08:00 and target timezone is +00:00.

Processing object typeTRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object typeTRANSPORTABLE_EXPORT/TABLE

Processing object typeTRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job"SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completedat Mon Nov 13 17:32:51 2017 elapsed 0 00:00:25

 

4.5. 目标端表空间校验

 [oracle@testdb1tts]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production onMon Nov 13 17:34:22 2017

 

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, Real ApplicationClusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testingoptions

 

SQL> alter tablespace xttstest readwrite;

 

Tablespace altered.

 

SQL> conn xttstest/xttstest

Connected.

SQL> select table_name from user_tables;

 

TABLE_NAME

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

TEST

XTTS_OBJECTS

XTTS_INDEX

XTTS_TABLES

XTTS_JOB

 

SQL> select count(1) from test;

 

 COUNT(1)

----------

        4

 

SQL>

 

参考:

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=176801744221976&id=1902618.1&_afrWindowMode=0&_adf.ctrl-state=b39z5avbw_124


本文出自 “技术门徒” 博客,请务必保留此出处http://lizhhui.blog.51cto.com/3222144/1982243

利用DBMS_FILE_TRANSFER迁移数据库从AIX至Linux

标签:xtts dbms_file_transfer aix linux

人气教程排行