Blog interesante: Andrew Reid.

Hace poco he descubierto el blog de Andrew Reid que no conocía y me ha parecido muy interesante. He leído algunos artículos y tienen muy buena pinta. Trata tanto temas de rendimiento como asuntos de administración, con scripts detallados y tests hechos a conciencia!

Totalmente recomendable!!

Si quieres conocer más sobre el trabajo de Andrew en la red, quizás quieras echarle un ojo a su blog en inglés: http://international-dba.blogspot.com.es/

Optimización SQL en Oracle – Ya a la venta!

El libro ya está disponible en todas las tiendas Amazon (.com, .co.uk, .es, .de, etc.)

¡Muchísimas gracias! ¡Espero que os guste y os sea útil!

Amazon.es              Amazon.com

Podéis echarle un ojo al interior aquí (mejor en pantalla completa):

Optimización SQL en Oracle. Últimos retoques.

¡Por fin!

El libro «Optimización SQL en Oracle» está terminado. 

En cuanto finalice el diseño de la portada y la contraportada (si los de Amazon no ponen impedimento) ya estará disponible para comprar tanto en amazon.com como en amazon.eu.

El libro ha contado con dos revisores técnicos de peso, que le han sacado punta a todo y han sentado a debate tanto los ejemplos, imágenes y conceptos expuestos, como la filosofía de trabajo del libro. Son Arturo Gutierrez y Jetro Marco. Gracias a ellos el libro ha pasado de unas 316 páginas a las más de 420 actuales. 
El índice finalmente ha quedado así:
Al final abordamos todo desde las tripas, tanto el tratamiento de las bind variables como el funcionamiento del Bind Variable Peeking, el Adaptive Cursor Sharing, el  como SQL Profiles, SQL Baselines, reescritura de vistas materializadas, el paralelismo, particionamiento, así como trazas y planes de ejecución de todo.
El libro está lleno de técnicas, herramientas, base de conocimiento y nuestro aporte profesional sobre cómo optimizar cualquier SQL que de un mal rendimiento. Nos metemos a contar qué pasa en particular con los entornos data warehouse (¿Se puede ejecutar PL/SQL masivo en un entorno data warehouse? … pues hay un capítulo que trata de cómo hacerlo a un rendimiento brutal!), desmontar mitos, descubrir la realizad subyacente de trucos como lanzar INSERTS con APPEND y cosas así. ¿Por qué Oracle dice que seguirá un plan de ejecución y luego decide utilizar otro? ¿cómo lidiar con todo esto sin volvernos locos????
Hemos destripado todas las hints, poniéndolas a prueba. Hemos buscado ejemplos de SQL ineficiente y de múltiples ejecuciones de un mismo código para rizar el rizo y comprender qué sucede en el CBO, cómo se estima la cardinalidad de las operaciones, por qué (a veces) Oracle se equivoca y por qué a veces somos nosotros los que no entendemos al motor.
Además, la bbdd está disponible para descargar gratuitamente aquí, y el SQL del libro (próximamente)!

Migraciones con mínimo tiempo de parada con Oracle GoldenGate.

Últimamente estoy enfrentando migraciones que suponen cambios de plataforma, que los clientes utilizan para subir la base de datos de versión. Así pues, bases de datos en solaris versión Oracle10g (10.1 por ejemplo) que han de migrarse a un entorno Linux Oracle11g ó Oracle10g (versión 10.2).

Si la plataforma del sistema operativo fuera la misma (de solaris a solaris o de linux a linux) y la migración no implicase un cambio de versión, una de las opciones recomendadas es duplicar la base de datos, convertir el clon duplicado en base de datos standby, aplicar los logs en el momento de la puesta en producción y abrir la base de datos duplicada como principal.

Si hay subida de versión, en la máquina destino debe haber dos juegos de binarios: el mismo que el de origen, para realizar la restauración, y el juego de binarios de la nueva versión. Así, con el juego de binarios primero se realiza el duplicado y restauración, y con el segundo se abre la base de datos en modo «migrate» y se ejecutan los scripts de upgrade. Mientras dure el proceso la base de datos principal ha de estar parada y eso es un obstáculo.

