Transportable Tablespaces
by Jeff Hunter, Sr. Database Administrator
Contents
- Overview
- Introduction to Transportable Tablespaces
- Using Transportable Tablespaces
Overview
Oracle's Transportable Tablespace is one of those much awaited features that was introduced in Oracle8i (8.1.5) and is commonly used in Data Warehouses (DW). Using transportable tablespaces is much faster than using other utilities like export/import, SQL*Plus copy tables, or backup and recovery options to copy data from one database to another.
This article provides a brief introduction into configuring and using transportable tablespaces.
Introduction to Transportable Tablespaces
Before covering the details of how to setup and use transportable tablespaces, let's first discuss some of the terminology and limitations to provide us with an introduction.
- The use of transportable tablespaces are much faster than using export/import, SQL*Plus copy tables, or backup and recovery options to copy data from one database to another.
- A transportable tablespace set is defined as two components:
- All of the datafiles that make up the tablespaces that will be moved.
AND
- An export that contains the data dictionary information about those tablespaces.
- COMPATIBLE must be set in both the source and target database to at least 8.1.
- When transporting a tablespace from an OLTP system to a data warehouse using the Export/Import utility, you will most likely NOT need to transport TRIGGER and CONSTRAINT information that is associated with the tables in the tablespace you are exporting. That is, you will set the TRIGGERS and CONSTRAINTS Export utility parameters equal to "N".
- The data in a data warehouse is inserted and altered under very controlled circumstances and does not require the same usage of constraints and triggers as a typical operational system does.
- It is common and recommended though that you use the GRANTS option by setting it to Y.
- The TRIGGERS option is new in Oracle8i for use with the export command. It is used to control whether trigger information, associated with the tables in a tablespace, are included in the tablespace transport.
Limitations of Transportable Tablespaces:
- The transportable set must be self-contained.
- Both the source and target database must be running Oracle 8.1 or higher release.
- The two databases do not have to be on the same release
- The source and target databases must be on the same type of hardware and operating-system platform.
- The source and target databases must have the same database block size.
- The source and target databases must have the same character set.
- A tablespace with the same name must not already exist in the target database.
- Materialized views, function-based indexes, scoped REFs, 8.0 compatible advanced queues with multiple-recipients, and domain indexes can't be transported in this manner. (As of Oracle8i)
- Users with tables in the exported tablespace should exist in the target database prior to initiating the import. Create the user reported by the error message.
Explanation: The metadata exported from the target database does not contain enough information to create the user in the target database. The reason is that, if the metadata contained the user details, it might overwrite the privileges of an existing user in the target database.
(i.e. If the user by the same name already exists in the target database)
By not maintaining the user details, we preserve the security of the database.
Using Transportable Tablespaces
In this section, we finally get to see how to use transportable tablespaces. Here is an overview of the steps we will perform in this section:
- Verify that the set of source tablespaces are self-contained
- Generate a transportable tablespace set.
- Transport the tablespace set
- Import the tablespaces set into the target database.
In this example, we will be transporting the tablespaces, "FACT1, FACT2, and FACT_IDX" from a database named DWDB to REPORTDB. The user that owns these tables will be "DW" and password "DW".
Verify Self-Contained Status with the DBMS_TTS Package
To verify that all tablespaces to transport are self-contained, we can use the TRANSPORT_SET_CHECK procedure within the DBMS_TTS PL/SQL Package. The first parameter to this procedure is a list of the tablespaces to transport. Keep in mind that all indexes for a table, partitions, and LOB column segments in the tablespace must also reside in the tablespace set. The second parameter to this procedure is a boolean value that indicates whether or not to check for referential integrity.
SQL> connect sys/change_on_install@dwdb as sysdba SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('fact1, fact2', TRUE); SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; VIOLATIONS -------------------------------------------------------------------------------- Index DW.DEPT_PK in tablespace FACT_IDX enforces primary constriants of table D W.DEPT in tablespace FACT1 Index DW.EMP_PK in tablespace FACT_IDX enforces primary constriants of table DW .EMP in tablespace FACT1OOOPS! As we can see from the above example, I forgot to include all tablespaces that will make this self-contained. In this example, I forgot to include the FACT_IDX tablespace. Let's correct that: SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('fact1, fact2, fact_idx', TRUE); SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; no rows selected
Generate a Transportable Tablespace Set
To generate a Transportable Tablespace Set, you will need to perform the following:
- Place all tablespace within the tablespace set in READ ONLY mode.
- Use Export to gather tablespace data-dictionary information.
- Copy datafiles and the export dump from the source location to the target location.
- Place all tablespace within the tablespace set back to READ/WRITE.
% sqlplus "sys/change_on_install@dwdb as sysdba" SQL> ALTER TABLESPACE fact1 READ ONLY; SQL> ALTER TABLESPACE fact2 READ ONLY; SQL> ALTER TABLESPACE fact_idx READ ONLY; SQL> exit % exp userid=\"sys/change_on_install@dwdb as sysdba\" transport_tablespace=y tablespaces=fact1, fact2, fact_idx triggers=y constraints=y grants=y file=fact_dw.dmp % cp /u10/app/oradata/DWDB/fact1_01.dbf /u10/app/oradata/REPORTDB/fact1_01.dbf % cp /u10/app/oradata/DWDB/fact2_01.dbf /u10/app/oradata/REPORTDB/fact2_01.dbf % cp /u09/app/oradata/DWDB/fact_idx01.dbf /u09/app/oradata/REPORTDB/fact_idx01.dbf % sqlplus "sys/change_on_install@dwdb as sysdba" SQL> ALTER TABLESPACE fact1 READ WRITE; SQL> ALTER TABLESPACE fact2 READ WRITE; SQL> ALTER TABLESPACE fact_idx READ WRITE; SQL> exit
Transport the Tablespace Set
To actually transport the tablespace, this is nothing more than copying (or FTP'ing) all tablespace set datafiles to be put in their proper location on the target database. In the section previous to this, we did that with the cp command in UNIX.
In some cases this would be necessary if the files where copied off to a staging area in the previous step.
Import the Tablespace Set
Before actually importing the tablespace(s) into the target database, you will need to ensure that all users that own segments in the imported tablespaces exist. For this example, the only user that owns segments in the exported tablespaces is DW. I will create this user: % sqlplus "sys/change_on_install@reportdb as sysdba" SQL> create user dw identified by dw default tablespace users; SQL> grant dba, resource, connect to dw; SQL> exit
We now use the Import utility to bring the tablespace set's data-dictionary information into the target database.
The two required parameters are TRANSPORT_TABLESPACE=Y and DATAFILES='...' as in the following example:
% imp userid=\"sys/change_on_install@reportdb as sysdba\" transport_tablespace=y datafiles='/u10/app/oradata/REPORTDB/fact1_01.dbf', '/u10/app/oradata/REPORTDB/fact2_01.dbf', '/u09/app/oradata/REPORTDB/fact_idx01.dbf' file=fact_dw.dmp
Final Cleanup
When the tablespaces are successfully imported into the target database, they are in READ ONLY mode. If you intend to use the tablespaces for READ WRITE, you will need to manually alter them: % sqlplus "sys/change_on_install@reportdb as sysdba" SQL> ALTER TABLESPACE fact1 READ WRITE; SQL> ALTER TABLESPACE fact2 READ WRITE; SQL> ALTER TABLESPACE fact_idx READ WRITE; SQL> exit
Oracle8i에서는 tablespace단위로 그 구성 datafile들을 옮겨서 다른 database에 연결시켜
사용할 수 있는 기능이 제공된다.
SCOPE
--------
8i~10g Standard Edition 에서는 Import transportable tablespaces 기능만이 지원이 됩니다.
유용성
-------
1. 전사적 정보시스템내에서 대량의 data의 흐름이 필요할 경우, - 예를 들어,
OLTP database에서 data warehouse database로의 data이전 또는 data
warehouse에서 data mart로의 data이전 등 - 8.1이전까지는 SQL*Loader의
direct path나 parallel DML등의 방법을 이용하여 그 작업속도를
향상시키려고 시도 하였다. 8.1의 Transportable Tablespace기능을
이용한다면 datafile들을 새로운 system으로 copy하는 정도의 시간으로
작업을 완료할 수 있다.
2. 중앙에서 변경, 관리되고 지방(지사)에서 사용되는 data들을 CD-ROM에 담아서
배포하는 등에 이용가능하다.
예를 들어, 제품의 사양, 가격등에 대한 정보를 담는 tablespace를 중앙에서
변경, 저장하여 배포하고, 이 data를 지방의 database에 연결하여 주문
system등에 이용할 수 있다.
3. Contents 사업자들은 자신이 제공하는 contents들을 Transportable
Tablespace형태로 제공 하여 고객들의 database에 바로 연결하여 사용할 수
있도록 할 수 있다.
특성, 제한사항
-------------
1. 특정 tablespace내의 전체 data를 이동시킨다.
2. Media recovery를 지원한다.
3. Source database와 target database는
- 동일한 OS에서 구동되고 있어야 한다.
- Oracle8i(8.1)이상의 version이어야 한다.
- 동일한 block size를 이용해야 한다.
- 동일한 characterset을 이용해야 한다.
작업절차
-------
1. 대상 tablespace를 read only 상태로 변경한다.
file을 copy하는 동안 해당 tablespace에 변경작업이 일어나지 않도록
보장한다.
2. Source database에서 metadata를 export한다.
해당 tablespace와 그 안의 object들에 대한 dictionary정보를 dump file에
받는 과정이다.
3. 대상 tablespace의 datafile들을 target system으로 이동시킨다.
4. Export dump file을 이동시킨다.
5. Metadata를 target database에 import한다.
6. 필요하다면 이후에 해당 tablespace를 read-write mode로 변경한다.
SAMPLE
------
Source database : dbA
Target database : dbB
이동 대상 tablespace : TRANS_TS(/u01/data/trans_ts01.dbf, /u01/data/trans_ts02.dbf 로 구성)
1. dbA에서 TRANS_TS를 read only로 변경
alter tablespace TRANS_TS read only ;
2. dbA에서 metadata를 export한다.
exp sys/manager file=trans.dmp transport_tablespace=y
tablespaces=trans_ts triggers=n constraints=n
version이 8.1.6이상이라면,
exp system/manager 대신에 exp \'sys/manager as sysdba\'와 같이
주여야 한다.
transport_tablespace(Y or N)는 Y로 설정한다.
tablespaces에는 transport의 대상이 되는 tablespace를 지정한다.
대상 tablespace의 table들에 걸려있는 trigger, constraint들도 대상으로
할 것인지를 지정한다.
3. TRANS_TS의 두개의 datafile들을 dbB가 존재하는 system으로 binary
copy한다.
4. 위의 2번 과정에서 export한 dump file을 dbB가 존재하는 system으로
binary copy한다.
5. dbB에 metadata를 import한다.
imp sys/manager file=trans.dmp transport_tablespace=y
datafiles=/disk1/trans_ts01.dbf,/disk2/trans_ts02.dbf
8.1.6이상이라면 이 부분도 sys/manager대신에 \'sys/manager as dba\'
와 같이 적는다.
transport_tablespace(Y or N)는 Y로 설정한다.
datafile의 name은 dbB system에 copy된 filename을 지칭한다.
6. 필요할 경우 tablespace를 read write mode로 변경한다.
alter tablespace TRANS_TS read write ;
TRANSPORT SET
-------------
Transport하고자 하는 tablespace set은 self-contained이어야만 한다.
대상이 되는 tablespace set 내에 partitioned table이 존재한다면 해당
table의 모든 partition들이 이들 tablespace 내에 존재해야 하며, 비슷하게
LOB column의 data들도 table의 data들과 함께 이들 tablespace 내에 존재해야
하는데, 이렇게 서로 관련된 object들이 tablespace set내에 모두 존재하는
것을 self-contained라고 지칭한다.
tablespace set이 self-contained하지 않다면 transport할 수 없다.
Transport tablespace set이 self-contained인지의 여부를 확인하기 위해서
DBMS_TTS.TRANSPORT_SET_CHECK procedure를 이용한다.
예를 들어,
DBMS_TTS.TRANSPORT_SET_CHECK(ts_list=>'A,B,C',incl_constraints=>TRUE)
을 수행하면 A, B, C 세개의 tablespace로 구성된 transport tablespace set이
self-contained인지에 대한 정보를 TRANSPORT_SET_VIOLATIONS view에 기록해
준다.
incl_constraints를 설정하면 referencial(foreign key) constraint에
대해서도 self-contained 여부를 check해준다.
==================================================================
10G: TRANSPORTABLE TABLESPACES ACROSS DIFFERENT PLATFORMS
==================================================================
Purpose
---------
이 자료에서는 서로 다른 Platform 으로 Tablespaces 를 transport 하는 방법에 대해
설명하고자 한다.
Explanation
-----------
10g 에서는 서로 다른 platform 으로 tablespace 를 transport 할 수 있으며
transport 가능한 platform 정보는 다음과 같다.
SQL> select * from v$transportable_platform;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ------------------------------ --------------
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows NT Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
10g 이전 version 까지는 동일한 platform 에 대해서만 지원하였으며 10g 에서는
서로 다른 platform 에서도 tablespace 이동이 가능하도록 지원하고 있다.
Transportable Tablespaces 가 사용되어지는 사례는 다음과 같다.
1. 다른 platform 으로 database 를 migration 하고자 할때
2. 서로 다른 platform 의 DW 환경에서 Distribute data 를 data marts 로 이동을 원할 경우
3. Publish structured data 를 기존과 다른 platforms 으로 통합하고자 할 경우 등 입니다.
Transportable Tablespaces 를 위단 Step 은 다음과 같습니다.
1. Tablespace 를 READ ONLY 상태로 변경한다.
SQL> alter tablespace REPOSIT read only;
Tablespace altered.
2. Metadata 를 다음 과 같이 export 한다.
$ exp userid=\'/ as sysdba\' transport_tablespace=y
tablespaces=reposit
file=tts.dmp log=exp_tts.log
statistics=none
Export: Release 10.1.0.1.0 - Beta on Mon Nov 24 11:49:49 2003
...
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace REPOSIT ...
. exporting cluster definitions
. exporting table definitions
. . exporting table MTG_COL_DEP_CHG
. . exporting table MTG_DATABASES
....
. . exporting table SYBASE11_SYSUSERS
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
3. 이동하고자 하는 target database platform 의 ENDIAN_FORMAT 정보를 확인한다.
Case 1 의 경우는 query 결과 ENDIAN_FORMAT 이 같은 상태로 conversion 필요 없이
Tablespaces 이동이 가능하고 Case 2 의 경우에는 ENDIAN_FORMAT 이 다른 경우로
ENDIAN_FORMAT conversion 을 위해 RMAN 을 이용하여 아래와 같이 추가적인 작업이 필요하다.
Case 1
------
The source platform is Sun SPARC Solaris: endianness Big
The target platform is HP-UX (64-bit): endianness Big
SQL> select PLATFORM_ID , PLATFORM_NAME from v$database;
PLATFORM_ID PLATFORM_NAME
----------- ------------------------------
3 HP-UX (64-bit)
No conversion needed.
Case 2
------
The source platform is Microsoft WIndows NT: endianness Little
The target platform is HP-UX (64-bit): endianness Big
다음과 같이 endian format 이 다른 상태에서 작업시 다음과 같이 에러가 발생한다.
. importing SYS's objects into SYS
IMP-00017: following statement failed with ORACLE error 1565:
"BEGIN sys.dbms_plugts.beginImpTablespace('TBS_TTS',37,'SYS',1,0,8192,2,57"
"54175,1,2147483645,8,128,8,0,1,0,8,462754339,1,1,5754124,NULL,0,0,NULL,NULL"
"); END;"
IMP-00003: ORACLE error 1565 encountered
ORA-01565: error in identifying file '/database/db101b2/V101B2/datafile/reposit01.dbf'
ORA-27047: unable to read the header block of file
HP-UX Error: 2: No such file or directory
Additional information: 2
ORA-06512: at "SYS.DBMS_PLUGTS", line 1540
ORA-06512: at line 1
IMP-00000: Import terminated unsuccessfully
결국, Tablespace import 를 하기전에 RMAN 을 통해 files convert 작업이 필요하다.
$ rman target=/
Recovery Manager: Release 10.1.0.1.0 - 64bit Beta
connected to target database: V101B2 (DBID=3287908659)
RMAN> convert tablespace 'REPOSIT'
2> to platform="Linux IA (32-bit)"
3> db_file_name_convert='/database/db101b2/V101B2/datafile/reposit01.dbf',
4> '/tmp/reposit01.dbf';
Starting backup at 24-NOV-03
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=8 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=/database/db101b2/V101B2/datafile/reposit01.dbf
converted datafile=/tmp/reposit01.dbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 24-NOV-03
Convert 된 files 은 /tmp 디렉토리에 존재하게 되고 이를 target server 에 copy 한다.
4. Datafile 과 export dump file 을 ftp 를 이용하여 move 한다.
$ftp tts.dmp
+
/database/db101b2/V101B2/datafile/reposit01.dbf (no conversion)
or
/tmp/reposit01.dbf (converted file if conversion had been required)
5. Metadata 를 import 한다.
$ imp userid=\'/ as sysdba\' TRANSPORT_TABLESPACE=Y
datafiles=/database/db101b2/V101B2/datafile/reposit01.dbf
(or /tmp/reposit01.dbf )
file=tts.dmp log=imp_tts.log
Import: Release 10.1.0.1.0 - Beta on Mon Nov 24 03:37:20 2003
Export file created by EXPORT:V10.01.00 via conventional path
About to import transportable tablespace(s) metadata...
...
. importing SYS's objects into SYS
. importing OMWB's objects into OMWB
. . importing table "MTG_COL_DEP_CHG"
...
. . importing table "SYBASE11_SYSUSERS"
Import terminated successfully without warnings.
6. Import 가 성공적으로 끝나면 tablespace 를 READ WRITE 상태로 변경한다.
SQL> alter tablespace reposit read write;
Tablespace altered.