合 【TTS】传输表空间 Linux和AIX 之间传输数据
【TTS】传输表空间AIX asm -> linux asm
实验部分
实验环境介绍
项目 | 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等等的就不用迁移了,整个处理过程和本文档的处理过程大同小异,需要关注的是业务对象的个数、大小、状态等。
实验过程
source端环境准备
在源库上创建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.
execute sys.dbms_tts.transport_set_check('app1tbs,app2tbs,idxtbs',true);
select * from sys.transport_set_violations;
先试试要传输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>
结论: 此时这个表空间集已经不在违背自包含的条件,可以确定为一个可传输表空间集
产生可传输表空间集
使自包含的表空间集中的所有表空间变为只读状态
SYS@ora11g> alter tablespace app1tbs read only;
Tablespace altered.
SYS@ora11g> alter tablespace app2tbs read only;
Tablespace altered.
SYS@ora11g> alter tablespace idxtbs read only;
Tablespace altered.
SYS@ora11g> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------ ---------------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE