Archivos mensuales: julio 2013

ORA-28002: The password expire within 7 days … como solucionarlo

Es posible que en alguna ocasión que como DBA te llame un usuario y te diga que al intentar acceder con su usuario a su cuenta le salga el mensaje de error:

ORA-28002: the password will expire within 7 days

Tú le cambias la contraseña, vuelves a probar, y desgraciadamente sigue saliendo el mismo error. Incluso, el usuario puede tener ya la cuenta bloqueada después de varios intentos fallidos de conexión. Visitar este enlace para ver como desbloquear la cuenta: Bloquear/Desbloquear una cuenta ORACLE

El problema es de fácil solución. Todo es debido al perfil (profile) que tenga asociado dicho usuario. Normalmente, el perfil asociado a un usuario en el proceso de creación es DEFAULT. Y dentro de ese perfil, se definen valores como la caducidad y otros parámetros que determinan la caducidad de la contraseña. Para evitar este error, revisaremos dichos parámetros del perfil.

Los podemos consultar via SQLPLUS o bien con OEM. En el ejemplo veremos las dos maneras.

Debemos tener en consideración que …

- Es recomendable que el password expire aunque sea en un periodo prolongado de tiempo.
- El perfil se define en la sentencia de creación del usuario “CREATE USER …”
- Podemos modificar los perfiles una vez creado dicho usuario e incluso cambiar el perfil del usuario.

Empecemos…..

Desde SQLPLUS

Miramos que perfil tiene el usuario asignado:

SQL>  select username, profile from dba_users
where username = 'USUARIO';

 

USERNAME                       PROFILE
—————————— ——————————
USUARIO                          DEFAULT

vemos que tiene asignado el perfil DEFAULT, será el que editaremos para cambiar los valores de expiración de la contraseña. Estos valores de perfil son PASSWORD_LIFE_TIME y basados en la tabla de atributos de usuario ASTATUS y EXPTIME.

sacamos los valores …

SQL> select username, account_status, expiry_date from dba_users;

USERNAME                       ACCOUNT_STATUS                   EXPIRY_DATE
—————————— ——————————– ————
USUARIO                           EXPIRED & LOCKED                 15-AUG-12

en este caso el usuario le expiró el 15 de Agosto de 2012 y además, tiene la cuenta bloqueada. Por tanto, primero desbloquearemos la cuenta tal y como explicamos en la entrada de blog del enlace anterior. Y procedermos a cambiar el límite de caducidad en el profile.

SQL> ALTER PROFILE "DEFAULT" LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Teniendo en cuenta que no le estamos poniendo fecha de “expiración” y siempre es aconsejable hacerlo.  Esto por si sólo no solventa el error, hasta que las tareas de cambio de password, desbloqueo pendientes estén realizadas. Una vez hechas, el usuario al conectarse cogerá los nuevos valores definidos en el perfil.

Recordad que para cambiar la clave del usuario haremos:

SQL>  ALTER USER usuario IDENTIFIED BY VALUES 'clave';

CONCLUSIÓN:
La caducidad de la contraseña está controlada por el límite PASSWORD_LIFE_TIME definido en el perfil (DEFAULT por defecto) y se activa al iniciar la sesión Oracle, pero depende de la combinación de los parámetros ASTATUS y EXPTIME y se reestablece por un cambio de contraseña (alter user … identified by …). El cambio de perfil NO cambiará ASTATUS. En el cambio de contraseña se restablece ASTATUS y PTIME, pero no EXPTIME. Por tanto, los usuarios que estén bloqueados, se tendrán que desbloquear y los que esten en “EXPIRED” se les tendrá que cambiar la contraseña.

Ahora vamos a ver lo mismo desde OEM …

iremos a la pestaña “Servidor” y en el apartado Seguridad pulsaremos en el enlace “Perfiles”.

Seguidamente en el perfil (normalmente suele ser el perfil DEFAULT) que corresponda al usuario afectado, pulsaremos en el perfil y luego lo editaremos …

Por último, editaremos el perfil y cambiaremos sus valores, pulsaremos en “Aplicar” para aplicar los cambios realizados.

Pues esto es todo …
Sencillito!!! ;-)

 

¡¡¡¡ Crecimiento UNDOscriminado !!!! – Tablespace UNDO

En esta entrada de Blog voy a intentar explicar a grandes rasgos como funcionan los tablespaces de UNDO y mostraré una solución a un crecimiento indiscriminado del tablespace de UNDO que llenó un filesystem e hizo tambalear el sistema.

Es bastante común encontrarse con que el tablespace de UNDO, debido a que algún proceso se cuelgue, empiece a crecer de manera indiscriminada y si a esto le sumas que se tenga configurado en autoextend=ON con un límite mayor de la capacidad libre existente en ese momento en el disco donde se encuentre el datafile del tablespace,  circunstancia, que puede provocar que el disco se llene y por tanto, un posible crash del sistema.

Para evitar esto, un  correcto dimensionamiento del tablespace UNDO nos puede evitar más de un quebradero de cabeza. Empezaré con un poco de teoría para situarnos ….

El tablespace de UNDO es lo que se conoce como un tablespace de sistema. Como lo son el de SYSTEM, SYSAUX o el TEMP. El objetivo de un tablespace de UNDO es almacenar información que permita deshacer cambios, crear imágenes antiguas de bloques de datos e incluso almacenar información útil que la funcionalidad del Flashback pueda requerir en algún momento. El tablespace UNDO debe crearse como UNDO en el proceso de creación y puede perfectamente hacerse con 2 datafiles y en filesystems distintos entre sí y distintos de donde esté ubicado el tablespace TEMP.

 Ejemplo muy básico de creación de UNDO:

CREATE UNDO TABLESPACE undotbs01
DATAFILE '/undotbs01.dbf' SIZE 200M 
AUTOEXTEND ON MAXSIZE 8192M;

El tablespace de tipo UNDO debe existir para que la base de datos pueda almacenar allí los registros propios “de deshacer”. Si no existe, la instancia usará el tablespace SYSTEM para tal fin. Cosa nada recomendable y que es más que probable que en el archivo ALERT_.log se genere el error:

ORA-01552: cannot use system rollback segment for non-system tablespace ‘%s’.

Por otro lado, existe un parámetro de inicialización “UNDO_TABLESPACE” que permite indicarle a la instancia cual es el tablespace UNDO a utilizar, útil en los casos en los que existen varios tablespaces UNDO.

¿ Como gestiona ORACLE el tablespace UNDO ?

Oracle lo puede gestionar de dos maneras distintas:

1.- SMU (System Managed Undo)- (AUTO*) o lo que es lo mismo, modo automático de gestión, donde no se utilizan los segmentos de rollback externos, la información de UNDO se almacena en un tablespace especial dedicado exclusivamente a este objetivo.

2.- RBU (modo manual de gestión del UNDO) – (MANUAL*) . Se almacena de forma externa en segmentos de rollback. Este era el único método que existía en versiones anteriores de Oracle.

Ambos modos de gestión se establecen mediante el parámetro dinámico UNDO_MANAGEMENT que libera a los DBA´s de la administración y monitoreo si lo configuras en modo AUTO.

SQL> alter system set undo_management = <valor>;

Ejemplo para setear el parámetro en modo AUTO:

SQL> alter system set undo_management = AUTO;

¿ Cómo ver que valores de UNDO tenemos configurados en la BBDD ?

SQL> show parameters undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------------
undo_management                      string       AUTO
undo_retention                       integer      900
undo_tablespace                      string       UNDOTBS1

¿ Que significan ?

undo_management
explicado en el punto anterior.

undo_retention
Parámetro dinámico que indica en segundos (900 por defecto) cuanto tiempo ha de permanecer, al menos, la información de UNDO disponible.

Ej: alter system set undo_retention=<valor>;

undo_tablespace
Parámetro dinámico que indica el tablespace de almacenamiento de “UNDO” a usar en el arranque.

Hasta aquí un poco de teoría, pondré el problema que me he encontrado alguna vez y su solución y ampliaré información durante la explicación para entender mejor otros parámetros que no he explicado anteriormente.

Problema: El Tablespace de UNDO crece indiscriminadamente y llega a llenar el filesystem donde estaba ubicado el datafile asociado. Este crecimiento indiscriminado puede ser debido a una transacción muy grande, un import de alguna tabla muy grande, etc…

Solución:
Volver a poner en el tamaño adecuado el tablespace de UNDO una vez realizada la transacción. Lamentablemente, no se puede reducir un tablespace de UNDO, tenemos que crear un nuevo tablespace tipo undo con el tamaño deseado, asignar este a la BBDD, y eliminar el antiguo. Luego si queremos volver a ponerlo todo igual, repetiremos la operación creando de nuevo otro tablespace tipo undo con el mismo nombre que tenía originalmente el primer tablespace undo y asignárselo a la BBDD. Finalmente borraremos el auxiliar creado. Por último, comprobaremos que realmente el tablespace de UNDO está correctamente definido.

NOTA: Realmente NO es que no se pueda reducir el tablespace de undo haciendo un RESIZE. Lo cierto es que si se puede, pero sólo hasta donde lo permita la HWM (High water mark).

Pasos para solucionar el problema:

Como el datafile llenó el filesystem, lo primero que tenía que hacer es hacer espacio en el filesystem, pero asegurándome de que cuando haga espacio, el tablespace de UNDO no siga creciendo de manera que lo vuelva a llenar. Para ello, primero quitaré el autoextend del tablespace UNDO.

Conectado como sys o system ….

SQL> ALTER DATABASE DATAFILE '/oracle/oradata/SID/undotbs01.dbf' AUTOEXTEND OFF;

Una vez asegurado de que el datafile no crecerá más, haremos espacio en el filesystem. Como se trata del filesystem donde están ubicados los tablespaces de sistema de Oracle como system, temp, etc... y no tenía otros ficheros que pudiese eliminar para hacer espacio. Lo que hice fue reducir el tamaño del tablespace TEMP que posteriormente, cuando ya haya conseguido volver a redefinir el tablespace UNDO volveré a poner a su tamaño original.

SQL> ALTER DATABASE TEMPFILE '/oracle/oradata/SID/temp01.dbf' RESIZE 2048M;

NOTA: Reducir TEMP a un tamaño que nos permita ganar espacio en el filesystem, pero que permita también trabajar a la base de datos.

Ahora podríamos probar de intentar reducir el TS UNDO con resize a ver si Oracle nos lo permite. Esto nos ahorraría el trabajo de crear el auxiliar, etc…. Pero tenemos que saber que tamaño nos deja reducirlo por encima de la HWM. (En mi caso, no lo conseguí, y tuve que crear el TS UNDO auxiliar).

Imaginemos que quiero reducir el datafile a 1000MB …

SQL> ALTER DATABASE DATAFILE ‘/oracle/oradata/SID/undotbs01.dbf’ RESIZE 1000M;

Al ejecutar la instrucción aparece el siguiente error:

ORA-03297: file contains used data beyond requested RESIZE value

Pues me veo obligado a crearme el TS Auxiliar para poder reducir el TS de Undo….

CONSULTAS ÚTILES RELACIONADAS CON TODAS LAS TAREAS A REALIZAR

Primero saber el tamaño actual del tablespace de UNDO desde SQLPLUS:

SQL>
SELECT Sum(v$datafile.bytes / 1024 / 1024) AS "Tamaño Actual TS UNDO [MB]"
FROM   v$datafile
       INNER JOIN v$tablespace
         ON v$datafile.ts# = v$tablespace.ts#
       INNER JOIN dba_tablespaces
         ON v$tablespace.NAME = dba_tablespaces.tablespace_name
WHERE  dba_tablespaces.contents = 'UNDO'
       AND dba_tablespaces.status = 'ONLINE'
/

 Podemos visualizarlo también directamente desde la consola de OEM:

Iremos a la pestaña “servidor” y pulsamos en el enlace “Gestión automática de deshacer”  y sale esta pantalla donde podemos ver la información del tablespace Undo incluído su tamaño actual.

La siguiente consulta nos puede ayudar a determinar un valor adecuado para el TS Undo

SELECT Substr(e.VALUE,1,25) "UNDO RETENTION [Sec]",
       d.undo_size / (1024 * 1024) "Tamaño Actual UNDO [MByte]",
       (To_number(e.VALUE) * To_number(f.VALUE) * g.undo_block_per_sec) / (1024 * 1024) "Tamaño Optimo UNDO [MByte]"
