Acerca de admin

Tras más de 20 años trabajando con tecnologías Oracle, me decidí a recopilar en un Blog algunas de las cosillas útiles para el día a día.

Consultas para tablespaces temporales

Posted on 16 julio, 2013 por admin

Hoy vamos a volver con las entradas para dummies. Vamos algunas consultas prácticas sobre tablespaces temporales.

Lo primero vamos a ir a las consultas mas básicas, ver los tablespaces temporales, los tempfiles, crear un o o modificar su tamaño

-- Ficheros temporales
select * from dba_temp_files;


-- Creación de Tablespaces temporales gestionados localmente.
create temporary tablespace tempaux 
tempfile '/oradata/orcl/temp_aux01.dbf' SIZE 300M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

--Añadimos un fichero a un tablespace
alter tablespace TEMPAUX 
     add  tempfile  '/oradata/orcl/temp:aux02.dbf' size 200M ;

--Cambio de tamaño
alter database tempfile
 '/oradata/orcl/temp_aux02.dbf' resize 5000M;

Y luego veamos algunas consultas algo mas complejas que nos pueden servir para comprobar el uso de los temporales

--Uso de tablespaces temporal
select t2."TempTotal" "TempTotal (Mb)",
       t1."TempUsed" "TempUsed (Mb)",
       t2."TempTotal" - t1."TempUsed" "TempFree (Mb)"
  from (select nvl(round(sum(tu.blocks * tf.block_size) / 1024 / 1024, 2), 0) "TempUsed"
          from v$tempseg_usage tu, dba_tablespaces tf
         where tu.TABLESPACE = tf.tablespace_name) t1,
       (select round(sum(bytes) / 1024 / 1024, 2) "TempTotal"
          from dba_temp_files) t2;  

--Uso de temporal por sesion 
SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr 
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid_serial;

Truncate table sin permisos TRUNCATE ANY TABLE

Posted on 11 julio, 2013 por admin

Hoy vamos a ver rápidamente un tema muy sencillo que puede traernos algun que otro dolor de cabeza. Intentar truncar una tabla de otro esquema.

Supongamos que tenemos una aplicación que utiliza dos esquemas:

ADMINISTRADOR-> Propietario de los datos y de todos los objetos del esquema
APLICACION-> Usuario de explotación de los datos que no tiene objetos propios y que accede a los datos de ADMINISTRADOR mediante grants

Este modelo de aplicación es muy común ya que nos garantiza que , el esquema APLICACION nunca va a modificar la estructura del modelo de datos, su funcionamiento se base en dar los grants
DELETE, INSERT, SELECT, UPDATE para todas las tablas del esquema ADMINISTRADOR, lo que permite a el usuario APLICACION borrar todos los datos de una tabla de ADMINISTRADOR, pero no es posible que lleve a cabo una acción de truncado, ya que , para Oracle el truncado es una acción distinta a el borrado (no lo hace sobre los datos sino sobre la propia tabla).

El problema con el que nos encontramos es que, oracle no contempla el otorgar un permiso «TRUNCATE TABLE» a un usuario sobre los objetos de otro usuario, el único permiso que contempla es el de «TRUNCATE ANY TABLE»; lo que es una brecha de seguridad ya que permitiría al usuario APLICACION el truncar cualquier tabla de cualquier otro esquema de la base datos.

¿Como se soluciona el problema?

La solución es muy sencilla, y pasa por hacer una funcion en el esquema ADMINISTRADOR que trunque la tabla que le pasamos por parámetro.
EL procedure sería tal que


create or replace procedure truncartabla(tabla_a_truncar varchar2) 
is
 begin
    execute immediate 'truncate table ' || tabla_a_truncar ;
 end;

Despues de eso , deberemos de dar permisos de ejecución sobre este procedimiento al usuario APLICACION (seguimos como usuario ADMINISTRADOR)


 grant execute on truncartabla to APLICACION

Ahora ya podremos truncar tablas desde el usuario expfin, el único cambio que habrá que hacer es, en el código SQL donde pone


TRUNCATE TABLE ADMINISTRADOR.TABLA1

por


execute ADMINISTRADOR.truncartabla('TABLA1');

Instalando Oracle 12c en linux

Posted on 7 julio, 2013 por admin

