Introducción a ASM

ASM son las siglas de Automatic Storage Management, que es un producto de Oracle que hace de gestor de volúmenes lógicos y/o sistema de ficheros para albergar los ficheros de las bases de datos de la misma compañía. De esta forma, cuando se crea una base de datos Oracle se puede escoger que el almacenamiento para sus archivos sea ASM en vez de un sistema de archivos normal. ¿Pero qué es en realidad ASM? ASM es una instancia Oracle, con su SGA y sus procesos background, que interactúan con las instancias de la base de datos "cliente" para proporcionarle la información necesaria para que pueda leer y escribir en los archivos de datos (datafiles, control files, redo logs, archived logs, flashback logs, spfiles, RMAN backups, Incremental tracking bitmaps y datapumps dumpfiles). Es importante notar que ASM no sirve los bloques de datos a las instancias "cliente", sino que son ellas mismas las que acceden a los discos para leer o escribir. ASM lo único que hace es proporcionarle metainformación sobre dónde leer o escribir los datos.

La instancia ASM, como instancia que es, en vez de montar una base de datos monta disk groups, que son agrupaciones de ASM disks que se usan de forma transparente como si fuera uno sólo. Para verlo con más facilidad, es como si los disk groups fueran tablespaces y los ASM disks, datafiles. Los ASM disks pueden ser LUNs, discos físicos, particiones de discos físicos o incluso volúmenes lógicos aunque Oracle no recomienda esto último porque existiría solapamiento de funciones entre el volume manager instalado y el propio ASM.

ASM puede dar servicio tanto a una base de datos única como a un RAC (Real Application Cluster), que de hecho es donde es más habitual instalarlo. En un RAC, ASM corre en cada nodo y se comunica con el resto de ASMs del RAC a través de la interconexión, pero no nos perdamos en la configuración de RAC y ASM.

Retomando el término disk group, como hemos dicho, un fichero de las bases de datos "cliente" se asigna a un único disk group (un fichero sólo se asocia a un disk group, pero un disk group tiene multitud de ficheros). Las extensiones de los ficheros se distribuyen homogéneamente entre los ASM disks que conforman el disk group equilibrando así la carga de trabajo de los discos y mejorando el rendimiento general del sistema. Normalmente, las instancias usan sólo 2 disk groups para alojar todos sus archivos, una para datos y otra para fast recovery area.

Otra de las funcionalidades de ASM es que permite añadir o eliminar en caliente ASM disks redistribuyendo el contenido del disk group entre los ASM disks que haya. Si se quiere eliminar un ASM disk, antes ASM tiene que redistribuir los datos del disco a eliminar entre los restantes. Si se añade un ASM disk, ASM redistribuye el contenido del disk group entre todos los ASM disks, dado que ahora hay uno más. Lo más interesante es que esta operación la hace ASM en caliente permitiendo a las instancias acceder a sus ficheros mientras se está produciendo el rebalanceo.

Además de poder hacer redistribución (striping), ASM puede hacer redundancia (mirroring) de los datos. Para ello, al crear un disk group se indica el failure group asociado y ASM distribuye cada copia a un fail group distinto. La ídea del fail group es coger los ASM disks del disk group y hacer grupos distintos, los llamados fail groups, de forma que cada grupo esté protegido de los fallos que pueda tener el otro. Ahora bien, en función del tipo de contingencia para la que nos queramos proteger tendremos una agrupaciones u otras. Por ejemplo, un disk group con dos ASM disks podemos definirlo con dos fail groups, cada uno con un sólo disco. Así, la avería de un disco no me dejaría sin datos (tengo otro réplica del afectado). Pero si hay una avería de la controladora de los discos, que es común para ambos, nos quedaríamos sin acceso a los datos. Entonces habría que escoger discos distintos en distintas controladoras. ¿Y si se avería el hardware en donde residen ambas controladoras?. De acuerdo, pues usamos dispositivos de almacenamiento absolutamente distintos. ¿Y si se prende fuego el CPD?.... y así podemos seguir hasta el infinito.

En relación a la redundancia, también se puede hacer réplica de los datos duplicándolos o triplicándolos. Cuando se define el disk group se indica si la replicación será EXTERNAL (sin replicación ASM), NORMAL (se duplican los datos) o HIGH (se triplican los datos). En una configuración NORMAL serán necesarios dos fail groups y en HIGH, tres fail groups.

Por último y para acabar esta breve introducción, sólo decir ASM se puede integrar completamente con OMF (Oracle Managed Files) escribiéndo únicamente el nombre del disk group allí donde se tendría que poner el nombre completo del archivo.

Continuaré escribiendo cosas de ASM, esto es sólo ha sido una breve introducción para coger nociones de qué es y para qué sirve.


Juan Lorenzo Arellano.
Infor Consult Soluciones.
www.inforconsult.es

Single Cliente Access Name (SCAN)

En Oracle11g Release 2 se ha creado un nuevo esquema de conexión SQL*Net para la arquitectura Real Application Cluster (RAC), donde los clientes no conectan a los listener locales de los nodos del cluster directamente; ahora se usan unas direcciones IPs, llamadas direcciones SCAN (normalmente son tres), que son asociadas con un mismo nombre de dominio o nombre del cluster, también llamado nombre SCAN, de forma que cuando un cliente interroga al DNS por ese dominio, éste responde con una IP distinta cada vez (de entre las tres configuradas) y de forma cíclica.

