[Oracle DBA]TRANSPORTABLE Tablespace

TRANSPORTABLE Tablespace

transportable tablespace

• 두 개의 오라클 데이터베이스 간에 많은 양의 데이터를 가장 빠르게 이동할 수 있는 방법
• 데이터 웨어하우스와 데이터 마트 간의 데이터 이동을 간소화(exp, imp가 없다)
• 플랫폼 간의 데이터베이스 이전을 허용

1. TARGET DB(orcl)  - platform과 endian format을 확인

/*

SYS@orcl2> select d.platform_name, tp.endian_format

           from v$transportable_platform tp, v$database d

           where tp.platform_name=d.platform_name;

PLATFORM_NAME        ENDIAN_FORMAT

-------------------- --------------

Linux IA (32-bit)    Little

*/

2. SOURCE DB (orcl2)

  

SYS@orcl2> create tablespace tts

           datafile '/home/oracle/tts01.dbf'

           size 5m;

Tablespace created.

SYS@orcl2> create table hr.tts

           tablespace tts

           as

           select * from hr.employees;

Table created.

SYS@orcl2> select count(*) from hr.tts;

  COUNT(*)

----------

       107

       107

SYS@orcl2> exec dbms_tts.transport_set_check('tts',true,true);

PL/SQL procedure successfully completed.

SYS@orcl2> select * from transport_set_violations;

no rows selected

SYS@orcl2> col platform_name format a20

SYS@orcl2> select d.platform_name, tp.endian_format

           from v$transportable_platform tp, v$database d

           where tp.platform_name=d.platform_name;

PLATFORM_NAME        ENDIAN_FORMAT

-------------------- --------------

Linux IA (32-bit)    Little

SYS@orcl2> ! mkdir -p /home/oracle/tts

SYS@orcl2> create directory tts_dir as '/home/oracle/tts';

Directory created.

SYS@orcl2> alter tablespace tts read only;

Tablespace altered.

SYS@orcl2> !cp -av /home/oracle/tts01.dbf /home/oracle/tts/tts01.dbf

`/home/oracle/tts01.dbf' -> `/home/oracle/tts/tts01.dbf'

SYS@orcl2> exit

/* 이건 하지 말것.

target 과 source 의 endian format이 다를 경우 다음과 같이 convert 한 것을 target 에 전달한다.

[orcl:~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 5 17:36:16 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1324638472)

RMAN> convert tablespace tts

to platform 'Linux IA (32-bit)' format '/home/oracle/tts/%U';2>

Starting conversion at source at 05-SEP-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=134 device type=DISK

channel ORA_DISK_1: starting datafile conversion

input datafile file number=00006 name=/home/oracle/tts01.dbf

converted datafile=/home/oracle/tts/data_D-ORCL_I-1324638472_TS-TTS_FNO-6_04tcc5d3

channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01

Finished conversion at source at 05-SEP-18

Starting Control File and SPFILE Autobackup at 05-SEP-18

piece handle=+FRA/orcl/autobackup/2018_09_05/s_986060196.270.986060199 comment=NONE

Finished Control File and SPFILE Autobackup at 05-SEP-18

RMAN> exit

*/

[orcl2:~]$ expdp system/oracle_4U directory=tts_dir dumpfile=tts.dmp transport_tablespaces=tts

Export: Release 11.2.0.1.0 - Production on Tue Apr 30 11:04:09 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** directory=tts_dir dumpfile=tts.dmp transport_tablespaces=tts

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

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:

  /home/oracle/tts/tts.dmp

******************************************************************************

Datafiles required for transportable tablespace TTS:

  /home/oracle/tts01.dbf

Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 18:09:16

[orcl2:~]$ ls /home/oracle/tts

export.log  tts01.dbf  tts.dmp

[orcl2:~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 5 17:42:46 2018

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SYS@orcl2> alter tablespace tts read write;

Tablespace altered.

SYS@orcl2> exit

3. TARGET DB (orcl)

/* 같은 machine이면 하지 말것.

source 에서 전달 받은 dump file, datafile 을 적당한 경로에 위치하고

directory object를 생성한다.

[orcl:~]$ cp tts.dmp /home/oracle/tts

[orcl:~]$ cp tts01.dbf /home/oracle/tts

*/

SYS@orcl> create directory tts1_dir as '/home/oracle/tts';

Directory created.

/*c

SYS@orcl> ! impdp system/oracle_4U directory=tts1_dir dumpfile=tts.dmp transport_datafiles=/home/oracle/tts/tts01.dbf

*/

SYS@orcl> create user TEST identified by oracle;

User created.

SYS@orcl> grant connect, resource to test;

Grant succeeded.

SYS@orcl> startup force

ORACLE instance started.

Total System Global Area  577511424 bytes

Fixed Size                  1338000 bytes

Variable Size             402654576 bytes

Database Buffers          167772160 bytes

Redo Buffers                5746688 bytes

Database mounted.

Database opened.

/*

SYS@orcl> ! impdp system/oracle_4U directory=tts_dir1 dumpfile=tts.dmp transport_datafiles=/home/oracle/tts/tts01.dbf

*/

SYS@orcl> ! impdp system/oracle_4U directory=tts1_dir dumpfile=tts.dmp transport_datafiles=/home/oracle/tts/tts01.dbf remap_tablespace=tts:tts1 remap_schema=hr:test

Import: Release 11.2.0.1.0 - Production on Tue Apr 30 11:21:11 2019

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/******** directory=tts1_dir dumpfile=tts.dmp transport_datafiles=/home/oracle/tts/tts01.dbf remap_tablespace=tts:tts1 remap_schema=hr:test

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:21:14

SYS@orcl> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

EXAMPLE                        ONLINE

RCAT_TBS                       ONLINE

TTS1                           READ ONLY

8 rows selected.

SYS@orcl> alter tablespace tts1 read write;

Tablespace altered.

SYS@orcl> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

EXAMPLE                        ONLINE

RCAT_TBS                       ONLINE

TTS1                           ONLINE

8 rows selected.