Oracle Live

11/04/2016

Migración – TTS Cross Platform con dbms_file_transport

Filed under: Migraciones — mogukiller @ 6:23 pm

Seguramente alguno de vosotros se haya encontrado delante de una migración que no solo cambiaba de versión sino también de entorno. Los más afortunados seguramente teniais Golden Gate para solventar el problema o una ventana de todo el fin de semana. Pero cuando careces de Golden Gate y tienes que minimizar el tiempo de downtime seguramente te interese este el precedimiento de combinar Trasnportable Tablespace (TTS) con backups incrementales de RMAN.

'Teoria:
	Documento: 2005729.1 (12c): Las bases de datos origien y destino tienen que ser 12c
	Documento: 1389592.1 (11g): La base de datos destino tiene que ser una 11.2.0.4

Los scripts utilizados en el proceso rman-xttconvert_2.0.zip esta adjunto a la nota de Oracle.
	
La idea de utilizar XTTS con incremental backups permite reducir el tiempo de downtime de la base de datos de produccion,
ya que cuando utilizamos XTTS unicamente tenemos que dejar los TS en READ ONLY todo lo que dura el proceso del ultimo incremental.

Los requisitos para el backend destino es que tiene que ser Linux (64bits)
Fases del proceso:

Fase 1: Preparacion (Tablespaces online)
		1.- Backup (level=0) Envido de los datafiles a la DB destino.
		2.- Transformacion de endian.
Fase 2: Roll Fordward (Tablespaces online)
		1.- Crear backup incremental.
		2.- Conversion de endian.
Fase 3: Transport (Tablespace read only)
		1.- Tablespace en origen a READ ONLY.
		2.- Backup incremental y conversion de endian. (Fase 2).
		3.- Exportar metadatos del tablespace por DataPump.
		4.- Importar los metadatos.
		5.- Tablespace en destino a READ WRITE.

 Scripts adjuntos en el documento: rman-xttconvert_2.0.zip
 
Prerrequisitos:
	- DB origen parametro compatible a 10.2 o superior y mayor que DB Destino.
	- DB origen en archivelog.
	- DB destino linux de 64 bits.
	- RMAN a disco y sin configurar los canales como COMPRESS.
	- TS origen deben estar en READ WRITE.
	
Para la transferencia de archivos de origen a destino se pueden utilizar dos metodos:
	- dbms_file_transfer	<< recomendado por oracle
	- RMAN
'
-- ENTORNO:
	
	Origen: 
		SO: Solaris 10	(Big endian)
		DB: Oracle 11.2.0.3
		Schema: MOGU (TS: DATOS, INDICES)
	Destino:
		SO: Red Hat 6.5 (Little endian)
		DB: Oracle 11.2.0.4
	Entradas /etc/hosts:
		192.165.1.5		linux6
		192.165.1.10	solaris10
		
Queremos migrar el usuario MOGU. Miramos en que tablespace tiene objetos.

SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='MOGU';
/*
TABLESPACE_NAME
-----------------
DATOS
INDICES
*/

SELECT PLATFORM_ID, PLATFORM_NAME FROM V$DATABASE;
/*
PLATFORM_ID PLATFORM_NAME
----------- ----------------------------------
         20 Solaris Operating System (x86-64)
*/

-- Comprobar que los tablespaces son totalmente autocontenidos:

EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'DATOS,INDICES', incl_constraints => TRUE);

SELECT * FROM transport_set_violations;
/*
no rows selected
*/
-- Creamos directorios y dblinks

-- Origen
CREATE DIRECTORY EXPORT AS '/export/home/oracle/product/oradata/ORA11GR2/datafile';

-- Destino
CREATE DIRECTORY EXPORT AS '/export/oracle/app/oracle/oradata/PRUEBA';
CREATE PUBLIC DATABASE LINK TTSLINK CONNECT TO SYSTEM IDENTIFIED BY temporal USING 'DB_ORIGEN';

-- Editamos xtt.properties
/*
## Tablespaces to transport
## ========================
##
## tablespaces
## -----------
## Comma separated list of tablespaces to transport from source database
## to destination database.
## Specify tablespace names in CAPITAL letters.
*/
tablespaces=DATOS,INDICES
/*
## Source database platform ID
## ===========================
##
## platformid
## ----------
## Source database platform id, obtained from V$DATABASE.PLATFORM_ID
*/
platformid=20
/*
## Parameters required for Prepare Phase method dbms_file_transfer		<<<<< Para este metodo
## ===============================================================
##
## srcdir
## ------
## Directory object in the source database that defines where the source datafiles currently reside.
*/
srcdir=EXPORT
/*
## dstdir
## ------
## Directory object in the destination database that defines where the destination datafiles will be created.
*/
dstdir=EXPORT
/*
## srclink
## -------
## Database link in the destination database that refers to the source database.
## Datafiles will be transferred over this database link using dbms_file_transfer.
*/
srclink=TTSLINK
/*
## Source system file locations
## ============================
## dfcopydir
## ---------
## This parameter is used only when Prepare phase method is RMAN backup.
##
## Location where datafile copies are created during the "-p prepare" step.
*/
dfcopydir=/export/home/oracle/product/fast_recovery_area/export
/*
## backupformat
## ------------
## Location where incremental backups are created.
*/
backupformat=/export/home/oracle/product/fast_recovery_area/export
/*
## Destination system file locations
## =================================
##
## stageondest
## -----------
## Location where datafile copies are placed by the user when they are transferred manually from the souce system.
## This is also the location from where datafiles copies and incremental
## backups are read when they are converted in the "-c conversion of datafiles"
## and "-r roll forward datafiles" steps.
*/
backupondest=/recovery_area/xtts
/*
## storageondest
## -------------
## This parameter is used only when Prepare phase method is RMAN backup.
##
## Location where the converted datafile copies will be written during the
## "-c conversion of datafiles" step.  This is the final location of the
## datafiles where they will be used by the destination database.
*/
storageondest=/export/oracle/app/oracle/oradata/PRUEBA
/*
## backupondest
## ------------
## Location where converted incremental backups on the destination system
## will be written during the "-r roll forward datafiles" step.
##
*/
backupondest=/recovery_area/xtts

-- Enviamos los scripts al otro entorno.

scp -r xtts oracle@solaris10:/export/home/oracle

-- Definimos en los dos entornos la variable TMPDIR
-- Origen
export TMPDIR=/export/home/oracle/xtts
-- Destino
export TMPDIR=/compartido/xtts

---------------------------------------------------------
-- Fase 1: Preparacion (Metodo dbms_file_transfer)
---------------------------------------------------------

[origen@oracle]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -S
/*
Parsing properties

Done parsing properties

Checking properties

Done checking properties

Starting prepare phase

Prepare source for Tablespaces:
                  'DATOS'  /recovery_area/xtts
xttpreparesrc.sql for 'DATOS' started at Mon Mar 21 17:04:53 2016
xttpreparesrc.sql for  ended at Mon Mar 21 17:04:53 2016
Prepare source for Tablespaces:
                  'INDICES'  /recovery_area/xtts
xttpreparesrc.sql for 'INDICES' started at Mon Mar 21 17:04:53 2016
xttpreparesrc.sql for  ended at Mon Mar 21 17:04:53 2016
Prepare source for Tablespaces:
                  ''  /recovery_area/xtts
xttpreparesrc.sql for '' started at Mon Mar 21 17:04:53 2016
xttpreparesrc.sql for  ended at Mon Mar 21 17:04:54 2016

Done with prepare phase
*/

Se generan los siguientes ficheros
'
-rw-r--r--   1 oracle   oinstall      92 Mar 21 17:04 xttnewdatafiles.txt
-rw-r--r--   1 oracle   oinstall     140 Mar 21 17:04 getfile.sql
'

Enviamos esos dos ficheros al destino

scp xttnewdatafiles.txt linux6:/compartido/xtts
scp getfile.sql linux6:/compartido/xtts

Copiamos los datafiles identificados en el paso anterior

[destino@oracle] $ORACLE_HOME/perl/bin/perl xttdriver.pl -G
/*
Parsing properties

Done parsing properties

Checking properties

Done checking properties

Getting datafiles from source

Executing getfile for getfile_export_o1_mf_datos_cgzgh3hy_.dbf_0.sql

Executing getfile for getfile_export_o1_mf_indices_cgzghv6w_.dbf_1.sql

Completed getting datafiles from source
*/

Resultado: Nos ha copiado los datafiles

'
-rw-r----- 1 oracle oinstall 501M Mar 21 17:52 o1_mf_indices_cgzghv6w_.dbf
-rw-r----- 1 oracle oinstall 1.1G Mar 21 17:52 o1_mf_datos_cgzgh3hy_.dbf
'

La conversion de endian se ha realizado de forma automatica durante esta parte.

---------------------------------------------------------
-- Fase 2: Roll Fordward
---------------------------------------------------------

