Oracle Error Troubleshooting (11g r2 tested) – part 04

/, Oracle, Oracle Troubleshooting, Uncategorized/Oracle Error Troubleshooting (11g r2 tested) – part 04

Oracle Error Troubleshooting (11g r2 tested) – part 04

Continuamos con la seria de posts sobre: Oracle  Error Troubleshooting, y como solucionarlos:

01 – https://itegram.com/2017/01/16/oracle-troubleshooting-11g-r2-tested-part-01/

02 – https://itegram.com/2017/01/30/oracle-troubleshooting-11g-r2-tested-part-02/

03 – https://itegram.com/2017/01/30/oracle-troubleshooting-11g-r2-tested-part-03/

05 – http://itegram.com/2017/04/27/oracle-error-troubleshooting-11g-r2-tested-part-05/

06 – http://itegram.com/2017/04/29/oracle-error-troubleshooting-11g-r2-tested-part-06/

07 – http://itegram.com/2017/04/29/oracle-error-troubleshooting-11g-r2-tested-part-07/

08 – http://itegram.com/2017/04/29/oracle-error-troubleshooting-11g-r2-tested-part-08/

Y links a diversos posts sobre Oracle:

https://itegram.wordpress.com/2017/01/13/indice-posts-sobre-oracle/

Errores encontrados en este documento

ORA-00060: Deadlock detected

ORA-1652: unable to extend temp segment by 128 in tablespace

ORA-1653: unable to extend table SYS.AUD$ by 128 in  tablespace SYSTEM

ORA-00060: Deadlock detected

ERROR:

ORA-00060: Deadlock detected. More info in file /o/app/oracle/diag/rdbms/uaacldb/UAACLDB/trace/UAACLDB_ora_7930.trc.

SOLUCION

En resumen: Nada de que preocuparse, si pasa muy seguido analizar quien lo genera y corregirlo. Si pasa una vez, dejarlo.

Reejecutar la query que dio error en el cli.


http://www.dataprix.com/blogs/il-masacratore/ora10g-ora-00060-deadlock-detected#frameId=widget_bem&height=815

“De vez en cuando puede pasar que dos sesiones que se pisen se bloqueen al intentar hacer cambios en los mismos datos (a nivel de registro o a nivel de tabla). En sistemas no concurrentes y/o bien diseñados no tiene por que pasar ya que las aplicaciones suelen estar mínimamente pensadas para evitarlo; o en todo caso en pruebas pre-producción ya se detecta y se corrige. El caso es que incluso aunque se planee evitarlos se pueden producir. En la mayoría de casos se resuelven solitos al acabar de realizar los cambios la sesión bloqueante, incluso ni nos daremos cuenta. En otros casos más infrecuentes se producen bloqueos circulares irresolubles, “deadlocks”, donde se acaba haciendo rollback de una transacción y se genera una entrada en el fichero de alerta:

ORA-00060: Deadlock detected. More info in file /opt/oracle/admin/XXX/udump/XXX_ora_28205.trc

Si consultamos el fichero de traza encontraremos información más detallada: sesiones involucradas, objeto, registro, consulta que lo provoca, etc… Aquí incluyo algunas partes de un fichero de ejemplo. En la primera parte del fichero vemos que sesión sufrirá el rollback(marcada en negrita) y más abajo vemos el bloqueo circular (donde la 1706 espera a la 1693 y viceversa):


*** ACTION NAME:() 2011-01-27 08:07:36.110
*** MODULE NAME:(Servicio.exe) 2011-01-27 08:07:36.110
*** SERVICE NAME:(SYS$USERS) 2011-01-27 08:07:36.110
*** SESSION ID:(1693.30703) 2011-01-27 08:07:36.110
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                      ———Blocker(s)——–  ———Waiter(s)———
Resource Name          process session holds waits  process session holds waits
TX-00090022-00173f07        82    1693     X             21    1706           X
TX-00030014-0013a2dd        21    1706     X             82    1693           X
Rows waited on:
Session 1706: obj – rowid = 0000CF1C – AAAM8cAA5AAACX+AAF
 (dictionary objn – 53020, file – 57, block – 9726, slot – 5)
Session 1693: obj – rowid = 0000CF1C – AAAM8cAA5AAACX+AAI
 (dictionary objn – 53020, file – 57, block – 9726, slot – 8)

