【TTS】传输表空间AIX asm -> linux asm基于rman

实验部分

实验环境介绍

实验目标

要实现将自定义的应用程序表空间app1tbs,app2tbs,idxtbs从源平台传递到目标平台，而在实际的工作过程中，需要将AIX上的数据库迁移到Linux，或者将Linux上的数据库迁移到AIX上，除了exp/imp和expdp/impdp外，最常用的就是传输表空间了，若是整个库迁移的话，我们需要做的就是把业务用户和业务表空间的数据迁移过来就行，Undo、temp、system等等的就不用迁移了，整个处理过程和本文档的处理过程大同小异，需要关注的是业务对象的个数、大小、状态等。

实验过程

环境准备

在源库上创建3个用户应用的表空间，并在相应的表空间创建表和索引

oracle@ZDMTRAIN2:/oracle$ echo $ORACLE_SID

ora11g

oracle@ZDMTRAIN2:/oracle$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sat Feb 18 10:51:00 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SYS@ora11g> select name from v$datafile;

NAME

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

+DATA1/ora11g/datafile/system.379.936264859

+DATA1/ora11g/datafile/sysaux.378.936264861

+DATA1/ora11g/datafile/undotbs1.380.936264861

+DATA1/ora11g/datafile/users.382.936264861

+DATA1/ora11g/datafile/example.391.936264979

SYS@ora11g> create tablespace app1tbs DATAFILE '+DATA1' size 10m;

Tablespace created.

SYS@ora11g> create tablespace app2tbs DATAFILE '+DATA1' size 10m;

Tablespace created.

SYS@ora11g> CREATE TABLESPACE IDXTBS DATAFILE '+DATA1' SIZE 10M;

Tablespace created.

SYS@ora11g> create user user_app1 identified by user_app1 default tablespace app1tbs;

User created.

SYS@ora11g> create user user_app2 identified by user_app2 default tablespace app2tbs;

User created.

SYS@ora11g> grant connect , resource to user_app1;

Grant succeeded.

SYS@ora11g> grant connect , resource to user_app2;

Grant succeeded.

SYS@ora11g> create table user_app1.app1_tab tablespace app1tbs as select * from scott.emp;

Table created.

SYS@ora11g> create table user_app2.app2_tab tablespace app2tbs as select * from scott.dept;

Table created.

SYS@ora11g> create index user_app1.idx_emp_ename on user_app1.app1_tab(ename) tablespace idxtbs;

Index created.

SYS@ora11g> create index user_app2.idx_dept_dname on user_app2.app2_tab(dname) tablespace idxtbs;

Index created.

SYS@ora11g> set line 9999 pagesize 9999

SYS@ora11g> SELECT a.NAME, b.NAME FROM v$tablespace a , v$datafile b WHERE a.TS#=b.TS# ;

NAME NAME

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

SYSTEM +DATA1/ora11g/datafile/system.379.936264859

SYSAUX +DATA1/ora11g/datafile/sysaux.378.936264861

UNDOTBS1 +DATA1/ora11g/datafile/undotbs1.380.936264861

USERS +DATA1/ora11g/datafile/users.382.936264861

EXAMPLE +DATA1/ora11g/datafile/example.391.936264979

APP1TBS +DATA1/ora11g/datafile/app1tbs.393.936269553

APP2TBS +DATA1/ora11g/datafile/app2tbs.394.936269559

IDXTBS +DATA1/ora11g/datafile/idxtbs.395.936269565

8 rows selected.

SYS@ora11g>

判断平台支持并确定字节序

如果传输表空间集到不同的平台，则要确定对于源和目标平台这种跨平台表空间被支持，也要确定每个平台的字节序，如果平台具有相同的字节序，则不需要进行转化，否则必须做一个表空间集转化，在源端或目标端都可以，在源端用convert tablespace，在目标端用convert datafile。

SYS@ora11g> col platform_name for a40

SYS@ora11g> select tp.platform_name, tp.endian_format

2 from v$transportable_platform tp

3 where tp.platform_name in ('Linux x86 64-bit', 'AIX-Based Systems (64-bit)');

PLATFORM_NAME ENDIAN_FORMAT

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

