sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

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

【学习笔记】Oracle传输表空间 OTTS使用详细步骤

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

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle传输表空间OTTS的使用详细笔记。

环境情况

Source 端:
操作系统: OracleLinux 6.2 64位
endianness格式: little
数据库版本:11.2.0.3

Target 端:
操作系统:OracleLinux 6.2 64位
endianness 格式: little
数据库版本:11.2.0.3

1、查看操作系统endianness格式
[sql] view plain copy
col platform_name for a40 
 
SELECT *  
FROM V$TRANSPORTABLE_PLATFORM  
ORDER BY PLATFORM_ID; 
 
PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT 
----------- ---------------------------------------- -------------- 
          1 Solaris[tm] OE (32-bit)                  Big 
          2 Solaris[tm] OE (64-bit)                  Big 
          3 HP-UX (64-bit)                           Big 
          4 HP-UX IA (64-bit)                        Big 
          5 HP Tru64 UNIX                            Little 
          6 AIX-Based Systems (64-bit)               Big 
          7 Microsoft Windows IA (32-bit)            Little 
          8 Microsoft Windows IA (64-bit)            Little 
          9 IBM zSeries Based Linux                  Big 
         10 Linux IA (32-bit)                        Little 
         11 Linux IA (64-bit)                        Little 
         12 Microsoft Windows x86 64-bit             Little 
         13 Linux x86 64-bit                         Little 
         15 HP Open VMS                              Little 
         16 Apple Mac OS                             Big 
         17 Solaris Operating System (x86)           Little 
         18 IBM Power Based Linux                    Big 
         19 HP IA Open VMS                           Little 
         20 Solaris Operating System (x86-64)        Little 
         21 Apple Mac OS (x86-64)                    Little 
 
20 rows selected. 
 
--分别查看 source 端 和target端操作系统endianness格式 
--source 
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 
 
--target 
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 

2、在source端创建测试表空间

[sql] view plain copy
select tablespace_name, 
    status  
from dba_tablespaces; 
 
TABLESPACE_NAME                STATUS 
------------------------------ --------- 
SYSTEM                         ONLINE 
UNDOTBS1                       ONLINE 
SYSAUX                         ONLINE 
TEMPTS1                        ONLINE 
USERS                          ONLINE 
OUTLN                          ONLINE 
 
6 rows selected. 
 
select file_name from dba_data_files; 
 
FILE_NAME 
------------------------------------------------ 
/u01/app/oracle/oradata/normal/system01.dbf 
/u01/app/oracle/oradata/normal/undotbs01.dbf 
/u01/app/oracle/oradata/normal/sysaux01.dbf 
/u01/app/oracle/oradata/normal/users01.dbf 
/u01/app/oracle/oradata/normal/undotbs02.dbf 
/u01/app/oracle/oradata/normal/system02.dbf 
/u01/app/oracle/oradata/normal/outln01.dbf 
 
7 rows selected. 
 
--创建表空间创建表空间 tset 
create tablespace tset datafile '/u01/app/oracle/oradata/normal/test01.dbf' size 50M; 
Tablespace created. 
 
--创建用户source_test,并指定表空间 
 
--在source端 
create user source_test  
identified by oracle  
default tablespace tset  
temporary tablespace TEMPTS1; 
 
User created. 
 
grant connect,resource to source_test; 
 
Grant succeeded. 
 
--在target端(暂时只先创建用户) 
create user target_test  
identified by oracle 
temporary tablespace TEMPTS1; 
 
User created. 
 
grant connect,resource to target_test; 
 
Grant succeeded. 
 
--创建测试表 
SQL> conn source_test/oracle 
Connected. 
SQL> create table t1(id number, name varchar2(30)); 
 
Table created. 
 
SQL> insert into t1 values(1, 'AAAAA'); 
 
1 row created. 
 
SQL> insert into t1 values(2, 'BBBBB'); 
 
1 row created. 
 
SQL> commit; 
 
Commit complete. 
 
select * from t1; 
 
        ID NAME 
---------- ------------------------------ 
         1 AAAAA 
         2 BBBBB 

3、在source端和target端创建 backup 的目录

[sql] view plain copy
[oracle@normal ~]$ mkdir -p /u01/backup 
[oracle@normal ~]$ ls -l /u01 
total 24 
drwxr-xr-x 3 oracle oinstall  4096 Jul 28 12:31 app 
drwxr-xr-x 2 oracle oinstall  4096 Sep 14 16:21 backup 
 
SQL> show user 
USER is "SYS" 
SQL> create directory backup as '/u01/backup'; 
 
Directory created. 
 
