lunes, 23 de agosto de 2010

Script - Monitorización de conexiones basada en triggers

Script de creación de tabla y triggers para el registro de los logons y logoff en la base de datos, permitiendo la monitorización de las conexiones contra la base de datos.

(bajo esquema SYS)

create table
   stats$user_log
(
   user_id           varchar2(100),
   session_id           number,
   host              varchar2(1000),
   logon_day                 date,
   logon_time        varchar2(100),
   logoff_day                date,
   logoff_time       varchar2(10)
) tablespace USERS
;
 

create or replace trigger
   logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null
);
END;
/

create or replace trigger
   logoff_audit_trigger
before LOGOff ON DATABASE
BEGIN
insert into stats$user_log values(
   user,
   sys_context('USERENV','SESSIONID'),
   sys_context('USERENV','HOST'),
   null,
   null,
   sysdate,
   to_char(sysdate, 'hh24:mi:ss')
);
END;
/

Consulta para analizar las conexiones contra la base de datos entre dos fechas:

Ejemplo de llamada:

@consulta_audit_conex.sql '01/01/2010 00:00:00' '23/08/2010 23:59:59'

COLUMN HOST FORMAT A45
COLUMN USER_ID FORMAT A18
SELECT COUNT(1), USER_ID , DECODE(LOGON_DAY,NULL,'DESCONEXIONES','CONEXIONES') , host,
MIN(LOGOFF_DAY),MIN(LOGON_DAY),MAX(LOGOFF_DAY),MAX(LOGON_DAY)
 FROM SYS.STATS$USER_LOG
where
(logon_day is null or logon_day between '&&1' and '&&2' ) and
(logoff_day is null or logoff_day between '&&1' and '&&2' )
GROUP BY USER_ID , DECODE(LOGON_DAY,NULL,'DESCONEXIONES','CONEXIONES'), host
ORDER BY 2
/

Comando - Dividir conjuntos de datos en tablas según id , proporcionalmente

SELECT   subconjunto, MIN (ID), MAX (ID), COUNT (*)
    FROM (SELECT object_id ID, NTILE (10) OVER (ORDER BY object_id) subconjunto
            FROM all_objects)
GROUP BY subconjunto

SUBCONJUNTO    MIN(ID)    MAX(ID)   COUNT(*)
----------- ---------- ---------- ----------
          6      24579      29363       4785
          7      29364      34148       4785
          5      19794      24578       4785
          8      34149      38938       4785
          1          2       4931       4786
          2       4932      10115       4786
          3      10116      15008       4786
          4      15009      19793       4785
          9      38939     322991       4785
         10     322992     361999       4785

Comando - Cambiar parámetros de session

EN SQLPLUS
alter session set NLS_CHARACTERSET=WE8ISO8859P1;

--------
EN PL/SQL
dbms_session.set_nls('NLS_CHARACTERSET','WE8ISO8859P1');

Script - Juego_caracteres

Juego de caracteres utilizado en la base de datos:

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
       decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKOWN'),
                     9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
                    96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
                   112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
      from sys.col$ where charsetform in (1,2) and type# in (1, 9, 96, 112);

viernes, 20 de agosto de 2010

Script - Genera_auditorias.sql

Script para la generación de tablas de auditorias y triggers relacionados para implantar las llamadas "Auditorias de Aplicación".

El script se ha de llamar desde una sesión SQL conectada como usuario con rol DBA (system) así:

@genera_auditorias.sql ESQUEMA

Donde ESQUEMA es el nombre del esquema en mayúsculas al que vamos a generar las tablas y triggers de auditorias.

NOTAS:

El script genera los nombres de las tablas de auditorias como AUDI_[nombre_tabla_original].

Las tablas las genera bajo el mismo esquema que las tablas originales.

El tablespace utilizado es USERS.

Añade a cada tabla de auditorias las columnas USER_ID, HOST, OS_USER, AUD_FEC y AUD_DML, que se informarán a través de los triggers de la forma:
USER_ID: Id del usuario de bbdd.
HOST: Host desde el que se establece la conexión cliente contra el servidor de bbdd.
OS_USER: Usuario de SO con el que se establece la conexión.
AUD_FEC: Fecha-hora del cambio (insert, update, delete).
AUD_DML: Tipo de cambio, U=Update, D=Delete, I=Insert.

Los triggers se generan de forma que si el cambio es un UPDATE o un DELETE, se registrará en la tabla de auditorias los valores anteriores de las columnas (:OLD), si el cambio es un INSERT se registrará en la tabla de auditorias correspondiente los nuevos valores insertados (:NEW).

El último paso del script es el lanzamiento de los comandos de creación de tablas y triggers.


-------------------------------------------------------------------------------
--
-- Script:    GENERA_AUDITORIAS.SQL
--
-- Propósito:    CREA LAS TABLAS DE AUDITORIAS RELACIONADAS CON LAS TABLAS DEL ESQUEMA QUE SE PASA COMO
--        PARÁMETRO Y CREA LOS TRIGGERS NECESARIOS PARA LA INFORMACIÓN DE LAS MISMAS.
--
-- Para:    8.1.7 o superior
--
-------------------------------------------------------------------------------

set serveroutput on size 1000000
set termout on
set verify off
set feedback off
set echo off
set heading off
set pagesize 0
set linesize 1000
set pause off
set wrap on
column comando format a200
SET TRIMSPOOL ON
SPOOL CREA_AUDITORIAS_&&1..SQL
SELECT 'CREATE TABLE &&1..AUDI_'||TABLE_NAME||' TABLESPACE USERS AS SELECT A.*,SYSDATE AUD_FEC, '||CHR(39)||'D'||CHR(39)||' AUD_DML FROM &&1.'||TABLE_NAME||' A WHERE 0=1;' COMANDO
FROM DBA_TABLES WHERE OWNER='&&1';

SELECT 'ALTER TABLE &&1..AUDI_'||TABLE_NAME||' ADD (USER_ID NUMBER,HOST varchar2(30),OS_USER varchar2(30));' COMANDO
FROM DBA_TABLES WHERE OWNER='&&1' ;
SPOOL OFF;

SPOOL CREA_TRIGGERS_&&1..SQL
declare
    esquema varchar2(100) ;
    nombre_tabla varchar2(100);
    crea_tabla varchar2(100);
    columna varchar2(100);
    par1 varchar2(100);
    par2 varchar2(100);
    par3 varchar2(100);
    par4 varchar2(100);
    par5 varchar2(100);
    par6 varchar2(100);

    par7 number;
    par8 varchar2(4000);
    par9 date;
    par10 varchar2(200);
    par11 varchar2(1);

    sin varchar2(100);
    per varchar2(100);
    tipo varchar2(100);
    precision varchar2(100);
    length varchar2(100);
    scale varchar2(100);
    nullable varchar2(100);
    cadena varchar2(100);
    cursor usuarios is
        select username
        from dba_users
        where username IN ('&&1');
    cursor tablas (usuario in varchar2) is
        select a.table_name
         from
         dba_tables a,
         dba_part_tables b,
         dba_extents c
        where a.owner = usuario  and
              a.table_name = b.table_name(+) and
              a.owner=b.owner(+) and
              b.table_name is null and
              b.owner is null and
              a.table_name=c.segment_name and
              a.owner=c.owner
             and a.duration is null
        group by a.table_name;
    cursor columnas (usuario in varchar2, tabla in varchar2) is
        select     column_name,data_type, data_precision,data_length,data_scale,nullable
        from sys.dba_tab_columns
        where table_name = tabla
             and owner = usuario and column_name not in ('USER_ID','HOST','OS_USER','AUD_FEC','AUD_DML')
        order by column_id;
