Administración Oracle

Un afectuoso saludos de navidad y que el próximo año este lleno de paz, amor, éxito, optimismo y fe.

Saludo Navideño Oracle.Xgodoy.Com

Por alguna razón puede que se requiera modificar el nombre de la base de datos, modificando un par de parámetros y recreando el controlfile puede ser una tarea entre paréntesis fácil pero esto no asegura el cambio íntegro ya que solo cambia el DBNAME pero no el DBID, el DBID es interno y único identificador de la base de datos. Por ejemplo, se requiere restaurar una base de datos en el mismo servidor de origen (algo así como una copia de la base de datos), se ve algo fácil, solo bastaría con levantar una instancia con otro nombre, restaurar la base de datos sobre esa instancia y recrear el controlfile, pero las dos base de datos seguirían con el mismo DBID, algunos de los problemas con esto son por ejemplo los backups con RMAN ya que este identifica a las bases de datos con el DBID. Otro caso práctico es restaurar una copia de nuestra base de datos en el mismo servidor donde se encuentra nuestro DATAGUARD, simplemente nuestro DATAGUARD tendrá muchos problemas al encontrarse dos bases de datos en el mismo servidor con el mismo DBID (aunque las instancias tengan distinto nombre). La solución para estos casos es una utilidad llamada DBNEWID, fácil de utilizar pero con consideraciones no documentadas explícitamente, en el siguiente artículo realizaremos un par de ejemplos sobre cómo utilizar el DBNEWID y las consideraciones que se deben tener en cuenta.

Cambiando solo el DBNAME

Se cuenta con una base de datos llamada jimydb, cambiaremos solo el nombre de esta a TESTDB:

Lo primero es crear un archivo de init:

cp $ORACLE_HOME/dbs/initjimydb.ora $ORACLE_HOME/dbs/initTESTDB.ora

Modificamos el parámetro db_name en el init y todo parámetro que haga referencia al nombre de la instancia o la base de datos. Luego es necesario agregar al listener.ora, tnsnames.ora y /etc/oratab la instancia TESTDB.

Ahora a renombrar la base de datos.

La base de datos debe están en modo MOUNT:

[oracle@SERVER dbs]$ . oraenv
ORACLE_SID = [jimydb] ? jimydb
[oracle@SERVER dbs]$sqlplus / as sysdba

SQL> shutdown immediate
SQL>startup mount

Luego de sede ejecutar el utilitario DBNEWID con un usuario con privilegios sysdba de la siguiente forma:

nid TARGET=SYS/password@jimydb DBNAME=TESTDB SETNAME=YES

Preguntará si deseas cambiar el nombre de la base de datos, le damos “Y”:

La salida es algo por el estilo:

DBNEWID: Release 10.2.0.3.0 – Production on Wed Jul 8 16:57:36 2009
 
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 
Connected to database JIMYDB (DBID=3309000021)
 
Connected to server version 10.2.0
 
Control Files in database:
    /u02/oradata/jimydb/control01.ctl
    /u02/oradata/jimydb/control02.ctl
    /u02/oradata/jimydb/control03.ctl
 
Change database name of database JIMYDB to TESTDB? (Y/[N]) => Y
 
Proceeding with operation
Changing database name from JIMYDB to TESTDB
    Control File /u02/oradata/jimydb/control01.ctl – modified
    Control File /u02/oradata/jimydb/control02.ctl – modified
    Control File /u02/oradata/jimydb/control03.ctl – modified
    Datafile /u02/oradata/jimydb/system01.dbf – wrote new name
    Datafile /u02/oradata/jimydb/undotbs01.dbf – wrote new name
    Datafile /u02/oradata/jimydb/sysaux01.dbf – wrote new name
    Datafile /u02/oradata/jimydb/users01.dbf – wrote new name
    Datafile /u02/oradata/jimydb/testdat.dbf – wrote new name
    Datafile /u02/oradata/jimydb/testdat2.dbf – wrote new name
    Datafile /u02/oradata/jimydb/temp01.dbf – wrote new name
    Control File /u02/oradata/jimydb/control01.ctl – wrote new name
    Control File /u02/oradata/jimydb/control02.ctl – wrote new name
    Control File /u02/oradata/jimydb/control03.ctl – wrote new name
    Instance shut down
 
Database name changed to TESTDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.DBNEWID – Completed succesfully.

Se puede apreciar la modificación del controlfile y como marca los datafiles. Termina la correcta ejecución bajando la base de datos. Luego y ya que el archivo initTESTDB.ora ya esta creado nos ambientamos como TESTDB y levantamos la base de datos:

[oracle@SERVER dbs]$ . oraenv
ORACLE_SID = [jimydb] ? TESTDB
[oracle@SERVER dbs]$ sqlplus / as sysdba

SQL> startup

Verificamos parámetros y nombre de la base de datos:

SQL> sho parameters name
NAME                                 TYPE        VALUE
- – - – - – - – - – - – - – – - – - – - – - – - – - – - – -
db_file_name_convert                 string
db_name                              string      TESTDB
db_unique_name                       string      TESTDB
global_names                         boolean     FALSE
instance_name                        string      TESTDB
lock_name_space                      string
log_file_name_convert                string
service_names                        string      TESTDB

SQL> select DBID, NAME, DB_UNIQUE_NAME from v$database;
      DBID NAME      DB_UNIQUE_NAME
- – - – - – - – - – - – - – - – - – - – - – - – -
3309000021 TESTDB    TESTDB

Listo!, por ultimo creamos un nuevo archivo de password y verificamos los procesos oracle:

[oracle@SERVER dbs]$ orapwd file=/u01/app/oracle/db/dbs/orapwTESTDB password=password entries=10;
[oracle@SERVER dbs]$ ps -fea |grep ora_
oracle    1108     1  0 23:21 ?        00:00:00 ora_pmon_TESTDB
oracle    1110     1  0 23:21 ?        00:00:00 ora_psp0_TESTDB
oracle    1112     1  0 23:21 ?        00:00:00 ora_mman_TESTDB
oracle    1114     1  0 23:21 ?        00:00:00 ora_dbw0_TESTDB
oracle    1116     1  0 23:21 ?        00:00:00 ora_lgwr_TESTDB
oracle    1118     1  0 23:21 ?        00:00:00 ora_ckpt_TESTDB
oracle    1120     1  0 23:21 ?        00:00:00 ora_smon_TESTDB
oracle    1122     1  0 23:21 ?        00:00:00 ora_reco_TESTDB
oracle    1124     1  0 23:21 ?        00:00:00 ora_cjq0_TESTDB
oracle    1126     1  0 23:21 ?        00:00:00 ora_mmon_TESTDB
oracle    1128     1  0 23:21 ?        00:00:00 ora_mmnl_TESTDB
oracle    1134     1  0 23:21 ?        00:00:01 ora_j000_TESTDB
oracle    1136     1  0 23:21 ?        00:00:00 ora_j001_TESTDB

Cambiando el DBNAME y DBID

Actualmente la base de datos se llama TESTDB, la renombraremos a PRUEBADB y además cambiaremos el DBID de esta:

Primero identificaremos el nombre actual y DBID de la base de datos:

SQL> select DBID, NAME, DB_UNIQUE_NAME from v$database;
      DBID NAME      DB_UNIQUE_NAME
- – - – - – - – - – - – - – - – - – - – - – - – -
3309000021 TESTDB    TESTDB

Crear un archivo de init:

cp $ORACLE_HOME/dbs/initTESTDB.ora $ORACLE_HOME/dbs/initPRUEBADB.ora

Modificamos el parámetro db_name en el init y todo parámetro que haga referencia al nombre de la instancia o la base de datos. Luego es necesario agregar al listener.ora, tnsnames.ora y /etc/oratab la instancia PRUEBADB.

La base de datos debe están en modo MOUNT:

[oracle@SERVER dbs]$ . oraenv
ORACLE_SID = [TESTDB] ? TESTDB
[oracle@SERVER dbs]$sqlplus / as sysdba

SQL> shutdown immediate
SQL> startup mount

Luego de sede ejecutar el utilitario DBNEWID con un usuario con privilegios sysdba de la siguiente forma:

nid TARGET=SYS/password@TESTDB DBNAME=PRUEBADB

Preguntará si deseas cambiar el nombre de la base de datos, le damos “Y”:

La salida es algo por el estilo:

DBNEWID: Release 10.2.0.3.0 – Production on Wed Jul 8 23:40:07 2009
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to database TESTDB (DBID=3309000021)
Connected to server version 10.2.0
Control Files in database:
    /u02/oradata/jimydb/control01.ctl
    /u02/oradata/jimydb/control02.ctl
    /u02/oradata/jimydb/control03.ctl
Change database ID and database name TESTDB to PRUEBADB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 3309000021 to 455356631
Changing database name from TESTDB to PRUEBADB
    Control File /u02/oradata/jimydb/control01.ctl – modified
    Control File /u02/oradata/jimydb/control02.ctl – modified
    Control File /u02/oradata/jimydb/control03.ctl – modified
    Datafile /u02/oradata/jimydb/system01.dbf – dbid changed, wrote new name
    Datafile /u02/oradata/jimydb/undotbs01.dbf – dbid changed, wrote new name
    Datafile /u02/oradata/jimydb/sysaux01.dbf – dbid changed, wrote new name
    Datafile /u02/oradata/jimydb/users01.dbf – dbid changed, wrote new name
    Datafile /u02/oradata/jimydb/testdat.dbf – dbid changed, wrote new name
    Datafile /u02/oradata/jimydb/testdat2.dbf – dbid changed, wrote new name
    Datafile /u02/oradata/jimydb/temp01.dbf – dbid changed, wrote new name
    Control File /u02/oradata/jimydb/control01.ctl – dbid changed, wrote new name
    Control File /u02/oradata/jimydb/control02.ctl – dbid changed, wrote new name
    Control File /u02/oradata/jimydb/control03.ctl – dbid changed, wrote new name
    Instance shut down
Database name changed to PRUEBADB.
Modify parameter file and generate a new password file before restarting.
Database ID for database PRUEBADB changed to 455356631.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.

Se puede apreciar la modificación del controlfile y como marca los datafiles. A diferencia del proceso anterior el utilitario avisa el cambio de DBID. Termina la correcta ejecución bajando la base de datos.

Luego y ya que el archivo initPRUEBADB.ora ya esta creado nos ambientamos como PRUEBADB y levantamos la base de datos:

[oracle@SERVER dbs]$ . oraenv
ORACLE_SID = [TESTDB] ? PRUEBADB
[oracle@SERVER dbs]$ sqlplus / as sysdba

SQL> startup mount
SQL> alter database open resetlogs;

Verificamos parámetros y nombre de la base de datos:

SQL> sho parameters name
NAME                                 TYPE        VALUE
- – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – - – -
db_file_name_convert                 string
db_name                              string      PRUEBADB
db_unique_name                       string      PRUEBADB
global_names                         boolean     FALSE
instance_name                        string      PRUEBADB
lock_name_space                      string
log_file_name_convert                string
service_names                        string      PRUEBADB

SQL> select DBID, NAME, DB_UNIQUE_NAME from v$database;
      DBID NAME      DB_UNIQUE_NAME
- – - – - – - – - – - – - – - – - – - – - – - – -
455356631  PRUEBADB  PRUEBADB

Listo!, por ultimo creamos un nuevo archivo de password y verificamos los procesos oracle:

[oracle@SERVER dbs]$ orapwd file=/u01/app/oracle/db/dbs/orapwPRUEBADB password=password entries=10;
[oracle@SERVER dbs]$ ps -fea |grep ora_
oracle    1208     1  0 23:25 ?        00:00:00 ora_pmon_PRUEBADB
oracle    1210     1  0 23:25 ?        00:00:00 ora_psp0_PRUEBADB
oracle    1212     1  0 23:25 ?        00:00:00 ora_mman_PRUEBADB
oracle    1214     1  0 23:25 ?        00:00:00 ora_dbw0_PRUEBADB
oracle    1216     1  0 23:25 ?        00:00:00 ora_lgwr_PRUEBADB
oracle    1218     1  0 23:25 ?        00:00:00 ora_ckpt_PRUEBADB
oracle    1220     1  0 23:25 ?        00:00:00 ora_smon_PRUEBADB
oracle    1222     1  0 23:25 ?        00:00:00 ora_reco_PRUEBADB
oracle    1224     1  0 23:25 ?        00:00:00 ora_cjq0_PRUEBADB
oracle    1226     1  0 23:25 ?        00:00:00 ora_mmon_PRUEBADB
oracle    1228     1  0 23:25 ?        00:00:00 ora_mmnl_PRUEBADB
oracle    1234     1  0 23:25 ?        00:00:01 ora_j000_PRUEBADB

Consideraciones

Al cambiar el nombre y el DBID de la base de datos, todos los backup vía RMAM y los archivelogs que existan no servirá más ya que después de cambiar el DBID se debe abrir la base de datos con la opción RESETLOGS lo que resetea la secuencia de los redologs a 1. Luego, inmediatamente después de realizar el cambio de DBID se debe realizar un backup full de la base de datos.

Si de desea cambiar el DBID de una base de datos recientemente restaurada (sin abrir aun), es necesario crear un tablespace temporal, si un tablespace temporal no existe el DBID fallará y no se podrá revertir el cambio, luego la base de datos quedará inservible y la documentación al respecto dice que es necesario entre muchos pasos recrear el controlfile (BUG 5861994). Del problema descrito conozco casos de éxito donde se ha logrado salvar la base de datos, pero el proceso es muy engorroso y lo mejor es evitar esos casos.

Referencias Metalink:

DOC ID 552053.1: NID Fails if Tempfiles Do Not Exist
DOC ID 224266.1: How to Change the DBID and the DBNAME by using NID

Saludos!
Jimy Godoy Maureira

CLOUG Noviembre 2009

El 16 de noviembre de 2009 de realizó el segundo evento del Grupo De Usuarios Oracle Chile – CLOUG, este evento contó con la presencia de un gran exponente de Oracle a nivel mundial, el Sr. Tom Kyte de ASK TOM, los expositores fueron:

• Francisco Muñoz (Chile), organizador del evento junto a Juan Puga. Las presentaciones de Francisco fueron:
           o Tips and Best Practices for DBA
           o Logging or NoLogging: That’s the question!
• Juan Camilo Ruiz (Colombia), presentando:
           o Creating SOA Composite Applications with ADF and SOA Suite 11g.
• Tom Kyte, presentando:
           o All about binding.
           o Reorganizing Objects
           o Top 10, no 11, new features of Oracle database 11g realese 2.
           o All about metadata; why telling the database about your schema matters.

Excelente presentación, un poco agotadora, fueron casi 9 horas seguidas de presentaciones.

Cuando estén disponibles las diapositivas del evento, subiré la documentación.

Si la base de datos se encuentra en modo archivelog, es necesario determinar una política de respaldo y eliminación de estos, si los archivelog no son borrados, el espacio configurado para estos puede volverse insuficiente generando incluso que la base de datos se “congele” debido a que no puede generar un archivelog.

Un error (WARNING) típico que se puede apreciar en el alertlog es:

ORA-19815: WARNING: db_recovery_file_dest_size of 85899345920 bytes is 100.00% used, and has 0 remaining bytes available.
Mon Mar 30 01:11:02 2009
**********************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
**********************************************************************

En este caso, el WARNING se convierte en un error grave, no hay espacio suficiente (“100.00% used”) configurado para la generación de archivelog, luego comienzan errores como:

Mon Mar 30 01:11:02 2009
Errors in file /u01/app/oracle/db/admin/jimydb/bdump/caefdb2_arc0_10217.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 97741824 bytes disk space from 85899345920 limit
ARC0: Error 19809 Creating archive log file to ‘/u02′
ARCH: Archival stopped, error occurred. Will continue retrying
Mon Mar 30 01:11:02 2009
ORACLE Instance jimydb – Archival Error
Mon Mar 30 01:11:02 2009
ORA-16038: log 11 sequence# 54433 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 11 thread 2: ‘/u02/jimydb/onlinelog/group_11.315.681334097′
ORA-00312: online log 11 thread 2: ‘/u02/jimydb/onlinelog/group_11.789.681334099′

Como se puede observar el error es grave, la base de datos ya no puede escribir archivelogs, el error indica que no se puede escribir en el directorio /u02, realmente esto no significa que el directorio no tenga espacio disponible (aunque podría ser causa del error), realmente el error es que se han almacenado archivelog ocupando más espacio que los permitidos por el parametro db_recovery_file_dest_size, para este caso se ha excedido el límite de 80 GB:

SQL> sho parameters db_recovery_file_dest_size
NAME                                 TYPE       VALUE
db_recovery_file_dest_size           big integer 80G
SQL>

Una salida rápida es el aumento del valor de este parámetro (si el espacio físico y real lo permite):

SQL> alter system set db_recovery_file_dest_size = 90G scope=both;
System altered.
SQL> sho parameters db_recovery_file_dest_size
NAME                                 TYPE       VALUE
db_recovery_file_dest_size           big integer 90G
SQL>

Listo!, la base de datos comienza a operar nuevamente, la acción inmediata tras esta alteración es eliminar los archivelog (previo respaldo) .

Lo importante es ser proactivo y no esperar a que se congele la base de datos, la siguiente query ayuda a monitorear el espacio disponible en el directorio db_recovery_file_dest  según lo configurado en db_recovery_file_dest_size:

SELECT
  NAME AS “Directorio Raiz Recovery Dest”,
  space_limit
    / 1024
    / 1024 AS “Max Espacio Configurado [MB]“,
  TRUNC(space_used
          / 1024
          / 1024,2) AS “Espacio Utilizado [MB]“,
  number_of_files AS “Cantidad De Archivos”,
  TRUNC(space_used
          * 100
          / space_limit,2) AS “% Utilizado Recovery Dest”
FROM
  v$recovery_file_dest;

Resultado:

El resultado muestra:

Directorio Raiz Recovery Dest: Directorio donde se generan los archivelog. Se puede revisar con “show parameters db_recovery_file_dest” .

Max Espacio Configurado [MB]: Espacio configurado el parametro db_recovery_file_dest_size y que significa el máximo espacio asignado para archivelog.

Espacio Utilizado [MB]: Espacio utilizado del directorio db_recovery_file_dest.

Cantidad De Archivos: Cantidad de archivelog que actualmente residen es el directorio db_recovery_file_dest.

% Utilizado Recovery Dest: Porcentaje del espacio total utilizado por archivelog en el directorio db_recovery_file_dest.

Descargar Articulo Completo Formato PDF

Saludos!
Jimy Godoy

El crecimiento indiscriminado del tablespace de UNDO puede volverse un gran problema, además de indicarnos que algo no anda bien, por otro lado un UNDO muy pequeño puede significar que ciertas instrucciones no terminen correctamente arrojando el siguiente error:

A continuación algunas formas para monitorear el estado del UNDO, las transacciones que más UNDO requieren y técnicas para setear el tamaño más adecuado.

Primero revisaremos los parámetros referentes al UNDO:

UNDO_MANAGEMENT: Parámetro dinámico (alter system set undo_management = <valor>) disponible desde 9i y que libera a los DBA de la administración y monitoreo del UNDO.

UNDO_RETENTION: Parámetro dinámico (alter system set undo_retention= <valor>) que indica en segundos  (por defecto 900) cuanto tiempo ha de permanecer, al menos, la información de UNDO disponible.

UNDO_TABLESPACE:Parámetro dinámico (alter system …) que indica el espacio de almacenamiento de “UNDO” a usar en el arranque. Si se indica en modo manual, provoca error y falla el arranque.

Para este caso setearemos la retención de UNDO a 600 segundos:

Segmentos de UNDO

Con la siguiente query podemos ver el estado de los segmentos de UNDO:

Por ejemplo el resultado para esta prueba es:

Resultado:

Donde,

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.  He creado una tabla llamada undo_utilizado donde se guarda el retorno de esta query cada 20 segundos para luego realizar un a análisis más fino.

Por ejemplo ejecutaremos un insert sin dar commit:

El comportamiento de los segmentos de UNDO es:

Resultado:

Como se puede observar, actualmente existen 2,9375 MB de segmentos en estado ACTIVE y 0,0625 MB en estado UNEXPIRED. Si ejecutamos un commit para la sesión ocurre lo siguiente:

Resultado:

Como se puede observar, luego de realizar commit, ya no existen segmentos en estado ACTIVE y estos pasaron a ser segmentos en estado UNEXPIRED.

En el tablespace de UNDO, los segmentos en estado UNEXPIRED significan espacio utilizado.

Un análisis de lo ocurrido con el siguiente grafico:

En el grafico podemos identificar los siguientes eventos:

• 17:39:06, aumento segmentos en estado ACTIVE de 0 MB a 2,9375 MB, esto debido a la instrucción de insert realizada. El insert tardó 11 segundos.

• 17:44:59, aumento de segmentos en estado UNEXPIRED de 0,0625 MB a 3 MB y disminución de segmentos en estado ACTIVE de 2,9375 MB a 0 MB, esto debido a la instrucción de commit realizada en ese instante.

• 17:55:43, Disminución de segmentos en estado UNEXPIRED de 2,9375 MB a 1,0625 MB y aumento de segmentos en estado EXPIRED de 10,1875 MB a 12,0625 MB. Esto debido a lo comprometido en el parámetro undo_retention.

Podemos concluir que al gatillar el insert comienzan a ocuparse segmento de undo y quedan en estado activo (consume espacio en tablespace de UNDO), estos segmentos quedan en estado activo hasta que se gatille un commit pasando a quedar estos segmentos en estado UNEXPIRED (se sigue consumiendo espacio en tablespace de UNDO), luego de un tiempo (determinado por el undo_retention) los segmentos en estado UNEXPIRED pasan a estado EXPIRED liberando espacio en tablespace de UNDO.

OJO!, no siempre el tiempo de retención en el UNDO es el definido en parámetro undo_retention, el tiempo de retención es calculado automáticamente por el motor en periodos de 10 minutos, esto se puede verificar en la vista v$undostat, la forma de auto calcular el tiempo de retención es basado en un porcentaje del tamaño de tablaspace UNDO.

Para verificar, la siguiente prueba:

El undo_retention estaba definido en 900 segundos, se modificó a 60 segundos. Luego para la prueba ejecutaremos un insert:

Lo esperado es que la retención de segmentos en estado UNEXPIRED no supere los 60 segundos. Un análisis de lo ocurrido con el siguiente grafico:

Como se puede ver en el grafico, los segmentos de UNDO en estado UNEXPIRED se mantuvieron por 600 segundos y no por 60 como se había seteado en undo_retention. La no utilización del tiempo determinado en undo_retention se debe al valor de retención calculado mor el motor y que es posible verificar en la vista v$undostat:

El resultado es:

Como se puede observar, el periodo de undo_retention calculado por el motor es de 600 segundos.

Para reparar esto, es necesario incorporar una variable oculta en el init.ora de la base de datos que inhibe la utilización del TUNED_UNDORETENTION. Es necesario setear en el init.ora el siguiente parámetro:

O en spfile:

Con esta configuración, el TUNED_UNDORETENTION no es actualizado, luego el tiempo de retención de UNDO que se utilizará es el especificado en undo_retention.

Repetiremos el ejemplo:

Seteamos el parámetro:

Se ejecuta un insert:

Lo esperado es que la retención de segmentos en estado UNEXPIRED no supere los 60 segundos. Un análisis de lo ocurrido con el siguiente grafico:

Como se puede ver en el grafico, los segmentos de UNDO en estado UNEXPIRED se mantuvieron por 60 segundos, tal como lo indicado en el parámetro undo_retention.

Transacciones que requieren UNDO

Es posible monitorear que transacciones están ocupando segmentos de UNDO y/o generando bloqueos debido a falta de commit o rollback. Con la siguiente query se puede revisar que transacciones están requiriendo UNDO activamente:

Por ejemplo ejecutaremos un insert sin dar commit:

Al ejecutar la consulta, el resultado es el siguiente (por cuestiones de presentación el resultado se presenta de manera transpuesta):

Donde,

LOGON_TIME, es la fecha y hora en que se ejecutó la instrucción.
BLOCKING_SESSION_STATUS, indica si la sesión está bloqueando a otra sesión.
SCHEMANAME, schema que ejecutó la instrucción.
MACHINE, nombre de la maquina desde donde se está ejecutando la instrucción.
PROGRAM, programa desde donde se ejecuta la instrucción.
MODULE, modulo desde donde se ejecuta la instrucción.
SQL_TEXT, instrucción SQL ejecutada.
SERIAL#, Serial number de la sesión que está ejecutando la instrucción.
SID, sesión Id de la sesión que está ejecutando la instrucción.
USERNAME, usuario que ejecutó la instrucción.
STATUS_SESION, estado de la sesión. ACTIVE si se está ejecutando alguna instrucción, INCATIVE si ya se ejecutó la instrucción y no está realizando ninguna operación.
SQL_ID, ID de la instrucción SQL que se está ejecutando.
PREV_SQL_ID, ID de la instrucción SQL previa a la que se está ejecutando.

Al ejecutar un commit:

El resultado es el siguiente:

O sea, no aparecen transacciones activas.

Una o varias transacciones activas que no ejecuten un commit, pueden significar utilización de una gran cantidad de segmentos de UNDO provocando utilización de espacio en tablespace de UNDO.

Calculo UNDO_RETENTION  óptimo

Es posible setear según las estadísticas de la base de datos  los óptimos para el tamaño de tablespace de UNDO y el parámetro undo_retention.

El undo_retention es calculado según el tamaño del tablespace de UNDO, por ejemplo, si contamos con limitaciones de storage y el máximo tamaño del tablespace de UNDO es limitado, es posible calcular el undo_retention optimo para no superar el tamaño de tablespace definido con la siguiente fórmula:

Para realizar este cálculo es necesario contar con las estadísticas de v$undostat, luego para esta prueba es necesario habilitar el undo_autotune de la siguiente forma:

Es necesario setear en el init.ora el siguiente parámetro:

O en spfile:

Además se recomienda que las estadísticas que se toman en v$undostat sean de un tiempo representativo, de este modo no se queda ninguna query fuera del proceso. Por ejemplo, si fue reiniciada la base de datos hace algunos minutos, las estadísticas no serán de mucha ayuda, la idea es que la base de datos esté activa y tomando estadísticas mínimo un día o el tiempo suficiente para que se ejecuten todos los procesos de negocio que  debe soportar el servicio.

Para la prueba, se activará el autotune de la base de datos:

La siguiente query entrega los datos necesarios para calcular el undo_retention óptimo:

El resultado es el siguiente:

O sea, el tamaño del Tablespace de UNDO es 1.3 GB, la cantidad máxima de bloques por segundo es 95 bloques y el tamaño de un bloque es 8 KB. Luego el undo_retention optimo es:

Con la siguiente queda simplificado el cálculo a una sola query:

El resultado de esta es:

Para modificar el undo_retention de la base de datos y no superar en tamaño los 1.3 GB del Tablespace de UNDO es necesario setear el undo_retention a 1831 segundos:

Ahora, si no existen problemas de storage, o sea, el Tablespace de UNDO puede crecer aun más y se requiere un tiempo de retención mayor, es necesario calcular el tamaño del tablespace de UNDO óptimo dependiendo del undo_retention que se requiera setear.

Por ejemplo, digamos que se requiere un tiempo de retención igual a 3000 segundos:

El tamaño óptimo del tablespace de UNDO para esta retención se da por la siguiente fórmula:

La siguiente query entrega los datos necesarios para calcular el tamaño tablespace UNDO óptimo:

El resultado de esta es la siguiente:

O sea, el undo_retention es 3000 segundos, la cantidad máxima de bloques por segundo es 95 bloques y el tamaño de un bloque es 8 KB. Luego el tamaño de tablespace UNDO optimo es:

Con la siguiente queda simplificado el cálculo a una sola query:

El resultado de esta es:

Luego, solo es necesario modificar el tamaño del datafile asociado al tablespace de UNDO a 2227 MB.

Reducir Tamaño de Tablespace de UNDO

Si el tablespace de UNDO tiene los datafiles asociados con la opción autoextend, es posible que crezca indiscriminadamente por alguna razón en particular (una transacción muy grande, un import de alguna tabla grande, etc), luego, en algún momento tal vez se requiera reducir el tablespace de UNDO, lamentablemente, no se puede. La técnica para reducir el tamaño del tablespace de UNDO consiste en crear un nuevo tablespace de tipo UNDO con el tamaño deseado, asignar este tablespace como UNDO predeterminado y eliminar el antiguo.

Lo anteriormente descrito, paso a paso:

El tamaño del tablespace de UNDO obtenido con la siguiente query:

El resultado es el siguiente:

Supongamos que se requiere reducir el tamaño del tablespace a 1000 MB, la instrucción es la siguiente:

Al ejecutar la instrucción aparece el siguiente error:

Bueno, tal vez es muy tajante decir que el tamaño del tablespace no se puede reducir, la verdad si se puede pero solo hasta donde lo permita nuestro bien conocido High Water Mark (HWM). La siguiente query muestra el tamaño actual del tablespace, lo recuperable, el mínimo tamaño que puede tener el tablespace de UNDO, el o  los datafiles a modificar con tamaño propuesto.

El resultado de esta:

Luego, según el resultado de la query, se puede reducir el tamaño del tablespace (datafile asociado) a 1361 MB:

Ningún Problema!

En caso de que se requiera modificar el tamaño del tablespace de UNDO a un valor menor al permitido por la HWM es necesario aplicar la técnica comentada al principio. Para este ejemplo, dejaremos el tablespace de UNDO en 1000 MB.

Primero se debe crear un nuevo tablespace de UNDO:

Luego se debe establecer el nuevo tablespace como tablespace de UNDO  predeterminado:

Ahora la base de datos cuenta con nuevo tablespace de UNDO y del tamaño requerido, luego, es posible eliminar el antiguo tablespace de UNDO

Ejemplo:

¡Excelente!, notar que se nombro el Nuevo tablespace como UNDOTBS_TEMP dado que se podría desear que el tablespace de UNDO mantenga el mismo nombre del inicio, para eso habría que repetir los pasos anteriores, ejemplo:

¡Listo!, ahora está la base de datos igual que antes y el tablespace de UNDO sigue siendo el mismo pero con un tamaño reducido.

Tal vez genere errores al intentar eliminar el tablespace antiguo (en el primer ejemplo), esto se debe a que existen segmentos activos, para ello es necesario identificar los segmentos que no están OFFLINE:

El resultado es:

Todos los segmentos que no están OFFLINE es necesario forzar su estado, para ello se debe modificar el init.ora con el siguiente parámetro:

Se baja la base de datos y se sube con archivo init.ora, luego se borran los segmentos conflictivos:

Se omite el error que se arrojará y se procede a borrar el tablespace:

Espero sea de gran utilidad los temas tratados en este documento.