AIX-Based Systems (64-bit) Big

Linux x86 64-bit Little

SQL>

源平台和目标平台的Endian_format 不同，source端为Big，target端为Little，所以需要进行表空间集转换，前边说过在源端或目标端都可以进行转换，这里我们选择在目标端来进行转换。

选择自包含的表空间集

进行检查

Indicates whether a full or partial dependency check is required. If TRUE, treats all IN and OUT pointers(dependencies) and captures them as violations if they are not self-contained in the transportable set.

先试试要传输app1tbs和idxtbs这2个表空间：

SYS@ora11g> execute sys.dbms_tts.transport_set_check('app1tbs,idxtbs',true);

PL/SQL procedure successfully completed.

SQL> col violations for a70

SYS@ora11g> select * from sys.transport_set_violations;

VIOLATIONS

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

ORA-39907: Index USER_APP2.IDX_DEPT_DNAME in tablespace IDXTBS points

to table USER_APP2.APP2_TAB in tablespace APP2TBS.

SQL>

结论： 在idxtbs表空间中IDX_DEPT_DNAME索引指向了表空间集外的user_app2.APP2_TAB表，所以这里选择app1tabs,app2tabs,idxtbs作为新的表空间集再次进行检查

SYS@ora11g> execute sys.dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);

PL/SQL procedure successfully completed.

SYS@ora11g> select * from sys.transport_set_violations;

no rows selected

SYS@ora11g>

结论： 此时这个表空间集已经不再违背自包含的条件，可以确定为一个可传输表空间集。在实际生产环境中也是如此检查的，若是全库迁移，得把需要迁移的表空间修改为自包含的。

产生可传输表空间集

rman备份source库

当然，如果已经有全库备份了就可以省略这个步骤。

oracle@ZDMTRAIN2:/oracle$ mkdir -p /lxm/oracle_bk/ora11g

oracle@ZDMTRAIN2:/oracle$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Feb 18 11:27:16 2017

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

connected to target database: ORA11G (DBID=37497795)

RMAN> list backupset;

using target database control file instead of recovery catalog

specification does not match any backup in the repository

RMAN> backup as compressed backupset format '/lxm/oraclebk/ora11g/full%n%T%t_%s.bak' database include current controlfile plus archivelog delete input ;

Starting backup at 2017-02-18 11:27:32

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=196 device type=DISK

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=6 RECID=1 STAMP=936271653

channel ORA_DISK_1: starting piece 1 at 2017-02-18 11:27:34

channel ORA_DISK_1: finished piece 1 at 2017-02-18 11:27:37

piece handle=/lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271654_3.bak tag=TAG20170218T112734 comment=NONE

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

channel ORA_DISK_1: deleting archived log(s)

archived log file name=+DATA1/ora11g/archivelog/2017_02_18/thread_1_seq_6.396.936271653 RECID=1 STAMP=936271653

Finished backup at 2017-02-18 11:27:38

Starting backup at 2017-02-18 11:27:38

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=+DATA1/ora11g/datafile/system.379.936264859

input datafile file number=00002 name=+DATA1/ora11g/datafile/sysaux.378.936264861

input datafile file number=00005 name=+DATA1/ora11g/datafile/example.391.936264979

input datafile file number=00003 name=+DATA1/ora11g/datafile/undotbs1.380.936264861

input datafile file number=00006 name=+DATA1/ora11g/datafile/app1tbs.393.936269553

input datafile file number=00007 name=+DATA1/ora11g/datafile/app2tbs.394.936269559

input datafile file number=00008 name=+DATA1/ora11g/datafile/idxtbs.395.936269565

input datafile file number=00004 name=+DATA1/ora11g/datafile/users.382.936264861

channel ORA_DISK_1: starting piece 1 at 2017-02-18 11:27:38

channel ORA_DISK_1: finished piece 1 at 2017-02-18 11:28:53

piece handle=/lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271658_4.bak tag=TAG20170218T112738 comment=NONE

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

channel ORA_DISK_1: starting compressed full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 2017-02-18 11:28:55

channel ORA_DISK_1: finished piece 1 at 2017-02-18 11:28:56

piece handle=/lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271734_5.bak tag=TAG20170218T112738 comment=NONE

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