Con esta información también podemos saber exactamente que objetos y registro/s es el origen de la disputa. Si es algo que se repite de forma cíclica podemos ayudar al responsable de la aplicación entregándole más datos (además de las consultas) para que lo resuelva. Para saber que registro de que tabla:

  1. Convertimos a decimal el obj que se indica al final en hex (0000CF1C->53020)
  2. Obtenemos el nombre del objeto del diccionario de datos:
  3. SELECT owner, object_name, object_type FROM dba_objects WHERE object_id = 53020;
  4. Consultamos la tabla obtenida buscando el registro por el rowid:
  5. SELECT * FROM tabla WHERE rowid=’AAAM8cAA5AAACX+AAF’’

Un poco más abajo del fichero también se puede ver que consultas han provocado el deadlock y otra información como la cantidad de “waits” de la session en cuestión etc…

Todo esto me hace gracia comentarlo porque a veces desarrolladores mal acostumbrados nos piden que comprobemos si existen bloqueos entre usuarios porque tienen algo que no va tan rápido como siempre. En contraposición también tenemos a usuarios muy impacientes, que cierran a saco sus aplicaciones… Si a ti te pillan en horario, te vienen a preguntar, lo miras sin renegar, miras si existe y si la sessión bloqueante sigue trabajando… Si es el caso y hay que esperar, ¿que le dices? La primera vez le explicas de que va el tema, la segunda vez se lo recuerdas y la tercera que se vaya a tomar un café… “

 

 

ORA-1652: unable to extend temp segment by 128 in tablespace

Probablemente sea alguna query que la está llenando.

 

Main goal of PGA_AGGREGATE_TARGET would be to minimize physical I/O to your temporary tablespaces. You can change the PGA_AGGREGATE_TARGET dynamically with an alter system set command.

 

http://ora10gadmin.blogspot.com.ar/2012/09/ora-1652-unable-to-extend-temp-segment.html


TOP SESSIONS QUE USAN TEMP


https://alexzeng.wordpress.com/2012/06/13/how-to-find-the-sql-that-using-lots-of-temp-tablespace-in-oracle/

 

CHECK FREE SPACE

 

check the sessions that use temp tablespace:

 

** Ver la query que falta en el link de aca arriba.

 


 

http://www.dbspecialists.com/files/presentations/temp_space.html

SET EVENTS

Unfortunately, Oracle does not identify the text of the SQL statement that failed. Thus we are informed that a problem has occurred but we are not given tools with which to identify the cause of the problem nor measure the user impact of the statement failure.

However, Oracle does have a diagnostic event mechanism that can be used to give us more information whenever an ORA-1652 error occurs by causing Oracle server processes to write to a trace file. This trace file will contain a wealth of information, including the exact text of the SQL statement that was being processed at the time that the ORA-1652 error occurred. This diagnostic event imposes very little overhead on the system, because Oracle only writes information to the trace file when an ORA-1652 error occurs.

You can set a diagnostic event for the ORA-1652 error in your individual database session with the following statement:

You can set the diagnostic event instance-wide with the following statement:

The above statement will affect the current instance only and will not edit the server parameter file. That is to say, if you stop and restart the instance, the diagnostic event setting will no longer be active. I don’t recommend setting this diagnostic event on a permanent basis, but if you want to edit your server parameter file, you could use a statement like the following:

You can also set diagnostic events in another session (without affecting all sessions instance-wide) by using the “oradebug event” command in SQL*Plus.

You can deactivate the ORA-1652 diagnostic event or remove all diagnostic event settings from the server parameter file with statements such as the following:

If a SQL statement fails due to lack of space in the temporary tablespace and the ORA-1652 diagnostic event has been activated, then the Oracle server process that encountered the error will write a trace file to the directory specified by the user_dump_dest instance parameter. The entry in the instance alert log that indicates an ORA-1652 error occurred will also indicate that a trace file was written. An entry in the instance alert log will look like this:

Tue Jan  2 17:21:14 2007
Errors in file /u01/app/oracle/admin/rpkprod/udump/rpkprod_ora_10847.trc:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

 

ORA-1653: unable to extend table SYS.AUD$ by 128 in  tablespace SYSTEM

Se lleno la tabla system por culpa de SYS.AUD$

En el alert log dela base vemos lo siguiente:

Immediate Kill Session#: 2803, Serial#: 44801
Immediate Kill Session: sess: 0x55ecbf3a0  OS pid: 11712
Mon Nov 30 16:37:13 2015
opiodr aborting process unknown ospid (18541) as a result of ORA-28
Mon Nov 30 16:37:49 2015
opiodr aborting process unknown ospid (28863) as a result of ORA-28
ORA-1653: unable to extend table SYS.AUD$ by 128 in                 tablespace SYSTEM
ORA-1653: unable to extend table SYS.AUD$ by 8192 in                 tablespace SYSTEM

 

When trying to connect to database , below SQL errors are generated.

ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM
ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYST

SOLUCIONES POSIBLES

Soluciones inmediatas para reactivar la base

  1. Deshabilitar, y truncar la tabla (si no necesitamos los registros)
  2. Borrar registros de determinadas fechas

SOLUCIONES A LARGO PLAZO

  1. Tareas de housekeeping para mantener la tabla y que no se agrande.
  2.  También para que no suceda nuevamente, tenemos la posibilidad de enviar la auditoria no a tablas, sino a archivos. Entonces a nivel de sistema operativo lo podemos mantener de forma más fácil.
    Podemos enviarlo a xml, que podemos leer haciendo query como si fuesen tablas.

La 2 es la que para mi es la mejor.

COMO HACER ESTAS COSAS

 

DESHABILITAR AUDIT

https://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_auditing.htm#TDPSG50526

http://www.shaiksameer.com/2011/07/ora-1653-unable-to-extend-table-sysaud.html

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams017.htm#REFRN10006

 

 

PURGUE

http://www.oradba.ch/2011/05/database-audit-and-audit-trail-purging/

https://community.oracle.com/thread/2170439

TRUNCATE

http://shareolite.blogspot.com.ar/2014/08/ora-01653-unable-to-extend-table-sysaud.html

truncate table SYS.AUD$;

 

SOLVE ISSUE

http://www.dbasupport.com/forums/showthread.php?56682-ORA-1653-unable-to-extend-table-SYS-AUD-by-3596-in-tablespace-SYSTEM

 

Audit viene por defecto activado, pero no se purga:

SOLUCION RAPIDA, BORRAR REGISTROS

 

 

 

SOLUCION A LARGO PLAZO : PROCEDURE

–CONFIG DESDE CERO (ojo q si la tenemos llena a la tabla, init_cleanup la mueve y te puede romper todo):

http://www.dbarj.com.br/en/2013/05/changing-audit-tablespace-create-purge-job-11g/

First create a PL/SQL procedure that will accept a number of days to keep. It rounds down to the beginning of the day. I like to dump a few diagnostic messages to alert.log when my maintenance procedures are running, so I included that here as well. (There was a typo in purge data calculation — thanks to Nial for catching it.

During the job run you will see the following in the alert.log:

Tue Jul 01 15:18:02 2008
AUDIT: Purging Audit Trail until 30-JUN-08 started
AUDIT: Purging Audit Trail until 30-JUN-08 has completed

The good news, in a nutshell — Oracle 11g includes default audit settings that should be enabled if you have even a tiny thought about security in your environment. But don’t stop there. Make sure that you implement some purging strategy to avoid unlimited growth of the audit trail.

SOLUCION A LARGO PLAZO: CAMBIAR TIPO DE AUDIT

Se puede hacer el audit a un archivo xml si no se quiere desactivar. https://dbatrain.wordpress.com/2009/05/14/is-your-audit-trailing-behind/

 

 

By |2017-04-30T00:27:23+00:00marzo 10th, 2017|Categories: DB, Oracle, Oracle Troubleshooting, Uncategorized|Tags: , |5 Comments

About the Author:

5 Comments

  1. […] 04 –  http://itegram.com/2017/03/10/oracle-troubleshooting-11g-r2-tested-part-04/ […]

  2. […] Oracle Error Troubleshooting (11g r2 tested) – part 04 […]

  3. francisco abril 24, 2017 at 1:38 am - Reply

    Muy buenos los posts!, Me gusta que estén varios juntos y que des los links al resto, asi se puede buscar facil.

    Saludos!

Leave A Comment