ODC Appreciation Day: Operaciones Online en Oracle 12.2 y 18c

A lo largo de los años, la base de datos Oracle ha ido evolucionando y ofreciendo nuevas opciones para una mejor administración.  Las funcionalidades con mayor impacto para la labor de los DBAs han sido las operaciones Online.  Gracias a las actividades online, podemos evitar largas ventanas de mantenimiento sobre ambientes productivos en horarios nocturnos o de fin de semana.

Ya desde la versión Oracle 11g pudimos tener acceso a operaciones online como:

•           CREATE INDEX.
•       REBUILD INDEX.

En Oracle 12c Release 1, Oracle continúa mejorando las opciones online y añade nuevas operaciones de gran ayuda como:

•         DROP INDEX.
•         DROP CONSTRAINT.
•         ALTER INDEX UNUSABLE.
•         SET COLUMN UNUSED.
•         MOVE PARTITION & SUBPARTITION.

Sin embargo, a partir de Oracle 12c Release 2 es donde se agregan las operaciones online de mayor impacto como:

• MOVE TABLE.
• SPLIT & MERGE PARTITION.
•  CONVERT NON-PARTITION TABLE TO PARTITION TABLE.

En este artículo quiero explicar en detalle las operaciones online para Oracle 12cR2 y 18c; los casos de uso más comunes y algunas restricciones para cada tipo de operación.

MOVE TABLE ONLINE.

El comando nos permite mover una tabla de manera online agregando la opción ONLINE sobre ALTER TABLE.

Lo mejor de esta operación es que no afecta el estado de los índices; por lo que es posible realizar desfragmentación de tablas sin la necesidad de una ventana de mantenimiento.

Para nuestro ejemplo, creamos una tabla y le agregamos una gran cantidad de datos.  Posteriormente creamos su Primary Key y un índice sobre el campo código.

SQL> create table music.BEATLES (id number, codigo number, fec_registro date, miembro varchar2(42)) tablespace USERS;

Table created.

SQL> insert into music.BEATLES (

select rownum, mod(rownum,1000), sysdate - mod(rownum,100),

decode(mod(rownum,10),1,'PAUL',2,'JOHN',3,'RINGO',4,'GEORGE',5,'STUART',6,'PETE',7,'NORMAN',8,'TOMMY',9,'JIMMIE','THE BEATLES')

from dual connect by level <= 1700000); 

1700000 rows created.

SQL> commit;

Commit complete.

SQL> alter table music.BEATLES ADD CONSTRAINT PK_BEATLES PRIMARY KEY (id);

Table altered.

SQL> create index music.IDX1_BEATLES on music.BEATLES (codigo) tablespace USERS;

Index created.

Finalmente verificamos las características de la tabla y sus índices.

SQL> select owner, table_name, tablespace_name from dba_tables where table_name='BEATLES';

OWNER   TABLE_NAME     TABLESPACE_NAME  COMPRESS

------- -------------- ---------------- --------

MUSIC   BEATLES        USERS            DISABLED

SQL> select OWNER,INDEX_NAME,STATUS, CLUSTERING_FACTOR, NUM_ROWS, TABLESPACE_NAME from dba_indexes where table_name='BEATLES';

OWNER   INDEX_NAME     STATUS   CLUSTERING_FACTOR   NUM_ROWS TABLESPACE_NAME

------- -------------- -------- ----------------- ---------- ----------------

MUSIC   PK_BEATLES     VALID                 6800    1700000 USERS

MUSIC   IDX1_BEATLES   VALID              1700000    1700000 USERS

Procedemos a realizar el movimiento de la tabla al tablespace DATA y verificamos que los índices se mantienen validos después del movimiento.

SQL> ALTER TABLE music.BEATLES MOVE ONLINE tablespace DATA;

Table altered.

SQL> select owner, table_name, tablespace_name from dba_tables where table_name='BEATLES';

OWNER    TABLE_NAME     TABLESPACE_NAME

-------- -------------- ----------------

MUSIC    BEATLES        DATA

SQL> select OWNER,INDEX_NAME,STATUS, CLUSTERING_FACTOR, NUM_ROWS, TABLESPACE_NAME from dba_indexes where table_name='BEATLES';

OWNER    INDEX_NAME     STATUS   CLUSTERING_FACTOR   NUM_ROWS TABLESPACE_NAME

-------- -------------- -------- ----------------- ---------- --------

MUSIC    PK_BEATLES     VALID                13524    1700000 USERS

MUSIC    IDX1_BEATLES   VALID              1579897    1700000 USERS

Podemos utilizar el movimiento de una tabla de manera Online para activar la compresión e incluir la reconstrucción de los índices (sobre diferentes tablespaces). Todo ejecutado desde el mismo comando con la opción UPDATE INDEXES. 

SQL>  ALTER TABLE music.BEATLES MOVE ONLINE COMPRESS tablespace DATA

    UPDATE INDEXES (

    music.PK_BEATLES tablespace DATA,

    music.IDX1_BEATLES tablespace USERS);

Table altered.

SQL> select owner, table_name, tablespace_name,COMPRESSION from dba_tables where table_name='BEATLES';

OWNER     TABLE_NAME    TABLESPACE_NAME    COMPRESS

--------- ------------- ------------------ --------

MUSIC     BEATLES       DATA               ENABLED

SQL> select OWNER,INDEX_NAME,STATUS, CLUSTERING_FACTOR, NUM_ROWS, TABLESPACE_NAME from dba_indexes where table_name='BEATLES';

OWNER    INDEX_NAME     STATUS   CLUSTERING_FACTOR   NUM_ROWS TABLESPACE_NAME

-------- -------------- -------- ----------------- ---------- ---------------

MUSIC    PK_BEATLES     VALID                10846    1700000 DATA

MUSIC    IDX1_BEATLES   VALID              1386859    1700000 USERS

Finalmente podemos reorganizar la tabla de tal manera que valor de Clustering Factor se reduzca para columnas indexadas (diferentes al Primary Key). Usamos el concepto de CLUSTERING ATTRIBUTE.

SQL> alter table music.BEATLES ADD CLUSTERING by linear order(codigo) without materialized zonemap;

Table altered.

SQL> alter table music.BEATLES MOVE ONLINE NOCOMPRESS tablespace DATA UPDATE INDEXES;

Table altered.

SQL> select OWNER,INDEX_NAME,STATUS, CLUSTERING_FACTOR, NUM_ROWS, TABLESPACE_NAME from dba_indexes where table_name='BEATLES';

OWNER    INDEX_NAME     STATUS   CLUSTERING_FACTOR   NUM_ROWS TABLESPACE_NAME

-------- -------------- -------- ----------------- ---------- ---------------

MUSIC    PK_BEATLES     VALID              1687292    1700000 DATA

MUSIC    IDX1_BEATLES   VALID                 6140    1700000 USERS

Es importante indicar que el movimiento de tablas Online tiene las siguientes restricciones:

• No es posible ejecutar este comando sobre tablas particionada de tipo IOT y tablas que contengan índices de tipo Domain.
• No se puede realizar DML con paralelismo e inserciones masivas (direct path) mientras se ejecuta el movimiento Online.
• No es posible ejecutar este comando para tablas de tipo IOT con columnas LOB, VARRAY o de tipo User-Defined.

CONVERTIR NON-PARTITIONED TABLE TO PARTITIONED TABLE.

En versiones anteriores es posible convertir una tabla no particionada a particionada con comandos como Exchange Partition o DBMS_Redefinition, sin embargo, estos métodos pueden llegar a ser complejos y con validaciones previas a la conversión.

A partir de Oracle 12cR2 es posible convertir una tabla no particionada a particionada a través de un solo comando y de manera Online.  Podemos definir diferentes características para cada partición que se vaya a crear.

SQL> alter table music.BEATLES MODIFY 
    PARTITION BY RANGE (fec_registro) (

     partition T1_07_2018 values less than (TO_DATE('01-AUG-2018','DD-MON-YYYY')) tablespace USERS COMPRESS,

     partition T2_08_2018 values less than (TO_DATE('01-SEP-2018','DD-MON-YYYY')) tablespace DATA RESULT_CACHE (mode default),

     partition T3_09_2018 values less than (TO_DATE('01-OCT-2018','DD-MON-YYYY')) tablespace USERS,

     partition T4_10_2018 values less than (TO_DATE('01-NOV-2018','DD-MON-YYYY')) tablespace DATA

) ONLINE;

Table altered.

Si la tabla contiene índices, es posible definir el particionamiento de cada índice con la opción UPDATE INDEXES.  Si esta opción no se define (como en el comando anterior) los índices son redefinidos de la siguiente manera:

-       Índices NonUnique se convierten en LOCAL INDEXES.

-       Índices Unique y que forman parte del rango de partición se convierten en LOCAL INDEXES.

-       Índices Unique y que no forman parte del rango de partición se convierten en GLOBAL INDEXES.

Para nuestro ejemplo vamos a definir diferentes tipos de particionamiento por cada índice.

SQL> alter table music.BEATLES MODIFY
     PARTITION BY RANGE (fec_registro) (

     partition T1_07_2018 values less than (TO_DATE('01-AUG-2018','DD-MON-YYYY')) tablespace USERS READ ONLY,

     partition T2_08_2018 values less than (TO_DATE('01-SEP-2018','DD-MON-YYYY')) tablespace DATA READ ONLY,

     partition T3_09_2018 values less than (TO_DATE('01-OCT-2018','DD-MON-YYYY')) tablespace USERS,

     partition T4_10_2018 values less than (TO_DATE('01-NOV-2018','DD-MON-YYYY')) tablespace DATA

 ) ONLINE

 UPDATE INDEXES(

 music.PK_BEATLES GLOBAL PARTITION BY HASH (ID) PARTITIONS 4,

 music.IDX1_BEATLES LOCAL

 );

Table altered.

Finalmente es posible tomar solo una parte de la información de la tabla para el particionamiento. Usamos la opción INCLUDING ROWS.  Esta opción borra los registros que no cumplen con la condición definida.

Para nuestro ejemplo vamos a particionar la tabla, pero solo incluimos los valores a partir de agosto 2018.  Se puede observar que los valores antes de dicha fecha son eliminados de la tabla.

SQL> alter table music.BEATLES MODIFY PARTITION BY RANGE (fec_registro) (

     partition T2_08_2018 values less than (TO_DATE('01-SEP-2018','DD-MON-YYYY')) tablespace DATA COMPRESS,

     partition T3_09_2018 values less than (TO_DATE('01-OCT-2018','DD-MON-YYYY')) tablespace USERS,

     partition T4_10_2018 values less than (TO_DATE('01-NOV-2018','DD-MON-YYYY')) tablespace USERS

 ) ONLINE

 UPDATE INDEXES(

 music.PK_BEATLES GLOBAL,

 music.IDX1_BEATLES LOCAL

 )

 INCLUDING ROWS WHERE fec_registro >= TO_DATE('01-AUG-2018','DD-MON-YYYY');

Table altered.

SQL> select fec_registro, count(1)

     from music.BEATLES

     where fec_registro < TO_DATE('01-AUG-2018','DD-MON-YYYY')

     group by fec_registro;

no rows selected

La conversión a tabla particionada de manera Online también tiene algunas restricciones como:

•         No es posible ejecutar este comando sobre tablas de tipo IOT.
•         No se pude utilizar el comando sobre tablas que poseen índices de tipo Domain.
•         No es posible particionar una tabla hija de tipo Reference-Partitioned de manera Online.

MERGE & SPLIT ONLINE

Una vez que se tiene una tabla particionada, es posible realizar tareas de mantenimiento como MERGE o SPLIT de manera online. 

Estas operaciones requieren de un bloqueo de tipo X DML sobre las particiones involucradas, es por ello que ambas operaciones son completadas sólo cuando todos los DML, iniciados antes de la operación online, finalizan.

Verificamos las particiones de nuestra tabla.

SQL> select TABLE_OWNER,  TABLE_NAME, PARTITION_NAME,PARTITION_POSITION, NUM_ROWS from dba_tab_partitions where TABLE_NAME='BEATLES';

TABLE_OWNE TABLE_NAME           PARTITION_ PARTITION_POSITION   NUM_ROWS

---------- -------------------- ---------- ------------------ ----------

MUSIC      BEATLES              T1_07_2018                  1     510000

MUSIC      BEATLES              T2_08_2018                  2     527000

MUSIC      BEATLES              T3_09_2018                  3     510000

MUSIC      BEATLES              T4_10_2018                  4     153000

Desde la versión Oracle 12cR1 es posible realizar SPLIT y MERGE de múltiples particiones.  A partir de Oracle 12cR2 es posible realizar dichas operaciones múltiples de manera Online.

La operación SPLIT se puede realizar Online a partir de Oracle 12cR2, mientras que la operación MERGE solo se puede realizar de manera Online a partir de la versión 18c.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> alter table music.BEATLES

     MERGE PARTITIONS T1_07_2018, T2_08_2018 INTO PARTITION T12_07_08_2018

     ONLINE UPDATE INDEXES;

     ONLINE UPDATE INDEXES

     *

ERROR at line 3:

ORA-14020: this physical attribute may not be specified for a table partition

SQL> alter table music.BEATLES

     MERGE PARTITIONS T1_07_2018, T2_08_2018 INTO PARTITION T12_07_08_2018;

Table altered.

SQL> alter table music.BEATLES SPLIT PARTITION T12_07_08_2018 at (TO_DATE('01-AUG-2018','DD-MON-YYYY'))

INTO (PARTITION T1_07_2018, PARTITION T2_08_2018)

ONLINE UPDATE INDEXES;

Table altered.

Connected to:

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production

Version 18.1.0.0.0

SQL> alter table music.BEATLES

     MERGE PARTITIONS T1_07_2018, T2_08_2018 INTO PARTITION T12_07_08_2018

     ONLINE UPDATE INDEXES;

Table altered.

EXTRA: MOVE DATAFILE ONLINE

La opción de mover datafiles de manera Online es una funcionalidad presente desde la versión Oracle 12c Release 1, sin embargo, me pareció importante agregarla como una funcionalidad extra debido su importancia en tareas de mantenimiento como:

• Migración de Filesystem a ASM o viceversa.
• Reorganización de datafiles en diferentes Filesystems o Diskgroups.

SQL>  alter database move datafile '+DATA/APEXDB/DATAFILE/sysaux.304.977269941' to '/u02/oradata/apexdb/sysaux_01.dbf';

Database altered.

SQL> alter database move datafile '/u02/oradata/apexdb/sysaux_01.dbf' to '+DATA';

Database altered.

•  Renombramiento de datafiles.

SQL> alter database move datafile '+DATA/APEXDB/DATAFILE/data.263.989013263' to '+DATA/APEXDB/DATAFILE/DATA_01.dbf';

Database altered.

En este articulo hemos podido explicar las diferentes operaciones online en cada una de las versiones de base de datos Oracle.  Sin embargo, es importante resaltar que las operaciones Online a partir de Oracle 12cR2 tienen un impacto muy importante en las tareas de mantenimiento que los DBAs realizamos en ambientes Críticos que no permiten indisponibilidad.