El blogoracle de Javier Morales

Instalación Oracle Grid Control 11g para Solaris/Linux plataforma 64bit.

2011-06-23T17:03:00.001+02:00

INGREDIENTES:

Java versión 1.6u18 (Descargar del archivo histórico de versiones antiguas de Java)

http://www.oracle.com/technetwork/java/archive-139210.html

NOTA: En Solaris es necesario descargar el bundle de JDK 32bit y las librerías de 64bit.

Java SE Development Kit 6u18
jdk-6u18-solaris-sparcv9.sh

Java SE Development Kit 6u18
jdk-6u18-solaris-sparcv9.tar.Z

Weblogic 10.3.2 (Descargar del archivo histórico de versiones de Weblogic)

http://www.oracle.com/technetwork/middleware/weblogic/downloads/wls-main-097127.html

Oracle11gR2 (11.2.0.2)

Oracle Grid Control 11.1.0.1

PASOS A SEGUIR:

1.- Instale la versión de java en un directorio a su gusto, y compruebe la versión.

oracle@leg93uxgrid:~$ /u01/app/oracle/java/jdk1.6.0_18/bin/java -version

java version "1.6.0_18"

Java(TM) SE Runtime Environment (build 1.6.0_18-b07)

Java HotSpot(TM) Server VM (build 16.0-b13, mixed mode)

2.- Tras comprobar con un xclock que las Xwindows están habilitadas, ejecute el weblogic invocando el java recién instalado.

oracle@leg00uxgrid:~$ /u01/app/jdk-6u18/jdk1.6.0_18/bin/java -d64 -jar /software/wls1032_generic.jar

Extracting 0%..................................................................................100%

2.1.- Escoger “Typical” con las opciones por defecto.

2.2.- En la última pantalla “Installation Complete” DESACTIVAR la opción de “Run Quickstar”.

2.3.- Done.

4.- SOLO EN LINUX. Instalar el parche de Weblogic WDJ7

oracle@leg00uxgrid:~$ /u01/app/Middleware/utils/bsu/bsu.sh

5.- Verifique los parches e instale el software de Oracle11g.

6.- Cree una base de datos para el respositorio del Grid Control.

6.1.- Escoger el modo “Personalizado” y desmarcar la opción “Configure Enterprise Manager”.

6.2.- En el apartado de componentes, desmarcar “Enterprise Manager Repository”.

6.3.- Definir los parámetros de procesos a más de 500 y session_cachad_cursors a 550

6.4.- Aumentar el datafile de UNDO a 2Gb

7.- Desactivar dbconsole y el repositorio de Enterprise Manager de la base de datos recién creada en caso de que exista (al haber ejecutado incorrectamente el paso 6.1).

oracle@leg93uxgrid:~$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at 13/06/2011 13:17:30

EM Configuration Assistant, Version 11.2.0.0.2 Production

Enter the following information:

Database SID: omsgrid

Listener port number: 1521

Password for SYS user:

Password for SYSMAN user:

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

WARNING : While repository is dropped the database will be put in quiesce mode.

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

Do you wish to continue? [yes(Y)/no(N)]: y

8.- Habilitar el usuario DBSNMP.

SQL> alter user DBSNMP account unlock;

User altered.

SQL> alter user DBSNMP identified by grid13c1sa;

User altered.

9.- Instalar Oracle Enterprise Manager Grid Control 11g.

Etiquetado de discos para ASM en Solaris.

2011-05-04T14:07:00.001+02:00

* Con la ayuda de Sergio Ramírez en la parte de Solaris. Muchísimas gracias, compañero!.

Cuando he tenido que etiquetar discos en linux para que el ASM los reconozca para candidatos, la librería asmlib me lo ha puesto muy fácil, pues solamente era necesario marcar los discos de la siguiente forma.

[root@host Server]# /etc/init.d/oracleasm createdisk ASMDISK1_DATA /dev/mapper/racdb_asm1

Marking disk "ASMDISK1_DATA" as an ASM disk: [ OK ]

[root@host Server]# /etc/init.d/oracleasm createdisk ASMDISK1_FRA /dev/mapper/racdb_asm2

Marking disk "ASMDISK1_FRA" as an ASM disk: [ OK ]

[root@cun0401 Server]# /etc/init.d/oracleasm listdisks

ASMDISK1_DATA

ASMDISK1_FRA

Pero esta librería no está disponible para Solaris y el proceso de etiquetar los discos para que ASM los vea como candidatos no resulta tan evidente como se describe en la documentación de instalación de grid en Solaris.

En concreto, una vez los discos están visibles y los dispositivos son del propietario grid:dba , en el comando format hay que modificar los volúmenes tal como se muestra.

root@host:~$ format

Searching for disks...done

AVAILABLE DISK SELECTIONS:

0. c0t0d0

/pci@0,600000/pci@0/pci@8/pci@0/scsi@1/sd@0,0

1. c0t1d0 Solaris

/pci@0,600000/pci@0/pci@8/pci@0/scsi@1/sd@1,0

2. c2t600A0B800029B03400001B474BE28D9Bd0

/scsi_vhci/ssd@g600a0b800029b03400001b474be28d9b

3. c2t600A0B800029B034000022A64DB65A53d0

/scsi_vhci/ssd@g600a0b800029b034000022a64db65a53 --> Este es el disco a añadir

4. c2t600A0B800029B034000020104C43B3C1d0

/scsi_vhci/ssd@g600a0b800029b034000020104c43b3c1

5. c2t600A0B800029B034000021104C612E6Dd0

/scsi_vhci/ssd@g600a0b800029b034000021104c612e6d

6. c2t600A0B8000269A3200007F3F4BE25284d0

/scsi_vhci/ssd@g600a0b8000269a3200007f3f4be25284

7. c2t600A0B8000269A32000084B24CC8D1D5d0

/scsi_vhci/ssd@g600a0b8000269a32000084b24cc8d1d5

8. c2t600A0B8000269A32000084C74CD0F3D2d0

/scsi_vhci/ssd@g600a0b8000269a32000084c74cd0f3d2

9. c2t600A0B8000269A320000802A4BF3820Ad0

/scsi_vhci/ssd@g600a0b8000269a320000802a4bf3820a

10. c2t600A0B8000269A320000834C4C439596d0

/scsi_vhci/ssd@g600a0b8000269a320000834c4c439596

11. c2t600A0B8000269A320000834F4C43960Fd0

/scsi_vhci/ssd@g600a0b8000269a320000834f4c43960f

Specify disk (enter its number): 3 --> corresponde al identificador del disco a añadir

Select partitioning base:

0. Current partition table (original)

1. All Free Hog

Choose base (enter number) [0]? 1 --> para modificarlas todas las particiones

Part Tag Flag Cylinders Size Blocks

0 root wm 0 0 (0/0/0) 0

1 swap wu 0 0 (0/0/0) 0

2 backup wu 0 - 51197 100.00GB (51198/0/0) 209707008

3 unassigned wm 0 0 (0/0/0) 0

4 unassigned wm 0 0 (0/0/0) 0

5 unassigned wm 0 0 (0/0/0) 0

6 usr wm 0 0 (0/0/0) 0

7 unassigned wm 0 0 (0/0/0) 0

Do you wish to continue creating a new partition

table based on above table[yes]? Yes -> Confirmamos que deseamos crear nuestras particiones.

Free Hog partition[6]? 7 -> Determina que partición es la que almacena el espacio libre

Enter size of partition '0' [0b, 0c, 0.00mb, 0.00gb]: 500mb -> En la partición 0 definimos un tamaño de 500mb la que necesita ASM para su gestión.

Enter size of partition '1' [0b, 0c, 0.00mb, 0.00gb]:

Enter size of partition '3' [0b, 0c, 0.00mb, 0.00gb]:

Enter size of partition '4' [0b, 0c, 0.00mb, 0.00gb]:

Enter size of partition '5' [0b, 0c, 0.00mb, 0.00gb]:

Enter size of partition '6' [0b, 0c, 0.00mb, 0.00gb]: 99.50gb -> Configuramos el resto del tamaño en una de las particiones

Part Tag Flag Cylinders Size Blocks

0 root wm 0 - 249 500.00MB (250/0/0) 1024000

1 swap wu 0 0 (0/0/0) 0

2 backup wu 0 - 51197 100.00GB (51198/0/0) 209707008

3 unassigned wm 0 0 (0/0/0) 0

4 unassigned wm 0 0 (0/0/0) 0

5 unassigned wm 0 0 (0/0/0) 0

6 usr wm 250 - 51193 99.50GB (50944/0/0) 208666624

7 unassigned wm 51194 - 51197 8.00MB (4/0/0) 16384

Okay to make this the current partition table[yes]? Yes -> Confirmamos que esta correcto tal cual la hemos definido

Enter table name (remember quotes): "asm" -> Le ponemos un nombre al modelo de configuración de la tabla, para posteriores discos para la ASM del mismo tamaño.

Ready to label disk, continue? yes

partition> q --> salimos del menú de partición

FORMAT MENU:

disk - select a disk

type - select (define) a disk type

partition - select (define) a partition table

current - describe the current disk

format - format and analyze the disk

repair - repair a defective sector

label - write label to the disk

analyze - surface analysis

defect - defect list management

backup - search for backup labels

verify - read and display labels

save - save new disk/partition definitions

inquiry - show vendor, product and revision

volname - set 8-character volume name

! - execute , then return

quit

format> verify -> Mediante el comando verify comprobamos que esta correcto.

Primary label contents:

Volume name = < >

ascii name =

pcyl = 51200

ncyl = 51198

acyl = 2

nhead = 64

nsect = 64

Part Tag Flag Cylinders Size Blocks

0 root wm 0 - 249 500.00MB (250/0/0) 1024000

1 swap wu 0 0 (0/0/0) 0

2 backup wu 0 - 51197 100.00GB (51198/0/0) 209707008

3 unassigned wm 0 0 (0/0/0) 0

4 unassigned wm 0 0 (0/0/0) 0

5 unassigned wm 0 0 (0/0/0) 0

6 usr wm 250 - 51193 99.50GB (50944/0/0) 208666624

7 unassigned wm 51194 - 51197 8.00MB (4/0/0) 16384

format> q

Ahora, desde la interfaz gráfica (asmca) o desde sqlplus, Oracle es capaz de identificar los discos como candidatos para añadirlos al diskgroup ASM que corresponda.

-bash-3.00$ sqlplus / as sysasm

SQL> select path, name from v$asm_disk;

PATH                                               NAME
-------------------------------------------------- ------------------------------
/dev/did/rdsk/d9s6
/dev/did/rdsk/d9s7
/dev/did/rdsk/d9
/dev/did/rdsk/d4s6                                 RECOVERY_0000
/dev/did/rdsk/d22s6                                DATOS_0001
/dev/did/rdsk/d18s6                                OCRVOTING_0000
/dev/did/rdsk/d19s6                                OCRVOTING_0001

Al aparecer los tres nuevos volúmenes como candidatos, ya es posible añadir el disco al diskgroup desde sqlplus conectado como SYSASM.

SQL> alter diskgroup DATOS add disk ‘/dev/did/rdsk/d9s6’;

SQL> select path, name from v$asm_disk;
PATH                                               NAME
-------------------------------------------------- ------------------------------
/dev/did/rdsk/d9s7
/dev/did/rdsk/d9
/dev/did/rdsk/d4s6                                RECOVERY_0000
/dev/did/rdsk/d9s6                                 DATOS_0000
/dev/did/rdsk/d22s6                                DATOS_0001
/dev/did/rdsk/d18s6                                OCRVOTING_0000
/dev/did/rdsk/d19s6                                OCRVOTING_0001