Finished backup at 2017-02-18 11:28:56

Starting backup at 2017-02-18 11:28:56

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting compressed archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=7 RECID=2 STAMP=936271736

channel ORA_DISK_1: starting piece 1 at 2017-02-18 11:28:57

channel ORA_DISK_1: finished piece 1 at 2017-02-18 11:28:58

piece handle=/lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271737_6.bak tag=TAG20170218T112856 comment=NONE

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

channel ORA_DISK_1: deleting archived log(s)

archived log file name=+DATA1/ora11g/archivelog/2017_02_18/thread_1_seq_7.396.936271737 RECID=2 STAMP=936271736

Finished backup at 2017-02-18 11:28:58

RMAN> list backupset;

List of Backup Sets

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

BS Key Size Device Type Elapsed Time Completion Time

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

2 8.32M DISK 00:00:03 2017-02-18 11:27:37

BP Key: 2 Status: AVAILABLE Compressed: YES Tag: TAG20170218T112734

Piece Name: /lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271654_3.bak

List of Archived Logs in backup set 2

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 6 1116417 2017-02-18 09:43:58 1131262 2017-02-18 11:27:32

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

3 Full 284.70M DISK 00:01:15 2017-02-18 11:28:53

BP Key: 3 Status: AVAILABLE Compressed: YES Tag: TAG20170218T112738

Piece Name: /lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271658_4.bak

List of Datafiles in backup set 3

File LV Type Ckp SCN Ckp Time Name

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

1 Full 1131273 2017-02-18 11:27:38 +DATA1/ora11g/datafile/system.379.936264859

2 Full 1131273 2017-02-18 11:27:38 +DATA1/ora11g/datafile/sysaux.378.936264861

3 Full 1131273 2017-02-18 11:27:38 +DATA1/ora11g/datafile/undotbs1.380.936264861

4 Full 1131273 2017-02-18 11:27:38 +DATA1/ora11g/datafile/users.382.936264861

5 Full 1131273 2017-02-18 11:27:38 +DATA1/ora11g/datafile/example.391.936264979

6 Full 1131273 2017-02-18 11:27:38 +DATA1/ora11g/datafile/app1tbs.393.936269553

7 Full 1131273 2017-02-18 11:27:38 +DATA1/ora11g/datafile/app2tbs.394.936269559

8 Full 1131273 2017-02-18 11:27:38 +DATA1/ora11g/datafile/idxtbs.395.936269565

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4 Full 1.03M DISK 00:00:01 2017-02-18 11:28:55

BP Key: 4 Status: AVAILABLE Compressed: YES Tag: TAG20170218T112738

Piece Name: /lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271734_5.bak

SPFILE Included: Modification time: 2017-02-18 11:25:43

SPFILE db_unique_name: ORA11G

Control File Included: Ckp SCN: 1131300 Ckp time: 2017-02-18 11:28:53

BS Key Size Device Type Elapsed Time Completion Time

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

5 4.00K DISK 00:00:00 2017-02-18 11:28:57

BP Key: 5 Status: AVAILABLE Compressed: YES Tag: TAG20170218T112856

Piece Name: /lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271737_6.bak

List of Archived Logs in backup set 5

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 7 1131262 2017-02-18 11:27:32 1131306 2017-02-18 11:28:56

RMAN>

RMAN>

transport tablespace 生成文件

该步骤需要注意的是磁盘剩余空间：如下的目录/lxm/ora11g/transportdest剩余空间必须大于source库整个表空间的大小+需要传输的表空间的大小，否则会因为磁盘空间不够而报错。

oracle@ZDMTRAIN2:/oracle$ mkdir -p /lxm/ora11g/transportdest

oracle@ZDMTRAIN2:/oracle$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Feb 18 11:30:34 2017

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

connected to target database: ORA11G (DBID=37497795)

RMAN> transport tablespace APP1TBS,APP2TBS,IDXTBS tablespace destination '/lxm/ora11g/transportdest' auxiliary destination '/lxm/ora11g/transportdest';

using target database control file instead of recovery catalog

RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace UNDOTBS1

Creating automatic instance, with SID='wBBm'

initialization parameters used for automatic instance:

db_name=ORA11G

db_unique_name=wBBm_tspitr_ORA11G

compatible=11.2.0.0.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/lxm/ora11g/transportdest

log_archive_dest_1='location=/lxm/ora11g/transportdest'

#No auxiliary parameter file used

starting up automatic instance ORA11G

Oracle instance started

Total System Global Area 292278272 bytes

Fixed Size 2220880 bytes

Variable Size 100666544 bytes

Database Buffers 184549376 bytes

Redo Buffers 4841472 bytes

Automatic instance created

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:

{

# set requested point in time

set until scn 1131306;

# restore the controlfile

restore clone controlfile;

# mount the controlfile

sql clone 'alter database mount clone database';

# archive current online log

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET until clause

Starting restore at 2017-02-18 11:31:02

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=80 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271734_5.bak

channel ORA_AUX_DISK_1: piece handle=/lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271734_5.bak tag=TAG20170218T112738

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/lxm/ora11g/transportdest/ORA11G/controlfile/o1_mfdbhhzqsd.ctl

Finished restore at 2017-02-18 11:31:04

sql statement: alter database mount clone database

sql statement: alter system archive log current

contents of Memory Script:

{

# set requested point in time

set until scn 1131306;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile 1 to new;

set newname for clone datafile 3 to new;

set newname for clone datafile 2 to new;

set newname for clone tempfile 1 to new;

set newname for datafile 6 to

"/lxm/ora11g/transportdest/o1_mfapp1tbs%u_.dbf";

set newname for datafile 7 to

"/lxm/ora11g/transportdest/o1_mfapp2tbs%u_.dbf";

set newname for datafile 8 to

"/lxm/ora11g/transportdest/o1_mfidxtbs%u_.dbf";

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile 1, 3, 2, 6, 7, 8;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /lxm/ora11g/transportdest/ORA11G/datafile/o1_mftemp%u_.tmp in control file

Starting restore at 2017-02-18 11:31:10

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /lxm/ora11g/transportdest/ORA11G/datafile/o1_mfsystem%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /lxm/ora11g/transportdest/ORA11G/datafile/o1_mfundotbs1%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /lxm/ora11g/transportdest/ORA11G/datafile/o1_mfsysaux%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00006 to /lxm/ora11g/transportdest/o1_mfapp1tbs%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00007 to /lxm/ora11g/transportdest/o1_mfapp2tbs%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00008 to /lxm/ora11g/transportdest/o1_mfidxtbs%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271658_4.bak

channel ORA_AUX_DISK_1: piece handle=/lxm/oracle_bk/ora11g/full_ORA11Gxx_20170218_936271658_4.bak tag=TAG20170218T112738

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25

Finished restore at 2017-02-18 11:32:42

datafile 1 switched to datafile copy

input datafile copy RECID=8 STAMP=936271962 file name=/lxm/ora11g/transportdest/ORA11G/datafile/o1_mf_systemdbhj055o.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=9 STAMP=936271962 file name=/lxm/ora11g/transportdest/ORA11G/datafile/o1_mf_undotbs1dbhj058d.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=10 STAMP=936271962 file name=/lxm/ora11g/transportdest/ORA11G/datafile/o1_mf_sysauxdbhj055q.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=11 STAMP=936271962 file name=/lxm/ora11g/transportdest/o1_mf_app1tbsdbhj05ll.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=12 STAMP=936271962 file name=/lxm/ora11g/transportdest/o1_mf_app2tbsdbhj05nz.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=13 STAMP=936271962 file name=/lxm/ora11g/transportdest/o1_mf_idxtbsdbhj05o8.dbf

contents of Memory Script:

{

# set requested point in time

set until scn 1131306;

# online the datafiles restored or switched

sql clone "alter database datafile 1 online";

sql clone "alter database datafile 3 online";

sql clone "alter database datafile 2 online";

sql clone "alter database datafile 6 online";

sql clone "alter database datafile 7 online";

sql clone "alter database datafile 8 online";

# recover and open resetlogs

recover clone database tablespace "APP1TBS", "APP2TBS", "IDXTBS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile 1 online

sql statement: alter database datafile 3 online

后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！

验证码：

获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。