Archivo de la categoría: sqlplus

Consultas para tablespaces temporales

Posted on por

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 por

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');

Exportar roles entre bases de datos

Posted on por

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

Modificar el editor por defecto de SQLplus en Unix

Posted on por

Vamos con una nota rápida y sencilla, pero bastante útil.

SQLPlus tiene por defecto el editor ed , sinceramente, el ed no es el editor mas usado del mundo, de hecho, no conozco a nadie que lo tenga como su primera opción, la pregunta ahora es, ¿Como hacemos para modificar este editor por defecto?

muy sencillo:


define_editor='vi'

Para no tener que estar añadiendo esta línea cada vez que entramos, podemos añadirla al fichero profile de sqlplus genérico que se encuentra en


$ORACLE_HOME/sqlplus/admin/glogin.sql

Recuperacion con RMAN desde Dataprotector desde linea de comandos

Posted on por

Muchas veces tenemos el backup integrado por scripts propietarios del software de backup.
Esta integración nos garantiza el pode recuperar con «botón derecho», pero , puede darse el caso de querer recuperar manualmente, bien por que queremos tener el control total sobre el proceso o bien por que es en otra maquina o por que queramos hacer una recuperación mas especifica del RMAN que la que nos ofrezcan los botones del software de backup.

En este caso vamos a hacer una recuperación total de una base de datos que se ha copiado con dataprotector. Entre las cosas que necesitaremos son:

  • Init.ora de la base de datos, deberíamos de hacer una copia del mismo junto con el backup,con lo que podemos sacarlo de ahi
  • DBID de la base de datos, este DBID aparece en el log de rman, con lo que podremos sacarlo del ultimo log del backup
  • Cadena de configuración de la cinta. Esta en las propiedades avanzadas de la política de backup que usamos para copiar nuestra base de datos

Además, necesitaremos ser capaces de llegar al log de la ultima copia de rman, esto se hace desde dataprotector, mirando en las siguientes pestañas

Internal Database
     -> "log del backup"  (tiene el formato fecha/backup)
           -> Propierties (boton derecho)
               --> Messages (el log completo del rman)

Para clarificar un poco los logs, tendremos en este caso:

  • instancia=pruebas
  • Servidor=serveroracle.pamplona.name
  • DBID=3751694031 (obtenido del log del backup desde dataprotector)

Si no tuviésemos el init.ora podríamos recuperarlo también del backup ya que la 11g hace copia del init.ora con el controlfile autobackup, pero, es una buena practica el tener una copia del init.ora en modo texto, ya que, nos evita uno de los pasos mas engorrosos.
Con estas 3 cosas, podemos comenzar la recuperación de la base de datos.

Lo primero que recuperaremos será el controlfile, para ello haremos un script al que llamaremos restore_controlfile.cmd tal que

startup nomount;
set DBID=3751694031
run {
allocate channel 'dev_0' type 'sbt_tape'
 parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=pruebas,OB2BARLIST=Online Diaria)';
restore controlfile from autobackup;
}

Al que llamaremos con 

rman target / cmdfile restore_controlfile.cmd

En este punto, podemos llevarnos la sorpresa de que obtenemos un RMAN-06172

Al igual que vimos en el post RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece tendremos que decirle exactamente cual es el nombre del controlfile que queremos recuperar, para ello, nos iremos al dataprotector y exploramos el log hasta la última línea en la que encontremos la palabra controlfile

[Normal] From: OB2BAR_DMA@serveroracle.pamplona.name "pruebas"  Time: 06/03/2013 21:21:19
	Starting OB2BAR Backup:serveroracle.pamplona.name: pruebas DP Managed Control File Backup "Oracle8"

La línea que estamos buscando es pruebas DP Managed Control File Backup «Oracle8», que es el fichero dentro de dataprotector donde se encuentra nuestro controlfile.
Así pues, modificaremos el script de backup y ahora será:

startup nomount;
set DBID=3751694031
run {
allocate channel 'dev_0' type 'sbt_tape'
 parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=pruebas,OB2BARLIST=Online Diaria)';
restore controlfile from 'pruebas DP Managed Control File Backup "Oracle8"';
}

Con esto conseguiremos tener nuestro controlfile restaurado. Ahora, ya tenemos una restauracion standard de RMAN típica de manual.