SQL> col owner format a5 
SQL> col directory_name format a25 
SQL> col DIRECTORY_PATH format a50    
 
SQL> select * from dba_directories;    
OWNER DIRECTORY_NAME            DIRECTORY_PATH 
----- ------------------------- -------------------------------------------------- 
SYS   BACKUP                    /u01/backup 
SYS   OUTLN_DIR                 /home/oracle 
SYS   DATA_PUMP_DIR             /u01/app/oracle/product/11.2.0/db_1/rdbms/log/ 
SYS   ORACLE_OCM_CONFIG_DIR     /u01/app/oracle/product/11.2.0/db_1/ccr/state 
 
SQL> GRANT read, write ON DIRECTORY backup TO source_test; 
 
Grant succeeded. 
 
--在target端 
[oracle@test ~]$ mkdir -p /u01/backup 
[oracle@test ~]$ ls -l /u01 
total 24 
drwxr-xr-x 3 oracle oinstall  4096 Aug 28 09:09 app 
drwxr-xr-x 2 oracle oinstall  4096 Sep 14 16:40 backup 
 
SQL> show user 
USER is "SYS" 
SQL> create directory backup as '/u01/backup'; 
 
Directory created. 
 
SQL> col owner format a5 
SQL> col directory_name format a25 
SQL> col DIRECTORY_PATH format a50 
SQL> select * from dba_directories; 
 
OWNER DIRECTORY_NAME            DIRECTORY_PATH 
----- ------------------------- -------------------------------------------------- 
SYS   BACKUP                    /u01/backup 
SYS   OUTLN_DIR                 /home/oracle 
SYS   DATA_PUMP_DIR             /u01/app/oracle/product/11.2.0/db_1/rdbms/log/ 
SYS   ORACLE_OCM_CONFIG_DIR     /u01/app/oracle/product/11.2.0/db_1/ccr/state 
 
SQL> GRANT read, write ON DIRECTORY backup TO target_test; 
 
Grant succeeded. 

4、检查表空间自包含
(就是改表空间里的数据没有和其他表空间数据有关联,如果有关联会报错)

[sql] view plain copy
SQL> execute dbms_tts.transport_set_check('TSET', TRUE); 
 
PL/SQL procedure successfully completed. 
 
--查看自包含验证结果: 
SQL> select * from transport_set_violations; 
 
no rows selected 
--没有记录说明没有错 

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

[sql] view plain copy
SQL> alter tablespace TSET read only; 
 
Tablespace altered. 
 
select tablespace_name, 
    status  
from dba_tablespaces; 
 
TABLESPACE_NAME                STATUS 
------------------------------ --------- 
SYSTEM                         ONLINE 
UNDOTBS1                       ONLINE 
SYSAUX                         ONLINE 
TEMPTS1                        ONLINE 
USERS                          ONLINE 
OUTLN                          ONLINE 
TSET                           READ ONLY 
 
7 rows selected.     

6、生成:Transportable Tablespace Set

Transportable Tablespace Set有两部分:

1.expdp 导出的表空间的metadata

2.还有就是表空间对应的数据文件

[sql] view plain copy
--expdp 导出的表空间的metadata   
[oracle@normal normal]$ pwd 
/u01/app/oracle/oradata/normal 
[oracle@normal normal]$ ll 
total 2294664 
-rw-r----- 1 oracle oinstall   9781248 Sep 14 16:46 control01.ctl 
drwx------ 2 oracle oinstall     16384 Aug 22 12:44 lost+found 
-rw-r----- 1 oracle oinstall  20979712 Sep 14 15:52 outln01.dbf 
-rw-r----- 1 oracle oinstall  52429312 Sep 14 16:45 redo01a.log 
-rw-r----- 1 oracle oinstall  52429312 Sep 14 16:45 redo01b.log 
-rw-r----- 1 oracle oinstall  52429312 Sep 14 15:52 redo02a.log 
-rw-r----- 1 oracle oinstall  52429312 Sep 14 15:52 redo02b.log 
-rw-r----- 1 oracle oinstall  52429312 Sep 14 15:52 redo03a.log 
-rw-r----- 1 oracle oinstall  52429312 Sep 14 15:52 redo03b.log 
-rw-r--r-- 1 oracle oinstall     22633 Aug 22 17:00 su.lst 
-rw-r----- 1 oracle oinstall 340795392 Sep 14 16:40 sysaux01.dbf 
-rw-r----- 1 oracle oinstall 340795392 Sep 14 16:43 system01.dbf 
-rw-r----- 1 oracle oinstall 314580992 Sep 14 16:43 system02.dbf 
-rw-r----- 1 oracle oinstall  20979712 Sep 14 15:53 temp01.dbf 
-rw-r----- 1 oracle oinstall  52436992 Sep 14 15:53 temp02.dbf 
-rw-r----- 1 oracle oinstall  52436992 Sep 14 16:31 test01.dbf 
-rw-r----- 1 oracle oinstall 209723392 Sep 14 16:43 undotbs01.dbf 
-rw-r----- 1 oracle oinstall 209723392 Sep 14 16:40 undotbs02.dbf 
-rw-r----- 1 oracle oinstall 524296192 Sep 14 15:52 users01.dbf 
 
[oracle@normal normal]$ expdp dumpfile=test01.dmp directory=backup transport_tablespaces=TSET transport_full_check=y logfile=TSET.log  
 
Export: Release 11.2.0.3.0 - Production on Sun Sep 14 16:54:30 2014 
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
 
Username: / as sysdba 
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  /********/ AS SYSDBA dumpfile=test01.dmp directory=backup transport_tablespaces=TSET transport_full_check=y logfile=TSET.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 "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded 
****************************************************************************** 
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is: 
  /u01/backup/test01.dmp 
****************************************************************************** 
Datafiles required for transportable tablespace TSET: 
  /u01/app/oracle/oradata/normal/test01.dbf 
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:55:13 
 
[oracle@normal normal]$ ls -l /u01/backup/   
total 80 
-rw-r----- 1 oracle oinstall 77824 Sep 14 16:55 test01.dmp 
-rw-r--r-- 1 oracle oinstall  1160 Sep 14 16:55 TSET.log 
7、将Transportable Tablespace set 传送到Target端
1)将表空间test 对应的数据文件copy到Target 对应的ORADATA目录下。

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

[sql] view plain copy
--将表空间test 对应的数据文件copy到Target 对应的ORADATA目录下。 
[oracle@normal normal]$ scp /u01/backup/test01.dmp 192.168.137.12:/u01/backup 
oracle@192.168.137.12 s password:  
test01.dmp                                  100%   76KB  76.0KB/s   00:00  
          
--将expdp 导出的表空间metadta 数据copy 到Target 端的backup 目录下    
[oracle@normal normal]$ scp test01.dbf 192.168.137.12:/u01/app/oracle/oradata/normal/test01.dbf 
oracle@192.168.137.12 s password:  
test01.dbf                                  100%   50MB  16.7MB/s   00:03   
 
--在target端查看文件是否已经传输 
[oracle@test ~]$ ll /u01/backup/              
total 76 
-rw-r----- 1 oracle oinstall 77824 Sep 14 17:03 test01.dmp 
 
[oracle@test ~]$ ll $ORACLE_BASE/oradata/normal/test01.dbf 
-rw-r----- 1 oracle oinstall 52436992 Sep 14 17:04 /u01/app/oracle/oradata/normal/test01.dbf 
8、在Target 系统上Import 表空间的metadata(使用target_test用户,需要用到remap_schema)
[sql] view plain copy
[oracle@test ~]$ impdp directory=backup dumpfile=test01.dmp transport_datafiles=/u01/app/oracle/oradata/normal/test01.dbf remap_schema=source_test:target_test logfile 
=test.log 
 
Import: Release 11.2.0.3.0 - Production on Sun Sep 14 17:09:25 2014 
 
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. 
 
Username: / as sysdba 
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production 
With the Partitioning, OLAP, Data Mining and Real Application Testing options 
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded 
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  /********/ AS SYSDBA directory=backup dumpfile=test01.dmp transport_datafiles=/u01/app/oracle/oradata/normal/test01.dbf remap_schema=source_test:target_test logfile=test.log  
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK 
Processing object type TRANSPORTABLE_EXPORT/TABLE 
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK 
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 17:09:55 
9、查看并修改表空间状态
[sql] view plain copy
select tablespace_name, 
    status  
from dba_tablespaces; 
 
TABLESPACE_NAME                STATUS 
------------------------------ --------- 
SYSTEM                         ONLINE 
UNDOTBS1                       ONLINE 
SYSAUX                         ONLINE 
TEMPTS1                        ONLINE 
USERS                          ONLINE 
OUTLN                          ONLINE 
TSET                           READ ONLY 
 
7 rows selected. 
 
SQL> alter tablespace TSET read write; 
 
Tablespace altered. 
10、验证
[sql] view plain copy
SQL> conn target_test/oracle 
Connected. 
 
SQL> select * from t1; 
 
        ID NAME 
---------- ------------------------------ 
         1 AAAAA 
         2 BBBBB  

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

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

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

Oracle研究中心

关键词:

Oracle传输表空间

OTTS使用详细步骤