简要说明:利用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/