Oracle BBDD: Como copiar una o más particiones de una tabla entre dos BBDDs

CASO DE USO

Tenemos una tabla con cientos de millones de filas y queremos copiar una partición de la misma a otra base de datos para trabajar con un subconjunto de la tabla principal.

QUÉ NECESITAMOS?

Para este taller hemos utilizado una instalación de 12c en RAC mediante lo indicado aquí.

Medios: Con un ordenador intel i5, 16Gb de RAM y 100gb de disco SSD libre podremos realizar el ejercicio satisfactoriamente.

OPERACIONES EN LA CDB

Creamos dos PDB’s y las abrimos:

create pluggable database “PDB1” admin user “PDBADMIN” identified by welcome1;

alter pluggable database “PDB1” open read write instances = all;

create pluggable database “PDB2” admin user “PDBADMIN” identified by welcome1 ;

alter pluggable database “PDB2” open read write instances = all;

OPERACIONES EN LA BBDD DE ORIGEN

• Creamos un tablespace y un usuario con privilegios suficientes:

create tablespace mysales_prt datafile  size 50M;

create user sales identified by welcome1;

grant dba to sales;

• Creamos una tabla particionada y la cargamos con datos:

CREATE TABLE “SALES”.”MYSALES_PRT”
(    “PROD_ID” NUMBER(6,0),
“CUST_ID” NUMBER,
“TIME_ID” DATE,
“CHANNEL_ID” CHAR(1 BYTE),
“PROMO_ID” NUMBER(6,0),
“QUANTITY_SOLD” NUMBER(3,0),
“AMOUNT_SOLD” NUMBER(10,2)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “MYSALES_PRT”
PARTITION BY RANGE (“TIME_ID”)
(PARTITION “SALES_Q1_2000”  VALUES LESS THAN (TO_DATE(‘ 2000-04-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “MYSALES_PRT” ,
PARTITION “SALES_Q2_2000”  VALUES LESS THAN (TO_DATE(‘ 2000-07-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “MYSALES_PRT” ,
PARTITION “SALES_Q3_2000”  VALUES LESS THAN (TO_DATE(‘ 2000-10-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “MYSALES_PRT” ,
PARTITION “SALES_Q4_2000”  VALUES LESS THAN (TO_DATE(‘ 2001-01-01 00:00:00’, ‘SYYYY-MM-DD HH24:MI:SS’, ‘NLS_CALENDAR=GREGORIAN’)) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “MYSALES_PRT” ) ;

• Insertamos dos filas de datos, cada una en diferente partición:

SET DEFINE OFF;
Insert into sales.MYSALES_prt (PROD_ID,CUST_ID,TIME_ID,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD) values (1,1,to_date(’12-MAR-00′,’DD-MON-RR’),’A’,1,10,100);
Insert into sales.MYSALES_prt (PROD_ID,CUST_ID,TIME_ID,CHANNEL_ID,PROMO_ID,QUANTITY_SOLD,AMOUNT_SOLD) values (2,1,to_date(’12-JUN-00′,’DD-MON-RR’),’A’,1,20,100);

• Generamos filas adicionales:

begin
for i in 1..10
loop
insert into sales.mysales_prt select * from sales.mysales_prt;
end loop;
commit;
end;

• Veamos de forma agregada los datos que hemos cargado en la tabla:

select count(*),prod_id from sales.mysales_prt group by prod_id;

COUNT(*)    PROD_ID
———- ———-
16384          1
16384          2

• Creamos los directorios par la exportación:

create or replace directory datapumpdir as ‘/u01/fmw/datapump’;
CREATE or REPLACE DIRECTORY datapumpdirlog as ‘/u01/fmw/datapump/’;

GRANT READ, WRITE ON DIRECTORY datapumpdir to sales;
GRANT READ, WRITE ON DIRECTORY datapumpdirlog to sales;

• Preparamos el tablespace para exportar:

alter tablespace MYSALES_prt read only;

• Ahora ejecutamos la siguinte línea de comandos:

expdp sales/welcome1@ol6-121-scan:1521/pdb1 dumpfile=mysales_prt.dmp  directory=datapumpdir tables=sales.mysales_prt:sales_q1_2000 transportable=always  logfile=datapumpdirlog:exp.log

• Debemos fijarnos en la salida de expdp:

Export: Release 12.1.0.2.0 – Production on Sun Dec 14 15:35:39 2014

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Starting “SALES”.”SYS_EXPORT_TABLE_01″: sales/********@ol6-121-scan:1521/pdb1 dumpfile=mysales_prt.dmp directory=datapumpdir tables=sales.mysales_prt:sales_q1_2000 transportable=always logfile=datapumpdirlog:exp.log
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table “SALES”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SALES.SYS_EXPORT_TABLE_01 is:
/u01/fmw/datapump/mysales_prt.dmp
******************************************************************************
Datafiles required for transportable tablespace MYSALES_PRT:
+DATA/CDBRAC/0A249A8EBA747693E0536638A8C03742/DATAFILE/mysales_prt.300.866266551
Job “SALES”.”SYS_EXPORT_TABLE_01″ successfully completed at Sun Dec 14 15:36:49 2014 elapsed 0 00:00:50

OPERACIONES EN LA BBDD DE DESTINO

create user sales identified by welcome1;

grant dba to sales;
create or replace directory datapumpdir as ‘/u01/fmw/datapump’;
CREATE or REPLACE DIRECTORY datapumpdirlog as ‘/u01/fmw/datapump/’;

GRANT READ, WRITE ON DIRECTORY datapumpdir to sales;
GRANT READ, WRITE ON DIRECTORY datapumpdirlog to sales;

COPIAMOS LOS DATAFILES

• El fichero de exportación lo hemos puesto en disco compartido. Para copiar el datafile de la partición exportada utilizaremos ASMCMD:

./grid_env

asmcmd cp ‘+DATA/CDBRAC/0A249A8EBA747693E0536638A8C03742/DATAFILE/mysales_prt.300.866266551‘  ‘+DATA/CDBRAC/0A249A8EBA747693E0536638A8C03742/DATAFILE/mysales_prt.300.866266551-copia’‘

• Ahora importamos la partición exportada:

impdp sales/welcome1@ol6-121-scan:1521/pdb2 dumpfile=’mysales_prt.dmp’ directory=datapumpdir transport_datafiles=’+DATA/CDBRAC/0A249A8EBA747693E0536638A8C03742/DATAFILE/mysales_prt.300.866266551-copia logfile=datapumpdirlog:imp.log

• La salida de impdp:

Import: Release 12.1.0.2.0 – Production on Sun Dec 14 15:42:17 2014

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Master table “SALES”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully loaded/unloaded
Starting “SALES”.”SYS_IMPORT_TRANSPORTABLE_01″: sales/********@ol6-121-scan:1521/pdb2 dumpfile=mysales_prt.dmp directory=datapumpdir transport_datafiles=+DATA/CDBRAC/0A249A8EBA747693E0536638A8C03742/DATAFILE/mysales_prt.300.866266551-bis logfile=datapumpdirlog:imp.log
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job “SALES”.”SYS_IMPORT_TRANSPORTABLE_01″ successfully completed at Sun Dec 14 15:42:33 2014 elapsed 0 00:00:12

DE NUEVO EN LA BBDD DE ORIGEN

• Volvemos a poner el tablespace en modo read write:

alter tablespace MYSALES_prt read write;

COMPROBACIÓN

Si examinamos las tablas del esquema sales en pdb2 veremos que se crea una tabla por cada partición importada. En nuestro caso sólo se carga mysales_prt_sales_q1_2000, pero si realizamos una exportación indicando más de una partición (tables=sales.mysales_prt:sales_q1_2000, sales.mysales_prt:sales_q2_2000), veremos que se crean dos tablas en pdb2:

select table_name from all_tables where owner=’SALES’;

TABLE_NAME
——————————————————————————————————————————–
MYSALES_PRT_SALES_Q2_2000
MYSALES_PRT_SALES_Q1_2000

CONCLUSIÓN
Hemos copiado una partición entre dos bases de datos en un RAC versión 12c de dos nodos. Con esta operación no es necesario trasladar la tabla entera.