一、基本理论
1、表空间迁移
在exp -help中,有一个参数
transport_tablespace 导出可传输的表空间元数据(N)
通过此选项,可以对一组自包含、只读的表空间只导出元数据,然后在OS层将这些表空间的数据文件拷贝到目标平台;
将元数据导入数据字典(称插入),即完成迁移
2、自包含
可传输表空间有一个重要概念,自包含。自包含表示待传输的表空间集合没有指向其他表空间(不传输)的引用。
自包含分两种:一般自包含表空间集合、严格自包含表空间集合。
以下情况违反自包含原则:
1)、索引在内部表空间集(即待传输的表空间集合),而表在外部表空间集(即不传输的表空间集合),则违反自包含原则;
相反,若表在内部表空间集,而索引在外部表空间集,则不违反自包含原则。
2)、分区表,一部分在内部表空间集,一部分在外部表空间集,则违反自包含原则。
3)、如果在传输表空间时,同时传输约束,则对于引用完整性约束,约束指向的表在外部表空间集,则违反自包含原则;
如果不传输约束,则与约束指向无关。
4)、表在内部表空间集,而lob列在外部表空间集,则违反自包含约束。
3、DBMS_TTS包
可通过dbms_tts包的transport_set_check过程,来检查表空间是否自包含,分两种验证方式:
非严格方式(full_check=false),检查表空间集引用的对象是否自包含。
严格方式(full_check=true),严格方式不只检查表空间集引用的对象是否自包含,同时会检查被其它表空间引用的对象,引用者是否在表空间集中。
dbms_tts.transport_set_check(
ts_list IN VARCHAR2,
incl_constraints IN BOOLEAN DEFAULT FALSE,
full_check IN BOOLEAN DEFAULT FALSE);
transport_set_check的3个参数:
ts_list指定内部表空间集,即待传输的表空间集合,多个表空间用逗号“,”隔开;
incl_constraints为true时,指定检查表空间集引用的对象是否自包含。默认为false;
full_check为true时,指定为严格检查,即不只检查表空间集引用的对象是否自包含,同时会检查被其它表空间引用的对象,引用者是否在表空间集中。默认为false,即非严格检查;
利用dbms_tts.transport_set_check执行完自包含检查后,查transport_set_violations临时表中的记录;
若内部表空间集符合自包含条件,则返回空记录;
否则,返回违反自包含条件的信息。
SQL> select * from transport_set_violations;
注意:transport_set_violations;
二、实验:利用利用可传输表空间技术,迁移表空间
SQL> col name for a40
SQL> select name from v$datafile;
NAME
----------------------------------------
/oradata/mydata/mydata/system01.dbf
/oradata/mydata/mydata/undotbs01.dbf
/oradata/mydata/mydata/sysaux01.dbf
/oradata/mydata/mydata/users01.dbf
/oradata/mydata/mydata/example01.dbf
1、创建表空间、用户以及数据
创建数据表空间
SQL> create tablespace testtbs
2 datafile '/oradata/mydata/mydata/test01.dbf' size 20M;
Tablespace created.
创建索引表空间
SQL> create tablespace indextbs
2 datafile '/oradata/mydata/mydata/index01.dbf' size 10M;
Tablespace created.
创建用户trans,并授予权限
SQL> create user trans identified by oracle
2 default tablespace testtbs;
User created.
SQL> grant connect,resource to trans;
Grant succeeded.
SQL> connect trans/oracle
Connected.
创建测试数据
SQL> create table test as select * from dict;
Table created.
SQL> select count(*) from test;
COUNT(*)
----------
659
SQL> desc dict;
Name Null? Type
------------------------------ -------- ----------------
TABLE_NAME VARCHAR2(30)
COMMENTS VARCHAR2(4000)
SQL> create index ind_test on test(table_name) tablespace indextbs;
Index created.
SQL> col index_name for a10
SQL> col table_name for a10
SQL> col tablespace_name for a10
SQL> col table_owner for a10
SQL> select index_name,table_name,table_owner,tablespace_name from dba_indexes where table_name='TEST';
INDEX_NAME TABLE_NAME TABLE_OWNE TABLESPACE
---------- ---------- ---------- ----------
IND_TEST TEST TRANS INDEXTBS
2、利用dbms_tts.transport_set_check执行自包含检查
以sysdba权限登录,执行自包含检查
SQL> conn / as sysdba
Connected.
执行非严格自包含检查(full_check=false)
SQL> exec dbms_tts.transport_set_check('testtbs',true);
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
执行严格自包含检查(full_check=true)
SQL> exec dbms_tts.transport_set_check('testtbs',true,true);
PL/SQL procedure successfully completed.
SQL> set linesize 100
SQL> select * from transport_set_violations;
VIOLATIONS
----------------------------------------------------------------------------------------------------
Index TRANS.IND_TEST in tablespace INDEXTBS points to table TRANS.TEST in tablespace TESTTBS
严格自包含检查,返回的违反自包含信息,提示表空间indextbs中的索引ind_test指向了表空间testtbs中的test表。
此时,可对TESTTBS,INDEXTBS表空间,同时执行自包含检查。
SQL> exec dbms_tts.transport_set_check('TESTTBS,INDEXTBS',TRUE,TRUE);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
no rows selected
3、设置表空间为只读
SQL> conn / as sysdba
Connected.
SQL> alter tablespace testtbs read only;
Tablespace altered.
SQL> alter tablespace indextbs read only;
Tablespace altered.
4、用exp导出表空间的元数据
[oracle@oraserver ~]$ exp \'/ as sysdba\' tablespaces='indextbs,testtbs' transport_tablespace=y file=exp_indextesttbs.dmp
Export: Release 10.2.0.1.0 - Production on Thu Aug 18 14:04:24 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and UTF8 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace INDEXTBS ...
. exporting cluster definitions
. exporting table definitions
For tablespace TESTTBS ...
. exporting cluster definitions
. exporting table definitions
. . exporting table TEST
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
5、若跨平台迁移,需要用RMAN对表空间做字节序转换
[oracle@oraserver ~]$ rman target /
RecovCopyrery Manager: Release 10.2.0.1.0 - Production on Thu Aug 18 14:07:32 2011
ight (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: MYDATA (DBID=305115346)
RMAN> convert tablespace indextbs
2> to platform. 'Linux IA (32-bit)'
3> format '/tmp/%N_%f';
Starting backup at 18-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/oradata/mydata/mydata/test01.dbf
converted datafile=/tmp/TESTTBS_6
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 18-AUG-11
RMAN> convert tablespace indextbs
2> to platform. 'Linux IA (32-bit)'
3> format '/tmp/%N_%f';
Starting backup at 18-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=/oradata/mydata/mydata/index01.dbf
converted datafile=/tmp/INDEXTBS_7
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 18-AUG-11
查看生成的数据文件
[oracle@oraserver ~]$ ls -l /tmp
总用量 30868
-rw------- 1 oracle oinstall 0 8月 17 16:33 283bmHu2rE
-rw-r----- 1 oracle oinstall 1090 8月 9 16:17 cpuinfo.txt
drwx------ 3 oracle oinstall 4096 8月 17 17:20 gconfd-oracle
-rw-r----- 1 oracle oinstall 18 8月 3 17:03 glibc.txt
drwxr-x--- 2 oracle oinstall 4096 8月 17 17:16 hsperfdata_oracle
-rw-r----- 1 oracle oinstall 10493952 8月 18 14:52 INDEXTBS_7
drwx------ 2 oracle oinstall 4096 8月 3 16:51 keyring-aGne5j
drwx------ 2 oracle oinstall 4096 4月 13 14:06 keyring-Q5xDUB
drwx------ 2 oracle oinstall 4096 5月 4 17:13 keyring-qsi4JY
-rw-r----- 1 oracle oinstall 20 8月 9 16:17 LinuxVendor_output.txt
srwxr-xr-x 1 oracle oinstall 0 8月 3 16:51 mapping-oracle
srwxr-xr-x 1 root root 0 3月 5 17:43 mapping-root
drwx------ 2 oracle oinstall 4096 8月 17 17:20 orbit-oracle
-rw-r----- 1 oracle oinstall 46998 8月 3 17:03 pkginfo.txt
-rw-r----- 1 oracle oinstall 100 8月 9 16:17 swapinfo.txt
-rw-r----- 1 oracle oinstall 20979712 8月 18 14:52 TESTTBS_6
-rw-r----- 1 oracle oinstall 11 8月 9 16:17 tmpFileKernelParms.txt
将转换后的数据文件拷贝到目标平台
[oracle@oraserver ~]$ scp /tmp/TESTTBS_6 192.168.1.24:/home/oracle/
[oracle@oraserver ~]$ scp /tmp/INDEXTBS_7 192.168.1.24:/home/oracle/
6、用RMAN转换拷贝过来的数据文件,使其具有规范的命名
RMAN> convert datafile '/home/oracle/TESTTBS_6'
2> db_file_name_convert
3> '/home/oracle/TESTTBS_6','/oradata/mydata02/test01.dbf';
Starting backup at 18-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/TESTTBS_6
converted datafile=/oradata/mydata02/test01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 18-AUG-11
RMAN> convert datafile '/home/oracle/INDEXTBS_7'
2> db_file_name_convert
3> '/home/oracle/INDEXTBS_7','/oradata/mydata02/index01.dbf';
Starting backup at 18-AUG-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/home/oracle/INDEXTBS_7
converted datafile=/oradata/mydata02/index01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 18-AUG-11
7、用imp导入数据(将数据导入system用户)
[oracle@oraserver02 ~]$ imp \'/ as sysdba\' tablespaces=testtbs,indextbs transport_tablespace=y file=exp_indextesttbs.dmp fromuser=trans touser=system datafiles='/oradata/mydata02/test01.dbf','/oradata/mydata02/index01.dbf'
可通过fromuser/touser参数,将数据导入其他用户(这里由trans用户导入system用户)
8、验证
1)、验证数据文件是否生成
[oracle@oraserver02 mydata02]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Aug 18 15:30:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select name from v$datafile;
NAME
---------------------------------------
/oradata/mydata02/system01.dbf
/oradata/mydata02/undotbs01.dbf
/oradata/mydata02/sysaux01.dbf
/oradata/mydata02/users01.dbf
/oradata/mydata02/example01.dbf
/oradata/mydata02/index01.dbf
/oradata/mydata02/test01.dbf
7 rows selected.
2)、验证表空间是否传输成功
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
INDEXTBS READ ONLY
TESTTBS READ ONLY
8 rows selected.
3)、设置传输过来的表空间为读写
SQL> alter tablespace indextbs read write;
Tablespace altered.
SQL> alter tablespace testtbs read write;
Tablespace altered.
4)、验证表内数据和索引是否正确插入
SQL> select count(*) from system.test;
COUNT(*)
----------
659
SQL> col index_name for a10
SQL> col table_name for a10
SQL> col tablespace_name for a10
SQL> col table_owner for a10
SQL> select index_name,table_name,table_owner,tablespace_name from dba_indexes where table_name='TEST';
INDEX_NAME TABLE_NAME TABLE_OWNE TABLESPACE
---------- ---------- ---------- ----------
IND_TEST TEST SYSTEM INDEXTBS
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25264937/viewspace-705270/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25264937/viewspace-705270/