"La query que tumba la máquina".

2011-01-27T16:50:00.000+01:00

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

Mapa Mental 1: Oracle 10g RAC en Linux

2010-10-26T10:03:00.000+02:00

En la instalación de un RAC sobre Linux es preciso tener en cuenta las características de la plataforma a los siguientes niveles:

1.- Red. ¿Existen tarjetas de red para conexiones privadas y públicas en todos los nodos? Es preciso configurarlas correctamente para que el cluster pueda manejar las tres direcciones (pública, privada y virtual). ¿Hay redundancia en las tarjetas? ¿está configurado correctamente el bonding?

2.- Almacenamiento. ¿ASM o OCFS2? O uno, u otro. Nunca los dos o una combinación de ambos en el mismo sistema, ya que puede llevar a conflictos. ¿Hay multipath? El almacenamiento con controladoras redundantes debe configurarse correctamente para evitar desastres.

3.- Sistema. ¿Todos los parches están instalados? ¿los parámetros de kernel y límites de sesión están definidos de forma persistente? ¿los privilegios sobre los dispositivos de almacenamiento tienen privilegios sobre root y oracle como corresponde?

Mapa mental 1: estrella

Quizás en estos casos pueda ayudar el siguiente mapa mental, a modo de guía paso a paso, adaptándolo a la plataforma y las características del entorno a instalar.

Mapa mental 2: vertical

Los procesos del mapa al detalle

· Verificar red public y privada

o Ejecutar /sbin/ifconfig
El resultado debe ser dos tarjetas de red visibles, una con la dirección publica y otra con la privada (mas el tcploopback)

§ Configuración Bonding
Cuando hay redundancia de tarjetas es importante conocer la configuración de bonding para hacer referencia a las tarjetas virtuales y no a las físicas.

§ Configuración interconnect
El interconnect debe estar configurado correctamente para las pruebas de caída de servidores. Los switches deben contemplar las posibles caídas y sus enrutamientos.

o Registrar entradas /etc/hosts
Hay que añadir las direcciones IP para publica, privada y virtual.

La ip virtual no será visible hasta que los servicios de cluster se inicien con el vipca al final de la instalación del clusterware.

o Ping entradas /etc/hosts
Ping a los alias públicos y privados, con y sin nombre de dominio

· Configurar storage

o Crear particiones con /sbin/fdisk
Crear particiones para OCR, voting y rawdevices para ASM.

Minimo:
120 Mb para OCR x 2
20 Mb para voting x 3

Si no se crean particiones, los discos deben estar preparados, así como sus dispositivos.

o Editar /etc/sysconfig/rawdevices

o Asignar privilegios a los rawdevices
Los dispositivos deben pertenecer al usuario Oracle y tener 660 (ocr) y 664 (voting)

Una vez creado el clusterware, los ocr pasaran a ser de root, y será necesario hacer persistentes los permisos al arranque en el fichero /etc/rc.d/rc.local

o Habilitar rawdevices nodo 1

o Propagar configuración nodo 2-3-x
El fichero /etc/sysconfig/rawdevices ha de propagarse al resto de nodos.

Una vez copiado el fichero, hay que actualizar la tabla de partición y habilitar los rawdevices en ese nodo.

services rawdevices start

o Los rawdevices están habilitados en todos los nodos

· Crear grupos y usuarios

o Crear grupos dba y oinstall

o Crear usuario oracle

o chmod y chown a usuario

o Definir .bash_profile

o Configurar secure shell
Generar claves rsa y dsa para concatenar en todos los ficheros authorized_keys de los diferentes nodos.

o Comprobar ssh transparente entre nodos

· Configuración OS

o Configuración Secure Shell
El usuario Oracle debe poder hacer ssh entre nodos sin necesidad de contraseña.

§ Crear claves rsa y dsa
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa

Los ficheros de $HOME/.ssh id_dsa.pub y id_rsa.pub de todos los nodos han de unirse en uno nuevo llamado authorized_keys, que se copiará a los directorios .ssh del usuario oracle para cada nodo.

§ Añadir hosts a known_hosts
En cada ssh el sistema preguntará para añadir ese nodo a la lista de know_hosts. Solo se preguntará esta vez y las siguientes conexiones serán transparentes.

§ Test ssh nodos /etc/hosts

o Configuración hangcheck-timer
Según notas 811306.1 y 726833.1
Es necesario añadir la línea

/sbin/modprobe hangcheck-timer hangcheck_tick=1 hangcheck_margin=10 hangcheck_reboot=1

Y hacer este cambio permanente al reinicio del servidor en /etc/rc.d/rc.local

o Configurar hugepages
Según nota 361468.1
Es necesario añadir la siguiente línea a /etc/security/limits.conf

* hard memlock 5243000
* soft memlock 5243000

o Configurar kernel

o Configurar limites shell
Los limites de shell han de hacerse permanentes en el inicio de sesión. Para ello, hay que añadir la siguiente línea a /etc/pam.d/login

session required /lib/security/pam_limits.so

o rda hcve para cada nodo

o Cluvfy

§ Chequeo hardware y OS
./runcluvfy.sh stage -post hwos -n nodo1,nodo2,nodoN - verbose

§ Chequeo conectividaf
./runcluvfy.sh comp nodecon -n nodo1,nodo2,nodoN - verbose

§ Chequeo usuario
./runcluvfy.sh comp admprv -n nodo1,nodo2,nodoN - verbose -o user_equiv

§ PreCheck configuración servicios de cluster
./runcluvfy.sh stage -pre crsinst -n nodo1,nodo2,nodoN -r 10gR2

· Ejecutar runInstaller clusterware software
Definir variables de entorno para ORA_CRS_HOME

o Seguir nota 414163.1 en RHL5 para errores conocidos
Existen tres errores conocidos en la instalación de RAC 10.2.0.1 en RHL5

- ejecutar el runinstaller con -ignoreSysPrereqs
- editar vipca y svrctl para añadir la línea "unset LD_ASSUME_KERNEL"
- La ejecución de root.sh fallará en el ultimo nodo y habrá que ejecutar el vipca manualmente tras configurar las tarjetas de red con

/bin # ./oifcfg setif -global eth0/192.168.1.0:public
/bin # ./oifcfg setif -global eth1/10.10.10.0:cluster_interconnect
/bin # ./oifcfg getif
eth0 192.168.1.0 global public
eth1 10.10.10.0 global cluster_interconnect

o Chequeo servicios
Ejecutar crs_stat -t

Los servicios de cluster deben estar arrancados.

o Instalar patchset 10.2.0.5 clusterware
Instalar la versión mas reciente del patchset.
Mucho mejor antes de que haya ASM y DB's que deban pararse y parchearse.

· Ejecutar runInstaller ASM software
Definir variables de entorno para ORA_ASM_HOME

o Instalar asmlib
Instalar librerias asmlib

o Configurar multipath para ASM
Seguir nota 564580.1 si hay redundancia de controladores de disco por multipath

o Creación de diskgroups
Crear dos diskgroups: uno para DATA y el otro para FRA

DG_DATA_1
DG_FRA_1

o Chequeo servicios
Ejecutar crs_stat -t

Los servicios de cluster deben estar arrancados incluyendo los de ASM.

o Instalar patchset 10.2.0.5 ASM
Instalar la versión mas reciente del patchset.
Mucho mejor antes de que haya ASM y DB's que deban pararse y parchearse.

· Ejecutar runInstaller DB software
Definir variables de entorno para ORACLE_HOME

o Crear bbdd RAC usando asm
Seguir nota 564580.1 si hay redundancia de controladores de disco por multipath

o Chequeo servicios
Ejecutar crs_stat -t

Los servicios de cluster deben estar arrancados incluyendo los de ASM y los de base de datos.

o Instalar patchset 10.2.0.5 ASM
Instalar la versión mas reciente del patchset.
Mucho mejor antes de que haya ASM y DB's que deban pararse y parchearse.

Plagiado.

2010-06-29T17:09:00.000+02:00

Alguien llamado Fernando Valenzuela me ha plagiado unos siete artículos.

En la mayoría ni siquiera ha cambiado la tipografía, ni los datos de ejemplo, ni comentarios personales como dedicatorias o alusiones a las personas que motivaron la creación de esa entrada, o los responsables de las peticiones o consultas.

Me parece algo muy grave, sobre todo teniendo en cuenta que todo el contenido que hay publicado en mi blog está bajo una licencia creativa (Creative Commons) que permite prácticamente cualquier uso de mi trabajo, o incluso hacer obras derivadas, siempre y cuando se reconozca al autor, y se publique bajo una licencia similar sin provecho comercial.

En concreto, los artículos plagiados son:

Copiar una Base de datos en Windows
Original creado el 29 de Julio de 2005, y plagiado el 31 de agosto de 2009

Acceso a una bbdd MSAccess desde Oracle
Original creado el 8 de Septiembre de 2006 y plagiado el 31 de agosto de 2009

Uso de Logminer
Original creado el 21 de Marzo de 2006 y plagiado el 31 de agosto de 2009

Optimización SQL - varios artículos
Original creados el 25 de Octubre de 2005, el 17 de Octubre de 2005 , el 13 de Octubre de 2005 , y el 11 de Octubre de 2005, y plagiados el 31 de agosto de 2009 aquí y aquí.

Encriptación de datos desde aplicación.

2010-06-24T15:35:00.004+02:00

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.

Oracle10g Data Miner.

2010-06-11T12:47:00.002+02:00

Una de las cosas más interesantes en la explotación de una base de datos es, finalmente, la minería de éstos.

El concepto de "minería de datos" se basa en el análisis de los datos con fines predictivos, para encontrar patrones ocultos en éstos... ¿quien podría adivinar que a una determinada hora o un determinado día de la semana se consume un determinado producto? ¿o que un producto orientado a hombres (cuchillas de afeitar) pasa a ser usado por mujeres solteras de un rango de edad?

Una predicción de este tipo podría sugerir la creación de una nueva linea de producto, ofertas, etc.

Hace tiempo impartí una conferencia sobre cómo implementar un modelo de base de datos de reservas en vuelos, desde un diagrama entidad-relación concreto hasta la explotación de datos históricos en el futuro. La aplicación pasaba por varias etapas (diseño, implementación, uso/cargas de datos, paso a histórico y reporting), y aunque los datos eran cargas completamente aleatorias, sucedían ciertos patrones interesantes.

Los casados tomaban vuelos a Roma, los solteros a Milán, "Air France" viajaba con los vuelos a medio llenar, y otras compañías apenas tenían uno o dos vuelos con pérdidas...

De modo que se me ocurrió hacer una prueba de minería de datos, para buscar una predicción que no pudiera verse "a simple vista". Éste fue el resultado:

Ejemplo de Uso - Oracle Data Miner - Parte 1/2

Ejemplo de Uso - Oracle Data Miner - Parte 2/2

Al final, la predicción (absurda) sobre los datos aleatorios fue una relación de probabilidades en las cuales un determinado tipo de persona (hombre/mujer, de una edad, de una ciudad) reservaría billetes con precios superiores a la media.

Acceso a una bbdd MSAccess desde Oracle.

2006-09-08T20:16:00.000+02:00

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:\oracle\ora92)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = orcl)
    )
     (SID_DESC =
      (ORACLE_HOME = D:\oracle\ora92)
      (SID_NAME = odbc_access)
       (PROGRAM = hsodbc)
    )
  )

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

