10g transportable tablespace的一个例子

试想了如下一种比较极端的情况，自己做了个例子。

1. 从单节点 到 RAC
2. 从文件系统到ASM
3. 跨平台
4. 数据库小版本不同

可传输表空间的局限性比较明显，因为SYSTEM不能被传输，只适合去传输数据，而不能做迁移。除非你愿意把过程，视图等对象重建。

[@more@]

目的
========1. 从单节点 到 RAC
2. 从文件系统到ASM
3. 跨平台
4. 数据库小版本不同

环境设置
========
源数据库
------
Solaris x86-32bit 10.2.0.2 单节点，文件系统
SID: TTS

INST_ID INSTANCE_NAME HOST_NAME VERSION STATUS
---------- ---------------- ---------- ----------------- ------------
1 TTS nascds5 10.2.0.2.0 OPEN


目标数据库
------
Linux x86-32bit 10.2.0.4 两节点RAC ASM
SID: RACDB

INST_ID INSTANCE_NAME HOST_NAME VERSION STATUS
---------- ---------------- ---------- ----------------- ------------
1 racdb1 nascds10 10.2.0.4.0 OPEN
2 racdb2 nascds11 10.2.0.4.0 OPEN

准备
========

1. 两边的数据都需要建好

2. 生成一些数据用作测试

SQL> select name from v$database;

NAME
---------
TTS

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE

6 rows selected.

SQL> create tablespace tts1 datafile '/u01/app/oracle/oradata/TTS/tts1.dbf' size 10M;

Tablespace created.

SQL> create tablespace tts2 datafile '/u01/app/oracle/oradata/TTS/tts2.dbf' size 10M;

Tablespace created.

SQL> create user roger identified by roger
2 default tablespace tts1
3 temporary tablespace temp;

User created.

SQL> grant dba to roger;

Grant succeeded.

SQL>
SQL> conn roger/roger
Connected.
SQL> create table mark (message varchar(20));

Table created.

SQL> insert into mark values ('I am in source');

1 row created.

SQL> commit;

Commit complete.

SQL> create index idx_mark on mark(message)
2 tablespace tts2;

Index created.

SQL>

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
EXAMPLE_TTS
TTS1
TTS2

8 rows selected.

SQL>


7 rows selected.

SQL>

===> 我们接下来会去传输 TTS1 和 TTS2 这两个表空间



检查
========

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm#ADMIN01101

++ 使用可传输表空间的限制

* 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).
* 从10GR2开始，我们可以传输含有XMLTypes的表空间，但是必须使用imp/exp工具，而不能用datapump。CONSTRAINTS 和TRIGGERS需要等于Y


1. 查看数据库字符集是否一致.
SQL> select * from NLS_DATABASE_PARAMETERS where PARAMETER in ('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET');

PARAMETER
------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET
AL16UTF16

NLS_CHARACTERSET
AL32UTF8


2. 查看目标库是否有同名的表空间
SQL> select tablespace_name from dba_tablespaces;

3. 下面查询返回含有XMLTypes的表空间:
SQL>select distinct p.tablespace_name
from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t
where t.table_name=x.table_name and
t.tablespace_name=p.tablespace_name and
x.owner=u.username

TABLESPACE_NAME
------------------------------
SYSAUX
USERS

4.SYSTEM表空间，以及所有者是SYS的对象 无法被传输
SQL> select distinct owner from dba_segments where tablespace_name in ('TTS1','TTS2');

OWNER
------------------------------
ROGER

5. 查看表空间是否有外部的已存关系:

SQL> execute sys.dbms_tts.transport_set_check('TTS1,TTS2',TRUE);

PL/SQL procedure successfully completed.

SQL> select * from sys.transport_set_violations;

no rows selected

SQL>

==>如果这个查询返回值，需要在传输处理完毕，知道无返回值



步骤
=========

STEP 1
-------
源表空间在被传输前需要被置成READ ONLY，以保证数据的一致性

SQL> alter tablespace TTS1 read only;

Tablespace altered.

SQL> alter tablespace TTS2 read only;

Tablespace altered.

SQL>


STEP 2
-------
[源数据库] 导出Metadata (参见 **Notice 2)

$ exp userid='sys/sys as sysdba' file=tts_exp.dmp log=tts_exp.log transport_tablespace=y tablespaces=TTS1,TTS2

Export: Release 10.2.0.2.0 - Production on Tue Apr 14 12:06:50 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TTS1 ...
. exporting cluster definitions
. exporting table definitions
. . exporting table MARK
For tablespace TTS2 ...
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
$


STEP 3
-------
[目标数据库] 查看试图 V$TRANSPORTABLE_PLATFORM，找到操作系统的准确名称

SQL> SELECT tp.platform_id,d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------- --------------
10 Linux IA (32-bit) Little

STEP 4
-------
[源数据库] 如果两个操作系统的 endian formats 不同，则需要convert.

RMAN> CONVERT TABLESPACE TTS1 TO PLATFORM 'Linux IA (32-bit)' FORMAT '/export/home/oracle/%U';
RMAN> CONVERT TABLESPACE TTS2 TO PLATFORM 'Linux IA (32-bit)' FORMAT '/export/home/oracle/%U';

$ rman target /

Recovery Manager: Release 10.2.0.2.0 - Production on Tue Apr 14 17:03:41 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TTS (DBID=1643225788)

RMAN> CONVERT TABLESPACE TTS1 TO PLATFORM 'Linux IA (32-bit)' FORMAT '/export/home/oracle/%U';

Starting backup at 14-APR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/u01/app/oracle/oradata/TTS/tts1.dbf
converted datafile=/export/home/oracle/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:02
Finished backup at 14-APR-09

RMAN> CONVERT TABLESPACE TTS2 TO PLATFORM 'Linux IA (32-bit)' FORMAT '/export/home/oracle/%U';

Starting backup at 14-APR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=/u01/app/oracle/oradata/TTS/tts2.dbf
converted datafile=/export/home/oracle/data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 14-APR-09

RMAN> exit


STEP 5
-------
讲生成的dmp和备份文件传输到目标数据库所在的服务器

[nascds10/12_RAC1]ls -ltr
total 20552
-rw-r--r-- 1 oracle oinstall 4096 Apr 14 17:24 tts_exp.dmp
-rw-r--r-- 1 oracle oinstall 694 Apr 14 17:24 tts_exp.log
-rw-r--r-- 1 oracle oinstall 10493952 Apr 14 17:24 data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v
-rw-r--r-- 1 oracle oinstall 10493952 Apr 14 17:24 data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k
[nascds10/12_RAC1]pwd
/home/oracle/roger
[nascds10/12_RAC1]


STEP 6
-------
在目标库上创建必要的用户 (后者在imp的时候使用 TOUSER)

SQL> create user roger identified by roger;

User created.

SQL>


STEP 7
-------
使用imp做导入 (参见**Notice 3)

$ imp userid='sys/sys as sysdba' file=tts_exp.dmp log=tts_imp.log transport_tablespace=y datafiles='/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v','/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k'


Import: Release 10.2.0.4.0 - Production on Wed Apr 15 09:53:50 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. importing ROGER's objects into ROGER
. . importing table "MARK"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
[nascds10/12_RAC1]

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
UNDOTBS2 ONLINE
EXAMPLE ONLINE
TEST ONLINE
UNDOTBS ONLINE
TTS1 READ ONLY
TTS2 READ ONLY

11 rows selected.

SQL>
SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------
+DG1/racdb/datafile/users.256.678963235
USERS

+DG1/racdb/datafile/sysaux.270.678963225
SYSAUX

+DG1/racdb/datafile/undotbs1.265.678963229
UNDOTBS1

+DG1/racdb/datafile/system.269.678963227
SYSTEM

+DG1/racdb/datafile/example.259.678963231
EXAMPLE

+DG1/racdb/datafile/undotbs2.264.678963231
UNDOTBS2

+DG1/racdb/datafile/test.257.678963233
TEST

+DG1/racdb/datafile/undotbrac1
UNDOTBS

/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v
TTS1

/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k
TTS2


10 rows selected.

SQL>


STEP 8
-------
[目标数据库] 把表空间置成读写状态:

SQL> alter tablespace tts1 read write;

Tablespace altered.

SQL> alter tablespace tts2 read write;

Tablespace altered.

SQL>



STEP 9
-------
在ASM中制作数据文件镜像 (参见 **Notice 4):

RMAN> backup as copy datafile '/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v' format '+DG1';
RMAN> backup as copy datafile '/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k' format '+DG1';


[nascds10/12_RAC1]rman nocatalog target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Apr 15 10:31:37 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: RACDB (DBID=669324570)
using target database control file instead of recovery catalog

RMAN> backup as copy datafile '/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v' format '+DG1';

Starting backup at 15-APR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00009 name=/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v
output filename=+DG1/racdb/datafile/tts1.271.684239563 tag=TAG20090415T103243 recid=12 stamp=684239568
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 15-APR-09

RMAN> backup as copy datafile '/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k' format '+DG1';

Starting backup at 15-APR-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00010 name=/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k
output filename=+DG1/racdb/datafile/tts2.272.684239605 tag=TAG20090415T103325 recid=13 stamp=684239610
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 15-APR-09

RMAN>


STEP 10
-------
把数据文件切入ASM。如果数据是打开的，则需要先把相应的数据文件offline

SQL> alter database datafile '/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k' offline;

Database altered.

SQL> alter database datafile '/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v' offline;

Database altered.

SQL>

[nascds10/12_RAC1]rman nocatalog target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Apr 15 10:37:50 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: RACDB (DBID=669324570)
using target database control file instead of recovery catalog

RMAN> switch datafile '/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v' to copy;

datafile 9 switched to datafile copy "+DG1/racdb/datafile/tts1.271.684239563"

RMAN> switch datafile '/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k' to copy;

datafile 10 switched to datafile copy "+DG1/racdb/datafile/tts2.272.684239605"

RMAN>


STEP 11
-------
做数据文件的恢复，再置成online

SQL> recover datafile '+DG1/racdb/datafile/tts1.271.684239563';
Media recovery complete.
SQL> alter database datafile '+DG1/racdb/datafile/tts1.271.684239563' online;

Database altered.

SQL> recover datafile '+DG1/racdb/datafile/tts2.272.684239605';
Media recovery complete.
SQL> alter database datafile '+DG1/racdb/datafile/tts2.272.684239605' online;

Database altered.

SQL> select name, status from v$datafile;

NAME
--------------------------------------------------------------------------------
STATUS
-------

...

+DG1/racdb/datafile/tts1.271.684239563
ONLINE

+DG1/racdb/datafile/tts2.272.684239605
ONLINE


10 rows selected.

SQL>


STEP 11
-------
搞定。对了不要忘记把源数据库的表空间改回读写.

SQL> conn roger/roger
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
MARK TABLE

SQL> select * from mark;

MESSAGE
--------------------
I am in source

SQL> select INDEX_NAME,STATUS from user_indexes;

INDEX_NAME STATUS
------------------------------ --------
IDX_MARK VALID

SQL>



** Notice

1. TTS只适用于传输表空间中的数据。如果要做数据库迁移，就需要手工传输SYSTEM表空间中的对象，比如package, procedure, trigger, statistics等

2. 在做导入导出的时候，环境变量NLS_LANG必须被正确设置

3. 目标库的"compatible" 必须大于或等于源数据库, 否则会出错

[nascds10/12_RAC1]imp userid='sys/sys as sysdba' file=tts_exp.dmp log=tts_imp.log transport_tablespace=y datafiles='/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v','/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS2_FNO-7_02kcfd8k'

Import: Release 10.2.0.4.0 - Production on Tue Apr 14 17:41:52 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Oracle Label Security, OLAP,
Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via conventional path
About to import transportable tablespace(s) metadata...
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
export client uses AL32UTF8 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 721:
"BEGIN sys.dbms_plugts.checkCompType('COMPATSG','10.2.0.2.0'); END;"
IMP-00003: ORACLE error 721 encountered
ORA-00721: changes by release 10.2.0.2.0 cannot be used by release 10.2.0.1.0
ORA-06512: at "SYS.DBMS_PLUGTS", line 2004
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
[nascds10/12_RAC1]


4. 在复制数据文件到ASM的时候，表空间需要在读写状态下。.

[nascds10/12_RAC1]rman nocatalog target /

Recovery Manager: Release 10.2.0.4.0 - Production on Wed Apr 15 09:59:17 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: RACDB (DBID=669324570)
using target database control file instead of recovery catalog

RMAN> backup as copy datafile '/home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v' format '+DG1/racdb/datafile';

Starting backup at 15-APR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=119 instance=racdb1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=148 instance=racdb2 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 04/15/2009 09:59:25
RMAN-20201: datafile not found in the recovery catalog
RMAN-06010: error while looking up datafile: /home/oracle/roger/data_D-TTS_I-1643225788_TS-TTS1_FNO-6_01kcfd7v

RMAN>



参考
============

Article-ID: Note 1071511.6
Title: How to determine if a tablespace can be transported

Article-ID: Note 733824.1
Title: HowTo Recreate a database using TTS (TransportableTableSpace)

Article-ID: Note 394798.1
Title: How to Create Transportable Tablespaces Where the Source and Destination are ASM-Based

Article-ID: Note 371556.1
Title: How move tablespaces across platforms using Transportable Tablespaces with RMAN

Article-ID: Note 340848.1
Title: Performing duplicate database with ASM/OMF/RMAN

Article-ID: Note 252219.1
Title: Steps To Migrate/Move a Database From Non-ASM to ASM And Vice-Versa

Article-ID: Note 468458.1
Title: How To Move Controlfile To ASM

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

转载于:http://blog.itpub.net/225056/viewspace-1022200/