Archivos mensuales: agosto 2013

ORA-01552: no se puede usar segm. rollback SYSTEM para tablespace ”

En esta entrada de blog voy a intentar dar alguna solución a este error sobretodo para versiones antiguas de ORACLE. En versiones a partir de la 10G la solución estaría más basada en los tablespace de UNDO que en los segmentos de rollback propiamente dichos.

El error que suele aparecer es el siguiente:

Error de Java Gateway: JDBC Gateway SP execute(0) error 0:
 ORA-01552: no se puede usar segm. rollback SYSTEM para tablespace '<tablespace_afectado>' NO-SYSTEM

Donde … tablespace_afectado corresponderá al tablespace específico de tu BBDD.

Lo primero que tenemos que hacer es consultar el fichero de alertas del servidor Oracle. Normalmente ubicado en $ORACLE_HOME/oracle/admin/<SID>/bdump. El fichero de alertas suele llamarse alert_<SID>.log y almacena todos los movimientos y errores de la BBDD. Donde <SID> es el nombre de nuestra instancia.

Normalmente en esta ruta, se almacenan también los ficheros de traza que tienen la extensión.trc y almacenarán el error concreto de manera más específica que el fichero alert_<SID>.log.

En el caso que nos ocupa, el error aparecerá en el fichero de alertas, pero muy probablemente en el mismo fichero de alerta se nos indicará el nombre del fichero de traza que almacenará el error “origen” que ha provocado el error que finalmente vemos en el fichero de traza.

Por tanto, miraremos el fichero de alerta para encontrar el fichero de traza que miraremos posteriormente para averiguar “el nuevo error” o el “error origen” para ponerle remedio.

En el ejemplo siguiente vemos un posible  caso de lo que puede aparecer en el fichero de alerta:

FULL status of rollback segment 19 set.
ORA-1650: unable to extend rollback segment RBS9 by 128 intablespace RBS

Failure to extend rollback segment 10 because of 1650 condition
FULL status of rollback segment 10 set.
Thu Aug 22 14:04:52 2013
Errors in file /oracle/admin/<SID>/bdump/snp3_3448_p103.trc:
ORA-12012: error on auto execute of job 121
ORA-01552: cannot use system rollback segment for non-system tablespace '<tablespace_afectado>'

Lo siguiente sería consultar el fichero de traza que hemos visto en el de alerta….

Si nos fijamos en el ejemplo anterior, en negrita, tenemos el nombre del fichero de traza que nos detalla de manera más precisa el error que ha provocado la falta de espacio en los rollback segments: (snp3_3448_p103.trc).

Si miramos su contenido veremos algo como:

Unix process pid: 3448, image: oracle@h0514 (SNP3)
*** SESSION ID:(511.55794) 2013-08-12 14:25:26.821
*** 2013-08-12 14:25:26.821
ORA-12012: error on auto execute of job 101
ORA-01555: snapshot too old: rollback segment number 6 with name "RBS5" too small
ORA-06512: at "OPS$USUARIO.PROCEDIMIENTO", line 109

Este fichero nos informa del pid del proceso Unix por si tenemos que cancelarlo, el segmento de rollback afectado y el procedimiento, acción, función, etc… que se estaba ejecutando y que ha provocado el error. Esto lo usaremos para informar al usuario que lanza dicho proceso o a los desarrolladores de que es lo que está provocando la carga excesiva, por si es necesario, crear algún segmento de rollback especial para este proceso y especificarlo en la aplicación por medio del comando:

SQL> set transaction use rollback segment <nombre_tablespace_rollback>;

Pero la solución para evitar el error ORA_01552 sería ampliar con un datafile <tablespace_afectado> y revisar la contención, que los datafiles del tablespace del segmento de rollback tengan espacio y el parámetro OPTIMAL de los segmentos de rollback.

Para modificar el OPTIMAL de un segmento de rollback se hace:

ALTER ROLLBACK SEGMENT "RBS5" STORAGE ( OPTIMAL 4096K);

Va de extents …

En esta entrada de blog os pongo un par de consultas SQL que van perfectas para sacar el tamaño de los segmentos de ORACLE. Es ideal sobretodo para versiones anteriores a la 10G.

Tamaño de los segmentos por búsqueda concreta de segmento …

select initial_extent/1024/1024, next_extent/1024/1024, extents
from dba_segments
where segment_name like '%cadena_busqueda%'

Fragmentación de la Base de datos a nivel de extensiones … (Muestra los que tengan más de 5 extensiones).

REM    FRAGMENTACIÓN DE LA BASE DE DATOS.
REM     Obtiene el número de extensiones de los objetos de la base de datos
REM     y el número máximo a las que pueden llegar.

spool extents.lst

set    pagesize 66
clear  break
clear  columns
ttitle off

break on TABLESPACE skip 3 on TIPO skip 1

column EXTENTS format 999
column MAX_EXTENTS format 999
column NOMBRE format A21
column TIPO format A9
column TABLESPACE format A13
column PROPIETARIO format A13
column today new_value _date noprint

SELECT sysdate today FROM dual;

ttitle center 'Fragmentación de la Base de Datos' skip 1 -
center &_date  skip 1 -
right 'Pág.: ' format 999 sql.pno skip 2

REM Extrae los objetos de DB que tienen más de 5 extents.

SELECT tablespace_name TABLESPACE,
       segment_type TIPO,
       segment_name NOMBRE,
       owner PROPIETARIO,
       extents EXTENTS,
       max_extents MAX_EXTENTS
FROM sys.dba_segments
WHERE extents > 5
ORDER BY tablespace_name,segment_type,segment_name,owner;

spool off

Configurar el paquete (package) UTL_FILE en el init.ora en 10G

Oracle ofrece el paquete UTL_FILE para usar/manipular archivos de PL/SQL.  UTL_FILE es un conjunto de procedimientos y funciones simples para procesar archivos del sistema de archivos del sistema operativo, de Oracle, utilizando PL / SQL.

En la siguiente entrada de blog explicaré cómo modificar el init<XXX>.ora para poder utilizar el paquete UTL_FILE y que consideraciones debemos tener en cuenta.

Puedes ampliar información en la documentación oficial de Oracle:
http://docs.oracle.com/cd/B14117_01/appdev.101/b10802/u_file.htm

Comenzamos ….

En primer lugar me conecto al UNIX con el usario oracle y posteriormente a sqlplus con el usuario SYS

(oracle) $ whoami
 oracle
 $ sqlplus /nolog
SQL*Plus: Release 10.2.0.4.0 - Production on Lun Ago 19 09:47:28 2013
 Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 SQL> show user
 USER is ""
SQL> connect /as sysdba
 Connected.

Compruebo que soy SYS …

 SQL> show user USER is "SYS"

Compruebo el valor que contiene utl_file_dir y veo que está vacio…

SQL> select name,value from v$parameter where name='utl_file_dir';

NAME
——————————————————————————–
VALUE
——————————————————————————–
utl_file_dir

Compruebo que tipo de parámetro es utl_file_dir para ver si lo puedo modificar en caliente o deberé bajar la base de datos …

SQL>
col NAME format a50
col ISSYS_MODIFIABLE format a20;
SELECT NAME, ISSYS_MODIFIABLE FROM V$PARAMETER
WHERE name='utl_file_dir' ORDER BY 1;

NAME                                               ISSYS_MODIFIABLE
————————————————– ——————–

utl_file_dir                                       FALSE

FALSE: Indica que obligatoriamente la base de datos debe bajarse para poder efectuar el cambio. Por tanto, se trata de un parámetro estático y se puede modificar, pero su modificación implica cerrar la base de datos y volverla a abrir para que los lea del fichero de inicialización y pueda realizar el cambio. Por eso en scope ponemos spfile y no both.

Si usamos both nos dará el siguiente error:

SQL> alter system set utl_file_dir=’<directorio_o_ruta>’ scope=both
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

