Transporting Tablespace Between Database
【测试目的】
利用Oracle TTS(transport tablespace)特性实现表空间合并
【主要步骤】
确定源平台/目的平台信息以及以及字节序信息
确定待传输的表空间是否独立 (self-contained)
生成transportable tablespace set
transportable tablespace set包含待传输表空间所有数据文件以及待传输表空间结构信息(metadata)导出文件;
如果源平台/目的平台的字节序信息不同, 就必须将tablespace set的字节序信息转换为目的平台;
既可以在源平台上转换, 也可以在目的平台转换;
传输(scp/rsync) tablespace set
导入tablespace set
【测试一】
从DB01将user01用户相关的表空间传输到DB02
实例信息:
user01 DB01 1.1.1.1 1.1.1.2 user02 DB02 2.2.2.2 2.2.2.3 数据文件:
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name in ('user01_DAT','user01_IDX'); FILE_NAME TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/data1/oradata/DB01/r221dat009.dbf user01_DAT
/data1/oradata/DB01/r221dat010.dbf user01_DAT ......
/data1/oradata/DB01/r221idx007.dbf user01_IDX
/data1/oradata/DB01/r221idx008.dbf user01_IDX 30 rows selected.
测试示例:
- Step 1: 确定平台信息以及字节信息
source platform:
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; PLATFORM_NAME ENDIAN_FORMAT
-------------------------- --------------
Linux x86 64-bit Little
target platform:
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME; PLATFORM_NAME ENDIAN_FORMAT
-------------------------- --------------
Linux x86 64-bit Little
从查询结果看到: 平台和字节序信息相同, 因此不需要进行convert操作
- Step 2: 确认Self-Contained Set of Tablespaces
执行存储过程:
DBMS_TTS.TRANSPORT_SET_CHECK检查表空间是否独立
SQL>EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('user01_DAT,user01_IDX',TRUE); PL/SQL procedure successfully completed. SQL> select * from TRANSPORT_SET_VIOLATIONS; no rows selected
Step 3: 生成Transport Tablespace Set
将要传输的相关表空间read-only
SQL> alter tablespace user01_DAT read only;
SQL> alter tablespace user01_IDX read only;
导出metadata信息
2.1创建导出目录
SQL> create directory dump_dir as '/u/dump/';
SQL> grant read,write on directory dump_dir to PUBLIC;
2.2调用expdp工具
expdp dumpfile=expdat.dmp directory=dump_dir transport_tablespaces = user01_DAT,user01_IDX transport_full_check=y Export: Release 11.2.0.2.0 - Production on Thu Sep 26 11:45:33 2013 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Username:****** Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=dump_dir transport_tablespaces=user01_DAT,user01_IDX transport_full_check=y
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PLUGTS_BLK]
ORA-00904: "Q"."TABOBJ_NUM": invalid identifier ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8358 ----- PL/SQL Call Stack -----
object line object
handle number name
0x5ba4f3e20 19208 package body SYS.KUPW$WORKER
0x5ba4f3e20 8385 package body SYS.KUPW$WORKER
0x5ba4f3e20 2935 package body SYS.KUPW$WORKER
0x5ba4f3e20 9054 package body SYS.KUPW$WORKER
0x5ba4f3e20 1688 package body SYS.KUPW$WORKER
0x5ba4f7e60 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 11:13:56
在这里调用expdp工具时, 遇到错误:
encounter some error: ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UNLOAD_METADATA [PLUGTS_BLK]
for more information: http://martincarstenbach.wordpress.com/2011/05/10/slight-11-2-0-2-caveat-with-tts-and-missing-xmldb/
解决办法:
A. 使用exp/imp工具
exp file=/u/dump/expdat.dmp log=/u/dump/expdat.log transport_tablespace=y tablespaces=user01_DAT,user01_IDX
B. 编译后，使用expdp/impdp工具
conn / as sysdba
@?/rdbms/admin/catbundle.sql psu apply
@?/rdbms/admin/catqm.sql xdb sysaux temp yes
@?/rdbms/admin/utlrp.sql expdp dumpfile=expdat.dmp directory=dump_dir transport_tablespaces = user01_DAT,user01_IDX transport_full_check=y
如果Step 1中的平台信息, 以及字节序信息不同, 那么就需要对表空间进行convert; 否则直接跳过, 进行Step 4操作
这里假设target platform是Microsoft Windows NT平台, 需要在source platform上convert数据文件到临时目录:
RMAN TARGET / NOCATALOG
RMAN> convert tablespace user01_DAT,user01_IDX to platform 'Microsoft Windows NT' format '/temp/%U';
Step 4: 传输Tablespace Set
rsync -a --progress [email protected]:/data1/oradata/DB01/r221*dbf /data1/oradata/DB01/
rsync -a --progress [email protected]:/u/dump/expdat.* /u/
如果是将表空间传输到不同平台不同字节序的目的端, 并且Step 3中没有做convert操作, 然而不管数据文件是否被convert过, 都必须移动到特定的目的端位置;
使用RMAN命令convert传输过来的数据文件为目的端字节序信息, 并且将结果存储在/data1/oradata/DB01/
这里假设数据文件存放在目的端目录：/data1/dbbak/test/，target platform是Microsoft Windows NT平台:
RMAN> convert datafile
2> '/data1/dbbak/test/r231dat005.dbf'
......
13> '/data1/dbbak/test/r231idx004.dbf'
14> to platform="Microsoft Windows NT"
15> from platform="Linux x86 64-bit"
16> db_file_name_convert=
17> "/data1/dbbak/test/","/data1/oradata/DB01/"
18> parallelism=5;
备注:
- 省略号出需要填写全部的文件信息;
- 在target platform上RMAN是通过文件名来辨识数据文件的, 而不是表空间名称. 因为目的端是无法知道表空间名称的, 直到metadata导入到实例;
- 参数: to platform和from platform信息是可选的, RMAN通过存在的数据文件确定source platform信息, 以及运行convert主机上默认的target platform信息;
Step 5: Import the Tablespace Set
在目标平台上, 导入metadata信息之前用户必须存在
SQL> create user user01 identified by user01;
SQL> grant connect to user01;
或者
可以用下面sql迁移source上的用户和权限
1.1 创建用户
SQL> select 'create user '||a.name||' identified by values "'||a.password||'";' from user$ a,dba_users b where a.user#=b.user_id and b.account_status='OPEN' and a.name not in ('SYS','SYSTEM','PERFSTAT');
1.2 设置用户默认的表空间
SQL> select 'alter user '||a.name||' default tablespace '||b.default_tablespace||';' from user$ a,dba_users b where a.user#=b.user_id and b.account_status='OPEN' and a.name not in ('SYS','SYSTEM','PERFSTAT');
1.3 初始化用户权限
SQL> select 'grant create session to '||a.name||';' from user$ a,dba_users b where a.user#=b.user_id and b.account_status='OPEN' and a.name not in ('SYS','SYSTEM','PERFSTAT');
SQL> select 'grant '||c.PRIVILEGE||' to '||d.name||';' from (select distinct a.name from user$ a,dba_users b,dba_sys_privs c where a.user#=b.user_id and a.name=c.grantee and b.account_status='OPEN' and a.name not in ('SYS','SYSTEM','PERFSTAT')) d,dba_sys_privs c where d.name=c.grantee;
Import metadata信息
2.1 使用imp/exp工具
imp parfile=/u/par.f
--参数：parfile可以用来指定impdp参数
/u/par.f内容:
file=/u/expdat.dmp
log=/u/impdat.log
transport_tablespace=y
tablespace=user01_DAT,user01_IDX
datafiles=
/data1/oradata/DB01/r221dat015.dbf,
......
/data1/oradata/DB01/r221idx002.dbf,
/data1/oradata/DB01/r221idx001.dbf
备注:
- 省略号处需要列举所有数据文件信息;
2.2 使用expdp/impdp工具
impdp parfile=/u/par.f
--参数: parfile可以用来指定impdp参数
/u/par.f内容:
dumpfile=expdat.dmp
directory=dump_dir
transport_datafiles=
/data1/oradata/DB02/r231dat005.dbf,
......
/data1/oradata/DB02/r231idx003.dbf,
/data1/oradata/DB02/r231idx004.dbf
logfile=tts_impdp.log
备注:
- 省略号处需要列举所有数据文件信息;
--END--