A primeros de este mes Oracle anuncio el lanzamiento de la versión 12c de Oracle.En el post de hoy vamos a ver como se hace la instalación para Oracle Linux 6.4
La mayor ventaja de el uso de Oracle Linux va a ser que como veremos la instalación será practicamente limpia.

Los prerrequisitos van a ser los mismos que en versiones anteriores de Oracle, instalaremos un Oracle Linux, y crearemos un usuario oracle y un grupo oinstall

La primera imagen que vemos es una pantalla con los colores corporativos y un aspecto mas renovado.

Tras esta pantalla, nos viene la tipica imagen en la que nos solicita nuestro usuario de soporte. Nosotros le diremos que no queremos actualizaciones de Oracle.

También omitiremos la opcion de registrarla automáticamente con metalink.

Tras estas primeras pantallas, llegamos a la opción de crear base de datos, instalar o actualizar, nosotros vamos a probar la instalación en un servidor limpio.En otra entrada probaremos como funciona la actualización, aunque, siempre soy mas partidario de instalar en otro entorno y actualizar despues la base de datos

En la siguiente pantalla nos encontramos una opcion nueva y , para mi bastante sorprendente, «escritorio o servidor» .
Sinceramente no se que puede ser una «instalacion de escritorio» de oracle, con lo que, directamente iremos a servidor

La siguiente pantalla es bastante mas interesante, además de las opciones clásicas de base de datos «alone» o «Rac», nos aparece la nueva entrada de «Rac OneNode»
En esta entrada haremos una instalación «alone», dejando la de RAC ONE NODE para otra entrada.
Como aperitivo, os diré que el producto Rac One Node es la respuesta de Oracle a la necesidad de una alta disponibilidad «activo/pasivo».

Seleccionamos instalación avalada

En ingles

y Enterprise ( como decía mi abuela, «ande o no ande la burra grande»)

En la siguiente pantalla al fin empezamos a elegir opciones de la base de datos.
Al tratarse de una primera instalación de base de datos de pruebas en un entorno virtual vamos a ir a lo fácil y elegiremos una instalación en filesystem.

Indicamos donde queremos el orainventory y el grupo de instalacion (oinstall)
instalacion oracle 12c paso 10
Y el tipo de base de datos (en nuestro caso es indiferente)

En el paso 13 además del SID nos encontramos con una opción interesante, la creación de un contenedor de bases de datos, nosotros vamos a dejarlo como nos pide por defecto

Asignamos la memoria y el juego de caracteres

Y la ubicación de los datafiles (en nuestro caso lo haremos sencillo indicándole que queremos un directorio en el filesystem)

Nosotros no vamos a registrarla en ningun cloud control, ni activaremos la FRA (lo haremos después )

La siguiente pantalla es la de las contraseñas, dado que es un entorno de pruebas, vamos ha hacer la poco recomendable opción de poner en todas la misma
instalacion oracle 12c paso 17[/caption]

Al fin llegamos al punto 20, el punto de las comprobaciones, aquí veremos si tenemos todos los parámetros del kernel y paquetes.

Tras esto llegamos a la ventana de resumen, esta version 12c tiene dos mejoras considerables

Opcion de editar directamente los puntos que te puedan parecer incorrectos
Opcion de generar un fichero de respuesta con las opciones que has creado de la base de datos

Con toda esta información oracle se pondrá a instalar hasta la habitual pantalla en la que nos pide que ejecutemos como root 2 scripts en el filesystem

tras esto ya solamente queda que cree nuestra base de datos y ya tendremos nuestra version de Oracle 12c funcionando

Exportar roles entre bases de datos

Posted on 20 junio, 2013 por admin

Hoy vamos a ver como transportar roles de bases de datos.

Los roles de bases de datos son una solución cómoda y rápida para la asignación de grupos de permisos entre usuarios, el principal problema de los mismos, es que los permisos que asignas a los roles no están en las especificaciones de los usuarios que los poseen, sino que están dentro del esquema SYS dela base de datos, lo que provoca que cuando intentas importar algún esquema de usuario que posee un rol falle si no está el rol previamente definido.