Puedes ampliar información de los parámetros de Oracle en esta otra entrada de blog (http://colacios.es/blog/WordPress3/tipos-de-parametros-en-oracle/).

Se hace así:

SQL> alter system set utl_file_dir='<directorio_o_ruta>' scope=spfile


Ejemplo:

SQL> alter system set utl_file_dir='/users/oracle/usuarioftp' scope=spfile
System altered.

Ahora debo bajar la base de datos y volverla a levantar para que los cambios tengan efecto…
Primero pararé el listener para evitar conexiones …

$ lsnrctl stop 

 

Ahora me conecto a sqlplus para bajar y levantar la base de datos …

$ sqlplus /nolog
SQL> show user USER is ""
SQL> connect /as sysdba;
Connected.

Bajarla …

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Levantarla …

SQL> startup
ORACLE instance started. 

Total System Global Area 1442840576 bytes
Fixed Size                  2069240 bytes
Variable Size             738200840 bytes
Database Buffers          687865856 bytes
Redo Buffers               14704640 bytes
Database mounted.
Database opened.

Compruebo que se ha levantado …

 SQL> show sga 

Total System Global Area 1442840576 bytes
Fixed Size                  2069240 bytes
Variable Size             738200840 bytes
Database Buffers          687865856 bytes
Redo Buffers               14704640 bytes

Compruebo que el parámetro ha cogido el valor correspondiente …

SQL> select name,value from v$parameter where name='utl_file_dir';  

NAME
——————————————————————————–
VALUE
——————————————————————————–

utl_file_dir

/users/oracle/usuarioftp

Ahora levanto el listener:

$ lsnrctl start

Nota:
Si se tienen que añadir más directorios se debe especificar como sigue en la sentencia alter system:

utl_file_dir = ‘/ora102/log’, ‘/escherdata/carbono/archivos’,'/cherdata/operational_reports/datos’, ‘/rdata/plata/static/log’

o bien puedes agregar el valor “*
utl_file_dir = *

Evidentemente, antes de modificar todo lo anterior, previamente se han tenido que hacer los siguientes pasos:

Para usar paquete UTL_FILE, que es propiedad de SYS, tienes que conceder el privilegio EXECUTE para el usuario. Dale privilegio EXECUTE para el usuario requerido o público (todos los usuarios) de la siguiente manera:

grant execute on UTL_FILE to public;

El siguiente paso y no por ello menos importante es crear un directorio lógico (alias del directorio) de Oracle que apunte a un directorio físico en el sistema de archivos. Sin embargo, el alias del directorio sólo puede sercreado por el DBA (SYS o SYSTEM). Así que después de iniciar sesión como SYS, crear el alias de directorio para la carpeta de ORACLE y conceder permisos de lectura y escritura a PUBLIC como sigue:

create directory filesdir as '/users/oracle/usuarioftp';
grant read on directory filesdir to public;
grant write on directory filesdir to public;

Consideraciones a tener en cuenta para las instalaciones de ORACLE

En numerosas ocasiones nos hemos podido encontrar con la situación en que tenemos que instalar por ejemplo clientes Oracle de versiones diferentes para poder acceder a distintas bases de datos de versiones, también diferentes. Este hecho, puede provocar que durante las instalaciones nos encontremos con numerosos inconvenientes y sobretodo dudas de cómo encarar la instalación, si hacerlo en el mismo ORACLE_HOME o distinto, que pasa con el registro en Windows, el PATH, etc…

Seguidamente dejo unas cuantas recomendaciones, que evidentemente se tienen que interpretar como lo que son, recomendaciones que para mis casos en concreto, he seguido porque las necesidades de mis clientes lo exígian. Lo que “recomiendo” seguidamente puede diferir en otros clientes que tengan necesidades distintas.

Recomendación 1

Si hay que instalar diferentes clientes de ORACLE en un mismo PC, hacedlo por orden de versión. Es decir, si tenemos que instalar un cliente 7, y un cliente 10g, instalar primero el cliente 7 y posteriormente el 10g.

Recomendación 2

En el ejemplo anterior, además de instalar los clientes por orden de versión, lo haremos en un ORACLE_HOME diferente para evitar sobreescribir ficheros e inhabilitar el cliente de versión más antigua.

Recomendación 3

En clientes bajo Windows, es recomendable definir una memória virtual adecuada, sobretodo para instaladores de versiones más antiguas. Es importante definirla bien para evitar errores que nos pueden despistar y pensar que están relacionados con otros errores de Oracle.

Recomendación 4

Es importante tener claro, que cada instalación de cliente de Oracle de versiones diferentes que hagamos en una misma máquina, tendrá un SQL*NET diferente y por tanto, los ficheros tnsnames.ora y listener.ora deberán ser configurados de manera adecuada para poder acceder a las bases de datos que necesitamos acceder. Puede suceder que con algunos clientes de versiones superiores podemos, evidentemente acceder a versiones anteriores, pero no poder hacerlo con clientes anteriores a versiones superiores. Es por este motivo, que podemos tener en el cliente de versión más nueva, entradas en el fichero tnsnames.ora para conexiones a versiones anteriores, de ahí el hecho, de que tengamos que configurarlo una vez instalado el cliente Oracle de versión superior, aunque tengamos configurado el fichero tnsnames.ora del cliente de versión anterior correctamente.

Recomendación 5

En el registro de Windows, dentro de HKEY_LOCAL_MACHINE/Software/ se guardan las entradas de los clientes instalados de ORACLE. Es necesario saber, que si hemos realizado por ejemplo una instalación de la versión 7 de Oracle y queremos volverla a realizar desde cero, tenemos que eliminar la rama de ORACLE y la ruta que aparece en la variable de entorno PATH. Luego eliminaremos el directorio donde tengamos el cliente instalado y podremos así, volver a arrancar el instalador de Oracle. En versiones superiores, 10G y 11GR2 los instaladores están más logrados y no tenemos que hacer tantas “artimañas”.

Recomendación 6

Existen unos ficheros digamos de rastro que almacena el instalador de Oracle donde se detalla lo que hay instalado en el sistema.
En vesiones anteriores de ORACLE existe un fichero con extensión .rgs ubicado normalmente en /../orainst donde se almacena lo que hay instalado. Si vamos a instalar por ejemplo OEM en una máquina donde ya hay productos instalados de Oracle, podemos renombrar este fichero y ponerle por ejemplo extensión “.old” y posteriormente lanzar el instalador de Oracle “Oracle Installer” así evitaremos problemas en la instalación.

Tened en cuenta que a partir de la versión 6 de Oracle Developer, el instalador ya es capaz de detectar si hay servicios de Windows arrancados. Pero siempre es recomendable bajarlos antes de cualquier instalación.

Los ficheros .rgs almacenados en /../orainst son el registro de los productos Oracle con sus respectivas versiones instalados en la máquina.

Recomendación 7

En versión 7 de Oracle existe un fichero con extensión .log en ORANTORAINST llamado orainst.log digamos de rastro que almacena el instalador de Oracle cuya extensión es .log y no .rgs como en versiones posteriores.

Recomendación 8

En versión 7 de Oracle existe una “herramienta” que permite crear/borrar los servicios de Windows. Se trata del ejecutable oradim73. Podemos ver sus diferentes opciones ejecutando desde la linea de comandos de Windows:

C:> oradim73 /?
oradim73 -¿
oradim73 –h
oradim73 –help

Ejemplo:

C:> oradim73 –new –sid ORCL –intpwd oracle –startmode manual –pfile C:ORANTDATABASEinitSID.ora

- manual–> Se suele poner manual durante el proceso de instalación, y una vez finalice esta, lo cambiaremos a auto.

C:oradim73 –edit –sid ORCL –startmode auto

Esta herramienta nos puede ser útil si alguna instalación se nos complica y tenemos que borrar los servicios de Windows.

Recomendación 9

En versión 7 de Oracle para windows ORACLE_SID se define manualmente… Sí es la primera base de datos ORACLE que se crea debemos hacer:

En el registro de Windows:

Ir a HKEY_LOCAL_MACHINE
. Software
. ORACLE
Pulsar el menú “Edición”
Nuevo -> Valor alfanumérico.

Introducir ORACLE_SID en “nuevo valor #1”, seguidamente con el botón derecho pulsar modificar sobre el campo, y poner el valor que corresponda al SID ( en este caso ORCL). Este parámetro ORACLE_SID es el que identifica a la Base de datos a la cual  nos  conectamos por defecto. Imaginemos, que queremos entrar al sqlplus, nos pide usuario, clave y la cadena de conexión, el usuario iria a conectarse a la Base de datos especificada en “cadena de conexión”, pero en caso de que no se especificase, lo haría donde indicase ORACLE_SID.