ORA-29702: error occurred in Cluster Group Service operation

Hoy hemos cambiado los parámetros de memoria de base de la base de datos y nos hemos encontrado con este error, mirando un poco el alert vemos esto:

Mon Jun 02 10:50:39 2014

DISM started, OS id=22936

Mon Jun 02 10:51:14 2014

USER (ospid: 22284): terminating the instance due to error 29702

Instance terminated by USER, pid = 22284

Lo que hemos visto es que el cambio de memoria a activado el DISM, tenéis más información en el siguiente documento

http://www.oracle.com/technetwork/articles/systems-hardware-architecture/using-dynamic-intimate-memory-sparc-168402.pdf

Pero resulta que no funciona bien con Solaris 10 (DISM Is not Working As Expected in 11.1.0.7 under Solaris 10 (Doc ID 1500293.1), así que hemos conseguido desactivarlo de la siguiente forma

SGA_MAX_SIZE = SGA_TARGET = PGA_AGGREGATE_TARGET = 0

DB_WRITER_PROCESSES

El parámetro DB_WRITER_PROCESSES  especifica el número de procesos DBWn (Database Writer Processes) que se pueden levantar en una base de datos (tengo otra entrada donde explico lo que hace este proceso)
El valor por defecto es 1 o CPU_COUNT / 8, cualquiera que sea el valor mayor.
DB_WRITER_PROCESSES  no puede ser mayor que el parámetro DB_BLOCK_LRU_LATCHES. Si se utiliza el parámetro DBWR_IO_SLAVES, solamente se levantará un proceso de escritura en la base de datos, no importa lo que este especificado en el DB_WRITER_PROCESSES.

Consultas

SQL> Show parameter writer

SQL> Show parameter db_writer_processes

SQL> Alter system set db_writer_processes=3 scope=spfile sid=’*’;

SQL> Select * from
2 v$buffer_pool_statistics;

Archiver Processes (ARCn)

El proceso archiver (ARCn) copia los ficheros redo log files a un dispositivo de almacenaje designado cada vez que se produce un cambio de log. Solamente aparecen cuando la base de datos está en modo ARCHIVELOG y está activado el “automatic archiving”.

Una instancia Oracle puede tener levantados10 procesos ARCn  (del ARC0 al ARC9). El proceso LGWR levanta un nuevo ARCn cada vez que el número de procesos ARCn es insuficiente para mantener la carga. En el alert se registra cada vez que el LGWR levanta un nuevo proceso ARCn.

Si se anticipa un carga de trabajo de archive muy alta, por ejemplo en una carga masiva de datos, se pueden especificar múltiples procesos archive con el parámetro LOG_ARCHIVE_MAX_PROCESSES. La sentencia ALTERSYSTEM puede cambiar el valor de este parámetro dinámicamente incrementando o disminuyendo el número de proceso. Sin embargo no es necesario modificar este parámetro porque la base de datos lo incrementará el número de procesos siempre que lo considere necesario.

Parámetro DB_WRITER_PROCESSES

Este parámetro se introduce en Oracle 8.0.4

DB_WRITER_PROCESSES no puede ser mayor que e l parámetro: DB_BLOCK_LRU_LATCHES.

DB_WRITER_PROCESSES  controla el número de procesos ‘DBW’ que utilizará la instancia. Múltiples DBWn son útiles en sistemas con alta modificación de datos. Este parámetro especifica el número inicial de procesos de escritura en una instancia. Oracle lo calcula automáticamente basándose en el parámetro cpu_count. (Número de cpu disponibles para Oracle,  un DBWn por cada ocho CPU’s. A pesar de que normalmente con un proceso basta se pueden configurar hasta 20 procesos adicionales (DBW1 hasta  DBW9 y DBWa hasta DBWj).

DBWR funciona de una manera orientada a lotes. Va recopilando todos los buffers sucios en un lote y luego escribe todos los buffers almacenados en el lote. La escritura utiliza una entrada/salida asíncrona si es posible, si no es posible entonces  Oracle7 / Oracle8 permiten paralelizar el proceso de escritura utilizando múltiples db_writes o dbwr_io_slaves

Dar valor al DB_WRITER_PROCESSES paraleliza tanto la recopilación como la escritura de buffers. Multiples escritores debe ser más eficientes que in único DBWR con múltiples esclavos

En  8.0.4 DB_WRITER_PROCESSES y DBWR_IO_SLAVES son excluyentes uno del otro.

Si se da valor al parámetro DBWR_IO_SLAVES, solo se utilizará un proceso de escritura a pesar de lo que ponga en  DB_WRITER_PROCESSES.

Alguna versiones dejan un mensaje en el alert si no se inicializan el número indicado de DB writers

Eg: “Cannot start more dbwrs than db_block_lru_latches.”

 “Cannot start multiple dbwrs when using I/O slaves.”

Otras versiones no dejan error y sobrescriben el parámetro con un valor válido

Que hace el proceso CJQ0?

 CJQ0 es el coordinador procesos de cola de jobs.

 El proceso de cola de jobs ejecuta los jobs a medida que son asignados por el proceso CJQ.

Esto es lo que pasa:

1.  El coordinador del proceso, CJQ0, periódicamente selecciona los jobs que necesita que corran el desde la table de sistema JOB$. Los nuevos jobs incorporados se ordenen por tiempo.

2.  El proceso CJQ0 dinámicamente expande procesos esclavos de la cola de jobs (J000…J999) para ejecutar los jobs.

3.  El proceso de la cola ejecuta una vez el job que ha sido seleccionado por CJQ. El proceso ejecuta un job a la vez.

4.  Después que el proceso termina la ejecución de un job, sondea si existen más jobs, después se queda dormido, despertando en intervalos periódicos para sondear por nuevos jobs. Si el proceso no encuentra ningún job nuevo, luego se para después de un intervalo predefinido.

 El parámetro de inicialización JOB_QUEUE_PROCESSES representa el máximo número de colas de procesos jobs, que se pueden ejecutar de forma concurrente en una instancia

 Sin embargo el cliente no debe asumir que todos los procesos de cola de jons están disponibles para la ejecución de jobs.

 Nota:

El coordinador de procesos no se levanta si e parámetro de inicialización se pone a 0.

Para más información:

‘Oracle® Database Concepts 10g Release 2 (10.2)

‘Oracle® Database Administrator’s Guide10g Release 2 (10.2)’

How to control The Frequency That The Server Checks For New Scheduled Jobs

Tablas donde se guarda está información:

SQL> select JOB, LAST_DATE, NEXT_DATE, FAILURES from job$;
       JOB LAST_DATE          NEXT_DATE            FAILURES
---------- ------------------ ------------------ ----------
      4001 26-NOV-10          27-NOV-10                   0
      4002 26-NOV-10          26-NOV-10                   0
     23028                    26-NOV-10
     23027                    01-DEC-10

       JOB LAST_DATE          NEXT_DATE            FAILURES
---------- ------------------ ------------------ ----------
        22                    26-NOV-10                   4
        21                    26-NOV-10                   4
     23029                    26-NOV-10                   4
     23030                    26-NOV-10                   4
     23025                    26-NOV-10                   4

select NEXT_RUN_DATE from sys.scheduler$_job;
SQL>
NEXT_RUN_DATE
---------------------------------------------------------------------------
27-NOV-10 03.00.00.900000 AM GMT0
27-NOV-10 03.00.00.500000 AM GMT0

01-DEC-10 01.01.01.400000 AM +00:00
28-NOV-10 12.00.00.000000 AM +00:00

26-NOV-10 04.44.02.600000 PM +00:00
26-NOV-10 04.49.39.000000 PM +01:00
 

Flashback Drop y Recycle Bin

Cuando se borra una tabla, la base de datos no libera inmediatamente el espacio asociado con la tabla. La base de datos renombra la tabla y la coloca junto sus objetos asociados en el recycle bin, donde en caso de que se haya borrado por error, puede ser recuperada posteriormente. Esta opción se llama Flasback Drop y se utiliza la sentencia FLASBACK TABLE para restaurar la tabla.
El recycle bin es una tabla del diccionario de datos que contienen información sobre objetos borrados. Las tablas borradas y sus objetos asociados como índices, constrains y tablas anidadas no se borran y siguen ocupando espacio. Solo liberan el espacio si se purga la recycle bin.
Cada usuario  puede consultar sus objetos en recycle  bin utilizando el siguiente comando: 

SELECT * FROM RECYCLEBIN;

Cuando se borra un tablespace y su contenido, los objetos del tablespace no se colocan en el recycle bin, lo mismo ocurre cuando se borra un usuario,  un cluster, o un tipo todos los objetos dependientes de este último tampoco se colocan en el recycle bin. 

 Los nombre de objetos en el Recycle Bin 

 Cuando se borra una tabla esta y a todos sus objetos asociados se les otorga un nombre generado por el sistema, esto se hace para evitar los conflictos que se puedan producir cuando diferentes tablas tiene el mismo nombre, por ejemplo: Un usuario borra una tabla, la recrea con el mismo nombre y la vuelve a borrar. Dos usuarios tienen una tabla con el mismo nombre y ambos la borran El nombre generado por el sistema tendrá el siguiente formato: BIN$unique_id$version

Donde:

¦ unique_id es un identificador único de 26 caracteres.

¦ versión es un número de versión asignado por la base de datos      

Habilitar y deshabilitar la Recycle Bin      

Se realiza mediante el parámetro de inicialización recyclebin.  Cuando el parámetro  esta enable, las tablas borradas y sus objetos dependientes se colocan en el recycle bin. Cuando el recycle bin esta deshabilitado las tablas borradas y sus objetos dependientes no se colocan en el reycle bin, simplemente se borran, y se deben utilizar otros medios para recuperarlos. El recycle bin está habilitado por defecto.

 Para deshabilitarlo:                  

ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

Para habilitarlo:   

ALTER SESSION SET recyclebin = ON;
ALTER SYSTEM SET recyclebin = ON;

La habilitación y des habilitación de recycle bin con alter system o con alter session es inmediata. La des habilitación no implica que se purguen los objetos en el recycle bin.
Existen dos vistas de donde obtener información sobre los objetos en el recycle bin:
Una de los usos de estas vistas es identificar el nombre que la base de datos ha asignado a los objetos borrados, como se muestra en el siguiente ejemplo:   

SELECT object_name, original_name FROM dba_recyclebin
WHERE owner = 'HR';
OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 EMPLOYEES
Otro de los comandos que se puede utilizar
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19

Si se decide que no va restaurar los elementos que están situados en el reycle bin, puedes utilizar la sentencia purge para borrar todos los elementos y sus objetos asociados. Se necesitan los mismos privilegios que se utilizaron para borrar el objeto.
Cuando se utiliza el comando PURGE para borrar una taba, se puede utilizar el nombre que utiliza en en recycle bin o el nombre original de la tabla.
Para consultar los objetos que hay en la recycle bin: USER_RECYCLEBIN y  DBA_RECYCLEBIN
Se pueden purgar todos los objetos de un tablespace específico o de un usuario y tablespace especifico:   

PURGE TABLESPACE data;
PURGE TABLESPACE data USER scott;

Los usuarios pueden purgar sus objetos del recycle bin con el siguiente comando:   

PURGE RECYCLEBIN;

Si tienes privilegios de SYSDBA se puede purgar toda la reyclebin utilizando
DBA_RECYCLEBIN, en vez de  RECYCLEBIN en la sentencia anterior.   

Restaurando tablas del Recycle Bin   

Se utiliza  FLASHBACK TABLE … TO BEFORE DROP para recuperar tablas, se puede especificar el nombre original de la tabla o el nombre de la recycle bin. Existe la opción RENAME  TO para cambiar el nombre tabla en el momento que se recupera. El nombre del recycle bin se puede obtener de las vistas DBA_ o USER_RECYCLEBIN.
Para utilizar el comando FLASHBACK TABLE … TO BEFORE DROP se necesitan los mismos privilegios que para borrar una tabla.
Restaurar los objetos Dependientes.
Cuando se restaura una tabla del recycle bn, lo objetos dependietnes como los índices no recuperan sus nombres originales, si no que mantienes los que tenían en el recycle bin

ORA-1000 y los cursores abiertos:

OPEN CURSORS

El parámetro open cursor establece el máximo número de cursores abiertos por sesión. Si una sessión abre un número de cursores mayor a este parámetro aparecerá el ORA-1000. Los cursores abiertos ocupan espacio en la shared pool, en la library cache. El valor por defecto de OPEN_CURSOR es 50, pero Oracle recomienda que lo pongas para algunas aplicaciones hasta 500. Tom Kyte recomienda un valor cercano a 1000. Hay gente que no está de acuerdo con establecer un valor tan elevado y cree que los 50 del valor por defecto son más que suficientes, que la utilización de más 50 cursores por sesión implica que se debe revisar el código. A mi me parece un poco utópica está última postura, pero quien sabe.

SESSION CACHE CURSOR

 Es el número de cursores que se cachean en cada sesión. El valor de Session_cached_cursor puede ser mayor que el OPEN_CURSOR. Este no tiene nada que ver con el ORA-01000. Por lo tanto el parámetro OPEN_CURSORS no tiene efecto sobre el número cursos cachados. No hay relación entre ambos parámetros.
Si SESSION_CHACED_CURSOR no esta definido, el valor por defecto es 0 y ningún cursor será cacheado por sesión. (los cursores estarán cacheados en la shared pool, pero la sesión tendrá que encontrarlos). Si se define, cuando una solicitud de parseo es emitida, Oracle comprueba en la library Cache si se han realizado más de tres peticiones de parseo para esa sentencia. Si es así, Oracle mueve el cursor de sesión asociado con la sentencia al sesión cursor cache. Las siguientes solicitudes de parseo para esa sentencia en la misma sesión se obtienen del sesión cursor cache, evitando así el soft parse (Técnicamente, el parseo no puede ser completamente evitado, se hace algo todavía más suave que un soft parse, lo cual es más rápido y se evita consumo de cpu).En la session cursor cache, Oracle utiliza el algoitmo LRU, para determinar que cursores deben ser desalojados en caso de necesitar espacio.La ventaja obvia de los cachear los cursores por sesión  es reducir el tiempo de parseo. La otra ventaja es evitar los bloqueos (latches) en la shared pool y en la library cache ya que la sesión ya no va ahí a buscar las sentencias

Monitorear los cursores abiertos:

 Para empezar aclarar que la vista v$open_cursor no muestra los cursores abiertos sino los cacheados.Para buscar los cursores abiertos se debe recurrir a v$sesstat where name=’opened cursors current’.ofrece el número de cursors abiertos por sesión:   

SELECT a.VALUE, s.username, s.SID, s.serial#
  FROM v$sesstat a, v$statname b, v$session s
 WHERE a.statistic# = b.statistic#
   AND s.SID = a.SID
   AND b.NAME = 'opened cursors current'; 
 

 También es útil monitorearlos por usuario y maquina:

 --total cursors open, by username & machine       
 select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine        
 from v$sesstat a, v$statname b, v$session s         
 where a.statistic# = b.statistic#          
 and s.sid=a.sidand b.name = 'opened cursors current'         
 group by s.username, s.machineorder by 1 desc;

 Optimizando el parámetro OPEN_CURSORS:

 Lo mejor es no optimizarlo, si no abrir los suficientes para no tener que preocuparte por él. Si tus sesiones abren un número de cursores cercano al límite que has establecido para el parámetro OPEN_CURSORS súbelo.Si las das un valor elevado no significa que todas las sesiones utilicen ese número de cursores. Y si tienes un problema con una sesión en concreto, obtendrás el ORA-01000Para ver si tu parámetro OPEN_CURSORS está bien definido y para saber cual es la sesión que más cursores abre es útil está query:

select max(a.value) as highest_open_cur, p.value as max_open_cur
 from v$sesstat a, v$statname b, v$parameter p
 where a.statistic# = b.statistic#
  and b.name = 'opened cursors current'
   and p.name= 'open_cursors'
   group by p.value;
 

Después de incrementar lo cursores, revisa la vista v$ssestat para ver si el número de cursores abiertos en ese momento se incrementa para alguna de las sesiones. Si tienes alguna sesión de aplicación cuyos cursores siempre aumentan hasta alcanzar el parámetro OPEN_CURSOR, entonces seguramente hay un problema en código de tu aplicación y deja demasiados cursores abiertos.