La plataforma sobre la cual se realizó y verifico lo expuesto en documento es:

Versión Oracle: 10.2.0.3
S.O.: RHEL 4U5

Descargar Articulo Completo Formato PDF

Saludos!
Jimy Godoy Maureira

Necesitamos realizar hot backup vía RMAN, una base de datos standby, stream, para esto y muchas otras cosas es necesario que nuestra base de datos se encuentre en modo archivelog.
Lo primero es verificar si nuestra base de datos se encuentra en modo archivelog:

No Archive Mode indica que nuestra base de datos no se encuentra en modo archivelog, USE_DB_RECOVERY_FILE_DEST indica que el destino por defecto para los archivelog es el db_recovery_file_dest.
Para identificar el destino de los archivelog verificamos el parámetro db_recovery_file_dest:

Ahora dejaremos nuestra base de datos en modo archivelog, para ello es necesario que la base de datos se levante modo mount:

Verificamos nuevamente el modo de nuestra base de datos:

Para probar se genera un archivelog:

Verificamos el archivelog generado en el direcotrio db_recovery_file_dest, dentro de este directorio se creará un directorio con el nombre de la base de datos y dentro de este otro directorio con la fecha de hoy:

Si se desea cambiar el formato y directorio de archivelog log_archive_dest_1 y log_archive_format. Para este ejemplo dejaremos como formato de los archivelog el thread seguido del número de secuencia y el stamp con la extensión arc, los archivelogs los guardaremos en el directorio /u02/archivelog:

Luego verificamos:

Espero la información les sea de utilidad.

Descargar Articulo Completo Formato PDF

Saludos!
Jimy Godoy Maureira

“Necesito una copia exacta de la base de datos”, “Quiero que clonen esta base de datos aquí, allá, y otro clon para comparar versiones y otro clon para…”. ¿Cuántas veces hemos escuchado esas solicitudes?, afortunadamente RMAN tiene la habilidad de clonar una base de datos, el requisito: un backup de la base de datos a clonar realizado con RMAN. A continuación realizaremos un ejemplo de clonación o duplicación de una base de datos.

Para este ejemplo utilizaremos una base de datos llamada PRUEBADB y la clonaremos con el nombre CLONDB.

Requisitos:

Debe existir al menos un backup de la base de datos a clonar, este backup debe ser realizado con RMAN. Si nuestra base de datos no se encuentra bajo un régimen de respaldos tendremos que realizar un backup de esta.
Para este ejemplo el ORACLE_HOME es /u01/app/oracle/

Paso 0:

Modificar el archivo /etc/oratab y agregar al final la siguiente línea:

Luego, debemos ambientarnos:

Si no existe un backup, se realizará uno a través de RMAN.
Se crea el directorio /u02/backup

Es necesario dejar la base de datos en modo ARCHIVELOG:

Nos conectamos a RMAN:

Y ejecutamos el siguiente script:

Paso 1:

Creamos un pfile para extraer los parámetros de inicialización de la base de datos PRUEBADB:

Modificamos el pfile generado, para este caso el pfile contiene los siguientes parámetros:

Modificamos el archivo initCLONDB.ora cambiando donde referencia PRUEBADB por CLONDB y agregar las siguientes líneas para indicar que los archivos acepten una estructura diferente a como vienen en el backup., luego el archivo initCLONDB.ora debe quedar de la siguiente forma:

Seguido, es necesario crear los directorios que utilice el archivo de parámetros:

Paso 2:

Se crea un archivo de password para la nueva base de datos CLONDB:

Paso 3

Modificar listener.ora y tnsnames.ora para la nueva base de datos:

Reload el listener

Paso 4

Nos ambientarnos como CLONDB y conectamos vía sqlplus:

Luego creamos un SPFILE:

Luego levantamos la base de datos en modo nomount:

Paso 5

Con la base de datos clon montada podemos conectarnos a RMAN, es necesario conectarse a la base de datos original (PRUEBADB) y a la clonada como AUXILIARY:

Paso 6

En RMAN ejecutamos la siguiente instrucción para clonar la base de datos:

Listo, base de datos duplicada!

Descargar Articulo Completo Formato PDF

Saludos!
Jimy Godoy.

La siguiente query entrega el nombre del todos los scheduler jobs, la fecha y hora de inicio y la duración de estos en segundos. Se puede filtrar por fecha y hora (marcado con rojo). Es posible filtrar por nombre del job con el campo JOB_NAME.

Descargar Articulo Completo Formato PDF

Saludos!
Jimy Godoy Maureira