C:\Documents and Settings\Administrador>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:\oracle\ora92\network\admin\sqlnet.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

Oracle Enterprise Manager. Ejemplo de uso.

2006-08-27T13:41:00.000+02:00

Oracle Enterprise Manager (OEMGR) es una herramienta que permite monitorizar bases de datos Oracle mediante dos tipos de conexiones:
- Standalone (o modo autónomo)
- Mediante OMS (Oracle Management Server)

El modo Standalone funciona como una aplicación estándar cliente/servidor, mientras que la conexión a un OMS utiliza un repositorio intermedio con información global sobre las bases de datos registradas.

(ver el video en pantalla completa)

00:15 - Autentificación con el OMS.

00:30 - Packs de aplicaciones disponibles en OEMGR

01:00 - Conexión a una base de datos y cambios en la SGA (memória). Monitorización de sesiones.

02:00 - Monitorización y gestión de tablespaces. Añadir un datafile a un tablespace y visualización del SQL generado.

03:30 - Monitorización de varias bases de datos de forma global. Sobre estas bases de datos OEMGR permite implementar trabajos (jobs) y alertas que actuen ante cualquier cambio de interés a monitorizar.

03:55 - Creación de una alerta sobre una bbdd.

05:30 - La alerta, creada correctamente, hace visualizar en el mapa una bandera verde que confirma que todo funciona correctamente.

Funciones Analíticas.

2006-05-14T14:20:00.000+02:00

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;

Hablemos de Constraints.

2006-04-12T18:06:00.000+02:00

Las constraints son reglas que ofrecen una garantía de integridad en la información vinculada. Existen 5 tipos de constraints.

-   Primary key
-   Foreign key
-   Unique key
-   Not Null
-   Check

Las constraints tienen, básicamente, tres propósitos:

         1.- Forzar integridad. Una constraint con estado ENABLED evita que, sobre las tablas, puedan modificarse valores de forma que no se satisfaga la regla de la constraint.

         2.- Validar la información. Una constraint con estado DISABLED VALIDATE realiza una validación sobre los datos, certificando que la información contenida en las tablas satisface la regla de la constraint.

         3.- Asumir la validez de la información. Una constraint con estado RELY no verifica la información, ni fuerza la integridad para las modificaciones y asume que los datos introducidos satisfacen la regla de la constraint.

Las constraints, por tanto, pueden estar ENABLED o DISABLED.
Las constraints habilitadas cumplen el propósito 1. Ningún dato introducido o modificado viola la integridad de la regla de la constraint. De este modo, sobre una primary key ENABLED no se permite un insert sobre una fila ya existente. No obstante, si la constraint está en el estado ENABLED NOVALIDATE, no se permitirán cambios que violen la regla, pero no se verificarán que los datos existentes la cumplan.

Las constraints, por tanto, pueden estar en estado VALIDATE o NOVALIDATE independientemente de si están ENABLED o DISABLED.
Las constraints con VALIDATE cumplen el propósito 2: La información existente está validada. No obstante, si la constraint está deshabilitada con DISABLED VALIDATE, no se permitirán cambios sobre las columnas afectadas, ya que aunque la constraint no está habilitada, certifica que los datos existentes son válidos y cumplen con la regla de la constraint.

Las constraints, por tanto, pueden estar en estado RELY o NORELY (también conocido como BELIEF) cuando las constraints están en estado DISABLED NOVALIDATE.
Las constraints en ese estado cumplen con el propósito 3: Asumen que la información es correcta y aportan al optimizador información válida para el uso de vistas materializadas, optimización de queries, etc. De este modo, por ejemplo, al optimizador le puede resultar mucho más fácil encontrar tablas FACT o DIMENSION por las constraints de foreign key entre las tablas.

Las constraints DISABLED NOVALIDATE RELY ni validan la información de la tabla, ni comprueban que las modificaciones cumplen las reglas de las restricciones.

Ejemplo 1: Una restricción de clave primaria habilitada con la opción de NOVALIDATE. No verifica la integridad referencial de las filas, pero no permite que las nuevas filas puedan violar la restricción.

SQL> create table valores(
  2   id number,
  3   valor varchar2(50),
  4   descrip varchar2(300),
  5   CONSTRAINT PK_VALORES PRIMARY KEY (id) deferrable initially immediate);

Tabla creada.

SQL> alter table valores disable primary key;

Tabla modificada.

SQL> insert into valores values (1,'ELEMENTO UNO','Primer elemento, tabla vacía');

1 fila creada.

SQL> insert into valores values (1,'ELEMENTO UNO''','Primer elemento, repetido');

1 fila creada.

