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

实验部分

实验环境介绍

实验目标

要实现将自定义的应用程序表空间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

APP1TBS READ ONLY

APP2TBS READ ONLY

IDXTBS READ ONLY

9 rows selected.

SYS@ora11g>

使用数据泵导出工具，导出要传输的各个表空间的元数据

确定导出目录

SYS@ora11g> set line 9999

SYS@ora11g> col directory_name for a28

SYS@ora11g> col directory_path for a100

SYS@ora11g> select directory_name,directory_path from dba_directories;

DIRECTORY_NAME DIRECTORY_PATH

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

SUBDIR /oracle/app/oracle/product/11.2.0/db/demo/schema/order_entry//2002/Sep

SS_OE_XMLDIR /oracle/app/oracle/product/11.2.0/db/demo/schema/order_entry/

LOG_FILE_DIR /oracle/app/oracle/product/11.2.0/db/demo/schema/log/

MEDIA_DIR /oracle/app/oracle/product/11.2.0/db/demo/schema/product_media/

XMLDIR /oracle/app/oracle/product/11.2.0/db/rdbms/xml

DATA_FILE_DIR /oracle/app/oracle/product/11.2.0/db/demo/schema/sales_history/

DATA_PUMP_DIR /oracle/app/oracle/admin/ora11g/dpdump/

ORACLE_OCM_CONFIG_DIR /oracle/app/oracle/product/11.2.0/db/ccr/state

8 rows selected.

SYS@ora11g>

开始导出

oracle@ZDMTRAIN2:/oracle$ expdp \'/ as sysdba \' dumpfile=expdat_tts_read_20160203.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=expdat_20160201.log

Export: Release 11.2.0.3.0 - Production on Sat Feb 18 11:03:07 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. 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

Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": "/******** AS SYSDBA" dumpfile=expdat_tts_read_20160203.dmp directory=DATA_PUMP_DIR transport_tablespaces=app1tbs,app2tbs,idxtbs logfile=expdat_20160201.log

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX

Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:

/oracle/app/oracle/admin/ora11g/dpdump/expdat_tts_read_20160203.dmp

******************************************************************************

Datafiles required for transportable tablespace APP1TBS:

+DATA1/ora11g/datafile/app1tbs.393.936269553

Datafiles required for transportable tablespace APP2TBS:

+DATA1/ora11g/datafile/app2tbs.394.936269559

Datafiles required for transportable tablespace IDXTBS:

+DATA1/ora11g/datafile/idxtbs.395.936269565

Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 11:04:49

oracle@ZDMTRAIN2:/oracle$

查看文件，使用asmcmd中copy命令将数据文件copy到文件系统，需要给目录赋予权限：

root@ZDMTRAIN2:/# chmod 777 /oracle/app/oracle/admin/ora11g/dpdump

root@ZDMTRAIN2:/oracle/app/oracle/admin/ora11g/dpdump# l

total 224

-rw-r--r-- 1 oracle asmadmin 1597 Feb 18 11:04 expdat_20160201.log

-rw-r----- 1 oracle asmadmin 110592 Feb 18 11:04 expdat_tts_read_20160203.dmp

root@ZDMTRAIN2:/oracle/app/oracle/admin/ora11g/dpdump# su - grid

grid@ZDMTRAIN2:/home/grid$ asmcmd

ASMCMD> cd +DATA1/ora11g/datafile/

ASMCMD> ls -lt

Type Redund Striped Time Sys Name

DATAFILE UNPROT COARSE FEB 18 10:00:00 Y UNDOTBS1.380.936264861

DATAFILE UNPROT COARSE FEB 18 10:00:00 Y SYSAUX.378.936264861

DATAFILE UNPROT COARSE FEB 18 10:00:00 Y IDXTBS.395.936269565

DATAFILE UNPROT COARSE FEB 18 10:00:00 Y APP2TBS.394.936269559

DATAFILE UNPROT COARSE FEB 18 10:00:00 Y APP1TBS.393.936269553

DATAFILE UNPROT COARSE FEB 18 09:00:00 Y USERS.382.936264861

DATAFILE UNPROT COARSE FEB 18 09:00:00 Y SYSTEM.379.936264859

DATAFILE UNPROT COARSE FEB 18 09:00:00 Y EXAMPLE.391.936264979

ASMCMD> cp IDXTBS.395.936269565 /oracle/app/oracle/admin/ora11g/dpdump

copying +DATA1/ora11g/datafile/IDXTBS.395.936269565 -> /oracle/app/oracle/admin/ora11g/dpdump/IDXTBS.395.936269565

ASMCMD> cp APP2TBS.394.936269559 /oracle/app/oracle/admin/ora11g/dpdump

copying +DATA1/ora11g/datafile/APP2TBS.394.936269559 -> /oracle/app/oracle/admin/ora11g/dpdump/APP2TBS.394.936269559

ASMCMD> cp APP1TBS.393.936269553 /oracle/app/oracle/admin/ora11g/dpdump

copying +DATA1/ora11g/datafile/APP1TBS.393.936269553 -> /oracle/app/oracle/admin/ora11g/dpdump/APP1TBS.393.936269553

ASMCMD>

还原源库中的表空间为读/写模式

SYS@ora11g> alter tablespace app1tbs read write;

Tablespace altered.

SYS@ora11g> alter tablespace app2tbs read write;

Tablespace altered.

SYS@ora11g> alter tablespace idxtbs read write;

Tablespace altered.

SYS@ora11g> col tablespace_name format a20

SYS@ora11g> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

EXAMPLE ONLINE

APP1TBS ONLINE

APP2TBS ONLINE

IDXTBS ONLINE

9 rows selected.

SYS@ora11g>

至此，已和源库没有任何关系。

传输文件

这里需要传输转储元文件和数据文件到目标库

查看目标库数据文件位置和导入目录

[oracle@rhel6 ~]$ export ORACLE_SID=orclasm

[oracle@rhel6_lhr dpdump]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期三 2月 3 20:17:58 2016

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

连接到:

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

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

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

验证码：

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