Descubrir que Query hace mas Rollback

La manera mas rápida para descubrir la query que está generando demasiado Rollback podría ser:

1.- Buscamos las sesiones que estan generando mucho Rollback, usa la siguiente query:

col sid format 9999999

select t.*,n.NAMEfrom v$sesstat t,V$STATNAME n
where t.STATISTIC# = 5 --Donde 5 corresponde al # de estadistica de los rollbacks
and t.STATISTIC# = n.STATISTIC#
and value > 50;

2.- Luego con el SID, se busca el HASH_VALUE, que es el identificador de cada Query dentro del motor de Oracle, para esto usar la siquiente Query:

select s.SQL_HASH_VALUE, s.*
from v$session s
where sid = &SID;

3.- Una vez que encontramos el HASH_VALUE ahora debemos encontrar la query y listo, para esto usamos la siguiente Query:

select *from v$sql s
where s.HASH_VALUE = &HASH_VALUE;

ORA-01092: ORACLE instance terminated. Disconnection forced

Hace poco tuve un inconveniente algo extraño con mi base de datos Oracle 10g. El error se presentó luego de poner a mi base de datos en modo ARCHIVELOG, al pasar del estado MOUNT al OPEN saltó el siguiente error:

ORA-01092 ORACLE instance terminated. Disconnection forced


Intenté quitarle el modo ARCHIVELOG e intentar volver a subir la base, pero esto no funcionó.

Cada que vez que la intentaba subir de cualquier forma me daba el mismo error. Revisando respecto a este error solo encontré que puede suceder por una bajada no limpia de la base (por ejemplo un SHUTDOWN ABORT) y que la solución es esperar un tiempo e intentar volver a subir la base, pero inclusive re-inicié el equipo donde reside la base y esto no funcionó. Al consultar el alert_log para tener algún tipo de información adicional encontré el siguiente error:

ORA-30012: undo tablespace ‘UNDOTBS1′ does not exist or of wrong type

Lo cual claramente me indica que existe algún tipo de problema con mi tablespace de UNDO. Entonces, partiendo de esto realicé los siguientes pasos para poder levantar mi base:


1) Subir a la base en estaus NO MOUNT:

SQL> startup nomount;


2) Cambiar el parámetro UNDO_TABLESPACE para que no apunte al TBS de undo con problemas y así permitir que la base sea abierta:

SQL> alter system set UNDO_TABLESPACE=”;

3) Subir a la base de datos:

SQL> alter database mount;SQL> alter database open;


4) Con la base abierta creamos un nuevo tablespace de UNDO:

SQL> create undo tablespace UNDOTBS2 datafile ‘/home/oracle/admin/oradata/db10g/datafiles /undotbs2.dbf’ size 200M autoextend on maxsize 1024M;


5)Modificamos nuevamente al parámetro UNDO_TABLESPACE para que apunte al nuevo tablespace de UNDO (luego de esto podemos borrar al tablespace que estaba dando problemas):

SQL> alter system set UNDO_TABLESPACE=’UNDOTBS2′;SQL> drop tablespace UNDOTBS1 including contents and datafiles;

6)Bajamos limpiamente la base y la volvemos a subir:

SQL> shutdown immediate;SQL> startup;

Y listo!! Con esto la base de datos se encuentra arriba nuevamente. Luego logré ponerla a trabajar en modo ARCHIVELOG sin problemas.

Cambio de password del usuario "oc4jadmin" del iasR3.

Para modificar las claves del usuario "oc4jadmin", hay que actualizar las lineas de los archivos system-jazn-data.xml de los contedores, antes hacer una copia de los viejos por las dudas.

El archivo "system-jazn-data.xml" se encuentra en el directorio config del contenedor correspondiente ($ORACLE_HOME/j2ee/home/config/system-jazn-data.xml).

Pasos:
1- Bajar los contenedores a modificar.

2- En las lineas del usuario "oc4jadmin" del archivo system-jazn-data.xml:
{903}z0jYUSxW3jZeOyMHUHr7whDJoPni2XXu
modificarlas, por:
!oc4jadmin_password.

Donde oc4jadmin_password es la nueva clave del usuario, en la versión IasR3 debe ser la misma para todas las instancias, si se utiliza oc4jadminpara adminstrar todas las instancias.

3- Subir el contenedor modificado. Y probar loguearse para encriptar el dato en el archivo.

High Water Mark (HWM)

Generalmente, cuando se crea una tabla o un índice... se crea un segmento asociado al objeto. El segmento se crea con determinados bloques del data file, pero muy poco espacio es destinado para nuestro uso. Cuando los datos van llenando los bloques asignados al segmento, se van alocando más bloques para que usemos. A medida que los bloques van siendo alocados, la HWM se posiciona en el último bloque para mostrarnos la cantidad total de bloques alocados hasta el momento y estaban disponibles para ser usados. Un error común es pensar que a medida que eliminamos datos de los bloques, la HWM se refresca para mostrarnos que estamos utilizando menos bloques; pero la realidad es que la HWM se mantiene siempre apuntando al último bloque alocado. Sabemos que cuando realizamos un Full Scan, son leídos secuencialmente todos los bloques de la tabla hasta la HWM, por lo cual, debemos tener en claro que aunque eliminemos datos de una tabla, ese espacio "vacío" se va a seguir leyendo, lo cual trae como consecuencia la lectura de bloques innecesarios.

