sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle传输表空间 OTTS跨平台传输表空间的详细步骤

时间:2016-12-24 21:33   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于OTTS Oracle传输表空间的使用笔记,详细记录了linux系统下的Oracle表空间传输到solaris系统的详细步骤。

source os:

1 [oracle@Silent ~]$ more /etc/redhat-release
2 Red Hat Enterprise Linux Server release 5.3 (Tikanga) 64bit
3
4 SQL> select * from v$version;
5
6 BANNER
7 --------------------------------------------------------------------------------
8 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
9 PL/SQL Release 11.2.0.1.0 - Production
10 CORE 11.2.0.1.0 Production
11 TNS for Linux: Version 11.2.0.1.0 - Production
12 NLSRTL Version 11.2.0.1.0 - Production

target os:

1 bash-3.2$ more /etc/release
2 Oracle Solaris 10 8/11 s10x_u10wos_17b X86 64bit
3 Copyright (c) 1983, 2011, Oracle and/or its affiliates. All rights reserved.
4 Assembled 23 August 2011
5
6 SQL> select * from v$version;
7
8 BANNER
9 --------------------------------------------------------------------------------
10 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
11 PL/SQL Release 11.2.0.1.0 - Production
12 CORE 11.2.0.1.0 Production
13 TNS for Solaris: Version 11.2.0.1.0 - Production
14 NLSRTL Version 11.2.0.1.0 - Production

begin:

1.检查数据库字符集

1 SQL> select userenv('language') from dual;
2
3 USERENV('LANGUAGE')
4 ----------------------------------------------------
5 AMERICAN_AMERICA.ZHS16GBK

2.检查ZONE时区

1 SQL> SELECT version FROM v$timezone_file;
2
3    VERSION
4 ----------
5         11

3.检查数据库endian

1 SQL> SELECT d.PLATFORM_NAME,ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp,V$DATABASE d WHERE tp.PLATFORM_NAME =d.PLATFORM_NAME;
2
3 SQL> PLATFORM_NAME               ENDIAN_FORMAT
4
5 -----------------------------    --------------
6 Linux x86 64-bit                 Little


SQL> 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
---------------------------------------- --------------
Solaris Operating System (x86-64)        Little

4.创建测试表空及用户

source:

1 SQL> create tablespace ttbs1 datafile '/u01/app/oracle/oradata/silent/ttbs1.dbf' size 100m;
2
3 Tablespace created.

1 SQL> create user test1 identified by test default tablespace ttbs1;
2
3 User created.
4
5 SQL> grant connect,resource to test1;
6
7 Grant succeeded.

target:

1 SQL> create tablespace ttbs2 datafile '/u01/app/oracle/oradata/sun/ttbs2.dbf' size 100m;
2
3 Tablespace created.

SQL> create user test2 identified by test default tablespace ttbs2;

User created.

SQL> grant connect,resource to test2;

Grant succeeded.

5.test1用户登录创建表及导入数据

1 SQL> create table ttbs as select * from all_objects;
2
3 Table created.
4
5 SQL> select * from tab;
6
7 TNAME                          TABTYPE  CLUSTERID
8 ------------------------------ ------- ----------
9 TTBS                           TABLE
10
11 SQL> select count(*) from ttbs;
12
13   COUNT(*)
14 ----------
15      55624

6.check tablespace contain-self


1 SQL> exec dbms_tts.transport_set_check('ttbs1',true);
2
3 PL/SQL procedure successfully completed.
4
5 SQL> select * from transport_set_violations;
6
7 no rows selected

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

1 SQL> select * from transport_set_violations;
2 VIOLATIONS
3 -----------------------------------------------------------------------------
4 ORA-39917: 可插入集内不允许使用 SYS 拥有的对象 TTBS1 (在表空间 TTBS1 中)
7.set tablespace ttbs1 read only

1 SQL> alter tablespace ttbs1 read only;
2
3 Tablespace altered.

8.平台转换

soruce转换:

1 RMAN> convert tablespace 'TTBS1'to platform="Solaris Operating System (x86-64)" db_file_name_convert='/u01/app/oracle/oradata/silent/ttbs1.dbf','/tmp/ttbs1.dbf';
2
3 Starting conversion at source at 12-AUG-12
4 using channel ORA_DISK_1
5 channel ORA_DISK_1: starting datafile conversion
6 input datafile file number=00005 name=/u01/app/oracle/oradata/silent/ttbs1.dbf
7 converted datafile=/tmp/ttbs1.dbf
8 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:16
9 Finished conversion at source at 12-AUG-12

