ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 10g transportable tablespace

Oracle 10g transportable tablespace

原创 Linux操作系统 作者:bournetai 时间:2011-09-05 15:38:45 0 删除 编辑

goal: transport tablespace of source to target cross platform.

source environment: 10.2.0.1 redhat 4 as 7 64 bit
target environment: 10.2.0.1 windows xp 32 bit


1.check os of source and target database:
os of source:
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
     FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d
     WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

  2    3 
PLATFORM_NAME
--------------------------------------------------------------------------------
ENDIAN_FORMAT
--------------
Linux 64-bit for AMD
Little


os of target:
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
  2       FROM V$TRANSPORTABLE_PLATFORM. tp, V$DATABASE d
  3       WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_NAME
----------------------------------------------------------------------------

ENDIAN_FORMAT
--------------
Microsoft Windows IA (32-bit)
Little

 


2.Pick a self-contained set of tablespaces on source:
SQL> execute dbms_tts.transport_set_check('bourne_tai',TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

no rows selected   --if result contains other tablespaces, translates them together.

 

3.Generate a transportable tablespace set on source:
ALTER TABLESPACE bourne_tai READ ONLY;

generate expdp file and copy it to target
expdp system/password DUMPFILE=bourne_tai.expdp DIRECTORY=DIRDP TRANSPORT_TABLESPACES = bourne_tai TRANSPORT_FULL_CHECK=Y

copy datafile to target
/opt/oracle/oradata/public1/bourne_tai.dbf

ALTER TABLESPACE bourne_tai READ WRITE;

 

4.convert platform. of datafile, also do it on source. but here do it on target.

RMAN> convert datafile
2> 'd:\bourne_tai.dbf'
3> to platform="Microsoft Windows IA (32-bit)"
4> from platform="Linux 64-bit for AMD"
5> db_file_name_convert="d:\","c:\";

启动 backup 于 05-9月 -11
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=138 devtype=DISK
通道 ORA_DISK_1: 启动数据文件转换
输出文件名=D:\BOURNE_TAI.DBF
已转换的数据文件 = C:\BOURNE_TAI.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:04
完成 backup 于 05-9月 -11

after issued this command. the datafile bourne_tai.dbf found in c:\ directory.

 

5.create bourne_tai tablespace and bourne_tai user on target:
SQL> create tablespace bourne_tai datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOCTOR\BOURNE_TAI.DBF' size 50M;


表空间已创建。

SQL> create user bourne_tai identified by password default tablespace bourne_tai;

用户已创建。

SQL> grant resource, connect to bourne_tai;

授权成功。

 

6.drop bourne_tai tablspace on target:
conn / as sysdba
drop tablespace bourne_tai but not user bourne_tai
SQL> drop tablespace bourne_tai including contents and datafiles;

表空间已删除。

use converted bourne_tai datafile to replace original bourne_tai datafile on target database.


7.import expdp backed up of source database, also including datafile:
impdp system/Oracle10g DUMPFILE=bourne_tai.expdp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=c:\BOURNE_TAI.DBF REMAP_SCHEMA=(bourne_tai:bourne_tai)

REMAP_TABLESPACE=(bourne_tai:bourne_tai)

TRANSPORT_DATAFILES parameter uses absolute path
the path of DIRECTORY DATA_PUMP_DIR is D:\oracle\product\10.2.0\admin\doctor\dpdump\


the detail like that:
C:\>impdp system/Oracle10g DUMPFILE=bourne_tai.expdp DIRECTORY=DATA_PUMP_DIR T
RANSPORT_DATAFILES=D:\oracle\product\10.2.0\oradata\DOCTOR\BOURNE_TAI.DBF REMAP_SCHEMA=(bourne_tai:bourne_tai)
REMAP_TABLESPACE=(bourne_tai:bourne_tai)

Import: Release 10.2.0.1.0 - Production on 星期一, 05 9月, 2011 15:04:36

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

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已成功加载/卸载了主表 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01"
启动 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** DUMPFILE=base_ying
shi.expdp DIRECTORY=DATA_PUMP_DIR TRANSPORT_DATAFILES=D:\oracle\product\10.2.0\oradata\DOCTOR\BOURNE_TAI.DBF REMAP_
SCHEMA=(bourne_tai:bourne_tai) REMAP_TABLESPACE=(bourne_tai:bourne_tai)

处理对象类型 TRANSPORTABLE_EXPORT/PLUGTS_BLK
处理对象类型 TRANSPORTABLE_EXPORT/TABLE
处理对象类型 TRANSPORTABLE_EXPORT/INDEX
处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
处理对象类型 TRANSPORTABLE_EXPORT/INDEX_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/COMMENT
处理对象类型 TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
处理对象类型 TRANSPORTABLE_EXPORT/TABLE_STATISTICS
处理对象类型 TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
作业 "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" 已于 15:06:13 成功完成

 

8.after impdp imported, check status of bourne_tai tablespace and tables of user bourne_tai and make tablespace bourne_tai read write.

SQL> select default_tablespace from dba_users where username='BOURNE_TAI';

DEFAULT_TABLESPACE
------------------------------
BOURNE_TAI

SQL> select file_name from dba_data_files where tablespace_name='
BOURNE_TAI';

FILE_NAME
--------------------------------------------------------------------------------

D:\ORACLE\PRODUCT\10.2.0\ORADATA\DOCTOR\
BOURNE_TAI.DBF

SQL> select count(*) from user_tables;

  COUNT(*)
----------
        54

SQL> alter tablespace bourne_tai read write;

表空间已更改。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26019288/viewspace-706675/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-07-06

  • 博文量
    6
  • 访问量
    20320