合 【TTS】传输表空间 Linux和AIX 之间传输数据
【TTS】传输表空间AIX asm -> linux asm基于rman
实验部分
实验环境介绍
项目 | source db | target db |
---|---|---|
db 类型 | 单实例 | 单实例 |
db version | 11.2.0.3 | 11.2.0.3 |
db 存储 | ASM | ASM |
ORACLE_SID | ora11g | orclasm |
db_name | ora11g | orclasm |
主机IP地址: | 22.188.139.33 | 192.168.59.30 |
OS版本及kernel版本 | AIX 64位 5.3.0.0 | RHEL6.5 64位,2.6.32-504.16.2.el6.x86_64 |
OS hostname | ZFXDESKDB2 | rhel6_lhr |
platform_name | AIX-Based Systems (64-bit) | Linux x86 64-bit |
db time zone | 14 | 14 |
字符集 | ZHS16GBK | ZHS16GBK |
compatible | 11.2.0.0.0 | 11.2.0.0.0 |
归档模式 | Archive Mode | Archive Mode |
实验目标
要实现将自定义的应用程序表空间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