如果在target端转换,方法如下:

1 RMAN> CONVERT DATAFILE '/u01/app/oracle/oradata/sun/ttbs1.dbf' TO PLATFORM="Solaris Operating System (x86-64)" FROM PLATFORM="Linux x86 64-bit" DB_FILE_NAME_CONVERT="/u01/app/oracle/oradata/sun/","/tmp/";
2
3 Starting conversion at target at 12-AUG-12
4 using channel ORA_DISK_1
5 channel ORA_DISK_1: starting datafile conversion
6 input file name=/u01/app/oracle/oradata/sun/ttbs1.dbf
7 converted datafile=/tmp/ttbs1.dbf
8 channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:14
9 Finished conversion at target at 12-AUG-12

9.将dump文件及生成的/tmp/ttbs1.dbf拷贝到target,将dump放在dpdir目录下,将数据文件放在target的数据文件目录下(注:数据文件可其他位置,只要oracle可以读取即可)

10.在target端导入


1 bash-3.2$ impdp TRANSPORT_DATAFILES='/u01/app/oracle/oradata/sun/ttbs1.dbf' directory=dpdir dumpfile=ttbs.dmp remap_tablespace=ttbs1:ttbs2 remap_schema=test1:test2 logfile=ttbs1.log
2
3 Import: Release 11.2.0.1.0 - Production on Mon Aug 13 22:46:54 2012
4
5 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
6
7 Username: /as sysdba
8
9 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
10 With the Partitioning, OLAP, Data Mining and Real Application Testing options
11 Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
12 Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA TRANSPORT_DATAFILES=/u01/app/oracle/oradata/sun/ttbs1.dbf directory=dpdir dumpfile=ttbs.dmp remap_tablespace=ttbs1:ttbs2 remap_schema=test1:test2 logfile=ttbs1.log
13 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
14 Processing object type TRANSPORTABLE_EXPORT/TABLE
15 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
16 Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:47:16

备注:此步试验我重新做一下,原因是我想用到参数remap_tablespace=ttbs1:ttbs2,第一次失败的原因是我已经在target端建好ttbs2表空间,所以在导入的时候报错"ttbs2表空间已存在",后来我删除了ttbs2表空间,重新导入成功,也就是说在导入的时候到自动创建新的表空间而无需事先建好。

如果不加remap_tablespace参数直接导入的话,结果是这样:

1 bash-3.2$ impdp TRANSPORT_DATAFILES='/u01/app/oracle/oradata/sun/ttbs1.dbf' directory=dpdir dumpfile=ttbs.dmp remap_schema=test1:test2 logfile=ttbs1.log               
2 Import: Release 11.2.0.1.0 - Production on Mon Aug 13 22:25:32 2012
3
4 Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
5
6 Username: /as sysdba
7
8 Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
9 With the Partitioning, OLAP, Data Mining and Real Application Testing options
10 Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
11 Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /******** AS SYSDBA TRANSPORT_DATAFILES=/u01/app/oracle/oradata/sun/ttbs1.dbf directory=dpdir dumpfile=ttbs.dmp remap_schema=test1:test2 logfile=ttbs1.log
12 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
13 Processing object type TRANSPORTABLE_EXPORT/TABLE
14 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
15 Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 22:27:03
16
17 bash-3.2$ exit
18 exit
19
20 SQL> conn test2/test
21 Connected.
22 SQL> select table_name,tablespace_name from user_tables;
23
24 TABLE_NAME                     TABLESPACE_NAME
25 ------------------------------ ------------------------------
26 TTBS                           TTBS1

11.验证操作正确性


1 SQL> conn test2/test
2 Connected.
3 SQL> select * from tab;
4
5 TNAME                          TABTYPE  CLUSTERID
6 ------------------------------ ------- ----------
7 TTBS                           TABLE

9 SQL> select count(*) from ttbs;
10
11    COUNT(*)
12 ----------
13       55624
14
15 SQL> select USERNAME,DEFAULT_TABLESPACE from dba_users where USERNAME='TEST2';
16
17 USERNAME                       DEFAULT_TABLESPACE
18 ------------------------------ ------------------------------
19 TEST2                          TTBS2
20
21 SQL> select table_name,tablespace_name from user_tables;
22
23 TABLE_NAME                     TABLESPACE_NAME
24 ------------------------------ ------------------------------
25 TTBS                           TTBS2

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle传输表空间 OTTS跨平台传输表空间的详细步骤

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1433.html

Oracle研究中心

关键词:

Oracle传输表空间

OTTS使用案例

Oracle不同平台传输表空间的步骤