Últimas Actualizaciones del Evento
Recuperación de espacio en SYSAUX mediante limpieza de AWR
En ocasiones dependiendo de la actividad de una base de datos podremos encontrarnos con un tablespace SYSAUX muy grande en relación al tamaño total de la base de datos o simplemente una situación que requiera conseguir liberar espacio en el almacenamiento asignado.
Consultando el tamaño de SYSAUX se ve que este es casi 15 veces mayor que cuando se creó la base de datos:
SQL> select f.tn TABLESPACE, 2 round(usado/1048576,2) "USADO Mb", 3 round(total/1048576 ,2) "TOTAL Mb" 4 from 5 (select tablespace_name tn, sum(nvl(bytes,0)) usado from dba_segments group by tablespace_name) s, 6 (select tablespace_name tn, sum(nvl(bytes,0)) total from dba_data_files group by tablespace_name) f 7 where s.tn=f.tn 8 and f.tn='SYSAUX';
TABLESPACE USADO Mb TOTAL Mb ------------------------------ ---------- ---------- SYSAUX 13884.31 14756
En el desglose de ocupación se observa que la mayoría de esta ocupación viene dada por el componente ‘SM/AWR’:
SQL> select occupant_name, 2 space_usage_kbytes/1024 Mb 3 from v$sysaux_occupants 4 order by 2 desc;
Este espacio está dedicado a la recolección de snashots y estadísticas para generar informes AWR, ASH, ADDM … y para el propio sistema de AMM.
Por defecto esta recolección de datos está configurada por defecto para recoger un snapshot cada hora y guardarlos durante 8 dias, confirmamos mediante esta vista:
SQL> select * from dba_hist_wr_control;
DBID SNAP_INTERVAL RETENTION TOPNSQL ---------- ---------------------- ---------------------- ---------- 3860690452 +00000 01:00:00.0 +00008 00:00:00.0 DEFAULT
Dependiendo del uso y finalidad de esta base de datos podemos bajar la retención a por ejemplo 3 dias, recuperando así mas de la mitad del espacio consumido.
NOTA: A partir de 11g, previamente hemos de modificar la ‘MOVING_WINDOW’ para evitar un error ORA-13541, esta ventana es utilizada por los Adaptive Thresholds que recogen el comportamiento general para generar alertas predefinidas si el rendimiento de la instancia varia según alertas predefinidas:
SQL> select moving_window_size 2 from dba_hist_baseline 3 where baseline_type = 'MOVING_WINDOW'; MOVING_WINDOW_SIZE ------------------ 8 SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE(window_size =>3); PL/SQL procedure successfully completed. SQL> select moving_window_size 2 from dba_hist_baseline 3 where baseline_type = 'MOVING_WINDOW'; MOVING_WINDOW_SIZE ------------------ 3
Una vez redimensionada la ‘MOVING WINDOW’ ya podemos reducir la retención de los snapshots (especificada en minutos):
SQL> exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention => 4320); PL/SQL procedure successfully completed.
Podemos entonces esperar a que se limpien los que superan la nueva retención en la ejecución nocturna de las tareas de mantenimiento de MMON o eliminarlos manualmente ahora, para ello averiguamos el rango de valores concreto que descartamos:
SQL> def DIAS=3 SQL> select snap_id, 2 dbid, 3 begin_interval_time SNAPSHOT 4 from dba_hist_snapshot 5 where snap_id = (select min(snap_id) from dba_hist_snapshot) or 6 snap_id = (select max(snap_id) from dba_hist_snapshot where begin_interval_time < sysdate-&DIAS);
SNAP_ID DBID SNAPSHOT ---------- ---------- ------------------------------------ 20443 3860690452 21-AUG-16 11.00.14.541 PM 20573 3860690452 27-AUG-16 09.00.30.899 AM
Y los eliminamos:
exec dbms_workload_repository.drop_snapshot_range(20443,20573); PL/SQL procedure successfully completed.
Si queremos contener el consumo de espacio, hay que revisar tambien que el modo de recolección es ‘TYPICAL’ y no ‘ALL’:
SQL> show parameter statistics_level NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ statistics_level string TYPICAL
Si no fuese asi podemos bajar el nivel con:
SQL> alter system set statistics_level=TYPICAL;
Se podría optar también por bajar a ‘BASIC’ pero puede afectar a funcionalidades muy interesantes como la gestión automática de la memoria (AMM) o el optimizador de planes de ejecución SQL (CBO).
NOTA: Podemos ver el detalle de lo que se activa/desactiva en cada nivel en ‘v$statistics_level’.
Pero puede suceder que al comprobar de nuevo el espacio en el tablespace vemos que no se ha liberado nada. Esto puede ser debido a que la tabla particionada ‘WRH$_ACTIVE_SESSION_HISTORY’ haya quedado con particiones pertenecientes a rangos de datos antiguos que no se estén eliminando como debieran, dejando registros ‘huérfanos’ ocupando espacio. Comprobamos el tamaño de los principales segmentos de SYSAUX:
SQL> select * from ( 2 select OWNER, SEGMENT_NAME, SEGMENT_TYPE, ROUND(BYTES/1048576,2) "SIZE Mb" 3 from dba_segments 4 where tablespace_name = 'SYSAUX' 5 order by 4 desc 6 ) where ROWNUM <= 10;
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE Mb ---------- ------------------------------ ------------------ ---------- SYS WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 2939 SYS WRH$_LATCH TABLE PARTITION 638.69 SYS WRH$_EVENT_HISTOGRAM_PK INDEX PARTITION 544 SYS WRH$_EVENT_HISTOGRAM TABLE PARTITION 519.69 SYS WRH$_SQLSTAT TABLE PARTITION 460.31 SYS WRH$_SYSSTAT_PK INDEX PARTITION 448.56 SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 432 SYS WRH$_SYSSTAT TABLE PARTITION 421 SYS WRH$_PARAMETER_PK INDEX PARTITION 384 SYS WRH$_LATCH_PK INDEX PARTITION 382.06 10 rows selected.
Confirmamos la sospecha de que el origen de la ocupación son registros huérfanos:
SQL> select count(1) TOTAL from WRH$_ACTIVE_SESSION_HISTORY; TOTAL ---------- 9045188
SQL> select count(1) OBSOLETOS from WRH$_ACTIVE_SESSION_HISTORY where sample_time < sysdate - 3; OBSOLETOS ---------- 8970681
Verificamos que existe una partición que contiene los snapshots obsoletos, la propia base de datos debería eliminarla, pero no lo está haciendo, así que siguiendo el documento de Oracle 387914.1 forzamos la creación de una nueva partición con la siguiente variable, a los ocho días (o a los que tengamos establecida la retención) la partición de eliminará de manera natural:
SQL> SELECT owner, 2 segment_name, 3 partition_name, 4 segment_type, 5 bytes/1024/1024/1024 Size_GB 6 FROM dba_segments 7 WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY'; OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_GB ---------- ------------------------------ ------------------------------ ------------------ ---------- SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3860690452_0 TABLE PARTITION 2.87011719 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035
SQL> alter session set "_swrf_test_action" = 72; Session altered.
SQL> SELECT owner, 2 segment_name, 3 partition_name, 4 segment_type, 5 bytes/1024/1024/1024 Size_GB 6 FROM dba_segments 7 WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY'; OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_GB ---------- ------------------------------ ------------------------------ ------------------ ---------- SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3860690452_0 TABLE PARTITION 2.87011719 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3860690452_20674 TABLE PARTITION .000061035 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035
En cuanto se supera la retención de los snapshots (3 dias en este caso) se elimina la partición y se libera el espacio en el tablespace:
SQL> SELECT owner, 2 segment_name, 3 partition_name, 4 segment_type, 5 bytes/1024/1024/1024 Size_GB 6 FROM dba_segments 7 WHERE segment_name='WRH$_ACTIVE_SESSION_HISTORY';
OWNER SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE SIZE_GB ---------- ------------------------------ ------------------------------ ------------------ ---------- SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_3860690452_20674 TABLE PARTITION .0390625 SYS WRH$_ACTIVE_SESSION_HISTORY WRH$_ACTIVE_SES_MXDB_MXSN TABLE PARTITION .000061035
SQL> select f.tn TABLESPACE, 2 round(usado/1048576,2) "USADO Mb", 3 round(total/1048576 ,2) "TOTAL Mb" 4 from 5 (select tablespace_name tn, sum(nvl(bytes,0)) usado from dba_segments group by tablespace_name) s, 6 (select tablespace_name tn, sum(nvl(bytes,0)) total from dba_data_files group by tablespace_name) f 7 where s.tn=f.tn 8 and f.tn='SYSAUX';
TABLESPACE USADO Mb TOTAL Mb ------------------------------ ---------- ---------- SYSAUX 2744.31 14756
Si nada de esto funciona podemos optar por el método mas drástico que es eliminar y reconstruir el componente AWR al completo:
SQL> connect / as sysdba SQL> @?/rdbms/admin/catnoawr.sql SQL> @?/rdbms/admin/catawrtb.sql
Mi experiencia con el OCA 12c
Recientemente he obtenido la certificación OCA 12c (Oracle Database 12c Administrator Certified Associate), me he decidido ahora a escribir mi experiencia por si pudiese ayudar a alguien ya que en su momento no encontré mucha información de primera mano, al menos en castellano.
Si estáis pensando en sacarla ya sabréis que consta de dos exámenes, uno de SQL, a elegir entre:
1Z0-071 Oracle Database 12c SQL 1Z0-061 Oracle Database 12c: SQL Fundamentals 1Z0-051 Oracle Database 11g: SQL Fundamentals I 1Z0-047 Oracle Database SQL Expert
Y uno propio de la administración de Oracle 12c:
1Z0-062 Oracle Database 12c: Installation and Administration 1Z0-075 Oracle Database 12c Administration
Información oficial actualizada: Oracle Database 12c – Certification Path
Yo en su día, y por las circunstancias del momento, opté por hacer el «Oracle Database 11g: SQL Fundamentals I» (1Z0-051) y el «Oracle Database 12c: Installation and Administration» (1Z0-062), estos exámenes, al igual que las certificaciones, no tienen caducidad por lo que no hay un tiempo máximo entre el primero y el segundo, siempre que Oracle no decida cambiar la «Certification Path» e invalide el uso del primer examen para la certificación que queremos obtener.
Sobre los exámenes es mejor no marcarse una fecha de antemano y esperar a ver como avanza la preparación para poder elegir una fecha.
Oracle Database 11g: SQL Fundamentals I 1Z0-051
Para este examen no estudié ningún libro en concreto, es eminentemente practico por lo que lo mejor es hacerse con un Oracle rápido (tipo Express Edition) y ponerse a picar SQL. Este examen es online pero, aunque inicialmente parezca una ventaja, las preguntas y su tiempo están diseñados para no dejarte lugar a repasos ni pruebas, se pasa volando y es importante ser consciente de que si gastas mas de minuto y medio con una pregunta la marques para revisión y pruebes con la siguiente para al menos poder contestarlas todas.
Mi consejo es saberse de memoria todas las mascaras de conversión de TO_CHAR y TO_DATE, tener muy claras todas las combinaciones de funciones de cadena y de control (NVL, NVL2, NULLIF, DECODE, TRIM …) y especialmente los joins y los distintos operadores de unión.
En cuanto a la teoría, Tahití (mejor dicho: el anteriormente conocido como Tahití) será tu aliado y para la practica yo confié en el tester recomendado por Oracle: SelfTest Kaplan y no me fue mal aunque es mas bien caro.
Oracle Database 12c: Installation and Administration 1Z0-062
Este examen, aunque es mas denso y con una nota de corte mayor (95 preguntas, 2.5h, 67%), se me hizo mas llevadero que el de SQL, quizás a alguien que provenga del mundo de desarrollo le resulte al revés. Aqui si opté por un libro que recomiendo encarecidamente, no solo porque es relativamente ameno si no porque por el precio viene con un tester en DVD de 170 preguntas bastante completo.
OCA Oracle Database 12c Installation and Administration Exam Guide (Exam 1Z0-062) de John Watson editado por Oracle Press
Ademas del libro que ayuda a sentar las bases, en la fase final compré dos examinadores ademas del que venia en el DVD del libro:
oraclestudy.com
Sin duda el mas barato que he encontrado, con garantía de devolución y con mas de 400 preguntas actualizadas, pero en general de muy baja calidad y con algunos problemas como:
- Incluye mucha materia que no entra en examen como tecnología Multitenant o Database Vault, lo que hace bajar la nota de las pruebas si no estás de usarlo o no lo has estudiado.
- Algunas preguntas son sobre entornos 11g y algunas de ellas incluso incorrectas en 12c por cambios en la tecnología, por ejemplo en 12c el registro de la instancia en el listener lo lleva a cabo el nuevo proceso LREG, mientras que en anteriores versiones lo hacia el PMON, si la pregunta está anticuada te la dará por inválida aunque este correcta.
- Tras realizar el examen de prueba da una nota e indica las respuestas fallidas pero no se explica el porque del error, lo que hace que no tenga mucho valor didáctico.
- En algunas preguntas te indican marcar dos respuestas validas, pero el selector solo deja una e irremediablemente es imposible que la de por buena.
- Aunque el inglés no es mi fuerte, me da la sensación de que algunas frases tienen una composición extraña y hay bastantes faltas de ortografía/mecanografía, así como ‘imágenes’ (fotografías de papeles) de dudosa procedencia con marcas de agua borradas.
En definitiva, te devuelven el dinero si suspendes siempre que hayas realizado tests en los últimos 15 dias y al menos tres de ellos con al menos una nota de 80, quizás esto sea la explicación de algunos de los ‘problemas’ descritos para conseguir notas altas. Asi que si buscáis algo barato y con muchísimas preguntas es vuestro test, pero yo no lo escogería almenos como único método.
SelfTest Kaplan
Es el recomendado por Oracle, es sensiblemente mas caro y solo tiene 130 preguntas, aunque los detalles están mas cuidados, por ejemplo un histórico de tus notas, el entorno es casi idéntico al que te encontrarás en el examen, tras la revisión da una explicación de porque has fallado cada pregunta…
Sin duda si fuese algo mas barato o tuviese mas preguntas seria la opción ideal junto con el DVD del libro. Y si tuviera que quedarme con solo un método eligiria sin duda el libro y su DVD.
Nada mas, animaos, y espero que este resumen le haya podido ser de ayuda a alguien.
Capacidad de UNDO
Ocupación en el tablespace de UNDO a nivel de bloques usados en los datafiles, lo que no indica que esté ‘lleno’ si no que de haber muchos bloques libres el tablespace está sobredimensionado.
--
-- Tamaño de tablespace de UNDO (ocupación de bloques)
-- Javier Castro - 09/09/2016
-- https://cuandoeldbanoesta.wordpress.com
--
select f.tn TABLESPACE,
round(usado/1048576,2) "USADO (Mb)",
round(total/1048576,2) "TOTAL (Mb)",
round(usado/total*100,2) "PCT (%)"
from
(select tablespace_name tn, sum(nvl(bytes,0)) usado from dba_segments group by tablespace_name) s,
(select tablespace_name tn, sum(nvl(bytes,0)) total from dba_data_files where online_status='ONLINE' group by tablespace_name) f
where s.tn=f.tn and f.tn=(select value from v$parameter where name = 'undo_tablespace');
TABLESPACE USADO (Mb) TOTAL (Mb) PCT (%) ------------------------------ ---------- ---------- ---------- UNDOTBS1 3007.63 3072 97.9
Ocupación en el tablespace de UNDO a nivel de tipo de segmentos, pueden ocurrir errores en las transacciones DML si hay demasiados activos, o errores en operaciones de lectura si hay demasiados ‘unexpired’ y no quedan libres.
--
-- Ocupación de UNDO por tipo de extents
-- Javier Castro - 07/10/2016
-- https://cuandoeldbanoesta.wordpress.com
--
set feedback off
break on tablespace_name
select tablespace_name,
status,
blocks,
round(blocks/(select sum(blocks)
from dba_data_files
where online_status='ONLINE' and
tablespace_name=(select value from v$parameter where name = 'undo_tablespace')
group by tablespace_name)*100,2) "OCUPACION DE TS (%)"
from (select tablespace_name, status, sum(blocks) blocks
from dba_undo_extents
group by tablespace_name, status
union
select tablespace_name, 'FREE', (select sum(blocks)
from dba_data_files
where online_status='ONLINE' and
tablespace_name=(select value from v$parameter where name = 'undo_tablespace')
group by tablespace_name) - sum(blocks) blocks
from dba_undo_extents
group by tablespace_name)
order by blocks desc;
TABLESPACE_NAME STATUS BLOCKS OCUPACION DE TS (%) ------------------------------ --------- ---------- ------------------- UNDOTBS1 UNEXPIRED 566376 72,02 EXPIRED 156008 19,84 FREE 63024 8,01 ACTIVE 1024 ,13
En que momento se generan mayor cantidad de bloques de UNDO activos.
--
-- Periodo de maximo ritmo de generación de undo
-- Javier Castro - 29/09/2016
-- https://cuandoeldbanoesta.wordpress.com
--
set heading off
set feedback off
alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';
select 'Inicio periodo monitorizado ...: '||min(begin_time) from dba_hist_undostat;
select 'Final periodo monitorizado ....: '||max(end_time) from dba_hist_undostat;
set heading on
select begin_time "INICIO PERIODO", end_time "FIN PERIODO", ritmo "RITMO (Blk/s)"
from (select begin_time, end_time, undoblks/((end_time-begin_time)*(60*60*24)) ritmo from dba_hist_undostat)
where ritmo = (select max(undoblks/((end_time-begin_time)*(60*60*24))) from dba_hist_undostat);
Inicio periodo monitorizado ...: 22/09/2016 15:18:37 Final periodo monitorizado ....: 29/09/2016 15:14:15 INICIO PERIODO FIN PERIODO RITMO (Blk/s) --------------------- --------------------- ------------- 26/09/2016 21:58:37 26/09/2016 22:08:37 295,26
Principales transacciones consumidoras de UNDO
--
-- Transacciones actuales consumidoras de UNDO
-- Javier Castro - 07/10/2016
-- https://cuandoeldbanoesta.wordpress.com
--
alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';
set linesize 200
select s.username,
s.sid, s.serial#,
s.status,
t.start_date "INICIO TRANSACCION",
t.used_ublk "BLOQUES UNDO ACTIVOS",
round(t.used_ublk / ((select sum(blocks)
from dba_data_files
where online_status='ONLINE' and
tablespace_name=(select value from v$parameter where name = 'undo_tablespace')
group by tablespace_name))*100,2) "OCUPACION DE TS (%)"
from v$transaction t, v$session s
where t.ses_addr=s.saddr
order by used_ublk desc;
USERNAME SID SERIAL# STATUS INICIO TRANSACCION BLOQUES UNDO ACTIVOS OCUPACION DE TS (%) ------------ ---------- ---------- -------- --------------------- -------------------- ------------------- TRINIDAD 1146 10013 INACTIVE 14/10/2016 09:43:52 4 0 COVADONGA 1153 33721 INACTIVE 14/10/2016 13:44:42 4 0 MARIA 808 31297 ACTIVE 14/10/2016 14:20:04 4 0 JAVIER 924 2197 INACTIVE 14/10/2016 14:18:59 3 0 PABLO 241 19555 INACTIVE 14/10/2016 09:26:06 3 0 EMILIO 919 25389 INACTIVE 14/10/2016 12:10:47 3 0 ALMUDENA 235 14029 INACTIVE 14/10/2016 11:00:39 3 0 IRENE 237 58327 INACTIVE 14/10/2016 09:31:13 3 0 CARMEN 1603 17667 INACTIVE 14/10/2016 13:10:13 3 0 AUGUSTA 1503 19435 INACTIVE 14/10/2016 14:07:23 2 0 MARIA 1045 44833 INACTIVE 14/10/2016 13:45:49 2 0 CELIA 582 4101 INACTIVE 14/10/2016 10:01:08 2 0 VERONICA 1043 43537 INACTIVE 14/10/2016 13:07:39 1 0 MARIA 1372 37981 INACTIVE 14/10/2016 14:24:06 1 0 ALBERTA 929 32001 INACTIVE 14/10/2016 14:24:22 1 0 LUISA 249 8685 INACTIVE 14/10/2016 13:30:07 1 0 ISMAEL 1264 55657 INACTIVE 14/10/2016 13:57:16 1 0 JAVIER 1724 40971 INACTIVE 14/10/2016 14:26:56 1 0 MARIOM 1380 23969 INACTIVE 14/10/2016 14:10:24 1 0 BEATRIZ 1150 23261 INACTIVE 14/10/2016 13:06:48 1 0 ELENA 4 49911 INACTIVE 14/10/2016 13:50:57 1 0
Sesiones con mayor consumo de UNDO desde que se inició la instancia
--
-- Sesiones con mayor ritmo de generación de UNDO
-- Javier Castro - 14/10/2016
-- https://cuandoeldbanoesta.wordpress.com
--
alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';
set linesize 200
select s.sid,
s.serial#,
s.username,
s.program,
s.logon_time "LOGON",
i.block_changes "BLOCK CHANGES",
round(i.block_changes/((sysdate-s.logon_time)*24*60),2) "BLOCK CHANGES / min"
from v$session s, v$sess_io i
where s.sid = i.sid
order by 7 desc;
SID SERIAL# USERNAME PROGRAM LOGON BLOCK CHANGES BLOCK CHANGES / min ---------- ---------- ----------- --------------------------- --------------------- ------------- ------------------- 359 37895 ESTEBAN Toad.exe 13/10/2016 11:13:14 19805583 12032,31 815 41927 FELIX java.exe 14/10/2016 14:36:37 1688 636,98 118 55751 LUISMIGUEL java.exe 14/10/2016 09:18:47 91479 285,44 123 42203 MANUEL java.exe 14/10/2016 10:45:14 38521 164,6 1145 30233 MARISA java.exe 14/10/2016 09:27:36 45605 146,33 18 61001 BEATRIZ java.exe 14/10/2016 09:20:04 31022 97,19 1597 1 oracle@ov01sun105 (SMON) 06/09/2016 19:58:38 5053854 92,9 1503 19435 AUGUSTA java.exe 14/10/2016 13:39:17 3924 65,42 1150 23261 ROBERTO java.exe 14/10/2016 13:06:04 5238 56,2 1265 38885 PATRICIA java.exe 14/10/2016 13:46:20 2441 46,11 246 43729 ALBERTA sqlplus.exe 14/10/2016 09:27:58 13738 44,13
Tamaño optimo del tablespace de UNDO en base a la máxima actividad reciente
--
-- Tamaño optimo del tablespace de UNDO en base a la máxima actividad reciente
-- Javier Castro - 04/10/2016
-- https://cuandoeldbanoesta.wordpress.com
--
set heading off
set feedback off
set linesize 200
alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';
select 'Inicio periodo monitorizado ...: '||min(begin_time) from v$undostat;
select 'Final periodo monitorizado ....: '||max(end_time) from v$undostat;
set heading on
select round(UNDOBL_S,2) "MAX BLOQUES UNDO/s",
TIEMPO "SQL DE MAYOR DURACION (s)",
round((TAM_BL * UNDOBL_S * TIEMPO) / (1024*1024),2) "TAMAÑO OPTIMO UNDO (Mb)"
from (select (select to_number(value) from v$parameter where name = 'db_block_size') TAM_BL,
(select max(undoblks/((end_time-begin_time)*(60*60*24)))from v$undostat) UNDOBL_S,
(select max(maxquerylen) from v$undostat) TIEMPO
from dual);
Inicio periodo monitorizado ...: 01/10/2016 14:49:41 Final periodo monitorizado ....: 05/10/2016 14:46:58 MAX BLOQUES UNDO/s SQL DE MAYOR DURACION (s) TAMAÑO OPTIMO UNDO (Mb) ------------------ ------------------------- ----------------------- 82,68 24669 15934,31
Tamaño optimo del tablespace de UNDO en base a la retención configurada (parámetro UNDO_RETENTION)
--
-- Tamaño optimo del tablespace de UNDO en base a la retención configurada
-- Javier Castro - 06/10/2016
-- https://cuandoeldbanoesta.wordpress.com
--
set heading off
set feedback off
set linesize 200
alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';
select 'Inicio periodo monitorizado ...: '||min(begin_time) from v$undostat;
select 'Final periodo monitorizado ....: '||max(end_time) from v$undostat;
set heading on
select round(UNDOBL_S,2) "MAX BLOQUES UNDO/s",
UNDO_RETENTION "UNDO_RETENTION (s)",
round((TAM_BL * UNDOBL_S * UNDO_RETENTION) / (1024*1024),2) "TAMAÑO OPTIMO UNDO (Mb)"
from (select (select to_number(value) from v$parameter where name = 'db_block_size') TAM_BL,
(select max(undoblks/((end_time-begin_time)*(60*60*24)))from v$undostat) UNDOBL_S,
(select to_number(value) from v$parameter where name = 'undo_retention') UNDO_RETENTION
from dual);
Inicio periodo monitorizado ...: 02/10/2016 13:59:41 Final periodo monitorizado ....: 06/10/2016 13:54:52 MAX BLOQUES UNDO/s UNDO_RETENTION (s) TAMAÑO OPTIMO UNDO (Mb) ------------------ ------------------ ----------------------- 85,07 1500 996,93
Tiempo máximo de retención de UNDO en base a la máxima actividad reciente detectada
--
-- Tiempo máximo de retención de UNDO en base a la máxima actividad reciente
-- Javier Castro - 06/10/2016
-- https://cuandoeldbanoesta.wordpress.com
--
set heading off
set feedback off
set linesize 200
alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS';
select 'Inicio periodo monitorizado ...: '||min(begin_time) from v$undostat;
select 'Final periodo monitorizado ....: '||max(end_time) from v$undostat;
set heading on
select round(TAM_TS / (1024*1024),2) "TAMAÑO UNDO (Mb)",
round(UNDOBL_S,2) "MAX BLOQUES UNDO/s",
(select to_number(value) from v$parameter where name = 'undo_retention') "UNDO_RETENTION (s)",
round(TAM_TS / (TAM_BL * UNDOBL_S), 0) "MAXIMA RETENCION DE UNDO (s)"
from (select (select sum(nvl(bytes,0)) from dba_data_files
where online_status='ONLINE' and
tablespace_name=(select value from v$parameter where name = 'undo_tablespace')
group by tablespace_name) TAM_TS,
(select to_number(value) from v$parameter where name = 'db_block_size') TAM_BL,
(select max(undoblks/((end_time-begin_time)*(60*60*24)))from v$undostat) UNDOBL_S
from dual);
Inicio periodo monitorizado ...: 02/10/2016 13:39:41 Final periodo monitorizado ....: 06/10/2016 13:31:50 TAMAÑO UNDO (Mb) MAX BLOQUES UNDO/s UNDO_RETENTION (s) MAXIMA RETENCION DE UNDO (s) ---------------- ------------------ ------------------ ---------------------------- 8192 85,07 1500 12326
Oracle 12cR2 saliendo del horno, principales novedades
Tras la salida de Oracle 12c en Julio de 2014 llega ahora su segunda Release (12.2) por el momento solo disponible en Oracle Cloud. Viene cargada de novedades, aquí expongo un resumen de las mas interesantes:
- Particionado de tablas usando un único comando de ALTER TABLE.
- Oracle «In-Memory», este nuevo sistema para combinar una base de datos transaccional (OLTP) con una analítica (DWH) ya está disponible desde 12cR1, pero ahora cuenta con mejoras como poder acceder desde una instancia standby, redimensionamiento dinámico de la memoria (solo para ampliar) o el volcado de esta en un segmento LOB para tener disponible dicha memoria en un reinicio de instancia y evitar tener que recalcularla (Fast Start).
- En cuanto a seguridad se añade el cifrado para los tablespaces de sistema además de poder cifrar en caliente datafiles ya existentes.
- En Database Vault se incorpora la posibilidad de realizar una simulación para observar y confirmar el correcto funcionamiento de las aplicaciones antes de aplicarlo de manera real. En este modo no se denegará el acceso a los datos, pero si se produce un acceso indebido quedará registrado para su estudio y resolución.
- Una muy interesante: se amplia el limite de los identificadores de objeto y columna de los históricos 30 bytes a 128 bytes.
- Muchos cambios en la estrella de Oracle 12c: el modo Multitenant, continua existiendo el modo clásico ‘Non-PDB’ pero no se dará mas soporte, la idea es eliminarlo completamente en la siguiente versión y remplazarlo por una CBD (Container Database) con una única PDB (Pluggable Database) sin coste adicional, aunque si se quieren incorporar más PDBs será necesario un licenciamiento aparte. Otros cambios son la gestión de recursos de las PDBs pudiendo limitar el número máximo de IOPs por cada una, clonado en caliente de PDBs, posibilidad de que cada PDB tenga su propio UNDO o realizar flashback de un PDB sin afectar a las demás.
- Motorización mas completa sobre estadísticas de uso e idoneidad de indices.
- Refresco de vistas materializadas en tiempo real.
- Particionado para tablas externas (usando varios ficheros).
Hay muchas mas novedades, quedamos a la espera de que se publique para descarga la versión instalable y poder jugar un poco.
Fuente y mas info: amitzil.wordpress.com
Limpiar juegos de backup RMAN de una base de datos ya eliminada
En ocasiones, al dar de baja una base de datos puede suceder que se elimine sin haber limpiado previamente sus juegos de backup de RMAN. Esto puede ser útil para una recuperación posterior, pero si la intención es una eliminación definitiva nos encontramos con que ya no disponemos de su instancia para administrarlos, o peor, podrían quedar estos juegos de backup fuera de la vista ocupando almacenamiento ‘huérfano’ de una manera no controlada al pasar a estado OBSOLETE pero sin eliminarse definitivamente.
En cualquier caso es una buena practica revisar que juegos de backup están almacenados en RMAN con una fecha superior a la retención estándar definida para comprobar si son necesarios, si pertenecen a antiguas bases de datos o encarnaciones obsoletas, o si están fallando las medidas definidas de retención y limpieza.
Para ello nos podemos valer de una consulta como la siguiente lanzada directamente contra la instancia del catálogo de RMAN, en este caso particular busco juegos de backup con mas de dos meses de antigüedad (con la variable OLDER_THAN) y reviso no que no se traten de juegos de backup con retención especial mediante la columna de KEEP_UNTIL:
SQL> DEF OLDER_THAN=62 SQL> set pagesize 100 SQL> set linesize 200 SQL> col type format a6 SQL> col completion_time format a18 SQL> col status format a18 SQL> col keep_until format a18 SQL> col device_type format a14 SQL> col tag format a30 SQL> col gigabytes format 9999.99 SQL> break on name on db_id skip page on report SQL> compute sum label TOTAL of GIGABYTES on report SQL> SELECT 2 D.NAME, 3 BS.DB_ID, 4 DECODE(BS.BACKUP_TYPE,'D','Full','I','Inc','L','Arch') TYPE, 5 TO_CHAR(BS.COMPLETION_TIME,'DD/MM/YYYY HH24:MI') COMPLETION_TIME, 6 DECODE(BS.STATUS,'A','Available','D','BckPieces deleted','O','Other') STATUS, 7 TO_CHAR(BS.KEEP_UNTIL,'DD/MM/YYYY HH24:MI') KEEP_UNTIL, 8 BP.DEVICE_TYPE, 9 BP.TAG, 10 ROUND(NVL(BP.BYTES,0)/1073741824,2) GIGABYTES 11 FROM RC_BACKUP_SET BS, RC_BACKUP_PIECE_DETAILS BP, RC_DATABASE D 12 WHERE BS.DB_ID = D.DBID 13 AND BS.BS_KEY = BP.BS_KEY (+) 14 AND BS.COMPLETION_TIME < SYSDATE - &OLDER_THAN 15 ORDER BY NAME, BS.COMPLETION_TIME;
NAME DB_ID TYPE COMPLETION_TIME STATUS KEEP_UNTIL DEVICE_TYPE TAG GIGABYTES -------- ---------- ------ ------------------ ------------------ ------------------ -------------- ------------------------------ --------- INTE046 855721479 Full 19/03/2016 02:20 Available SBT_TAPE TAG20160319T022051 .01 Full 19/03/2016 02:20 Available SBT_TAPE COLD_BCK 4.64 Full 02/04/2016 09:00 Available SBT_TAPE COLD_BCK 4.65 Full 02/04/2016 09:01 Available SBT_TAPE TAG20160402T090037 .01 Full 06/04/2016 07:17 Available SBT_TAPE TAG20160406T071741 .01 Full 06/04/2016 07:17 Available SBT_TAPE COLD_BCK 4.66 Full 07/04/2016 06:33 Available SBT_TAPE TAG20160407T063353 .01 Full 07/04/2016 06:33 Available SBT_TAPE COLD_BCK 4.66 Full 08/04/2016 02:41 Available SBT_TAPE COLD_BCK 4.66 Full 08/04/2016 02:41 Available SBT_TAPE TAG20160408T024153 .01 Full 09/04/2016 11:21 Available SBT_TAPE COLD_BCK 4.66 Full 09/04/2016 11:22 Available SBT_TAPE TAG20160409T112202 .01 Full 11/04/2016 02:28 Available SBT_TAPE TAG20160411T022851 .01 Full 11/04/2016 02:28 Available SBT_TAPE COLD_BCK 4.66 Full 12/04/2016 07:03 Available SBT_TAPE COLD_BCK 4.66 Full 12/04/2016 07:04 Available SBT_TAPE TAG20160412T070407 .01 Full 16/04/2016 07:53 Available SBT_TAPE COLD_BCK .17 Full 16/04/2016 07:54 Available SBT_TAPE COLD_BCK .79 Full 16/04/2016 07:54 Available SBT_TAPE COLD_BCK 1.56 Full 16/04/2016 07:55 Available SBT_TAPE TAG20160416T075528 .01 Full 16/04/2016 07:55 Available SBT_TAPE COLD_BCK 2.13 Full 24/04/2016 06:17 Available SBT_TAPE COLD_BCK .79 Full 24/04/2016 06:17 Available SBT_TAPE COLD_BCK .17 Full 24/04/2016 06:18 Available SBT_TAPE COLD_BCK 2.13 Full 24/04/2016 06:18 Available SBT_TAPE COLD_BCK 1.56 Full 24/04/2016 06:18 Available SBT_TAPE TAG20160424T061822 .01 Full 01/05/2016 07:53 Available SBT_TAPE COLD_BCK .17 Full 01/05/2016 07:54 Available SBT_TAPE COLD_BCK .80 Full 01/05/2016 07:55 Available SBT_TAPE COLD_BCK 1.57 Full 01/05/2016 07:55 Available SBT_TAPE TAG20160501T075557 .01 Full 01/05/2016 07:55 Available SBT_TAPE COLD_BCK 2.13 NAME DB_ID TYPE COMPLETION_TIME STATUS KEEP_UNTIL DEVICE_TYPE TAG GIGABYTES -------- ---------- ------ ------------------ ------------------ ------------------ -------------- ------------------------------ --------- TST014 1881841775 Full 12/12/2015 02:05 Available SBT_TAPE COLD_BCK 2.81 Full 12/12/2015 02:05 Available SBT_TAPE TAG20151212T020508 .01 Full 02/01/2016 02:06 Available SBT_TAPE TAG20160102T020632 .01 Full 02/01/2016 02:06 Available SBT_TAPE COLD_BCK 2.83 Full 09/01/2016 02:26 Available SBT_TAPE COLD_BCK 2.83 Full 09/01/2016 02:27 Available SBT_TAPE TAG20160109T022701 .01 Full 16/01/2016 02:07 Available SBT_TAPE TAG20160116T020727 .01 Full 16/01/2016 02:07 Available SBT_TAPE COLD_BCK 2.84 NAME DB_ID TYPE COMPLETION_TIME STATUS KEEP_UNTIL DEVICE_TYPE TAG GIGABYTES -------- ---------- ------ ------------------ ------------------ ------------------ -------------- ------------------------------ --------- ******** ********** --------- TOTAL 62.68 41 rows selected.
Se observan aquí diversos juegos de backup de dos bases de datos que están consumiendo algo mas de 60Gb en el gestor de backups. El problema es que para eliminar estos juegos y dar de baja definitiva la base de datos ya no disponemos de las instancias originales para poder abrir un cliente de RMAN desde ellas.
Por tanto para solucionar esto levantaremos una instancia ‘falsa’ para poder conectar desde ella al catalogo de RMAN con su identificador de base de datos.
Para levantar una instancia Oracle en modo NO-MOUNT solo es necesario un ‘pfile’ con el único parámetro obligatorio (DB_NAME) de todas formas también puede ser interesante limitar la memoria ya que esta es variable en función de nuestro sistema.
Por tanto, desde el sistema operativo creamos un pfile e iniciamos la instancia sin montarla:
$ vi $ORACLE_HOME/dbs/initDUMMY.ora $ cat $ORACLE_HOME/dbs/initDUMMY.ora db_name='DUMMY' memory_target=256M $ export ORACLE_SID=DUMMY $ sqlplus / as sysdba Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 267304960 bytes Fixed Size 2158456 bytes Variable Size 125833352 bytes Database Buffers 134217728 bytes Redo Buffers 5095424 bytes SQL> quit
Ya tememos una instancia Oracle desde la cual conectar a RMAN y emular la base de datos con la que queremos operar:
$ export NLS_DATE_FORMAT="dd-MM-YYYY hh24:mi:ss" $ rman target=/ Recovery Manager: Release 11.2.0.3.0 - Production on Mon Sep 5 14:43:56 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: DUMMY (not mounted) RMAN> connect catalog rmansch/******@RMAN connected to recovery catalog database
Establecemos el DB_ID obtenido previamente en la consulta sobre la base de datos del catálogo y veremos los juegos de backup:
RMAN> set dbid 855721479; executing command: SET DBID database name is "INTE046" and DBID is 855721479 RMAN> list backup summary; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- ------------------- ------- ------- ---------- --- 3524544 B F A SBT_TAPE 19-03-2016 02:20:21 1 1 NO COLD_BCK 3524559 B F A SBT_TAPE 19-03-2016 02:20:27 1 1 NO TAG20160319T022051 3653399 B F A SBT_TAPE 02-04-2016 09:00:27 1 1 NO COLD_BCK 3653414 B F A SBT_TAPE 02-04-2016 09:01:25 1 1 NO TAG20160402T090037 3683228 B F A SBT_TAPE 06-04-2016 07:17:30 1 1 NO COLD_BCK 3683243 B F A SBT_TAPE 06-04-2016 07:17:58 1 1 NO TAG20160406T071741 3700763 B F A SBT_TAPE 07-04-2016 06:33:39 1 1 NO COLD_BCK 3700778 B F A SBT_TAPE 07-04-2016 06:33:07 1 1 NO TAG20160407T063353 3712198 B F A SBT_TAPE 08-04-2016 02:41:07 1 1 NO COLD_BCK 3712213 B F A SBT_TAPE 08-04-2016 02:41:12 1 1 NO TAG20160408T024153 3740391 B F A SBT_TAPE 09-04-2016 11:21:59 1 1 NO COLD_BCK 3740406 B F A SBT_TAPE 09-04-2016 11:22:11 1 1 NO TAG20160409T112202 3744317 B F A SBT_TAPE 11-04-2016 02:28:38 1 1 NO COLD_BCK 3744332 B F A SBT_TAPE 11-04-2016 02:28:31 1 1 NO TAG20160411T022851 3754186 B F A SBT_TAPE 12-04-2016 07:03:43 1 1 NO COLD_BCK 3754201 B F A SBT_TAPE 12-04-2016 07:04:31 1 1 NO TAG20160412T070407 3810153 B F A SBT_TAPE 16-04-2016 07:53:44 1 1 NO COLD_BCK 3810154 B F A SBT_TAPE 16-04-2016 07:54:45 1 1 NO COLD_BCK 3810155 B F A SBT_TAPE 16-04-2016 07:54:18 1 1 NO COLD_BCK 3810156 B F A SBT_TAPE 16-04-2016 07:55:28 1 1 NO COLD_BCK 3810174 B F A SBT_TAPE 16-04-2016 07:55:39 1 1 NO TAG20160416T075528 3905900 B F A SBT_TAPE 24-04-2016 06:17:17 1 1 NO COLD_BCK 3905901 B F A SBT_TAPE 24-04-2016 06:17:55 1 1 NO COLD_BCK 3905902 B F A SBT_TAPE 24-04-2016 06:18:38 1 1 NO COLD_BCK 3905903 B F A SBT_TAPE 24-04-2016 06:18:45 1 1 NO COLD_BCK 3905921 B F A SBT_TAPE 24-04-2016 06:18:58 1 1 NO TAG20160424T061822 3950137 B F A SBT_TAPE 01-05-2016 07:53:00 1 1 NO COLD_BCK 3950138 B F A SBT_TAPE 01-05-2016 07:54:38 1 1 NO COLD_BCK 3950139 B F A SBT_TAPE 01-05-2016 07:55:58 1 1 NO COLD_BCK 3950140 B F A SBT_TAPE 01-05-2016 07:55:04 1 1 NO COLD_BCK 3950158 B F A SBT_TAPE 01-05-2016 07:55:59 1 1 NO TAG20160501T075557
Tras confirmar que son los que esperamos procedemos a su eliminación:
RMAN> run {
2> allocate channel sbt type 'SBT_TAPE';
3> delete backup;
4> }
allocated channel: sbt
channel sbt: SID=86 device type=SBT_TAPE
channel sbt: Data Protection for Oracle: version 6.3.0.0
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
3524547 3524544 1 1 AVAILABLE SBT_TAPE INTE046_6cr0r7vd_1_1_906862573
3524567 3524559 1 1 AVAILABLE SBT_TAPE c-855721479-20160319-00
3653402 3653399 1 1 AVAILABLE SBT_TAPE INTE046_6er20sri_1_1_908096370
3653422 3653414 1 1 AVAILABLE SBT_TAPE c-855721479-20160402-00
3683231 3683228 1 1 AVAILABLE SBT_TAPE INTE046_6gr2b89u_1_1_908435774
3683251 3683243 1 1 AVAILABLE SBT_TAPE c-855721479-20160406-00
3700766 3700763 1 1 AVAILABLE SBT_TAPE INTE046_6ir2dq2s_1_1_908519516
3700786 3700778 1 1 AVAILABLE SBT_TAPE c-855721479-20160407-00
3712201 3712198 1 1 AVAILABLE SBT_TAPE INTE046_6kr2g0p1_1_1_908591905
3712221 3712213 1 1 AVAILABLE SBT_TAPE c-855721479-20160408-00
3740394 3740391 1 1 AVAILABLE SBT_TAPE INTE046_6mr2jjmr_1_1_908709595
3740414 3740406 1 1 AVAILABLE SBT_TAPE c-855721479-20160409-00
3744320 3744317 1 1 AVAILABLE SBT_TAPE INTE046_6or2nt8b_1_1_908850443
3744340 3744332 1 1 AVAILABLE SBT_TAPE c-855721479-20160411-00
3754189 3754186 1 1 AVAILABLE SBT_TAPE INTE046_6qr2r1od_1_1_908953357
3754209 3754201 1 1 AVAILABLE SBT_TAPE c-855721479-20160412-00
3810159 3810153 1 1 AVAILABLE SBT_TAPE INTE046_6vr35m6s_1_1_909301980
3810160 3810154 1 1 AVAILABLE SBT_TAPE INTE046_6ur35m6s_1_1_909301980
3810161 3810155 1 1 AVAILABLE SBT_TAPE INTE046_6tr35m6s_1_1_909301980
3810162 3810156 1 1 AVAILABLE SBT_TAPE INTE046_6sr35m6r_1_1_909301979
3810176 3810174 1 1 AVAILABLE SBT_TAPE c-855721479-20160416-00
3905906 3905900 1 1 AVAILABLE SBT_TAPE INTE046_74r3qjjl_1_1_909987445
3905907 3905901 1 1 AVAILABLE SBT_TAPE INTE046_73r3qjjl_1_1_909987445
3905908 3905902 1 1 AVAILABLE SBT_TAPE INTE046_72r3qjjk_1_1_909987444
3905909 3905903 1 1 AVAILABLE SBT_TAPE INTE046_71r3qjjk_1_1_909987444
3905923 3905921 1 1 AVAILABLE SBT_TAPE c-855721479-20160424-00
3950143 3950137 1 1 AVAILABLE SBT_TAPE INTE046_79r4fs7q_1_1_910684410
3950144 3950138 1 1 AVAILABLE SBT_TAPE INTE046_78r4fs7q_1_1_910684410
3950145 3950139 1 1 AVAILABLE SBT_TAPE INTE046_77r4fs7q_1_1_910684410
3950146 3950140 1 1 AVAILABLE SBT_TAPE INTE046_76r4fs7p_1_1_910684409
3950160 3950158 1 1 AVAILABLE SBT_TAPE c-855721479-20160501-00
Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=INTE046_6cr0r7vd_1_1_906862573 RECID=203 STAMP=906862574
deleted backup piece
backup piece handle=c-855721479-20160319-00 RECID=204 STAMP=906862851
[...]
Deleted 31 objects
released channel: sbt
En este momento ya se puede ‘desregistrar’ definitivamente la base de datos:
RMAN> unregister database; database name is "INTE046" and DBID is 855721479 Do you really want to unregister the database (enter YES or NO)? y database unregistered from the recovery catalog RMAN> quit Recovery Manager complete.
Limpiamos el entorno deteniendo y eliminando la instancia dummy:
SQL> shu abort ORACLE instance shut down. SQL> quit $ rm $ORACLE_HOME/dbs/initDUMMY.ora
Una vuelta de tuerca mas
En el caso de usar IBM Tivoli Storage Manager, puede suceder que se generen discrepancias entre el catálogo de RMAN y los backupset realmente almacenados en cinta, produciéndose backups huérfanos si RMAN elimina un backup y TSM por algún error no llega a eliminarlo. Para revisar esto IBM provee la herramienta ‘tdposync’ para detectar y poder eliminar estos juegos huérfanos: IBM Knowledge Center > Programa de utilidad tdposync
Cuando el DBA no está los datafiles bailan 