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
/
lunes, 23 de agosto de 2010
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
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');
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);
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
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
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
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
Suscribirse a:
Entradas (Atom)