表空间迁移测试手记(dbms_tts)

简要说明：利用dbms_tts包测试表空间迁移。由于只有一个数据库作为测试环境，因此，本次测试将原表空间导出后删除再导入。

TTS前提：相同的db_block_size,相同字符集

部署测试环境：
------------------------------
drop user test_t cascade;
drop tablespace test_t_data including contents and datafiles;
drop tablespace test_t_idx including contents and datafiles;

create tablespace test_t_data datafile '/u03/oradata/ora9i/test_t_data1.dbf' size 20M;
alter tablespace test_t_data add datafile '/u03/oradata/ora9i/test_t_data2.dbf' size 20M;
create tablespace test_t_idx datafile '/u03/oradata/ora9i/test_t_idx.dbf' size 20M;
create user test_t identified by test_t default tablespace test_t_data;
grant connect,plustrace to test_t;
alter user test_t quota unlimited  on test_t_data;
alter user test_t quota unlimited  on test_t_idx;

create table test_t.T_EPLOG_BBS_POST as select * from el.T_EPLOG_BBS_POST;
create table test_t.T_EPLOG_NEWS_INFO as select * from el.T_EPLOG_NEWS_INFO;
create index test_t.idx_postid on test_t.T_EPLOG_BBS_POST(post_id) tablespace test_t_idx;

验证表空间的Self_container:
-----------------------------
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('test_t_data,test_t_idx',true);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
如果该表空间与其它表空间有关联（根据测试，主要是PK所关联的索引），TRANSPORT_SET_VIOLATIONS会说明与之关联的表空间。

导出source表空间的相关数据字典信息
-----------------------------
alter tablespace test_t_data read only;
alter tablespace test_t_idx read only;

host exp userid=\"sys/oracle as sysdba\" transport_tablespace=y tablespaces=test_t_idx,test_t_data triggers=y constraints=y grants=y file=/tmp/tina/testt.dmp log=/tmp/tina/testt.log
 
Export: Release 9.2.0.7.0 - Production on Tue Jun 3 15:09:40 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TEST_T_IDX ...
. exporting cluster definitions
. exporting table definitions
For tablespace TEST_T_DATA ...
. exporting cluster definitions
. exporting table definitions
. . exporting table               T_EPLOG_BBS_POST
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 353
ORA-06512: at "SYS.DBMS_METADATA", line 418
ORA-06512: at "SYS.DBMS_METADATA", line 451
ORA-06512: at "SYS.DBMS_METADATA", line 629
ORA-06512: at "SYS.DBMS_METADATA", line 1219
ORA-06512: at line 1
. . exporting table              T_EPLOG_NEWS_INFO
EXP-00056: ORACLE error 19206 encountered
ORA-19206: Invalid value for query or REF CURSOR parameter
ORA-06512: at "SYS.DBMS_XMLGEN", line 83
ORA-06512: at "SYS.DBMS_METADATA", line 353
ORA-06512: at "SYS.DBMS_METADATA", line 418
ORA-06512: at "SYS.DBMS_METADATA", line 451
ORA-06512: at "SYS.DBMS_METADATA", line 629
ORA-06512: at "SYS.DBMS_METADATA", line 1219
ORA-06512: at line 1
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully with warnings.

google了一下，是由于数据库XML相关的一些对象变得无效导致这个出错，需要执行catmeta.sql脚本恢复一下。

start $ORACLE_HOME/rdbms/admin/catmeta.sql

host exp userid=\"sys/oracle as sysdba\" transport_tablespace=y tablespaces=test_t_idx,test_t_data triggers=y constraints=y grants=y file=/tmp/tina/testt.dmp log=/tmp/tina/testt.log
导出成功

模拟传输datafile到目标库
---------------------------------
因为环境限制，就将原来的用户和表空间都删除了

host cp /u03/oradata/ora9i/test*  /u03/oradata/ora9i/tmp/

alter tablespace test_t_data read write;
alter tablespace test_t_idx read write;

drop user test_t cascade;
drop tablespace test_t_data including contents and datafiles;
drop tablespace test_t_idx including contents and datafiles;

host mv /u03/oradata/ora9i/tmp/* /u03/oradata/ora9i/

在目标库做恢复
----------------------------------
create user test_t identified by test_t ;
grant connect,plustrace to test_t;

host imp userid=\"sys/oracle as sysdba\" transport_tablespace=y datafiles='/u03/oradata/ora9i/test_t_data1.dbf,/u03/oradata/ora9i/test_t_data2.dbf,/u03/oradata/ora9i/test_t_idx.dbf' file=/tmp/tina/testt.dmp log=/tmp/tina/testt.log

Import: Release 9.2.0.7.0 - Production on Tue Jun 3 15:14:39 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TEST_T's objects into TEST_T
. . importing table             "T_EPLOG_BBS_POST"
. . importing table            "T_EPLOG_NEWS_INFO"
IMP-00003: ORACLE error 1925 encountered
ORA-01925: maximum of 30 enabled roles exceeded
IMP-00000: Import terminated unsuccessfully

经查，是由于source db的sys用户所拥有的rold数目超过了目标库max_enabled_roles参数值导致，两个办法：
(1) 在source db将sys用户的一些无用的role去除，重新导出
(2) 更改target db的max_enabled_roles参数（需重启实例）后再导入

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

转载于:http://blog.itpub.net/55472/viewspace-331129/