DB - Transportable Tablespaces x-Platforms
Neste post parilho como copiar dados entre bases de dados em diferentes plataformas.
Como nao tenho uma maquina HP-UX .. vou ter que criar datafile para HP-UX, e depois o inverso
Para isso irei executar os seguintes passos:
1 - Preparacao - Criar um tablespace, user e tabela
2 - Exportar o Tablespace
3 - Gerar the tablespace para 'HP-UX IA (64-bit)'
4 - Apagar Tablespace
5 - Preparacao para importar o tablespace
6 - Converter datafile 'HP-UX IA (64-bit)' > 'Linux IA (32-bit)'
7 - Importar o tablespace e Validar os dados
8 - Validar os dados
1 - 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.
2 - Exportar o Tablespace
Vamos criar uma pasta para o export e forcar um erro ( que nao e bem um erro, um TS tem q estar como read only)
Correr o expdp
E copiar o data file
SQL> create directory DIR_TTS as '/home/oracle';
Directory created.
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
3 - Gerar the tablespace para HP-UX
Ate aqui tudo igual - independentemente da plataforma
Para continuar e simular como se fosse um TBS HP-UX, vou converter o tablespace com RMAN
Para simular o outro systema operativo - vamos criar um datafile 'HP-UX IA (64-bit)'
RMAN> CONVERT TABLESPACE xpto TO PLATFORM 'HP-UX IA (64-bit)' FORMAT '/home/oracle/xpto_%U';
Starting conversion at source at 16-FEB-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00006 name=/home/oracle/xpto.bdf
converted datafile=/home/oracle/xpto_data_D-xTTS_I-2336971890_TS-XPTO_FNO-6_03srcukf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at source at 16-FEB-18
4 - Apagar Tablespace
Para simular outra base de dados / ou ate mesmo como backup/recover test
Vou remover o tablespace
SQL> drop tablespace xpto including contents and datafiles;
Tablespace dropped.
5 - Preparacao para importar o tablespace
Copiar dump e datafiles para path correcto - este passo aplica-se na realidade
Isso pode ser feito usand qualquer comando de copia scp,ftp .. etc
Neste este caso - mesmo servidor
6 - Converter datafile 'HP-UX IA (64-bit)' > 'Linux IA (32-bit)'
Converter datafile 'HP-UX IA (64-bit)' > 'Linux IA (32-bit)'
SQL> SELECT PLATFORM_NAME FROM V$TRANSPORTABLE_PLATFORM
WHERE PLATFORM_ID = ( SELECT PLATFORM_ID FROM V$DATABASE );
PLATFORM_NAME
-----------------------------------------------------------------------------------------------------
Linux IA (32-bit)
RMAN> CONVERT FROM PLATFORM 'HP-UX IA (64-bit)'
DATAFILE '/home/oracle/xpto_data_D-xTTS_I-2336971890_TS-XPTO_FNO-6_03srcukf'
FORMAT '/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b';
Starting conversion at target at 16-FEB-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=/home/oracle/xpto_data_D-xTTS_I-2336971890_TS-XPTO_FNO-6_03srcukf
converted datafile=/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished conversion at target at 16-FEB-18
7 - Importar o tablespace e Validar os dados
Aqui ja e um operacao impdp standard for TTS
usando o dmp file gerado no passo 2 , e o datafile anteriormente
$ impdp DIRECTORY=DIR_TTS DUMPFILE='tts_xpto.dmp' TRANSPORT_DATAFILES='/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b'
Import: Release 11.2.0.1.0 - Production on Fri Feb 16 17:35:14 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=/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b
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 17:35:19
8 - Validar os dados
Apos import estar completo podemos validar os dados
E, colacar o tablespace em "read write"
[oracle@ocm ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 16 17:35:26 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, OLAP, Data Mining and Real Application Testing options
SQL> select count(0) from xpto.users;
COUNT(0)
----------
37
SQL> alter tablespace xpto read write;
Tablespace altered.
SQL> col FILE_NAME form a100
SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME='XPTO';
FILE_NAME TABLESPACE_NAME
---------------------------------------------------------------------------------------------------- ------------------------------
/home/oracle/XPTO_CONVERTED_FROM_HP_2_LINUX-32b XPTO
Obrigado pela leitura, espero que este post o tenha ajudado.
Rogerio
Rogerio
Thank you for reading, hope this post was helpful.
Rogerio
Comments