Durante la instalación del RAC se crean los tres listener que escuchan por las tres IPs SCAN y es Oracle Clusterware el que reparte estos listener por los nodos del cluster según sus algoritmos de reparto de recursos en función de la carga de los nodos. Los clientes conectan al cluster usando el nombre SCAN que el DNS resolverá por una de tres IPs, lo que hará que los clientes conecten al listener SCAN que escucha por dicha IP. Los listener SCAN conocen los servicios ofrecidos por el cluster (a través del parámetro REMOTE_LISTENER de cada instancia), qué instancias ofrecen dichos servicios y con qué nivel de saturación, de forma que el listener SCAN redirecciona la petición al listener local del nodo que atiende el servicio solicitado por el cliente con la menor carga de trabajo.

De esta forma si añadimos o eliminamos nodos del cluster los clientes no se ven afectados porque seguirán conectando al nombre SCAN y cuyo listener SCAN redireccionará al listener del nodo adecuado. Además, dado que Oracle Clusteware mueve los listener SCAN entre los nodos, si quitamos un nodo en el que está corriendo un listener SCAN éste será movido a otro nodo y el cliente no se enterará de la operación resultando transparente para él.

Las instancias del cluster saben siempre en qué nodos están corriendo los listener SCAN por medio del parámetro REMOTE_LISTENER, cuyo valor es dirección_scan:puerto_scan. Las instancias del cluster resuelven la dirección_scan por las tres IPs, y la correspondencia entre la IP SCAN y el nodo en donde reside dicha IP (recuérdese que Oracle Clusterware mueve los listener SCAN entre los nodos) se hace a través del protocolo TCP/IP ARP. Por tanto, cuando Oracle Clusteware mueve los listener SCAN de un nodo a otro, ni en los clientes ni en las instancias se debe modificar nada porque el cambio se hace por debajo del nivel de applicación.

Para gestionar los listener SCAN se debe usar la herramienta del clusterware srvctl:

Saber el estado de las IPs SCAN:

c:\> srvctl status scan
SCAN VIP scan1 is enabled
SCAN VIP scan1 is running on node
SCAN VIP scan2 is enabled
SCAN VIP scan2 is running on node
SCAN VIP scan3 is enabled
SCAN VIP scan3 is running on node

Saber el estado de los listener SCAN:

c:\> srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node

Parar los listener SCAN:

c:\> srvctl stop scan_listener [-f force] --> para todos los listener SCAN
c:\> srvctl stop scan_listener -i 2 --> para el listener 2

Arrancar todos los listener SCAN:

c:\> srvctl start scan_listener --> arranca los listener SCAN en los nodos que el clusterware considere.
c:\> srvctl start scan_listener -n nombre_del_nodo --> arranca los listener SCAN en el nodo indicado
c:\> srvctl start scan_listener -i 2 [-n nombre_del_nodo] --> arranca el listener 2 [en el nodo indicado]

Finalmente, sólo añadir que la redirección que hacen los listener SCAN hacia los listener locales de los nodos se hace a través de la dirección IP virtual o VIP, es decir, los listener locales de los nodos escuchan tanto por la IP pública como por la VIP. Esto permite poder seguir usando conexiones clientes como hasta ahora, es decir, usando en el descriptor de conexión la IP virtual del nodo al que queremos conectarnos sin preocuparnos por SCAN para nada. Esto es especialmente valioso si tenemos en cuenta que para que un cliente pueda usar SCAN debe tener versión Oracle11g Release 2 o superior, de forma que tras una migración de nuestro servidor a Oracle11g nuestros clientes pueden permanecer inalterados, aunque no es lo recomendable.

Juan Lorenzo Arellano
Infor Consult Soluciones.
www.inforconsult.es

Habilitar y deshabiltar las constraints automáticamente

Muchas veces nos encontramos con la necesidad de tener que borrar el contenido de ciertas tablas o simplemente de todo nuestro esquema y debido a las restricciones/constraints de integridad referencial, más conocidad por Foreign Key -FK-, la tarea se vuelve pesada o sencillamente inabordable. En estos casos hay que tener muy claro el esquema de restricciones para ir borrando primero las tablas sin dependencias (las tablas hijas) e ir llegando a las tablas maestras.

Si no tenemos este esquema claro, no tenemos tiempo... etc. Podemos manejar esta situación deshabilitando todas las FK, lo que nos permitirá borrar el contenido de las tablas en el orden que queramos, y volviéndolas a habilitar. Una vez más, tener que teclear una por una las sentencias que deshabilitan cada FK es una tarea pesada y lenta. Para este propósitito podemos usar SQL, haciéndo que él mismo nos dé todas las instrucciones. De esta forma, nosotros tecleamos una sentencia que como resultado nos da "n" sentencias. Éstas las capturamos en un spool y lo lanzamos. Todo esto lo tengo automatizado en el siguiente script.

--------- INICIO DEL SCRIPT -------
set echo off

-- Al comienzo nos pide el usuario que posee las restriciones. Si somos nosotros mismos no tenemos que teclear nada.
ACCEPT usuario CHAR DEFAULT 'user' PROMPT 'Usuario (Enter para ninguno): '

-- Ahora nos pide si queremos habilitar o deshabilitar las constraints.
ACCEPT objeto CHAR DEFAULT 'ENABLE' PROMPT 'Si desea **deshabilitar** las restricciones escriba: DISABLE. ENTER para **habilitar**: '

-- Guardamos la sentencia en el buffer SQL
select 'ALTER TABLE ' ||
nvl('&usuario.', user ) ||'.'|| table_name ||
' &objeto. constraint ' || constraint_name || ';'
from dba_constraints
where owner = nvl(upper('&usuario.'), user )
and constraint_type = 'R'
.

-- Configuramos algunos parámetros SQLPlus.
set timing off feed off pagesize 0 verify off

-- Abrimos el spool. Esto escribirá en la carpeta temporal del sistema. Después ejecutamos la sentencia del buffer y cerramos el spool.
spool %TEMP%\constraints.sql
/
spool off

-- Ponemos los parámetros igual que estaban.
set timing on feed on pagesize 100 verify on

-- Sacamos unos mensajes aclaratorios.
prompt ******************* PRECAUCIÓN ********************
pause Se van a lanzar las sentencias que deshabilitan las Foreign Key. Si no desea seguir pulse Ctrl+C
pause ¿Seguro?. Si no desea seguir pulse Ctrl+C

-- Se abre el spool donde dejará el resultado de operar con las constraints. Se ejecutan las sentencias y se cierra el spool.
spool %TEMP%\constraints
start %TEMP%\constraints.sql
spool off

prompt ******************* INFORMACIÓN ********************
prompt Fin. Se ha dejado un fichero de log en %temp%\constraints.lst
--------- FIN DEL SCRIPT -------

Para ejecutarlo, os recomiendo que copiéis las lineas entre INICIO y FIN DEL SCRIPT, las peguéis en un fichero, por ejemplo, constraints.sql. Una vez hecho esto, tenéis que iros a SQLPlus, conectaros con el usuario que posee las restricciones o con otro que pueda administrar las restricciones de éste -normalmente un DBA-, y lanzarlo con la sentencia START ruta\constraints.sql.

Solo debéis responder a las preguntas que os hace y si queréis, él mismo lanzará todas las sentencias de enable/disable constraints dejando un log en la carpeta temporal con nombre constraints.lst.

Juan Lorenzo Arellano
Infor Consult Soluciones.
www.inforconsult.es

Transparent Data Encription (TDE)

Introducción

Desde la release 2 de Oracle 10g, existe la capacidad de cifrar los datos de las columnas de tablas de forma transparente para el usuario, que no debe intervenir para nada. La capa de seguridad cifra y descifra la información antes y después de leerla o almacenarla físicamente.

Este nos evita tener que usar las pesadas funciones de DBMS_CRYPTO y tenernos que pegar con los algoritmos, las semillas...etc.

Restricciones
Tiene algunos incovenientes importantes:

• Permite solamente índices B*Tree.
  
• No permite escaneos de rango para índices.
  
• No soporta tipos BLOB y CLOB.
  
• Utilidades de exportación antiguas.
  
• En general, utilidades que accedan directamente a los datos.
  
• No se pueden relacionar columnas FK/PK cifradas.
  

Visión general
La primera vez que se indica el cifrado de una columna, Oracle crea automáticamente la clave para esa tabla. Cada tabla tiene una clave de cifrado diferente de las que usan las demás. La correspondencia entre las claves de cifrado y las tablas se guarda en el diccionario de datos, estando las claves cifradas con la clave maestra de la base de datos.

Esta clave maestra se guarda en un mecanismo de seguridad externo, el wallet. El wallet es un fichero con extensión *.p12 que guarda un certificado con propósito de cifrado. Permanece cerrado por omisión, lo que impide que la base de datos pueda conocer la clave maestra y por tanto, cifrar y descifrar. Por el contrario, una vez abierto el wallet permanece abierto hasta que se reinicie la base de datos o hasta que se cierre el wallet explícitamente.

Wallets
Creación
Lo primero de todo es tener un wallet que contenga la clave maestra. Para crearlo, hay que configurar el parámetro ENCRYPTION_WALLET_LOCATION del fichero sqlnet.ora apuntando al directorio donde residirá el wallet:

ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=c:\oracle\product\10.2.0\db_1\network\admin)))

Ahora, para que Oracle genere automáticamente el wallet y le asigne la clave maestra:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY password;

lo que habrá generado el fichero ewallet.p12 en directorio indicado arriba. Las utilidades mkwallet (como comando) y Oracle Wallet Manager también se puden usar para crear wallets.

Apertura
Para poder usar TDE primero hay que abrir el wallet que contiene la clave maestra. Esto se puede hacer de forma explícita con:

ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY password;

Siempre hay que abrir el wallet después de reiniciar la base de datos salvo que esté configurado como autologin. Para configurarlo como autologin hay que usar Wallet Manager: en el menú, seleccionar el wallet y marcar la opción Auto Login.

Cierre
El cierre se hace automáticamente cuando se cierra la base de datos. De todas formas, se puede cerrar manualmente con la sentencia:

ALTER SYSTEM SET WALLET CLOSE;

Copia de seguridad
Este fichero contiene la clave maestra sin la cual no es posible descifrar las claves con la que están cifrados los datos. Perderlo significa perder los datos: no hay puerta trasera. Por tanto, es imprescindible tener backup del wallet.

Es un fichero con formato p12. Basta con copiarlo a menudo, tal vez con el backup diario de la base de datos y mantener múltiples copias y en formatos diferentes.

En caso de pérdida, bastará con restaurar el fichero a su ubicación original.

Operaciones de cifrado
Cifrar
Los requisitos para poder cifrar son:

1. El parámetro COMPATIBLE debe estár a 10.2 o superior.
   
2. Debe existir un wallet, tener una clave maestra y estar abierto.
   

Para cifrar basta con indicarlo después del tipo de datos:

CREATE TABLE table ( col_name col_type ENCRYPT, …);

ALTER TABLE table MODIFY ( col_name col_type ENCRYPT,…);

Una vez cifrada una columna, el acceso a los datos de la misma se hace normalmente, ya que Oracle devuelve los datos descifrados e introduce los datos cifrándolos previamente sin intervención del usuario.

Descifrar
Para eliminar el cifrado de una columna cifrada, hay que indicarlo con DECRYPT:

ALTER TABLE table MODIFY ( col_name col_type DECRYPT,…);

Recifrar
Después de haber cifrado las columnas de una tabla con la clave aleatoria generada por Oracle, se pude recifrar para cambiar la clave y opcionalmente el algoritmo:

ALTER TABLE table REKEY [USING '3DES168');

Algoritmos
Por defecto usa AES de 192 bits añadiendo previamente una lista de bits antes de hacer el cifrado (se conoce como "SALT"). Esto se hace para evitar que posibles atacantes usen técnicas de dedución basándose en parejas conocidas de texto en claro y texto cifrado. Por contra, una columna con "SALT" no puede se indexada. Si es requisito imprescindible, se le puede forzar para que no use la "SALT":

CREATE TABLE table ( col_name col_type ENCRYPT NO SALT, …);

ALTER TABLE table MODIFY ( col_name col_type ENCRYPT NO SALT,…);

Los algoritmos soportados son:

• Triple Des (DES) con 168 bits: 3DES168.
  
• Advanced Encryption Standard (AES) con 128, 168 y 256 bits: AES128, AES168 y AES256.
  

Vistas
Las vistas que nos ofrecen información sobre las columnas encriptadas son:

• DBA_ENCRYPTED_COLUMNS.
  
• ALL_ENCRYPTED_COLUMNS.
  
• USER_ENCRYPTED_COLUMNS.
  

Estadísticas de Oracle: análisis de los objetos

Estadísticas

Introducción

Para que Oracle pueda resolver una consulta SQL de forma óptima necesita conocer los datos físicos de cada tabla, índice o cluster que intervienen en ella. Estos datos se almacenan en el diccionario de datos y pueden ser consultadas usando las vistas adecuadas: dba_tables, dba_tab_statistics, dba_tab_col_statistics, dba_tab_histograms, dba_indexes, dba_ind_statistics… etc. En concreto, es el optimizador quién de todos los planes de ejecución posibles analiza estos datos estadísticos para evaluar cuál de todos supone un menor coste en términos de I/O y uso de CPU.

Mantenimiento

Las estadísticas deben reflejar verazmente el estado físico de los objetos, y conforme vayan sufriendo transformaciones éstas se irán quedando obsoletas. Es por esto por lo que hay que recopilar las estadísticas de nuevo a medida que ya no reflejen la situación real de los objetos a los que describen.

Si una tabla no posee estadísticas, Oracle puede recopilar ciertos datos dinámicamente en el momento de la consulta. Esto se llama muestreo dinámico “dynamic sampling”. El parámetro que gobierna este comportamiento es OPTIMIZER_DYNAMIC_SAMPLING.

Toma de estadísticas automáticamente

Las estadísticas se pueden recopilar  manual o automáticamente. Por omisión, Oracle recopila las estadísticas a través del job programado con scheduler en una ventana de tiempo predefinida: de lunes a viernes de 22:00 a 6:00 y sábados y domingos completos. Durante esta recopilación automática se toman datos de tablas sin estadísticas o con estadísticas obsoletas (que han sufrido más de 10% de modificaciones desde la anterior toma de datos) pero antes debe haberse configurado el parámetro STATISTICS_LEVEL a un valor distinto de BASIC. 

Para seguir el número de registros modificados existe la vista all_tab_modifications. Ésta muestra el número aproximado de inserciones, modificaciones, borrados y truncados que ha sufrido la tabla desde la última recopilación. La vista es consultable por el usuario pero hay que tener en cuenta que estos datos no son visibles inmediatamente. Existe un retardo de unos minutos para evitar que la escritura interfiera en el rendimiento. No obstante, se puede volcar la información a la vista usando el procedimiento FLUSH_DATABASE_MONITORING_INFO del paquete DBMS_STATS.

Toma de estadísticas manualmente

Para recopilar las estadísticas
manualmente hay que usar el paquete DBMS_STATS. En versiones anteriores se usaba la sentencia ANALYZE que ya ha quedado obsoleta. El paquete DBMS_STATS tiene métodos para:

1. Recopilar estadísticas.
2. Borrar estadísticas.
3. Guardar y asignar estadísticas.
4. Crear o borrar tablas donde guardar juegos de estadísticas.
5. Bloquear y desbloquear las estadísticas de las tablas.
6. Retornar las estadísticas que tenía una tabla en un momento dado.

Como se puede apreciar se pueden hacer muchas cosas con este paquete. Para la recopilación de estadísticas existen métodos que recopilan de toda la base de datos (GATHER_DATABASE_STATS), para un esquema determinado (GATHER_SCHEMA_STATS), para una tabla determinada (GATHER_TABLE_STATS), o para un índice determinado (GATHER_INDEX_STATS). Asimismo, también existen métodos para recopilar estadísticas de las tablas e índices del diccionario de datos (GATHER_DICCIONARY_STATS), para las vistas de rendimiento dinámico (GATHER_FIXED_OBJECTS_STATS) y para la recopilación de estadísticas del sistema (GATHER_SYSTEM_STATS).

Muchos de los métodos comparten los siguientes parámetros:

• Ownname: indica el propietario de los objetos a analizar.
• Tabname: indica el nombre de la tabla a analizar.
• Partname: indica el nombre de la partición de la tabla a analizar.
• Estimate_percent: si su valor es NULL se obliga a Oracle ha recorrer toda la tabla/índice. Este método logra la mayor exactitud pero puede llevar un tiempo muy grande. Para solucionarlo se puede indicar que se haga una estimación en base a un porcentaje de la tabla. De esta forma, Oracle recorre ese porcentaje y halla las estadísticas suponiendo que se ha recorrido la tabla entera. Por omisión, su valor esAUTO_SAMPLE_SIZE, lo que indica que Oracle escoge si recorrer total o parcialmente la tabla y si es parcial cuánto debe recorrer. Si se indica un valor debe estar en el rango 0.000001 – 100 (%).
• Block_sample: durante la recopilación de estadísticas en modo estimación Oracle escoge al azar filas hasta completar el porcentaje indicado en estimate_percent. Si el parámetro block_sample se pone a TRUE se le indica a Oracle que no escoja filas al azar sino bloques. Este método es más rápido puesto que una vez escoge el bloque lo lee entero (computa todas sus filas) haciendo que el número de saltos aleatorios sea menor. Por el contrario, si las filas no están uniformemente distribuidas a lo largo de todos los bloques puede ser que la estimación resulta menos exacta.
• Degree: cabe la posibilidad de paralelizar la toma de estadísticas con este parámetro. Por omisión, su valor es NULL. Esto indica que se use el grado de paralelismo que tenga la tabla (asignado mediante ALTER TABLE … DEGREE x). 
• Granularity: esta parámetro solo tiene relevancia para tablas particionadas. Con él se indica si queremos que se tomen únicamente estadísticas globales de tabla (GLOBAL), de las particiones (PARTITION), de las subparticiones (SUBPARTITION), global y particiones (GLOBAL AND PARTITION) o todas –global, particiones y subparticiones– (ALL). El valor por omisión es AUTO, que permite a Oracle elegir qué recopilar en función del tipo de objeto de que se trate.
• Cascade: si su valor es TRUE al analizar una tabla también se analizan sus índices. Por omisión es TRUE.
• Force: si su valor es TRUE se analiza la tabla incluso si sus estadísticas están bloqueadas.

Para analizar la base de datos completa se puede usar este ejemplo:

SQL> exec dbms_stats.gather_database_stats;

Procedimiento PL/SQL terminado correctamente.

En este caso no se ha indicado ningún parámetro dejando a Oracle los valores por omisión. Para analizar un esquema entero:

SQL> exec dbms_stats.gather_schema_stats (‘USUARIO’);

Procedimiento PL/SQL terminado correctamente.

Para analizar los objetos de los esquemas SYS, SYSTEM se usa el método DBMS_STATS.GATHER_DICTIONARY_STATS. Éste método tiene casi todos los parámetro vistos anteriormente.

Copia de seguridad de la estadísticas

Existe la posibilidad de crear una tabla para guardar una copia de seguridad de las estadísticas que tiene una tabla antes de recalcularle otras. Esto está pensado para los análisis no completos, esto es, para cuando se hacen estimaciones con un pobre porcentaje en tablas muy grandes. Como ya se ha indicado, cuando se analiza una tabla por estimación, Oracle escoge al azar filas o bloques analizándolos. Este procedimiento azaroso puede resultar en una estimación de diferente exactitud, esto es, diferentes estimaciones pueden dar resultados muy distintos. Por esta razón, es muy interesante que al analizar una tabla por estimación se guarden previamente sus estadísticas actuales, de forma que si la nueva estimación resulta menos eficaz se puedan restaurar las estadísticas que tenía.

Para crear esta tabla de copia de seguridad se usa el procedimiento DBMS_STATS.CREATE_STAT_TABLE que tiene por parámetros el nombre del esquema donde se crea y el nombre que recibe la tabla, y el tablespace. Veamos un ejemplo:

SQL> select count(1) from user_tables where table_name='TB_STATS';

COUNT(1)

----------

0

1 fila seleccionada.

SQL> exec dbms_stats.create_stat_table('SYSTEM','TB_STATS','USERS');

Procedimiento PL/SQL terminado correctamente.

SQL> select count(1) from user_tables

     where 

     table_name='TB_STATS';

COUNT(1)

----------

1

1 fila seleccionada.

A lo hora de analizar la tabla se indica cuál es la tabla de copia de seguridad a través de los siguientes campos:

• Stattab: nombre de la tabla (creada con CREATE_STAT_TABLE) donde guardar las estadísticas.
• Statid: identificador dado a las estadísticas que se guardan (por omisión NULL). Se usa para identificarlas fácilmente.
• Statown: propietario de la tabla donde se guardan las estadísticas.

Un ejemplo donde se analiza la tabla SYSTEM.HELP y se dejan las estadísticas actuales en SYSTEM.TB_STATS es el siguiente:  

SQL> begin    

     dbms_stats.gather_table_stats(ownname=>'SYSTEM',

                                    tabname=>'HELP', 

                                    stattab=>'TB_STATS',

                                    statid=>'help_1',

                                    statown=>'SYSTEM' );

     end;    

/

Procedimiento PL/SQL terminado correctamente.

Con este procedimiento, si ya existen estadísticas guardadas con el mismo statid se sobrescriben. Nótese igualmente, que si es la primera toma de estadísticas de una tabla no se escribe nada en la tabla tabname, ya que no hay nada que salvar.

Para restaurar las estadísticas se tiene que usar DBMS_STATS.IMPORT_TABLE_STATS. Si no se indica ningún statid Oracle restaurará la estadísticas con statid=NULL. Veamos un

ejemplo:

SQL> begin

     dbms_stats.import_table_stats(ownname=>'SYSTEM',

                                   tabname=>'HELP2',

                                   statid=>'help_1',

                                   stattab=>'TB_STATS');

     end;

/

Procedimiento PL/SQL terminado correctamente.

 

Estadísticas del sistema

Además de las estadísticas de las tablas existen también las estadísticas del sistema que muestran datos sobre el rendimiento del hardware/sistema operativo. Entre los datos que se recopilan está la velocidad de la cpu, la velocidad de transferencia de los discos, tiempo de localización de un bloque en lectura secuencial y aleatoria y número de bloques leídos secuencialmente en una lectura multibloque.

Estas estadísticas se deben tomar cuando haya carga de trabajo en la máquina que para las cifras obtenidas sean los más reales posibles. Por ello, existen dos métodos: las estadísticas workload y noworkload.

Cuando se ejecutan las workload se toman tiempos suponiendo que la instancia en está en plena carga. Para tomar estas estadísticas hay que hacer dos instantáneas en diferentes momentos, pero siempre en carga de trabajo, y la diferencia entre las cifras de ambas instantáneas serán los datos registrados. Para tomar estas instantáneas se debe ejecutar cualquiera de los métodos:

Método 1:

SQL> dbms_stats.gather_system_stats('start');

Tiempo después:

SQL> dbms_stats.gather_system_stats('stop');

Método 2:

SQL> dbms_stats.gather_system_stats('interval', interval=>N);

Donde N es el tiempo minutos que debe transcurrir para que se lance automáticamente la

segunda instantánea. 

En noworkload es la instancia la que genera actividad de forma autónoma lanzando lecturas aleatorias a los datafiles para tomando tiempos. Aunque este método no es tan fiable como el anterior resulta muy útil como línea base de trabajo. De hecho, Oracle recomienda lanzar este método nada más crear una base de datos nueva. Para tomar estas estadísticas hay que hacer:

SQL> dbms_stats.gather_system_stats(); 

Estas estadísticas se pueden guardar en tablas de copia de seguridad a través de los parámetros stattab, statid y statown o en el diccionario, en cuyo caso lo hace en sys.aux_stats$.

Por último decir que el optimizador usará estas estadísticas para las nuevas SQL que se ejecuten o para las que necesiten ser compiladas de nuevo. Las ya existentes usarán sus planes de ejecución ya precalculados:

Nada más. Espero que este artículo sea comprensible, interesante y que ayude a comprender mejor qué son necesarias las estadísticas y por qué resultan necesarias. 

Juan Lorenzo Arellano.

Oracle DBA

Infor Consult Soluciones

www.inforconsult.es

Backups automáticos del spfile y del controlfile

Introducción

Desde la versión 9 de Oracle existe la posibilidad de hacer backups del controfile y del SPFILE automáticamente (autobackups) si se usa RMAN, esto es, ante determinadas circunstancias, Oracle lanza automáticamente el backup asegurando que siempre se tiene un respaldo actualizado.

Lógicamente, este comportamiento permite asegurar casi al 100% que ante una caída se podrá hacer una recuperación exitosa siempre que contemos con alguna copia de los ficheros de datos y la base de datos esté en modo archivelog. Téngase en cuenta que Oracle necesita el repositorio para hacer una recuperación, y ese repositorio reside en el fichero de control y/o en un catálogo de recuperación si está configurado. En el caso de que se pierda el catálogo de recuperación y además todos los ficheros de control, la recuperación se hace (casi) imposible, salvo que tengamos activado el autobackup (que, curiosamente, “viene de fábrica” en OFF).

¿Cuándo lanza Oracle el autobackup?

Si está configurado el autobackup, Oracle salva el controlfile y el SPFILE (si se usa):

1. Después de ejecutar una orden BACKUP en el prompt o después de ejecutar un bloque RUN{} que incluya la orden BACKUP.
2. Después de cada cambio estructural que haya modificado en contenido del controlfile, por ejemplo, añadir o borrar tablespaces o datafiles.
   

¿Dónde se salvan los autobackups?
Depende. Si el autobackup se genera por el primer caso, Oracle usa el primer canal asignado para ese backup y lo guarda en otro backupset diferente. Obviamente, el medio físico donde se guardará ese autobackup es el mismo que el del canal, es decir, si el primer canal era de tipo TAPE el autobackup irá también a cinta.

Sin embargo, si el autobackup se genera como consecuencia de un cambio estructural de la base de datos, Oracle automáticamente abre un canal tipo DISK y guarda el autobackup. El lugar y el nombre que Oracle dará a este autobackup se puede configurar como se verá más adelante.

Cada vez que se lanza un autobackup Oracle escribe en el alert.log de la instancia la hora y la ruta completa del backup.

No obstante, aunque no esté configurado el autobackup, en realidad, se hace pero solo con cada orden BACKUP que incluya el datafile número 1 (datafile system). Además, en este caso el backup lo mete en el mismo backupset que el backup general. Otra diferencia es que si no está configurado, Oracle no hace autobackup después de cada cambio estructural.

¿Cómo se ve la configuración actual?
Para ver los valores de configuración se debe entrar en RMAN y una vez conectados a la base de datos target ejecutar SHOW ALL para ver todos los valores o SHOW OPCIÓN para ver la opción que se quiera, por ejemplo:

SHOW CONTROLFILE AUTOBACKUP;
SHOW CONTROLFILE AUTOBACKUP FORMAT;
SHOW DEFAULT DEVICE TYPE;

¿Cómo se activa/desactiva el autobackup?
Como ya se ha dicho, por omisión viene desactivado. Para activarlo hay que entrar en RMAN y una vez conectados a la base de datos target lanzar la orden:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

Para desactivarlo hay que hacer lo mismo pero con esta otra orden:

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

¿Cómo se cambian las rutas?
La ruta por omisión del autobackup tipo DISK es la flash recovery area si está configurada, o en su defecto, la carpeta $ORACLE_HOME/dbs. El formato es %F, que se traduce por una cadena de este tipo: c-IIIIIIIIII-YYYYMMDD-QQ, donde “c” es un literal, “IIIIIIIIII” se sustituye por el DBID de la base de datos, “YYYYMMDD” es la fecha y “QQ” es un número hexadecimal y sequencial.

Para cambiar la ruta se debe usar (es obligatorio que se use %F):

CONFIGURE CONTROFILE AUTOBACKUP FOR DEVICE TYPE DISK TO ‘ruta_formato’;

Ejemplos de ruta_formato:

‘%F’
‘/opt/oracle/%F’
‘/opt/oracle/controfile_%F’

Para volver a dejar el valor por omisión se puede usar CLEAR en vez de TO ‘ruta_formato’.

Y hasta aquí por hoy con este artículo. Espero que te haya sido útil. Por favor, deja
tus comentarios, correciones o sugerencias.

Juan Lorenzo Arellano
Oracle DBA
Infor Consult Soluciones

www.inforconsult.es

Gestión automática de la memoria PGA

¿Qué es la PGA?
Las siglas provienen de Program/Private Global Area, y es la memoria privada de cada proceso servidor. En esta memoria cada proceso almacena información que sólo es necesaria para su propio funcionamiento como por ejemplo sus variables globales, el estado actual de cada cursor (SQL) que se ejecuta... etc.

La PGA se compone de:

• Área SQL privada: cada SQL que se ejecuta necesita de este espacio para poder llevar el control de las operaciones propias de la sentencia. Se asigna cuando se abre el cursor y se libera completamente cuando se cierra. Esta parte de memoria se subdivide en dos: a) area persistente: perdura durante toda la vida del cursor. Guarda las bind variables además de otras cosas; b) area en tiempo de ejecución: se libera cuando finaliza la ejecución de la sentencia SQL (aunque no se haya cerrado el cursor ). Constituyen las áreas de trabajo (working areas) que se explican más adelante. El número máximo de cursores, y por tanto, el número máximo de áreas SQL privadas, que un usuario puede tener abiertos al mismo tiempo se controla con el parámetro OPEN_CURSORS. También hay que tener en cuenta que esta área SQL privada se almacena en la PGA si la Instancia está configurada como servidores dedicados (dedicated servers). En caso de servidors compartidos (shared servers) se almacena en la SGA.

• Memoria de las sesiones: guarda información relativa a la sesión como el login, variables de sesión... etc. En servidores compartidos (shared servers) este área pasa a ser pública ya que diferentes usuarios comparten los mismos procesos servidores.

¿Qué son las áreas de trabajo (working areas)?
Las área de trabajo (working areas) son el espacio usado por el proceso servidor para realizar ordenaciones y clasificaciones como los ORDER BY, GROUP BY o HASH JOIN. Esta área es la de mayor tamaño y forma parte del área en tiempo de ejecución.

Su tamaño es determinante para obtener buenos tiempos de respuesta en operaciones de este tipo, ya que si el espacio se queda pequeño para realizar estas tareas es necesario liberar espacio usando el tablespace temporal del usuario. Ésta actividad de swapping afecta drásticamente al rendimiento y recibe el nombre de "extra pass".

¿Dónde se almacena la PGA?
La PGA se almacena en la memoria del servidor pero fuera de la SGA. Por tanto, para dimensionar ambas áreas hay que tener en cuenta que la suma de las dos no supere los límites específicos del servidor y la plataforma (tamaño máximo memoria RAM, 2 GB en Windows sin /3GB, 3 GB en Windows con /3GB... etc.).

¿El tamaño de la PGA es configurable?
Sí, pero únicamente es configurable el tamaño de las áreas de trabajo. Desde Oracle9i el DBA puede fijar el tamaño máximo del cónjunto de todas las PGAs de la Instancia a través del parámetro PGA_AGGREGATE_TARGET. De esta forma, Oracle dinámicamente gestiona el tamaño de cada área de trabajo para que la suma de todas las PGAs (no de todas las áreas, que es un parte de la PGA como ya se ha indicado) no exceda del límite impuesto. En versiones anteriores la forma de controlar el tamaño de cada área de trabajo era a través de los parámetros SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE y CREATE_BITMAP_AREA_SIZE.

Para que la Instancia tenga el cuenta el parámetro PGA_AGGREGATE_TARGET, el parámetro WORKAREA_SIZE_POLICY se debe asignar a AUTO (por omisión). Con MANUAL, se indica que se trabaja de la forma anterior y se fija cada parámetro *_AREA_SIZE por separado.

¿Cuándo se crea la PGA?
La crea el Listener durante la creación del proceso servidor. Si esta memoria no puede ser asignada o reservada la creación del proceso servidor falla y el Listener devuelve un ORA-12500: TNS:listener failed to start a dedicated server process. Igualmente, si durante la ejecución de alguna sentencia es necesario incrementar el tamaño de la PGA y el sistema operativo no tiene más memoria para darle se devuelve un error indicándolo.

¿Cuándo se destruye la PGA?
Al cerrar el proceso servidor su PGA asociada se libera y se devuelve al sistema operativo el espacio reservado. El proceso servidor se cierra cuando el usuario ejecuta EXIT o DISCONNECT, lanza un Ctrl+C, se pierde la comunicación con el servidor, se produce un ORA-600 que desconecta la sesión, el DBA lanza un KILL SESSION, etc.

¿Cuánto ocupa cada PGA?
En cada versión de Oracle, e incluso en cada release, el tamaño mínimo cambia. No obstante, como el tamaño es cambiante en función del área de trabajo, lo mejor que se puede hacer para comprobar cuánto ocupa o cómo ocupa es monitorizarlas.

¿Cómo se monitoriza la PGA?
Hay diferentes vistas para saber cuánto ocupa cada PGA individual o para saber cuánto ocupan todas las PGAs o incluso para saber si está bien configurado el parámetro PGA_AGGREGGATE_TARGET.

Con V$PROCESS se puede saber el tamaño de la PGA de cada proceso de la Instancia (el proceso puede ser background o foreground -también llamado shadow-). Si es un proceso background se sabé rápidamente qué proceso es porque lleva el nombre del proceso en la columna PROGRAM. Si es un proceso shadow se sabe el usuario que hay detrás cruzando las columnas ADDR.V$PROCESS con PADDR.V$SESSION. Veamos un ejemplo:

SQL> select sid, serial#, username, command, status, machine from v$session where paddr='BE103FA4';

SID SERIAL# USERNAME COMMAND STATUS MACHINE
---- ---------- -------- ------- -------- -------
1386 9 TEST_USR 0 INACTIVE SATURNO

Para obtener unas estadísticas de la PGA se puede usar la vista V$PGASTAT:

SQL> select * from v$pgastat;

NAME VALUE UNIT
---------------------------------------- ---------- -------
aggregate PGA target parameter 256901120 bytes
aggregate PGA auto target 58300416 bytes
global memory bound 51380224 bytes
total PGA inuse 192188416 bytes
total PGA allocated 283714560 bytes
maximum PGA allocated 548391936 bytes
total freeable PGA memory 0 bytes
process count 343
max processes count 404
PGA memory freed back to OS 0 bytes
total PGA used for auto workareas 0 bytes
maximum PGA used for auto workareas 78736384 bytes
total PGA used for manual workareas 0 bytes
maximum PGA used for manual workareas 530432 bytes
over allocation count 15599
bytes processed 8,1983E+11 bytes
extra bytes read/written 1,4539E+10 bytes
cache hit percentage 98,25 percent
recompute count (total) 208640

Aggregate PGA target parameter indica el valor del parámetro PGA_AGGREGGATE_TARGET.
Aggregate PGA auto target indica la cantidad de PGA que Oracle puede usar como áreas de trabajo. Este valor varía en función de la carga de trabajo. Total PGA allocated indica el valor total de PGA asignado. Este es el valor que Oracle intenta mantener por debajo de PGA_AGGREGGATE_TARGET. Este valor puede ser más alto que el indicado por el parámetro de inicialización en momentos puntuales de mucha carga o de forma constante si PGA_AGGREGGATE_TARGET está pobremente dimensionado. Over allocation count indica el número de veces que ha habido que sobrepasar el límite impuesto por PGA_AGGREGGATE_TARGET, lo que es síntoma de que hay que aumentar el parámetro.
Cache hit percentage indica el número de veces que se ha podido realizar una operación intensiva de ordenación/clasificación sin incurrir en un "extra pass". Si este valor no es cercano al 100% indica que hay que aumentar el parámetro PGA_AGGREGGATE_TARGET.

La vista V$PGA_TARGET_ADVICE es muy útil para predecir cómo cambiaría el comportamiento de la Instancia si modificamos le valor de PGA_AGGREGATE_TARGET. Para que esta vista recopile información el parámetro STATISTICS_LEVEL debe estar configurado a TYPICAL o ALL:

SQL> select pga_target_for_estimate, pga_target_factor,
estd_pga_cache_hit_percentage, estd_overalloc_count
from v$pga_target_advice;

PGA_TGT_FE PGA_TGT_F ESTD_HIT_PCTGE ESTD_OVRALL_COUNT
---------- --------- -------------- -----------------
32112640   ,125      67             67303
64225280   ,25       67             67303
128450560  ,5        67             66960
192675840  ,75       74             40785
256901120  1         97             4652
308281344  1,2       98             73
359661568  1,4       98             0
411041792  1,6       98             0

462422016  1,8       98             0

513802240  2         98             0

770703360  3         98             0

1027604480 4         100            0

1541406720 6         100            0

2055208960 8         100            0

La columna pga_target_for_estimated indica la estimación para las que las demás columnas están dando información. La columna pga_target_factor indica la relación entre el valor de PGA_AGGREGGATE_TARGET actual y el valor de estimación de esa fila. Cuando este factor está a 1 indica la relación 1 a 1 -el tamaño actual-. Si es 2, es 1 a 2 -el doble-, y si es 0,125 es 1 a 0,125 -una octava parte-. La columna estd_pga_cache_hit_percentage indica el porcentage de éxito, operación resueltas sin "extra pass" para ese pga_target_for_estimated. Por último, la columna estd_overalloc_count indica el número de veces que habría que sobrepasar el valor indicado por PGA_AGGREGGATE_TARGET para ese pga_target_for_estimated.

Para este caso el valor óptimo sería subir el valor del parámetro 1,4 veces porque nos da una estimación de 0 "extra passes". Seguir aumentándolo no aportaría ninguna mejora. Si estamos justos de memoria y 73 "extra passes" son asumbles se puede empezar aumentándolo 1,2 veces en vez 1,4 y después de un tiempo de trabajo volver a chequear los valores.

Por último decir que las vistas V$PGASTAT y V$PGA_TARGET_ADVICE guardan información de la Instancia en tiempo real. Cuando se reinicia la Instancia estas vistas empiezan a recopilar datos nuevos. Para ver el histórica entre reinicios están las vistas DBA_HIST_PGASTAT y DBA_HIST_PGA_TARGET_ADVICE respectivamente.

Espero que te haya sido útil. Por favor, deja tus comentarios, correciones o sugerencias.

Juan Lorenzo Arellano
Oracle DBA
Infor Consult Soluciones
www.inforconsult.es