RUI个人博客 首页>>Oracle Basic Knowledge>>[原]Oracle跨平台传输表空间相关概念和实验

[原]Oracle跨平台传输表空间相关概念和实验

跨平台传输表空间

1.Compatibility Considerations for Transportable Tablespaces

传输表空间兼容性考虑

Transporting Tablespaces Between Database:

These tasks for transporting a tablespace are illustrated more fully in the example that follows, where it is assumed the following datafiles and tablespaces exist:
下面的例子就是为阐明更多的更丰富的例子来传输表空间,假设下面的数据文件和表空间存在。
//传输的表空间是TTS_1 TTS_2  
  TTS_IDX为测试的索引表空间
  
col file_name for a45
select FILE_NAME,FILE_ID,TABLESPACE_NAME,STATUS from dba_data_files where tablespace_name like 'TTS%';

FILE_NAME                                        FILE_ID TABLESPACE_NAME                STATUS
--------------------------------------------- ---------- ------------------------------ ---------
/u01/app/oracle/oradata/ORA11G/tts_1.dbf               7 TTS_1                          AVAILABLE
/u01/app/oracle/oradata/ORA11G/tts_2.dbf               8 TTS_2                          AVAILABLE
/u01/app/oracle/oradata/ORA11G/tts_idx.dbf             9 TTS_IDX                        AVAILABLE
Task1: Determine if Platforms are Supported and Determine Endianness
确定平台是否支持和确定字节序(Big or Small)
This task is only necessary if you are transporting the tablespace set to a platform different from the source platform.

If you are transporting the tablespace set to a platform different from the source platform, then determine if cross-platform tablespace transport is supported for both the source and destination platforms, and determine the endianness of each platform. If both platforms have the same endianness, no conversion is necessary. Otherwise you must do a conversion of the tablespace set either at the source or destination database.

If you are transporting sales_1 and sales_2 to a different platform, you can execute the following query on each platform. If the query returns a row, the platform supports cross-platform tablespace transport.

单词:cross-platform: 跨平台
          destination      目的地
          endianness       字节序

1.1 查询所有的传输平台字节序:
select * from v$transportable_platform order by 3;

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
----------- ---------------------------------------- --------------
          3 HP-UX (64-bit)                           Big
          6 AIX-Based Systems (64-bit)               Big
         18 IBM Power Based Linux                    Big
          2 Solaris[tm] OE (64-bit)                  Big
          4 HP-UX IA (64-bit)                        Big
         16 Apple Mac OS                             Big
          1 Solaris[tm] OE (32-bit)                  Big
          9 IBM zSeries Based Linux                  Big
         17 Solaris Operating System (x86)           Little
         19 HP IA Open VMS                           Little
         20 Solaris Operating System (x86-64)        Little
         12 Microsoft Windows x86 64-bit             Little
         13 Linux x86 64-bit                         Little
          8 Microsoft Windows IA (64-bit)            Little
         21 Apple Mac OS (x86-64)                    Little
         11 Linux IA (64-bit)                        Little
          5 HP Tru64 UNIX                            Little
         10 Linux IA (32-bit)                        Little
          7 Microsoft Windows IA (32-bit)            Little
         15 HP Open VMS                              Little

20 rows selected.


1.2 查询当前平台的字节序:
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
      FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
      WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME                            ENDIAN_FORMAT
---------------------------------------- --------------
Linux x86 64-bit                         Little

1.3 创建传输的测试表空间以及相关测试表
SYS@ORA11G>create tablespace tts_1 datafile '/u01/app/oracle/oradata/ORA11G/tts_1.dbf' size 10m;

Tablespace created.

SYS@ORA11G>create tablespace tts_2 datafile '/u01/app/oracle/oradata/ORA11G/tts_2.dbf' size 10m;

Tablespace created.

SYS@ORA11G>create tablespace tts_idx datafile '/u01/app/oracle/oradata/ORA11G/tts_idx.dbf' size 10m;

Tablespace created.

SYS@ORA11G>col name for a45
SYS@ORA11G>select FILE#,TS#,STATUS,NAME from v$datafile;

     FILE#        TS# STATUS  NAME
---------- ---------- ------- ---------------------------------------------
         1          0 SYSTEM  /u01/app/oracle/oradata/ORA11G/system01.dbf
         2          1 ONLINE  /u01/app/oracle/oradata/ORA11G/sysaux01.dbf
         3          2 ONLINE  /u01/app/oracle/oradata/ORA11G/undotbs01.dbf
         4          4 ONLINE  /u01/app/oracle/oradata/ORA11G/users01.dbf
         5          6 ONLINE  /u01/app/oracle/oradata/ORA11G/example01.dbf
         6         11 ONLINE  /u01/app/oracle/oradata/ORA11G/re_tyger.dbf
         7         14 ONLINE  /u01/app/oracle/oradata/ORA11G/tts_1.dbf
         8         15 ONLINE  /u01/app/oracle/oradata/ORA11G/tts_2.dbf
         9         16 ONLINE  /u01/app/oracle/oradata/ORA11G/tts_idx.dbf

9 rows selected.
1.4 创建源数据库中的测试用户 tyger (目标的测试用户为tom)
SYS@ORA11G>create user tyger identified by oracle account unlock;

User created.

SYS@ORA11G>grant connect,resource to tyger;

Grant succeeded.

SYS@ORA11G>create table tyger.tts_table tablespace tts_1 as select * from dba_objects;

Table created.

SYS@ORA11G>create table tyger.tts_table2 tablespace tts_2 as select * from dba_users;

Table created.
1.5 在测试表上创建索引,将索引放到非传输的表空间上
SYS@ORA11G>create index tyger.idx_object_id on tyger.tts_table(OBJECT_ID) tablespace tts_idx; 

Index created.


SYS@ORA11G>col segment_name for a30
SYS@ORA11G>l
  1* select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where SEGMENT_NAME in ('TTS_TABLE','TTS_TABLE2','IDX_OBJECT_ID')
SYS@ORA11G>/

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------------------ ------------------ ------------------------------
TYGER                          IDX_OBJECT_ID                  INDEX              TTS_IDX
TYGER                          TTS_TABLE                      TABLE              TTS_1
TYGER                          TTS_TABLE2                     TABLE              TTS_2


Task 2: Pick a Self-Contained Set of Tablespaces
选择自包含的表空间
There may be logical or physical dependencies between objects in the transportable set and those outside of the set. You can only transport a set of tablespaces that is self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. Some examples of self contained tablespace violations are:

An index inside the set of tablespaces is for a table outside of the set of tablespaces.
一个表上的索引创建在其他的表空间上
Note:
It is not a violation if a corresponding index for a table is outside of the set of tablespaces.

单词:violations  违反规则的

2.1 检测表空间TTS_1和TTS_2 是否是自包含,而且考虑了参考完整性限制(用TRUE控制)
SYS@ORA11G>EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('TTS_1,TTS_2', TRUE);

PL/SQL procedure successfully completed.
2.2 调用了PL/SQL程序包后,可以通过检索视图:TRANSPORT_SET_VIOLATIONS检测所有的违反规则的情况
SYS@ORA11G>SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected
Task 3: Generate a Transportable Tablespace Set
生成传输表空间集
After ensuring you have a self-contained set of tablespaces that you want to transport, generate a transportable tablespace set by completing the following steps:

3.1 Start SQL*Plus and connect to the database as an administrator or as a user who has either the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.
3.2 Make all tablespaces in the set read-only.
SYS@ORA11G>alter tablespace tts_1 read only;

Tablespace altered.

SYS@ORA11G>alter tablespace TTS_2 read only;

Tablespace altered.
3.3 查看DATA_PUMP_DIR目录的路径
SYS@ORA11G>select * from dba_directories where DIRECTORY_NAME ='DATA_PUMP_DIR';

OWNER                          DIRECTORY_NAME
------------------------------ ------------------------------
DIRECTORY_PATH
------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                            DATA_PUMP_DIR
/u01/app/oracle/admin/ORA11G/dpdump/


SYS@ORA11G>!
[oracle@ora11g64 ~]$ cd /u01/app/oracle/admin/ORA11G/dpdump/

[oracle@ora11g64 dpdump]$ ll
total 0

3.4 使用expdp工具以system用户将表空间tts_1和tts_2 导出
[oracle@ora11g64 dpdump]$ expdp system/oracle DUMPFILE=expdp_tts_1_2.dmp DIRECTORY=data_pump_dir TRANSPORT_TABLESPACES=tts_1,tts_2 logfile=tts_expdp.log

Export: Release 11.2.0.1.0 - Production on Fri Sep 18 10:14:13 2015

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

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** DUMPFILE=expdp_tts_1_2.dmp DIRECTORY=data_pump_dir TRANSPORT_TABLESPACES=tts_1,tts_2 logfile=tts_expdp.log 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/ORA11G/dpdump/expdp_tts_1_2.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS_1:
  /u01/app/oracle/oradata/ORA11G/tts_1.dbf
Datafiles required for transportable tablespace TTS_2:
  /u01/app/oracle/oradata/ORA11G/tts_2.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 10:15:12

[oracle@ora11g64 dpdump]$ ll
total 124
-rw-r----- 1 oracle oinstall 118784 Sep 18 10:15 expdp_tts_1_2.dmp
-rw-r--r-- 1 oracle oinstall   1300 Sep 18 10:15 tts_expdp.log
测试:这里测试了使用参数transport_full_check=y 进行导出:
报错:ORA-39907: Index TYGER.IDX_OBJECT_ID in tablespace TTS_IDX points to table TYGER.TTS_TABLE in tablespace TTS_1.
由于索引在表空间TTS_IDX上,不在本次传输的表空间之中
参数解释:TRANSPORT_FULL_CHECK  Verify storage segments of all tables [N]. 验证表的所有存储字段

[oracle@ora11g64 dpdump]$ expdp system/oracle DUMPFILE=expdp_tts_1_2_2.dmp DIRECTORY=data_pump_dir TRANSPORT_TABLESPAC[oracle@ora11g64 dpdump]$ expdp system/oracle DUMPFILE=expdp_tts_1_2_2.dmp DIRECTORY=data_pump_dir TRANSPORT_TABLESPACES=tts_1,tts_2 transport_full_check=y logfile=tts_expdp.log

Export: Release 11.2.0.1.0 - Production on Fri Sep 18 10:17:44 2015

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

UDE-28002: operation generated ORACLE error 28002
ORA-28002: the password will expire within 7 days

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** DUMPFILE=expdp_tts_1_2_2.dmp DIRECTORY=data_pump_dir TRANSPORT_TABLESPACES=tts_1,tts_2 transport_full_check=y logfile=tts_expdp.log 
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39187: The transportable set is not self-contained, violation list is

ORA-39907: Index TYGER.IDX_OBJECT_ID in tablespace TTS_IDX points to table TYGER.TTS_TABLE in tablespace TTS_1.
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 10:17:51

注:如果是不同的字节序的平台进行不同平台的迁移,需要使用rman工具进行相关的文件转换:
If sales_1 and sales_2 are being transported to a different platform, and the endianness of the platforms is different, and if you want to convert before transporting the tablespace set, then convert the datafiles composing the sales_1 and sales_2 tablespaces:
类似相关命令:
RMAN> CONVERT TABLESPACE sales_1,sales_2 
2> TO PLATFORM 'Microsoft Windows IA (32-bit)'
3> FORMAT '/tmp/%U';

Task 4: (Optional) Restore Tablespaces to Read/Write Mode

Make the transported tablespaces read/write again at the source database, as follows:

SYS@ORA11G>ALTER TABLESPACE tts_1 READ WRITE;

Tablespace altered.

SYS@ORA11G>ALTER TABLESPACE tts_2 READ WRITE;

Tablespace altered.

目的平台:
1.1 查找目标数据库平台的情况
SQL> col platform_name for a30
SQL> l
  1  SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
  2       FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
  3*      WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME
SQL> /

PLATFORM_NAME                  ENDIAN_FORMAT
------------------------------ ----------------------------
Microsoft Windows x86 64-bit   Little
1.2 创建传输测试用户tom
SQL> create user tom identified by orale account unlock;

User created.

SQL> grant connect,resource to tom;

Grant succeeded.

SQL> col directory_name for a30
SQL> col directory_path for a45
SQL> l
  1* select * from dba_directories where DIRECTORY_NAME ='DATA_PUMP_DIR'
SQL> /

OWNER
------------------------------------------------------------
DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ---------------------------------------------
SYS
DATA_PUMP_DIR                  O:\oracle\product\10.2.0/admin/orcl/dpdump/
将源数据库中的导出文件拷贝的该目录下 O:\oracle\product\10.2.0/admin/orcl/dpdump/

执行导入:
C:\Users\administer>impdp system/oracle dumpfile=expdp_tts_1_2.dmp directory=data_pump_dir transport_datafiles=O:\oracle\product\10.2.0\oradata\ORCL\tts_1.dbf,
 O:\oracle\product\10.2.0\oradata\ORCL\tts_2.dbf remap_schema=tyger:tom logfile=tts_import.log

Import: Release 10.2.0.4.0 - 64bit Production on 星期五, 18 9月, 2015 10:38:14

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 3.1 in dump file "O:\oracle\product\10.2.0/admin/orcl/dpdump/expdp_tts_1_2.dmp"

这里报错是由于:
我从高版本 11g 导入到10g 中 
ORA-39142: incompatible version number string in dump file "string"
Cause: A dump file was specified for an import operation whose version number is incompatible with the dump file version of the Data Pump product currently running on the system. Usually this message indicates that the dump file was produced by a newer version of the Data Pump export utility.
Action: Import this dump file using the Data Pump import utility with the same version as the export which created the file.

正常导入后,进行验证。。。。


      tyger.wang

     2015/09/18     于北京



版权声明:本文为博主原创文章,未经博主允许不得转载。




昵  称:
邮  箱:
评论内容:
验 证 码:
可用[code][/code]插入代码
点击刷新验证码