Veamos un ejemplo:

SQL> CREATE TABLE emp
2 (id NUMBER , sexo VARCHAR2(1 )) ;

Table created.

SQL> INSERT INTO emp
2 SELECT level , 'M'
3 FROM dual
4 CONNECT BY level <= 800000 ; 800000 rows created. SQL> INSERT INTO emp
2 SELECT level , 'F'
3 FROM dual
4 CONNECT BY level <= 200000 ; 200000 rows created. SQL> commit;

Commit complete.

SQL> ANALYZE TABLE emp COMPUTE STATISTICS ;

Table analyzed.

SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT sexo
2 FROM emp
3 WHERE id = 900000 ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 495 (10) 00:00:06
* 1 TABLE ACCESS FULL EMP 1 5 495 (10) 00:00:06
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=900000)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1655 consistent gets
0 physical reads
0 redo size
291 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

SQL>
SQL> DELETE FROM emp
2 WHERE sexo = 'M' ;

800000 rows deleted.

SQL> commit;

Commit complete.

SQL> SET AUTOTRACE TRACEONLY
SQL>
SQL> SELECT sexo
2 FROM emp
3 WHERE id = 900000 ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 495 (10) 00:00:06
* 1 TABLE ACCESS FULL EMP 1 5 495 (10) 00:00:06
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=900000)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1655 consistent gets
0 physical reads
0 redo size
291 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

Como pudimos ver, aunque hayamos eliminados datos de la tabla, seguimos leyendo la misma cantidad de bloques que antes
porque la HWM no se refrescó y sigue apuntando al último bloque alocado en el segmento.

Para solucionar éste problema, podemos realizar:

1) TRUNCATE ...
- Si la tabla esta vacia podemos hacer un TRUNCATE para resetear la HWM.
Sino, podemos hacer un export de los datos, luego truncar la tabla y realizar un import.

2) ALTER TABLE ... MOVE
- De ésta manera reorganizamos la tabla. Tener en cuenta que luego de ejecutar el ALTER, hay
que hacer un REBUILD de todos los índices de la tabla!

3) Dropear y recrear el objeto (export/import)

4) ALTER TABLE ... SHRINK SPACE SHRINK SPACE COMPACT
- Para 10g en adelante.

Apliquemos a nuestro ejemplo el punto 2...


SQL> ALTER TABLE emp MOVE ;

Table altered.

SQL> ANALYZE TABLE emp COMPUTE STATISTICS ;

Table analyzed.

SQL> SET AUTOTRACE TRACEONLY
SQL> SELECT sexo
2 FROM emp
3 WHERE id = 900000 ;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
--------------------------------------------------------------------------
0 SELECT STATEMENT 1 5 100 (9) 00:00:02
* 1 TABLE ACCESS FULL EMP 1 5 100 (9) 00:00:02
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("ID"=900000)


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
335 consistent gets
0 physical reads
0 redo size
291 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed


Como vemos, la HWM se refresco y ahora estamos leyendo sólo los bloques que contienen nuestros datos.

Reubicar y Renombrar Redo Log Members

Para cambiar el nombre o cambiar de ubicación a los redo logs, se debe tener privilegio de sistema para alterar la base de datos ALTER DATABASE. Además, se necesita privilegios de sistema operativo para copiar los archivos a la ubicación deseada y privilegios para abrir y respaldar la base de datos.

Nota: Es recomendable que antes de reubicar tus redo logs, o hacer un cambio estructural a la base de datos la respaldescompletamente, ya que se podría tener algún problema en la operación a realizar.Después de renombrar o reubicar un set de redo log files debes inmediatamente respaldar los archivos de control de la base de datos.

A continuación se describe el scenario:

1: Los redolog files de este ejemplo están localizados sobre dos discos: disk01 y disk02
2: El primer grupo de redo logs consiste de los siguientes miembros: /disk01/logs/log101.log y /disk02/logs/log102.log, y el segundo grupo consiste de los miembros /disk01/logs/log201.log y /disk02/logs/log202.log.
3: Los redolog files localizados sobre el disk01 deben ser reubicados al disk03, y deberán reflejar las siguientes rutas: /disk03/logs/log103.log y /disk03/logs/log203.log

Pasos para renombrar o reubicar los redo logs:

1: Bajar la base de datos:
SHUTDOWN

2: Copiar los redo redo log files a la nueva localización utilizando comandos de sistema operativo.

3: En el caso de Unix o Linux para mover los redo logs a una nueva localización ejecutamos el siguiente comando:
mv /disk01/logs/log101.log /disk03/logs/log103.logmv /disk01/logs/log201.log /disk03/logs/log203.log

4: Levantamos la base de datos en modo MOUNT:
STARTUP MOUNT

5: Utilizando la sentencia ALTER DATABASE con la cláusula RENAME FILE renombramos los redo logs:
ALTER DATABASERENAME FILE ‘/disk01/logs/log1a.log’, ‘/disk01/logs/log2a.log’ TO ‘/disk03/logs/log1c.log’, ‘/disk03/logs/log2c.log’;

6: Abrimos la base de datos:
ALTER DATABASE OPEN;