begin
    dbms_output.put_line('PROMPT Comienzo a crear las TRIGGERS');
    dbms_output.put_line('-----------------------------------------------------------');
   
    open usuarios;
    loop
        fetch usuarios into esquema;
        exit when usuarios%notfound;

        dbms_output.put_line('-----------------------------------------------------------');
        dbms_output.put_line('PROMPT ESQUEMA ...  ' || esquema);

        open tablas(esquema);
        loop
            fetch tablas into nombre_tabla;
            exit when tablas%notfound;
            dbms_output.put_line('-----------------------------------------------------------');
            dbms_output.put_line('PROMPT Creando TRIGGER ...  AUDI_' || nombre_tabla);
            dbms_output.put_line('-----------------------------------------------------------');
            dbms_output.put_line('CREATE OR REPLACE TRIGGER &&1..AUDI_'||nombre_tabla||' AFTER INSERT OR UPDATE  OR DELETE ON '||nombre_tabla||' FOR EACH ROW ');
            dbms_output.put_line(' BEGIN');
            dbms_output.put_line('  IF INSERTING THEN ');
            dbms_output.put_line('    INSERT INTO AUDI_'||nombre_tabla);
            open columnas(esquema, nombre_tabla);
            loop

                fetch columnas into columna,tipo,precision,length,scale,nullable;
                exit when columnas%notfound;
                if columnas%rowcount = 1 then
                    cadena := '(' ;
                else
                    cadena := ',' ;
                end if;
                cadena := cadena || columna;
                dbms_output.put_line(cadena);
            end loop;
            close columnas;
            dbms_output.put_line(',USER_ID,HOST,OS_USER,AUD_FEC,AUD_DML) ');
            dbms_output.put_line('    VALUES');
            open columnas(esquema, nombre_tabla);
            loop

                fetch columnas into columna,tipo,precision,length,scale,nullable;
                exit when columnas%notfound;
                if columnas%rowcount = 1 then
                    cadena := '(' ;
                else
                    cadena := ',' ;
                end if;
                cadena := cadena || ':NEW.'||columna;
                dbms_output.put_line(cadena);
            end loop;
            close columnas;
            dbms_output.put_line(',sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'SESSION_USERID'||CHR(39)||'),sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'HOST'||CHR(39)||'), sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'OS_USER'||CHR(39)||'),SYSDATE,'||CHR(39)||'I'||CHR(39)||'); ');
            dbms_output.put_line('END IF; ');
           
            dbms_output.put_line('  IF UPDATING THEN ');
            dbms_output.put_line('    INSERT INTO AUDI_'||nombre_tabla);
            open columnas(esquema, nombre_tabla);
            loop

                fetch columnas into columna,tipo,precision,length,scale,nullable;
                exit when columnas%notfound;
                if columnas%rowcount = 1 then
                    cadena := '(' ;
                else
                    cadena := ',' ;
                end if;
                cadena := cadena || columna;
                dbms_output.put_line(cadena);
            end loop;
            close columnas;
            dbms_output.put_line(',USER_ID,HOST,OS_USER,AUD_fEC,AUD_DML) ');
            dbms_output.put_line('    VALUES');
            open columnas(esquema, nombre_tabla);
            loop

                fetch columnas into columna,tipo,precision,length,scale,nullable;
                exit when columnas%notfound;
                if columnas%rowcount = 1 then
                    cadena := '(' ;
                else
                    cadena := ',' ;
                end if;
                cadena := cadena || ':OLD.'||columna;
                dbms_output.put_line(cadena);
            end loop;
            close columnas;
            dbms_output.put_line(',sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'SESSION_USERID'||CHR(39)||'),sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'HOST'||CHR(39)||'), sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'OS_USER'||CHR(39)||'),SYSDATE,'||CHR(39)||'U'||CHR(39)||'); ');
            dbms_output.put_line('END IF; ');
           
            dbms_output.put_line('  IF DELETING THEN ');
            dbms_output.put_line('    INSERT INTO AUDI_'||nombre_tabla);
            open columnas(esquema, nombre_tabla);
            loop

                fetch columnas into columna,tipo,precision,length,scale,nullable;
                exit when columnas%notfound;
                if columnas%rowcount = 1 then
                    cadena := '(' ;
                else
                    cadena := ',' ;
                end if;
                cadena := cadena || columna;
                dbms_output.put_line(cadena);
            end loop;
            close columnas;
            dbms_output.put_line(',USER_ID,HOST,OS_USER,AUD_FEC,AUD_DML) ');
            dbms_output.put_line('    VALUES');
            open columnas(esquema, nombre_tabla);
            loop

                fetch columnas into columna,tipo,precision,length,scale,nullable;
                exit when columnas%notfound;
                if columnas%rowcount = 1 then
                    cadena := '(' ;
                else
                    cadena := ',' ;
                end if;
                cadena := cadena || ':OLD.'||columna;
                dbms_output.put_line(cadena);
            end loop;
            close columnas;
            dbms_output.put_line(',sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'SESSION_USERID'||CHR(39)||'),sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'HOST'||CHR(39)||'), sys_context('||CHR(39)||'USERENV'||CHR(39)||','||CHR(39)||'OS_USER'||CHR(39)||'),SYSDATE,'||CHR(39)||'D'||CHR(39)||'); ');
            dbms_output.put_line('END IF; ');
        dbms_output.put_line('END ; ');
        dbms_output.put_line('/');
    end loop;
    close tablas;
end loop;
close usuarios;
end;
/
SPOOL OFF;

-- CREACION DE LAS TABLAS Y TRIGGERS

@CREA_AUDITORIAS_&&1..SQL
@CREA_TRIGGERS_&&1..SQL

jueves, 19 de agosto de 2010

Script - Backup_caliente_local_RMAN

El script backup_caliente_local_RMAN.sh se ha de llamar con los parámetros:

backup_caliente_local_RMAN.sh SID_BBDD DIR_DESTINO

Donde

SID_BBDD es el SID de la base de datos Oracle a copiar.
DIR_DESTINO es el directorio destino del backup (ej. /u01/backups) donde se almacenará el backup en caliente comprimido RMAN de la base de datos.

Se trata de un backup en caliente, es decir, la base de datos puede permanecer operativa mientras se realiza el backup, para ello, la base de datos debe estar en modo Archivelog.

#!/bin/sh
. $HOME/.profile

SID=$1
DEST=$2

RMAN_LOG=$HOME/backup_rman/logs/backup-caliente-local-RMAN_$SID.log
RMAN=$ORACLE_HOME/bin/rman

# Creamos el fichero de log

echo >> $RMAN_LOG
echo ==== Fecha Inicio: `date` ==== >> $RMAN_LOG
echo >> $RMAN_LOG

# Comandos RMAN para la base de datos

