transport tablespace测试

最新推荐文章于 2024-07-24 10:20:18 发布

cijiongjia0871

最新推荐文章于 2024-07-24 10:20:18 发布

阅读量103

点赞数

文章标签：数据库

先来看一些官方文档对transport tablespace的介绍：

什么场景可以用到TTS:
Starting with Oracle Database Release 10g, you can transport tablespaces across
platforms. This functionality can be used to:
■ Allow a database to be migrated from one platform to another
■ Provide an easier and more efficient means for content providers to publish
structured data and distribute it to customers running Oracle Database on
different platforms
■ Simplify the distribution of data from a data warehouse environment to data
marts, which are often running on smaller platforms
■ Enable the sharing of read-only tablespaces between Oracle Database installations
on different operating systems or platforms, assuming that your storage system is
accessible from those platforms and the platforms all have the same endianness, as
described in the sections that follow.

[@more@]

两种TTS方法：
There are two ways to transport a tablespace:
■ Manually, following the steps described in this section. This involves issuing
commands to SQL*Plus, RMAN, and Data Pump.
■ Using the Transport Tablespaces Wizard in Enterprise Manager
下面这段话很重要，如果源端和目标端平台的字节顺序不一致，那么必须在源端或者目标端进行转换：
If the source platform and the destination platform are of different endianness, then an
additional step must be done on either the source or destination platform to convert
the tablespace being transported to the destination format. If they are of the same
endianness, then no conversion is necessary and tablespaces can be transported as if
they were on the same platform.
查询平台的字节顺序：
SQL> COLUMN PLATFORM_NAME FORMAT A36
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM ORDER BY PLATFORM_NAME;
TTS测试的一般步骤：
1. For cross-platform transport, check the endian format of both platforms by
querying the V$TRANSPORTABLE_PLATFORM view. --检查字节顺序
Ignore this task if you are transporting your tablespace set to the same platform
2. Pick a self-contained set of tablespaces. --表空间必须自包含
SQL>EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('users', TRUE);
SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;
3. At the source database, place the set of tablespaces in read-only mode and
generate a transportable tablespace set. --将表空间设置为read-only
4. Transport the tablespace set.
Copy the datafiles and the export file to a place that is accessible to the destination
database. --复制数据文件
5. (Optional) Restore tablespaces to read/write mode. --将表空间设置问read/write
6. At the destination database, import the tablespace set. --在目标端导入元数据
Invoke the Data Pump utility to import the metadata for the tablespace set.

下面开始基本测试：
本次我们的实验是从linux传送到linux，字符集也相同，所以不需要转换平台
在源端：
[oracle@ora02 orcl2]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 23 15:54:34 2012

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exec dbms_tts.transport_set_check('BRENTT',TRUE);

PL/SQL procedure successfully completed

SQL> select * from transport_set_violations;

no rows selected
SQL> conn BRENTT/BRENTT
Connected.
SQL> select * from cat;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
TT TABLE
T1 TABLE
T2 TABLE
T4 TABLE
TA TABLE
TB TABLE
T3 TABLE
T5 TABLE
T6 TABLE
T7 TABLE
EXCEPTIONS TABLE

TABLE_NAME TABLE_TYPE
------------------------------ -----------
TEST2 TABLE
T TABLE
DIR TABLE
S1 SEQUENCE
S2 SEQUENCE
SEQ_TEST2 SEQUENCE
SEQ_TEST3 SEQUENCE
SEQ_TEST4 SEQUENCE
TEST TABLE

20 rows selected.

SQL> alter tablespace BRENTT read only;
alter tablespace BRENTT read only
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn / as sysdba
Connected.
SQL> alter tablespace BRENTT read only;

Tablespace altered.

SQL> col directory_path for a50
SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS DATA_PUMP_DIR /u01/app/oracle/product/10.2.0/db_1/rdbms/log/
SQL> create directory dumpdir as '/backup'；

Directory created.
SQL>exit
导出表空间的元数据：
[oracle@ora02 orcl2]$ expdp system/manager dumpfile=expdat.dmp directory=dumpdir transport_tablespaces=BRENTT logfile=tts.log

Export: Release 10.2.0.1.0 - Production on Monday, 23 July, 2012 16:12:09

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=dumpdir transport_tablespaces=BRENTT logfile=tts.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/backup/expdat.dmp
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:12:41
=====================================================================================================
如果你是不同平台之间进行传输，那么还需要用RMAN进行转换，例：
$ RMAN TARGET /
RMAN> CONVERT TABLESPACE BRENTT
2> TO PLATFORM 'Microsoft Windows IA (32-bit)'
3> FORMAT '/tmp/%U';
RMAN> exit
Recovery Manager complete.