[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i

/*
Parsing properties

Done parsing properties

Checking properties

Done checking properties

Backup incremental

Prepare newscn for Tablespaces: 'DATOS' 
Prepare newscn for Tablespaces: 'INDICES' 
Prepare newscn for Tablespaces: '' 
rman target /  cmdfile /export/home/oracle/xtts/rmanincr.cmd

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Mar 21 17:58:34 2016

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

connected to target database: ORA11GR2 (DBID=94419887)

RMAN> set nocfau;
2> host 'echo ts::DATOS';
3> backup incremental from scn 1097254 
4>   tag tts_incr_update tablespace 'DATOS'  format
5>  '/export/home/oracle/product/fast_recovery_area/export/%U';
6> set nocfau;
7> host 'echo ts::INDICES';
8> backup incremental from scn 1097254 
9>   tag tts_incr_update tablespace 'INDICES'  format
10>  '/export/home/oracle/product/fast_recovery_area/export/%U';

....

Recovery Manager complete.


Done backing up incrementals

*/

Resultado: Ha generado las piezas del backup incremental y los ficheros de configuracion.

oracle@solaris10:~/product/fast_recovery_area/export$ls -lrth
/*
total 43712
-rw-r-----   1 oracle   oinstall   1008K Mar 21 17:58 01r127ub_1_1
-rw-r-----   1 oracle   oinstall    9.3M Mar 21 17:58 02r127ue_1_1
-rw-r-----   1 oracle   oinstall    1.6M Mar 21 17:58 03r127uh_1_1
-rw-r-----   1 oracle   oinstall    9.3M Mar 21 17:58 04r127ui_1_1
*/
/*
-rw-r--r--   1 oracle   oinstall      54 Mar 21 17:58 tsbkupmap.txt
-rw-r--r--   1 oracle   oinstall     134 Mar 21 17:58 incrbackups.txt
*/

-- Enviamos los piezas del backup incremental al destino

[oracle@orig]$ scp `cat incrbackups.txt` linux6:/recovery_area/xtts
[oracle@orig]$ scp xttplan.txt linux6:/compartido/xtts
[oracle@orig]$ scp tsbkupmap.txt linux6:/compartido/xtts

-- Aplicamos las copias del dbf y los backups incrementales

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r
/*
Parsing properties

Done parsing properties

Checking properties

Done checking properties

Start rollforward

End of rollforward phase

*/

Si queremos repetir los backups incrementales tendremos que calcular el siguiente backup incremental

[oracle@orig]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -s
/*
Parsing properties

Done parsing properties

Checking properties

Done checking properties

Prepare newscn for Tablespaces: 'DATOS' 
Prepare newscn for Tablespaces: 'INDICES' 
Prepare newscn for Tablespaces: '' 
New /export/home/oracle/xtts/xttplan.txt with FROM SCN's generated
*/

Resultado: Actualiza el fichero xttplan.txt para el siguiente backup.

------------------------------------------
-- Fase 3: Transport
------------------------------------------
Ponemos los TS en read only

ALTER TABLESPACE DATOS READ ONLY;
ALTER TABLESPACE INDICES READ ONLY;

Ejecutamos un ultimo backup incremental

[oracle@orig]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -i

[oracle@orig]$ scp `cat incrbackups.txt` linux6:/recovery_area/xtts
[oracle@orig]$ scp xttplan.txt linux6:/compartido/xtts
[oracle@orig]$ scp tsbkupmap.txt linux6:/compartido/xtts

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -r

-- Importamos los metadatos desde destino

[oracle@dest]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -e
/*
Parsing properties

Done parsing properties

Checking properties

Done checking properties

Generating plugin

Done generating plugin file /compartido/xtts/xttplugin.txt
*/

Resultado: Genera el parfile para el impdp

cat >par_imp_metadatos.par 
directory=EXPORT logfile=tts_imp.log
network_link=ttslink
transport_full_check=no
transport_tablespaces=DATOS,INDICES 
transport_datafiles='/export/oracle/app/oracle/oradata/PRUEBA/o1_mf_datos_cgzgh3hy_.dbf','/export/oracle/app/oracle/oradata/PRUEBA/o1_mf_indices_cgzghv6w_.dbf'

impdp \'/ as sysdba \' parfile=par_imp_metadatos.par
'
/*
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01":  "/******** AS SYSDBA" parfile=par_imp_metadatos.par 
Source time zone is +00:00 and target time zone is +01:00.
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at Tue Mar 22 10:59:01 2016 elapsed 0 00:00:21
*/

-- Ponemos los tablespaces en destino como read write

SQL> ALTER TABLESPACE DATOS READ WRITE;
SQL> ALTER TABLESPACE INDICES READ WRITE;

-- Validamos los tablespaces 
RMAN> validate tablespace DATOS, INDICES check logical;
/*
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5    OK     0              130225       131072          18963793  
  File Name: +DATA/TEST12C/DATAFILE/datos_5.xtf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              700             
  Index      0              0               
  Other      0              147             

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              63553        64000           18963823  
  File Name: +DATA/TEST12C/DATAFILE/indices_6.xtf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0               
  Index      0              306             
  Other      0              141      
*/

-- Validamos los objetos
SQL>select object_name, object_type from dba_objects where owner='MOGU';
/*
OBJECT_NAME          OBJECT_TYPE
-------------------- -------------------
CF_TABLE             TABLE
IDX_ID_VALOR         INDEX
*/

1 comentario »


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: