DB - Transportable Tablespaces

EnglishVersion

A Oracle disponibiliza uma forma interessante de copiar aplicacoes / dados entre as bases de dados, chama-se TTS ( Transportable Tablespace).
Basicamente exporta-se um dump com metadata do tablespace. Com esse dump + data file (s) e possivel carregar os dados noutra base de dados.

Isto usado expdp + impdp, e commandos standard para copiar o dump e datafile(s) para o destino : cp,scp, ftp,etc
Ref : https://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces013.htm#ADMIN11394

Preparacao

Criar Tablespace : xpto : Data File : xpto.bdf
Criar user teste : xpto
Tabela : xpto.users

SQL> create tablespace xpto datafile 'xpto.bdf' size 10m autoextend on next 10m maxsize 100m;
Tablespace created.

SQL> create user xpto identified by xpto default tablespace xpto quota unlimited on xpto;
User created.

SQL> create table xpto.users as select * from dba_users;
Table created.

expdp

1 - Criar uma pasta para o export
2 - Forcar um erro ( que nao e bem um erro, um TS tem q estar como read only)
3 - Copiar o data file

SQL> create directory DIR_TTS as '/home/oracle';
Directory created.

SQL> !expdp DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES='XPTO' DUMPFILE=tts_xpto.dmp
Export: Release 11.2.0.1.0 - Production on Fri Feb 16 16:32:15 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES=XPTO DUMPFILE=tts_xpto.dmp
ORA-39123: Data Pump transportable tablespace job aborted
ORA-39185: The transportable tablespace failure list is

ORA-29335: tablespace 'XPTO' is not read only
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" stopped due to fatal error at 16:32:22

SQL> alter tablespace xpto read only;
Tablespace altered.

SQL> !expdp DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES='XPTO' DUMPFILE=tts_xpto.dmp

Export: Release 11.2.0.1.0 - Production on Fri Feb 16 16:32:50 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA DIRECTORY=DIR_TTS TRANSPORT_TABLESPACES=XPTO DUMPFILE=tts_xpto.dmp
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/home/oracle/tts_xpto.dmp
******************************************************************************
Datafiles required for transportable tablespace XPTO:
/u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf
Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 16:33:36

SQL> !cp /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf /home/oracle

SQL> !ls /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf /home/oracle/*xpto*
/home/oracle/tts_xpto.dmp /home/oracle/xpto.bdf /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf

SQL> !ls -l /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf /home/oracle/*xpto*
-rw-r----- 1 oracle oracle 90112 Feb 16 16:33 /home/oracle/tts_xpto.dmp
-rw-r----- 1 oracle oinstall 10493952 Feb 16 16:34 /home/oracle/xpto.bdf
-rw-r----- 1 oracle oracle 10493952 Feb 16 16:32 /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf

Teste - Apagar o tablespace

Para simular outra base de dados / ou ate mesmo como teste backup/recover vou remover o tablespace

SQL> drop tablespace xpto including contents and datafiles;
Tablespace dropped.

impdp

1 - colocar os data files no path correcto
2 - Correr o impdp
3 - Validar os dados da tabela xpto.users;

SQL> !cp /home/oracle/xpto.bdf /u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf

SQL> !impdp DIRECTORY=DIR_TTS DUMPFILE='tts_xpto.dmp' TRANSPORT_DATAFILES='/u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf'

Import: Release 11.2.0.1.0 - Production on Fri Feb 16 16:44:45 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": /******** AS SYSDBA DIRECTORY=DIR_TTS DUMPFILE=tts_xpto.dmp TRANSPORT_DATAFILES=/u01/app/oracle/Database/11.2.0/orcl/dbs/xpto.bdf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 16:44:50

SQL> select count(0) from xpto.users;

COUNT(0)
----------
37

Obrigado pela leitura, espero que este post o tenha ajudado.
Rogerio