ORACLE_SID=$SID
export ORACLE_SID
$RMAN target / nocatalog msglog $RMAN_LOG append <<EOF
set encryption on for all tablespaces algorithm 'AES128' identified by "password_encriptacion" only;
RUN {
allocate channel disk1 device type disk format '$DEST/data_%U' maxpiecesize 20 G;
allocate channel disk2 device type disk format '$DEST/data_%U' maxpiecesize 20 G;
backup filesperset = 5 keep until time 'SYSDATE+31' logs as COMPRESSED BACKUPSET tag '%TAG' database include current controlfile;
release channel disk1;
release channel disk2;

allocate channel disk1 device type disk format '$DEST/ctl_%U' maxpiecesize 20 G;
backup current controlfile;
release channel disk1;
allocate channel disk1 device type disk format '$DEST/spfile_%U' maxpiecesize 20 G;
backup spfile;
release channel disk1;
}
allocate channel for maintenance device type disk;
CROSSCHECK BACKUP;
CROSSCHECK archivelog all;
delete noprompt obsolete ;
delete noprompt EXPIRED BACKUP;
delete noprompt expired archivelog all;
release channel;

run {
# Generamos el ultimo fichero de redo log archivado
sql 'alter system archive log current';

allocate channel disk1 device type disk format '$DEST/arch_%U' maxpiecesize 20 G;
backup filesperset = 5 as COMPRESSED BACKUPSET tag '%TAG' archivelog all not backed up;
release channel disk1;
}

EOF

RESULTADO=$?

if [ "$RESULTADO" = "0" ]
then
    LOGMSG="Backup caliente local RMAN para la bbdd $SID sobre $DEST terminado correctamente"
else
    LOGMSG="Backup caliente local RMAN para la bbdd $SID sobre $DEST terminado con ERROR"
fi

MES=`date +%m`; export MES
DIA=`date +%d`; export DIA
ANO=`date +%Y`; export ANO
FECHA=$ANO$MES$DIA

USR_PWD="sys/sys as sysdba"
sqlplus $USR_PWD <<EOF
alter system checkpoint;
alter database backup controlfile to trace as '$DEST/control-$SID.ctl';
create pfile='$DEST/spfile-$SID.txt' from spfile;
exit;
EOF
mv $DEST/control-$SID.ctl $DEST/control-$SID.ctl.$FECHA

# Finalizamos el fichero de log

echo >> $RMAN_LOG
echo ==== $LOGMSG: `date` ==== >> $RMAN_LOG

exit $RESULTADO

Script - Backup_frio_local

El script backup_frio_local.sh se ha de llamar con los parámetros:

backup_frio_local.sh SID_BBDD DIR_DESTINO

Donde

SID_BBDD es el SID de la base de datos Oracle a copiar.
DIR_DESTINO es el directorio destino del backup (ej. /u01/backups) donde se almacenará el backup comprimido de la base de datos.

Este script corresponde a lo que Oracle denomina "user-managed" backups.

Se trata de un backup en FRIO, es decir, se deben cerrar los ficheros de base de datos y eliminar la instancia (bajar la base de datos) para poder hacer el backup.


. $HOME/.profile

# SOLO TENDREMOS 2 PARAMETROS, SID Y DEST

SID=$1
DEST=$2

MES=`date +%m`; export MES
DIA=`date +%d`; export DIA
ANO=`date +%Y`; export ANO
FECHA=$ANO$MES$DIA

#BORRAMOS EL BACKUP PREVIO