SQL> insert into valores values (1,'ELEMENTO UNO''''','Primer elemento, otra vez repetido');

1 fila creada.

SQL> alter table valores enable novalidate primary key;

Tabla modificada.

SQL> insert into valores values (1,'ELEMENTO UNO''''''','Primer elemento, violando PK, repetido');
insert into valores values (1,'ELEMENTO UNO''''''','Primer elemento, violando PK, repetido')
*
ERROR en lÝnea 1:
ORA-00001: restricci¾n ·nica (DW_VIAJES.PK_VALORES) violada

SQL> select * from valores;

        ID VALOR                                    DESCRIP
---------- ---------------------------------------- ----------------------------------------
         1 ELEMENTO UNO                             Primer elemento, tabla vacía
         1 ELEMENTO UNO'                            Primer elemento, repetido
         1 ELEMENTO UNO''                           Primer elemento, otra vez repetido

Ejemplo 2: Una restricción de clave primaria deshabilitada con la opción de VALIDATE. Verifica la integridad referencial de las filas, pero como está deshabilitada, no permite DML sobre la columna.

SQL> truncate table valores;

Tabla truncada.

SQL> alter table valores disable primary key;

Tabla modificada.

SQL> insert into valores values (1,'ELEMENTO UNO','Primer elemento, tabla vacía');

1 fila creada.

SQL> insert into valores values (2,'ELEMENTO DOS','Segundo elemento, ID distinto.');

1 fila creada.

SQL> insert into valores values (3,'ELEMENTO TRES','Tercer elemento, ID distinto.');

1 fila creada.

SQL> alter table valores disable validate primary key;

Tabla modificada.

SQL> insert into valores values (3,'ELEMENTO TRES','Tercer elemento, ID distinto.');
insert into valores values (3,'ELEMENTO TRES','Tercer elemento, ID distinto.')
*
ERROR en lÝnea 1:
ORA-25128: No se puede insertar/actualizar/suprimir en la tabla con la restricci¾n (DW_VIAJES.PK_VALORES) desactivada y validada

SQL> insert into valores values (4,'ELEMENTO CUATRO','Cuarto elemento, ID distinto.');
insert into valores values (4,'ELEMENTO CUATRO','Cuarto elemento, ID distinto.')
*
ERROR en lÝnea 1:
ORA-25128: No se puede insertar/actualizar/suprimir en la tabla con la restricci¾n (DW_VIAJES.PK_VALORES) desactivada y validada

Ejemplo 3: Una restricción de clave ajena deshabilitada. Permite la entrada de valores no existentes en la PK. La opción de enable constraint con la cláusula EXCEPTIONS INTO registra en la tabla exceptions los identificadores de las filas que no cumplen la condición de la constraint.

SQL> create table master (id number primary key);

Tabla creada.

SQL> create table detail (id number primary key,  valor varchar2(50),
  2  master_id number constraint fk_master_id references master(id));

Tabla creada.

SQL> insert into master select rownum from user_tables;

40 filas creadas.

SQL> alter table detail disable constraint fk_master_id;

Tabla modificada.

SQL> insert into detail select rownum, object_name, mod(rownum,45)+1 from user_objects;

68 filas creadas.

SQL> alter table detail enable constraint fk_master_id exceptions into exceptions;
alter table detail enable constraint fk_master_id exceptions into exceptions
                                     *
ERROR en línea 1:
ORA-02298: no se puede validar (ORADBA.FK_MASTER_ID) - claves principales no encontradas

SQL> select count(*) from exceptions;

  COUNT(*)
----------
         5
SQL> select * from exceptions;

ROW_ID             OWNER                          TABLE_NAME                     CONSTRAINT
------------------ ------------------------------ ------------------------------ ------------------
AAAY/nAAJAAAd7KAAn ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAo ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAp ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAq ORADBA                         DETAIL                         FK_MASTER_ID
AAAY/nAAJAAAd7KAAr ORADBA                         DETAIL                         FK_MASTER_ID

Uso de logminer.

2006-03-21T17:49:00.000+01:00

En alguna ocasión he necesitado recuperar una transacción validada con COMMIT, pero no deseada o errónea. Pongo un ejemplo:

- Usuario borra una fila.
- Por accidente.
- No sabe de qué fila se trata, pero 'algo' ha dicho "registro borrado".
- (...borrado y validado).
- Ni idea de qué datos tenía la fila.
- Ni él, ni nadie.
- Desde ese momento, la fila X ha desaparecido sin rastro.

Con variaciones, éste caso es bastante frecuente, y si el usuario que ha borrado el registro es lo suficientemente rápido, existe una posibilidad de recuperar los datos perdidos.

Generalmente, todas las transacciones convencionales procesan la información pasando por las áreas de redo log. En estos archivos está toda la información correspondiente al nuevo cambio. Oracle basa toda su actividad en el buen funcionamiento del registro de redo log y, por tanto, en esta área únicamente almacena los datos físicos elementales para rehacer la operación. Por tanto, no se almacena información sobre los valores anteriores.

Lo cual sí sucede en el área de rollback, y ¡la gestión de rollback también pasa por redolog!.

Oracle dispone de un paquete, el LOG MINER que permite rastrear los redo logs (online y archivados) junto con la información de rollback disponible también en éstos. Una vez ejecutado el paquete que crea el fichero de diccionario de objetos, Oracle es capaz de interpretar esos redologs y mostrar información tan válida como el SQL generado y el SQL inverso para "reparar" la acción.

SQL> connect / as sysdba
Conectado.
SQL> show parameters utl

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      c:\oraclefiles

SQL> exec DBMS_LOGMNR_D.BUILD( DICTIONARY_FILENAME =>'dictionary.ora', DICTIONARY_LOCATION => 'c:\oraclefiles');

Procedimiento PL/SQL terminado correctamente.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCL\REDO04A.LOG
C:\ORACLE\ORADATA\ORCL\REDO04B.LOG
C:\ORACLE\ORADATA\ORCL\REDO05A.LOG
C:\ORACLE\ORADATA\ORCL\REDO05B.LOG
C:\ORACLE\ORADATA\ORCL\REDO06A.LOG
C:\ORACLE\ORADATA\ORCL\REDO06B.LOG

6 filas seleccionadas.

SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO04A.LOG');

Procedimiento PL/SQL terminado correctamente.

SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO05A.LOG');

Procedimiento PL/SQL terminado correctamente.

SQL> exec DBMS_LOGMNR.add_logfile('C:\ORACLE\ORADATA\ORCL\REDO06A.LOG');

Procedimiento PL/SQL terminado correctamente.

SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME =>'c:\oraclefiles\dictionary.ora');

Procedimiento PL/SQL terminado correctamente.

SQL> set pages 100
SQL> set lines 120
SQL> column sql_redo format a50
SQL> column sql_undo format a50

SQL> select sql_redo, sql_undo from v$logmnr_contents where rownum<10;

SQL_REDO                                           SQL_UNDO
-------------------------------------------------- --------------------------------------------------
insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '170,01' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '170,01' and "Importe MIN" = '
,"Detalle Vuelo") values ('170,01','1','170,01','1 170,01' and "Importe AVG" = '170,01' and "Ciudad"
70,01','170,01','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52149');                     d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
                                                    "Detalle Vuelo" = 'VUELO52149' and ROWID = 'AAALV
                                                   mAAPAAAAn8AAA';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '112,95' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '112,95' and "Importe MIN" = '
,"Detalle Vuelo") values ('112,95','1','112,95','1 112,95' and "Importe AVG" = '112,95' and "Ciudad"
12,95','112,95','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52206');                     d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
                                                    "Detalle Vuelo" = 'VUELO52206' and ROWID = 'AAALV
                                                   mAAPAAAAn8AAB';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '123,29' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '123,29' and "Importe MIN" = '
,"Detalle Vuelo") values ('123,29','1','123,29','1 123,29' and "Importe AVG" = '123,29' and "Ciudad"
23,29','123,29','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52331');                     d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
                                                    "Detalle Vuelo" = 'VUELO52331' and ROWID = 'AAALV
                                                   mAAPAAAAn8AAC';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '178,55' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '178,55' and "Importe MIN" = '
,"Detalle Vuelo") values ('178,55','1','178,55','1 178,55' and "Importe AVG" = '178,55' and "Ciudad"
78,55','178,55','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52419');                     d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
                                                    "Detalle Vuelo" = 'VUELO52419' and ROWID = 'AAALV
                                                   mAAPAAAAn8AAD';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '107,36' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '107,36' and "Importe MIN" = '
,"Detalle Vuelo") values ('107,36','1','107,36','1 107,36' and "Importe AVG" = '107,36' and "Ciudad"
07,36','107,36','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52488');                     d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
                                                    "Detalle Vuelo" = 'VUELO52488' and ROWID = 'AAALV
                                                   mAAPAAAAn8AAE';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '120,5' and "Importe COUNT" = '1' a
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" nd "Importe MAX" = '120,5' and "Importe MIN" = '12
,"Detalle Vuelo") values ('120,5','1','120,5','120 0,5' and "Importe AVG" = '120,5' and "Ciudad" = 'M
,5','120,5','Madeira','Portugal','Mayores de 50 a± adeira' and "Pais" = 'Portugal' and "Rango Edad" =
os','Mujer','VUELO52561');                          'Mayores de 50 a±os' and "Sexo" = 'Mujer' and "De
                                                   talle Vuelo" = 'VUELO52561' and ROWID = 'AAALVmAAP
                                                   AAAAn8AAF';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '109,01' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '109,01' and "Importe MIN" = '
,"Detalle Vuelo") values ('109,01','1','109,01','1 109,01' and "Importe AVG" = '109,01' and "Ciudad"
09,01','109,01','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52690');                     d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
                                                    "Detalle Vuelo" = 'VUELO52690' and ROWID = 'AAALV
                                                   mAAPAAAAn8AAG';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '174,91' and "Importe COUNT" = '1'
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" and "Importe MAX" = '174,91' and "Importe MIN" = '
,"Detalle Vuelo") values ('174,91','1','174,91','1 174,91' and "Importe AVG" = '174,91' and "Ciudad"
74,91','174,91','Madeira','Portugal','Mayores de 5 = 'Madeira' and "Pais" = 'Portugal' and "Rango Eda
0 a±os','Mujer','VUELO52693');                     d" = 'Mayores de 50 a±os' and "Sexo" = 'Mujer' and
                                                    "Detalle Vuelo" = 'VUELO52693' and ROWID = 'AAALV
                                                   mAAPAAAAn8AAH';

insert into "DISCOVERER9I"."EUL5_MV101651"("Import delete from "DISCOVERER9I"."EUL5_MV101651" where "
e SUM","Importe COUNT","Importe MAX","Importe MIN" Importe SUM" = '131,5' and "Importe COUNT" = '1' a
,"Importe AVG","Ciudad","Pais","Rango Edad","Sexo" nd "Importe MAX" = '131,5' and "Importe MIN" = '13
,"Detalle Vuelo") values ('131,5','1','131,5','131 1,5' and "Importe AVG" = '131,5' and "Ciudad" = 'M
,5','131,5','Madeira','Portugal','Mayores de 50 a± adeira' and "Pais" = 'Portugal' and "Rango Edad" =
os','Mujer','VUELO52951');                          'Mayores de 50 a±os' and "Sexo" = 'Mujer' and "De
                                                   talle Vuelo" = 'VUELO52951' and ROWID = 'AAALVmAAP
                                                   AAAAn8AAI';

9 filas seleccionadas.

Tremendas artimañas

2005-11-09T17:36:00.000+01:00

En uno de los proyectos en los que estoy trabajando, los desarrolladores utilizan clandestinamente la herramienta TOAD para operar con la base de datos. Por un problema de licencias, se les ha pedido a los desarrolladores que no utilicen esta aplicación, y utilicen otros programas que, para lo que necesitan, cumplen la funcionalidad necesaria.

Parece que TOAD, de Quest, es una herramienta demasiado atractiva para algunos. Incluso ofreciendo alternativas gratuítas al respecto.

Tras comprobar que seguían utilizando TOAD, incluso contra las bases de datos de producción, decidimos implementar el siguiente trigger after logon:

create or replace trigger logon_program after logon on database
declare r_session v$session%rowtype;
begin
    -- Tomamos info de la sesion que se conecta
    select * into r_session
    from v$session
    where audsid=userenv('SESSIONID');

    if ((upper(r_session.program) like '%T%O%A%D%')
           or (upper(r_session.program) like '%TOAD%'))
    then
           raise_application_error(-20002,'Hacking detected. Application not supported.');
    end if;
end;
/

No obstante, hay gente que se sigue resignando a usar sustitutivos a TOAD, y recurren a estratégias tan imaginativas como renombrar el ejecutable TOAD.EXE por SQLPLUSW.EXE

Vamos, casi ni se nota!

SQL> select sid, serial#, program, module, osuser from v$session;

       SID    SERIAL# PROGRAM                        MODULE                                        OSUSER
---------- ---------- ------------------------------ --------------------------------------------- --------------
         1          1 ORACLE.EXE                                                                   SYSTEM
         2          1 ORACLE.EXE                                                                   SYSTEM
         3          1 ORACLE.EXE                                                                   SYSTEM
         4          1 ORACLE.EXE                                                                   SYSTEM
         5          1 ORACLE.EXE                                                                   SYSTEM
         6          1 ORACLE.EXE                                                                   SYSTEM
         7          1 ORACLE.EXE                                                                   SYSTEM
         8          1 ORACLE.EXE                                                                   SYSTEM
        10       1293 jrew.exe                       jrew.exe                                      bbdd
        11        333 sqlplus.exe                    sqlplus.exe                                   bbdd
*       13        583 sqlplusw.exe                   T.O.A.D.                                      slaborda
        14          2 JDBC Thin Client               OWB Runtime Service - Infrastructure          SYSTEM
*       17       1091 sqlplusw.exe                   T.O.A.D.                                      slaborda
        18       1006 OraEdit.exe                    OraEdit.exe                                   bbdd
        19        385 QueryReporter.exe              PL/SQL Developer                              bbdd
*       22        819 sqlplusw.exe                   T.O.A.D.                                      slaborda
        23         30 PLSQLDev.exe                   PL/SQL Developer                              bbdd
        37         75 jrew.exe                       jrew.exe                                      fdomingues
*       44         21 FCBARCELONA5-REALMADRID0.exe   T.O.A.D.                                      slaborda
        53        196 JDBC Thin Client               JDBC Thin Client                              fdomingues
        61        413 JDBC Thin Client               JDBC Thin Client                              fdomingues
*       62         50 FCBARCELONA5-REALMADRID0.exe   T.O.A.D.                                      slaborda

22 filas seleccionadas.

Impresionante...

In vs. Exists

2005-09-30T14:20:00.000+02:00

He escrito anteriormente en este artículo sobre el uso de NOT IN y NOT EXISTS, mencionando que se trataba de cláusulas diferentes y que, por lo tanto, podían generar diferentes resultados. También mencioné que en su forma natural, IN y EXISTS suponen semánticamente operaciones distintas con diferentes vías de ejecución la mayoría de los casos, pero que pueden implementarse de forma que los resultados sean idénticos y permitir así el sustituir IN por EXISTS y viceversa.

Antes de desglosar la ejecución de IN y EXISTS para una su optimización, menciono la forma de implementarse.

IN

SELECT campo1, campo2, ..., campoN
FROM   tabla1
WHERE  campoX IN (--subconsulta
                  SELECT campo1
                  FROM   tabla2
                  WHERE  condicion);

Cierto.

Esta consulta podría implementarse como una natural join. Se trata de un ejemplo. Oracle en ambos casos, tratándose de subconsultas simples, las interpreta como joins y resuelven el mismo plan de ejecución.

SELECT t1.campo1, t1.campo2, ..., t1.campoN
FROM   tabla1 t1, tabla2 t2
WHERE  t1.campoX = t2.campo1
  AND  condicion;


Es decir, ambas consultas combinan los resultados de Tabla1 y Tabla2, unidos por las columnas t1.campoX y t2.campo1 de modo que visualicen únicamente los resultados de la tabla1 que cumplen la condición de combinación.

La interpretación de la consulta, realizada con IN, es la siguiente:

Selecciona el campo1, campo2, ... hasta campoN
de la tabla tabla1
unicamente para las filas cuyo campoX se encuentre en la siguiente lista de valores campo1 de la tabla2, que cumplan con la condición condicion.

La forma de resolver esta petición, para el servidor, es consultar la tabla2 en la subconsulta y delimitar después qué campos de la tabla1 cumplen la condición de join.

EXISTS

SELECT campo1, campo2, ..., campoN
FROM   tabla1
WHERE  EXISTS  (--subconsulta
                  SELECT NULL
                  FROM   tabla2
                  WHERE  t1.campoX=t2.campo1
                    AND  condicion);

No existe en esta forma una comparación de campos, salvo en la subconsulta. Ésta devuelve NULL (ni siquiera tendría que devolver un valor concreto para ser válida) y su interpretación semántica es la siguiente:

Selecciona el campo1, campo2, ... hasta campoN
de la tabla tabla1
unicamente cuando la subconsulta devuelva un registro (aunque éste sea NULL).
La subconsulta, por tanto, devolverá un registro para aquellos elementos de la tabla2 cuyo campo1 sea igual que el campoX de la tabla principal.

Por lo que Oracle entiende que la consulta más idónea a resolverse antes es la principal, resolver los valores de campoX, y posteriormente compararlos con tabla2.

Es decir, en la ejecución con EXISTS, Oracle entiende que la consulta principal debe resolverse primero, y la subconsulta después. Justo al contrario que IN, lo que puede producir diferentes planes de ejecución.

Generalmente, IN resulta más ventajoso cuando la ejecución de la subconsulta tiene poco coste respecto a la principal, y EXISTS cuando sucede al revés.

(...continuará...)

Clientes gratuitos. Clientes ligeros.

2005-09-09T18:43:00.000+02:00

Uno de los principales problemas al desarrollar con PL/SQL sobre bases de datos es el uso de una buena herramienta de gestión del código. Los DBA's estamos bastante acostumbrados a trabajar con SQL*Plus, la aplicación consola en modo texto, donde tecleamos con más o menos acierto el código SQL que necesitamos para ejecutar comandos de gestión.

No obstante, programar paquetes en PL/SQL donde puede haber miles de líneas de código, o ejecutar consultas sobre un número elevado de tablas, que pueden devolver un número también elevado de filas, implica necesitar un entorno un poco más cómodo para la programación de estos lenguajes contra la base de datos.

Todos los desarrolladores que conozco tienen predilección por Toad, de Quest Software. Lo entiendo. Es una de las mejores herramientas para desarrolladores y DBA's que conozco. No obstante, es un cliente que lanza muchísimas consultas al diccionario por la gran cantidad de información que presenta y esto puede hacer que la aplicación funcione lenta al compilar, consultar dependencias de objetos y demás. Respecto al desarrollo, Toad no me parece la mejor aplicación para programar en PL/SQL.

Para los desarrolladores suelo recomendar PL/SQL Developer de Allround Automations. Mucho más ergonómica en la debugación, mayor comodidad en la gestión del código y, aunque no posea las herramientas de control de sesiones, comparación de base de datos o traceado de sesiones, lo cierto es que para el trabajo cotidiano del programador, la aplicación resulta mucho más cómoda.

No obstante, estas dos aplicaciones tienen un elevado coste de licencias que muchas empresas no pueden costearse. Mucho menos quien maneja Oracle para su aprendizaje, o programadores de otras áreas que puntualmente tienen que acceder a un servidor Oracle.

Para usuarios con una red lenta, que necesiten un cliente ligero o para programadores que únicamente necesiten ejecutar SQL plano a una base de datos, o para quienes no pueden permitirse licencias de Toad o PL/SQL Developer, aquí paso una relación de herramientas FREEWARE orientadas a plataformas Oracle y muy ligeras de instalar y ejecutar.

Query Reporter, de Allround Automations.

Se trata de una herramienta de SQL que genera los resultados en HTML. Muy cómoda, ligera y fácil de usar. El fabricante es el mismo que el de PL/SQL developer. Esta herramienta sería el "SQL Developer". Puede descargarse directamente aquí.

DBDesigner4, de Fabforce.

Herramienta CASE de modelaje de bases de datos. Cómoda y funcional, genera el código del modelo casi sin retoques. En esta página es posible descargar el programa para Windows y para Linux. Además, incluye manuales disponibles en HTML y PDF.

SQL Tools for Oracle, de SQLTools.

Esta herramienta de código abierto me ha sorprendido por lo versátil que es. Tanto permite sin más problemas resolver un plan de ejecución de una query como extraer la sentencia DDL de creación de un objeto y navegar por todos los objetos y sus dependencias de un usuario concreto. Una herramienta muy bien resuelta, con tutoriales en PDF. Puede descargarse aquí.

DB Comp, de Funduc.

Sobre esta herramienta poco tengo que decir, ya que no la he probado, pero es gratuita y los comentarios que he leído mencionan que es bastante buena. Puede descargarse aquí.

Espero que estos enlaces puedan resultar de utilidad a más de uno.

Búsquedas fuzzy, índices context y gestión documental con Oracle. Parte II.

2005-08-16T10:06:00.000+02:00

(Continua a partir de parte I).

Recientemente he estado hablando con gente interesada en la gestión de contenidos de documentos y, en general, la mayoría utiliza formatos PDF. El anterior artículo se basaba únicamente en documentos word, y he creído interesante hacer un ejemplo de como Intermedia realiza la indexación de documentos word y PDF de forma totalmente transparente.

Algo importante a mencionar sobre los índices context es que éstos no se mantienen automáticamente con las inserciones. Hay una causa mayor referente al impacto del rendimiento que tendría dicha reconstrucción. En este caso, se incluyen seis nuevas filas BFILE vinculadas a ficheros PDF existentes en el mismo directorio. Posteriormente a la inserción, es preciso reconstruir el índice para recrearlo con la nueva relación de términos.

Para la implementación completa del ejemplo (creación de la tabla, el índice, el objeto directory y temas relativos a privilegios) es preciso seguir los pasos de la Parte I.

Pasos:

Parte I.

       1.- Creación de una tabla con columna BFILE
       2.- Creación de un DIRECTORY oracle para vincular los documentos Word.
       3.- Concesión de privilegios sobre el DIRECTORY al usuario CTXSYS, propietario de Oracle Context.
       4.- Inserción de las filas en la tabla DOCUMENTOS.
       5.- Indexación de la columna BFILE.
       6.- Ejemplos de consultas.

Parte II.

       7.- Inserción de las filas de documentos PDF.
       8.- Reconstrucción del índice context.
       9.- Ejemplos de consultas.

SQL> host dir c:\documentos
El volumen de la unidad C no tiene etiqueta.
El número de serie del volumen es: 2416-B169

Directorio de c:\documentos

09/05/2005  09:31       <DIR>          .
09/05/2005  09:31       <DIR>          ..
25/04/2005  13:21              271.360 EntornosNoProd_Permisos extendidos.doc
25/04/2005  13:21               31.232 normas_dba.doc
25/04/2005  13:21              379.904 creacion_entorno_bbdd.doc
25/04/2005  13:21              252.928 CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc
04/06/2002  21:58            1.479.383 Oracle9i - New Features for Administrators - Vol I.pdf
04/06/2002  21:59            1.542.651 Oracle9i - New Features for Administrators - Vol II.pdf
04/06/2002  21:55            1.244.186 Oracle9i - New Features for Developers - Vol I.pdf
04/06/2002  21:57            2.342.972 Oracle9i - New Features for Developers - Vol II.pdf
04/06/2002  21:13            1.245.254 Oracle9i - New Features Overview - Vol I.pdf
04/06/2002  21:14              801.119 Oracle9i - New Features Overview - Vol II.pdf

10 archivos        9.935.424 bytes
               2 dirs   4.970.479.616 bytes libres

SQL> -- Inserción de los seis documentos PDF sobre la tabla
SQL> -- y recreación del índice CONTEXT
SQL> -----------------------------------------------------------------------------------
SQL> insert into documentos values
  2  (5,'PDF','Oracle9i - New Features for Administrators - Vol I.pdf',
  3  BFILENAME('DOCUMENTOS_WORD','Oracle9i - New Features for Administrators - Vol I.pdf'));

1 row created.

SQL> insert into documentos values
  2  (6,'PDF','Oracle9i - New Features for Administrators - Vol II.pdf',
  3  BFILENAME('DOCUMENTOS_WORD','Oracle9i - New Features for Administrators - Vol II.pdf'));

1 row created.

SQL> insert into documentos values
  2  (7,'PDF','Oracle9i - New Features for Developers - Vol I.pdf',
  3  BFILENAME('DOCUMENTOS_WORD','Oracle9i - New Features for Developers - Vol I.pdf'));

1 row created.

SQL> insert into documentos values
  2  (8,'PDF','Oracle9i - New Features for Developers - Vol II.pdf',
  3  BFILENAME('DOCUMENTOS_WORD','Oracle9i - New Features for Developers - Vol II.pdf'));

1 row created.

SQL> insert into documentos values
  2  (9,'PDF','Oracle9i - New Features Overview - Vol I.pdf',
  3  BFILENAME('DOCUMENTOS_WORD','Oracle9i - New Features Overview - Vol I.pdf'));

1 row created.

SQL> insert into documentos values
  2  (10,'PDF','Oracle9i - New Features Overview - Vol II.pdf',
  3  BFILENAME('DOCUMENTOS_WORD','Oracle9i - New Features Overview - Vol II.pdf'));

1 row created.

SQL> commit;

Commit complete.

SQL> ALTER INDEX idx_documentos_doc REBUILD;

Index altered.

-- Las búsquedas CONTEXT sobre índices domain se realizan con la cláusula CONTAINS
-- más documentación al respecto en   http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/cqoper.htm )
------------------------------------------------------------

-- documentos con la palabra 'tablespace' cerca de 'storage'
SQL> select tipo, titulo
  2  from documentos
  3  where CONTAINS(documento, 'tablespace near storage') > 0;

TIPO TITULO
---- ------------------------------------------------------------
PDF  Oracle9i - New Features Overview - Vol II.pdf
PDF  Oracle9i - New Features Overview - Vol I.pdf
WORD Creación de instancia Oracle en UNIX.
WORD Normativa de DBA.

-- documentos con una palabra parecida a 'locally' en un 70%
SQL> select tipo, titulo
  2  from documentos
  3  where CONTAINS(documento, 'fuzzy(locally, 70, 6, weight)', 1) > 0;

TIPO TITULO
---- ------------------------------------------------------------
PDF  Oracle9i - New Features Overview - Vol II.pdf
PDF  Oracle9i - New Features Overview - Vol I.pdf
WORD Creación de un entorno de BBDD.

-- documentos con una palabra parecida a 'locally' en un 60%
-- nota: la palabra LOCAL aparece en estos dos nuevos documentos
SQL> select tipo, titulo
  2  from documentos
  3  where CONTAINS(documento, 'fuzzy(locally, 60, 6, weight)', 1) > 0;

TIPO TITULO
---- ------------------------------------------------------------
PDF  Oracle9i - New Features Overview - Vol II.pdf
PDF  Oracle9i - New Features Overview - Vol I.pdf
WORD Creación de instancia Oracle en UNIX.
WORD Creación de un entorno de BBDD.
WORD Entornos no producción. Permisos extendidos.

-- documentos que hablen sobre 'listener'
SQL> select tipo, titulo
  2  from documentos
  3  where CONTAINS(documento, 'about(listener)') > 0;

TIPO TITULO
---- ------------------------------------------------------------
PDF  Oracle9i - New Features Overview - Vol II.pdf
WORD Creación de instancia Oracle en UNIX.
WORD Normativa de DBA.

-- relevancia de documentos que hablan de 'listener'
-- nota: en 'Normativa de DBA', efectivamente, se habla bastante menos sobre el listener.
SQL> select tipo, CONTAINS(documento, 'about(listener)')||'%' relevancia, titulo
  2  from documentos
  3  where CONTAINS(documento, 'about(listener)') > 0;

TIPO RELEVANCIA TITULO
---- ---------- ------------------------------------------------------------
PDF  41%        Oracle9i - New Features Overview - Vol II.pdf
WORD 91%        Creación de instancia Oracle en UNIX.
WORD 23%        Normativa de DBA.

Más información en http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/csql.htm#21732

Defragmentación de un tablespace mediante scripts.

2005-08-05T16:00:00.000+02:00

En muchas ocasiones recurro a generar scripts a partir de consultas.
Ejecuto en SQL*Plus algo parecido a:

SQL> select 'alter package '||owner||'.'||object_name||' compile;' sentencia
2> from dba_objects where status='INVALID' and object_type ='PACKAGE BODY';

SENTENCIA
------------------------------------------------------------------
alter package USUARIO.MAP02_CUACON02 compile;
alter package USUARIO.MAP01_CUACON03 compile;
alter package USUARIO.MAP01_FLUXHISTO03_CI compile;
alter package USUARIO.MAP01_BALANCEBE03 compile;
alter package USUARIO.MAP01_PAHISTOACT03 compile;
alter package USUARIO.MAP01_RAPPELS03 compile;

6 filas seleccionadas.

y simplemente copio y pego las filas como comandos en la consola SQL.

El procedimiento podrá parecer tosco, pero resulta muy versátil ejecutar una consulta que construya dinámicamente todas las sentencias SQL que se precisan para una determinada tarea y, lo más importante, que se adapten a cualquier entorno.

Un ejemplo de ésto podría ser aplicado a la reorganización de los segmentos de un tablespace.

Para la defragmentación de un tablespace, los objetos deben reconstruirse de la siguiente forma:

   - Tablas:   ALTER TABLE xxxx MOVE STORAGE INITIAL tamaño K/M);
   - Índices:   ALTER INDEX xxxx REBUILD [online];
   - Tablas particionadas:  ALTER TABLE xxxx MOVE PARTITION .......;
   - Índices particionados: ALTER INDEX xxxx REBUILD PARTITION ...... [online];

Teniendo en cuenta que mover una tabla en Oracle9i deja inválidos los índices, una vez ejecutados estos comandos por todas las tablas e índices del tablespace, sería preciso comprobar qué índices (o partitiones de índice) pueden haber quedado afectados y reconstruirlos.

Habría otro detalle a tener presente: los segmentos reconstruidos sobre el mismo tablespace, dificilmente consiguen liberar el espacio contiguo al final de los ficheros, por lo que sería útil hacer un doble movimiento: mover primero a un tablespace vacío, y posteriormente mover los objetos al tablespace original de vuelta.

Si bien esto no es complicado, sí resulta minucioso. Este script, en este caso, puede resultar tremendamente útil.

Una vez defragmentado el tablespace, es preciso ejecutar el segundo script para reconstruir índices, lo que evitará que éstos se queden inválidos después del transporte.

* Nota: Es preciso tener en cuenta el tamaño total de los objetos. Si éstos son grandes, conviene aumentar el tamaño definido como inicial. Lo mejor es utilizar tablespaces gestionados localmente con gestión automática de las extensiones para que Oracle decida, en tablas grandes, hacer extensiones mayores dinámicamente.

-- DEFRAGMENTA_TABLESPACE.SQL --
set verify off
set lines 120
set pages 1000
define tablespace_origen =&TABLESPACE_DATOS
define tablespace_destino=&TABLESPACE_VACIO
define t_extension_inicial=&TAMAÑO_EXTENSION_INICIAL
select 'alter table '||owner||'.'||table_name||
       ' move tablespace &tablespace_destino storage (initial &t_extension_inicial M);'
from dba_tables
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter table '||table_owner||'.'||table_name||
       ' move partition '||partition_name||
       ' tablespace &tablespace_destino storage (initial &t_extension_inicial M);'
from dba_tab_partitions
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter index '||owner||'.'||index_name||
       ' rebuild tablespace &tablespace_destino storage (initial &t_extension_inicial M);'
from dba_indexes
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter index '||index_owner||'.'||index_name||
       ' rebuild partition '||partition_name||
       ' tablespace &tablespace_destino storage (initial &t_extension_inicial M);'
from dba_ind_partitions
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter table '||owner||'.'||table_name||
       ' move tablespace &tablespace_origen storage (initial &t_extension_inicial M);'
from dba_tables
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter table '||table_owner||'.'||table_name||
       ' move partition '||partition_name||
       ' tablespace &tablespace_origen storage (initial &t_extension_inicial M);'
from dba_tab_partitions
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter index '||owner||'.'||index_name||
       ' rebuild tablespace &tablespace_origen storage (initial &t_extension_inicial M);'
from dba_indexes
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter index '||index_owner||'.'||index_name||
       ' rebuild partition '||partition_name||
       ' tablespace &tablespace_origen storage (initial &t_extension_inicial M);'
from dba_ind_partitions
where upper(tablespace_name)='&tablespace_origen';

-- RECONSTRUYE_INDICES.SQL --
select 'alter index '||index_owner||'.'||index_name||
       ' rebuild partition '||partition_name||
       ' tablespace &tablespace_origen storage (initial &t_extension_inicial M);' SENTENCIA
from dba_ind_partitions
where upper(tablespace_name)='&tablespace_origen'
union all
select 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes
where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||
       ' rebuild partition '||partition_name||';'
from dba_ind_partitions
where status='UNUSABLE';

TEST
====
SQL> @c:\reorganiza_tablespace
Introduzca un valor para tablespace_datos: DM_ACTIVIDAD01
Introduzca un valor para tablespace_vacio: TS_VACIO
Introduzca un valor para tama±o_extension_inicial: 1

'ALTERTABLE'||OWNER||'.'||TABLE_NAME||'MOVETABLESPACETS_VACIOSTORAGE(INITIAL1M);'
--------------------------------------------------------------------------------------------------------------
alter table USUARIO.TABLA_XXX02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXX02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXASMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXXX02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXXXX02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXPMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXP02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXAS02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXON02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXPMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXASMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOSMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOSMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOS02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTASMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTAS02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTASMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXXXXY02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXACION02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXACIONMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXACIONMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVS02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVSMA02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVSMES02 move tablespace TS_VACIO storage (initial 1 M);
alter table USUARIO.TABLA_XXXYY02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXY02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXASMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXX02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXX02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXPMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXP02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXAS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXON02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXPMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXASMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOSMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOSMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXCIBOS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTASMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTAS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTASMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXX02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXACION02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXACIONMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXACIONMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVS02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVSMA02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);
alter table USUARIO.TABLA_XXXTVSMES02 move tablespace DM_ACTIVIDAD01 storage (initial 1 M);

52 filas seleccionadas.

Una vez ejecutados todos estos comandos, lanzamos el script que consulta los índices inválidos con el fin de recompilarlos.

SQL> @c:\reconstruye_indices
SENTENCIA
-------------------------------------------------------------------
alter index USUARIO.INDICEXXXOSP02_01 rebuild;
alter index USUARIO.INDICEXXXURAS02_01 rebuild;
alter index USUARIO.INDICEXXXSION02_01 rebuild;
alter index USUARIO.INDICEXXXOSPMES02_01 rebuild;
alter index USUARIO.INDICEXXXRECIBOS02_01 rebuild;
alter index USUARIO.INDICEXXXABTAS02_01 rebuild;
alter index USUARIO.INDICEXXXDA02_01 rebuild;
alter index USUARIO.INDICEXXXCIACION02_01 rebuild;
alter index USUARIO.INDICEXXXTVS02_01 rebuild;
alter index USUARIO.INDICEXXXTVSMA02_01 rebuild;
alter index USUARIO.INDICEXXXTVSMES02_01 rebuild;

11 filas seleccionadas.

Generación de XML mediante query.

2005-08-03T20:42:00.000+02:00

En algunos proyectos en los que he participado, el equipo de front-end, que desarrollaba la interfaz web sobre plataforma .NET, tenía serios problemas para recuperar un conjunto de filas con un objeto tipo array incrustado.

La consulta debía devolver un vector de registros, algunos de ellos formados por una lista de valores. En aquel entonces, el driver de .NET no podía gestionar objetos UDT (tipos de datos definidos por el usuario) y hubo que comprar un componente aparte que tampoco dio buen resultado,pues no soportaba consultas a NESTED TABLES.

Una de las soluciones propuestas fue devolver el resultado (se trataba de fichas de clientes) en formato XML. La idea era buena, pero había alguno que pretendía "decodificar" columnas y filas para elaborar el resultado... ¡escribiendo un procedure con substrs!

No hace falta. La funcionalidad de crear UDT tiene una importancia relevante en las conversiones a XML. Definiendo los tipos como OBJECT y TABLE OF OBJECT podemos crear una estructura de datos facilmente exportable a XML tal y como muestra el ejemplo.

Las tablas tomadas como referencia corresponden a EMP y DEPT del esquema de ejemplo SCOTT.

SQL> create or replace type r_empleado is object(
  2     NUM_EMP          NUMBER(4),
  3     NOMBRE_EMP       VARCHAR2(10),
  4     OFICIO_EMP       VARCHAR2(9),
  5     JEFE_EMP         NUMBER(4),
  6     FCONTRATO_EMP    DATE,
  7     SALARIO_EMP      NUMBER(7,2),
  8     COMISION_EMP     NUMBER(7,2),
  9     DEPARTAMENTO_EMP NUMBER(2));
10  /

Tipo creado.

SQL>
SQL> create or replace type t_empleado is table of r_empleado;
  2  /

Tipo creado.

SQL>
SQL> create or replace type r_dept is object(
  2     NUM_DEPT     NUMBER(2),
  3     NOMBRE_DEPT  VARCHAR2(14),
  4     CIUDAD_DEPT  VARCHAR2(13),
  5     empleados    t_empleado);
  6  /

Tipo creado.

SQL>
SQL> create or replace type t_dept is table of r_dept;
  2  /

Tipo creado.

SQL>
SQL>
SQL> select sys_xmlgen(
  2               r_dept(dept.deptno, dept.dname, dept.loc,
  3                 cast(multiset(select emp.*
  4                                 from emp
  5                                where emp.deptno = dept.deptno) as t_empleado)
  6                    ),xmlformat.createFormat('DEPARTAMENTO')).getClobVal() as XML
  7  from dept
  8  where deptno=10;

XML
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<DEPARTAMENTO>
<NUM_DEPT>10</NUM_DEPT>
<NOMBRE_DEPT>ACCOUNTING</NOMBRE_DEPT>
<CIUDAD_DEPT>NEW YORK</CIUDAD_DEPT>
<EMPLEADOS>
  <R_EMPLEADO>
   <NUM_EMP>7782</NUM_EMP>
   <NOMBRE_EMP>CLARK</NOMBRE_EMP>
   <OFICIO_EMP>MANAGER</OFICIO_EMP>
   <JEFE_EMP>7839</JEFE_EMP>
   <FCONTRATO_EMP>09/06/81</FCONTRATO_EMP>
   <SALARIO_EMP>2450</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
  <R_EMPLEADO>
   <NUM_EMP>7839</NUM_EMP>
   <NOMBRE_EMP>KING</NOMBRE_EMP>
   <OFICIO_EMP>PRESIDENT</OFICIO_EMP>
   <FCONTRATO_EMP>17/11/81</FCONTRATO_EMP>
   <SALARIO_EMP>5000</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
  <R_EMPLEADO>
   <NUM_EMP>7934</NUM_EMP>
   <NOMBRE_EMP>MILLER</NOMBRE_EMP>
   <OFICIO_EMP>CLERK</OFICIO_EMP>
   <JEFE_EMP>7782</JEFE_EMP>
   <FCONTRATO_EMP>23/01/82</FCONTRATO_EMP>
   <SALARIO_EMP>1300</SALARIO_EMP>
   <DEPARTAMENTO_EMP>10</DEPARTAMENTO_EMP>
  </R_EMPLEADO>
</EMPLEADOS>
</DEPARTAMENTO>

Copiar una base de datos.

2005-07-29T17:42:00.000+02:00

Últimamente he tenido la necesidad de copiar bases de datos.

La petición textual era la creación de una base de datos igual que otra, pero con otro nombre. En algunos casos se trataba de replicar el entorno de producción para usarlo como test. En otros, simplemente, testear que el backup permitía recuperar la base de datos sobre otra máquina y dejar esa base de datos operativa como entorno auxiliar.

Cuando la base de datos puede conservar el mismo nombre y mismo SID, es tan fácil como copiar datafiles, redolog, controlfiles y archivos ORA sobre los mismos directorios originales en una máquina con un servidor Oracle instalado*.
* Por supuesto, misma plataforma O.S., versión y release de Oracle.

No obstante, en los casos en los que el nombre debe ser diferente, hay que ser muy preciso con los pasos a seguir. Incluyo dos pasos previos como opcionales por si son de utilidad.

En este ejemplo se asume que disponemos de la posibilidad de hacer un backup en frío de la base de datos.

Pasos preliminares (recomendado):

  - Backup frío, backup caliente, export, Transport Tablespaces, backup del backup, backup de todo lo posible.
  - Reducción de tamaño de los datafiles. (Tom Kyte tiene un fabuloso script para ello). Y otra vez backup.

Pasos a seguir:

  1.- Backup en frío de la BBDD original
  2.- Generación del pfile para la nueva BBDD
  3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...
  4.- Definir el nuevo ORACLE_SID
  5.- Crear el nuevo servicio.
  6.- Creación del fichero de passwords (sólo si el pfile tiene REMOTE_LOGIN_PASSWORDFILE=exclusive)
  7.- Conexión a sqlplus como SYSDBA
  8.- Arranque de la instancia y creación del fichero de parámetros SPFILE
  9.- Backup del controlfile de la BBDD original a traza
  10.- Recreación del controlfile con la cláusula SET NAME.
  11.- Abrir la base de datos con modo OPEN RESETLOGS.

1.- Backup en frío de la BBDD original.
    Restauración del backup sobre nueva ubicación.

-- Ejecutar el resultado de la siguiente sentencia como script.
-- NOTA: cuidado con los nombres duplicados de fichero.
-------------------------------------------------------------
select 'shutdown immediate;' from dual
union all
select 'host copy '||name||' &&directorio_destino' from v$controlfile
union all
select 'host copy '||member||' &directorio_destino' from v$logfile
union all
select 'host copy '||name||' &directorio_destino' from v$datafile
union all
select 'startup' from dual;

-- copia de los ficheros a los directorios destino
---------------------------------------------------

2.- Generación del pfile para la nueva base de datos

SQL> create pfile='?\admin\sid\pfile\inittest.ora' from spfile;

Archivo creado.

3.- Edición del pfile para sustituir: CONTROL_FILES, DB_NAME, y carpetas _DUMP_DEST,...

4.- Definir el nuevo ORACLE_SID

    c:\>set ORACLE_SID=test

5.- Crear el nuevo servicio.

    c:\>oradim -NEW -SRVC OracleServicetest  -startmode auto

6.- Creación del fichero de passwords

    c:\>orapwd file=C:\orant\ora92\database\PWDtest.ora password=xxxxxxxxx

7.- Conexión a sqlplus como SYSDBA

    C:\>sqlplus

    SQL*Plus: Release 9.2.0.6.0 - Production on Vie Jul 29 16:41:22 2005

    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

    Introduzca el nombre de usuario: sys/xxxxxxxxxx as sysdba
    Conectado a una instancia inactiva.

8.- Arranque de la instancia y creación del fichero de parámetros SPFILE

    SQL> STARTUP NOMOUNT PFILE='C:\DIRECTORIO_DESTINO\inittest.ora'
    Instancia ORACLE iniciada.

    Total System Global Area 1074866044 bytes
    Fixed Size                   456572 bytes
    Variable Size             905969664 bytes
    Database Buffers          167772160 bytes
    Redo Buffers                 667648 bytes
    SQL> create spfile from pfile='C:\DIRECTORIO_DESTINO\inittest.ora';

    Archivo creado.

9.- Backup del controlfile de PROD a trace.

    SQL> alter database backup controlfile to trace;

    Base de datos modificada.

10.- A partir de la traza del fichero de control.
     Recreación del controlfile con el SET NAME al nuevo nombre.

     CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS  NOARCHIVELOG REUSE
        MAXLOGFILES 50
        MAXLOGMEMBERS 5
        MAXDATAFILES 133
        MAXINSTANCES 1
        MAXLOGHISTORY 453
     LOGFILE
       GROUP 2 'C:\DATA\TEST\REDO\REDO02.LOG'  SIZE 100M,
       GROUP 3 'C:\DATA\TEST\REDO\REDO03.LOG'  SIZE 100M,
       GROUP 4 'C:\DATA\TEST\REDO\REDO04.LOG'  SIZE 150M,
       GROUP 5 'C:\DATA\TEST\REDO\REDO05.LOG'  SIZE 150M,
       GROUP 6 'C:\DATA\TEST\REDO\REDO06.LOG'  SIZE 200M
      -- STANDBY LOGFILE
     DATAFILE
        (... ficheros...)
        'C:\DATA\TEST\SYSTEM\SYSTEM01.DBF',
        'C:\DATA\TEST\DATA\DATA01.DBF'
     CHARACTER SET WE8MSWIN1252
     ;

     Archivo de control creado.

11.- Abrir la base de datos con modo OPEN RESETLOGS.

     SQL> alter database open resetlogs;

     Base de datos modificada.

12.- Verificación.

     SQL> select instance_name from v$instance;

     INSTANCE_NAME
     ----------------
     test

     SQL> select name from v$database;

     NAME
     ---------
     TEST

     SQL> select status from v$thread;

     STATUS
     ------
     OPEN

12.- Shutdown y Open de la base de datos.

     SQL> shutdown immediate;
     Base de datos cerrada.
     Base de datos desmontada.
     Instancia ORACLE cerrada.

     SQL> startup
     Instancia ORACLE iniciada.

     Total System Global Area 1074866044 bytes
     Fixed Size                   456572 bytes
     Variable Size             905969664 bytes
     Database Buffers          167772160 bytes
     Redo Buffers                 667648 bytes
     Base de datos montada.
     Base de datos abierta.

NOT IN y NOT EXISTS no son lo mismo.

2005-07-25T01:00:00.000+02:00

Aunque no lo parezca, NOT IN y NOT EXISTS no son cláusulas equivalentes. IN y EXISTS devuelven el mismo conjunto de resultados, pero su semántica es completamente distinta. Aun así, los programadores la utilizan indistintamente ya que se han acostumbrado a trabajar con una de las dos y realizan de igual modo la negación.

Por un lado, IN solicita aquellos registros cuyo valor exista dentro de un conjunto de valores. Por otro lado, la cláusula EXISTS incluye el registro si la subconsulta siguente devuelve un valor, sea cual sea, incluído NULL.

De este modo, la sintaxis de IN y EXISTS podrían aprobar los mismos registros si la consulta está bien construída, aunque los planes de ejecución sean completamente distintos.

NOT IN y NOT EXISTS no son sinónimos. El valor NULL determina la diferencia.

Dada la siguiente consulta con NOT IN:

SQL> select count(*) from clientes
  2  where ciu_id_ciudad not in (select ciu_id_ciudad from agencias);

  COUNT(*)
----------
         0

Y su equivalente con NOT EXISTS

SQL> select count(*) from clientes c
  2  where not exists (select a.ciu_id_ciudad from agencias a where c.ciu_id_ciudad=a.ciu_id_ciudad);

  COUNT(*)
----------
         0

Los resultados son idénticos: Efectivamente, no existe ningún cliente que viva en una ciudad donde no haya una agencia. En este caso, hay clientes y agencias en todas las ciudades de la base de datos.

No obstante, si las tablas contienen elementos nulos, las dos operaciones devolverán conjuntos de resultados distintos.

NOT IN y NOT EXISTS no son la misma cláusula con sintaxis distinta.

SQL> update clientes
  2  set ciu_id_ciudad=null
  3  where round(rownum/1000)=0;

499 filas actualizadas.

SQL> select count(*) from clientes
  2  where ciu_id_ciudad not in (select ciu_id_ciudad
  3                              from agencias);

  COUNT(*)
----------
         0

SQL> select count(*) from clientes c
  2  where not exists (select a.ciu_id_ciudad
  3   from agencias a where c.ciu_id_ciudad=a.ciu_id_ciudad);

  COUNT(*)
----------
       499

Así, NOT IN se basa en la consulta de todos los valores respecto un valor concreto para determinar que no está incluido en la lista. Los valores nulos se computan como FALSO y no incluyen el elemento en la selección.

NOT EXISTS se basa en la consulta de un valor y su no existencia en un subconjunto de registros. Los valores nulos se computan como VERDADERO, e incluyen el elemento en la selección.

PL/SQL y ejecuciones en host.

2005-07-21T00:30:00.000+02:00

Por un motivo de seguridad, desde PL/SQL no es posible ejecutar llamadas al host. En principio, ningún módulo programado en PL/SQL debería ejecutar nada en sistema operativo. No obstante, seguro que cualquiera es capaz de recordar alguna situación concreta en la que poder invocar un "copy" o quizás ejecutar algún fichero .exe hubiera sido de gran ayuda.

Existe una forma de dar un rodeo a este escenario.

Oracle permite la llamada a procedimientos externos mediante un servicio extproc que debe configurarse en el fichero de alias de servicios "tnsnames.ora" para el cliente, y en el de configuración del listener "listener.ora" en el lado del servidor.

Los ficheros tendrán este aspecto.

Nota: puede haber cambios por la versión del servidor. El ejemplo se implementó sobre Oracle 8.1.7.

Fichero LISTENER.ORA
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
      )
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = extproc_agent)
      (ORACLE_HOME = E:\oracle\ora92)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ANY")
    )
  )

Fichero TNSNAMES.ORA
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(Key = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = extproc_agent)
    )
  )

Con esta parametrización del listener, y la definición de un servicio para llamadas a 'extproc', Oracle es capaz de invocar desde PL/SQL librerías externas programadas en C o PASCAL.

Pasos:

      1.- Descarga de un compilador de C++. Este ejemplo utiliza C++Compiler de Borland.
      2.- Creación de un programa en C que ejecuta en S.O. un comando dado.
      3.- Compilación del programa e implementación de las librerías.
      4.- Desde Oracle, creación del objeto LIBRARY (biblioteca).
      5.- Creación de un procedimiento PL/SQL que invoque a una función de esa biblioteca.

Creación del programa shell.c
-----------------------------
   #include <windows.h>
   #include <stdio.h>
   #include <stdlib.h>

   void __declspec(dllexport) sh(char *);
   void sh(char *cmd)
   {
       system(cmd);
   }

Compilación del código C y creación de las librerías  .lib y .dll
-----------------------------------------------------------------
  bcc32 -WD shell.c
  implib shell.lib shell.dll

Creación del objeto LIBRARY, en la consola de SQL.
--------------------------------------------------
SQL> create library shell_lib is 'C:\Borland\BCC55\shell.dll';
  2  /

Biblioteca creada.

De modo que ya es posible crear un procedimiento PL/SQL que referencie externamente a esa librería. Ésta se encargará de ejecutar en host el parámetro que le incluyamos. Por ejemplo, un "copy".
--------------------------------------------------------------------------------------------------
SQL> create or replace procedure shell (
  2         cmd IN varchar2)
  3  as external
  4       library shell_lib
  5       name "_sh"
  6       language C
  7       parameters (cmd string);
  8  /

Procedimiento creado.

SQL>

El testeo podrá hacerse con un simple 'exec' desde SQL*Plus, ya que equivale a "BEGIN shell('parametro'); END;"
---------------------------------------------------------------------------------------------------------------
SQL> exec shell('copy c:\test\*.* c:\destino\');

Procedimiento PL/SQL terminado correctamente.

Funciona.

Búsquedas fuzzy, índices context y gestión documental con Oracle. Parte I.

2005-07-19T01:27:00.000+02:00

(Continua en parte II).

En muchas ocasiones he podido ver como el uso de las funcionalidades de gestión documental de Oracle era un misterio para los directores de proyecto, que solían confiar para estas tareas en otras herramientas documentales, analistas y programadores.

Éstos últimos, tenían serios problemas en realizar búsquedas por contenido sobre campos almacenados como CLOB, ya que en la mayoría de casos utilizaban la cláusula LIKE con comodines a ambos lados, lo cual impedía el uso por parte del servidor de cualquier índice asociado a esa columna. Otras veces la solución implementada era aun peor (lowers, uppers, substr, instr... ¡sobre un campo que puede llegar a tener hasta 4Gb de capacidad!). Terrible.

Oracle dispone de una potencia de gestión documental principalmente basada en el uso de índices domain. Se trata de índices por palabras (también conocidos como índices CONTEXT) que permiten a Oracle, en una columna de CLOBs o BFILEs, tener una relación del contenido de estos objetos.

El siguiente ejemplo es un pequeño ejercicio basado en el uso de las funcionalidades documentales del servidor Oracle9i.

Pasos:

       1.- Creación de una tabla con columna BFILE
       2.- Creación de un DIRECTORY oracle para vincular los documentos Word.
       3.- Concesión de privilegios sobre el DIRECTORY al usuario CTXSYS, propietario de Oracle Context.
       4.- Inserción de las filas en la tabla DOCUMENTOS.
       5.- Indexación de la columna BFILE.
       6.- Ejemplos de consultas.

SQL> -- Inicialmente creo una tabla DOCUMENTOS donde vincularé los documentos en formato word
SQL> -- para su posterior indexación

SQL> create table documentos(
  2        id number constraint pk_documentos primary key,
  3        tipo char(4),
  4        titulo varchar2(60) not null,
  5        documento bfile);

Tabla creada.

SQL> -- Es preciso crear un objeto DIRECTORY, vinculado al directorio de sistema operativo
SQL> -- donde se encuentran los documentos word.

SQL> create or replace directory documentos_word as 'c:\documentos';

Directorio creado.

SQL> grant read on directory documentos_word to CTXSYS;

Concesión terminada correctamente.

SQL> host dir c:\documentos
El volumen de la unidad C no tiene etiqueta.
El número de serie del volumen es: 2416-B169

Directorio de c:\documentos

09/05/2005  09:31       <DIR>          .
09/05/2005  09:31       <DIR>          ..
25/04/2005  13:21              271.360 EntornosNoProd_Permisos extendidos.doc
25/04/2005  13:21               31.232 normas_dba.doc
25/04/2005  13:21              379.904 creacion_entorno_bbdd.doc
25/04/2005  13:21              252.928 CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc
               4 archivos        935.424 bytes
               2 dirs   4.859.199.488 bytes libres

SQL> -- Inserción de los cuatro documentos sobre la tabla
SQL> -- y creación del índice CONTEXT (la cláusula es INDEXTYPE IS ctxsys.context)
SQL> -----------------------------------------------------------------------------------
SQL> insert into documentos
  2  values (1,'WORD','Entornos no producción. Permisos extendidos.',
  3          BFILENAME('DOCUMENTOS_WORD','EntornosNoProd_Permisos extendidos.doc'));

1 fila creada.

SQL> insert into documentos
  2  values (2,'WORD','Normativa de DBA.',
  3          BFILENAME('DOCUMENTOS_WORD','normas_dba.doc'));

1 fila creada.

SQL> insert into documentos
  2  values (3,'WORD','Creación de un entorno de BBDD.',
  3          BFILENAME('DOCUMENTOS_WORD','creacion_entorno_bbdd.doc'));

1 fila creada.

SQL> insert into documentos
  2  values (4,'WORD','Creación de instancia Oracle en UNIX.',
  3          BFILENAME('DOCUMENTOS_WORD','CREACION_INSTANCIA_DE_ORACLE_EN_UNIX_v2_4.doc'));

1 fila creada.

SQL> CREATE INDEX idx_documentos_doc ON documentos(DOCUMENTO) INDEXTYPE IS ctxsys.context;

Índice creado.

-- Las búsquedas CONTEXT sobre índices domain se realizan con la cláusula CONTAINS
-- más documentación al respecto en   http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/cqoper.htm )
------------------------------------------------------------

-- documentos con la palabra 'tablespace' cerca de 'storage'
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'tablespace near storage') > 0;

TITULO
------------------------------------------------------------
Creación de instancia Oracle en UNIX.
Normativa de DBA.

-- documentos con una palabra parecida a 'locally' en un 70%
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'fuzzy(locally, 70, 6, weight)', 1) > 0;

TITULO
------------------------------------------------------------
Creación de un entorno de BBDD.

-- documentos con una palabra parecida a 'locally' en un 60%
-- nota: la palabra LOCAL aparece en estos dos nuevos documentos
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'fuzzy(locally, 60, 6, weight)', 1) > 0;

TITULO
------------------------------------------------------------
Creación de instancia Oracle en UNIX.
Creación de un entorno de BBDD.
Entornos no producción. Permisos extendidos.

-- documentos que hablen sobre 'listener'
SQL> select titulo
  2  from documentos
  3  where CONTAINS(documento, 'about(listener)') > 0;

TITULO
------------------------------------------------------------
Creación de instancia Oracle en UNIX.
Normativa de DBA.

-- relevancia de documentos que hablan de 'listener'
-- nota: en 'Normativa de DBA', efectivamente, se habla bastante menos sobre el listener.
SQL> select CONTAINS(documento, 'about(listener)')||'%' relevancia, titulo
  2  from documentos
  3  where CONTAINS(documento, 'about(listener)') > 0;

RELEVANCIA TITULO
---------- ------------------------------------------------------------
78%        Creación de instancia Oracle en UNIX.
20%        Normativa de DBA.

Más información en http://download-west.oracle.com/docs/cd/B10501_01/text.920/a96518/csql.htm#21732

Uso de cursores y bulk insert.

2005-07-17T03:06:00.000+02:00

Uno de los muchos mitos en oracle es que el uso de cursores perjudica seriamente el rendimiento. Incluso he visto algunos proyectos en los que se había prohibido el uso de esta técnica.

El impacto sobre el rendimiento es cierto en parte, ya que el servidor trata individualmente cada registro y, por tanto, esto debería implementarse únicamente cuando fuera estrictamente necesario (sql dinámico, iteraciones dentro del fetch, etc.) utilizando, siempre que sea posible, SQL estándar.

No obstante, hay situaciones en las que no es posible el uso de SQL convencional. Por ejemplo, cuando en una inserción es preciso controlar los errores e insertarlos en otra tabla. Ante este caso, los procedimientos ETL filtran previamente las filas, las mueven a otra tabla, y cuando las filas ya están totalmente "limpias" de errores, entonces realizan
INSERT INTO tabla SELECT * ...

Oracle, a partir de la versión 9i, dispone de la funcionalidad de BULK sql, es decir, tratamiento de conjuntos de filas "a montón". Realizar bulk inserts puede resultar muy práctico en el caso anterior, ya que las filas en el cursor no se ejecutan individualmente y no es preciso el filtrado previo de las filas.

Así pues, ante la necesidad de insertar en una tabla un volúmen de filas, omitir los errores e insertar las filas erróneas en una tabla, el siguiente código PL/SQL puede ser de gran utilidad, ya que su coste de ejecución resulta idéntico al del INSERT.

-- Código PL/SQL para insertar en una tabla, manejando errores en filas, con BULK INSERT
--------------------------------------------------------------------------------------
DECLARE
    type clientes_array is table of BK_CLIENTE_BDM_03%rowtype index by binary_integer;
    registros clientes_array;
    errores NUMBER;
    dml_errores EXCEPTION;
    contador_errores number := 0;
    PRAGMA exception_init(dml_errores, -24381);
    cursor c is select * from BK_CLIENTE_BDM_03;
BEGIN
    open c;
    loop
      fetch c BULK COLLECT INTO registros LIMIT 1000;
      begin
       FORALL i IN 1 .. registros.count SAVE EXCEPTIONS
         insert into TRASPASO_BD_CLIENTE values registros(i);
      EXCEPTION
        WHEN dml_errores THEN
          errores := SQL%BULK_EXCEPTIONS.COUNT;
          contador_errores := contador_errores + errores;
          FOR i IN 1..errores LOOP
            dbms_output.put_line ('Se encontro el error '||;SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
                    ':'||SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
          end loop;
     end;
     exit when c%notfound;
    END LOOP;
    close c;
    dbms_output.put_line( contador_errores );
end;

Su coste de ejecución es idéntico al de ejecutar:

insert into TABLA_DESTINO select * from TABLA_ORIGEN;

Comparativa de costes de ejecución.
******************************

insert into TRASPASO_BD_CLIENTE select * from BK_CLIENTE_BDM_03

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      4.91     102.61      15713      31424      88189      600000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      4.91     102.62      15713      31425      88189      600000

********************************************************************************

DECLARE
        type clientes_array is table of BK_CLIENTE_BDM_03%rowtype index by binary_integer;
        registros   clientes_array;
        errores NUMBER;
        dml_errores EXCEPTION;
        contador_errores number := 0;
        PRAGMA exception_init(dml_errores, -24381);
        cursor c is select * from BK_CLIENTE_BDM_03;
BEGIN
       open c;
       loop
           fetch c BULK COLLECT INTO registros LIMIT 1000;
           begin
              FORALL i IN 1 .. registros.count SAVE EXCEPTIONS
                 insert into TRASPASO_BD_CLIENTE values registros(i);
           EXCEPTION
               WHEN dml_errores THEN
                    errores := SQL%BULK_EXCEPTIONS.COUNT;
                    contador_errores := contador_errores + errores;
                    FOR i IN 1..errores LOOP
                        dbms_output.put_line
                        ('Se encontrs el error '
                          SQL%BULK_EXCEPTIONS(i).ERROR_INDEX
                       ': '
                          SQL%BULK_EXCEPTIONS(i).ERROR_CODE );
                    end loop;
          end;
          exit when c%notfound;
        END LOOP;
        close c;
        dbms_output.put_line( contador_errores
end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      2.40       2.46          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      2.40       2.47          0          0          0           1

SELECT * FROM BK_CLIENTE_BDM_03

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch      601     20.90      65.99      15712      16373          0      600000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      603     20.90      65.99      15712      16373          0      600000

INSERT INTO TRASPASO_BD_CLIENTE
VALUES
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,
  :B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,
  :B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,
  :B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 ,:B46 ,:B47 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute    600     17.40      26.39          0      15835      87647      600000
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      601     17.40      26.39          0      15835      87647      600000

********************************************************************************

RESUMEN:

Insert Select  -->   Execute      1      4.91    102.61      15713      31424      88189      600000

BULK INSERT   -->   Execute      1      2.40       2.46          0          0          0           1
+                   Fetch      601     20.90      65.99      15712      16373          0      600000
+                   Execute    600     17.40      26.39          0      15835      87647      600000

Tiempo insert select: 1'42'' (mismos bloques físicos y en caché)
Tiempo bulk insert: 1'33'' (mismos bloques físicos y en caché)
No hay diferencia...

Quien soy.

2005-07-16T19:36:00.000+02:00

Hola,

Mi nombre es Javier Morales. Desde el año 1999 estoy enteramente dedicado al desarrollo y la administración de bases de datos Oracle. En el junio de 2003 me certifiqué en Administración de Oracle8i y en la actualidad trabajo como consultor, administrador y formador senior.

Tengo la opinión personal de que el conocimiento es un valor que aumenta cuanto más se comparte. También pienso que existe mucho desconocimiento en plataformas Oracle, debido a que muchos expertos no comparten su conocimiento. Quizás alguien podría cuestionarles al respecto.

Mi intención al abrir este blog es compartir conclusiones, tests, comparativas, artículos, etc. con la idea no sólo de publicar parte de mi trabajo, sino también de poder contrastar opiniones con otros usuarios de Oracle, bien administradores, desarrolladores o analistas.

Espero que estos artículos puedan servir de ayuda para los lectores, y que éste sea un inicio a una nueva experiencia personal.