也可以在目标端做，例如：
C:>RMAN TARGET /

Recovery Manager: Release 11.2.0.0.1

Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORAWIN (DBID=3462152886)
RMAN> CONVERT DATAFILE
2>'C:Tempbrent.dbf',
3>'C:Tempsales_201.dbf'
4>TO PLATFORM="Microsoft Windows IA (32-bit)"
5>FROM PLATFORM="Solaris[tm] OE (32-bit)"
6>DB_FILE_NAME_CONVERT=
7>'C:Temp', 'C:apporauseroradataorawin'
8> PARALLELISM=4;
=====================================================================================================

在目标端：
[oracle@ora01 mydb]$ scp 192.168.1.51:/backup/expdat.dmp .
oracle@192.168.1.51's password:
expdat.dmp 100% 240KB 240.0KB/s 00:00
[oracle@ora01 mydb]$ scp 192.168.1.51://u01/app/oracle/oradata/orcl2/BRENTT.dbf .
oracle@192.168.1.51's password:
BRENTT.dbf 100% 400MB 7.4MB/s 00:54

导入表空间的metadata：
[oracle@ora01 mydb]$ impdp system/manager dumpfile=expdat.dmp directory=dumpdir transport_datafiles=/u02/oradata/mydb/BRENTT.dbf logfile=imp_tts.log;

Import: Release 10.2.0.1.0 - Production on Monday, 23 July, 2012 16:32:32

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 16:32:45
出现错误，没有表空间内的用户，必须先创建表空间的用户，或者使用remap_scheme转换schemas，再次导入：
[oracle@ora01 mydb]$ impdp system/manager dumpfile=expdat.dmp directory=dumpdir transport_datafiles=/u02/oradata/mydb/BRENTT.dbf logfile=imp_tts.log;

Import: Release 10.2.0.1.0 - Production on Monday, 23 July, 2012 16:34:28

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" stopped due to fatal error at 16:34:32
再次出现错误，原来我的BRENTT表空间以前做测试，修改了block_size为16k，
SQL> select block_size from dba_tablespaces where tablespace_name='BRENTT';

BLOCK_SIZE
----------
16384
在目标端修改参数：
SQL> alter system set db_16K_cache_size = 20M scope = both ;

System altered.

SQL>exit
再次导入，不再报错了：
[oracle@ora01 mydb]$ impdp system/manager dumpfile=expdat.dmp directory=dumpdir transport_datafiles=/u02/oradata/mydb/BRENTT.dbf logfile=imp_tts.log;

Import: Release 10.2.0.1.0 - Production on Monday, 23 July, 2012 16:43:23

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=expdat.dmp directory=dumpdir transport_datafiles=/u02/oradata/mydb/BRENTT.dbf logfile=imp_tts.log
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
ORA-39082: Object type TRIGGER:"BRENTT"."TRIGGER_INSTEAD_OF" created with compilation warnings
ORA-39082: Object type TRIGGER:"BRENTT"."TRIGGER_INSTEAD_OF" created with compilation warnings
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 2 error(s) at 16:43:42

导入成功，我们进入sqlplus看看：
[oracle@ora01 mydb]$ sqlplus BRENTT/BRENTT

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 23 16:44:54 2012

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from cat;

TABLE_NAME TABLE_TYPE
------------------------------ -----------
TEST TABLE
TT TABLE
T1 TABLE
T2 TABLE
T4 TABLE
TA TABLE
TB TABLE
T3 TABLE
T5 TABLE
T6 TABLE
T7 TABLE

TABLE_NAME TABLE_TYPE
------------------------------ -----------
EXCEPTIONS TABLE
TEST2 TABLE
T TABLE
DIR TABLE

15 rows selected.

查看表空间是否是传输过来的

SQL> select tablespace_name,plugged_in from dba_tablespaces; --YES则表示此表空间是transport过来的。

TABLESPACE_NAME PLU
------------------------------ ---
SYSTEM NO
UNDOTBS1 NO
SYSAUX NO
TEMP NO
USERS NO
BRENTT YES

注意：transport tablespace 可以传输trigger，却不能传输sequence，procedure，functions，package等。

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

转载于:http://blog.itpub.net/26675752/viewspace-1058981/

cijiongjia0871

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
transport tablespace测试

先来看一些官方文档对transport tablespace的介绍：什么场景可以用到TTS:Starting with Oracle Database Release 10g, you can transport tablesp...
复制链接

扫一扫