touch /tmp/pru_borrar
/bin/rm /tmp/pru_borrar $DEST/*.Z

#GENERAMOS LA LISTA DE DATAFILES QUE TENEMOS QUE COPIAR

cd $HOME/backup_frio
ORACLE_SID=$SID
export ORACLE_SID;
$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
set heading off;
set feedback off;
spool logs/DF-$SID-$FECHA.txt
select file_name from dba_data_files;
select member from v\$logfile;
select name from v\$tempfile;
select name from v\$controlfile;
spool off;
spool logs/UDUMP-$SID-$FECHA.txt
select value from V\$parameter where name='user_dump_dest';
spool off;
alter database backup controlfile to trace;
exit
EOF

#Depuramos los ficheros, quitandole las lineas en blanco

cat logs/DF-$SID-$FECHA.txt |grep "/" > logs/DF-$SID-$FECHA-1.txt
mv logs/DF-$SID-$FECHA-1.txt logs/DF-$SID-$FECHA.txt

#Estas lineas son para copiar el fichero de control en formato ascii

cat logs/UDUMP-$SID-$FECHA.txt |grep "/" > logs/UDUMP-$SID-$FECHA-1.txt
mv logs/UDUMP-$SID-$FECHA-1.txt logs/UDUMP-$SID-$FECHA.txt

#Bajamos la BBDD

$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
shutdown immediate;
exit
EOF

#Realizamos la copia al directorio especificado

for j in `cat logs/DF-$SID-$FECHA.txt`
do
cp $j $DEST
done

#Copiamos el fichero init y/o spfile

cat $ORACLE_HOME/dbs/init$SID.ora > $DEST/init$SID.ora
cp $ORACLE_HOME/dbs/spfile$SID.ora $DEST/spfile$SID.ora

#Ahora copiamos las ultimas traza de bbdd para llevarnos el fichero de control en ascii

cd `cat logs/UDUMP-$SID-$FECHA.txt`
tar -cvf UDUMP-$SID-$FECHA.tar `find . -type f -mtime 0`
mv UDUMP-$SID-$FECHA.tar $DEST

#Subimos la BBDD

$ORACLE_HOME/bin/sqlplus -s / as sysdba << EOF
startup;
exit
EOF

# COMPRESION COPIA
for j in `ls $DEST/*.tar $DEST/*.ora $DEST/*.ctl $DEST/*.dbf`
do
compress -f $j
done



miércoles, 18 de agosto de 2010

Script - Backup_caliente_local.sh

El script backup_caliente_local.sh se ha de llamar con los parámetros:

backup_caliente_local.sh system/"password_system" SID_BBDD DIR_DESTINO

Donde

SID_BBDD es el SID de la base de datos Oracle a copiar (debe estar en modo Archivelog)
DIR_DESTINO es el directorio destino del backup (ej. /u01/backups) donde se almacenará el backup sin comprimir de la base de datos.

Este script corresponde a lo que Oracle denomina "user-managed" backups.

#!/bin/ksh
. $HOME/.bash_profile

# COGEMOS EL PARAMETRO DE LA CONEXION (US_PW (usuario/password)), EL SID (SID) Y EL DESTINO DE LA COPIA EN CALIENTE (DEST)

US_PW=$1
SID=$2
DEST=$3

MES=`date +%m`; export MES
DIA=`date +%d`; export DIA
ANO=`date +%Y`; export ANO
FECHA=$ANO$MES$DIA


#BORRAMOS LOS FICHEROS ARCHS COPIADOS ANTERIORMENTE

rm $DEST/arch*.arc
rm $DEST/*.gz

#GENERAMOS LA LISTA DE TABLESPACE QUE TENEMOS QUE HACER COPIA EN CALIENTE

$ORACLE_HOME/bin/sqlplus -s $US_PW@$SID << EOF
alter system switch logfile;
set heading off;
set trimspool on;
set feedback off;
spool /home/oracle/scripts/COPIACALIENTE/logs/TB-$SID-$FECHA.txt
select tablespace_name from dba_tablespaces where contents not in ('TEMPORARY') order by 1;
spool off;
spool /home/oracle/scripts/COPIACALIENTE/logs/TB-DF-$SID-$FECHA.txt
select tablespace_name||','||file_name from dba_data_files order by 1;
spool off;
exit
EOF

#AHORA FILTRAMOS POR TB COPIANDO LOS DATAFILES CORRESPONDIENTES

for j in `cat /home/oracle/scripts/COPIACALIENTE/logs/TB-$SID-$FECHA.txt`
do
echo 'ALTER TABLESPACE '$j' BEGIN BACKUP;' > /home/oracle/scripts/COPIACALIENTE/logs/TB-BEGIN.txt
echo 'ALTER TABLESPACE '$j' END BACKUP;' > /home/oracle/scripts/COPIACALIENTE/logs/TB-END.txt

#GENERAMOS LA LISTA DE DATAFILES A COPIAR A PARTIR DE UN TABLESPACE DETERMINADO

cat /home/oracle/scripts/COPIACALIENTE/logs/TB-DF-$SID-$FECHA.txt|grep $j|cut -f 2 -d ',' > /home/oracle/scripts/COPIACALIENTE/logs/DF-COP.txt

#Ahora lanzamos el begin backup

$ORACLE_HOME/bin/sqlplus -s $US_PW@$SID << EOF
@/home/oracle/scripts/COPIACALIENTE/logs/TB-BEGIN.txt
exit
EOF

#Ahora lanzamos la copia de los ficheros al directorio especificado

for i in `cat /home/oracle/scripts/COPIACALIENTE/logs/DF-COP.txt`
do
cp $i $DEST
done

#Ahora lanzamos el end backup

$ORACLE_HOME/bin/sqlplus -s $US_PW@$SID << EOF
@/home/oracle/scripts/COPIACALIENTE/logs/TB-END.txt
exit
EOF
done

#Ahora listamos los ficheros archs a copiar, generando antes el ultimo arch

$ORACLE_HOME/bin/sqlplus -s $US_PW@$SID << EOF
alter system switch logfile;
set heading off;
set trimspool on;
set feedback off;
spool /home/oracle/scripts/COPIACALIENTE/logs/ARCHS-$SID-$FECHA.txt
select t2.value||'/'||(replace(replace(t3.value,'%s',t1.sequence#),'%t',t1.thread#))  from v$loghist t1, v$parameter t2, v$parameter t3 where t1.first_time > sysdate-1 and t2.name = 'log_archive_dest' and t3.name = 'log_archive_format' order by sequence#;
spool off;
exit
EOF

#Ahora lanzamos la copia de los ficheros ARCHS al directorio especificado (con 1 dia de antiguedad)

for i in `cat /home/oracle/scripts/COPIACALIENTE/logs/ARCHS-$SID-$FECHA.txt`
do
if [ -f $i ];
then
cp $i $DEST;
fi
done

OEM Grid Control

Apuntes Oracle Enterprise Manager Grid Control:

Para la instalación exitosa de Oracle Enterprise Manager Grid Control (linux_x86_64) y los agentes necesarios, se ha de utilizar la documentación oficial Oracle:

http://www.oracle.com/technetwork/oem/grid-control/downloads/grid-control-10-089610.html
"Oracle Enterprise Manager
Grid Control ReadMe for Linux x86-64
10g Release 5 (10.2.0.5)
April 2009 "

Para la instalación de Oracle Management Agent de forma silenciosa, sin requerir interfaces Xs:

Instalación de Management Agents (se descara el .zip de la versión de sistema operativo adecuada de otn) y se modifica el fichero "additional_agent.rsp" con el siguiente contenido (modificando las rutas, grupo de sistema operativo, etc):

cd /u01/app/oracle/install_GC/linux/agent
./runInstaller -silent -responseFile=/u01/app/oracle/install_GC/linux/response/additional_agent.rsp -ignoresysprereqs

Muy importante esquemas de conectividad en entornos con Firewalls entre los agentes y el oms:

http://download.oracle.com/docs/cd/B13789_01/network.101/b10775/concepts.htm
"Oracle® Database Net Services Administrator's Guide
10g Release 1 (10.1)
Part Number B10775-01"

http://download-uk.oracle.com/docs/cd/B16240_01/doc/em.102/e10954/firewalls.htm#i1005609
"Oracle® Enterprise Manager Advanced Configuration
10g Release 5 (10.2.0.5)
Part Number E10954-03"

A tener en cuenta algunos bugs y notas de Metalink importantes:

The Grid Control Agent 10.2.0.5 Fails to Monitor 9.2.0.x Databases with ORA-3113/ORA-7445 [ID 828464.1]
Parche a aplicar en la instalación del agente: Patch 9162498

Problemas con el agente tras comunicación incorrecta con gridmanager : ...Thread-.. ERROR pingManager: nmepm_pingReposURL: Error in request response. code = 400. text =
Thread-... ERROR command: nmejcn: error receiving response headers from...
No funcionan las notas de Metalink [ID 378104.1] y similares.
Se borra completamente la instalación del agente y se instala de nuevo, funcionando en este caso todo correctamente.

Para problemas de comunicaciones con agentes en Windows: USE_SHARED_SOCKETS=TRUE ver nota metalink:"How to configure USE_SHARED_SOCKET on Windows NT/2000 [ID 124140.1]"

Problemas de carga de información en OMS: [XMLLoader0 ....xml] WARN  eml.XMLLoader LoadFiles.755 - Marking the file for retry : ...xml after receiving exceptionjava.sql.SQLException: ORA-04091: la tabla SYSMAN.MGMT_DB_HDM_METRIC_HELPER está mutando, puede que el disparador/la función no puedan verla
ORA-06512: en "SYSMAN.MGMT_DB_HDM_METRIC_TR", línea 4
ORA-04088: error durante la ejecución del disparador 'SYSMAN.MGMT_DB_HDM_METRIC_TR'
Se aplica el parche 8219750 en la instalación de OMS.

Problemas de captura de información relacionados con la variable TZ: [Thread-...] ERROR track.Snapshot captureIntoRepository.604 - ECM: Cannot capture [Information of type Host Configuration for Host ... at ... (MEST)]: invalid snapshot timestamp: ... (MEST). Unparseable date: "... (MEST)"
Se aplica nota de metalink: "Problem: Config: Invalid Snapshot Timestamp In emoms.log when using MET timezone [ID 404224.1]", estableciendo la variable TZ antes del arranque a: "Europe/Madrid" ó "CET"

Problema: "program nmhs is using a deprecated SCSI ioctl, please convert it to SG_IO"
Se aplica el parche 5897089 en la instalación del AGENTE en gridmanager.

Problema: "storage_report_metrics.pl: ...: WARN:  STORAGE_REPORTS:error::snmhsutl.c:executable nmhs should have root suid enabled
Se aplica Nota de Metalink: "Problem - storage_report_metrics.pl STORAGE_REPORTS error snmhsutl.c:executable nmhs should have root suid enabled [ID 435793.1]"

Problemas tras el cambio de entradas en /etc/hosts (el servidor no consigue arrancar): el problema estaba en el "formato" del fichero /etc/hosts,
Las entradas /etc/hosts deben ser así: ip nombre_cualificado_completo nombre_no_cualificado
Tras el cambio en el /etc/hosts, todo arranca correctamente.

Problema: Al intentar entrar en un destino de instancia nos aparece el mensaje: "The database target is currently unavailable" estando la bbdd correctamente levantada y accesible
Se soluciona con el comando: "./emctl clearstate agent" en el agente del nodo cliente, para evitar repeticiones futuras del problema, se han de definir blackouts cada vez que haya reinicios controlados de las intancias.

Problema: "Se ha producido un error inesperado al preparar el contenido de esta página.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "DBSNMP.MGMT_BSLN", line 236
ORA-06512: at "DBSNMP.MGMT_BSLN", line 255 ORA-06512: at "DBSNMP.MGMT_BSLN_INTERNAL", line 1435 ORA-06512: at "DBSNMP.MGMT_BSLN", line 2580"
Está motifivado por el "idioma" del explorador, hay que cambiarlo a English y reiniciar el navegador.

Problema: "ORA-07445: exception encountered: core dump [msqsub()+4] [SIGSEGV] [Address not mapped to object] [0x35B000000000070] [] []" al intentar obtener el plan de ejecución de consultas con campos "bind" en la parte del ORDER BY.
No se encuentra solución, no intentar analizar consultas con campos bind en la parte del ORDER BY.

Problema: error ... agent unreachable
... Thread-84888480 ERROR ssl: nzos_Handshake failed, ret=28862
... Thread-84888480 ERROR http: 12: Error initializing SSL connection for incoming request, aborting request. ret=-10
Se siguen los pasos de esta resolucion en el foro de oracle: http://kr.forums.oracle.com/forums/thread.jspa?threadID=931444
http://oracledbasupport.co.uk/oem-agent-problem-the-agent-is-running-but-not-ready/
Delete all files at $AGENT_HOME/sysman/emd/upload and $AGENT_HOME/sysman/emd/state
export AGENT_HOME=/opt/oracle/product/agent10g/
echo $AGENT_HOME
rm –Rf $AGENT_HOME/sysman/emd/upload/*.*
rm –Rf $AGENT_HOME/sysman/emd/state/*.*
$ ./emctl clearstate agent
$ ./emctl unsecure agent
$ ./emctl start agent
$ ./emctl upload agent
$ ./emctl secure agent
Check 731692.1 and 808318.1 documents on metalink

Enlace con scripts interesantes para OEM Grid Control:
http://www.oraclerant.com/?p=83

Script - Unix_espia

Para identificar una sesión de la que sólo conocemos el PID de la conexión en el sistema operativo, nos genera además la sentencia para “espiarla” . Se le pasa como parámetro el PID del proceso en el sistema operativo. Se necesita el script espia_session.


SELECT V$SESSION.LOGON_TIME CONEXION,
       SUBSTR(V$PROCESS.SPID,1,5) UNIX,
       SUBSTR(TO_CHAR(V$SESSION.SID),1,3) SID,
       SUBSTR(V$SESSION.USERNAME,1,10) USUARIO,
       SUBSTR(TO_CHAR(V$SESSION.SERIAL#),1,6) NSERIE,
       SUBSTR(V$SESSION.OSUSER,1,10) OSUSER,
       SUBSTR(V$SESSION.STATUS,1,10) ESTADO,
       SUBSTR(V$SESSION.SERVER,1,7) SERVER,
     SUBSTR(V$SESSION.MACHINE,1,30) MAQUINA
FROM
     V$SESSION,
     V$PROCESS
WHERE
    (V$PROCESS.SPID = &&1) AND
    (V$SESSION.PADDR = V$PROCESS.ADDR) AND
    (V$SESSION.USERNAME IS NOT NULL) AND
    (V$SESSION.SERVER LIKE 'DEDIC%')
ORDER BY V$PROCESS.SPID;

SELECT '@ESPIA_SESSION ' || SUBSTR(TO_CHAR(V$SESSION.SID),1,3) || ' ' || SUBSTR(TO_CHAR(V$SESSION.SERIAL#),1,6) ESPIA
FROM
     V$SESSION,
     V$PROCESS
WHERE
    (V$PROCESS.SPID = &&1) AND
    (V$SESSION.PADDR = V$PROCESS.ADDR) AND
    (V$SESSION.USERNAME IS NOT NULL) AND
    (V$SESSION.SERVER LIKE 'DEDIC%')
ORDER BY V$PROCESS.SPID;

Script - Tope

Muestra las sentencias ejecutadas más de 1000 veces ordenadas en función del número de ejecuciones.


SET HEAD ON
SET LINESIZE 1000
SET PAGESIZE 2000
SELECT
    BUFFER_GETS,
    EXECUTIONS,
    DISK_READS,
    ROWS_PROCESSED "FILAS PROC",   
    SUBSTR(SQL_TEXT,1,900) "CONSULTA"
FROM V$SQLAREA
WHERE
    EXECUTIONS > 1000
ORDER BY EXECUTIONS DESC;

Script - Topd

Muestra las sentencias ejecutadas cuyos accesos a disco sean superiores a 1000 ordenadas en función del acceso a disco.


SET HEAD ON
SET LINESIZE 1000
SET PAGESIZE 2000
SELECT
    BUFFER_GETS,
    EXECUTIONS,
    DISK_READS,
    ROWS_PROCESSED "FILAS PROC",   
    SUBSTR(SQL_TEXT,1,900) "CONSULTA"
FROM V$SQLAREA
WHERE
    DISK_READS > 1000
ORDER BY DISK_READS DESC;

Script - Topb

Muestra las sentencias ejecutadas cuyo acceso a bloques de búfferes sean superiores a 1000 ordenadas en función del acceso a bloques de búfferes.


SET HEAD ON
SET LINESIZE 1000
SET PAGESIZE 2000
SELECT
    BUFFER_GETS,
    EXECUTIONS,
    DISK_READS,
    ROWS_PROCESSED "FILAS PROC",   
    SUBSTR(SQL_TEXT,1,900) "CONSULTA"
FROM V$SQLAREA
WHERE
    BUFFER_GETS > 1000
ORDER BY BUFFER_GETS DESC;

Script - Actividad_rollbacks

Monitoriza la actividad en los distintos segmentos de rollback.

SET LINESIZE 1000

SELECT
       substr(r.name,1,15) "RBS",
       substr(p.pid,1,10) "BLOQUEANTE",
       substr(p.spid,1,10) system_pid,
       substr(s.sid,1,10) "BLOQUEADO",
       ' => ' "=>",
       substr(s.osuser,1,10) "OSUSER",
       nvl(p.username,'NO TRANSACTION') "TRANSACCION",
       E.EXTENTS
FROM
       V$LOCK L,
       V$PROCESS P,
       V$ROLLNAME R,
       V$SESSION S,
     DBA_SEGMENTS E
WHERE
     E.SEGMENT_TYPE='ROLLBACK' AND
     R.NAME=E.SEGMENT_NAME AND
       s.paddr(+) = p.addr and
       l.sid = p.pid(+) and
       trunc(l.id1(+)/65536)=r.usn and
       l.type(+) = 'TX' and
       l.lmode(+) = 6
ORDER BY
       r.name;

Script - Que

Para ver lo que está ejecutando una sesión en particular. Se le pasa como parámetro el sid de la sesión a monitorizar.

SET LINESIZE 1000

SELECT A.SID,A.USERNAME,B.ADDRESS,B.SQL_TEXT
FROM V$SESSION A,V$SQLTEXT B
WHERE
    A.SID=&&1 AND A.SQL_ADDRESS=B.ADDRESS
    AND A.SQL_HASH_VALUE=B.HASH_VALUE
ORDER BY A.SID,A.USERNAME,B.PIECE;

SET LINESIZE 1000

Script - Peso_datafiles

Nos indica el “peso” de escrituras/lecturas que está sufriendo cada uno de los datafiles.

set linesize 180
column filename format a50
column %lect format a5
column %esc format a5
column peso format a5
prompt
select
  substr(i.name,1,45) filename,
  x.phyrds lecturas,
  x.phywrts escrituras,
  x.phyrds+x.phywrts total_es,
  SUBSTR(round(100*(x.phyrds)/(x.phyrds+x.phywrts)),1,5) "%LECT",
  SUBSTR(round(100*(x.phywrts)/(x.phyrds+x.phywrts)),1,5) "%ESC",
  SUBSTR(round(round(100*(x.phyrds+x.phywrts)/SELTOTAL.max_total,2),0),1,5) peso
from v$filestat x, sys.ts$ ts, v$datafile i,sys.file$ f ,
(select
max(x1.phyrds+x1.phywrts) max_total
from v$filestat x1, sys.ts$ ts1, v$datafile i1, sys.file$ f1
where i1.file#=f1.file#
and ts1.ts#=f1.ts#
and x1.file#=f1.file#) SELTOTAL
where i.file#=f.file#
and ts.ts#=f.ts#
and x.file#=f.file#
order by peso;

Script - Mapa_extensiones

Mapa de extensiones de un tablespace, se le pasa como parámetro el nombre del tablespace.

SET LINESIZE 1000

SELECT 'ESPACIO LIBRE' PROPIETARIO,
       '          ' OBJETO,
       SUBSTR(DT.TABLESPACE_NAME,1,15) TABLESPACE,
       SUBSTR(DFS.FILE_ID,1,7)+0 ARCHIVO,
       DFS.BLOCK_ID BLOQUE,
       DFS.BLOCKS*1024/256 "TAM EXT Kb",
       DFS.BLOCKS/256 "TAM EXT Mb"
FROM DBA_FREE_SPACE DFS,
     DBA_TABLESPACES DT
WHERE DFS.TABLESPACE_NAME=DT.TABLESPACE_NAME AND
      DFS.TABLESPACE_NAME='&&1'
UNION
SELECT SUBSTR(DE.OWNER,1,13),
       SUBSTR(DE.SEGMENT_NAME,1,30),
       SUBSTR(DT.TABLESPACE_NAME,1,15) TABLESPACE,
       SUBSTR(DE.FILE_ID,1,7)+0,
       DE.BLOCK_ID,
       DE.BLOCKS*1024/256,
       DE.BLOCKS/256
FROM DBA_EXTENTS DE,
     DBA_TABLESPACES DT
WHERE DE.TABLESPACE_NAME=DT.TABLESPACE_NAME AND
      DE.TABLESPACE_NAME='&&1'
ORDER BY 3,4,5;

SET LINESIZE 1000

Script - Infotablespaces

Información de los distintos tablespaces de nuestra base de datos.

 (versión mejorada, antes no tenía en cuenta datafiles sin espacio libre)

PROMPT TABLESPACES
column tablespace format a25

SELECT SUBSTR(D.FILE_NAME,1,45) "ARCHIVO",
SUBSTR(T.tablespace_name,1,25) "TABLESPACE",
(D.BYTES/1024)/1024 "TAM(Mb)",
((D.BYTES/1024)/1024 - SUM((NVL(F.BYTES,0)/1024)/1024)) "OCUP(Mb)",
SUM((NVL(F.BYTES,0)/1024)/1024)"LIBRE(Mb)",
((D.BYTES - SUM(NVL(F.BYTES,0))) * 100 / D.BYTES) "% OCUP",
(SUM(NVL(F.BYTES,0)) * 100 / D.BYTES) "% LIBRE"
FROM DBA_FREE_SPACE F,DBA_DATA_FILES D,DBA_TABLESPACES T
WHERE
    D.FILE_ID=F.FILE_ID(+) AND
    D.TABLESPACE_NAME=T.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME,
    D.BYTES,
    SUBSTR(D.FILE_NAME,1,45)
ORDER BY 1;

SET LINESIZE 1000


PROMPT TABLESPACES


SELECT SUBSTR(D.FILE_NAME,1,45) "ARCHIVO",
SUBSTR(F.tablespace_name,1,15) "TABLESPACE",
(D.BYTES/1024)/1024 "TAM(Mb)",
((D.BYTES/1024)/1024 - SUM((F.BYTES/1024)/1024)) "OCUP(Mb)",
SUM((F.BYTES/1024)/1024)"LIBRE(Mb)",
((D.BYTES - SUM(F.BYTES)) * 100 / D.BYTES) "% OCUP",
(SUM(F.BYTES) * 100 / D.BYTES) "% LIBRE"
FROM DBA_FREE_SPACE F,DBA_DATA_FILES D,DBA_TABLESPACES T
WHERE D.FILE_ID=F.FILE_ID AND F.TABLESPACE_NAME=T.TABLESPACE_NAME
GROUP BY F.TABLESPACE_NAME,
    D.BYTES,
    SUBSTR(D.FILE_NAME,1,45),
    T.TABLESPACE_NAME
ORDER BY 1;


SET LINESIZE 1000

Script - Infotablas_particionadas

Monitorización de espacio de las tablas particionadas de la base de datos.

BREAK ON TABLA
SELECT NAME "TABLA",
    COLUMN_NAME "COLUMNA DE PARTICIONAMIENTO",
    COLUMN_POSITION "POSICION"
FROM DBA_PART_KEY_COLUMNS
WHERE OWNER IN ('USUARIO') AND
    OBJECT_TYPE LIKE 'TABLE%'
ORDER BY 1,3;

SET LONG 10
BREAK ON TABLA SKIP 1
SELECT
    SUBSTR(S.SEGMENT_NAME,1,25) "TABLA",
    SUBSTR(S.PARTITION_NAME,1,13) "PARTICION",
    P.HIGH_VALUE "MAXIMO",
    SUBSTR(S.TABLESPACE_NAME,1,12) "TABLESPACE",
    S.INITIAL_EXTENT/(1024) "INIT(Kb)",
    S.NEXT_EXTENT/(1024) "NEXT(Kb)",
    S.EXTENTS "NUM EXT",
    S.MAX_EXTENTS "MAX EXT",
    S.BYTES/(1024) "TAM(Kb)"
FROM DBA_SEGMENTS S,
     DBA_TAB_PARTITIONS P
WHERE
    S.OWNER IN ('USUARIO') AND
    S.SEGMENT_NAME = P.TABLE_NAME AND
    S.PARTITION_NAME = P.PARTITION_NAME AND
    S.SEGMENT_TYPE LIKE 'TABLE%'
ORDER BY S.SEGMENT_NAME,
    P.PARTITION_POSITION;

SET LONG 1000
CLEAR BREAKS

Script - Infotablas

Monitorización de espacio de las tablas del usuario con el que estamos conectados a la base de datos.

SET LINESIZE 1000

PROMPT TABLAS

SELECT
SUBSTR(T.TABLE_NAME,1,25) "TABLA",
SUBSTR(T.TABLESPACE_NAME,1,15) "TABLESPACE",
T.INITIAL_EXTENT/1024 "INIT(Kb)",
T.NEXT_EXTENT/1024 "NEXT(Kb)",
COUNT (*) "NUM EXT",
T.MAX_EXTENTS "MAX EXT",
SUM(BYTES)/1024 "TAM(Kb)"
FROM USER_TABLES T, USER_EXTENTS E
WHERE T.TABLE_NAME=E.SEGMENT_NAME
GROUP BY T.TABLE_NAME, T.TABLESPACE_NAME, T.PCT_FREE, T.PCT_USED,
T.INITIAL_EXTENT, T.NEXT_EXTENT,T.MAX_EXTENTS
ORDER BY T.TABLESPACE_NAME,TABLE_NAME;

SET LINESIZE 1000

Script - Infoindices_particionado

Monitorización de espacio de los índices particionados de la base de datos.

SET LONG 10
BREAK ON INDICE SKIP 1
SELECT
    SUBSTR(S.SEGMENT_NAME,1,15) "INDICE",
    SUBSTR(S.PARTITION_NAME,1,18) "PARTICION",
    P.HIGH_VALUE "MAXIMO",
    SUBSTR(S.TABLESPACE_NAME,1,12) "TABLESPACE",
    S.INITIAL_EXTENT/(1024) "INIT(Kb)",
    S.NEXT_EXTENT/(1024) "NEXT(Kb)",
    S.EXTENTS "NUM EXT",
    S.MAX_EXTENTS "MAX EXT",
    S.BYTES/(1024) "TAM(Kb)"
FROM DBA_SEGMENTS S,
     DBA_IND_PARTITIONS P
WHERE
    S.OWNER IN (‘USUARIO’) AND
    S.SEGMENT_NAME = P.INDEX_NAME AND
    S.PARTITION_NAME = P.PARTITION_NAME AND
    S.SEGMENT_TYPE LIKE 'INDEX%'
ORDER BY S.SEGMENT_NAME,
    P.PARTITION_POSITION;

SET LONG 1000
CLEAR BREAKS

Script - Infoindices

Monitorización de espacio de los índices del usuario con el que estamos conectados a la base de datos.

SET LINESIZE 1000

PROMPT INDICES

SELECT
SUBSTR(T.INDEX_NAME,1,35) "INDICE",
SUBSTR(T.TABLESPACE_NAME,1,15) "TABLESPACE",
T.INITIAL_EXTENT/1024 "INIT(Kb)",
T.NEXT_EXTENT/1024 "NEXT(Kb)",
COUNT(*) "NUM EXT",
T.MAX_EXTENTS "MAX EXT",
SUM(BYTES)/1024 "TAM(Kb)"
FROM USER_INDEXES T, USER_EXTENTS E
WHERE T.INDEX_NAME=E.SEGMENT_NAME
GROUP BY T.INDEX_NAME, T.TABLESPACE_NAME, T.INITIAL_EXTENT, T.NEXT_EXTENT,T.MAX_EXTENTS
ORDER BY 1,T.TABLESPACE_NAME,T.INDEX_NAME;


SET LINESIZE 1000

Script - Explain_plan

Para ver el plan de ejecución de una query recogido previamente mediante el comando Explain plan set statement_id=’test’ for ...

set lines 100 pages 100
select lpad (' ',2*level)||operation||' '||options
         ||' '||object_name  ||' '|| COST   q_plan
from plan_table
where statement_id='test'
connect by prior id = parent_id and statement_id='test'
start with id=1;

Script explain_plan distribuido por Oracle (utlxpls.sql), con algunos cambios relativos a formateo e inclusión del campo object_instance en la salida.

set linesize  200
Rem
Rem Display last explain plan
Rem
select '| Operation                         |  Name            |Rows|Bytes| Cost | Pos|Pstart| Pstop | '  as "Plan Table" from dual
union all
select '------------------------------------------------------------------------------------------------------------------' from dual
union all
select * from
(select /*+ no_merge */
       rpad('| '||substr(lpad(' ',1*(level-1))||operation||
            decode(options, null,'',' '||options), 1, 35), 36, ' ')||'|'||
       rpad(substr(object_name||' ',1, 17), 18, ' ')||'|'||
       lpad(decode(cardinality,null,'  ',
                decode(sign(cardinality-1000), -1, cardinality||' ',
                decode(sign(cardinality-1000000), -1, trunc(cardinality/1000)||'K',
                decode(sign(cardinality-1000000000), -1, trunc(cardinality/1000000)||'M',
                       trunc(cardinality/1000000000)||'G')))), 4, ' ') || '|' ||
       lpad(decode(bytes,null,' ',
                decode(sign(bytes-1024), -1, bytes||' ',
                decode(sign(bytes-1048576), -1, trunc(bytes/1024)||'K',
                decode(sign(bytes-1073741824), -1, trunc(bytes/1048576)||'M',
                       trunc(bytes/1073741824)||'G')))), 5, ' ') || '|' ||
       lpad(decode(cost,null,' ',
                decode(sign(cost-10000000), -1, cost||' ',
                decode(sign(cost-1000000000), -1, trunc(cost/1000000)||'M',
                       trunc(cost/1000000000)||'G'))), 6, ' ') || '|' ||
       lpad(decode(object_instance, null, ' ', object_instance),4,' ')||'|'||
       lpad(decode(partition_start, 'ROW LOCATION', 'ROWID',
            decode(partition_start, 'KEY', 'KEY', decode(partition_start,
            'KEY(INLIST)', 'KEY(I)', decode(substr(partition_start, 1, 6),
            'NUMBER', substr(substr(partition_start, 8, 10), 1,
            length(substr(partition_start, 8, 10))-1),
            decode(partition_start,null,' ',partition_start)))))||' ', 7, ' ')|| '|' ||
       lpad(decode(partition_stop, 'ROW LOCATION', 'ROW L',
          decode(partition_stop, 'KEY', 'KEY', decode(partition_stop,
          'KEY(INLIST)', 'KEY(I)', decode(substr(partition_stop, 1, 6),
          'NUMBER', substr(substr(partition_stop, 8, 10), 1,
          length(substr(partition_stop, 8, 10))-1),
          decode(partition_stop,null,' ',partition_stop)))))||' ', 7, ' ')||'|'