FROM   (SELECT Sum(a.bytes) undo_size
        FROM   v$datafile a,
               v$tablespace b,
               dba_tablespaces c
        WHERE  c.contents = 'UNDO'
               AND c.status = 'ONLINE'
               AND b.NAME = c.tablespace_name
               AND a.ts# = b.ts#) d,
       v$parameter e,
       v$parameter f,
       (SELECT Max(undoblks / ((end_time - begin_time) * 3600 * 24)) undo_block_per_sec
        FROM   v$undostat) g
WHERE  e.NAME = 'undo_retention'
       AND f.NAME = 'db_block_size'
/

Devuelve algo como:

UNDO RETENTION [Sec]      Tama??o Actual UNDO [MByte]
------------------------- ---------------------------
Tama??o Optimo UNDO [MByte]
---------------------------
900                                             12288
                 1779.82031

Aún así, yo recomiendo crearlo con el tamaño original e ir controlando durante el trabajo habitual de la BBDD que necesidades tiene el UNDO.

Seguimos … Creamos el nuevo TS Undo auxiliar con el tamaño mínimo de este óptimo sacado anteriormente, por ejemplo.

SQL>CREATE UNDO TABLESPACE "UNDOTBS_AUX" DATAFILE ‘/oracle/oradata/undotbs_aux.dbf’ SIZE 1800M;

Ahora debemos establecerlo en la BBDD. (Yo prefiero hacerlo desde OEM), pero desde SQL se hace así:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=’UNDOTBS_TEMP’ scope=both;

Ahora ya podríamos eleminar el primer tablespace de Undo (“el original” que creció desmesuradamente):

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;

Aquí nos puede pasar dos cosas, una que lo borre sin problemas y otra es que nos dé el siguiente error:

ERROR at line 1:
ORA-30013: undo tablespace ‘UNDOTBS1′ is currently in use

Pues tenemos que poner offline los segmentos de rollback que están online en el UNDO. Para saber que segmentos son, usaremos la siguiente consulta:

SELECT owner, segment_name, status FROM dba_rollback_segs
WHERE tablespace_name='UNDOTBS1' ORDER BY 3;

Sale algo como:

OWNER  SEGMENT_NAME                   STATUS
 ------ ------------------------------ ----------------
 PUBLIC _SYSSMU1_3129330276$           OFFLINE
 PUBLIC _SYSSMU2_1303119469$           OFFLINE
 PUBLIC _SYSSMU3_1479839839$           OFFLINE
 PUBLIC _SYSSMU4_3642025306$           OFFLINE
 PUBLIC _SYSSMU10_1545059069$          OFFLINE
 PUBLIC _SYSSMU6_1524158477$           OFFLINE
 PUBLIC _SYSSMU7_1101254083$           OFFLINE
 PUBLIC _SYSSMU8_4223133350$           OFFLINE
 PUBLIC _SYSSMU9_3952001453$           OFFLINE
 PUBLIC _SYSSMU5_2891492079$           ONLINE
10 rows selected.

Pues para cambiar el estado haremos:

SQL> ALTER ROLLBACK SEGMENT "_SYSSMU5_2891492079$" OFFLINE;

Seguidamente volveremos a ejecutar la sentencia:

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

y no debería dar problemas.

Ahora tan sólo deberíamos volver a crear el tablespace de UNDO con el nombre original y tamaño original antes del incidente y por último, volver a asignar a la Base de datos este tablespace de UNDO que crearemos. (Y en este ejemplo, también volveremos a poner el tamaño que tenía originalmente al tablespace TEMP usando “resize”).  Se hace así:

SQL> CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE ‘/oracle/oradata/SID/undotbs01.dbf’ SIZE 1000M;
Tablespace created.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=’UNDOTBS1' SCOPE=BOTH;
System altered.

SQL> DROP TABLESPACE UNDOTBS_TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

NOTA: si no deja borralo, proceder como en el ejemplo anterior.

SQL> show parameters undo
NAME                                 TYPE        VALUE
------------------------------------ -------     --------
_undo_autotune                       boolean     TRUE
undo_management                      string      AUTO
undo_retention                       integer     3000
undo_tablespace                      string      UNDOTBS1

CONSULTAS ÚTILES RELACIONADAS CON EL UNDO

La siguiente consulta busca una sesión de usuario que está en el undo para poderla matar…

column username format a30

SELECT a.name,b.status , d.username , d.sid , d.serial#
FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d
WHERE  a.usn = b.usn
AND    a.usn = c.xidusn
AND    c.ses_addr = d.saddr
AND    a.name IN (
    SELECT segment_name
    FROM dba_segments
    WHERE tablespace_name = 'UNDOTBS1'
   );
Para matar la sesión ejecutaremos:
 SQL>  alter system kill session '147,4';
 donde 147 es el SID y 4 el SERIAL#
# Con la siguiente query podemos ver el estado de los segmentos de UNDO:
# UNEXPIRED significa que estos segmentos de UNDO no contienen ninguna transacción activa,
# pero estos contienen transacciones que todavía son requeridos para FLASHBACK.
# EXPIRED significa que estos segmentos no son requeridos después del periodo de retención definido en undo_retention.
# ACTIVE significa que estos segmentos de UNDO contienen transacciones activas, o sea, no se ha realizado commit.
# Los valores son en MB.  

SELECT SYSDATE AS fecha,
       unexpired.unexpired,
       expired.expired,
       active.active
FROM   (SELECT Sum(bytes / 1024 / 1024) AS unexpired
        FROM   dba_undo_extents
        WHERE  status = 'UNEXPIRED') unexpired,
       (SELECT Sum(bytes / 1024 / 104) AS expired
        FROM   dba_undo_extents tr
        WHERE  status = 'EXPIRED') expired,
       (SELECT CASE
                 WHEN Count(status) = 0
                 THEN 0
                 ELSE Sum(bytes / 1024 / 1024)
               END AS active
        FROM   dba_undo_extents
        WHERE  status = 'ACTIVE') active
/
# Buscar la sesión que está colgada o pendiente de ponerse a OFFLINE en TS UNDO para luego matarla

set lines 10000
column name format a10

SELECT a.name,b.status
FROM   v$rollname a,v$rollstat b
WHERE  a.usn = b.usn
AND    a.name IN (
    SELECT segment_name
    FROM dba_segments
    WHERE tablespace_name = 'UNDOTBS1'
   );

# Transacciones activas en TS UNDO

SELECT NAME, XACTS "ACTIVE TRANSACTIONS"
FROM V$ROLLNAME, V$ROLLSTAT
WHERE STATUS = 'PENDING OFFLINE'
AND V$ROLLNAME.USN = V$ROLLSTAT.USN;

Tipos de parámetros en ORACLE

Existen tres tipos de parámetros en oracle:

- Parámetros “fijos”=> Son parámetros que una vez instalada la base de datos no se pueden volver a modificar / configurar. El juego de caracteres es un claro ejemplo.

- Parámetros Estáticos => Son parámetros que se pueden modificar, pero su modificación implica cerrar la base de datos y volverla a abrir para que los lea del fichero y pueda realizar el cambio.

- Parámetros Dinámicos=> Son parámetros cuyo valor se puede cambiar sin necesidad de cerrar la base de datos a diferencia de los estáticos.

Podemos asignar valores a los parámetros de la siguiente manera:

SQL> ALTER SYSTEM SET <parámetro> = <valor> [SCOPE = <ámbito>]

El valor del ámbito puede ser uno de los siguientes.

• spfile  –> Fichero de parámetros
• memory  –> En memória (en caliente)
• both  –> En memória y en el fichero de parámetros spfile.

Ejemplo:

El ejemplo anterior sólo funciona a partir de la versión 9.

Para ver el valor actual de un parámetro.
SQL> show parameter <parámetro>

Existe una manera sencilla de verificar que parámetros son dinámicos y cuales son estáticos.

La vista V$PARAMETER nos proporciona este tipo de información.

La columna ISSYS_MODIFIABLE contiene tres (3) tipos de valores:
• Immediate
• Deferred
• False
Por ejemplo:
SQL> select distinct ISSYS_MODIFIABLE from V$PARAMETER;

ISSYS_MODIFIABLE
—————————
IMMEDIATE
DEFERRED
FALSE

IMMEDIATE: identifica aquellos parámetros dinámicos, es decir, los ajustes se realizan de manera inmediata.
DEFERRED: los ajustes pueden ser realizados en “caliente” pero tomarán efecto solo después de que la base de datos sea re-inicializada.
FALSE: obligatoriamente la base de datos debe bajarse para poder efectuar el cambio.

La siguiente sentencia nos permite conocer lo anterior mencionado:

Col NAME format a50
Col ISSYS_MODIFIABLE format a20

Select NAME, ISSYS_MODIFIABLE
From V$PARAMETER
Order by 1