Sesiones y bloqueos en SQL Server

en junio 8, 2023junio 8, 2023 por pachita8002en SQL Server, UncategorizedDeja un comentario

Query para identificar sesiones bloqueadas

USE DB_PRUEBA
GO
SELECT session_id,
blocking_session_id,
wait_time,
wait_type,
last_wait_type,
wait_resource,
transaction_isolation_level,
lock_timeout
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0
GO

Matar todas las conexiones a una base:

declare @kill varchar(8000)=»;
begin
select ‘kill ‘+ CONVERT(varchar(5),session_id) + ‘;’
from sys.dm_exec_sessions where database_id = db_id(‘DB_PRUEBA’);
exec(@kill);
end;

truncar logs SQL Server

en noviembre 12, 2022 por pachita8002en UncategorizedDeja un comentario

Me pasaba frecuentemente que en SQL Server el espacio del log me llenaba el disco,

Me encontre este procedimiento y lo programé en una tarea despues de realizar el backup

USE [master]
GO
/****** Object: StoredProcedure [dbo].[TRUNCARLOG] Script Date: 28/04/2016 9:52:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TRUNCARLOG] as
DECLARE @dbName AS VARCHAR(100)
DECLARE @cmd AS VARCHAR(MAX)
DECLARE c1 CURSOR FOR
SELECT name FROM master..sysdatabases sdb
WHERE sdb.name NOT IN (‘master’,’model’,’msdb’,’pubs’,’northwind’,’tempdb’) and sdb.name NOT LIKE ‘%Share%’
ORDER BY name

OPEN c1

FETCH NEXT FROM c1
INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
–Establecer la base de datos en uso
SET @cmd = ‘USE ‘ + @dbName

–Si se establece el modo de restauración a simple, las partes inactivas del log de transacción deben ser borradas
–Este comando reducirá el archivo de log un poco
SET @cmd = @cmd + ‘ ALTER DATABASE ‘ + @dbName + ‘ SET RECOVERY SIMPLE’

–Obtener el nombre de log de la base de datos
SET @cmd = @cmd + ‘ DECLARE @logFile AS NVARCHAR(1000)’
SET @cmd = @cmd + ‘ SELECT @logFile = name FROM ‘ + @dbName + ‘.sys.database_files WHERE type_desc = »LOG»’
–Cambiar el modo de restauración a Simple no es suficiente, esto reduce el log a 1 MB
SET @cmd = @cmd + ‘ DBCC SHRINKFILE (@logFile , 1)’

EXEC(@cmd)
SET @cmd = @cmd + ‘ ALTER DATABASE ‘ + @dbName + ‘ SET RECOVERY FULL’
EXEC(@cmd)
FETCH NEXT FROM c1
INTO @dbName
END

CLOSE c1
DEALLOCATE c1

Encriptar varchar en oracle

en noviembre 12, 2022noviembre 12, 2022 por pachita8002en Scripts OracleDeja un comentario

paquete para encriptar y desencriptar datos en oracle

Para ejecutar el paquete:

--Actualizar la columna con el valor encriptado
update  tabla  set Columna_varchar=PKG_ENCRIPTAR.ENCRIPTA(Columna_varchar) 
where  Columna_varchar is not null;
commit;

SELECT CONSECUTIVO, PKG_ENCRIPTAR.DESENCRIPTA(Columna_varchar)
FROM tabla;

--Revisar los datos
select * from tabla;

--codigo del paquete

create or replace PACKAGE               pkg_encriptar
AS
    FUNCTION cipher_type RETURN PLS_INTEGER;
    FUNCTION encripta (input_string IN varchar2) RETURN raw;
    FUNCTION desencripta (encrypted_raw IN raw) RETURN varchar2;
END pkg_encriptar;

create or replace PACKAGE BODY               pkg_encriptar
AS
    ctype CONSTANT PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256
                                  + DBMS_CRYPTO.CHAIN_CBC
                                  + DBMS_CRYPTO.PAD_PKCS5;
    FUNCTION cipher_type RETURN PLS_INTEGER
    IS
    BEGIN
        RETURN ctype;
    END;

   FUNCTION encripta (input_string IN varchar2) RETURN raw
   IS
   output_string      VARCHAR2 (4000);
   encrypted_raw      RAW (4000);             -- stores encrypted binary text

BEGIN
   encrypted_raw := DBMS_CRYPTO.ENCRYPT
      (
         src => UTL_I18N.STRING_TO_RAW (input_string,  'AL32UTF8'),
         typ => ctype,
         key => UTL_ENCODE.BASE64_DECODE(UTL_I18N.STRING_TO_RAW('pMV3D4xhyfNxp3YyfLWzAErGcKkIjK3X6uc/WIeVTls=', 'AL32UTF8'))
      );
        RETURN   encrypted_raw;
END;	   
  
  
  FUNCTION desencripta (encrypted_raw IN raw) RETURN varchar2
   IS
   
   output_string      VARCHAR2 (4000);
   decrypted_raw      RAW (4000);             -- stores decrypted binary text
   v_error        varchar2(4000):=null;
   BEGIN
	  decrypted_raw := DBMS_CRYPTO.DECRYPT
      (
         src => encrypted_raw,
         typ => ctype,
         key => UTL_ENCODE.BASE64_DECODE(UTL_I18N.STRING_TO_RAW('pMV3D4xhyfNxp3YyfLWzAErGcKkIjK3X6uc/WIeVTls=', 'AL32UTF8'))
      );
  
    output_string := UTL_I18N.RAW_TO_CHAR (decrypted_raw, 'AL32UTF8');
   RETURN output_string;
   
   exception
   when others then
     v_error:= SQLCODE || ' - ' || SUBSTR (SQLERRM, 1, 1000);
     DBMS_OUTPUT.PUT_LINE ('Error ' || v_error); 
   END;

     
END pkg_encriptar;

Script tamaño backup RMAN

en marzo 8, 2020 por pachita8002en UncategorizedDeja un comentario

los siguientes son scripts útiles para obtener información del backup de RMAN desde la base de datos:

Tamaño total del backup en MB:

select sum(bytes)/1024/1024 Size_MB from v$backup_piece where to_char(start_time, ‘yyyy/mm/dd’) = ‘2020/03/08’;

Si utilizamos tags:

select sum(bytes)/1024/1024 Size_MB, sum(elapsed_seconds)/60 time_min
from v$backup_piece where
tag in ( ‘TAG20200212T221552′,’TAG20200213T013239’);

Información de las piezas del backup:

select * from v$backup_piece where to_char(start_time, ‘yyyy/mm/dd’) = ‘2019/02/26’;

crontab linux

en junio 8, 2017junio 8, 2017 por pachita8002en UncategorizedDeja un comentario

La herramienta crontab es una herramienta muy práctica para la programación de tareas en especial para bases de datos.

Cada tarea que queremos programar va en una linea del archivo, no se puede en la misma linea programar dos tareas

si lo que vamos a ejecutar es una shell, este archivo debe tener permisos de ejecución

Estructura de la linea crontab:

m h dom mon dow user command

m corresponde al minuto en que se va a ejecutar el script, el valor va de 0 a 59
h la hora exacta, se maneja el formato de 24 horas, los valores van de 0 a 23, siendo 0 las 12:00 de la medianoche.
dom hace referencia al día del mes, por ejemplo se puede especificar 15 si se quiere ejecutar cada dia 15
mon: hace referencia al mes, valor numérico del 1 al 12
dow significa el día de la semana, puede ser numérico (0 a 7, donde 0 y 7 son domingo) o las 3 primeras letras del día en inglés: mon, tue, wed, thu, fri, sat, sun.
user define el usuario que va a ejecutar el comando, puede ser root, u otro usuario diferente siempre y cuando tenga permisos de ejecución del script.
command refiere al comando o a la ruta absoluta del script a ejecutar, ejemplo: /home/usuario/scripts/actualizar.sh, si acaso llama a un script este debe ser ejecutable

Me parece mejor exponerlo con ejemplos, si quieres alguna opción especifica escribeme para compartirla:

Ejemplo 1: Tarea que se ejecuta todos los dias a las 5:15 am

15 5 * * * sh /ruta_del_archivo/nombre_archivo.sh

Ejemplo 2: Tarea que se ejecuta todos los martes a las 6:30 am

30 6 * * 2 * sh /ruta_del_archivo/nombre_archivo.sh

30 6 * * tue * sh /ruta_del_archivo/nombre_archivo.sh

Ejemplo 3: Tarea que se ejecuta el primer dia de cada mes, a las 4:00 pm(16:00)

00 16 1 * * * sh /ruta_del_archivo/nombre_archivo.sh

Ejemplo 4: Tarea que se ejecuta el dia 13 de Agosto a las 10:00 pm (22:00)

00 22 13 8 * sh /ruta_del_archivo/nombre_archivo.sh

Ejemplo 5: Tarea que se ejecuta en el minuto 30 de cada hora del mes de Mayo

30 * 5 * * sh /ruta_del_archivo/nombre_archivo.sh

Ejemplo 6: Tarea que se ejecuta los dias 1 y 15 de cada mes a las 2:00 a.m

00 2 1,15 * * sh /ruta_del_archivo/nombre_archivo.sh

Ejemplo 6: Tarea que se ejecuta los dias 1 y 15 de los meses Diciembre y Enero a las 2:00 a.m

00 2 1,15 1,12 * sh /ruta_del_archivo/nombre_archivo.sh

Truncar log de BD

en abril 28, 2016 por pachita8002en UncategorizedDeja un comentario

este procedimiento me lo encontre y lo programo despues del backup diario para liberar espacio en el servidor de SQL Server.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[TRUNCARLOG] Script Date: 28/04/2016 9:52:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

OPEN c1

FETCH NEXT FROM c1
INTO @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
–Establecer la base de datos en uso
SET @cmd = ‘USE ‘ + @dbName

EXEC(@cmd)
SET @cmd = @cmd + ‘ ALTER DATABASE ‘ + @dbName + ‘ SET RECOVERY FULL’
EXEC(@cmd)
FETCH NEXT FROM c1
INTO @dbName
END

CLOSE c1
DEALLOCATE c1

Roles en base de datos

en septiembre 11, 2015septiembre 11, 2015 por pachita8002en Scripts SQLDeja un comentario

Una buena práctica en Oracle es crear roles para los usuarios y de esta manera facilitar la administracion de usuarios y permisos.

En mi caso yo inicialmente creo 3 roles :

Administracion de datos: Este aplica para las personas que brindan soporte, que requieren modificar datos mas no estructuras de base de datos

CREATE ROLE ROLE_ADMDATOS;
— ROLES
GRANT «RESOURCE» TO ROLE_ADMDATOS ;
GRANT «CONNECT» TO ROLE_ADMDATOS ;
— SYSTEM PRIVILEGES
GRANT UPDATE ANY TABLE TO ROLE_ADMDATOS ;
GRANT INSERT ANY TABLE TO ROLE_ADMDATOS ;
GRANT SELECT ANY SEQUENCE TO ROLE_ADMDATOS ;
GRANT SELECT ANY TABLE TO ROLE_ADMDATOS ;
GRANT EXECUTE ANY PROCEDURE TO ROLE_ADMDATOS ;
GRANT UNDER ANY TABLE TO ROLE_ADMDATOS ;
GRANT UNDER ANY VIEW TO ROLE_ADMDATOS ;
GRANT DELETE ANY TABLE TO ROLE_ADMDATOS ;

Desarrollo de Aplicaciones: Este se le asigna a los desarrolladores que requieren la creacion de objetos y modificacion de los mismos, tienen permisos de consulta sobre diferentes vistas del sistema que les permiten ver la estructura de los objtetos de la base de datos.

CREATE ROLE ROLE_DLLO NOT IDENTIFIED
GRANT SELECT ON SYS.ALL_ALL_TABLES TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_ARGUMENTS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_CATALOG TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_COL_COMMENTS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_CONS_COLUMNS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_CONSTRAINTS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_ENCRYPTED_COLUMNS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_EXTERNAL_TABLES TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_OBJECTS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_OBJECT_TABLES TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_QUEUE_TABLES TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_SNAPSHOTS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_SYNONYMS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_TAB_COLUMNS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_TAB_COMMENTS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_TABLES TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_TAB_PRIVS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_TRIGGERS TO ROLE_DLLO;
GRANT SELECT ON SYS.ALL_USERS TO ROLE_DLLO;
GRANT SELECT ON SYS.SESSION_PRIVS TO ROLE_DLLO;
GRANT SELECT ON SYS.SESSION_ROLES TO ROLE_DLLO;
GRANT SELECT ON SYS.USER_OBJECTS TO ROLE_DLLO;
GRANT SELECT ON SYS.USER_ROLE_PRIVS TO ROLE_DLLO;
GRANT SELECT ON SYS.V_$OPTION TO ROLE_DLLO;
GRANT SELECT ON SYS.V_$PARAMETER TO ROLE_DLLO;
GRANT SELECT ON SYS.V_$SESSION TO ROLE_DLLO;
GRANT SELECT ANY DICTIONARY TO ROLE_DLLO;
GRANT CREATE SESSION TO ROLE_DLLO;
GRANT DROP ANY TABLE TO ROLE_DLLO;
GRANT ALTER ANY TABLE TO ROLE_DLLO;
GRANT CREATE ANY TABLE TO ROLE_DLLO;
GRANT DELETE ANY TABLE TO ROLE_DLLO;
GRANT INSERT ANY TABLE TO ROLE_DLLO;
GRANT SELECT ANY TABLE TO ROLE_DLLO;
GRANT UPDATE ANY TABLE TO ROLE_DLLO;
GRANT CREATE ANY PROCEDURE to ROLE_DLLO;
GRANT UNDER ANY VIEW TO ROLE_DLLO;
GRANT EXECUTE ANY PROCEDURE TO ROLE_DLLO;
GRANT EXECUTE ON UTL_FILE TO ROLE_DLLO;
GRANT RESOURCE TO ROLE_DLLO;
GRANT CONNECT TO ROLE_DLLO;
GRANT ROLE_DLLO TO SYS WITH ADMIN OPTION;

El último es el rol de consulta: Este se le asigna a quienes solo necesitan realizar consultas en la base, y ver la estructura de los objetos, adicional a este rol, a este tipo de usuarios se les dan permisos de consulta sobre tablas o vistas especificas segun la necesidad, para evitar la modificacion o consulta de informacion sensible en la empresa

create role ROLE_CONSULTA;
grant select on ALL_COL_COMMENTS to ROLE_CONSULTA;
grant select on ALL_CONS_COLUMNS to ROLE_CONSULTA;
grant select on ALL_CONSTRAINTS to ROLE_CONSULTA;
grant select on ALL_INDEXES to ROLE_CONSULTA;
grant select on ALL_TAB_COLS to ROLE_CONSULTA;
grant select on ALL_TAB_COLUMNS to ROLE_CONSULTA;
grant select on ALL_TAB_COMMENTS to ROLE_CONSULTA;
grant select on ALL_TABLES to ROLE_CONSULTA;
grant select on ALL_TRIGGERS to ROLE_CONSULTA;
grant select on ALL_VIEWS to ROLE_CONSULTA;
grant select on DUAL to ROLE_CONSULTA;
grant select on USER_INDEXES to ROLE_CONSULTA;
grant select on USER_OBJECTS to ROLE_CONSULTA;
grant select on USER_ROLE_PRIVS to ROLE_CONSULTA;

monitoreo de tablespaces oracle

en enero 8, 2015 por pachita8002en monitoreo oracle, shell monitoreo, UncategorizedDeja un comentario

con este procedimiento se configura desde linux que nos envie un correo informando cuando un tablespace ha superado el 80% de uso, de esta manera tomar las medidas correspondientes.

En la base de datos creee un procedure que muestra el nombre del tablespace y el porcentaje usado y si el porcentaje es mayor a 80% saca por pantalla el mensaje.

PROCEDURE MONITOREA_TABLSP AS

CURSOR c1 is
select SYSDATE AS FECHA, u.tblspc as TABLSPC,(a.fbytes/1024/1024) as ASIGNADO_MB,(u.ebytes/1024/1024) as USADO_MB,ROUND((u.ebytes/a.fbytes)*100,2) as PORC_USADO
from (select tablespace_name tblspc, sum(bytes) ebytes
from sys.dba_extents
group by tablespace_name) u,
(select tablespace_name tblspc, sum(bytes) fbytes
from sys.dba_data_files
group by tablespace_name) a
where u.tblspc = a.tblspc;
mensaje varchar2(200);
BEGIN

for reg in c1 loop
if reg.porc_usado >= 80 then
mensaje := mensaje || ‘ Tablespace: ‘|| reg.TABLSPC || ‘ Usado:’|| reg.porc_usado||’%’;
end if;
end loop;
dbms_output.put_line(mensaje);

END MONITOREA_TABLSP;

Luego se crea la shell en linux que ejecuta el procedimiento periódicamente, y si el procedure genera información, envia un correo con esta información.

#!/bin/bash
. /home/oracle/var_entorno
Para=pachita8002@yahoo.com
Fecha=$(date +»%Y%m%d»)
Asunto=Monitoreo_$Fecha
valor=$(sqlplus -s usuario/*******@BD @/home/oracle/scripts/espacio_tblsp.sql)
if [ -z «$valor» ]; then
echo «Reporte vacio: «$valor
else
echo $valor| mailx -s $Asunto $Para
fi

Esta shell se programó para ejecutarse cada 15 minutos, y si hay algun tablespace que supere el 80% de uso, se envia un correo.

Espero sea de utilidad

Apuntes útiles para dbas

Autor: pachita8002