as "Explain plan"
from plan_table
start with id=0 and timestamp = (select max(timestamp) from plan_table
                                 where id=0)
connect by prior id = parent_id
        and prior nvl(statement_id, ' ') = nvl(statement_id, ' ')
        and prior timestamp <= timestamp
order by id, position)
union all
select '------------------------------------------------------------------------------------------------------------------' from dual;

Script - Espia_sesion_valores

Para trazar una sesión con un nivel más elevado de traza, en el que podemos ver los valores de las variables utilizadas en la sentencia, así como todas las estadísticas de los eventos involucrados en su ejecución.

column serial new_value Serial

SELECT SUBSTR(TO_CHAR(V$SESSION.SERIAL#),1,6) serial
FROM
     V$SESSION,
     V$PROCESS
WHERE
    (V$SESSION.PADDR = V$PROCESS.ADDR) AND
    (V$SESSION.USERNAME IS NOT NULL) AND
    (V$SESSION.SERVER LIKE 'DEDIC%') AND
    SUBSTR(TO_CHAR(V$SESSION.SID),1,3) = &&1
ORDER BY V$PROCESS.SPID
/

execute sys.dbms_system.set_ev(&&1,&serial,10046,12,'');

PROMPT
PROMPT
PROMPT execute sys.dbms_system.set_ev(&&1,&serial,10046,0,'');
PROMPT
PROMPT

Aquí veremos la salida de la forma:

PARSING IN CURSOR #1 len=54 dep=0 uid=5 oct=3 lid=5 tim=3972374052 hv=2683306117 ad='866fac10'
select * from … where … =:"SYS_B_0"
END OF STMT
PARSE #1:c=2,e=9,p=3,cr=8,cu=0,mis=1,r=0,dep=0,og=0,tim=3972374052
BINDS #1:
 bind 0: dty=1 mxl=32(08) mal=00 scl=00 pre=00 oacflg=10 oacfl2=100 size=32 offset=0
   bfp=101f08358 bln=32 avl=08 flg=09
   value="52568588"
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=3972374053
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=675562835 p2=1 p3=0
WAIT #1: nam='SQL*Net more data to client' ela= 0 p1=675562835 p2=2009 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 0 p1=52 p2=6597 p3=1
FETCH #1:c=1,e=0,p=1,cr=2,cu=0,mis=0,r=0,dep=0,og=4,tim=3972374053
WAIT #1: nam='SQL*Net message from client' ela= 805 p1=675562835 p2=1 p3=0

Donde el valor resaltado es el valor que hemos puesto en la parte del where:

Select * from ... where ...=’52568588’

Script - Espia_sesion

Para tracear una sesión. Se le pasa como parámetros el SID de la sesión a trazar.

column serial new_value Serial

SELECT SUBSTR(TO_CHAR(V$SESSION.SERIAL#),1,6) serial
FROM
     V$SESSION,
     V$PROCESS
WHERE
    (V$SESSION.PADDR = V$PROCESS.ADDR) AND
    (V$SESSION.USERNAME IS NOT NULL) AND
    (V$SESSION.SERVER LIKE 'DEDIC%') AND
    SUBSTR(TO_CHAR(V$SESSION.SID),1,3) = &&1
ORDER BY V$PROCESS.SPID
/

execute sys.dbms_system.set_sql_trace_in_session(&&1,&serial,true);

PROMPT
PROMPT
PROMPT execute sys.dbms_system.set_sql_trace_in_session(&&1,&serial,false);
PROMPT
PROMPT

Script - Enqueue_locks

Para ver los bloqueos actuales y los requeridos/pendientes/encolados.

column resource format a20
column object format a30
column type format a10
column holding format a10
column wanting format a10
column sid format a4 justify right

break on resource
select /*+ ordered */
  l.type || '-' || l.id1 || '-' || l.id2  "RESOURCE",o.object_name "OBJECT", o.object_type "TYPE",
  nvl(b.name, lpad(to_char(l.sid), 4))  sid,
  decode(
    l.lmode,
    1, '      N',
    2, '     SS',
    3, '     SX',
    4, '      S',
    5, '    SSX',
    6, '      X'
  )  holding,
  decode(
    l.request,
    1, '      N',
    2, '     SS',
    3, '     SX',
    4, '      S',
    5, '    SSX',
    6, '      X'
  )  wanting,
  l.ctime  seconds
from
  sys.v_$lock l,
  sys.v_$session s,
  sys.v_$bgprocess b,
    (select object_name, object_type, object_id from dba_objects ) o
where
  s.sid = l.sid and
  s.sid not in (2,6,8) and
  b.paddr (+) = s.paddr and
  l.id1=o.object_id(+)
order by
  l.type || '-' || l.id1 || '-' || l.id2,
  sign(l.request),
  l.ctime desc
/

Script - Como_va

Script para ver el porcentaje de finalización de trabajo que lleva una operación lanzada por una sesión que ha sido registrada por Oracle como una operación LARGA (V$SESSION_LONGOPS). Se le pasa el sid de la sesión a monitorizar.


SET LINESIZE 1000
COL "LLEVA %" FORMAT 999.99

SELECT
    L.SID "SID",
    L.OPNAME "TAREA",
    L.SOFAR "LLEVA",
    (L.SOFAR/TOTALWORK) * 100 "LLEVA %",
    L.TOTALWORK "TOTAL",
    L.TOTALWORK-SOFAR "FALTA",
    L.TARGET "TRABAJANDO EN",       
    L.START_TIME,            
    ELAPSED_SECONDS
FROM
    V$SESSION_LONGOPS L
WHERE
    L.SID=&&1 AND
    TOTALWORK <> 1
ORDER BY
    L.START_TIME;

SET LINESIZE 1000

Script - Bloqueos_Actuales

Describe los bloqueos actualmente mantenidos por las distintas sessiones, y su descripción.

set echo off
set pagesize 60
Column SID         FORMAT 999 heading "Sess|ID "
COLUMN OBJECT_NAME FORMAT A17 heading "OBJ NAME or|TRANS_ID" Trunc
COLUMN OSUSER      FORMAT A10 heading "Op Sys|User ID"
COLUMN USERNAME    FORMAT A10
COLUMN TERMINAL    FORMAT A8  trunc
select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
       DECODE(B.ID2, 0, A.OBJECT_NAME,
            'Trans-'||to_char(B.ID1)) OBJECT_NAME,
     B.TYPE,
       DECODE(B.LMODE,0,'--Waiting--',
                      1,'Null',
                      2,'Row Share',
                      3,'Row Excl',
                   4,'Share',
                      5,'Sha Row Exc',
           6,'Exclusive',
                        'Other') "Lock Mode",
       DECODE(B.REQUEST,0,' ',
                      1,'Null',
                      2,'Row Share',
                      3,'Row Excl',
                      4,'Share',
                      5,'Sha Row Exc',
                      6,'Exclusive',
                     'Other') "Req Mode"
  from DBA_OBJECTS A, V$LOCK B, V$SESSION C
where A.OBJECT_ID(+) = B.ID1
  and B.SID = C.SID
  and C.USERNAME is not null
order by B.SID, B.ID2;

Script - Bloqueos_Espera

Nos muestra los objetos por los que está esperando una session (si está esperando por un segmento), se le pasa como parámetro el sid de la sessión a monitorizar.

select vw.* ,vw.event,segment_name, segment_type
from
dba_extents,
v$session_wait vw
where
file_id(+) = vw.p1 and
vw.sid=&&1 ;
and
vw.p2 between block_id and block_id+blocks;

Script - Activos

Con este script vemos las sesiones activas en la bbdd, (entornos no MTS).

SET LINESIZE 1000

SELECT V$SESSION.LOGON_TIME CONEXION,
SUBSTR(V$PROCESS.SPID,1,5) UNIX,
SUBSTR(TO_CHAR(V$SESSION.SID),1,3) SID,
SUBSTR(V$SESSION.USERNAME,1,10) USUARIO,
SUBSTR(TO_CHAR(V$SESSION.SERIAL#),1,6) NSERIE,
SUBSTR(V$SESSION.OSUSER,1,10) OSUSER,
SUBSTR(V$SESSION.STATUS,1,10) ESTADO,
SUBSTR(V$SESSION.SERVER,1,7) SERVER,
SUBSTR(V$SESSION.MACHINE,1,30) MAQUINA
FROM
V$SESSION,
V$PROCESS
WHERE
(V$SESSION.PADDR = V$PROCESS.ADDR) AND
(V$SESSION.USERNAME IS NOT NULL) AND
(V$SESSION.SERVER LIKE 'DEDIC%') AND
SUBSTR(V$SESSION.STATUS,1,10) LIKE 'ACTIV%'
ORDER BY V$PROCESS.SPID;

SET LINESIZE 1000