Si bien las arquitecturas de sistema operativo son diferentes será necesario comprobar si el «endian» es distinto entre las dos plataformas. Existen dos tipos de endian, el pequeño y el grande. Entre plataformas de un mismo endian, basta con duplicar con RMAN la base de datos, pero si el endian es distinto (de little a big, o de big a little) requiere además convertir los ficheros y realizar la migración transportando tablespaces.

SQL> select * from v$transportable_platform
  2  order by endian_format;

PLATFORM_ID PLATFORM_NAME                            ENDIAN_FORMAT
———– —————————————- ————–
          3 HP-UX (64-bit)                           Big
          6 AIX-Based Systems (64-bit)               Big
         18 IBM Power Based Linux                    Big
          2 Solaris[tm] OE (64-bit)                  Big
          4 HP-UX IA (64-bit)                        Big
         16 Apple Mac OS                             Big
          1 Solaris[tm] OE (32-bit)                  Big
          9 IBM zSeries Based Linux                  Big
         17 Solaris Operating System (x86)           Little
         19 HP IA Open VMS                           Little
         20 Solaris Operating System (x86-64)        Little
         12 Microsoft Windows x86 64-bit             Little
         13 Linux x86 64-bit                         Little
          8 Microsoft Windows IA (64-bit)            Little
         21 Apple Mac OS (x86-64)                    Little
         11 Linux IA (64-bit)                        Little
          5 HP Tru64 UNIX                            Little
         10 Linux IA (32-bit)                        Little
          7 Microsoft Windows IA (32-bit)            Little
         15 HP Open VMS                              Little

20 filas seleccionadas.

Existen varias soluciones para hacer una migración de este tipo con mínimo tiempo de parada, como utilizar streams o utilizar un dataguard lógico, pero Oracle GoldenGate es definitivamente uno de los mejores métodos y más versátiles para migrar tanto bases de datos enteras, como esquemas, realizando además cambios de versión y de plataforma sincronizando lógicamente dos entornos.

Los pasos a seguir serían los siguientes, tomando nodo1 y nodo2 como los dos servidores, origen y destino.

1.- Descargar el software de Oracle GoldenGate de eDelivery   http://edelivery.oracle.com  y descomprimirlo en ambos servidores.
(el registro es gratuíto, y su descarga con fines no comerciales también!)

host1@oracle $ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Solaris, sparc, 64bit (optimized), Oracle 10g on Apr 24 2012 09:06:57
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (host1) 1> create subdirs
Creating subdirectories under current directory /opt/oracle/product/OracleGG
Parameter files                /opt/oracle/product/OracleGG/dirprm: already exists
Report files                   /opt/oracle/product/OracleGG/dirrpt: created
Checkpoint files               /opt/oracle/product/OracleGG/dirchk: created
Process status files           /opt/oracle/product/OracleGG/dirpcs: created
SQL script files               /opt/oracle/product/OracleGG/dirsql: created
Database definitions files     /opt/oracle/product/OracleGG/dirdef: created
Extract data files             /opt/oracle/product/OracleGG/dirdat: created
Temporary files                /opt/oracle/product/OracleGG/dirtmp: created
Stdout files                   /opt/oracle/product/OracleGG/dirout: created

2.- Chequear que la base de datos origen está en modo ARCHIVELOG


ARCHIVE LOG LIST;
3.- Activar el suplemental logging en la base de datos.

SELECT force_logging, supplemental_log_data_min FROM v$database;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;
4.-Crear el usuario GoldenGate en la base de datos origen.

CREATE USER oggadm1 IDENTIFIED BY “13c1sa”;
GRANT dba TO oggadm1;
5.-Comprobar la visibilidad entre las bases de datos (todas).