Otra de las incomodidades de los roles es que , no hay una consulta sencilla que puedas hacer a la base de datos que te muestre la sintaxsis SQL del rol, con lo que su uso se convierte en algo engorroso en operaciones de movimiento de datos entre entornos.

Así pues, si queremos trabajar con roles tendremos que tener algún tipo de mecanismo para obtener los comandos de creacion de los roles.
Para esto, oracle tiene el paquete dbms_metadata que con la llamada get_ddl es capaz de obtener el código SQL para un determinado objeto.

Ahora bien,¿como llamamos a este paquete?

Para cada uno de nuestros roles tendremos que obtener las DDL de :

creacion del rol
Grants del rol
Grants de system
Grants de objetos

Así pues, para obtener los datos del rol «MIROL» haríamos:

set linesize 200
set head off
set pages 0
set long 9999999
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
SELECT 'create role MIROL' from dual;
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
SELECT dbms_metadata.get_granted_ddl('ROLE_GRANT',  'MIROL') FROM dual;
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
SELECT dbms_metadata.get_granted_ddl('SYSTEM_GRANT','MIROL') FROM dual;
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
SELECT dbms_metadata.get_granted_ddl('OBJECT_GRANT','MIROL') FROM dual;

Hay que tener en cuenta que, la consulta de los SYSTEM_GRANT es muy pesada, especialmente en sistemas con un alto número de objetos.
Por otra parte,tampoco estamos hablando de una operacion que no hemos de hacer muy amenudo y que, probablemente podamos guardar de una vez a otra

Detectando bloques corruptos en Oracle

Posted on 5 junio, 2013 por admin

Hoy vamos a ver como detectar que se nos ha roto en casos de problemas de bloques corruptos de Oracle.

Lo primero que tenemos que tener en mente es tener la base de datos en modo archivelog y hacer copias periódicas con RMAN, pero , ¿que ocurre si no es así y tenemos una corrupción de datos?
Seguramente habremos perdido esos bloques. Pero, lo primero va a ser el saber que objetos lógicos tenemos corruptos .

Tenemos dos maneras de detectar la corrupción de datos

RMAN

Si tenemos la base de datos en modo archivelog ejecutaremos

 rman target / nocatalog
run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

El comando validate de RMAN hará un chequeo de los bloques inválidos y nos los dejará en la vista v$database_block_corruption;

DBVERIFY

Si no tenemos la base de datos en modo archivelog, no podemos usar RMAN (a no ser que esté en modo MOUNT), asi que,usaermos dbv.

Oracle provee en todas las plataformas el comando de sistema operativo DBVERIFY (dbv) que nos permite el llevar a cabo una comprobación de la integridad física de los ficheros de la base de datos.Su gran ventaja es que no necesita corte de servicio y podemos ejecutarlo tranquilamente mientras los usuarios trabajan en la base de datos.

La sintaxis la podemos ver en http://docs.oracle.com/cd/A97630_01/server.920/a96652/ch13.htm , y su principal inconveniente es que debe lanzarse para cada uno de los datafiles de la base de datos.

Una manera rápida de automatizar el comando para toda la base de datos es mediante el siguiente script ejecutado como sys o system

spool /tmp/datafiles_corruptos.sh
set linesize 200
set heading off
set pagesize 200
select 'dbv  file=' || name || ' blocksize=' || block_size || 
       ' feedback=' || round(blocks*.10,0)||
       '  logfile=' || file# || '.log'
          from v$datafile;

Este script nos dejara un fichero /tmp/datafiles_corruptos.sh con tantas líneas como datafiles tenga la base de datos de la forma

dbv  file=/opt/oracle/oradata/pruebas/tablespace_indices001.dbf blocksize=8192 feedback=3840  logfile=2

El comando dbv nos va a generar un fichero de log de nombre DATAFILE#.log, con lo que, mi consejo es crear un subdirectorio y lanzar el script /tmp/datafiles_corruptos.sh desde este subdirectorio, asi no llenaremos el path donde nos encontremos de ficheros de log.

EL detectar lo bloques inválidos es muy sencillo, solamente tenemos que ordenar por tamaño de fichero en el subdirectorio que tenemos los logs. Los ficheros que no tienen bloques corruptos tendrás un tamaño aproximado de 1 Km y su contenido será similar a :

