Oracle表空间的迁移

表空间的迁移


传输表空间的迁移方式快速，便捷， 不仅可以在同版本同平台中使用， 而且还是在不同版本不同平台中使用， 从而到达升级的目的。不过TTS有如下的一些限制：


The source and target database must use the same character set and national character set.


You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.


Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.


Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes, but you must use the IMP and EXP utilities, not Data Pump. When using EXP, ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).


SQL> select file_name from dba_data_files where tablespace_name = 'VSAT';
FILE_NAME
--------------------------------------------------------------------------------
/eqldisk01/oradata/vsat01.dbf
/eqldisk01/oradata/vsat02.dbf
/eqldisk01/oradata/vsat03.dbf
/eqldisk01/oradata/vsat04.dbf
/eqldisk01/oradata/vsat05.dbf
/eqldisk01/oradata/vsat06.dbf
/eqldisk01/oradata/vsat07.dbf
/eqldisk01/oradata/vsat08.dbf
/eqldisk01/oradata/vsat09.dbf
/eqldisk01/oradata/vsat10.dbf
/eqldisk01/oradata/vsat11.dbf
/eqldisk01/oradata/vsat12.dbf
/eqldisk01/oradata/vsat13.dbf
/eqldisk01/oradata/vsat14.dbf
/eqldisk01/oradata/vsat15.dbf
/eqldisk01/oradata/vsat16.dbf
/eqldisk01/oradata/vsat17.dbf
/eqldisk01/oradata/vsat18.dbf


-- 检查SOE表空间是否自包含                
SQL> exec dbms_tts.TRANSPORT_SET_CHECK('SOE', true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
SQL> show parameter compatible
compatible                           string      10.2.0.4.0 必须确保导出oracle的版本比要导入的版本高(要不会导入失败，已经验证)


-- 在传输表空间之前需要将表空间设置为只读
SQL> alter tablespace soe read only;
[oracle@oracle114 ~]$exp \"/ as sysdba\" file=vsat.dmp TRANSPORT_TABLESPACE=y TABLESPACES=vsat log=vsat.log
在导出是会出现： EXP-00091: Exporting questionable statistics


解决：
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK
LINUX>export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"


把/eqldisk01/oradata/vsat*.dbf文件和导出的dmp文件copy到新的主机上


最后：
-- 创建相应的用户并授予权限， 我这里为了简单就直接给了dba权限
SQL>create user vsat identified by vsat;
SQL> grant connect,resource,dba to vsat;


把dmp文件导入：
imp '/ as sysdba' file=/data/vsat.dmp TRANSPORT_TABLESPACE=y TABLESPACES=vsat DATAFILES=('/data/oradata/vsat01.dbf','/data/oradata/vsat02.dbf','/data/oradata/vsat03.dbf','/data/oradata/vsat04.dbf','/data/oradata/vsat05.dbf','/data/oradata/vsat06.dbf','/data/oradata/vsat07.dbf',................'/data/oradata/vsat18.dbf')


用imp时出现：
ORA-00721: changes by release 10.2.0.4.0 cannot be used by release 10.2.0.1.0
解决：
改用impdp导入或把compatible改成10.2.0.4.0 ,主要是改init$ORACLE_SID.ora中的compatible，spfile是二进制文件不能直接修改。


    若要彻底删除表，则使用语句：drop table purge;
                   清除回收站里的信息
                   清除指定表：purge table ;
                   清除当前用户的回收站：purge recyclebin;
                   清除所有用户的回收站：purge dba_recyclebin;
    不放入回收站，直接删除则是：drop table xx purge;




出现的问题：
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1
[oracle@oracle114 log]$ vim /tmp/a.txt 
UDI-00008: operation generated ORACLE error 31626
ORA-31626: job does not exist
ORA-06512: at "SYS.KUPC$QUE_INT", line 536
ORA-25254: time-out in LISTEN while waiting for a message
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2772
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3886
ORA-06512: at line 1
检查vsstat.log时发现有这个问题，并且再次导入时出现以下问题：


ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_TRANSPORTABLE_01 for user SYS
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20130204102835" and
"KUPC$S_1_20130204102835" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1606
ORA-04031: unable to allocate 56 bytes of shared memory ("streams
pool","unknown object","streams pool","fixed allocation callback")


IMP-00041: Warning: object created with compilation warnings
 "CREATE TRIGGER "VSAT".cada_bmqk"
这个问题是因为触发引起的，可以先disable,进行导出导入。


解决：
 重启DB，