`

Oracle 传输表空间(Transportable Tablespaces) 示例(一) -- 跨操作系统迁移表空间 (endianness 格式相同)

 
阅读更多

有关跨操作系统迁移的相关说明,之前的Blog有说明:

Oracle 跨操作系统 迁移 说明

http://blog.csdn.net/tianlesoftware/article/details/7252788

Oracle Transportable TableSpace(TTS) 传输表空间 说明

http://blog.csdn.net/tianlesoftware/article/details/7267582

Oracle expdp impdp 使用示例

http://blog.csdn.net/tianlesoftware/article/details/6260138

这篇演示endianness格式相同的跨操作系统的迁移,测试环境如下:

Source 端:

操作系统: OracleLinux 6.1 32位

endianness格式: little

数据库版本:11.2.0.3

Target 端:

操作系统:OracleLinux 6.1 64位

endianness 格式: little

数据库版本:11.2.0.3

1.查看endianness格式

查看endianness 格式,可以使用如下命令:

SQL> col platform_name for a40

SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

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

6 AIX-Based Systems (64-bit) Big

16 Apple Mac OS Big

21 Apple Mac OS (x86-64) Little

19 HP IA Open VMS Little

15 HP Open VMS Little

5 HP Tru64 UNIX Little

3 HP-UX (64-bit) Big

4 HP-UX IA (64-bit) Big

18 IBM Power Based Linux Big

9 IBM zSeries Based Linux Big

10 Linux IA (32-bit) Little

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

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

11 Linux IA (64-bit) Little

13 Linux x86 64-bit Little

7 Microsoft Windows IA (32-bit) Little

8 Microsoft Windows IA (64-bit) Little

12 Microsoft Windows x86 64-bit Little

17 Solaris Operating System (x86) Little

20 Solaris Operating System (x86-64) Little

1 Solaris[tm] OE (32-bit) Big

2 Solaris[tm] OE (64-bit) Big

20 rows selected.

从上面的查询结果来看,基本都是little 类型的endianness,

或者:

SQL> SELECT d.PLATFORM_NAME,ENDIAN_FORMAT

2 FROM V$TRANSPORTABLE_PLATFORMtp, V$DATABASE d

3 WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT

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

Linux IA (32-bit) Little

SQL> SELECT d.PLATFORM_NAME,ENDIAN_FORMAT

2 FROM V$TRANSPORTABLE_PLATFORMtp, V$DATABASE d

3 WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;

PLATFORM_NAME ENDIAN_FORMAT

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

Linux x86 64-bit Little

2. Source上创建测试表空间

这里创建表空间,并在该表空间上创建几个自包含的对象,即对象的内容都在这个表空间里,和其他表空间没有关联。

SQL> select tablespace_name,status fromdba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

SQL> select file_name fromdba_data_files;

FILE_NAME

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

/u01/app/oracle/oradata/anqing/users01.dbf

/u01/app/oracle/oradata/anqing/undotbs01.dbf

/u01/app/oracle/oradata/anqing/sysaux01.dbf

/u01/app/oracle/oradata/anqing/system01.dbf

--创建表空间

SQL> create tablespace anqing datafile '/u01/app/oracle/oradata/anqing/anqing01.dbf'size 50M;

Tablespace created.

--创建用户,并指定表空间

SQL> create user anqing identified byanqing default tablespace anqing temporary tablespace temp;

User created.

SQL> grant connect,resource to anqing;

Grant succeeded.

--创建表:

SQL> conn anqing/anqing;

Connected.

SQL> create table anqing as select * fromall_objects;

Table created.

3.在两端创建backup 的目录

使用数据泵之前,需要创建一个存放文件的目录。 这个目录要写入Oracle的数据字典中才能识别。

SQL> create directory backup as'/u01/backup';

Directory created.

SQL> col owner format a5

SQL> col directory_name format a25

SQL> select * from dba_directories;

4. 检查表空间自包含

SQL> execute dbms_tts.transport_set_check('anqing', TRUE);

PL/SQL procedure successfully completed.

--查看自包含验证结果:

SQL> select * fromtransport_set_violations;

no rows selected

--如果不符合规定的,这里会显示的相关的信息。比如:

SQL> select * fromtransport_set_violations;

VIOLATIONS

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

ORA-39917: 可插入集内不允许使用 SYS 拥有的对象 ANQING (在表空间 ANQING 中)

5. 将表空间设置成read­-only

SQL>alter tablespace anqing read only;

Tablespacealtered.

SQL>select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

ANQING READ ONLY

6 rowsselected.

6. 生成:TransportableTablespace Set

一个完成的Transportable tablespace set 由2部分组成,expdp 导出的表空间的metadata,还有就是表空间对应的数据文件。

[oracle@tianlesoftware u01]$ expdp dumpfile=anqing.dmp directory=backup transport_tablespaces=anqing transport_full_check=y logfile=anqing.log

Export: Release 11.2.0.3.0 - Production onMon Feb 27 17:44:32 2012

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

Username: / as sysdba

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Starting"SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA dumpfile=anqing.dmpdirectory=backup transport_tablespaces=anqing transport_full_check=ylogfile=anqing.log

Processing object typeTRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object typeTRANSPORTABLE_EXPORT/TABLE

Processing object typeTRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Master table"SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfullyloaded/unloaded

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

Dump file set forSYS.SYS_EXPORT_TRANSPORTABLE_01 is:

/u01/backup/anqing.dmp

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

Datafiles required for transportabletablespace ANQING:

/u01/app/oracle/oradata/anqing/anqing01.dbf

Job"SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completedat 17:45:41

[oracle@tianlesoftware u01]$

7.将Transportable Tablespace set 传送到Target端

(1)将表空间anqing 对应的数据文件copy到Target 对应的ORADATA目录下。

(2)将expdp 导出的表空间metadta 数据copy 到Target 端的backup 目录下

[oracle@tianlesoftware backup]$ scp anqing.dmp 192.168.3.201:/u01/backup

oracle@192.168.3.201's password:

anqing.dmp 100% 104KB 104.0KB/s 00:00

[oracle@tianlesoftware anqing]$ scp anqing01.dbf 192.168.3.201:/u02/app/oracle/oradata/anqing

oracle@192.168.3.201's password:

anqing01.dbf 100% 50MB16.7MB/s 00:03

8.在Target 系统上Import 表空间的metadata

--这里创建一个用户,然后remap_schema:

SQL> create user dave identified bydave;

User created.

SQL> grant connect,resource to dave;

Grant succeeded.

rac1:/u02/app/oracle/oradata/anqing>impdp directory=backup dumpfile=anqing.dmp transport_datafiles=/u02/app/oracle/oradata/anqing/anqing01.dbf remap_schema=anqing:dave logfile=anqing.log

Import: Release 11.2.0.3.0 - Production onMon Feb 27 17:51:49 2012

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

Username: / as sysdba

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Master table"SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfullyloaded/unloaded

Starting"SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=backupdumpfile=anqing.dmptransport_datafiles=/u02/app/oracle/oradata/anqing/anqing01.dbfremap_schema=anqing:dave logfile=anqing.log

Processing object typeTRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object typeTRANSPORTABLE_EXPORT/TABLE

Processing object typeTRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job"SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completedat 17:52:18

注意:

(1)这里transport_datafiles 写的是datafile的全路径, 也可以用transport_datafiles来对数据文件进行重命名和移动位置。

(2)如果文件很多,也可以写入个配置文件里。 导入时通过PARFILE参数来指定。

9.查看并修改表空间状态

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

ANQING READ ONLY

6 rows selected.

至此,Source 和 Target 端都是read only 状态,修改2个表空间的状态:

SQL> alter tablespace anqing read write;

Tablespace altered.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

ANQING ONLINE

6 rows selected.

10. 验证TTS

在Target 端查询我们之前创建的表:

SQL> conn dave/dave;

Connected.

SQL> select count(*) from anqing;

COUNT(*)

----------

56281

数据已经迁移过来,TTS 操作完成。

小结:

1.我们之前的表空间对应的是anqing 用户,在TTS 过程中,我们remap 到了dave 用户下。

2.DB 版本相同,time zone version 相同,如果time zone 不同,就会遇到ORA-39322的错误。

Oracle TTSORA-39322: Cannot use transportable tablespace with different timezone version 说明

http://blog.csdn.net/tianlesoftware/article/details/7298547

3.虽然这里测试的是32位linux 到64位linux,但是他们对应的endianness值相同,所以这里不需要进行endianness的转换。

MOS 上的相关链接:

10g : Transportable Tablespaces AcrossDifferent Platforms [ID 243304.1]

How to Move Tablespaces Across PlatformsUsing Transportable Tablespaces With RMAN [ID 371556.1]


PS:

原先设计的是windows 64到linux 64的TTS,结果2端time zoneversion 不一致,导致imp 报错,本本上暂时也没有64位的11.2.0.3的安装介质,下载又巨慢,4.7G。 就改成linux 32到linux 64了。

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

版权所有,文章允许转载,但必须以链接方式注明源地址,否则追究法律责任!

Skype: tianlesoftware

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系,否则拒绝申请----

DBA1 群:62697716(满); DBA2 群:62697977(满)DBA3 群:62697850(满)

DBA 超级群:63306533(满); DBA4 群:83829929 DBA5群: 142216823

DBA6 群:158654907 DBA7 群:172855474 DBA总群:104207940

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics