ITPub博客

首页 > 数据库 > Oracle > 传输表空间的使用

传输表空间的使用

原创 Oracle 作者:lucyne 时间:2015-08-14 11:29:26 0 删除 编辑
step:
--1.在传输前要先检查下这个表空间的依赖关系,看有没有 有依赖的对象,执行以下sql就可以了
sys@STATDB2> exec dbms_tts.transport_set_check('BB_DATA2', TRUE);


PL/SQL procedure successfully completed.


--查询返回结果 有一个索引表空间有依赖关系
sys@STATDB2> SELECT * FROM transport_set_violations;


VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
Index TLBB.PK_bbuser_basic in tablespace bbuser_basic_INDEX enforces primary constriants  of table TLBB.bbuser_basic in tables
pace bbuser_basic_DATA


--再查询下索引表空间
sys@STATDB2> exec dbms_tts.transport_set_check('bbuser_basic_INDEX', TRUE);


PL/SQL procedure successfully completed.


--和数据表空间是相关的
sys@STATDB2> SELECT * FROM transport_set_violations;


VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
Index TLBB.PK_bbuser_basic in tablespace bbuser_basic_INDEX enforces primary constriants  of table TLBB.bbuser_basic in tables
pace bbuser_basic_DATA


Index TLBB.IND_UB_CN in tablespace bbuser_basic_INDEX points to table TLBB.bbuser_basic in tablespace bbuser_basic_DATA
Index TLBB.IND_UB_REGDATE in tablespace bbuser_basic_INDEX points to table TLBB.bbuser_basic in tablespace bbuser_basic_DATA


sys@STATDB2> !
--需要导出表空间中的ddl定义,先看在硬盘空间是否满足
[@stat2.cyou.com ~]$ ll
[@stat2.cyou.com dump_dir]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             5.9G  2.6G  3.0G  46% /
/dev/sdb1             2.6T  1.7T  837G  67% /U01
/dev/sda3             7.8G  571M  6.8G   8% /var
/dev/sda5              12G  2.6G  8.5G  24% /usr
tmpfs                  48G   32K   48G   1% /dev/shm
10.10.74.61:/backup/10.11.154.151_statdb2
                       19T   17T  1.7T  92% /home/oracle/backup_stage/10.11.154.151_statdb2
10.10.75.29:/backup/10.11.17.84_sol_29
                      3.6T  3.0T  611G  84% /home/oracle/backup_stage/10.11.17.84_sol_29
10.11.54.192:/data/oracle/oradata/statdb2
                      1.7T  1.3T  327G  80% /U01/mnt_192_data
[@stat2.cyou.com dump_dir]$ exit
@stat2.cyou.com dump_dir]$ exit
exit
--传输表空间必须将表空间设置为read only;


sys@STATDB2> ALTER TABLESPACE bbuser_basic_DATA READ ONLY;


Tablespace altered.


sys@STATDB2> ALTER TABLESPACE bbuser_basic_INDEX READ ONLY;


Tablespace altered.


sys@STATDB2> 


--执行导出DDL定义
[@stat2.cyou.com dump_dir]$ expdp \'/ as sysdba\' directory=DATAPUMP_DIR  dumpfile=bbuser_basic.dmp logfile=bbuser_basic.log TRANSPORT_TABLESPACES=\(bbuser_basic_INDEX,bbuser_basic_DATA\);


Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 08 December, 2011 17:19:47


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
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01":  '/******** AS SYSDBA' directory=DATAPUMP_DIR dumpfile=bbuser_basic.dmp logfile=bbuser_basic.log TRANSPORT_TABLESPACES=(bbuser_basic_INDEX,bbuser_basic_DATA) 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
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:
  /home/oracle/dump_dir/bbuser_basic.dmp
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 17:20:05


[@stat2.cyou.com dump_dir]$ 




--看下目标机器上空间是否满足
[@statdb1.cyou.com statdb1]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             6.0G  2.7G  3.0G  48% /
none                   20G  328K   20G   1% /dev/shm
/dev/sda6             120G  360M  113G   1% /opt
/dev/sdb1             3.1T  1.1T  1.9T  38% /U01
/dev/sda5             5.0G  2.8G  2.0G  60% /usr
/dev/sda2             9.9G  836M  8.6G   9% /var
/dev/shm               20G  328K   20G   1% /tmp
10.10.75.29:/backup/10.11.17.84_sol_29
                      3.6T  3.0T  611G  84% /home/oracle/backup_stage/10.11.17.84_sol_29
10.10.75.29:/backup/10.11.18.149_statdb1
                      3.6T  3.0T  611G  84% /home/oracle/backup_stage/10.11.18.149_statdb1
                      
--copy               
[@stat2.cyou.com dump_dir]$ scp /U01/app/oracle/oradata/statdb2/bbuser_basic_data01.dbf 10.11.18.149:/U01/app/oracle/oradata/statdb1/
oracle@10.11.18.149's password: 
bbuser_basic_data01.dbf                                                                             100%   20GB  21.1MB/s   16:29    
[@stat2.cyou.com dump_dir]$ 
[@stat2.cyou.com ~]$ scp /U01/app/oracle/oradata/statdb2/bbuser_basic_index01.dbf 10.11.18.149:/U01/app/oracle/oradata/statdb1/
oracle@10.11.18.149's password: 
bbuser_basic_index01.dbf                                                                            100%   19GB  20.1MB/s   16:27    
[@stat2.cyou.com ~]$ 
--设置表空间read write
sys@STATDB2> ALTER TABLESPACE bbuser_basic_DATA READ WRITE;


Tablespace altered.


sys@STATDB2> ALTER TABLESPACE bbuser_basic_INDEX READ WRITE;


Tablespace altered.
--最后别忘记把ddl定义导过去
[@stat2.cyou.com dump_dir]$ scp bbuser_basic.dmp 10.11.18.149:/home/oracle/out2inner/
oracle@10.11.18.149's password: 
bbuser_basic.dmp                                                                                    100%  112KB 112.0KB/s   00:00    
[@stat2.cyou.com dump_dir]$ 
--在目标机执行导入
[@statdb1.cyou.com out2inner]$ impdp \'/ as sysdba\' directory=DATA_PUMP_out2inner dumpfile=bbuser_basic.dmp logfile=bbuser_basic.log REMAP_SCHEMA=tlbb:ldj  TRANSPORT_DATAFILES=\('/U01/app/oracle/oradata/statdb1/bbuser_basic_data01.dbf','/U01/app/oracle/oradata/statdb1/bbuser_basic_index01.dbf'\);


Import: Release 10.2.0.5.0 - 64bit Production on Thursday, 08 December, 2011 17:43:30


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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.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=DATA_PUMP_out2inner dumpfile=bbuser_basic.dmp logfile=bbuser_basic.log REMAP_SCHEMA=tlbb:ldj TRANSPORT_DATAFILES=(/U01/app/oracle/oradata/statdb1/bbuser_basic_data01.dbf,/U01/app/oracle/oradata/statdb1/bbuser_basic_index01.dbf) 
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT 
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 6 error(s) at 17:43:34




--最后别忘记到表空间设置为READ WRITE
sys@statdb1> ALTER TABLESPACE bbuser_basic_DATA READ WRITE;


Tablespace altered.


sys@statdb1> ALTER TABLESPACE bbuser_basic_INDEX READ WRITE;




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

上一篇: DBMS_MONITOR跟踪SQL
请登录后发表评论 登录
全部评论
. . 一个 DBA. . .

注册时间:2012-01-06

  • 博文量
    84
  • 访问量
    742529