Skip to main content

DB - Transportable Tablespaces x-Platforms


EnglishVersion



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


Versao Portuguesa


Thank you for reading, hope this post was helpful.
Rogerio
Labels:

Comments

Post a Comment

Popular posts from this blog

DB - How to monitor Oracle datapump jobs

These days there is lot of work around database migrations not only but mainly to cloud providers. One of the ways to migrate Oracle databases is using data pump (expdp/impdp). In general works fine, it allows an easy way to bring the database even different OS. It is always good to monitor the progress, to know where we are and estimate how long we are from the end. In this post, I am sharing some queries to help with database monitoring progress. The queries can be easily tailed to various scenarios. Starting with a simple one - the track the number of objects loaded during an import operation. When there is a massive amount of errors during the import. Drop and run the import again. The bellow query can be useful also to monitor the number of objects if we are dropping the users. Here, I’m making the assumption there were no other database activity in progress on the last 24 hours. select owner, count(0) from dba_objects where owner in ( select username from dba_users where created...
Post a Comment
Read more

VM - Partilhar Pastas Windows - Linux

Neste post escrevo como partilhar pastas entre Windows (Host) e uma VM Guest, neste caso Linux. Ha algumas formas de fazer isto, esta parece me a mais facil. Seleccionar a Pasta Windows Na consola Virtual Box > Devices > Shared Folder Seleccionar a pasta windows para partilhar no Linux   O   Montar a pasta Windows na VM Ha duas opcoes : 1) Reiniciar a VM (Auto-mount) 2) Linha de comando [root@host ~]# mkdir -p /u01/stage/win_ebs_sw [root@host ~]# chmod 777 /u01/stage/win_ebs_sw [root@host ~]# mount -t vboxsf Oracle_EBS /u01/stage/win_ebs_sw/ [root@host ~]# ls /u01/stage/win_ebs_sw ebs_weblogic_webtier [root@host ~]# df -h /u01/stage/win_ebs_sw Filesystem Size Used Avail Use% Mounted on Oracle_EBS 895G 494G 401G 56% /u01/stage/win_ebs_sw Guardar a Configuracao no /etc/fstab para tornar as alteracoes permanentes. [root@host ~]# echo "Oracle_EBS /u01/stage/win_ebs_sw vboxsf defaults 0 0" >> /etc/fstab [root@host ~]# cat /etc/fstab # /etc/...
Post a Comment
Read more

DB – How to monitor Oracle database long ops ?

As DBAs, some questions we are often asked and to be fair we ask ourselfs when we are doing massive operations, are : Is the database doing something? How long the database takes to complete the task? There a data dictionary view, GV$SESSION_LONGOPS whom is really usefull for monitoring and to provide estimated time to get the task done. Starting with a first example, a query for general propose . set lines 240 col message form a100 col opname form a50 select * from ( select round(l.sofar/l.totalwork*100,2) as complete, --l.* l.sid, --l.opname, l.message, l.start_time, l.time_remaining/60 "minuts remaining", l.elapsed_seconds from gv$session_longops l where totalwork !=0 ) where complete < 100; The view is also usefull on specific operations such as Oracle Recovery Manager (RMAN) jobs monitoring, this query returns information about backup,restore and recovery. select sid, serial#,opname, context, sofar, totalwork, round(sofar/totalwork*100,2) "%_complet...
Post a Comment
Read more