host1@oracle $ tnsping b_new
TNS Ping Utility for Solaris: Version 10.2.0.5.0 – Production on 30-APR-2013 12:23:25
Copyright (c) 1997,  2010, Oracle.  All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = host2)(PORT = 1521))) (CONNECT_DATA = (SID = BBDD2)))
OK (320 msec)

6.-Configurar manager.

host1@oracle $ more GLOBALS
CheckpointTable oggadm1.oggchkp
host1@oracle $ more startup.oby
DBLogin UserID oggadm1@B, Password 13c1sa
Start Mgr
Info Mgr
Info CheckpointTable
Set Editor vi
GGSCI (host1) 1> Edit Param mgr
«/opt/oracle/product/OracleGG/dirprm/mgr.prm» [New file]
Port 15002
PurgeOldExtracts ./dirdat/*, UseCheckpoints
GGSCI (host1) 1> obey startup.oby
GGSCI (host1) 2> DBLogin UserID oggadm1@B, Password 13c1sa
Successfully logged into database.
GGSCI (host1) 3> Start Mgr
MGR is already running.
GGSCI (host1) 4> Info Mgr
Manager is running (IP port host1.15001).
GGSCI (host1) 5> Info CheckpointTable
No checkpoint table specified, using GLOBALS specification (oggad1.oggchkp)…
Checkpoint table oggad1.oggchkp does not exist.
GGSCI (host1) 6> Set Editor vi
7.-Crear la tabla de checkpoints.

GGSCI (host1) 7> Add CheckpointTable
No checkpoint table specified, using GLOBALS specification (oggadm1.oggchkp)…
Successfully created checkpoint table oggadm1.oggchkp.

8.- Crear el proceso EXTRACT

GGSCI (host1) 9> edit param extr
host1@oracle $ more /opt/oracle/product/OracleGG/dirprm/extr.prm
Extract extr
UserID oggadm1@B, Password 13c1sa
ExtTrail ./dirdat/aa
Table USUARIO1.*;
Table USUARIO2.*;
Table USUARIO3.*;
Table USUARIO4.*;

GGSCI (host1) 2> Add Extract extr, TranLog, Begin Now

EXTRACT added.
GGSCI (host1) 18> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     STOPPED     EXTR        00:00:00      00:00:44

9.- Arrancar el proceso EXTRACT en el servidor origen. Una vez arrancado lanzar un log switch y un EXPORT DATA PUMP de la base de datos principal sobre la réplica con la opción FLASHBACK_SCN=xxxxxxx. En este caso es el 501308592, correspondiente al FIRST_CHANGE del último redolog online.
10.- Añadir trandata para tablas en el servidor origen.
Add TranData
Add TranData USUARIO1.*
Add TranData USUARIO2.*
Add TranData USUARIO3.*
Add TranData USUARIO4.*




11.- Configurar manager en servidor de replica (pasos 6 y 7)

12.- Configurar pump en servidor principal.

GGSCI (host1) 2> edit param pump
«/opt/oracle/product/OracleGG/dirprm/pump.prm» [New file]
Extract pump
RmtHost host2, MgrPort 15002, Compress
RmtTrail ./dirdat/ab
Passthru
Table USUARIO1.*;
Table USUARIO2.*;
Table USUARIO3.*;
Table USUARIO4.*;

«/opt/oracle/product/OracleGG/dirprm/pump.prm» [New file] 8 lines, 969 characters
GGSCI (host1) 3> Add Extract pump, ExtTrailSource ./dirdat/aa
EXTRACT added.
GGSCI (host1) 4> Add RmtTrail ./dirdat/ab, Extract pump, megabytes 5
RMTTRAIL added.
GGSCI (host1) 5> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXTR        00:00:00      00:00:02
EXTRACT     STOPPED     PUMP        00:00:00      00:00:11


13.- Configurar replicat en servidor replica.

GGSCI (host2) 10> edit param repl
Replicat repl
UserID oggadm1@B, password 13c1sa
AssumeTargetDefs
DiscardFile ./dirrpt/repl.dsc
Map USUARIO1.*, Target USUARIO1.*;
Map USUARIO2.*, Target USUARIO2.*;
Map USUARIO3.*, Target USUARIO3.*;
Map USUARIO4.*, Target USUARIO4.*;

GGSCI (host2) 11> Add Replicat repl, ExtTrail ./dirdat/ab
REPLICAT added.
GGSCI (host2) 12> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     REPL        00:00:00      00:00:06

14.- Arrancar pump en servidor principal.

GGSCI (host1) 7> start extract pump
Sending START request to MANAGER …
EXTRACT PUMP starting
GGSCI (host1) 8> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXTR        00:00:00      00:00:08
EXTRACT     RUNNING     PUMP        00:00:00      00:13:38
GGSCI (host1) 9> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING     EXTR        00:00:00      00:00:08
EXTRACT     RUNNING     PUMP        00:00:00      00:00:09
14.- Importar el fichero de Export Data Pump generado en el paso 9.


15.- Arrancar replicat en replica al SCN de la exportación.

GGSCI (host2) 16> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    STOPPED     REPL        00:00:00      00:06:11
GGSCI (host1) 7> Start Replicat, aftercsn 501308592
GGSCI (host2) 9> Start Replicat repl, aftercsn 501308592
Sending START request to MANAGER …
REPLICAT REPL starting
GGSCI (host2) 10> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
REPLICAT    RUNNING     REPL        00:00:00      00:00:06



Una vez las dos bases de datos están sincronizadas, toda la información del servidor principal se propaga a la base de datos réplica, que es idéntica en estructura a la base de datos principal. En el momento de la puesta en producción simplemente hay que cambiar los clientes para dirigirlos a la réplica.

Optimización SQL en Oracle. En venta, próximamente!

En breve estará a la venta mi libro «Optimización SQL en Oracle». 
Durante los últimos dos años he estado escribiendo este libro que resume, a mi modo de ver, todo lo que un administrador o programador debería conocer para optimizar código SQL.
En él describo cómo funciona el optimizador y cómo se comporta el servidor para escoger los mejores planes de ejecución, los aspectos a considerar para crear tablas  de diferentes tipos (tablas IOT, clusters, tablas particionadas, etc.) y lo mismo relativo a los índices. Herramientas para optimizar SQL, desde asesores a las herramientas «manuales» como explain plan, tkprof, autotrace, generación de trazas, análisis de AWR, etc.
Además, también dedico un apartado a los entornos datawarehouse, a optimización SQL de código ineficiente con casos prácticos resueltos, y un glosario completo de hints con ejemplos de su uso y «maluso», y sus consecuencias para el rendimiento.
Este libro responde preguntas y cuestiones habituales como el motivo por qué no siempre es eficiente acceder a las tablas usando índices, escenarios ineficientes, usos incorrectos de tipos de datos y sus consecuencias en la optimización, uso correcto del paralelismo, el particionamiento, las vistas materializadas, jerarquías, dimensiones, consecuencias de usar NOLOGGING, como tratar subconsultas, uso de IN y EXISTS, DISTINCT, ordenaciones, etc.
El esquema del libro es el siguiente:
Por el momento está en fase BETA, pendiente de revisión técnica. Para esta revisión cuento con dos administradores de los más fuertes de España, y vamos a asegurarnos que en las más de 300 páginas no se nos escapa un error.
Me gustaría decir, como los de Valve, «When it’s done, it’s done» como fecha de publicación, pero espero que en cosa de un par de meses pueda estar disponible a la venta.
Estoy contento porque se trata del primer libro en español que trata exclusivamente de optimización SQL y todo su universo. Muchos libros (principalmente en inglés) tratan de aspectos del rendimiento, sobre todo del motor (memoria, procesos) o se centran exclusivamente en administración o programación, pero éste es el primer libro que conozco absolutamente específico, en español, con ejemplos en español, tablas con nombres en cristiano (vuelos, reservas, etc.), sin ser una traducción de una obra en inglés o un copia/pega de partes de la documentación de Oracle.
Yo estoy satisfecho del resultado, y espero que pueda ser de utilidad. Estoy seguro de que incluso los usuarios más avanzados se sorprenderán aprendiendo cosas nuevas, o redefiniendo conceptos, o encontrando una forma práctica y accesible de resumir las funcionalidades y componentes que afectan a la eficiencia del servidor de base de datos.
Os dejo unas imágenes del libro, en fase BETA, listo para revisarlo y corregirlo antes de sacarlo a la luz.


«La query que tumba la máquina».

Hace unos años publiqué un artículo llamado «PL/SQL y ejecuciones en host» en el que describía el paso a paso para poder, desde PL/SQL, ejecutar código en el sistema operativo.

Oracle no permite que los procedimientos y funciones puedan acceder al host, pero sí permite llamadas a funciones externas implementadas con C o PASCAL, y redireccionadas como librerías mediante un objeto library.

Mi intención inicial fue la de crear un procedimiento PL/SQL que realizara un backup en caliente del servidor, realizase un export, import, o cualquier otra invocación a un ejecutable residente en el sistema operativo.

Hoy he visto una configuración similar en una base de datos en un entorno de producción, que realizan la misma implementación pero mediante una función.

create or replace
FUNCTION sysrun (syscomm IN VARCHAR2)
  RETURN BINARY_INTEGER
  AS LANGUAGE C
  NAME «sysrun»
  LIBRARY shell_lib
  PARAMETERS(syscomm string);

De este modo, la función retorna un 0 si la llamada al SHELL_LIB se ha ejecutado correctamente, o un número si ha habido un error.
Hice esta prueba, y pude constatar un mito: La consulta que tumba el servidor existe!!.
SQL> select sysrun('sudo reboot') from dual;

Broadcast message from root (Thu Jan 27 13:16:34 2011):

The system is going down for reboot NOW!

SYSTEM.SYSRUN('SUDOREBOOT')
---------------------------
                          0

Encriptación de datos desde aplicación.

En todos los ejemplos que he encontrado sobre encriptación y desencriptación de datos en Oracle, siempre se usan procedimientos PL/SQL para establecer la seguridad en la base de datos. No he encontrado un sólo ejemplo que permita hacer un insert «encriptado» y una consulta «desencriptada».

Imaginando el siguiente escenario: Cada usuario tiene una «palabra secreta» para desencriptar su propia información. En la base de datos todo se registra encriptado.

Para ello, Oracle ofrece dos paquetes:

DBMS_OBFUSCATION_TOOLKIT. A partir de Oracle8i, que soporta encriptación DES y triple DES (Data Encription Standard), y con ciertas limitaciones (por ejemplo, los datos a encriptar han de ser un múltiplo de 8 bytes).

DBMS_CRYPTO. A partir de Oracle10g. Soporta más formas de encriptación, como la AES (Advanced Encription Standard), que sustituye el anterior DES y no hay limitación con el número de carácteres.

Para mas información, la documentación de Oracle ofrece esta comparativa de funcionalidades.

Uso de DBMS_OBFUSCATION_TOOLKIT

El siguiente ejemplo muestra la encriptación de la palabra «SECRETO» (8 bytes) y genera un error al intentar encriptar «SECRETITOS!» (11 bytes)

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>SECRETO!’,key_string=>’clavedesencript’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT_STRING=>’SECRETO!’,KEY_STRING=>’CLAVED
——————————————————————————–
lr??

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>SECRETITOS!’,key_string=>’clavedesencript’) from dual;
select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’SECRETITOS!’,key_string=>’clavedesencript’) from dual
*
ERROR at line 1:
ORA-28232: invalid input length for obfuscation toolkit
ORA-06512: at «SYS.DBMS_OBFUSCATION_TOOLKIT_FFI», line 21
ORA-06512: at «SYS.DBMS_OBFUSCATION_TOOLKIT», line 126

El error ORA-28232 corresponde a la longitud inadecuada de la cadena a encriptar. ‘SECRETITOS!» tiene 11 carácteres y el paquete está limitado a múltiplos de 8 bytes. Por ejemplo, el número de una tarjeta de crédito.

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1234567812345678′,
2 key_string=>’clavedesencript’)
3 from dual;

DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT_STRING=>’1234567812345678′,KEY_STRING=
——————————————————————————–
}??X??

De modo que la desencriptación funciona de igual modo, usando la función DESDecrypt

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’SECRETO!’,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_BUENA’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
SECRETO!

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1111222233334444′,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_BUENA’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
1111222233334444

y si se utiliza una clave distinta, la información no se desencriptará adecuadamente.

SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’SECRETO!’,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_ERRONEA’) from dual;

DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
???! k

…ó producirá un error.


SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1111222233334444′,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_MALA’) from dual;
ERROR:
ORA-29275: partial multibyte character

no rows selected

Uso de DBMS_CRYPTO

El siguiente ejemplo muestra la encriptación de la palabra «SECRETITOS!» (11 bytes) usando una suite de encriptación que ya viene implementada. En concreto es la DES_CBC_PCKS5, que contiene encriptación DES, encadenamiento de cifrado de bloques y modificadores de relleno PCKS5.

Es preciso, para invocar correctamente a este paquete, realizar una conversión a RAW de las cadenas a encriptar. He utilizado para ello el paquete UTL_RAW y la función UTL_I18N.STRING_TO_RAW.

SQL> select DBMS_CRYPTO.ENCRYPT(src => UTL_I18N.STRING_TO_RAW (‘SECRETITOS!’, ‘AL32UTF8’),
2 typ => 4353,
3 key => UTL_I18N.STRING_TO_RAW (‘clavedesencript’, ‘AL32UTF8’)
4 )
5 from dual;

DBMS_CRYPTO.ENCRYPT(SRC=>UTL_I18N.STRING_TO_RAW(‘SECRETITOS!’,’AL32UTF8′),TYP=>4
——————————————————————————–
1BA7F933C2CAD0C7F4FDA685775BE0E7

Y la desencriptación de la información, con la función DECRYPT.

SQL> select UTL_RAW.cast_to_varchar2(
2 DBMS_CRYPTO.DECRYPT(
3 DBMS_CRYPTO.ENCRYPT(src => UTL_I18N.STRING_TO_RAW (‘SECRETITOS!’, ‘AL32UTF8’),
4 typ => 4353,
5 key => UTL_I18N.STRING_TO_RAW (‘clavedesencript’, ‘AL32UTF8’)
6 ),
7 typ => 4353,
8 key => UTL_I18N.STRING_TO_RAW (‘clavedesdecript’, ‘AL32UTF8’)
9 )
10 )
11 from dual;

UTL_RAW.CAST_TO_VARCHAR2(DBMS_CRYPTO.DECRYPT(DBMS_CRYPTO.ENCRYPT(SRC=>UTL_I18N.S
——————————————————————————–
SECRETITOS!

Para más información sobre las múltiples formas de encriptación y uso de claves, lo mejor es consultar la documentación del paquete DBMS_CRYPTO.

Acceso a una bbdd MSAccess desde Oracle.

Es posible acceder a bases de datos no oracle de forma transparente con Oracle Transparent Gateways.

Esta funcionalidad permite configurar una entrada DNS de ODBC como si se tratara de un servicio Oracle cualquiera. De este modo, mediante un dblink, un usuario podría acceder a una tabla de una base de datos access (o SQLServer o MySQL, por ejemplo) desde una query o desde PL/SQL.

La arquitectura de servicios heterogeneos se basa en la siguiente configuración:

Se especifica una entrada de TNS en el fichero tnsnames.ora que resuelva una conexión al host, puerto y nombre de servicio determinado, con el parámetro HS=OK (Oracle Heterogeneous Services).

Por otro lado, el listener destino tiene definido para ese servicio la invocación a un programa llamado «hsodbc» que, mediante un fichero de parámetros llamado init.ora accede al DNS asociado a la base de datos MSAccess concreta.

De este modo, y creando un dblink, cualquier usuario podría hacer una select que cruzara datos de su esquema Oracle y de una bbdd Access.

PASOS A SEGUIR
1.- Instalar en el diccionario los servicios heterogeneos.
2.- Configurar el tnsnames.ora
3.- Configurar el listener.
4.- Comprobar que el listener y el tnsnames.ora funcionan correctamente.
5.- Configurar el ODBC para la bbdd access.
6.- Crear el DBLINK hacia la bbdd access.
7.- Crear el fichero init.ora
8.- Ejecutar una consulta mediante ese dblink.


1.- Instalar en el diccionario los servicios heterogeneos
@?/rdbms/admin/caths.sql

NOTA: puede que estas vistas estén instaladas ya en la bbdd.

2.- Configurar el tnsnames.ora
bbdd_access =
   (DESCRIPTION=
      (ADDRESS= (PROTOCOL=tcp)
               (HOST=192.168.2.4)
               (PORT=1521)
     )
     (CONNECT_DATA =
         (SERVICE_NAME=odbc_access)
     )
     (HS = OK)
)

3.- Configurar el listener. (la entrada en negrita es la que hay que añadir)…

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )
      (ADDRESS_LIST =
         (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.4)(PORT = 1521))
      )
    )
  )

SID_LIST_LISTENER =
   (SID_LIST =
    (SID_DESC =
       (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:oracleora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:oracleora92)
      (SID_NAME = orcl)
    )
     (SID_DESC =
      (ORACLE_HOME = D:oracleora92)
      (SID_NAME = odbc_access)
       (PROGRAM = hsodbc)
    )
  )

4.- Comprobar que el listener y el tnsnames.ora funciona correctamente.

C:Documents and SettingsAdministrador>tnsping bbdd_access

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 – Production on 08-SEP-2006 18:19:42

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Archivos de parßmetros utilizados:
D:oracleora92networkadminsqlnet.ora

Adaptador TNSNAMES utilizado para resolver el alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=tcp) (HOST=192.168.2.4) (PORT=1521)) (CONNECT_DATA = (SERVICE_NAME=odbc_access)) (HS = OK))
Realizado correctamente (50 mseg)

5.- Configurar el ODBC para la bbdd access.
La base de datos access utilizada en el ejemplo tiene una única tabla con cuatro filas de ejemplo…

create table ejemplo

   (id counter,
    valor text)

Es preciso crear un ODBC para acceder a la bbdd access determinada. Desde Windows esta opción se configura en Panel de control, Herramientas administrativas, Administrador ODBC y la pestaña DNS de sistema.

desde ahí se crea el driver    odbc_access vinculado a la base de datos que uso: «test_hs.mdb» con esta tabla de ejemplo.

NOTA: esta bbdd access tiene password «tesths»

6.- Crear el DBLINK hacia la bbdd access.

SQL> CREATE DATABASE LINK bbdd_access
2      CONNECT TO ADMIN IDENTIFIED BY TESTHS
3      USING ‘bbdd_access’;

Enlace con la base de datos creado.

7.- Crear el fichero init.ora en el directorio de ORACLE_HOME   /hs/admin/

— FICHERO initODBC_ACCESS.ora
#
# HS init parameters
#

HS_FDS_CONNECT_INFO = odbc_access
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = odbc_access.trc
HS_AUTOREGISTER = TRUE

8.- Lanzar la query via ese dblink

SQL> column «valor» format a40
SQL> set pages 1000
SQL> set lines 120
SQL> select * from ejemplo@bbdd_access;

        id valor
———- —————————————-
         1 dato1
         2 dato2
         3 dato3
         4 dato4

Funciones Analíticas.

Hay veces en las que encuentro consultas SQL a las que llamo «query ataque de pánico», porque puedo imaginarme el sufrimiento del que la programó. Con mucha frecuencia, este tipo de consultas aparecen en casos en las que una función analítica hubiera resuelto toda la complejidad del problema.

Al detalle. Una consulta del tipo «Datos de cliente con la fecha del primer contrato, fecha de la primera cancelación de contrato, fecha del último contrato contratado, fecha de…» suele consultarse con una subconsulta para cada «fecha de…».

Éste ejemplo, o el típico «Los tres contratos más recientes, las cinco últimas cancelaciones, etc.» siempre hacen que los programadores realicen una subconsulta por cada una de las condiciones… y otra y otra y al final el rendimiento se incrementa tanto de consultar varias veces la misma tabla.

…evidentemente, la consulta SQL se ha hecho tan vasta que resulta muy complicado mantenerla.

Para esta casuística, las funciones analíticas se aplican a un subconjunto de registros, por lo que Oracle, para gestionarlo correctamente, crea una ventana SQL intermedia para reagrupar una y otra vez los resultados de una consulta. Así, dado el anterior ejemplo, Oracle tomaría todos los contratos de ese cliente y los agruparía para cada columna de resultados: el primer contrato contratado, el primer cancelado, el último contrato de alta, etc. sin necesidad de consultar una y otra vez la tabla de contratos.

Las funciones analíticas tienen la siguiente sintaxis (no es la sintaxis completa).

FUNCIÓN_ANALITICA(campo)
OVER (PARTITION BY campo_agr1, campo_agr2
ORDER BY campo_ord1 NULLS LAST)

Las funciones analíticas están descritas en la documentación de Oracle y entre éstas, por ejemplo, se hallan MAX(), MIN(), RANK(), etc.

Un ejemplo de su uso sería, por ejemplo, intentar corregir esta consulta:


SELECT     a.ID_FACTURA,                                  
           a.FALINEA_AUX – b.minCount + 1 ID_FALINEA,     
           a.ID_CLIENT,                                   
           a.ID_COMPTEFACT,                               
           a.PRODUCT_ID,                                  
           a.ID_PRCATPRODUCTE,                            
           a.DS_PRNUMSERVEI,                              
           a.ID_FACONCEPTE,                               
           a.DT_FAFACTURACIO,                             
           a.NUM_FAIMPORTCONCEPTE,                        
           a.PRODUCT_LABEL,                               
           a.DT_MOVIMENT,                                 
           a.FG_TIPUSOPERACIO,                            
           a.asset_id,                                    
           a.PRODUCT_ATTR_VALUE                           
FROM vw_ci_linia_factura_tmp a,                       
     (select t.id_factura,                                
             t.dt_fafacturacio,                           
             min(t.falinea_aux) minCount                  
      from vw_ci_linia_factura_tmp t                  
      group by t.id_factura,                              
               t.dt_fafacturacio                          
     ) b                                                  
WHERE a.id_factura = b.id_factura                         
ORDER BY a.id_factura, a.FALINEA_AUX – b.minCount + 1 ASC;

En este caso, la necesidad de obtener la primera línea de factura para cada linea de factura, parece que obliga a cargar dos veces la vista (y su conjunto de tablas) en memoria y hacer una JOIN entre ambas.

No es necesario. Los costes de ejecución se reducen a la mitad.


SELECT     a.ID_FACTURA,                     
           a.FALINEA_AUX – min(falinea_aux) over 
(partition by id_factura, dt_fafacturacio) +1 ID_FALINEA,      
           a.ID_CLIENT,                   
           a.ID_COMPTEFACT,             
           a.PRODUCT_ID,                 
           a.ID_PRCATPRODUCTE,           
           a.DS_PRNUMSERVEI,             
           a.ID_FACONCEPTE,              
           a.DT_FAFACTURACIO,            
           a.NUM_FAIMPORTCONCEPTE,       
           a.PRODUCT_LABEL,              
           a.DT_MOVIMENT,                
           a.FG_TIPUSOPERACIO,           
           a.asset_id,                   
        a.PRODUCT_ATTR_VALUE             
FROM sta_vw_ci_linia_factura_tmp a       
ORDER BY 1,2 ASC;