DBVERIFY: Release 11.2.0.3.0 - Production on Jue Jun 6 13:14:28 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Iniciando verificación : FILE =/opt/oracle/oradata/pruebas/tablespace_indices018.dbf
DBVERIFY - Verificación terminada
Total de Páginas Examinadas     : 153600
Total de Páginas Procesadas (Datos): 91682
Total de Páginas con Fallos (Datos): 0
Total de Páginas Procesadas (Índice): 28488
Total de Páginas con Fallos (Índice): 0
Total de Páginas Procesadas (Otras): 17288
Total de Páginas Procesadas (Seg): 1
Total de Páginas con Fallos (Seg): 0
Total de Páginas Vacías         : 16142
Total de Páginas Marcadas como Corruptas: 0
Total de Páginas de Entrada     : 0
Total de Páginas Cifradas        : 0
SCN de Bloque Superior            : 1961026896 (9.1961026896)

Los datafiles con bloques corruptos tendrán un tamaño mayor, y su contenido será

DBVERIFY: Release 11.2.0.3.0 - Production on Jue Jun 6 13:16:14 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Iniciando verificación : FILE = /opt/oracle/oradata/pruebas/SYSAUX01.DBF
La página 45847 es de entrada - probablemente el medio físico esté corrupto
Corrupt block relative dba: 0x00c0b317 (file 3, block 45847)
Fractured block found during dbv: 
Data in bad block:
 type: 6 format: 2 rdba: 0x00c0b317
 last change scn: 0x0009.5a58966b seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000000
 check value in block header: 0x5708
 computed block checksum: 0x48fd

La página 45849 está marcada como corrupta
Corrupt block relative dba: 0x00c0b319 (file 3, block 45849)
Completely zero block found during dbv: 

La página 45850 está marcada como corrupta
Corrupt block relative dba: 0x00c0b31a (file 3, block 45850)
Completely zero block found during dbv: 
.
.
.
.
.
.

La página 117911 es de entrada - probablemente el medio físico esté corrupto
Corrupt block relative dba: 0x00c1cc97 (file 3, block 117911)
Fractured block found during dbv: 
Data in bad block:
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xc90c0601
 check value in block header: 0x0
 block checksum disabled

DBVERIFY - Verificación terminada
Total de Páginas Examinadas     : 141312
Total de Páginas Procesadas (Datos): 41849
Total de Páginas con Fallos (Datos): 0
Total de Páginas Procesadas (Índice): 46801
Total de Páginas con Fallos (Índice): 0
Total de Páginas Procesadas (LOB)  : 3100
Total de Páginas con Fallos (LOB)  : 0
Total de Páginas Procesadas (Otras): 27154
Total de Páginas Procesadas (Seg): 0
Total de Páginas con Fallos (Seg): 0
Total de Páginas Vacías         : 22352
Total de Páginas Marcadas como Corruptas: 56
Total de Páginas de Entrada     : 6
Total de Páginas Cifradas        : 0
SCN de Bloque Superior            : 1961026953 (9.1961026953)

Con esto tendremos también la información en la vista v$database_block_corruption;, pero ¿que objetos son los que tenemos con problemas?
Si ejecutáis la consulta :

set linesize 200
set pagesize 200
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
order by file#, corr_start_block#;

Obtendréis un resultado similar a este


OWNER        SEGMENT_TYPE       SEGMENT_NAME                   PARTITION_NAME   CORR_START_BLOCK# CORR_END_BLOCK# BLOCKS_CORRUPTED DESCRIPTION
----------- ------------------ ------------------------------------------------- ---------- ----------------- ---
SYS           TABLE              WRH$_PGASTAT                       3             45847         45847                1
SYSMAN        TABLE              MGMT_TARGET_ASSOC_ERROR            3             45851          45851             1 Segment Header

Como siempre, tenemos mas información y mas precisa en soporte oracle en :

OERR: ORA-1578 «ORACLE data block corrupted (file # %s, block # %s)» Master Note [ID 1578.1]
Note 28814.1 Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g
Note 556733.1 DBMS_REPAIR script y Note 68013.1 DBMS_REPAIR example
Physical and Logical Block Corruptions. All you wanted to know about it. [ID 840978.1]

Dba stuff

Un cajon desastre para el día a día

Archivo del Autor: admin